relax! it’s just 0s and 1s

with the use of LINQ and Entity Framework, I haven’t been writing much ADO.NET code to for the Data Access layer. Until recently, when one of my projects in the solution is still .NET 2.0 based, with the standard ADO.NET Data Access. its been some time, since I saw the SqlConnection and SqlCommand classses.

offline, I was working with a pastime application of mine, demonstrating Data Source independence. It is a provider architecture, where the specific data source is easily pluggable.

The base interface (IDataProvider) is used by the application. I already had the SqlDataProvider defined for SQL Databases. I tried switiching the provider to a MySql data source. At the end of it, just wanted to publish a couple of snippets to do data access tasks using C# and MySql.

• You can use the ADO.NET Driver provided by MySql. Download the latest MySql Connector for .NET from: http://dev.mysql.com/downloads/connector/net. This is a free developer version of the component.
• You can download the appropriate connector based on the .NET version.
• You can also use the Odbc Driver, but my snippet is for the MySql connector.
• Add a reference to the MySqlData.DLL to your project. Do not forget to ship this DLL.

Add the following namespace:

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

C# and MySql Data Access without a Transaction.

using (MySqlConnection connection = new MySqlConnection(mySqlConnectionString))
{
    using (MySqlCommand command = new MySqlCommand())
    {
        command.Connection = connection;
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "SELNewRecord";
 
        command.Parameters.AddWithValue("Param1", value1);
 
        connection.Open();
 
        using (MySqlDataReader reader = command.ExecuteReader())
        {
            // read the contents.
        }
    }
}

C# and MySql Data Access with a Transaction.

using (MySqlConnection connection = new MySqlConnection(mySqlConnectionString))
{
    using (MySqlCommand command = new MySqlCommand())
    {
        command.Connection = connection;
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "INSNewRecord";
 
        connection.Open();
 
        using (command.Transaction = connection.BeginTransaction())
        {
            command.ExecuteNonQuery();
            // Execute additional SQL Queries.
 
            command.Transaction.Commit();
        }
    }
}

after all the latest ORM technologies and ease of code writing, it feels nice to write a good old data access snippet. though only once every 12 months…