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
In those lessons I covered how to use the ADO.NET SqlCommand and SqlParameter classes (along with the SqlDataAdapter) to run SQL query and update operations 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. 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 Insert 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 Insert operation and use parameters?
The answer is whenever you need to create a new record in the database. You need a way to pass some data values for the table columns. The data values are passed using parameters.
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 creation of new data in the table Customer. In order to do that I am going to create a new web form named Customer_Insert.aspx. This new web page will be responsible for giving the user the ability to enter data for a brand new record in the database.
I will put three TextBox controls on the form for the database fields First Name, Last Name and E-mail Address. I will also put a Save Button control that will create the new record in the database when clicked. Let me show you the code for that button so you can see how an ADO.NET SQL Insert is performed with C# and then I will discuss each block of code separately.
string first_name = txtFirstName.Text;
string last_name = txtLastName.Text;
string email = txtEmail.Text;
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 SQLInsert = "insert into [customer] "
+" (first_name, last_name, email_address) VALUES (";
SQLInsert += "@first_name ";
dbparam = dbcmd.CreateParameter();
dbparam.DbType = System.Data.DbType.String;
dbparam.ParameterName = "first_name";
dbparam.Value = first_name;
dbcmd.Parameters.Add(dbparam);
SQLInsert += ",@last_name ";
dbparam = dbcmd.CreateParameter();
dbparam.DbType = System.Data.DbType.String;
dbparam.ParameterName = "last_name";
dbparam.Value = last_name;
dbcmd.Parameters.Add(dbparam);
SQLInsert += ",@email )";
dbparam = dbcmd.CreateParameter();
dbparam.DbType = System.Data.DbType.String;
dbparam.ParameterName = "email";
dbparam.Value = email;
dbcmd.Parameters.Add(dbparam);
dbcmd.CommandText = SQLInsert;
dbcmd.Connection = dbconn;
dbconn.Open();
dbcmd.ExecuteNonQuery();
}
If you read through the prior lesson about ADO.NET SQL Updates, you will notice that the code is very similar to this set of code that performs and SQL Insert. In fact, it is almost identical. Now let’s go through each block of code in more detail.
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;
After that, inside the using { } block, I start setting up my Insert operation.
string SQLInsert = "insert into [customer] "
+" (first_name, last_name, email_address) VALUES (";
SQLInsert += "@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 SQLInsert 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 lessons 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 create 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.
I have to also create the SqlParameter objects for the other database columns last_name and email_address.
SQLInsert += ",@last_name "; dbparam = dbcmd.CreateParameter(); dbparam.DbType = System.Data.DbType.String; dbparam.ParameterName = "last_name"; dbparam.Value = last_name; dbcmd.Parameters.Add(dbparam); SQLInsert += ",@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.
Next to finish off the code for the Save button, there are four lines of code left.
dbcmd.CommandText = SQLInsert; dbcmd.Connection = dbconn; dbconn.Open(); dbcmd.ExecuteNonQuery();
In those final lines of code I assign the text string for the Insert 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 Insert operation as follows:
1) Specify your SQL Insert 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 Insert operation.
3) Associate the SqlConnection object with the SqlCommand object.
4) Open the SqlConnection object.
5) Run the ExecuteNonQuery() method of the SqlCommand object.
Take a look at this video that shows me Inserting an SQL Server database record with ADO.NET in ASP.NET.
Stay tuned for the next lesson where I’m going to show you how to Delete a database record using ADO.NET with parameters in ASP.NET.
