Inserting and Deleting Data with Parameters in ASP.NET - Explaining the Code Example
(Page 3 of 4 )
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?
SqlParameter firstParameter = new SqlParameter();
firstParameter.ParameterName = "@LastName";
firstParameter.Value = LastNameTextBox.Text;
command.Parameters.Add(firstParameter);
SqlParameter secondParameter = new SqlParameter();
secondParameter.ParameterName = "@FirstName";
secondParameter.Value = FirstNameTextBox.Text;
command.Parameters.Add(secondParameter);
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:
private void PopulateList(bool operation){
GetEmployeesRecords();
if (operation)
ListBox1.Items[ListBox1.Items.Count - 1].Selected = true;
else
ListBox1.Items[0].Selected = true;
ListBox1_SelectedIndexChanged(this, EventArgs.Empty);
}
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.
protected void NewEmployeeButton_Click(object sender, EventArgs
e){
if (LastNameTextBox.Text != String.Empty &
FirstNameTextBox.Text != String.Empty){
InsertNewEmployee();
DeleteEmployeeButton.Enabled = true;
}
}
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:
protected void NewButton_Click(object sender, EventArgs e){
ListBox1.Enabled = false;
ListBox1.SelectedIndex = -1;
EmployeeIDTextBox.Text = "You can't enter a value here";
LastNameTextBox.Text = String.Empty;
FirstNameTextBox.Text = String.Empty;
NewButton.Enabled = false;
NewEmployeeButton.Enabled = true;
DeleteEmployeeButton.Enabled = false;
}
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'INSERT INTO Employees(LastName, FirstName)
VALUES(@LastName, @FirstName)', N'@LastName nvarchar(4),
@FirstName nvarchar(6)', @LastName=N'Mick',@FirstName=N'Joseph'
And here is the DELETE command:
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.
Next: Fixing the @EmployeeID SqlParameter >>
More ASP.NET Articles
More By Michael Youssef
