Chapter 5 Connector/NET Programming

Table of Contents

5.1 Connecting to MySQL Using Connector/NET
5.1.1 Creating a Connector/NET Connection String
5.1.2 Handling Connection Errors
5.1.3 Using GetSchema on a Connection
5.2 Using MySqlCommand
5.3 Using Connector/NET with Connection Pooling
5.4 Using the Windows Native Authentication Plugin
5.5 Writing a Custom Authentication Plugin
5.6 Using Connector/NET with Table Caching
5.7 Using the Connector/NET with Prepared Statements
5.7.1 Preparing Statements in Connector/NET
5.8 Accessing Stored Procedures with Connector/NET
5.8.1 Using Stored Routines from Connector/NET
5.9 Handling BLOB Data With Connector/NET
5.9.1 Preparing the MySQL Server
5.9.2 Writing a File to the Database
5.9.3 Reading a BLOB from the Database to a File on Disk
5.10 Asynchronous Methods
5.11 Using the Connector/NET Interceptor Classes
5.12 Handling Date and Time Information in Connector/NET
5.12.1 Fractional Seconds
5.12.2 Problems when Using Invalid Dates
5.12.3 Restricting Invalid Dates
5.12.4 Handling Invalid Dates
5.12.5 Handling NULL Dates
5.13 Using the MySqlBulkLoader Class
5.14 Using the Connector/NET Trace Source Object
5.14.1 Viewing MySQL Trace Information
5.14.2 Building Custom Listeners
5.15 Binary/Nonbinary Issues
5.16 Character Set Considerations for Connector/NET
5.17 Using Connector/NET with Crystal Reports
5.17.1 Creating a Data Source
5.17.2 Creating the Report
5.17.3 Displaying the Report
5.18 ASP.NET Provider Model
5.19 Working with Partial Trust / Medium Trust
5.19.1 Evolution of Partial Trust Support Across Connector/NET Versions
5.19.2 Configuring Partial Trust with Connector/NET Library Installed in GAC
5.19.3 Configuring Partial Trust with Connector/NET Library Not Installed in GAC

MySQL Connector/NET comprises several classes that are used to connect to the database, execute queries and statements, and manage query results.

The following are the major classes of Connector/NET:

In the following sections, you will learn about some common use cases for Connector/NET, including BLOB handling, date handling, and using Connector/NET with common tools such as Crystal Reports.

5.1 Connecting to MySQL Using Connector/NET

All interaction between a .NET application and the MySQL server is routed through a MySqlConnection object. Before your application can interact with the server, it must instantiate, configure, and open a MySqlConnection object.

Even when using the MySqlHelper class, a MySqlConnection object is created by the helper class.

This section describes how to connect to MySQL using the MySqlConnection object.

5.1.1 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 Chapter 6, Connector/NET 6.10 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 Chapter 6, Connector/NET 6.10 Connection-String Options Reference. The Old Syntax connection string option has now been deprecated.

Opening a Connection

After 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.

MySQL Connector/NET can also connect using the native Windows authentication plugin. See Section 5.4, “Using the Windows Native Authentication Plugin” for details.

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

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);
}

After the connection is open, it can be used by the other Connector/NET classes to communicate with the MySQL server.

5.1.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 adapt the response of your application 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, user password, or both.

The following code example shows how to manage the response of an application 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");
            break;
        case 1045:
            MessageBox.Show("Invalid username/password, please try again");
            break;
    }
}
Important

If you are using multilanguage databases then you must specify the character set in the connection string. If you do not specify the character set, the connection defaults to the latin1 character set. 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");

5.1.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.

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 Collections.  The following collections are common to all data providers:

  • MetaDataCollections

  • DataSourceInformation

  • DataTypes

  • Restrictions

  • ReservedWords

Provider-Specific Collections.  The following are the collections currently provided by Connector/NET, in addition to the common collections shown previously:

  • 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.

5.2 Using MySqlCommand

A MySqlCommand has the CommandText and CommandType properties associated with it. The CommandText will be handled differently depending on the setting of CommandType. CommandType can be one of:

  • Text - An SQL text command (default)

  • StoredProcedure - The name of a Stored Procedure

  • TableDirect - The name of a table (new in Connector/NET 6.2)

The default CommandType, Text, is used for executing queries and other SQL commands. Some example of this can be found in the following section Section 4.1.2, “The MySqlCommand Object”.

If CommandType is set to StoredProcedure, set CommandText to the name of the Stored Procedure to access.

If CommandType is set to TableDirect, all rows and columns of the named table will be returned when you call one of the Execute methods. In effect, this command performs a SELECT * on the table specified. The CommandText property is set to the name of the table to query. This is illustrated by the following code snippet:

...
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "mytable";
cmd.Connection = someConnection;
cmd.CommandType = CommandType.TableDirect;
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
   Console.WriteLn(reader[0], reader[1]...);
}
...

Examples of using the CommandType of StoredProcedure can be found in the section Section 5.8, “Accessing Stored Procedures with Connector/NET”.

Commands can have a timeout associated with them. This is useful as you may not want a situation were a command takes up an excessive amount of time. A timeout can be set using the CommandTimeout property. The following code snippet sets a timeout of one minute:

MySqlCommand cmd = new MySqlCommand();
cmd.CommandTimeout = 60;

The default value is 30 seconds. Avoid a value of 0, which indicates an indefinite wait. To change the default command timeout, use the connection string option Default Command Timeout.

Prior to Connector/NET 6.2, MySqlCommand.CommandTimeout included user processing time, that is processing time not related to direct use of the connector. Timeout was implemented through a .NET Timer, that triggered after CommandTimeout seconds. This timer consumed a thread.

Connector/NET 6.2 introduced timeouts that are aligned with how Microsoft handles SqlCommand.CommandTimeout. This property is the cumulative timeout for all network reads and writes during command execution or processing of the results. A timeout can still occur in the MySqlReader.Read method after the first row is returned, and does not include user processing time, only IO operations. The 6.2 implementation uses the underlying stream timeout facility, so is more efficient in that it does not require the additional timer thread as was the case with the previous implementation.

Further details on this can be found in the relevant Microsoft documentation.

5.3 Using Connector/NET with Connection Pooling

The MySQL Connector/NET supports connection pooling for better performance and scalability with database-intensive applications. This is enabled by default. You can turn it off or adjust its performance characteristics using the connection string options Pooling, Connection Reset, Connection Lifetime, Cache Server Properties, Max Pool Size and Min Pool Size. See Section 5.1.1, “Creating a Connector/NET Connection String” for further information.

Connection pooling works by keeping the native connection to the server live when the client disposes of a MySqlConnection. Subsequently, if a new MySqlConnection object is opened, it will be created from the connection pool, rather than creating a new native connection. This improves performance.

Guidelines

To work as designed, it is best to let the connection pooling system manage all connections. Do not create a globally accessible instance of MySqlConnection and then manually open and close it. This interferes with the way the pooling works and can lead to unpredictable results or even exceptions.

One approach that simplifies things is to avoid manually creating a MySqlConnection object. Instead use the overloaded methods that take a connection string as an argument. Using this approach, Connector/NET will automatically create, open, close and destroy connections, using the connection pooling system for best performance.

Typed Datasets and the MembershipProvider and RoleProvider classes use this approach. Most classes that have methods that take a MySqlConnection as an argument, also have methods that take a connection string as an argument. This includes MySqlDataAdapter.

Instead of manually creating MySqlCommand objects, you can use the static methods of the MySqlHelper class. These take a connection string as an argument, and they fully support connection pooling.

Resource Usage

Starting with Connector/NET 6.2, there is a background job that runs every three minutes and removes connections from pool that have been idle (unused) for more than three minutes. The pool cleanup frees resources on both client and server side. This is because on the client side every connection uses a socket, and on the server side every connection uses a socket and a thread.

Prior to this change, connections were never removed from the pool, and the pool always contained the peak number of open connections. For example, a web application that peaked at 1000 concurrent database connections would consume 1000 threads and 1000 open sockets at the server, without ever freeing up those resources from the connection pool. Connections, no matter how old, will not be closed if the number of connections in the pool is less than or equal to the value set by the Min Pool Size connection string parameter.

5.4 Using the Windows Native Authentication Plugin

MySQL Connector/NET applications can authenticate to a MySQL server using the Windows Native Authentication Plugin as of Connector/NET 6.4.4 and MySQL 5.5.16/5.6.10. Users who have logged in to Windows can connect from MySQL client programs to the server based on the information in their environment without specifying an additional password. For background and usage information about the authentication plugin, see Windows Pluggable Authentication.

The interface matches the MySql.Data.MySqlClient object. To enable, pass in Integrated Security to the connection string with a value of yes or sspi.

Passing in a user ID is optional. When Windows authentication is set up, a MySQL user is created and configured to be used by Windows authentication. By default, this user ID is named auth_windows, but can be defined using a different name. If the default name is used, then passing the user ID to the connection string from Connector/NET is optional, because it will use the auth_windows user. Otherwise, the name must be passed to the connection string using the standard user ID element.

5.5 Writing a Custom Authentication Plugin

Advanced users with special security requirements can create their own authentication plugins for MySQL Connector/NET applications. You can extend the handshake protocol, adding custom logic. This capability requires Connector/NET 6.6.3 or higher, and MySQL 5.5.16 or higher. For background and usage information about MySQL authentication plugins, see Authentication Plugins and Writing Authentication Plugins.

To write a custom authentication plugin, you will need a reference to the assembly MySql.Data.dll. The classes relevant for writing authentication plugins are available at the namespace MySql.Data.MySqlClient.Authentication.

How the Custom Authentication Plugin Works

At some point during handshake, the internal method

void Authenticate(bool reset)

of MySqlAuthenticationPlugin is called. This method in turns calls several overridable methods of the current plugin.

Creating the Authentication Plugin Class

You put the authentication plugin logic inside a new class derived from MySql.Data.MySqlClient.Authentication.MySqlAuthenticationPlugin. The following methods are available to be overridden:

protected virtual void CheckConstraints()
protected virtual void AuthenticationFailed(Exception ex)
protected virtual void AuthenticationSuccessful()
protected virtual byte[] MoreData(byte[] data)
protected virtual void AuthenticationChange()
public abstract string PluginName { get; }
public virtual string GetUsername()
public virtual object GetPassword()
protected byte[] AuthData;

The following is a brief explanation of each one:


/// <summary>
/// This method must check authentication method specific constraints in the
environment and throw an Exception
/// if the conditions are not met. The default implementation does nothing.
/// </summary>
protected virtual void CheckConstraints()

/// <summary>
/// This method, called when the authentication failed, provides a chance to
plugins to manage the error
/// the way they consider decide (either showing a message, logging it, etc.).
/// The default implementation wraps the original exception in a MySqlException
with an standard message and rethrows it.
/// </summary>
/// <param name="ex">The exception with extra information on the error.</param>
protected virtual void AuthenticationFailed(Exception ex)

/// <summary>
/// This method is invoked when the authentication phase was successful accepted
by the server.
/// Derived classes must override this if they want to be notified of such
condition.
/// </summary>
/// <remarks>The default implementation does nothing.</remarks>
protected virtual void AuthenticationSuccessful()

/// <summary>
/// This method provides a chance for the plugin to send more data when the
server requests so during the
/// authentication phase. This method will be called at least once, and more
than one depending upon whether the
/// server response packets have the 0x01 prefix.
/// </summary>
/// <param name="data">The response data from the server, during the
authentication phase the first time is called is null, in
subsequent calls contains the server response.</param>
/// <returns>The data generated by the plugin for server consumption.</returns>
/// <remarks>The default implementation always returns null.</remarks>
protected virtual byte[] MoreData(byte[] data)

/// <summary>
/// The plugin name.
/// </summary>
public abstract string PluginName { get; }

/// <summary>
/// Gets the user name to send to the server in the authentication phase.
/// </summary>
/// <returns>An string with the user name</returns>
/// <remarks>Default implementation returns the UserId passed from the
connection string.</remarks>
public virtual string GetUsername()

/// <summary>
/// Gets the password to send to the server in the authentication phase. This
can be a string or a
/// </summary>
/// <returns>An object, can be byte[], string or null, with the password.
</returns>
/// <remarks>Default implementation returns null.</remarks>
public virtual object GetPassword()

/// <summary>
/// The authentication data passed when creating the plugin.
/// For example in mysql_native_password this is the seed to encrypt the
password.
/// </summary>
protected byte[] AuthData;

Sample Authentication Plugin

Here is an example showing how to create the authentication plugin, then enable it by means of a configuration file. Follow these steps:

  1. Create a console app, adding a reference to MySql.Data.dll.

  2. Design the main program as follows:

    
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using MySql.Data.MySqlClient;
    
    namespace AuthPluginTest
    {
      class Program
      {
        static void Main(string[] args)
        {
          // Customize the connection string as necessary.
          MySqlConnection con = new MySqlConnection("server=localhost;
    database=test; user id=myuser; password=mypass");
          con.Open();
          con.Close();
        }
      }
    }
    
    
  3. Create your plugin class. In this example, we add an alternative implementation of the Native password plugin by just using the same code from the original plugin. We name our class MySqlNativePasswordPlugin2:

    
    using System.IO;
    using System;
    using System.Text;
    using System.Security.Cryptography;
    using MySql.Data.MySqlClient.Authentication;
    using System.Diagnostics;
    
    namespace AuthPluginTest
    {
      public class MySqlNativePasswordPlugin2 : MySqlAuthenticationPlugin
      {
        public override string PluginName
        {
          get { return "mysql_native_password"; }
        }
    
        public override object GetPassword()
        {
          Debug.WriteLine("Calling MySqlNativePasswordPlugin2.GetPassword");
          return Get411Password(Settings.Password, AuthData);
        }
    
        /// <summary>
        /// Returns a byte array containing the proper encryption of the
        /// given password/seed according to the new 4.1.1 authentication scheme.
        /// </summary>
        /// <param name="password"></param>
        /// <param name="seed"></param>
        /// <returns></returns>
        private byte[] Get411Password(string password, byte[] seedBytes)
        {
          // if we have no password, then we just return 1 zero byte
          if (password.Length == 0) return new byte[1];
    
          SHA1 sha = new SHA1CryptoServiceProvider();
    
          byte[] firstHash = sha.ComputeHash(Encoding.Default.GetBytes(password));
          byte[] secondHash = sha.ComputeHash(firstHash);
    
          byte[] input = new byte[seedBytes.Length + secondHash.Length];
          Array.Copy(seedBytes, 0, input, 0, seedBytes.Length);
          Array.Copy(secondHash, 0, input, seedBytes.Length, secondHash.Length);
          byte[] thirdHash = sha.ComputeHash(input);
    
          byte[] finalHash = new byte[thirdHash.Length + 1];
          finalHash[0] = 0x14;
          Array.Copy(thirdHash, 0, finalHash, 1, thirdHash.Length);
    
          for (int i = 1; i < finalHash.Length; i++)
            finalHash[i] = (byte)(finalHash[i] ^ firstHash[i - 1]);
          return finalHash;
        }
      }
    }
    
    
  4. Notice that the plugin implementation just overrides GetPassword, and provides an implementation to encrypt the password using the 4.1 protocol. We also put the following line in the GetPassword body:

    Debug.WriteLine("Calling MySqlNativePasswordPlugin2.GetPassword");
    

    to provide confirmation that the plugin was effectively used. (You could also put a breakpoint on that method.)

  5. Enable the new plugin in the configuration file:

    <?xml version="1.0"?>
    <configuration>
      <configSections>
        <section name="MySQL" type="MySql.Data.MySqlClient.MySqlConfiguration,
    MySql.Data"/>
      </configSections>
      <MySQL>
        <AuthenticationPlugins>
          <add name="mysql_native_password"
    type="AuthPluginTest.MySqlNativePasswordPlugin2, AuthPluginTest"></add>
        </AuthenticationPlugins>  
      </MySQL>
    <startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
    </startup></configuration>
    
  6. Run the application. In Visual Studio, you will see the message Calling MySqlNativePasswordPlugin2.GetPassword in the debug window.

  7. Continue enhancing the authentication logic, overriding more methods if you required.

5.6 Using Connector/NET with Table Caching

This feature exists with MySQL Connector/NET versions 6.4 and above.

Table caching is a feature that can be used to cache slow-changing datasets on the client side. This is useful for applications that are designed to use readers, but still want to minimize trips to the server for slow-changing tables.

This feature is transparent to the application, and is disabled by default.

Configuration

  • To enable table caching, add 'table cache = true' to the connection string.

  • Optionally, specify the 'Default Table Cache Age' connection string option, which represents the number of seconds a table is cached before the cached data is discarded. The default value is 60.

  • You can turn caching on and off and set caching options at runtime, on a per-command basis.

5.7 Using the Connector/NET with Prepared Statements

Use of prepared statements can provide significant performance improvements on queries that are executed more than once.

Note

Prepared statement support is available with MySQL 4.1 and higher.

Prepared execution is faster than direct execution for statements executed more than once, primarily because the query is parsed only once. In the case of direct execution, the query is parsed every time it is executed. Prepared execution also can provide a reduction of network traffic because for each execution of the prepared statement, it is necessary only to send the data for the parameters.

Another advantage of prepared statements is that, with server-side prepared statements enabled, it uses a binary protocol that makes data transfer between client and server more efficient.

Note

Enable server-side prepapared statements (they are disabled by default) by passing in "IgnorePrepare=false" to your connection string.

5.7.1 Preparing Statements in Connector/NET

To prepare a statement, create a command object and set the CommandText property to your query.

After entering your statement, call the Prepare method of the MySqlCommand object. After the statement is prepared, add parameters for each of the dynamic elements in the query.

After you enter your query and enter parameters, execute the statement using the ExecuteNonQuery(), ExecuteScalar(), or ExecuteReader methods.

For subsequent executions, you need only modify the values of the parameters and call the execute method again, there is no need to set the CommandText property or redefine the parameters.

Visual Basic Example

Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand

conn.ConnectionString = strConnection

Try
   conn.Open()
   cmd.Connection = conn

   cmd.CommandText = "INSERT INTO myTable VALUES(NULL, @number, @text)"
   cmd.Prepare()

   cmd.Parameters.AddWithValue("@number", 1)
   cmd.Parameters.AddWithValue("@text", "One")

   For i = 1 To 1000
       cmd.Parameters("@number").Value = i
       cmd.Parameters("@text").Value = "A string value"

       cmd.ExecuteNonQuery()
     Next
Catch ex As MySqlException
    MessageBox.Show("Error " & ex.Number & " has occurred: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

C# Example

MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();

conn.ConnectionString = strConnection;

try
{
    conn.Open();
    cmd.Connection = conn;

    cmd.CommandText = "INSERT INTO myTable VALUES(NULL, @number, @text)";
    cmd.Prepare();

    cmd.Parameters.AddWithValue("@number", 1);
    cmd.Parameters.AddWithValue("@text", "One");

    for (int i=1; i <= 1000; i++)
    {
        cmd.Parameters["@number"].Value = i;
        cmd.Parameters["@text"].Value = "A string value";

        cmd.ExecuteNonQuery();
    }
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
        "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

5.8 Accessing Stored Procedures with Connector/NET

MySQL 5.0 and higher supports stored procedures with the SQL 2003 stored procedure syntax.

A stored procedure is a set of SQL statements that is stored in the server. Clients make a single call to the stored procedure, passing parameters that can influence the procedure logic and query conditions, rather than issuing individual hardcoded SQL statements.

Stored procedures can be particularly useful in situations such as the following:

  • Stored procedures can act as an API or abstraction layer, allowing multiple client applications to perform the same database operations. The applications can be written in different languages and run on different platforms. The applications do not need to hardcode table and column names, complicated queries, and so on. When you extend and optimize the queries in a stored procedure, all the applications that call the procedure automatically receive the benefits.

  • When security is paramount, stored procedures keep applications from directly manipulating tables, or even knowing details such as table and column names. Banks, for example, use stored procedures for all common operations. This provides a consistent and secure environment, and procedures can ensure that each operation is properly logged. In such a setup, applications and users would not get any access to the database tables directly, but can only execute specific stored procedures.

Connector/NET supports the calling of stored procedures through the MySqlCommand object. Data can be passed in and out of a MySQL stored procedure through use of the MySqlCommand.Parameters collection.

Note

When you call a stored procedure (in versions before the MySQL 8.0 release series), the command object makes an additional SELECT call to determine the parameters of the stored procedure. You must ensure that the user calling the procedure has the SELECT privilege on the mysql.proc table to enable them to verify the parameters. Failure to do this will result in an error when calling the procedure.

This section will not provide in-depth information on creating Stored Procedures. For such information, please refer to https://dev.mysql.com/doc/mysql/en/stored-routines.html.

A sample application demonstrating how to use stored procedures with Connector/NET can be found in the Samples directory of your Connector/NET installation.

5.8.1 Using Stored Routines from Connector/NET

Stored procedures in MySQL can be created using a variety of tools. First, stored procedures can be created using the mysql command-line client. Second, stored procedures can be created using MySQL Workbench. Finally, stored procedures can be created using the ExecuteNonQuery method of the MySqlCommand object.

Unlike the command-line and GUI clients, you are not required to specify a special delimiter when creating stored procedures in Connector/NET.

To call a stored procedure using Connector/NET, you create a MySqlCommand object and pass the stored procedure name as the CommandText property. You then set the CommandType property to CommandType.StoredProcedure.

After the stored procedure is named, you create one MySqlCommand parameter for every parameter in the stored procedure. IN parameters are defined with the parameter name and the object containing the value, OUT parameters are defined with the parameter name and the data type that is expected to be returned. All parameters need the parameter direction defined.

After defining the parameters, you call the stored procedure by using the MySqlCommand.ExecuteNonQuery() method.

Once the stored procedure is called, the values of the output parameters can be retrieved by using the .Value property of the MySqlConnector.Parameters collection.

Note

When a stored procedure is called using MySqlCommand.ExecuteReader, and the stored procedure has output parameters, the output parameters are only set after the MySqlDataReader returned by ExecuteReader is closed.

The following C# example code demonstrates the use of stored procedures. It assumes the database 'employees' has already been created:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

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

namespace UsingStoredRoutines
{
    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "server=localhost;user=root;database=employees;port=3306;password=******";
            MySqlCommand cmd = new MySqlCommand();

            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DROP TABLE IF EXISTS emp";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20), birthdate DATE)";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "CREATE PROCEDURE add_emp(" +
                                  "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT)" +
                                  "BEGIN INSERT INTO emp(first_name, last_name, birthdate) " +
                                  "VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END";

                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                Console.WriteLine ("Error " + ex.Number + " has occurred: " + ex.Message);
            }
            conn.Close();
            Console.WriteLine("Connection closed.");
            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "add_emp";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@lname", "Jones");
                cmd.Parameters["@lname"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@fname", "Tom");
                cmd.Parameters["@fname"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@bday", "1940-06-07");
                cmd.Parameters["@bday"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@empno", MySqlDbType.Int32);
                cmd.Parameters["@empno"].Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();

                Console.WriteLine("Employee number: "+cmd.Parameters["@empno"].Value);
                Console.WriteLine("Birthday: " + cmd.Parameters["@bday"].Value);
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            }
            conn.Close();
            Console.WriteLine("Done.");
        }
    }
}

The following code shows the same application in Visual Basic:


Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text

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

Module Module1

    Sub Main()
        Dim conn As New MySqlConnection()
        conn.ConnectionString = "server=localhost;user=root;database=world;port=3306;password=******"
        Dim cmd As New MySqlCommand()

        Try
            Console.WriteLine("Connecting to MySQL...")
            conn.Open()
            cmd.Connection = conn
            cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "DROP TABLE IF EXISTS emp"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20), birthdate DATE)"
            cmd.ExecuteNonQuery()

            cmd.CommandText = "CREATE PROCEDURE add_emp(" & "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT)" & "BEGIN INSERT INTO emp(first_name, last_name, birthdate) " & "VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END"

            cmd.ExecuteNonQuery()
        Catch ex As MySqlException
            Console.WriteLine(("Error " & ex.Number & " has occurred: ") + ex.Message)
        End Try
        conn.Close()
        Console.WriteLine("Connection closed.")
        Try
            Console.WriteLine("Connecting to MySQL...")
            conn.Open()
            cmd.Connection = conn

            cmd.CommandText = "add_emp"
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.AddWithValue("@lname", "Jones")
            cmd.Parameters("@lname").Direction = ParameterDirection.Input

            cmd.Parameters.AddWithValue("@fname", "Tom")
            cmd.Parameters("@fname").Direction = ParameterDirection.Input

            cmd.Parameters.AddWithValue("@bday", "1940-06-07")
            cmd.Parameters("@bday").Direction = ParameterDirection.Input

            cmd.Parameters.AddWithValue("@empno", MySqlDbType.Int32)
            cmd.Parameters("@empno").Direction = ParameterDirection.Output

            cmd.ExecuteNonQuery()

            Console.WriteLine("Employee number: " & cmd.Parameters("@empno").Value)
            Console.WriteLine("Birthday: " & cmd.Parameters("@bday").Value)
        Catch ex As MySql.Data.MySqlClient.MySqlException
            Console.WriteLine(("Error " & ex.Number & " has occurred: ") + ex.Message)
        End Try
        conn.Close()
        Console.WriteLine("Done.")

    End Sub

End Module

5.9 Handling BLOB Data With Connector/NET

One common use for MySQL is the storage of binary data in BLOB columns. MySQL supports four different BLOB data types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB, all described in The BLOB and TEXT Types and Data Type Storage Requirements.

Data stored in a BLOB column can be accessed using MySQL Connector/NET and manipulated using client-side code. There are no special requirements for using Connector/NET with BLOB data.

Simple code examples will be presented within this section, and a full sample application can be found in the Samples directory of the Connector/NET installation.

5.9.1 Preparing the MySQL Server

The first step is using MySQL with BLOB data is to configure the server. Let's start by creating a table to be accessed. In my file tables, I usually have four columns: an AUTO_INCREMENT column of appropriate size (UNSIGNED SMALLINT) to serve as a primary key to identify the file, a VARCHAR column that stores the file name, an UNSIGNED MEDIUMINT column that stores the size of the file, and a MEDIUMBLOB column that stores the file itself. For this example, I will use the following table definition:

CREATE TABLE file(
file_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
file_name VARCHAR(64) NOT NULL,
file_size MEDIUMINT UNSIGNED NOT NULL,
file MEDIUMBLOB NOT NULL);

After creating a table, you might need to modify the max_allowed_packet system variable. This variable determines how large of a packet (that is, a single row) can be sent to the MySQL server. By default, the server only accepts a maximum size of 1MB from the client application. If you intend to exceed 1MB in your file transfers, increase this number.

The max_allowed_packet option can be modified using the MySQL Workbench Server Administration screen. Adjust the Maximum permitted option in the Data / Memory size section of the Networking tab to an appropriate setting. After adjusting the value, click the Apply button and restart the server using the Startup / Shutdown screen of MySQL Workbench. You can also adjust this value directly in the my.cnf file (add a line that reads max_allowed_packet=xxM), or use the SET max_allowed_packet=xxM; syntax from within MySQL.

Try to be conservative when setting max_allowed_packet, as transfers of BLOB data can take some time to complete. Try to set a value that will be adequate for your intended use and increase the value if necessary.

5.9.2 Writing a File to the Database

To write a file to a database, we need to convert the file to a byte array, then use the byte array as a parameter to an INSERT query.

The following code opens a file using a FileStream object, reads it into a byte array, and inserts it into the file table:

Visual Basic Example

Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand

Dim SQL As String

Dim FileSize As UInt32
Dim rawData() As Byte
Dim fs As FileStream

conn.ConnectionString = "server=127.0.0.1;" _
    & "uid=root;" _
    & "pwd=12345;" _
    & "database=test"

Try
    fs = New FileStream("c:\image.png", FileMode.Open, FileAccess.Read)
    FileSize = fs.Length

    rawData = New Byte(FileSize) {}
    fs.Read(rawData, 0, FileSize)
    fs.Close()

    conn.Open()

    SQL = "INSERT INTO file VALUES(NULL, @FileName, @FileSize, @File)"

    cmd.Connection = conn
    cmd.CommandText = SQL
    cmd.Parameters.AddWithValue("@FileName", strFileName)
    cmd.Parameters.AddWithValue("@FileSize", FileSize)
    cmd.Parameters.AddWithValue("@File", rawData)

    cmd.ExecuteNonQuery()

    MessageBox.Show("File Inserted into database successfully!", _
    "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

    conn.Close()
Catch ex As Exception
    MessageBox.Show("There was an error: " & ex.Message, "Error", _
        MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

C# Example

MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();

string SQL;
UInt32 FileSize;
byte[] rawData;
FileStream fs;

conn.ConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test";

try
{
    fs = new FileStream(@"c:\image.png", FileMode.Open, FileAccess.Read);
    FileSize = fs.Length;

    rawData = new byte[FileSize];
    fs.Read(rawData, 0, FileSize);
    fs.Close();

    conn.Open();

    SQL = "INSERT INTO file VALUES(NULL, @FileName, @FileSize, @File)";

    cmd.Connection = conn;
    cmd.CommandText = SQL;
    cmd.Parameters.AddWithValue("@FileName", strFileName);
    cmd.Parameters.AddWithValue("@FileSize", FileSize);
    cmd.Parameters.AddWithValue("@File", rawData);

    cmd.ExecuteNonQuery();

    MessageBox.Show("File Inserted into database successfully!",
        "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);

    conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
        "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

The Read method of the FileStream object is used to load the file into a byte array which is sized according to the Length property of the FileStream object.

After assigning the byte array as a parameter of the MySqlCommand object, the ExecuteNonQuery method is called and the BLOB is inserted into the file table.

5.9.3 Reading a BLOB from the Database to a File on Disk

After a file is loaded into the file table, we can use the MySqlDataReader class to retrieve it.

The following code retrieves a row from the file table, then loads the data into a FileStream object to be written to disk:

Visual Basic Example

Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
Dim myData As MySqlDataReader
Dim SQL As String
Dim rawData() As Byte
Dim FileSize As UInt32
Dim fs As FileStream

conn.ConnectionString = "server=127.0.0.1;" _
    & "uid=root;" _
    & "pwd=12345;" _
    & "database=test"

SQL = "SELECT file_name, file_size, file FROM file"

Try
    conn.Open()

    cmd.Connection = conn
    cmd.CommandText = SQL

    myData = cmd.ExecuteReader

    If Not myData.HasRows Then Throw New Exception("There are no BLOBs to save")

    myData.Read()

    FileSize = myData.GetUInt32(myData.GetOrdinal("file_size"))
    rawData = New Byte(FileSize) {}

    myData.GetBytes(myData.GetOrdinal("file"), 0, rawData, 0, FileSize)

    fs = New FileStream("C:\newfile.png", FileMode.OpenOrCreate, FileAccess.Write)
    fs.Write(rawData, 0, FileSize)
    fs.Close()

    MessageBox.Show("File successfully written to disk!", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

    myData.Close()
    conn.Close()
Catch ex As Exception
    MessageBox.Show("There was an error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

C# Example

MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
MySql.Data.MySqlClient.MySqlDataReader myData;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();

string SQL;
UInt32 FileSize;
byte[] rawData;
FileStream fs;

conn.ConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test";

SQL = "SELECT file_name, file_size, file FROM file";

try
{
    conn.Open();

    cmd.Connection = conn;
    cmd.CommandText = SQL;

    myData = cmd.ExecuteReader();

    if (! myData.HasRows)
        throw new Exception("There are no BLOBs to save");

    myData.Read();

    FileSize = myData.GetUInt32(myData.GetOrdinal("file_size"));
    rawData = new byte[FileSize];

    myData.GetBytes(myData.GetOrdinal("file"), 0, rawData, 0, (int)FileSize);

    fs = new FileStream(@"C:\newfile.png", FileMode.OpenOrCreate, FileAccess.Write);
    fs.Write(rawData, 0, (int)FileSize);
    fs.Close();

    MessageBox.Show("File successfully written to disk!",
        "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);

    myData.Close();
    conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
        "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

After connecting, the contents of the file table are loaded into a MySqlDataReader object. The GetBytes method of the MySqlDataReader is used to load the BLOB into a byte array, which is then written to disk using a FileStream object.

The GetOrdinal method of the MySqlDataReader can be used to determine the integer index of a named column. Use of the GetOrdinal method prevents errors if the column order of the SELECT query is changed.

5.10 Asynchronous Methods

The Task-based Asynchronous Pattern (TAP) is a pattern for asynchrony in the .NET Framework. It is based on the Task and Task<TResult> types in the System.Threading.Tasks namespace, which are used to represent arbitrary asynchronous operations.

Async-Await are new keywords introduced to work with the TAP. The Async modifier is used to specify that a method, lambda expression, or anonymous method is asynchronous. The Await operator is applied to a task in an asynchronous method to suspend the execution of the method until the awaited task completes.

Requirements

  • Async-Await support requires .NET Framework 4.5 or later

  • TAP support requires .NET Framework 4.0 or later

  • MySQL Connector/NET 6.9 or later

Methods

The following methods can be used with either TAP or Async-Await.

  • Namespace MySql.Data.Entity

    • Class EFMySqlCommand

      • Task PrepareAsync()

      • Task PrepareAsync(CancellationToken)

  • Namespace MySql.Data

    • Class MySqlBulkLoader

      • Task<int> LoadAsync()

      • Task<int> LoadAsync(CancellationToken

    • Class MySqlConnection

      • Task<MySqlTransaction> BeginTransactionAsync()

      • Task<MySqlTransaction> BeginTransactionAsync (CancellationToken)

      • Task<MySqlTransaction> BeginTransactionAsync(IsolationLevel)

      • Task<MySqlTransaction> BeginTransactionAsync (IsolationLevel , CancellationToken)

      • Task ChangeDatabaseAsync(string)

      • Task ChangeDatabaseAsync(string, CancellationToken)

      • Task CloseAsync()

      • Task CloseAsync(CancellationToken)

      • Task ClearPoolAsync(MySqlConnection)

      • Task ClearPoolAsync(MySqlConnection, CancellationToken)

      • Task ClearAllPoolsAsync()

      • Task ClearAllPoolsAsync(CancellationToken)

      • Task<MySqlSchemaCollection> GetSchemaCollection(string, string[])

      • Task<MySqlSchemaCollection> GetSchemaCollection(string, string[], CancellationToken)

    • Class MySqlDataAdapter

      • Task<int> FillAsync(DataSet)

      • Task<int> FillAsync(DataSet, CancellationToken)

      • Task<int> FillAsync(DataTable)

      • Task<int> FillAsync(DataTable, CancellationToken)

      • Task<int> FillAsync(DataSet, string)

      • Task<int> FillAsync(DataSet, string, CancellationToken)

      • Task<int> FillAsync(DataTable, IDataReader)

      • Task<int> FillAsync(DataTable, IDataReader, CancellationToken)

      • Task<int> FillAsync(DataTable, IDbCommand, CommandBehavior)

      • Task<int> FillAsync(DataTable, IDbCommand, CommandBehavior, CancellationToken)

      • Task<int> FillAsync(int, int, params DataTable[])

      • Task<int> FillAsync(int, int, params DataTable[], CancellationToken)

      • Task<int> FillAsync(DataSet, int, int, string)

      • Task<int> FillAsync(DataSet, int, int, string, CancellationToken)

      • Task<int> FillAsync(DataSet, string, IDataReader, int, int)

      • Task<int> FillAsync(DataSet, string, IDataReader, int, int, CancellationToken)

      • Task<int> FillAsync(DataTable[], int, int, IDbCommand, CommandBehavior)

      • Task<int> FillAsync(DataTable[], int, int, IDbCommand, CommandBehavior, CancellationToken)

      • Task<int> FillAsync(DataSet, int, int, string, IDbCommand, CommandBehavior)

      • Task<int> FillAsync(DataSet, int, int, string, IDbCommand, CommandBehavior, CancellationToken)

      • Task<DataTable[]> FillSchemaAsync(DataSet, SchemaType)

      • Task<DataTable[]> FillSchemaAsync(DataSet, SchemaType, CancellationToken)

      • Task<DataTable[]> FillSchemaAsync(DataSet, SchemaType, string)

      • Task<DataTable[]> FillSchemaAsync(DataSet, SchemaType, string, CancellationToken)

      • Task<DataTable[]> FillSchemaAsync(DataSet, SchemaType, string, IDataReader)

      • Task<DataTable[]> FillSchemaAsync(DataSet, SchemaType, string, IDataReader, CancellationToken)

      • Task<DataTable[]> FillSchemaAsync(DataSet, SchemaType, IDbCommand, string, CommandBehavior)

      • Task<DataTable[]> FillSchemaAsync(DataSet, SchemaType, IDbCommand, string, CommandBehavior, CancellationToken)

      • Task<DataTable> FillSchemaAsync(DataTable, SchemaType)

      • Task<DataTable> FillSchemaAsync(DataTable, SchemaType, CancellationToken)

      • Task<DataTable> FillSchemaAsync(DataTable, SchemaType, IDataReader)

      • Task<DataTable> FillSchemaAsync(DataTable, SchemaType, IDataReader, CancellationToken)

      • Task<DataTable> FillSchemaAsync(DataTable, SchemaType, IDbCommand, CommandBehavior)

      • Task<DataTable> FillSchemaAsync(DataTable, SchemaType, IDbCommand, CommandBehavior, CancellationToken)

      • Task<int> UpdateAsync(DataRow[])

      • Task<int> UpdateAsync(DataRow[], CancellationToken)

      • Task<int> UpdateAsync(DataSet)

      • Task<int> UpdateAsync(DataSet, CancellationToken)

      • Task<int> UpdateAsync(DataTable)

      • Task<int> UpdateAsync(DataTable, CancellationToken)

      • Task<int> UpdateAsync(DataRow[], DataTableMapping, CancellationToken)

      • Task<int> UpdateAsync(DataSet, string)

      • Task<int> UpdateAsync(DataSet, string, CancellationToken)

    • Class MySqlHelper

      • Task<DataRow> ExecuteDataRowAsync(string, string, params MySqlParameter[])

      • Task<DataRow> ExecuteDataRowAsync(string, string, CancellationToken, params MySqlParameter[])

      • Task<int> ExecuteNonQueryAsync(MySqlConnection, string, params MySqlParameter[])

      • Task<int> ExecuteNonQueryAsync(MySqlConnection, string, CancellationToken, params MySqlParameter[])

      • Task<int> ExecuteNonQueryAsync(string, string, params MySqlParameter[])

      • Task<int> ExecuteNonQueryAsync(string, string, CancellationToken, params MySqlParameter[])

      • Task<DataSet> ExecuteDatasetAsync(string, string)

      • Task<DataSet> ExecuteDatasetAsync(string, string, CancellationToken)

      • Task<DataSet> ExecuteDatasetAsync(string, string, CancellationToken, params MySqlParameter[])

      • Task<DataSet> ExecuteDatasetAsync(MySqlConnection, string)

      • Task<DataSet> ExecuteDatasetAsync(MySqlConnection, string, CancellationToken)

      • Task<DataSet> ExecuteDatasetAsync(MySqlConnection, string, params MySqlParameter[])

      • Task<DataSet> ExecuteDatasetAsync(MySqlConnection, string, CancellationToken, params MySqlParameter[])

      • Task UpdateDataSetAsync(string, string, DataSet, string)

      • Task UpdateDataSetAsync(string, string, DataSet, string, CancellationToken)

      • Task<MySqlDataReader> ExecuteReaderAsync(MySqlConnection, MySqlTransaction, string, MySqlParameter[], bool)

      • Task<MySqlDataReader> ExecuteReaderAsync(MySqlConnection, MySqlTransaction, string, MySqlParameter[], bool, CancellationToken)

      • Task<MySqlDataReader> ExecuteReaderAsync(string, string)

      • Task<MySqlDataReader> ExecuteReaderAsync(string, string, CancellationToken)

      • Task<MySqlDataReader> ExecuteReaderAsync(MySqlConnection, string)

      • Task<MySqlDataReader> ExecuteReaderAsync(MySqlConnection, string, CancellationToken)

      • Task<MySqlDataReader> ExecuteReaderAsync(string, string, params MySqlParameter[])

      • Task<MySqlDataReader> ExecuteReaderAsync(string, string, CancellationToken, params MySqlParameter[])

      • Task<MySqlDataReader> ExecuteReaderAsync(MySqlConnection, string, params MySqlParameter[])

      • Task<MySqlDataReader> ExecuteReaderAsync(MySqlConnection, string, CancellationToken, params MySqlParameter[])

      • Task<object> ExecuteScalarAsync(string, string)

      • Task<object> ExecuteScalarAsync(string, string, CancellationToken)

      • Task<object> ExecuteScalarAsync(string, string, params MySqlParameter[])

      • Task<object> ExecuteScalarAsync(string, string, CancellationToken, params MySqlParameter[])

      • Task<object> ExecuteScalarAsync(MySqlConnection, string)

      • Task<object> ExecuteScalarAsync(MySqlConnection, string, CancellationToken)

      • Task<object> ExecuteScalarAsync(MySqlConnection, string, params MySqlParameter[])

      • Task<object> ExecuteScalarAsync(MySqlConnection, string, CancellationToken, params MySqlParameter[])

    • Class MySqlScript

      • Task<int> ExecuteAsync()

      • Task<int> ExecuteAsync(CancellationToken)

In addition to the methods listed above, the following are methods inherited from the .NET Framework:

  • Namespace MySql.Data.Entity

    • Class EFMySqlCommand

      • Task<DbDataReader> ExecuteDbDataReaderAsync(CommandBehaviour, CancellationToken)

      • Task<int> ExecuteNonQueryAsync()

      • Task<int> ExecuteNonQueryAsync(CancellationToken)

      • Task<DbDataReader> ExecuteReaderAsync()

      • Task<DbDataReader> ExecuteReaderAsync(CancellationToken)

      • Task<DbDataReader> ExecuteReaderAsync(CommandBehaviour)

      • Task<DbDataReader> ExecuteReaderAsync(CommandBehaviour, CancellationToken)

      • Task<object> ExecuteScalarAsync()

      • Task<object> ExecuteScalarAsync(CancellationToken)

  • Namespace MySql.Data

    • Class MySqlCommand

      • Task<DbDataReader> ExecuteDbDataReaderAsync(CommandBehaviour, CancellationToken)

      • Task<int> ExecuteNonQueryAsync()

      • Task<int> ExecuteNonQueryAsync(CancellationToken)

      • Task<DbDataReader> ExecuteReaderAsync()

      • Task<DbDataReader> ExecuteReaderAsync(CancellationToken)

      • Task<DbDataReader> ExecuteReaderAsync(CommandBehaviour)

      • Task<DbDataReader> ExecuteReaderAsync(CommandBehaviour, CancellationToken)

      • Task<object> ExecuteScalarAsync()

      • Task<object> ExecuteScalarAsync(CancellationToken)

    • Class MySqlConnection

      • Task OpenAsync()

      • Task OpenAsync(CancellationToken)

    • Class MySqlDataReader

      • Task<T> GetFieldValueAsync<T>(int)

      • Task<T> GetFieldValueAsync<T>(int, CancellationToken)

      • Task<bool> IsDBNullAsync(int)

      • Task<bool> IsDBNullAsync(int, CancellationToken)

      • Task<bool> NextResultAsync()

      • Task<bool> NextResultAsync(CancellationToken)

      • Task<bool> ReadAsync()

      • Task<bool> ReadAsync(CancellationToken)

Examples

The following examples demonstrate how to use the asynchronous methods:

In this example, a method has the async modifier because the method await call made applies to the method LoadAsync. The method returns a Task object that contains information about the result of the awaited method. Returning Task is like having a void method, but you should not use async void if your method is not a top-level access method like an event.


public async Task BulkLoadAsync()
{
  MySqlConnection myConn = new MySqlConnection("MyConnectionString");
  MySqlBulkLoader loader = new MySqlBulkLoader(myConn);

  loader.TableName       = "BulkLoadTest";
  loader.FileName        = @"c:\MyPath\MyFile.txt";
  loader.Timeout         = 0;

  var result             = await loader.LoadAsync();

}

In this example, an "async void" method is used with "await" for the ExecuteNonQueryAsync method, to correspond to the onclick event of a button. This is why the method does not return a Task.


private async void myButton_Click()
{
  MySqlConnection myConn = new MySqlConnection("MyConnectionString");
  MySqlCommand proc      = new MySqlCommand("MyAsyncSpTest", myConn);

  proc.CommandType       = CommandType.StoredProcedure;

  int result             = await proc.ExecuteNonQueryAsync();
}

5.11 Using the Connector/NET Interceptor Classes

An interceptor is a software design pattern that provides a transparent way to extend or modify some aspect of a program, similar to a user exit. No recompiling is required. With MySQL Connector/NET, the interceptors are enabled and disabled by updating the connection string to refer to different sets of interceptor classes that you instantiate.

Note

The classes and methods presented in this section do not apply to Connector/NET applications developed with the .NET Core 1.1 framework.

Connector/NET includes the following interceptor classes:

  • The BaseCommandInterceptor lets you perform additional operations when a program issues a SQL command. For example, you can examine the SQL statement for logging or debugging purposes, substitute your own result set to implement a caching mechanism, and so on. Depending on the use case, your code can supplement the SQL command or replace it entirely.

    The BaseCommandInterceptor class has these methods that you can override:

    public virtual bool ExecuteScalar(string sql, ref object returnValue);
    public virtual bool ExecuteNonQuery(string sql, ref int returnValue);
    public virtual bool ExecuteReader(string sql, CommandBehavior behavior, ref MySqlDataReader returnValue);
    public virtual void Init(MySqlConnection connection);
    

    If your interceptor overrides one of the Execute... methods, set the returnValue output parameter and return true if you handled the event, or false if you did not handle the event. The SQL command is processed normally only when all command interceptors return false.

    The connection passed to the Init method is the connection that is attached to this interceptor.

  • The BaseExceptionInterceptor lets you perform additional operations when a program encounters an SQL exception. The exception interception mechanism is modeled after the Connector/J model. You can code an interceptor class and connect it to an existing program without recompiling, and intercept exceptions when they are created. You can then change the exception type and optionally attach information to it. This capability lets you turn on and off logging and debugging code without hardcoding anything in the application. This technique applies to exceptions raised at the SQL level, not to lower-level system or I/O errors.

    You develop an exception interceptor first by creating a subclass of the BaseExceptionInterceptor class. You must override the InterceptException() method. You can also override the Init() method to do some one-time initialization.

    Each exception interceptor has 2 methods:

    public abstract Exception InterceptException(Exception exception,
      MySqlConnection connection);
    public virtual void Init(MySqlConnection connection);
    

    The connection passed to Init() is the connection that is attached to this interceptor.

    Each interceptor is required to override InterceptException and return an exception. It can return the exception it is given, or it can wrap it in a new exception. We currently do not offer the ability to suppress the exception.

Here are examples of using the FQN (fully qualified name) on the connection string:

MySqlConnection c1 = new MySqlConnection(@"server=localhost;pooling=false;
commandinterceptors=CommandApp.MyCommandInterceptor,CommandApp");

MySqlConnection c2 = new MySqlConnection(@"server=localhost;pooling=false;
exceptioninterceptors=ExceptionStackTraceTest.MyExceptionInterceptor,ExceptionStackTraceTest");

In this example, the command interceptor is called CommandApp.MyCommandInterceptor and exists in the CommandApp assembly. The exception interceptor is called ExceptionStackTraceTest.MyExceptionInterceptor and exists in the ExceptionStackTraceTest assembly.

To shorten the connection string, you can register your exception interceptors in your app.config or web.config file like this:


<configSections>
<section name="MySQL" type="MySql.Data.MySqlClient.MySqlConfiguration,
MySql.Data"/>
</configSections>
<MySQL>
<CommandInterceptors>
  <add name="myC" type="CommandApp.MyCommandInterceptor,CommandApp" />
</CommandInterceptors>
</MySQL>

<configSections>
<section name="MySQL" type="MySql.Data.MySqlClient.MySqlConfiguration,
MySql.Data"/>
</configSections>
<MySQL>
<ExceptionInterceptors>
  <add name="myE"
type="ExceptionStackTraceTest.MyExceptionInterceptor,ExceptionStackTraceTest" />
</ExceptionInterceptors>
</MySQL>


After you have done that, your connection strings can look like these:

MySqlConnection c1 = new MySqlConnection(@"server=localhost;pooling=false;
commandinterceptors=myC");

MySqlConnection c2 = new MySqlConnection(@"server=localhost;pooling=false;
exceptioninterceptors=myE");

5.12 Handling Date and Time Information in Connector/NET

MySQL and the .NET languages handle date and time information differently, with MySQL allowing dates that cannot be represented by a .NET data type, such as '0000-00-00 00:00:00'. These differences can cause problems if not properly handled.

The following sections demonstrate how to properly handle date and time information when using MySQL Connector/NET.

5.12.1 Fractional Seconds

MySQL Connector/NET 6.5 and higher support the fractional seconds feature in MySQL, where the fractional seconds part of temporal values is preserved in data stored and retrieved through SQL. For fractional second handling in MySQL 5.6.4 and higher, see Fractional Seconds in Time Values.

To use the more precise date and time types, specify a value from 1 to 6 when creating the table column, for example TIME(3) or DATETIME(6), representing the number of digits of precision after the decimal point. Specifying a precision of 0 leaves the fractional part out entirely. In your C# or Visual Basic code, refer to the Millisecond member to retrieve the fractional second value from the MySqlDateTime object returned by the GetMySqlDateTime function. The DateTime object returned by the GetDateTime function also contains the fractional value, but only the first 3 digits.

For related code examples, see the following blog post: https://blogs.oracle.com/MySqlOnWindows/entry/milliseconds_value_support_on_datetime

5.12.2 Problems when Using Invalid Dates

The differences in date handling can cause problems for developers who use invalid dates. Invalid MySQL dates cannot be loaded into native .NET DateTime objects, including NULL dates.

Because of this issue, .NET DataSet objects cannot be populated by the Fill method of the MySqlDataAdapter class as invalid dates will cause a System.ArgumentOutOfRangeException exception to occur.

5.12.3 Restricting Invalid Dates

The best solution to the date problem is to restrict users from entering invalid dates. This can be done on either the client or the server side.

Restricting invalid dates on the client side is as simple as always using the .NET DateTime class to handle dates. The DateTime class will only allow valid dates, ensuring that the values in your database are also valid. The disadvantage of this is that it is not useful in a mixed environment where .NET and non .NET code are used to manipulate the database, as each application must perform its own date validation.

Users of MySQL 5.0.2 and higher can use the new traditional SQL mode to restrict invalid date values. For information on using the traditional SQL mode, see Server SQL Modes.

5.12.4 Handling Invalid Dates

Although it is strongly recommended that you avoid the use of invalid dates within your .NET application, it is possible to use invalid dates by means of the MySqlDateTime data type.

The MySqlDateTime data type supports the same date values that are supported by the MySQL server. The default behavior of Connector/NET is to return a .NET DateTime object for valid date values, and return an error for invalid dates. This default can be modified to cause Connector/NET to return MySqlDateTime objects for invalid dates.

To instruct Connector/NET to return a MySqlDateTime object for invalid dates, add the following line to your connection string:

Allow Zero Datetime=True

The MySqlDateTime class can still be problematic. The following are some known issues:

  • Data binding for invalid dates can still cause errors (zero dates like 0000-00-00 do not seem to have this problem).

  • The ToString method return a date formatted in the standard MySQL format (for example, 2005-02-23 08:50:25). This differs from the ToString behavior of the .NET DateTime class.

  • The MySqlDateTime class supports NULL dates, while the .NET DateTime class does not. This can cause errors when trying to convert a MySQLDateTime to a DateTime if you do not check for NULL first.

Because of the known issues, the best recommendation is still to use only valid dates in your application.

5.12.5 Handling NULL Dates

The .NET DateTime data type cannot handle NULL values. As such, when assigning values from a query to a DateTime variable, you must first check whether the value is in fact NULL.

When using a MySqlDataReader, use the .IsDBNull method to check whether a value is NULL before making the assignment:

Visual Basic Example

If Not myReader.IsDBNull(myReader.GetOrdinal("mytime")) Then
    myTime = myReader.GetDateTime(myReader.GetOrdinal("mytime"))
Else
    myTime = DateTime.MinValue
End If

C# Example

if (! myReader.IsDBNull(myReader.GetOrdinal("mytime")))
    myTime = myReader.GetDateTime(myReader.GetOrdinal("mytime"));
else
    myTime = DateTime.MinValue;

NULL values will work in a data set and can be bound to form controls without special handling.

5.13 Using the MySqlBulkLoader Class

MySQL Connector/NET features a bulk loader class that wraps the MySQL statement LOAD DATA INFILE. This gives Connector/NET the ability to load a data file from a local or remote host to the server. The class concerned is MySqlBulkLoader. This class has various methods, the main one being load to cause the specified file to be loaded to the server. Various parameters can be set to control how the data file is processed. This is achieved through setting various properties of the class. For example, the field separator used, such as comma or tab, can be specified, along with the record terminator, such as newline.

The following code shows a simple example of using the MySqlBulkLoader class. First an empty table needs to be created, in this case in the test database:

CREATE TABLE Career (
       Name VARCHAR(100) NOT NULL,
       Age INTEGER,
       Profession VARCHAR(200)
);

A simple tab-delimited data file is also created (it could use any other field delimiter such as comma):

Table Career in Test Database
Name  Age  Profession

Tony  47  Technical Writer
Ana  43  Nurse
Fred  21  IT Specialist
Simon  45  Hairy Biker

The first three lines need to be ignored with this test file, as they do not contain table data. This can be achieved using the NumberOfLinesToSkip property. This file can then be loaded and used to populate the Career table in the test database:

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            string connStr = "server=localhost;user=root;database=test;port=3306;password=******";
            MySqlConnection conn = new MySqlConnection(connStr);

            MySqlBulkLoader bl = new MySqlBulkLoader(conn);
            bl.TableName = "Career";
            bl.FieldTerminator = "\t";
            bl.LineTerminator = "\n";
            bl.FileName = "c:/career_data.txt";
            bl.NumberOfLinesToSkip = 3;

            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();

                // Upload data from file
                int count = bl.Load();
                Console.WriteLine(count + " lines uploaded.");

                string sql = "SELECT Name, Age, Profession FROM Career";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    Console.WriteLine(rdr[0] + " -- " + rdr[1] + " -- " + rdr[2]);
                }

                rdr.Close();

                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Console.WriteLine("Done.");
        }
    }
}

Further information on LOAD DATA INFILE can be found in LOAD DATA INFILE Syntax. Further information on MySqlBulkLoader can be found in the reference documentation that was included with your connector.

5.14 Using the Connector/NET Trace Source Object

MySQL Connector/NET 6.2 introduced support for .NET 2.0 compatible tracing, using TraceSource objects.

The .NET 2.0 tracing architecture consists of four main parts:

  • Source - This is the originator of the trace information. The source is used to send trace messages. The name of the source provided by Connector/NET is mysql.

  • Switch - This defines the level of trace information to emit. Typically, this is specified in the app.config file, so that it is not necessary to recompile an application to change the trace level.

  • Listener - Trace listeners define where the trace information will be written to. Supported listeners include, for example, the Visual Studio Output window, the Windows Event Log, and the console.

  • Filter - Filters can be attached to listeners. Filters determine the level of trace information that will be written. While a switch defines the level of information that will be written to all listeners, a filter can be applied on a per-listener basis, giving finer grained control of trace information.

To use tracing MySql.Data.MySqlClient.MySqlTrace can be used as a TraceSource for Connector/NET and the connection string must include "Logging=True".

To enable trace messages, configure a trace switch. Trace switches have associated with them a trace level enumeration, these are Off, Error, Warning, Info, and Verbose.

MySqlTrace.Switch.Level = SourceLevels.Verbose;

This sets the trace level to Verbose, meaning that all trace messages will be written.

It is convenient to be able to change the trace level without having to recompile the code. This is achieved by specifying the trace level in application configuration file, app.config. You then simply need to specify the desired trace level in the configuration file and restart the application. The trace source is configured within the system.diagnostics section of the file. The following XML snippet illustrates this:


<configuration>
  ...
  <system.diagnostics>
    <sources>
      <source name="mysql" switchName="MySwitch"
              switchType="System.Diagnostics.SourceSwitch" />
      ...
    </sources>
    <switches>
      <add name="MySwitch" value="Verbose"/>
      ...
    </switches>
  </system.diagnostics>
  ...
</configuration>

By default, trace information is written to the Output window of Microsoft Visual Studio. There are a wide range of listeners that can be attached to the trace source, so that trace messages can be written out to various destinations. You can also create custom listeners to allow trace messages to be written to other destinations as mobile devices and web services. A commonly used example of a listener is ConsoleTraceListener, which writes trace messages to the console.

To add a listener at runtime, use code such as the following:

ts.Listeners.Add(new ConsoleTraceListener());

Then, call methods on the trace source object to generate trace information. For example, the TraceInformation(), TraceEvent(), or TraceData() methods can be used.

5.14.1 Viewing MySQL Trace Information

This section describes how to set up your application to view MySQL trace information.

The first thing you need to do is create a suitable app.config file for your application. An example is shown in the following code:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.diagnostics>
    <sources>
      <source name="mysql" switchName="SourceSwitch"
        switchType="System.Diagnostics.SourceSwitch" >
        <listeners>
          <add name="console" />
          <remove name ="Default" />
        </listeners>
      </source>
    </sources>
    <switches>
      <!-- You can set the level at which tracing is to occur -->
      <add name="SourceSwitch" value="Verbose" />
      <!-- You can turn tracing off -->
      <!--add name="SourceSwitch" value="Off" -->
    </switches>
    <sharedListeners>
      <add name="console"
        type="System.Diagnostics.ConsoleTraceListener"
        initializeData="false"/>
    </sharedListeners>
  </system.diagnostics>
</configuration>

This ensures a suitable trace source is created, along with a switch. The switch level in this case is set to Verbose to display the maximum amount of information.

In the application the only other step required is to add logging=true to the connection string. An example application could be:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using MySql.Data;
using MySql.Data.MySqlClient;
using MySql.Web;


namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            string connStr = "server=localhost;user=root;database=world;port=3306;password=******;logging=true";
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();

                string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent='Oceania'";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    Console.WriteLine(rdr[0] + " -- " + rdr[1]);
                }

                rdr.Close();

                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Console.WriteLine("Done.");
        }
    }
}

This simple application will then generate the following output:

Connecting to MySQL...
mysql Information: 1 : 1: Connection Opened: connection string = 'server=localhost;User Id=root;database=world;port=3306
;password=******;logging=True'
mysql Information: 3 : 1: Query Opened: SHOW VARIABLES
mysql Information: 4 : 1: Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=272, skipped rows=0, size (bytes)=7058
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SHOW COLLATION
mysql Information: 4 : 1: Resultset Opened: field(s) = 6, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=127, skipped rows=0, size (bytes)=4102
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SET character_set_results=NULL
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 10 : 1: Set Database: world
mysql Information: 3 : 1: Query Opened: SELECT Name, HeadOfState FROM Country WHERE Continent='Oceania'
mysql Information: 4 : 1: Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1
American Samoa -- George W. Bush
Australia -- Elisabeth II
...
Wallis and Futuna -- Jacques Chirac
Vanuatu -- John Bani
United States Minor Outlying Islands -- George W. Bush
mysql Information: 5 : 1: Resultset Closed. Total rows=28, skipped rows=0, size (bytes)=788
mysql Information: 6 : 1: Query Closed
Done.
mysql Information: 2 : 1: Connection Closed

The first number displayed in the trace message corresponds to the MySQL event type:

EventDescription
1ConnectionOpened: connection string
2ConnectionClosed:
3QueryOpened: mysql server thread id, query text
4ResultOpened: field count, affected rows (-1 if select), inserted id (-1 if select)
5ResultClosed: total rows read, rows skipped, size of resultset in bytes
6QueryClosed:
7StatementPrepared: prepared sql, statement id
8StatementExecuted: statement id, mysql server thread id
9StatementClosed: statement id
10NonQuery: [varies]
11UsageAdvisorWarning: usage advisor flag. NoIndex = 1, BadIndex = 2, SkippedRows = 3, SkippedColumns = 4, FieldConversion = 5.
12Warning: level, code, message
13Error: error number, error message

The second number displayed in the trace message is the connection count.

Although this example uses the ConsoleTraceListener, any of the other standard listeners could have been used. Another possibility is to create a custom listener that uses the information passed using the TraceEvent method. For example, a custom trace listener could be created to perform active monitoring of the MySQL event messages, rather than simply writing these to an output device.

It is also possible to add listeners to the MySQL Trace Source at runtime. This can be done with the following code:

MySqlTrace.Listeners.Add(new ConsoleTraceListener());

Connector/NET 6.3.2 introduced the ability to switch tracing on and off at runtime. This can be achieved using the calls MySqlTrace.EnableQueryAnalyzer(string host, int postInterval) and MySqlTrace.DisableQueryAnalyzer(). The parameter host is the URL of the MySQL Enterprise Monitor server to monitor. The parameter postInterval is how often to post the data to MySQL Enterprise Monitor, in seconds.

5.14.2 Building Custom Listeners

To build custom listeners that work with the MySQL Connector/NET Trace Source, it is necessary to understand the key methods used, and the event data formats used.

The main method involved in passing trace messages is the TraceSource.TraceEvent method. This has the prototype:

public void TraceEvent(
    TraceEventType eventType,
    int id,
    string format,
    params Object[] args
)

This trace source method will process the list of attached listeners and call the listener's TraceListener.TraceEvent method. The prototype for the TraceListener.TraceEvent method is as follows:

public virtual void TraceEvent(
    TraceEventCache eventCache,
    string source,
    TraceEventType eventType,
    int id,
    string format,
    params Object[] args
)

The first three parameters are used in the standard as defined by Microsoft. The last three parameters contain MySQL-specific trace information. Each of these parameters is now discussed in more detail.

int id

This is a MySQL-specific identifier. It identifies the MySQL event type that has occurred, resulting in a trace message being generated. This value is defined by the MySqlTraceEventType public enum contained in the Connector/NET code:

public enum MySqlTraceEventType : int
{
    ConnectionOpened = 1,
    ConnectionClosed,
    QueryOpened,
    ResultOpened,
    ResultClosed,
    QueryClosed,
    StatementPrepared,
    StatementExecuted,
    StatementClosed,
    NonQuery,
    UsageAdvisorWarning,
    Warning,
    Error
}

The MySQL event type also determines the contents passed using the parameter params Object[] args. The nature of the args parameters are described in further detail in the following material.

string format

This is the format string that contains zero or more format items, which correspond to objects in the args array. This would be used by a listener such as ConsoleTraceListener to write a message to the output device.

params Object[] args

This is a list of objects that depends on the MySQL event type, id. However, the first parameter passed using this list is always the driver id. The driver id is a unique number that is incremented each time the connector is opened. This enables groups of queries on the same connection to be identified. The parameters that follow driver id depend on the MySQL event id, and are as follows:

MySQL-specific event typeArguments (params Object[] args)
ConnectionOpenedConnection string
ConnectionClosedNo additional parameters
QueryOpenedmysql server thread id, query text
ResultOpenedfield count, affected rows (-1 if select), inserted id (-1 if select)
ResultClosedtotal rows read, rows skipped, size of resultset in bytes
QueryClosedNo additional parameters
StatementPreparedprepared sql, statement id
StatementExecutedstatement id, mysql server thread id
StatementClosedstatement id
NonQueryVaries
UsageAdvisorWarningusage advisor flag. NoIndex = 1, BadIndex = 2, SkippedRows = 3, SkippedColumns = 4, FieldConversion = 5.
Warninglevel, code, message
Errorerror number, error message

This information will allow you to create custom trace listeners that can actively monitor the MySQL-specific events.

5.15 Binary/Nonbinary Issues

There are certain situations where MySQL will return incorrect metadata about one or more columns. More specifically, the server will sometimes report that a column is binary when it is not and vice versa. In these situations, it becomes practically impossible for the connector to be able to correctly identify the correct metadata.

Some examples of situations that may return incorrect metadata are:

  • Execution of SHOW PROCESSLIST. Some of the columns will be returned as binary even though they only hold string data.

  • When a temporary table is used to process a resultset, some columns may be returned with incorrect binary flags.

  • Some server functions such DATE_FORMAT will incorrectly return the column as binary.

With the availability of BINARY and VARBINARY data types, it is important that we respect the metadata returned by the server. However, we are aware that some existing applications may break with this change, so we are creating a connection string option to enable or disable it. By default, Connector/NET 5.1 respects the binary flags returned by the server. You might need to make small changes to your application to accommodate this change.

In the event that the changes required to your application would be too large, adding 'respect binary flags=false' to your connection string causes the connector to use the prior behavior: any column that is marked as string, regardless of binary flags, will be returned as string. Only columns that are specifically marked as a BLOB will be returned as BLOB.

5.16 Character Set Considerations for Connector/NET

Treating Binary Blobs As UTF8

Before the introduction of 4-byte UTF-8 character set (in MySQL 5.5.3), MySQL did not support 4-byte UTF8 sequences. This makes it difficult to represent some multibyte languages such as Japanese. To try and alleviate this, MySQL Connector/NET supports a mode where binary blobs can be treated as strings.

To do this, you set the 'Treat Blobs As UTF8' connection string keyword to yes. This is all that needs to be done to enable conversion of all binary blobs to UTF8 strings. To convert only some of your BLOB columns, you can make use of the 'BlobAsUTF8IncludePattern' and'BlobAsUTF8ExcludePattern' keywords. Set these to a regular expression pattern that matches the column names to include or exclude respectively.

When the regular expression patterns both match a single column, the include pattern is applied before the exclude pattern. The result, in this case, would be that the column would be excluded. Also, be aware that this mode does not apply to columns of type BINARY or VARBINARY and also do not apply to nonbinary BLOB columns.

This mode only applies to reading strings out of MySQL. To insert 4-byte UTF8 strings into blob columns, use the .NET Encoding.GetBytes function to convert your string to a series of bytes. You can then set this byte array as a parameter for a BLOB column.

5.17 Using Connector/NET with Crystal Reports

Crystal Reports is a common tool used by Windows application developers to perform reporting and document generation. In this section we will show how to use Crystal Reports XI with MySQL and MySQL Connector/NET.

5.17.1 Creating a Data Source

When creating a report in Crystal Reports there are two options for accessing the MySQL data while designing your report.

The first option is to use Connector/ODBC as an ADO data source when designing your report. You will be able to browse your database and choose tables and fields using drag and drop to build your report. The disadvantage of this approach is that additional work must be performed within your application to produce a data set that matches the one expected by your report.

The second option is to create a data set in VB.NET and save it as XML. This XML file can then be used to design a report. This works quite well when displaying the report in your application, but is less versatile at design time because you must choose all relevant columns when creating the data set. If you forget a column you must re-create the data set before the column can be added to the report.

The following code can be used to create a data set from a query and write it to disk:

Visual Basic Example

Dim myData As New DataSet
Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter

conn.ConnectionString = "server=127.0.0.1;" _
    & "uid=root;" _
    & "pwd=12345;" _
    & "database=world"

Try
    conn.Open()
    cmd.CommandText = "SELECT city.name AS cityName, city.population AS CityPopulation, " _
        & "country.name, country.population, country.continent " _
        & "FROM country, city ORDER BY country.continent, country.name"
    cmd.Connection = conn

    myAdapter.SelectCommand = cmd
    myAdapter.Fill(myData)

    myData.WriteXml("C:\dataset.xml", XmlWriteMode.WriteSchema)
Catch ex As Exception
    MessageBox.Show(ex.Message, "Report could not be created", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

C# Example

DataSet myData = new DataSet();
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();

conn.ConnectionString = "server=127.0.0.1;uid=root;" +
  "pwd=12345;database=test";

try
{
  cmd.CommandText = "SELECT city.name AS cityName, city.population AS CityPopulation, " +
  "country.name, country.population, country.continent " +
  "FROM country, city ORDER BY country.continent, country.name";
  cmd.Connection = conn;

  myAdapter.SelectCommand = cmd;
  myAdapter.Fill(myData);

  myData.WriteXml(@"C:\dataset.xml", XmlWriteMode.WriteSchema);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
  MessageBox.Show(ex.Message, "Report could not be created",
  MessageBoxButtons.OK, MessageBoxIcon.Error);
}

The resulting XML file can be used as an ADO.NET XML datasource when designing your report.

If you choose to design your reports using Connector/ODBC, it can be downloaded from dev.mysql.com.

5.17.2 Creating the Report

For most purposes, the Standard Report wizard helps with the initial creation of a report. To start the wizard, open Crystal Reports and choose the New > Standard Report option from the File menu.

The wizard first prompts you for a data source. If you use Connector/ODBC as your data source, use the OLEDB provider for ODBC option from the OLE DB (ADO) tree instead of the ODBC (RDO) tree when choosing a data source. If using a saved data set, choose the ADO.NET (XML) option and browse to your saved data set.

The remainder of the report creation process is done automatically by the wizard.

After the report is created, choose the Report Options entry from the File menu. Un-check the Save Data With Report option. This prevents saved data from interfering with the loading of data within our application.

5.17.3 Displaying the Report

To display a report we first populate a data set with the data needed for the report, then load the report and bind it to the data set. Finally we pass the report to the crViewer control for display to the user.

The following references are needed in a project that displays a report:

  • CrystalDecisions.CrystalReports.Engine

  • CrystalDecisions.ReportSource

  • CrystalDecisions.Shared

  • CrystalDecisions.Windows.Forms

The following code assumes that you created your report using a data set saved using the code shown in Section 5.17.1, “Creating a Data Source”, and have a crViewer control on your form named myViewer.

Visual Basic Example

Imports CrystalDecisions.CrystalReports.Engine
Imports System.Data
Imports MySql.Data.MySqlClient

Dim myReport As New ReportDocument
Dim myData As New DataSet
Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter

conn.ConnectionString = _
    "server=127.0.0.1;" _
    & "uid=root;" _
    & "pwd=12345;" _
    & "database=test"

Try
    conn.Open()

    cmd.CommandText = "SELECT city.name AS cityName, city.population AS CityPopulation, " _
        & "country.name, country.population, country.continent " _
        & "FROM country, city ORDER BY country.continent, country.name"
    cmd.Connection = conn

    myAdapter.SelectCommand = cmd
    myAdapter.Fill(myData)

    myReport.Load(".\world_report.rpt")
    myReport.SetDataSource(myData)
    myViewer.ReportSource = myReport
Catch ex As Exception
    MessageBox.Show(ex.Message, "Report could not be created", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

C# Example

using CrystalDecisions.CrystalReports.Engine;
using System.Data;
using MySql.Data.MySqlClient;

ReportDocument myReport = new ReportDocument();
DataSet myData = new DataSet();
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();

conn.ConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test";

try
{
    cmd.CommandText = "SELECT city.name AS cityName, city.population AS CityPopulation, " +
        "country.name, country.population, country.continent " +
        "FROM country, city ORDER BY country.continent, country.name";
    cmd.Connection = conn;

    myAdapter.SelectCommand = cmd;
    myAdapter.Fill(myData);

    myReport.Load(@".\world_report.rpt");
    myReport.SetDataSource(myData);
    myViewer.ReportSource = myReport;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show(ex.Message, "Report could not be created",
        MessageBoxButtons.OK, MessageBoxIcon.Error);
}

A new data set it generated using the same query used to generate the previously saved data set. Once the data set is filled, a ReportDocument is used to load the report file and bind it to the data set. The ReportDocument is the passed as the ReportSource of the crViewer.

This same approach is taken when a report is created from a single table using Connector/ODBC. The data set replaces the table used in the report and the report is displayed properly.

When a report is created from multiple tables using Connector/ODBC, a data set with multiple tables must be created in our application. This enables each table in the report data source to be replaced with a report in the data set.

We populate a data set with multiple tables by providing multiple SELECT statements in our MySqlCommand object. These SELECT statements are based on the SQL query shown in Crystal Reports in the Database menu's Show SQL Query option. Assume the following query:

SELECT `country`.`Name`, `country`.`Continent`, `country`.`Population`, `city`.`Name`, `city`.`Population`
FROM `world`.`country` `country` LEFT OUTER JOIN `world`.`city` `city` ON `country`.`Code`=`city`.`CountryCode`
ORDER BY `country`.`Continent`, `country`.`Name`, `city`.`Name`

This query is converted to two SELECT queries and displayed with the following code:

Visual Basic Example

Imports CrystalDecisions.CrystalReports.Engine
Imports System.Data
Imports MySql.Data.MySqlClient

Dim myReport As New ReportDocument
Dim myData As New DataSet
Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter

conn.ConnectionString = "server=127.0.0.1;" _
    & "uid=root;" _
    & "pwd=12345;" _
    & "database=world"

Try
    conn.Open()
    cmd.CommandText = "SELECT name, population, countrycode FROM city ORDER BY countrycode, name; " _
        & "SELECT name, population, code, continent FROM country ORDER BY continent, name"
    cmd.Connection = conn

    myAdapter.SelectCommand = cmd
    myAdapter.Fill(myData)

    myReport.Load(".\world_report.rpt")
    myReport.Database.Tables(0).SetDataSource(myData.Tables(0))
    myReport.Database.Tables(1).SetDataSource(myData.Tables(1))
    myViewer.ReportSource = myReport
Catch ex As Exception
    MessageBox.Show(ex.Message, "Report could not be created", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

C# Example

using CrystalDecisions.CrystalReports.Engine;
using System.Data;
using MySql.Data.MySqlClient;

ReportDocument myReport = new ReportDocument();
DataSet myData = new DataSet();
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();

conn.ConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test";

try
{
    cmd.CommandText = "SELECT name, population, countrycode FROM city ORDER " +
        "BY countrycode, name; SELECT name, population, code, continent FROM " +
        "country ORDER BY continent, name";
    cmd.Connection = conn;

    myAdapter.SelectCommand = cmd;
    myAdapter.Fill(myData);

    myReport.Load(@".\world_report.rpt");
    myReport.Database.Tables(0).SetDataSource(myData.Tables(0));
    myReport.Database.Tables(1).SetDataSource(myData.Tables(1));
    myViewer.ReportSource = myReport;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show(ex.Message, "Report could not be created",
        MessageBoxButtons.OK, MessageBoxIcon.Error);
}

It is important to order the SELECT queries in alphabetic order, as this is the order the report will expect its source tables to be in. One SetDataSource statement is needed for each table in the report.

This approach can cause performance problems because Crystal Reports must bind the tables together on the client-side, which will be slower than using a pre-saved data set.

5.18 ASP.NET Provider Model

MySQL Connector/NET includes support for the ASP.NET 2.0 provider model. This model enables application developers to focus on the business logic of their application instead of having to recreate such boilerplate items as membership and roles support.

Connector/NET supplies the following providers:

  • Membership Provider

  • Role Provider

  • Profile Provider

  • Session State Provider (Connector/NET 6.1 and later)

The following tables show the supported providers, their default provider and the corresponding MySQL provider.

Membership Provider

Default ProviderMySQL Provider
System.Web.Security.SqlMembershipProviderMySql.Web.Security.MySQLMembershipProvider

Role Provider

Default ProviderMySQL Provider
System.Web.Security.SqlRoleProviderMySql.Web.Security.MySQLRoleProvider

Profile Provider

Default ProviderMySQL Provider
System.Web.Profile.SqlProfileProviderMySql.Web.Profile.MySQLProfileProvider

SessionState Provider

Default ProviderMySQL Provider
System.Web.SessionState.InProcSessionStateStoreMySql.Web.SessionState.MySqlSessionStateStore
Note

The MySQL Session State provider uses slightly different capitalization on the class name compared to the other MySQL providers.

Installing the Providers

The installation of Connector/NET 5.1 or later will install the providers and register them in your machine's .NET configuration file, machine.config. The additional entries created will result in the system.web section appearing similar to the following code:

<system.web>
  <processModel autoConfig="true" />
  <httpHandlers />
  <membership>
    <providers>
      <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
      <add name="MySQLMembershipProvider" type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.1.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Clear" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
    </providers>
  </membership>
  <profile>
    <providers>
      <add name="AspNetSqlProfileProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
      <add name="MySQLProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.1.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
    </providers>
  </profile>
  <roleManager>
    <providers>
      <add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
      <add name="AspNetWindowsTokenRoleProvider" applicationName="/" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
      <add name="MySQLRoleProvider" type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.1.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
    </providers>
  </roleManager>
</system.web>

Each provider type can have multiple provider implementations. The default provider can also be set here using the defaultProvider attribute, but usually this is set in the web.config file either manually or by using the ASP.NET configuration tool.

At time of writing, the MySqlSessionStateStore is not added to machine.config at install time, and so add the following:

<sessionState>
  <providers>
    <add name="MySqlSessionStateStore" type="MySql.Web.SessionState.MySqlSessionStateStore, MySql.Web, Version=6.1.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
  </providers>
</sessionState>

The SessionState Provider uses the customProvider attribute, rather than defaultProvider, to set the provider as the default. A typical web.config file might contain:

   <system.web>
        <membership defaultProvider="MySQLMembershipProvider" />
        <roleManager defaultProvider="MySQLRoleProvider" />
        <profile defaultProvider="MySQLProfileProvider" />
        <sessionState customProvider="MySqlSessionStateStore" />
        <compilation debug="false">
          ...

This sets the MySQL Providers as the defaults to be used in this web application.

The providers are implemented in the file mysql.web.dll and this file can be found in your Connector/NET installation folder. There is no need to run any type of SQL script to set up the database schema, as the providers create and maintain the proper schema automatically.

Using the Providers

The easiest way to start using the providers is to use the ASP.NET configuration tool that is available on the Solution Explorer toolbar when you have a website project loaded.

In the web pages that open, you can select the MySQL membership and roles providers by picking a custom provider for each area.

When the provider is installed, it creates a dummy connection string named LocalMySqlServer. Although this has to be done so that the provider will work in the ASP.NET configuration tool, you override this connection string in your web.config file. You do this by first removing the dummy connection string and then adding in the proper one, as shown in the following example:

<connectionStrings>
  <remove name="LocalMySqlServer"/>
  <add name="LocalMySqlServer" connectionString="server=xxx;uid=xxx;pwd=xxx;database=xxx"/>
</connectionStrings>
Note

You must specify the database in this connection.

Rather than manually editing configuration files, consider using the MySQL Website Configuration tool in MySQL for Visual Studio to configure your desired provider setup. The tool modifies your website.config file to the desired configuration. A tutorial on doing this is available in the following section MySQL Website Configuration Tool.

A tutorial demonstrating how to use the Membership and Role Providers can be found in the following section Section 4.2, “Tutorial: Connector/NET ASP.NET Membership and Role Provider”.

Deployment

To use the providers on a production server, distribute the MySql.Data and the MySql.Web assemblies, and either register them in the remote systems Global Assembly Cache or keep them in your application's bin/ directory.

5.19 Working with Partial Trust / Medium Trust

.NET applications operate under a given trust level. Normal desktop applications operate under full trust, while web applications that are hosted in shared environments are normally run under the partial trust level (also known as medium trust). Some hosting providers host shared applications in their own app pools and allow the application to run under full trust, but this configuration is relatively rare. The MySQL Connector/NET support for partial trust has improved over time to simplify the configuration and deployment process for hosting providers.

5.19.1 Evolution of Partial Trust Support Across Connector/NET Versions

The partial trust support for MySQL Connector/NET has improved rapidly throughout the 6.5.x and 6.6.x versions. The latest enhancements do require some configuration changes in existing deployments. Here is a summary of the changes for each version.

6.6.4 and Above: Library Can Be Inside or Outside GAC

Now you can install the MySql.Data.dll library in the Global Assembly Cache (GAC) as explained in Section 5.19.2, “Configuring Partial Trust with Connector/NET Library Installed in GAC”, or in a bin or lib folder inside the project or solution as explained in Section 5.19.3, “Configuring Partial Trust with Connector/NET Library Not Installed in GAC”. If the library is not in the GAC, the only protocol supported is TCP/IP.

6.5.1 and Above: Partial Trust Requires Library in the GAC

Connector/NET 6.5 fully enables our provider to run in a partial trust environment when the library is installed in the Global Assembly Cache (GAC). The new MySqlClientPermission class, derived from the .NET DBDataPermission class, helps to simplify the permission setup.

5.0.8 / 5.1.3 and Above: Partial Trust Requires Socket Permissions

Starting with these versions, Connector/NET can be used under partial trust hosting that has been modified to allow the use of sockets for communication. By default, partial trust does not include SocketPermission. Connector/NET uses sockets to talk with the MySQL server, so the hosting provider must create a new trust level that is an exact clone of partial trust but that has the following permissions added:

  • System.Net.SocketPermission

  • System.Security.Permissions.ReflectionPermission

  • System.Net.DnsPermission

  • System.Security.Permissions.SecurityPermission

Prior to 5.0.8 / 5.1.3: Partial Trust Not Supported

Connector/NET versions prior to 5.0.8 and 5.1.3 were not compatible with partial trust hosting.

5.19.2 Configuring Partial Trust with Connector/NET Library Installed in GAC

If the library is installed in the GAC, you must include the connection option includesecurityasserts=true in your connection string. This is a new requirement as of MySQL Connector/NET 6.6.4.

The following list shows steps and code fragments needed to run a Connector/NET application in a partial trust environment. For illustration purposes, we use the Pipe Connections protocol in this example.

  1. Install Connector/NET: version 6.6.1 or higher, or 6.5.4 or higher.

  2. After installing the library, make the following configuration changes:

    In the SecurityClasses section, add a definition for the MySqlClientPermission class, including the version to use.

    
    <configuration>
      <mscorlib>
        <security>
          <policy>
            <PolicyLevel version="1">
              <SecurityClasses>
                ....
                <SecurityClass Name="MySqlClientPermission" Description="MySql.Data.MySqlClient.MySqlClientPermission, MySql.Data, Version=6.6.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    
    

    Scroll down to the ASP.Net section:

    
    <PermissionSet  class="NamedPermissionSet"  version="1"  Name="ASP.Net">
    
    

    Add a new entry for the detailed configuration of the MySqlClientPermission class:

    
    <IPermission class="MySqlClientPermission" version="1" Unrestricted="true"/>
    
    
    Note

    This configuration is the most generalized way that includes all keywords.

  3. Configure the MySQL server to accept pipe connections, by adding the --enable-named-pipe option on the command line. If you need more information about this, see Installing MySQL on Microsoft Windows.

  4. Confirm that the hosting provider has installed the Connector/NET library (MySql.Data.dll) in the GAC.

  5. Optionally, the hosting provider can avoid granting permissions globally by using the new MySqlClientPermission class in the trust policies. (The alternative is to globally enable the permissions System.Net.SocketPermission, System.Security.Permissions.ReflectionPermission, System.Net.DnsPermission, and System.Security.Permissions.SecurityPermission.)

  6. Create a simple web application using Visual Studio 2010.

  7. Add the reference in your application for the MySql.Data.MySqlClient library.

  8. Edit your web.config file so that your application runs using a Medium trust level:

    
    <system.web>
      <trust level="Medium"/>
    </system.web>
    
    
  9. Add the MySql.Data.MySqlClient namespace to your server-code page.

  10. Define the connection string, in slightly different ways depending on the Connector/NET version.

    Only for 6.6.4 or later: To use the connections inside any web application that will run in Medium trust, add the new includesecurityasserts option to the connection string. includesecurityasserts=true that makes the library request the following permissions when required: SocketPermissions, ReflectionPermissions, DnsPermissions, SecurityPermissions among others that are not granted in Medium trust levels.

    For Connector/NET 6.6.3 or earlier: No special setting for security is needed within the connection string.

    MySqlConnectionStringBuilder myconnString = new MySqlConnectionStringBuilder("server=localhost;User Id=root;database=test");
    myconnString.PipeName = "MySQL55";
    myconnString.ConnectionProtocol = MySqlConnectionProtocol.Pipe;
    // Following attribute is a new requirement when the library is in the GAC.
    // Could also be done by adding includesecurityasserts=true; to the string literal
    // in the constructor above.
    // Not needed with Connector/NET 6.6.3 and earlier.
    myconnString.IncludeSecurityAsserts = true;
    
  11. Define the MySqlConnection to use:

    MySqlConnection myconn = new MySqlConnection(myconnString.ConnectionString);
    myconn.Open();
    
  12. Retrieve some data from your tables:

    MySqlCommand cmd = new MySqlCommand("Select * from products", myconn);
    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
    DataSet1 tds = new DataSet1();
    da.Fill(tds, tds.Tables[0].TableName);
    GridView1.DataSource = tds;
    GridView1.DataBind();
    myconn.Close()
    
  13. Run the program. It should execute successfully, without requiring any special code or encountering any security problems.

5.19.3 Configuring Partial Trust with Connector/NET Library Not Installed in GAC

When deploying a web application to a Shared Hosted environment, where this environment is configured to run all their .NET applications under a partial or medium trust level, you might not be able to install the MySQL Connector/NET library in the GAC. Instead, you put a reference to the library in the bin or lib folder inside the project or solution. In this case, you configure the security in a different way than when the library is in the GAC.

Connector/NET is commonly used by applications that run in Windows environments where the default communication for the protocol is used via sockets or by TCP/IP. For this protocol to operate is necessary have the required socket permissions in the web configuration file as follows:

  1. Open the medium trust policy web configuration file, which should be under this folder:

    %windir%\Microsoft.NET\Framework\{version}\CONFIG\web_mediumtrust.config
    

    Use Framework64 in the path instead of Framework if you are using a 64-bit installation of the framework.

  2. Locate the SecurityClasses tag:

    
    <SecurityClass Name="SocketPermission"
    Description="System.Net.SocketPermission, System, Version=4.0.0.0,
    Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
    
    
  3. Scroll down and look for the following PermissionSet:

    
    <PermissionSet version="1" Name="ASP.Net">
    
    
  4. Add the following inside this PermissionSet:

    
    <IPermission class="SocketPermission" version="1" Unrestricted="true" />
    
    

    This configuration lets you use the driver with the default Windows protocol TCP/IP without having any security issues. This approach only supports the TCP/IP protocol, so you cannot use any other type of connection.

    Also, since the MySQLClientPermissions class is not added to the medium trust policy, you cannot use it. This configuration is the minimum required in order to work with Connector/NET without the GAC.