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> </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.
If you like this article, subscribe to our 
RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.