使用.Net连接MySQL

Presently, developers can access MySQL using three different methods.

1. The ODBC.NET Solution - MyODBC Driver
2. Using MySQL Native .NET Providers
3. Using the OLEDB.NET Solution - MyOLDDB Provider

The ODBC.NET Solution - MyODBC Driver

The ODBC .NET Data Provider is a data provider that wraps an existing ODBC connection.

The data provider is defined in the System.Data assembly and ships as a standard part of the 1.1 version of the framework. Users still building with .NET 1.0 can download the ODBC provider directly from Microsoft.

ODBC.NET - MyODBC Architecture

Requirements - Setup of the ODBC.NET Environment

1. Download and install the latest .NET Framework SDK.
2. Install Microsoft Data Access Components (MDAC) 2.6 or later. MDAC 2.8 is the latest and is recommended.
3. Install the ODBC.NET Provider. Note that this step is only necessary if you are building applications using .NET 1.0. The ODBC provider comes standard with .NET 1.1. Note: Using ODBC.NET with MyODBC, while fetching empty string (0 length), will give SQL_NO_DATA exception as shown below.

    System.Data.Odbc.OdbcException: NO_DATA - no error information available
        at System.Data.Odbc.OdbcConnection.HandleError(IntPtr hHandle, SQL_HANDLE hType, RETCODE retcode)
        at System.Data.Odbc.OdbcDataReader.GetData(Int32 i, SQL_C sqlctype, Int32 cb)
        at System.Data.Odbc.OdbcDataReader.internalGetString(Int32 i)
        at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i, TypeMap typemap)
        at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)
        at System.Data.Odbc.OdbcDataReader.IsDBNull(Int32 i)
        at GameShopWS.GameProvider.GetGameInfo(Int32 game_ID) in ..
   

   You can fix the above problem by applying the patch for ODBC32.DLL from: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q319243.
4. Install MySQL Server. For more information on how to install and setup the MySQL Server, refer to the Installation chapter of the MySQL Reference Manual.
5. Now install MySQL ODBC Driver-MyODBC 3.51; and for installation instructions, refer to
   the Installation section of the Connector/ODBC documentation.
6. Setup an MyODBC DSN to be used for connecting to MySQL by following the instructions in the "DSN on Windows" section of the Connector/ODBC documentation.

Developing MyODBC-ODBC.NET Provider Applications

The System.Data namespace contains the ODBC .NET Data Provider. A .NET data provider provides functionality for connecting to a data source, executing commands, and retrieving results. Those results can be processed directly, or placed in an ADO.NET DataSet for further processing while in a disconnected state. While in the DataSet, data can be exposed to the user, combined with other data from multiple sources, or passed remotely between tiers. Any processing performed on the data while in the DataSet can then be reconciled to the data source.

All .NET data providers are designed to be lightweight. They consist of a minimal layer between the data source and your code. This extends functionality without sacrificing performance.

These are the core classes that make up a ODBC.NET data provider:

Core classes:

CLASS	DESCRIPTION
OdbcCommand	Represents an SQL statement or stored procedure to execute against a data source. This class cannot be inherited.
OdbcCommandBuilder	Automatically generates single-table commands used to reconcile changes made to a DataSet with the associated data source. This class cannot be inherited.
OdbcConnection	Represents an open connection to a data source.
OdbcDataAdapter	Represents a set of data commands and a connection to a data source that are used to fill the DataSet and update the data source. This class cannot be inherited.
OdbcDataReader	Provides a way of reading a forward-only stream of data rows from a data source. This class cannot be inherited.
OdbcError	Collects information relevant to a warning or error returned by the data source. This class cannot be inherited.
OdbcErrorCollection	Collects all errors generated by the OdbcDataAdapter. This class cannot be inherited.
OdbcException	The exception that is generated when a warning or error is returned by an ODBC data source. This class cannot be inherited.
OdbcInfoMessageEventArgs	Provides data for the InfoMessage event. This class cannot be inherited.
OdbcParameter	Represents a parameter to an OdbcCommand and optionally, its mapping to a DataColumn. This class cannot be inherited.
OdbcParameterCollection	Represents a collection of parameters relevant to an OdbcCommand as well as their respective mappings to columns in a DataSet. This class cannot be inherited.
OdbcPermission	Enables the ODBC .NET Data Provider to ensure that a user has a security level adequate to access an ODBC data source. This class cannot be inherited.
OdbcPermissionAttribute	Associates a security action with a custom security attribute.
OdbcRowUpdatedEventArgs	Provides data for the RowUpdated event. This class cannot be inherited.
OdbcRowUpdatingEventArgs	Provides data for the RowUpdating event. This class cannot be inherited.
OdbcTransaction	Represents an SQL transaction to be made at a data source. This class cannot be inherited

Delegates:

DELEGATE	DESCRIPTION
OdbcInfoMessageEventHandler	Represents the method that will handle the InfoMessage event of an OdbcConnection.
OdbcRowUpdatedEventHandler	Represents the method that will handle the RowUpdated event of an OdbcDataAdapter.
OdbcRowUpdatingEventHandler	Represents the method that will handle the RowUpdating event of an OdbcDataAdapter.

Enumerations:

ENUMERATION	DESCRIPTION
OdbcType	Specifies the data type of a field, property, or OdbcParameter.

For more information about all these commands, and its usage, refer to the ODBC.NET Data Provider Documentation that comes with ODBC.NET.

To use the ODBC .NET Data Provider, you must import the System.Data namespace to your application, as the following code illustrates:

 [Visual Basic]
 Imports System.Data.Odbc

 [C#]
 using System.Data.Odbc;

You also must include a reference to the .DLL when you compile your code. For example, if you are compiling a C# program, your command line should include:

  csc /r:System.Data.dll

Demo Example - Establishing a Connection

In the demo example, we will look at how to connect to MySQL server through MyODBC using ODBC.NET.

1. Import the System.Data.Odbc namespace (ODBC.NET) to your application using the following statement:

     using System.Data.Odbc;
     

   In case of VB, it should be:

     Imports System.Data.Odbc;
     

2. Once the namespace is imported in your application, you can create a simple class and establish a connection to MySQL server through MyODBC using an OdbcConnection object.

       string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
                            "SERVER=localhost;" +
                            "DATABASE=test;" +
                            "UID=venu;" +
                            "PASSWORD=venu;" +
                            "OPTION=3";
   
       OdbcConnection MyConnection = new OdbcConnection(MyConString);
       MyConnection.Open();
     

   The above one uses DSN-less connection, if you have a MyODBC DSN defined already then you can just use "DSN=dsn_name" as the connection string i.e.

       OdbcConnection MyConnection = new OdbcConnection("DSN=myodbc3-test");
       MyConnection.Open();
     

   In case of VB, it should be

       Dim MyConnection As New OdbcConnection(MyConString)
       MyConnection.Open()
     

3. Once connected, you can execute the SQL statements using the interfaces provided by ODBC.NET.

For a complete example in C# and VB and how to build it, refer to the online MyODBC FAQ, which provides a demo sample with all basic commands.

Using MySQL Native .NET Providers

There are a number of fully managed .NET providers available to help MySQL users develop applications in the .NET environment.

Two popular choices are:

1. ByteFX.Data
2. CoreLabs

The following ADO.NET classes are implemented by both providers:

MySqlConnection	the main connection to the MySQL database
MySqlCommand	enables the execution of any command against the database.
MySqlDataReader	provides fast, forward-only read access to the database.
MySqlDataAdapter	serves as an interface between the MySQL data classes and the Microsoft DataSet.
MySqlParameter	used to store dynamic parameters for a command.
MySqlTransaction	used to represent a MySQL transaction.

ByteFX.Data

ByteFX.Data

ByteFX, Inc. has developed a fully managed .NET provider for MySQL database connectivity. This provider is open-source, available under the LGPL license, developed entirely in C#, and offers access to advanced functionality such as compression, batch SQL submission, and VS.Net integration.

ByteFX.Data implements the ADO.NET managed provider interface, so it behaves exactly like Microsoft's SqlClient and OledbClient providers. You use the familiar Connection, Command, DataAdapter, and DataReader classes just as you would with SQL Server.

Some documentation is provided with the latest release which show the proper way to interact with the classes, but almost any Microsoft example can be used by simply changing any System.Data.SqlClient objects to `ByteFX.Data.MySqlClient objects.

Read more information about ByteFX.Data.

CoreLabs

Core Lab is a closed-source fully-managed .NET provider for MySQL. It is available in standard and professional configurations and can be purchased from http://www.crlab.com.

ADO.NET Architecture

The basic architecture of a MySQL ADO.NET provider looks like:

The architecture will be similar to that of ADO.NET as described at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconadonetarchitecture.asp.

ADO.NET Requirements - Setup

1. Download and install the latest .NET Framework SDK.
2. Install Microsoft Data Access Components (MDAC) 2.6 or later. MDAC 2.7 is recommended.
3. Install MySQL Server. For more information on how to install and setup the MySQL Server, refer to the Installation chapter of the MySQL Reference Manual.
4. Now install a MySQL managed .NET provider. Refer to the installation instructions in the provider's README file.

Developing Applications Using MySQL ADO.NET

The following section, will explain how to connect to the MySQL server through MySQL managed providers.

1. Take any basic ADO.NET samples either from the Microsoft Framework SDK or from the MySQL managed provider, and make sure to replace the system.data.sqlclient namespace with provider specific namespace (in case the example is from Microsoft).

       MySQLNet    :  using ByteFX.Data.MySqlClient;
       CoreLab     :  using CoreLab.MySql;
   

   In case of VB, instead of 'using' use 'Imports' i.e.:

       Imports ByteFX.Data.MySqlClient  or
       Imports CoreLab.MySql
   

2. Once the namespace is imported in your application, you can create a simple class and establish a connection to the MySQL server using:

       string DataSource = "localhost";
       string Database   = "test";
       string UserID     = "root";
       string Password   = "root";
   
       string MyConString  = Data Source=" + DataSource +
           ";Database=" + Database +
           ";User ID=" + UserID +
           ";Password=" + Password;
   
       MySqlConnection mycon = new MySqlConnection(MyConString);
       mycon.Open();
   

For more connection options and programming considerations, refer to the provider's README file.

Using the OLEDB.NET Solution - MyOLDDB Provider

The OLDDB.NET provider can be used in a similar way to ODBC.NET, for exploring MySQL through the MyOLEDB Provider.

MySQL currently doesn't officially support MyOLEDB, so this solution will not be discussed here.

Comparison Between Different Implementations

The basic advantages and disadvantages of these two (ODBC.NET and native .NET Provider) implementations are listed below:

ODBC.NET

Advantages:

• ODBC is designed for maximum interoperability i.e. the ability of a single application to access different database management systems (DBMS) with the same source code
• Ease of Use

Disadvantages:

• Performance is a big negative factor for ODBC as it involves too many layers.

Native .NET Provider

Advantages:

• Speed
• VM benefits including garbage collection and security
• Good exposure of database specific syntax

Disadvantages:

• Not a standardized way of accessing.