- The ODBC.NET Solution - MyODBC Driver
- Using MySQL Native .NET Providers
- 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
- Download and install the latest .NET Framework SDK.
- Install Microsoft Data Access Components (MDAC) 2.6 or later. MDAC 2.8 is the latest and is recommended.
- 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
You can fix the above problem by applying the patch for
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 ..ODBC32.DLL
from: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q319243. - 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.
- Now install MySQL ODBC Driver-MyODBC 3.51; and for installation instructions, refer to
the Installation section of the Connector/ODBC documentation. - 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.
- 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;
- 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};" +
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.
"SERVER=localhost;" +
"DATABASE=test;" +
"UID=venu;" +
"PASSWORD=venu;" +
"OPTION=3";
OdbcConnection MyConnection = new OdbcConnection(MyConString);
MyConnection.Open();
OdbcConnection MyConnection = new OdbcConnection("DSN=myodbc3-test");
In case of VB, it should be
MyConnection.Open();
Dim MyConnection As New OdbcConnection(MyConString)
MyConnection.Open()
- 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:
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
- Download and install the latest .NET Framework SDK.
- Install Microsoft Data Access Components (MDAC) 2.6 or later. MDAC 2.7 is recommended.
- 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.
- 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.
- 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;
In case of VB, instead of 'using' use 'Imports' i.e.:
CoreLab : using CoreLab.MySql;Imports ByteFX.Data.MySqlClient or
Imports CoreLab.MySql - 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.