Updating and Inserting Data with ADO.NET and ASP.NET 2.0 - Inserting and Updating Data using the SqlCommand
(Page 3 of 4 )
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:
<%@ 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: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;
public partial class _Default : System.Web.UI.Page{
protected void Page_Load(object sender, EventArgs e){ }
protected void Button1_Click(object sender, EventArgs e){
string connectionString = "Data Source=(local);
Initial Catalog=Northwind;Integrated Security=True";
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.
Next: Using Parameterized Queries to Retrieve Data >>
More ASP.NET Articles
More By Michael Youssef
