How to use transactions in ADO.NET?

Sheonarayan
Posted by in ADO.NET category on for Intermediate level | Points: 250 | Views : 10992 red flag
Rating: 5 out of 5  
 1 vote(s)

In this article, we shall learn how to use transactions in ADO.NET.

Introduction

ADO.NET is a data access mechanism to communicate with the data sources such as SQL server, XML, ODBC & OLEDB data sources. It allows us to work in connected (database connection is alive while the operation is performed) as well as disconnected architecture (database connection is closed and operation can be performed on the in-memory data).

Get hundreds of ASP.NET Tips and Tricks and ASP.NET Online training here.

In order to guarantee that failing execution of one sql statements in the group of statements, rollbacks the changes made by other successful statements as well, we can use this approach of enforcing the transactions. So with this approach, either all sql statemetns executes successfully or none executes i.e. it makes the database operations "atomic" in turn guarantees the "atomicity".

Below is my ASPX code that contains a simple form.

ASPX PAGE

<h3>ADO.NET Transaction Demo</h3>

<table cellpadding="3" cellspacing="1">

<tr><td>First name: </td><td><asp:TextBox ID="txtFirstName"

runat="server" /> </td></tr>

<tr><td>Last name: </td><td><asp:TextBox ID="txtLastName" runat="server"

/> </td></tr>

<tr><td>Age: </td><td><asp:TextBox ID="txtAge" runat="server" />

</td></tr>

<tr><td>Active: </td><td>

<asp:DropDownList ID="dropActive" runat="server">

<asp:ListItem Text="Yes" Value="True" />

<asp:ListItem Text="No" Value="False" />

</asp:DropDownList>

</td></tr>

<tr><td>File name: </td><td><asp:FileUpload ID="FileUpload1"

runat="server" /></td></tr>

<tr><td>&nbsp;</td><td><asp:Button ID="btnSubmit" runat="server"

Text="Submit" OnClick="SubmitData" /></td></tr>

</table>

<p><asp:Label ID="lblMessage" runat="server" EnableViewState="false" /></p>

 

In the above code snippet, we have three textboxes, a dropdown and a FileUpload control. We shall try to enter FirstName, LastName, Age and Active details into one table and File related details into another table. We want to make sure that if any one of the operation is failing no changes will be made to the database.

CODE BEHIND

string _connStr =

ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

 

 

protected void SubmitData(object sender, EventArgs e)

{

// get the connection

using (SqlConnection conn = new SqlConnection(_connStr))

{

// initialte transaction

conn.Open();

using (SqlTransaction transaction = conn.BeginTransaction())

{

try

{

//-- Insert personal details into the database

 

// write the sql statement to execute

string sql = "INSERT INTO PersonalDetail (FirstName, LastName, Age,

Active) VALUES " +

"(@FirstName, @LastName, @Age, @Active)";

 

// attach the parameter to pass, if no parameter is in the sql no

need to attach

SqlParameter[] prms = new SqlParameter[4];

prms[0] = new SqlParameter("@FirstName", SqlDbType.VarChar, 50);

prms[0].Value = txtFirstName.Text.Trim();

prms[1] = new SqlParameter("@LastName", SqlDbType.VarChar, 50);

prms[1].Value = txtLastName.Text.Trim();

prms[2] = new SqlParameter("@Age", SqlDbType.Int);

prms[2].Value = int.Parse(txtAge.Text.Trim());

prms[3] = new SqlParameter("@Active", SqlDbType.Bit);

prms[3].Value = bool.Parse(dropActive.SelectedValue);

 

// instantiate the command object to fire

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

cmd.Transaction = transaction;

cmd.Parameters.AddRange(prms);

cmd.ExecuteNonQuery();

}

 

//-- now Insert file into the database

string fileName = FileUpload1.PostedFile.FileName;

int fileLength = FileUpload1.PostedFile.ContentLength;

 

byte[] imageBytes = new byte[fileLength];

FileUpload1.PostedFile.InputStream.Read(imageBytes, 0, fileLength);

 

sql = "INSERT INTO Files (FileName, FileContent, Active) VALUES

(@fileName, @fileContent, @Active)";

 

SqlParameter[] prms1 = new SqlParameter[3];

// purposefully created error by writing parameter name as

"@fileName1" instead of "@fileName"

prms1[0] = new SqlParameter("@fileName1", SqlDbType.VarChar, 50);

prms1[0].Value = fileName;

 

prms1[1] = new SqlParameter("@fileContent", SqlDbType.Image);

prms1[1].Value = imageBytes;

prms1[2] = new SqlParameter("@Active", SqlDbType.Bit);

prms1[2].Value = true; // hard coded value

 

using (SqlCommand cmd1 = new SqlCommand(sql, conn))

{

cmd1.Transaction = transaction;

cmd1.Parameters.AddRange(prms1);

cmd1.ExecuteNonQuery();

}

 

// looks like everything is fine, commit the transaction

transaction.Commit();

 

conn.Close();

 

lblMessage.Text = "Record inserted successfully !";

}

catch (Exception ee)

{

// error occured, rollback the transaction

transaction.Rollback();

lblMessage.Text = "Error occured . " + ee.ToString();

}

}

}

}

 

On click of the button, SubmitData server side method fires. Here we have tried to insert Person details into PersonalDetail database table and File details into Files database table (This may not be an ideal scenario, however this will demonstate how both SqlCommand objects doesn’t affect the database tables if any one of them error out).

In the SubmitData method, we have first instantiated the SqlConnection object by passing the connection string; then opened the connection. Once the connection is open, we can call the begin transaction on the connection object (The connection must be in open state before attaching a transaction with it).

Now we have wrapped remaining code under try block so if any error occurs we will be able to call the
transaction.Rollback() method so that all the operations on this connection will be rolled back. In the try block we have executed two Sql statements separately. While executing the second Sql statement, we have purposefully passed wrong parameter as “@firstName1” instead of “@firstName”. Then at the last of the try block we have called the Commit method of the transaction object that commits the transaction if everything executes well under the try block.

When this code executes, we shall notice that execution of the second command object throws error (due to wrong parameter name) and execution flow goes into the catch block that calls the Rollabck method of the transaction and the changes made by the first SqlCommand object gets rolled back (as a result no changes is made in the database even if first SqlCommand object executed successfully).

OUTPUT

Above image is my output of the page that clearly shows the error occurred on the page.

Hope you liked this article. Hope you have followed my earlier article on ADO.NET where I have described many frequently used scenarios.

Stay tuned for many more articles like this.

Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)