HomeASP.NET Updating and Inserting Data with ADO.NET a...
Updating and Inserting Data with ADO.NET and ASP.NET 2.0
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.
Contributed by Michael Youssef Rating: / 6 September 24, 2007
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:
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.
In C# we can make sure that any object that has a Dispose() method, by implementing the IDisposable interface, can be disposed of with the using block. The using block is passed a reference to an object that is used inside the block; when the execution of the block's code completes, the object's Dispose() method is called. Note that the Dispose() method will also be called in the event of an exception; in fact the Dispose() method will be called no matter what happens. Both the SqlConnection and the SqlDataReader implement the IDisposable interface so they have a Dispose() method defined and implemented. The Dispose() method implicitly calls the Close() method which is exactly what we need. Let's see the modified version of the above example that uses this technique.
Replace the Page_Load() event handler with the following:
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(); } // make sure that the connection is closed after we finish // using it using (connection){ // get a SqlDataReader object from the Command's // ExecuteReader() method and make sure that the data // reader is closed too using (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; } }
What we have added in this example is two using blocks, one for the connection object and the other for the data reader object. Note that you can create the object with the using block like so:
using(SqlConnection connection = new SqlConnection(connectionString)){ connection.Open(); // execute the command and return the data reader } // here the SqlConnection.Dispose() method will be called // which in turn calls the SqlConnection.Close() method
But in our example we passed a reference to the connection object because we wanted to test whether or not the connection is open before we use it. We have also used another nested using block with the data reader object to make sure that it's closed when we finish using it. Actually if we don't close the SqlDataReader object, the SqlConnection object can't be used for anything else because it's busy serving the SqlDataReader object, so it's very important to close the SqlDataReader object. We have removed the finally block because it's not needed anymore; we have the using block which closes the connection, and the data reader, when it finishes executing its statements.
Up to this point we have been using SqlDataReader to retrieve data, through the use of the SqlCommand.ExecuteReader() method, but we also need to execute other T-SQL statements like INSERT, UPDATE and DELETE. In this section we are going to do just that. The SqlCommand class contains a method called ExecuteNonQuery() which executes statements that don't return a result set, like the INSERT, UPDATE and DELETE statements, and returns an int value which represents the number of rows that have been affected by executing the statement. You can execute Data Definition Language (DDL) statements, like the CREATE TABLE statement, but most of the time you will be using the SqlCommand.ExecuteNonQuery() method to insert, update and delete data in your database tables. We want to execute the following T-SQL UPDATE statement on the employees table of the Northwind database from our web page:
UPDATE Employees SET FirstName = 'Nanci' WHERE EmployeeID = 1
In the next example, we are going to provide the user with a textbox to enter the T-SQL statement to execute it, and then return the number of rows affected by the statement. Although this is not the perfect example, it's suitable for our discussion; we will show more elegant examples later on. Start by replacing the Default.aspx code with the following code:
<!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:Label ID="CodeLabel" runat="server"> Enter a T-SQL INSERT, UPDATE or DELETE statements only to be executed </asp:Label><br /> <asp:TextBox ID="CodeTextBox" runat="server" Height="100px" TextMode="MultiLine" Width="350px"></asp:TextBox><br /> <asp:Button ID="ExecuteButton" runat="server" Text="Execute T-SQL" OnClick="Button1_Click" /><br /> <asp:Label ID="ResultLabel" runat="server"></asp:Label> <asp:Label ID="ErrorLabel" runat="server" ForeColor="Red"></asp:Label> </div> </form> </body> </html>
We simply have placed several controls, namely three labels, a button and a textbox, on the page. The following is the code for the Default.aspx.cs file:
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;
try{ // create the connection object with the using block using (SqlConnection connection = new SqlConnection (connectionString)){ // assign the T-SQL code to the SqlCommand object string commandText = CodeTextBox.Text; SqlCommand command = new SqlCommand(commandText, connection); // if the textbox is not empty open the connection and // execute the command if (CodeTextBox.Text != String.Empty){ connection.Open(); // assign the number of affected rows to a local // variable int rowsAffected = command.ExecuteNonQuery(); // display the number of affected rows in a label // control ResultLabel.Text = rowsAffected.ToString() + " rows affected by this operation."; } else { ErrorLabel.Text = "Please enter a T-SQL statement to execute"; } } } catch (Exception ex){ // display the exception message in a label control and // enable the textbox so the user can modify the T-SQL // code ErrorLabel.Text = ex.Message; } } }
Run the page and type the following T-SQL code; you will get the result shown in the next screenshot.
UPDATE Employees SET FirstName = 'Nanci' WHERE EmployeeID = 1
If you entered the following statement, which generates an exception because there is no column named Employee in the Employees table, you would get the result shown in the next screen shot.
UPDATE Employees SET FirstName = 'Nanci' WHERE Employee = 1
Let's walk through the code and explain it. We have placed our code in the click event's handler of the ExecuteButton button control. We have created a SqlConnection object with the using block using the same connection string that we have been using since the previous article. Then we created a SqlCommand object and passed the T-SQL code, we got the T-SQL code through the TextBox.Text property, and the SqlConnection object. Before we opened the connection we had tested that the textbox is not empty, through the use of the String.Empty value. If the textbox contains text we open the connection, execute the command and assign the return value of calling the method SqlCommand.ExecuteNonQuery() to the ResultLabel's text. If the textbox is empty we ask the user to insert the T-SQL code. The above example can also be used to insert a new row into the table you simply need to write the following INSERT statement in the textbox and click the Execute T-SQL button.
INSERT INTO Employees (LastName, FirstName, Title, City, Country) VALUES ('Mina', 'Paul', 'Sales Representative', 'Seattle', 'USA')
The result is shown in the following screenshot.
The examples that we have discussed so far are very basic because the user must be a T-SQL Guru to use your web site. Usually, you will have controls on the page that the user interacts with and those controls, along with the ADO.NET code that you write, will update the database.
To make this happen you must use parameterized queries. In the next example we will use a parameterized query to retrieve data using the SqDataReader class, and in the next article we will use parameterized queries to update data in the database.
You might have used a parameterized query while working on a SQL Server database before. A parameterized query is a query that has one or more parameters. The following query is an example.
SELECT FirstName, LastName FROM Employees WHERE EmployeeID = @EmployeeID
We need to build an example that asks the user for the value of the @EmployeeID parameter and retrieves the data depending on that value. Let's see the code first, and then discuss it as usual. The following is the code for the Default.aspx page:
We have created the page with the necessary Labels and TextBoxes in addition to the Button control that executes the data access code. The following is the code for the Default.aspx.cs file.
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;
The SqlCommand class has a Parameters property that is used to add SqlParameter objects to the command. Those parameters, along with their values, will be sent to the SQL Server to be executed with the T-SQL statement. So to use a parameterized query you need to write it first in T-SQL code and assign the query, as a string value, to the SqlCommand object. Then you need to create SqlParameter objects to match those in the T-SQL query.
A SqlParameter is a representation of a T-SQL parameter and its behavior. We will talk about parameters more in the next few articles, but for now you just need to know that the AddWithValue method of the SqlCommand.Parameters collection is passed the name of the parameter; you must prefix the parameter name with the @ sign as you do in T-SQL code; and the value of the parameter, and then the parameter object, will be added to the Parameters collection.
The next article will discuss in detail how to create and use Sqlparameter objects with Parameterized queries and stored procedures.
ASP.NET 
ASP.NET Export to Excel and Word using VB.NET and C#
