Sql Server Transactions - ADO.NET 2.0 - Commit and Rollback

Sql Server Transactions - ADO.NET 2.0 - Commit and Rollback - Using Statement - IDisposable

My copy of Pro ADO.NET 2.0 by Apress showed up the other day. I am not ready to give my final review of it, but it seems to be a solid book. Most of ADO.NET has not changed with the release of ADO.NET 2.0, so when you buy a new book about ADO.NET 2.0 don't expect a wealth of new information.

Anywhoo, I thought I would share some basic information about SQL Server Transactions for those who are new to ADO.NET. The book does a good job of providing an overview of transactions and a basic template for how to execute local transactions in code. Here is the basic template for an ADO.NET 2.0 Transaction:

using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction = null;

try
{
// BeginTransaction() Requires Open Connection
connection.Open();

transaction = connection.BeginTransaction();

// Assign Transaction to Command
command.Transaction = transaction;

// Execute 1st Command
command.CommandText = "Insert ...";
command.ExecuteNonQuery();

// Execute 2nd Command
command.CommandText = "Update...";
command.ExecuteNonQuery();

transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}

A c# using statement wraps up the connection, because SqlConnection implements IDisposable. The using statement makes sure that Dispose() gets called on the connection object so it can free up any unmanaged resources.

Before you can begin a transaction, you must first open the connection. You begin your transaction and then assign any newly created command objects to that transaction and perform queries as necessary. Commit the transaction. If an error occurs, Rollback the transaction in a catch statement to void out any changes and then rethrow the error so that the application can deal with it accordingly. The connection is properly closed in the finally statement, which gets called no matter what, and any unmanaged resources are disposed when the using statement calls Dispose() on the connection. Pretty simple solution to a fairly advanced topic.

The above template could actually implement a second c# using statement around command, because SqlCommand also implements IDisposable. I don't know that it is really necessary, however. More theoretical than probably anything. I just like to see using statements around anything that implements IDisposable:

using (SqlConnection connection =
new SqlConnection(connectionString))
{
using (SqlCommand command =
connection.CreateCommand())
{
SqlTransaction transaction = null;

try
{
// BeginTransaction() Requires Open Connection
connection.Open();

transaction = connection.BeginTransaction();

// Assign Transaction to Command
command.Transaction = transaction;

// Execute 1st Command
command.CommandText = "Insert ...";
command.ExecuteNonQuery();

// Execute 2nd Command
command.CommandText = "Update...";
command.ExecuteNonQuery();

transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}

Add prerequisites of dotnet framework in visual studio setup project

When we developing an application using any platform we should prepare our application to be deployed properly. Specially for dotnet, the prerequisites of dotnet framework is must. So here by I am explaining how to add framework to be installed before our application installation process.

After developing our .Net application we need to create a setup project. Just follow the steps mentioned in below.

- Go to File menu, select Add New Project and then select Setup And Deployment in under “Other Project Types”.

- Give a name to your project as your wish.

- Then select your setup project solution and add Primary output of your developed project. You just need to right click your setup project in solution explorer, click add --> Project Output --> select your project.

- Then add custom action by right clicking setup project --> view --> Custom Actions --> right click Custom Actions --> Add Custom Actions.

- Now we are going to add the prerequisites for your setup project.

- Select your setup project right click and go to properties then click the Prerequisites button.

- Now select .Net Framework 2.0 tick box after selecting the “Create setup program to install prerequisite components”.

- Then specify the install location for prerequisites by selecting the option box.

- Here I am selecting the second one “Download prerequisites from the same location as my application”.

- Click ok and apply then ok.

- Now build your setup project, the dotnet framework will be added in your debug folder.

Achieve database independence by developing a pluggable data layer


This article explains how to use .NET 2.0’s data provider factory classes to develop a pluggable data layer that is independent of database type and ADO.NET data provider.
Introduction
If you want to develop a data layer that supports many types of database products, it should not be tightly coupled with a particular database product or ADO.NET data provider. The fact that ADO.NET has data providers that are enhanced for specific databases makes that independence more difficult and cumbersome to achieve.
You should have a good understanding of the .NET framework and familiarity with the ADO.NET library before using .NET 2.0’s data provider factory classes to create a pluggable data layer.
Supporting many database products
If you plan to market your application to many potential clients, it should support more than one database product. Since some clients may have already invested in a particular database, the ability to easily configure your application to work with different products is a strong feature.
When developing a data-centric application, I generally use a particular ADO.NET data provider and develop the data layer targeting a particular database product. One benefit of isolating the data layer is that it makes it easy to change the database product without affecting the application too much.
If the business and user interface layers in your application use the data layer for database-related operations and do not directly access the database, then you can have multiple data layers for the database products you want to support. Although this approach sounds reasonable, maintaining multiple data layers for every database product you intend to support is not feasible. The classes in System.Data.Common namespace enable you to build a data layer independent of the database product, and easily change the database product on which it works.
System.Data.Common namespace
Take the following ADO.NET code that connects to a SQL Server database and execute an arbitrary SQL statement:


C# Code
System.Data.SqlClient.SqlConnection con = new
System.Data.SqlClient.SqlConnection();
con.ConnectionString = "Data Source=.;initial
catalog=Northwind;Integrated security=true";
System.Data.SqlClient.SqlCommand cmd = new
System.Data.SqlClient.SqlCommand();
cmd.CommandText = "Update Products set UnitsInStock=UnitsInStock+10";
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
There are quite a few problems in the code above that would make it difficult to modify to work with a different database product. One obvious change that is needed is to move the hard-coded connection string information out to a configuration file. Another problem is that we are tying our code to a particular ADO.NET data provider, in this case the SQL Client data provider. This increases the changes that are needed if we want to support another database product.
Now let us see how the code is changed after we move the connection string out to the application configuration file and use the classes in the System.Data.Common namespace instead of the SQL Client ADO.NET data provider:


C# Code
System.Configuration.AppSettingsReader appReader = new
System.Configuration.AppSettingsReader();
string provider = appReader.GetValue("provider", typeof(string)).ToString();
string connectionString = appReader.GetValue("connectionString",
typeof(string)).ToString();
System.Data.Common.DbProviderFactory factory =
System.Data.Common.DbProviderFactories.GetFactory(provider);
System.Data.Common.DbConnection con = factory.CreateConnection();
con.ConnectionString = connectionString;
System.Data.Common.DbCommand cmd = factory.CreateCommand();
cmd.CommandText = "Update Products set UnitsInStock=UnitsInStock+10″;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();


App.config file




initial catalog=Northwind;Integrated security=true" />




In the code above, other than isolating the connection string, we have used the common ADO.NET data provider in the System.Data.Common namespace. This is a simple implementation of the abstract factory pattern. Each ADO.NET data provider has a factory class that enables us to create ADO.NET objects of its provider type.
The SQL Client ADO.NET data provider, for example, has a SqlClientFactory that can be used to create SqlConnection, SqlCommand, and other SQL Client ADO.NET data provider-specific objects. Based on the string value that is passed to the GetFactory method of the DbProviderFactories class, a concrete instance of a particular ADO.NET data provider factory will be created. Instead of creating the connection and the command objects directly, we use this factory instance to create the necessary ADO.NET objects for us.
The code above shows that we are passing the string value System.Data.SqlClient from the application configuration file, indicating that we want a SqlClientFactory object to be created and assigned to the factory variable. From that point on, all the create methods of the DbProviderFactory object will create ADO.NET objects of the SQL Client ADO.NET data provider.
The classes in ADO.NET have been altered from .NET 1.1 to inherit common base classes from the SystemData.Common namespace. ADO.NET connection classes such as SqlConnection and OleDbConnection inherit from the DbConnection class, for example. The following diagram shows the inheritance hierarchy of the factory classes and the ADO.NET classes:

Provided we have used standard SQL statements, we can easily make our product work with a different ADO.NET data provider by changing the provider in the application configuration file. If we set it to System.Data.OleDb, an OleDbFactory class will be created, which will create OleDb data provider-specific ADO.NET objects such as OleDbConnection and so on.
You might also want to list all of the available ADO.NET data providers. You can do so using the GetFactoryClasses method of the DbProviderFactories class:


C# Code
DataTable tbl =
System.Data.CommonDbProviderFactories.GetFactoryClasses();
dataGridView1.DataSource = tbl;
foreach (DataRow row in tbl.Rows)
{
Console.WriteLine(row["InvariantName"].ToString());
}



The GetFactoryClasses method returns a data table containing information about the available ADO.NET data providers. The InvariantName column provides the necessary string value needed to pass to the GetFactory method in order to create a factory for a particular ADO.NET data provider.
One disadvantage of using the factory classes and developing a common data layer is that it limits us to standard SQL statements. This means we cannot take advantage of the full functionality of a particular database product.
One way to overcome this is to make a check on the type of ADO.NET object created by a factory and execute some statements based on it. Though it’s not an elegant approach, it is useful when we need to execute database product-specific SQL statements. For example:


C# Code
DbProviderFactory factory =
DbProviderFactories.GetFactory("System.Data.SqlClient");
DbCommand cmd = factory.CreateCommand();
if(cmd is System.Data.SqlClient.SqlCommand)
{
//set command text to SQL Server specific statement
}
else if (cmd is System.Data.OleDb.OleDbCommand)
{
//set command text to OleDb specific statement
}


Conclusion:



The ADO.NET data providers in .NET 2.0 provide factory and common ADO.NET classes that make it easy to keep your code independent from a particular ADO.NET data provider or database product.