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 prior lesson entitled “How to use ADO.NET with parameters in ASP.NET to Query a SQL Server database“. In that lesson I covered how to use the ADO.NET SqlCommand and SqlParameter classes (along with the SqlDataAdapter) to run an SQL query with parameters against an SQL Server database table. If you haven’t read that lesson yet, it is important to do so because it is the foundation for this lesson. The code for this lesson also builds on the code from the prior lesson.
In this lesson, I am going to cover how to use the ADO.NET SqlCommand and SqlParameter classes to execute an SQL Update 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 Update operation and use parameters?
The answer is whenever you need to modify or update a record in the database. You need a way to pass some data values for the table columns. The data values are passed using parameters. You also need a way to execute the SQL Update with a “where” clause so that you can specify exactly which database record you are interested in modifying. The where clause values are passed using parameters.
Let me get started. First the business requirements. In the prior lesson I had created a web form named Customer_Edit.aspx. That form displays a specific record in a SQL Server database table named “Customer”.
I had a requirement which states I must allow Editing (or Updating) of the records in the Customer table. In the web form Customer_Edit.aspx I had added some TextBox controls to hold the data from the Customer table. I also added a Save button that will give the user the ability to update the information back to the database table.
In this lesson I’m going to show you how to program the Save button Click event. In order to do that, let’s do a quick review from the prior lesson. Here is what Customer_Edit.aspx does in sequence:
1) GridView_SQLServer.aspx displays a list of records from the Customer table.
2) The user clicks on a hyperlink for a specific record. The GridView_SQLServer.aspx page opens the Customer_Edit.aspx page and passed it some data using a QueryString variable:
Customer_Edit.aspx?customerid=55
3) The Customer_Edit.aspx page opens and the Page_Load event executes. In the Page_Load method, the data for a specific customer is retrieved from the database and the TextBox controls on the page are populated with their corresponding data values.
Now I have to create code for a new step #4:
4) The user modifies the data in the TextBox controls and clicks Save. The information is updated back to the database.
Let me show you what the code looks like for the Save button click event and then I will explain it in more detail.
string first_name = txtFirstName.Text;
string last_name = txtLastName.Text;
string email = txtEmail.Text;
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 SQLUpdate = "update [customer] set ";
SQLUpdate += "first_name = @first_name ";
dbparam = dbcmd.CreateParameter();
dbparam.DbType = System.Data.DbType.String;
dbparam.ParameterName = "first_name";
dbparam.Value = first_name;
dbcmd.Parameters.Add(dbparam);
SQLUpdate += ",last_name = @last_name ";
dbparam = dbcmd.CreateParameter();
dbparam.DbType = System.Data.DbType.String;
dbparam.ParameterName = "last_name";
dbparam.Value = last_name;
dbcmd.Parameters.Add(dbparam);
SQLUpdate += ",email_address = @email ";
dbparam = dbcmd.CreateParameter();
dbparam.DbType = System.Data.DbType.String;
dbparam.ParameterName = "email";
dbparam.Value = email;
dbcmd.Parameters.Add(dbparam);
SQLUpdate += " 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 = SQLUpdate;
dbcmd.Connection = dbconn;
dbconn.Open();
dbcmd.ExecuteNonQuery();
}
So let’s walk through each of the blocks of code together. First I get the data entered by the user into the TextBox controls on the web form.
string first_name = txtFirstName.Text; string last_name = txtLastName.Text; string email = txtEmail.Text;
Then I get the unique identifier for the record that I need to update in the database. The database table Customer that I’m trying to update, 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 Update operation.
string SQLUpdate = "update [customer] set "; SQLUpdate += "first_name = @first_name "; dbparam = dbcmd.CreateParameter(); dbparam.DbType = System.Data.DbType.String; dbparam.ParameterName = "first_name"; dbparam.Value = first_name; dbcmd.Parameters.Add(dbparam);
Notice that I created a string variable named SQLUpdate that will be progressively built over several lines of code. I have three pieces of information that I need to save to the database: the customer’s first name, last name and e-mail address. That means that I need three SqlParameter objects. You can see above that I create the first SqlParameter for the first_name column. Remember from the prior lesson that in an SQL operation, you can use the @ symbol to specify a parameter like @first_name. That allows ADO.NET to dynamically substitute a value for the variable @first_name 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 @first_name to :first_name.
When you are creating SqlParameter objects, you need to specify the data type, the name of the database column you want to update 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.
I have to also create the SqlParameter objects for the other database columns last_name and email_address.
SQLUpdate += ",last_name = @last_name "; dbparam = dbcmd.CreateParameter(); dbparam.DbType = System.Data.DbType.String; dbparam.ParameterName = "last_name"; dbparam.Value = last_name; dbcmd.Parameters.Add(dbparam); SQLUpdate += ",email_address = @email "; dbparam = dbcmd.CreateParameter(); dbparam.DbType = System.Data.DbType.String; dbparam.ParameterName = "email"; dbparam.Value = email; dbcmd.Parameters.Add(dbparam);
So that makes a total of three SqlParameter objects that I created. But there is one more parameter that I need. That parameter is for the customer_id column. The customer_id is used in the “where” clause to ensure that only the correct record is updated in the database.
SQLUpdate += " 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);
Next to finish off the code for the Save button, there are four lines of code left.
dbcmd.CommandText = SQLUpdate; dbcmd.Connection = dbconn; dbconn.Open(); dbcmd.ExecuteNonQuery();
In those final lines of code I assign the text string for the Update to the SqlCommand object dbcmd and associate the SqlConnection object with the dbcmd. Then notice that I explicitly open the connection. If you remember in the prior lesson, 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 Update operation as follows:
1) Specify your SQL Update statement 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 Update operation.
3) Associate the SqlConnection object with the SqlCommand object.
4) Open the SqlConnection object.
5) Run the ExecuteNonQuery() method of the SqlCommand object.
Stay tuned for the next lesson where I’m going to show you how to Insert a database record using ADO.NET with parameters in ASP.NET.
