Updating and Inserting Data with ADO.NET and ASP.NET 2.0
(Page 1 of 4 )
In this article we will use methods of the SqlCommand class to insert and update data. We will look at a technique for making sure that the Close() method of the SqlConnection and the SqlDataReader classes is called even when an exception is raised.
I like to introduce concepts as well as code in the same article. In the previous article, we used the SqlConnection, SqlCommand and the SqlDataReader classes to retrieve data from Northwind's Employees table, but we didn't investigate those objects. We can't talk about a big part of the ADO.NET technology in one article, so in the next example I'm going to create the example we have used in the previous article but modify the code so we can talk more about the SqlConnection and the SqlCommand classes.
So let's see the code first, then discuss it. Create a new website and add a ListBox and a Label control to the page as in the following code:
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ListBox ID="ListBox1" runat="server" Height="165px"
Width="152px"></asp:ListBox>
<asp:Label ID="Label1" runat="server"
ForeColor="Red"></asp:Label>
</div>
</form>
</body>
</html>
Now replace the auto-generated class template code in the Default.aspx.cs class with the following:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
// use the namespace of the ADO.NET SQL Server Data Provider
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page{
protected void Page_Load(object sender, EventArgs e){
// create the connection and set the connection string
// through the connection object's constructor
string connectionString = "Data Source=(local);
Initial Catalog=Northwind;Integrated Security=True";
SqlConnection connection = new SqlConnection
(connectionString);
// create the command object and the T-SQL SELECT statement
// and assign the connection object along with the T-SQL
// SELECT statement to command object through the
// constructor
string commandText = "SELECT LastName, FirstName FROM
Employees";
SqlCommand command = new SqlCommand(commandText, connection);
// we have initialized the connection and the command so
// it's time to open the connection and execute the command
// using a try/catch block to handle exceptions
SqlDataReader dataReader;
try{
// check if the connection is already open
if (connection.State == ConnectionState.Closed){
connection.Open();
}
// get a SqlDataReader object from the Command's
// ExecuteReader() method
dataReader = command.ExecuteReader();
// adding the returned rows to the listbox control
while (dataReader.Read()){
ListBox1.Items.Add(dataReader["LastName"] + ", " +
dataReader["FirstName"]);
}
}
catch (Exception ex){
Label1.Text = ex.Message;
}
finally{
connection.Close();
}
}
}
When you run the page you will get the same results as you did with the previous article's example.
In the previous article we assigned the connection string to the SqlConnection object through the property SqlConnection.ConnectionString. Today, however, we passed the connection string to the SqlConnection object's constructor, so we wrote less code. Also we passed the T-SQL SELECT statement along with the SqlConnection object to the SqlCommand object's constructor. So instead of assigning the SqlConnection object to the SqlCommand.Connection property we passed the SqlConnection object to the SqlCommand's constructor, which has the same effect (both techniques associate the SqlConnection with the SqlCommand object).
Now we are ready to open the connection. We used the SqlConnection.State property to test whether the connection is already open. The SqlConnection.State property takes its values from the ConnectionState enumeration. Only two values are used for this ADO.NET release: the Closed and the Open values. We used the SqlConnection.State property because if we call the SqlConnection.Open() method on an opened connection we would get an exception. We opened the connection, through calling the SqlConnection.Open() method, only if the SqlConnection.State property's value equals ConnectionState.Closed.
If the connection is already open we simply execute the T-SQL SELECT statement and return our SqlDataReader object. Note that if an exception has been generated the catch block gets executed and the exception is passed to it as a parameter. The exception message will be displayed in the label control's text, and then the connection is closed in the finally block. Let's do one more thing before we move on to inserting and updating data.
Next: Closing SqlConnection and SqlDataReader objects through the using block >>
More ASP.NET Articles
More By Michael Youssef
