Click here to download the source code for this lesson. When you open the project files, set GridView_SQLServer.aspx as the Start Page.
This lesson builds on the knowledge of a some prior lessons. Here are the links to those lessons:
How to use ADO.NET with parameters in ASP.NET to Query a SQL Server database
How to use ADO.NET with parameters in ASP.NET to UPDATE a record in a SQL Server database
How to use ADO.NET with parameters in ASP.NET to INSERT a record into a SQL Server database
In those lessons I covered how to use the ADO.NET SqlCommand and SqlParameter classes (along with the SqlDataAdapter) to run SQL Queries, Updates and Inserts with parameters against an SQL Server database table. If you haven’t read those lessons yet, it is important to do so because they are the foundation for this lesson and I don’t repeat the same material. The code for this lesson also builds on the code from the prior lessons.
In this lesson, I am going to cover how to use the ADO.NET SqlCommand and SqlParameter classes to execute an SQL Delete query with parameters against an SQL Server database table. This lesson applies to ASP.NET with C#.
Why would you need to execute an SQL Delete operation and use parameters?
The answer is whenever you need to remove a record from the database. You need a way to execute the SQL Delete with a “where” clause so that you can make sure to remove a specific record that you are interested in. Normally a Delete where clause will need a unique identifier for the record to be removed. The where clause value for the unique identifier is passed using a parameter.
Let me get started. First the business requirements. In the prior lessons I had created a couple of web forms named GridView_SQLServer.aspx and Customer_Edit.aspx. Those forms display records from a SQL Server database table named “Customer” and allow the user to modify the records.
I have a new requirement which states I must also allow the removal of data in the table Customer. I have to give the user the ability to Delete a record. In order to do that I am going to create a new Delete button on the form Customer_Edit.aspx. This new button will be responsible for giving the user the ability to Delete a customer record in the database.
Let me show you the code for that button so you can see how an ADO.NET SQL Delete is performed with C# and then I will discuss each block of code separately.
string sCustomerId = Request.QueryString["customerid"];
int customerid = int.Parse(sCustomerId);
SqlCommand dbcmd = new SqlCommand();
SqlParameter dbparam = null;
using (SqlConnection dbconn = new SqlConnection(
"Server=localhost;"
+ "Database=csharpuniversity;"
+ "User ID=sa;"
+ "Password=Sqlserverpass$123;"
+ "Trusted_Connection=False;"))
{
string SQLDelete = "delete from [customer] "
+ "where customer_id = @customer_id";
dbparam = dbcmd.CreateParameter();
dbparam.DbType = System.Data.DbType.Int32;
dbparam.ParameterName = "customer_id";
dbparam.Value = customerid;
dbcmd.Parameters.Add(dbparam);
dbcmd.CommandText = SQLDelete;
dbcmd.Connection = dbconn;
dbconn.Open();
dbcmd.ExecuteNonQuery();
}
If you read through the prior lessons about ADO.NET SQL Updates and Inserts, you will notice that the code is very similar to this set of code that performs and SQL Delete. In fact, it is almost identical. Now let’s go through each block of code in more detail.
First I get the unique identifier for the record that I need to delete in the database. The database table Customer that I’m trying to delete data from, has a customer_id column that contains an Identity value (a SQL Server automatically incremented number). The customer_id is the unique identifier of a Customer record. I can get this value from the QueryString since it is being passed to the page. Here is how I get the value.
string sCustomerId = Request.QueryString["customerid"]; int customerid = int.Parse(sCustomerId);
After that, inside the using { } block, I start setting up my Delete operation.
string SQLDelete = "delete from [customer] " + "where customer_id = @customer_id";
Notice that I created a string variable named SQLDelete that contains the SQL text for the Delete operation. You can see above that I use a parameter for the customer_id column. Remember from the prior lessons that in an SQL operation, you can use the @ symbol to specify a parameter like @customer_id. That allows ADO.NET to dynamically substitute a value for the variable @customer_id at runtime.
For Oracle, you can use the : (colon) symbol to specify an ADO.NET parameter. So if you were using Oracle as your database, you would change @customer_id to :customer_id.
Then I need to create an SqlParameter object to pass the value of customer_id dynamically.
dbparam = dbcmd.CreateParameter(); dbparam.DbType = System.Data.DbType.Int32; dbparam.ParameterName = "customer_id"; dbparam.Value = customerid; dbcmd.Parameters.Add(dbparam);
When you are creating SqlParameter objects, you need to specify the data type, the name of the database column you want to delete data for and the new value for the column. Then you have to add the SqlParameter to the Parameters collection in the SqlCommand object that you are using.
Next to finish off the code for the Delete button, there are four lines of code left.
dbcmd.CommandText = SQLDelete; dbcmd.Connection = dbconn; dbconn.Open(); dbcmd.ExecuteNonQuery();
In those final lines of code I assign the text string for the Delete to the SqlCommand object dbcmd and associate the SqlConnection object with the dbcmd. Then notice that I explicitly open the connection. If you remember from the prior lessons, when I was using the SqlDataAdapter class, I didn’t need to open the connection because the SqlDataAdapter does that automatically. When you are not using the SqlDataAdapter like in this lesson, you have to explicitly open the connection because the SqlCommand object doesn’t do that automatically. The last line of code is a call to the ExecuteNonQuery() method of the SqlCommand class.
ExecuteNonQuery() is used whenever you want to run a database operation and you are not expecting back any data. Examples of when you can use ExecuteNonQuery are when you need to execute SQL Update, Insert or Delete operations.
That wasn’t so hard was it?
You can think of the entire development process of using parameterized ADO.NET Delete operation as follows:
1) Specify your SQL Delete statement with a where clause and use the @ symbol where SqlParameter variables are needed. If you are using Oracle, us the : (colon) symbol to prefix the SqlParameter variable name.
2) Create each SqlParameter object and assign it a name, a type and a value. You can create multiple SqlParameter objects if you need more than one variable in the Delete operation.
3) Associate the SqlConnection object with the SqlCommand object.
4) Open the SqlConnection object.
5) Run the ExecuteNonQuery() method of the SqlCommand object.
