Connecting to MySQL using Microsoft .NET

Connecting to MySQL using Microsoft .NET
This article explains how to integrate MySQL with Microsoft Visual Studio to develop .NET applications that access MySQL databases.

Adding MySQL support to Visual Studio
Visual Studio does not include MySQL support by default. You need to install the following components:

• MySQL for Visual Studio: Adds MySQL support to Visual Studio's visual database tools. Download it from MySQL for Visual Studio.

• Connector/Net: Adds .NET drivers for MySQL. Download it from Connector/Net.

Important: To access MySQL databases remotely, add your IP address to the allowed list for remote access to avoid “Access denied” errors.

Using Server Explorer
After installing MySQL for Visual Studio:

1. Start Visual Studio and go to View > Server Explorer.

2. Click the Connect to Database icon.

3. In the Data source list, select MySQL Database and click Continue.

4. Enter your hosting.com server name, database username, and password.

5. Enter the database name and click Test Connection. If successful, click OK.

6. Server Explorer displays the server and database. Double-click to explore Tables, Views, Stored Procedures, Stored Functions, and UDFs.

Connecting to MySQL using .NET code
After installing Connector/Net:

1. Create a new Console Application project in Visual Studio.

2. Delete existing code and paste a template. Replace the connection string values with your server, database, user, and password details. Replace table_name with the table you want to query.

C# Example:

using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace MySQL_test
{
    class Program
    {
        static void Main(string[] args)
        {
            string connstring = @"server=example.com;userid=example_user;password=example_password;database=example_database";
            MySqlConnection conn = null;
            
            try
            {
                conn = new MySqlConnection(connstring);
                conn.Open();

                string query = "SELECT * FROM table_name;";
                MySqlDataAdapter da = new MySqlDataAdapter(query, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "table_name");
                DataTable dt = ds.Tables["table_name"];

                foreach (DataRow row in dt.Rows)
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        Console.Write(row[col] + "\t");
                    }
                    Console.Write(" ");                  
                }           
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: {0}", e.ToString());
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
    }
}

Visual Basic Example:

Imports System
Imports System.Data
Imports MySql.Data.MySqlClient

Module Module1
    Sub Main()
        Dim connstring As String = "server=example.com;userid=example_user;password=example_password;database=example_database"
        Dim conn As MySqlConnection = Nothing

        Try
            conn = New MySqlConnection(connstring)
            conn.Open()

            Dim query As String = "SELECT * FROM table_name;"
            Dim da As New MySqlDataAdapter(query, conn)
            Dim ds As New DataSet()
            da.Fill(ds, "table_name")
            Dim dt As DataTable = ds.Tables("table_name")

            For Each row As DataRow In dt.Rows
                For Each col As DataColumn In dt.Columns
                    Console.Write(row(col).ToString() + vbTab)
                Next
                Console.Write(vbNewLine)
            Next
        Catch e As Exception
            Console.WriteLine("Error: {0}", e.ToString())
        Finally
            If conn IsNot Nothing Then
                conn.Close()
            End If
        End Try
    End Sub
End Module

3. Add a reference to MySql.Data via Project > Add Reference > Assemblies > Extensions.

4. Build the project and run it (Debug > Start Without Debugging) to see data from the selected table.

More information
For details about Microsoft Visual Studio, visit Visual Studio homepage.