The MySqlConnection object is configured using
a connection string. A connection string contains sever key/value
pairs, separated by semicolons. Each key/value pair is joined with
an equal sign.
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.
The following options are available:
Using the '@' symbol for parameters is now the preferred approach although the old pattern of using '?' is still supported.
Please be aware however that using '@' can cause conflicts when
user variables are also used. To help with this situation please
see the documentation on the Allow User
Variables connection string option, which can be found
here:
Section 22.2.5.2, “Creating a Connection String”.
The Old Syntax connection string option has
now been deprecated.
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.
Visual Basic Example
Dim conn As New MySql.Data.MySqlClient.MySqlConnection
Dim myConnectionString as String
myConnectionString = "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 String
myConnectionString = "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.
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 String
myConnectionString = "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 Select
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)
{
switch (ex.Number)
{
case 0:
MessageBox.Show("Cannot connect to server. Contact administrator");
case 1045:
MessageBox.Show("Invalid username/password, please try again");
}
}
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;");
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 organised 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 callingGetSchema()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.
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.