In the first part in this series, Working with ADO.NET, we have had a look at the basics of ADO.NET and how we can use it to perform CRUD operations in our applications. In this part we will learn how we can use ADO.NET to bind data to the data controls of ASP.NET. We will also learn how we can perform transactional database operations using ADO.NET.
In the first example we will use a DropDownList control. The example next will make use of a Repeater control to bind data.
The steps to bind data to an ASP.NET data control are:
Here is how the mark-up code of the DropDownList control looks like:
<asp:DropDownList ID="drpStudent" AutoPostBack="true" runat="server" onselectedindexchanged="drpStudent_SelectedIndexChanged"></asp:DropDownList>
Here is the code snippet that illustrates how you can bind data to the DropDownList control using ADO.NET:
SqlConnection sqlConnection = new SqlConnection();
try
{
sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings _
["DBConnectionString"].ConnectionString.Trim();
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "Select * from StudentMaster";
drpStudent.DataSource = sqlCommand.ExecuteReader();
drpStudent.DataValueField = "StudentID";
drpStudent.DataTextField = "Name";
drpStudent.DataBind();
}
catch (Exception ex)
{
//Usual code
}
finally
{
sqlConnection.Close();
}
In our next example, we will use a Repeater control to display a tabular format of records. Here is how the mark-up code of the Repeater control will look like after you have configured it:
<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate> <table border="1">
<tr>
<th><asp:Label id="Student_ID" Text="Student ID" runat="server" /></th>
<th><asp:Label id="Student_Name" Text="Student Name" runat="server" /></th>
<th><asp:Label id="Student_Address" Text="Student Address" runat="server" /></th>
<th><asp:Label id="Student_Phone" Text="Student Phone" runat="server" /> </th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><%# DataBinder.Eval(Container.DataItem, "StudentID")%></td>
<td><%# DataBinder.Eval(Container.DataItem, "Name")%></td>
<td><%# DataBinder.Eval(Container.DataItem, "Address")%></td>
<td><%# DataBinder.Eval(Container.DataItem, "Phone")%></td>
</tr>
</ItemTemplate>
</asp:Repeater>
The next step is to bind data to the control. Here is how you can bind data to the Repeater control:
SqlConnection sqlConnection = new SqlConnection();
try
{
sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings _
["DBConnectionString"].ConnectionString.Trim();
sqlConnection.Open();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter _
("Select * from StudentMaster", sqlConnection);
DataSet dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet);
Repeater1.DataSource = dataSet;
Repeater1.DataBind();
}
catch (Exception ex)
{
Response.Write("Error occured: " + ex.ToString());
}
finally
{
sqlConnection.Close();
}
What are transactions? Why are they required? A transaction is actually a block of statements that will either be executed in whole or not executed at all. In essence, it guarantees that all or none of the statements in the block are executed.
To work with transactions in ADO.NET, follow these simple steps:
To start a transaction, you need to make a call to the BeginTransaction method on the active connection instance. This method returns a transaction instance. You can commit the transaction using the Commit method or rollback your changes using the Rollback method on the transaction instance. In essence, in order to work with transactions, invoke the BeginTransaction() method of the appropriate database connection instance and then call either the Commit() or Rollback() method on the returned transaction object reference depending on the circumstances.
Here is a code snippet that illustrates how you can perform a transactional insert into two database tables simultaneously:
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
ConfigurationManager.ConnectionStrings _
["DBConnectionString"].ConnectionString.Trim();
SqlCommand sqlCommand = new SqlCommand();
try
{
sqlConnection.Open();
SqlTransaction sqlTransaction = null;
try
{
sqlConnection.Open();
sqlTransaction =sqlConnection.BeginTransaction();
sqlCommand.Transaction = sqlTransaction;
sqlCommand.CommandText = "Insert into _
StudentMaster values(1,'Jini')";
sqlCommand.Connection = sqlConnection;
sqlCommand.ExecuteNonQuery();
sqlCommand.CommandText = "Insert into PaymentsMaster
values(1,12,300)";
sqlCommand.Connection = sqlConnection;
sqlCommand.ExecuteNonQuery();
sqlTransaction.Commit();
}
catch (Exception e)
{
sqlTransaction.Rollback();
}
finally
{
sqlConnection.Close();
}
Let’s take another example. Transactions are most important in situations like the one discussed below where either both or none of the operations need to be performed. Suppose you are transferring data from your account to your friend’s account. So, the amount should be debited in your account and credited in your friend’s account. What if one of the statements executes, while the other fails?
Here is the code that illustrates how both such statements will be executed in a batch of statements using transactions:
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
ConfigurationManager.ConnectionStrings _
["DBConnectionString"].ConnectionString.Trim();
SqlCommand sqlCommand = new SqlCommand();
try
{
sqlConnection.Open();
SqlTransaction sqlTransaction = null;
try
{
sqlConnection.Open();
sqlTransaction =sqlConnection.BeginTransaction();
sqlCommand.Transaction = sqlTransaction;
sqlCommand.CommandText = "Update Accounts _
Set Balance = Balance - 5000 where CustomerID = 5";
sqlCommand.Connection = sqlConnection;
sqlCommand.ExecuteNonQuery();
sqlCommand.CommandText = "Update Accounts _
Set Balance = Balance + 5000 where CustomerID = 9";
sqlCommand.Connection = sqlConnection;
sqlCommand.ExecuteNonQuery();
sqlTransaction.Commit();
}
catch (Exception e)
{
sqlTransaction.Rollback();
}
finally
{
sqlConnection.Close();
}
With ADO.NET 2.0, there is an improved support for transaction handling in ADO.NET; you have the TransactionScope class in the System.Transactions namespace that you can use to execute a batch of SQL statements. Note that you can even use TransactionScope for supporting distributed transactions.
The following piece of code illustrates how you can use the TransactionScope class for performing transactional operations using ADO.NET:
bool IsConsistent = false;
using (System.Transactions.TransactionScope _
transactionScope = new System.Transactions.TransactionScope())
{
SqlConnection sqlConnection = new SqlConnection(connectionString);
string sqlString = "Update Accounts _
Set Balance = Balance + 5000 Where CustomerID = 5";
SqlCommand cmd = new SqlCommand(sql, sqlConnection);
sqlConnection.Open();
cmd.ExecuteNonQuery();
sqlConnection.Close();
transactionScope.Consistent = IsConsistent;
}
Note that usage of the "using" statement for disposing off the objects when they are not in use. The MSDN states that the "using" statement, "defines a scope, outside of which an object or objects will be disposed. A using statement can be exited either when the end of the using statement is reached or if an exception is thrown and control leaves the statement block before the end of the statement".
Conclusion
In this part in this series of articles on ADO.NET, we have had a look at how we can data bind the ASP.NET data controls using ADO.NET. We also discussed how we can perform transactional operations using ADO.NET. In the next and the concluding part in this series, we will discuss the advanced issues, like, batch updates, new features added in ADO.NET vNext, etc.