22.2.5.2. Creating a Connector/Net Connection String

The MySqlConnection object is configured using a connection string. A connection string contains several key/value pairs, separated by semicolons. In each key/value pair, the option name and its corresponding value are joined by an equal sign. For the list of option names to use in the connection string, see Section 22.2.6, "Connector/Net Connection String Options Reference".

The following is a sample connection string:

Server=127.0.0.1;Uid=root;Pwd=12345;Database=test;

In this example, the MySqlConnection object is configured to connect to a MySQL server at 127.0.0.1, with a user name of root and a password of 12345. The default database for all statements will be the test database.

Note

Using the '@' symbol for parameters is now the preferred approach, although the old pattern of using '?' is still supported. To avoid conflicts when using the '@' symbol in combination with user variables, see the Allow User Variables connection string option in Section 22.2.6, "Connector/Net Connection String Options Reference". The Old Syntax connection string option has now been deprecated.

22.2.5.2.1. Opening a Connection

Once you have created a connection string it can be used to open a connection to the MySQL server.

The following code is used to create a MySqlConnection object, assign the connection string, and open the connection.

Connector/Net can also connect using the native Windows authentication plugin. See Section 22.2.5.5, "Using the Windows Native Authentication Plugin" for details.

You can further extend the authentication mechanism by writing your own authentication plugin. See Section 22.2.5.6, "Writing a Custom Authentication Plugin" for details.

Visual Basic Example

Dim conn As New MySql.Data.MySqlClient.MySqlConnectionDim myConnectionString as StringmyConnectionString = "server=127.0.0.1;" _            & "uid=root;" _            & "pwd=12345;" _            & "database=test;"Try  conn.ConnectionString = myConnectionString  conn.Open()Catch ex As MySql.Data.MySqlClient.MySqlException  MessageBox.Show(ex.Message)End Try

C# Example

MySql.Data.MySqlClient.MySqlConnection conn;string myConnectionString;myConnectionString = "server=127.0.0.1;uid=root;" +    "pwd=12345;database=test;";try{    conn = new MySql.Data.MySqlClient.MySqlConnection();    conn.ConnectionString = myConnectionString;    conn.Open();}catch (MySql.Data.MySqlClient.MySqlException ex){    MessageBox.Show(ex.Message);}

You can also pass the connection string to the constructor of the MySqlConnection class:

Visual Basic Example

Dim myConnectionString as StringmyConnectionString = "server=127.0.0.1;" _              & "uid=root;" _              & "pwd=12345;" _              & "database=test;"Try    Dim conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)    conn.Open()Catch ex As MySql.Data.MySqlClient.MySqlException   MessageBox.Show(ex.Message)End Try

C# Example

MySql.Data.MySqlClient.MySqlConnection conn;string myConnectionString;myConnectionString = "server=127.0.0.1;uid=root;" +    "pwd=12345;database=test;";try{    conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);    conn.Open();}catch (MySql.Data.MySqlClient.MySqlException ex){    MessageBox.Show(ex.Message);}

Once the connection is open it can be used by the other Connector/Net classes to communicate with the MySQL server.

22.2.5.2.2. Handling Connection Errors

Because connecting to an external server is unpredictable, it is important to add error handling to your .NET application. When there is an error connecting, the MySqlConnection class will return a MySqlException object. This object has two properties that are of interest when handling errors:

Message: A message that describes the current exception.
Number: The MySQL error number.

When handling errors, you can your application's response based on the error number. The two most common error numbers when connecting are as follows:

0: Cannot connect to server.
1045: Invalid user name and/or password.

The following code shows how to adapt the application's response based on the actual error:

Visual Basic Example

Dim myConnectionString as StringmyConnectionString = "server=127.0.0.1;" _          & "uid=root;" _          & "pwd=12345;" _          & "database=test;"Try    Dim conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)    conn.Open()Catch ex As MySql.Data.MySqlClient.MySqlException    Select Case ex.Number        Case 0            MessageBox.Show("Cannot connect to server. Contact administrator")        Case 1045            MessageBox.Show("Invalid username/password, please try again")    End SelectEnd Try

C# Example

MySql.Data.MySqlClient.MySqlConnection conn;string myConnectionString;myConnectionString = "server=127.0.0.1;uid=root;" +    "pwd=12345;database=test;";try{    conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);    conn.Open();}    catch (MySql.Data.MySqlClient.MySqlException ex){    switch (ex.Number)    {        case 0:            MessageBox.Show("Cannot connect to server.  Contact administrator");        case 1045:            MessageBox.Show("Invalid username/password, please try again");    }}

Important

Note that if you are using multilanguage databases you must specify the character set in the connection string. If you do not specify the character set, the connection defaults to the latin1 charset. You can specify the character set as part of the connection string, for example:

MySqlConnection myConnection = new MySqlConnection("server=127.0.0.1;uid=root;" +"pwd=12345;database=test;Charset=latin1;");

22.2.5.2.3. Using GetSchema on a Connection

The GetSchema() method of the connection object can be used to retrieve schema information about the database currently connected to. The schema information is returned in the form of a DataTable. The schema information is organized into a number of collections. Different forms of the GetSchema() method can be used depending on the information required. There are three forms of the GetSchema() method:

GetSchema() - This call will return a list of available collections.
GetSchema(String) - This call returns information about the collection named in the string parameter. If the string "MetaDataCollections" is used then a list of all available collections is returned. This is the same as calling GetSchema() without any parameters.
GetSchema(String, String[]) - In this call the first string parameter represents the collection name, and the second parameter represents a string array of restriction values. Restriction values limit the amount of data that will be returned. Restriction values are explained in more detail in the Microsoft .NET documentation.

22.2.5.2.3.1. Collections

The collections can be broadly grouped into two types: collections that are common to all data providers, and collections specific to a particular provider.

Common

The following collections are common to all data providers:

MetaDataCollections
DataSourceInformation
DataTypes
Restrictions
ReservedWords

Provider-specific

The following are the collections currently provided by MySQL Connector/Net, in addition to the common collections above:

Databases
Tables
Columns
Users
Foreign Keys
IndexColumns
Indexes
Foreign Key Columns
UDF
Views
ViewColumns
Procedure Parameters
Procedures
Triggers

Example Code

A list of available collections can be obtained using the following code:

using System;using System.Data;using System.Text;using MySql.Data;using MySql.Data.MySqlClient;namespace ConsoleApplication2{    class Program    {        private static void DisplayData(System.Data.DataTable table)        {            foreach (System.Data.DataRow row in table.Rows)            {                foreach (System.Data.DataColumn col in table.Columns)                {                    Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);                }                Console.WriteLine("============================");            }        }        static void Main(string[] args)        {            string connStr = "server=localhost;user=root;database=world;port=3306;password=******;";            MySqlConnection conn = new MySqlConnection(connStr);            try            {                Console.WriteLine("Connecting to MySQL...");                conn.Open();                DataTable table = conn.GetSchema("MetaDataCollections");                //DataTable table = conn.GetSchema("UDF");                DisplayData(table);                conn.Close();            }            catch (Exception ex)            {                Console.WriteLine(ex.ToString());            }            Console.WriteLine("Done.");        }    }}

Further information on the GetSchema() method and schema collections can be found in the Microsoft .NET documentation.