HomeASP.NET Inserting and Deleting Data with Parameter...
Inserting and Deleting Data with Parameters in ASP.NET
In the first part of this article we saw how to display data, retrieved from a database table, on a web page. We also saw how to perform update operations, using the T-SQL UPDATE statement along with the SqlCommand and SqlParameter objects, if the user has changed the data on the web page. Today we will learn more about updating data. Specifically, we are going to use the T-SQL INSERT and DELETE statements to perform insert and delete operations on the data. I will be using the same example I used in the last article to add those new operations.
Contributed by Michael Youssef Rating: / 13 September 26, 2007
Before I start I want tell you that there are better ways of working with data in ASP.NET, specifically the Data Source controls and the Data Bound controls, but now we are discussing the basic concepts of ADO.NET so I'm just presenting simple examples with a lot of ADO.NET code. By using those new controls you can create data-aware pages in a few seconds without writing a single line of code, but believe me it's better to spend time on writing ADO.NET code by hand first, especially if you are a beginner. I will discuss those new controls in great detail in my upcoming articles, namely the SqlDataSource, ObjectDataSource, GridView and DetailsView controls. For now let's continue with our web page.
As you know, we perform the insert and delete operations using T-SQL INSERT and DELETE statements. For our example, we need to insert a new employee with first and last names only. The EmployeeID column is set as an IDENTITY column which means that its value is generated automatically by SQL Server, so we will not permit the user to enter a value for this column. The INSERT statement that we can use for this operation may look like this:
INSERT INTO Employees(LastName, FirstName) VALUES ('Mick', 'Joseph')
But we don't want to hard-code the values that we insert into the columns in the string value that we assign to the SqlCommand.CommandText property. We need to use a parameterized INSERT statement so we can pass values to those parameters. The code may look as follows:
DECLARE @LastName NVARCHAR(20), @FirstName NVARCHAR(20) SET @LastName = 'Paul' SET @FirstName = 'Mina' INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName)
This means we need to use SqlParameter objects from our ADO.NET code to create the parameters, assign values to them and send the statement to be executed on the server. The statement that the SqlCommand object built is different from the one above; we will take a look at it in the following sections.
From our DELETE statement, we might execute something like this:
DECLARE @EmployeeID INT SET @EmployeeID = 11 DELETE FROM Employees WHERE EmployeeID = @EmployeeID
As you can see, we are using a parameterized DELETE statement, so we will use the SqlParameter object from our ADO.NET code with this statement too. Let's see the code, and note that I have removed the UPDATE button from the web page for simplicity. As I have said, there are better and easier ways to do the same set of operations but we are writing those lines of code so you can get a good idea of how ADO.NET works.
And here 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{ private string connectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"; private string[] employeeFields = new string[3]; private char[] splitChar = new char[1] { ',' };
When you run the page, after replacing the auto-generated code with the above code, you will get a populated list of employees and, as in the previous article, when you select another employee the text boxes are updated to reflect that new selection as shown in the next screen shot.
When you click the New Employee button on the ListBox control, the New Employee button itself and the DELETE Employee button will all be disabled, and the Insert Employee button will be enabled with empty text boxes so you can enter the first name and the last name of the employee as shown in the next screen shot.
Enter the first name and the last name for the new employee.
Note that you don't have any choice that allows you to cancel the operation, except closing the browser or navigating to another website. If you want this behavior you can provide a cancel button, and in its click event handler enable the controls and set the ListBox-selected index to the first item or the last item or something similar. When you click on the Insert Employee button, the new employee record will be entered in the database's Employees table and the ListBox's SelectedIndex has been set to that new employee item.
Your result maybe different from mine if you have made changes to the Northwind database. I myself make a lot of changes to this database, especially if I'm writing an article, so don't worry about it as long as it's working as it should be. You can download the T-SQL script that creates this database from Microsoft's web site, if you haven't done that already. Now you can delete that new record by clicking the DELETE Employee button.
Note how the ListBox control selects the last item only when you INSERT or DELETE a record, but when the page is first loaded it's set to the first item on the list. So let's see what we have done in the code.
Let's discuss the main functionality of our page, namely the INSERT and DELETE operations, which have been written in the InsertNewEmployee() and DeleteEmployee() methods. The InsertNewEmployee() method uses the familiar SqlConnection and SqlCommand objects to execute the INSERT statement. This is accomplished through using the SqlCommand.ExecuteNonQuery() method with the SqlParameter objects to create the first name and last name parameters, and their values, needed by the T-SQL INSERT statement.
What's new here in the syntax we used to create the SqlParameter objects?
We simply have created an object of type SqlParameter and used its properties to initialize it. The SqlParameter.ParameterName property is used to assign the parameter name for that object (the name of the T-SQL parameter that will be created that corresponds to this SqlParameter object). The SqlParameter.Value property is used to set the value for that parameter. After we create the parameter we must add it to the SqlCommand.Parameters collection property, to be sent and executed with the command, through using the Add() method of this Parameters collection and passing it the object.
The DeleteEmployee() method uses only one parameter, the @EmployeeID parameter, to execute the DELETE statement. This time we have used another variation to create the SqlParameter object needed for the DeleteEmployee() method.
SqlParameter parameter = new SqlParameter("@EmployeeID", EmployeeIDTextBox.Text); command.Parameters.Add(parameter);
We have passed the parameter name and the parameter value that we get through the EmployeeIdTextBox.Text property, to the SqlParameter constructor and added it in the same way. Note that there are many other SqlParameter constructors and we will use them in the upcoming articles when we need to.
We have used the ListBox control to reflect the changes we made in the page. When the user inserts a new record, it is then selected in the ListBox control, and also when the user deletes a record the ListBox control selects its last item after the delete operation completes. We have done this through the use of the PopulateList() method as follows:
The PopulateList() method populates the ListBox control from the database by using a SqlDataReader object, but in this example we have created the PopulateList() method to accept a bool value as a parameter. We have used this value to select the first or the last item in the ListBox. We select the last item in the ListBox control by using the syntax ListBox1.Items[ListBox1.Items.Count - 1].Selected = true, which uses the Count property of the ListBox.Items collection property to count how many items are in the ListBox, and then subtract it by 1 to return the value of the last item in the ListBox control. Note that the first item in the ListBox has an index value of 0 not 1, and this value is used to access the ListItem.Selected property to set it to true. We have passed true to the PopulateList() method from both the InsertNewEmployee() and DeleteEmployee() methods to select the last item in the ListBox control.
The NewEmployeeButton_Click() event handler method is used to test whether the user has entered values in the FirstNameTextBox.Text and the LastNameTextBox.Text. If so it calls the InsertNewEmployee() method to make the insertion. It also enables the DeleteEmployeeButton so the user can now delete an Employee.
You need to enable the NewEmployeeButton button (and disable the DeleteEmployeeButton because we can't use it while we are inserting an employee record) in the NewButton_Click() event handler method as in the following code:
This event handler method disables the ListBox control and prepares the text boxes to accept values from the user. It then enables the NewEmployeeButton and disables the DeleteEmployeeButton, using the Enable property. I really want to show you both the INSERT and DELETE statements that the web page sends to the SQL SERVER when you click both the buttons, so here is the code:
exec sp_executesql N'DELETE FROM Employees WHERE EmployeeID = @EmployeeID',N'@EmployeeID nvarchar(2)',@EmployeeID=N'11'
I caught those commands by using the SQL SERVER Profiler tool while executing the web page. What I want you to note is that the @EmployeeID parameter is declared as nvarchar(2) although the EmployeeID column in the database is defined as integer. What is happening then? When we created the @EmployeeID SqlParameter we didn't specify its data type and we assigned its value from the EmployeeIDTextBox.Text, which is a string value not integer. The SqlParameter object places the value of the EmployeeID, say 10 or 11, in the nearest possible SQL Server datatype which is NVARCHAR.
This makes sense because you have passed a string value from the web page, and all string values passed from your web pages would make the data type of the parameter NVARCHAR. Given the size of your value as the size of the NVARCHAR parameter, that will happen if you don't specify the data type for the SqlParameter object. On the server the NVARCHAR value is implicitly converted to INT and the code runs without any problems. We can solve this issue with some code, so let's see how we can do that.
We need to modify the DeleteEmployee() method to be able to send the following code, which declares the @EmployeeID as an integer instead of nvarchar parameter.
exec sp_executesql N'DELETE FROM Employees WHERE EmployeeID = @EmployeeID',N'@EmployeeID int',@EmployeeID=11
Instead of the code:
exec sp_executesql N'DELETE FROM Employees WHERE EmployeeID = @EmployeeID',N'@EmployeeID nvarchar(2)',@EmployeeID=N'11'
We can do that by converting the value that we assign to the @EmployeeID SqlParameter to int value, using the .NET Convert class and its Int32 method which converts the value given to it as a parameter to int data type, and that's it.
SqlParameter parameter = new SqlParameter("@EmployeeID", Convert.ToInt32(EmployeeIDTextBox.Text));
The complete code for the method is:
private void DeleteEmployee(){ try{ using (SqlConnection connection = new SqlConnection (connectionString)){ string commandText = "DELETE FROM Employees WHERE EmployeeID = @EmployeeID"; SqlCommand command = new SqlCommand(commandText, connection);
SqlParameter parameter = new SqlParameter("@EmployeeID", Convert.ToInt32(EmployeeIDTextBox.Text)); command.Parameters.Add(parameter);
If you want to learn more about SqlParameter objects and how they can be used in your ASP.NET web pages, please read my three article series Creating the StudentDB Class for ASP.NET 2.0.
ASP.NET 
Using Parameters with ADO.NET to Update Data in ASP.NET 2.0 Pages
