Inserting and Deleting Data with Parameters in ASP.NET
(Page 1 of 4 )
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.
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.
Next: The INSERT, DELETE Code Example >>
More ASP.NET Articles
More By Michael Youssef
