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 “Using the ASP.NET GridView control to query a SQL Server database with ADO.NET”
In that lesson I covered the basics of ADO.NET programming with ASP.NET and C#.  Concepts such as the System.Data.SqlClient namespace, the SqlConnection class, the SqlDataAdapter class, and the DataSet class were covered.  If you haven’t read that lesson yet, it is important to do so because it is the foundation for this lesson and I don’t repeat the same material.

In this lesson, I am going to cover 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.  This lesson applies to ASP.NET with C#.

Why would you need to use parameters with an SQL query?
Whenever you need to find an individual database record or perform a search to retrieve a group of records.  In both cases, you need a way to execute an SQL query with a “where” clause so that you can limit the data returned to specific records that you are interested in finding.  The where clause values are passed using parameters.  This lesson assumes you are searching for a specific record, but you can apply the same technique to search for multiple records.

Let me get started.  First the business requirements.  In the prior lesson “Using the ASP.NET GridView control to query a SQL Server database with ADO.NET” I had created a web form named GridView_SQLServer.aspx.  That form contains an ASP.NET GridView that displays the records in a SQL Server database table named “Customer”.

Let’s say that I have a new requirement which states I must allow Editing (or Updating) of the records in the Customer table.  One way I can achieve this is by creating an Edit page that will retrieve the data for a specific Customer record and allow modification of the fields.

I will create a new web form named Customer_Edit.aspx.  Then I will add some TextBox controls to hold the data from the Customer table and allow the user to modify the data.  I will also add a Save button that will give the user the ability to update the information back to the database table.  Here is what my Customer_Edit.aspx looks like inside the form tag:

<asp:Label ID="Label4" runat="server" 
    Text="Edit Customer Record"></asp:Label>
<br />
<br />
<asp:Label ID="Label1" runat="server" Text="First Name:">
</asp:Label>
<asp:TextBox ID="txtFirstName" runat="server" MaxLength="50">
</asp:TextBox>
<br />
<asp:Label ID="Label2" runat="server" Text="Last Name:">
</asp:Label>
<asp:TextBox ID="txtLastName" runat="server" MaxLength="50">
</asp:TextBox>
<br />
<asp:Label ID="Label3" runat="server" Text="E-mail address:">
</asp:Label>
<asp:TextBox ID="txtEmail" runat="server" MaxLength="50">
</asp:TextBox>
<br />
<asp:Button ID="btnSave" runat="server" Text="Save" />

Now I need a way to retrieve the individual Customer record when this web form opens.  If you want to execute code when a web form opens, you place the code in the special event named Page_Load.

In order to retrieve a specific Customer record from the database, I need to use the unique identifier for a Customer which is the “customer_id” column in my Customer table.  In my application, the GridView_SQLServer.aspx page contains a hyperlink to this form Customer_Edit.aspx and it passes a variable named “customerid” by using a QueryString.  That variable’s value is dynamically taken from the “customer_id” database column.  In the browser this looks something like this:
Customer_Edit.aspx?customerid=55

Notice the QueryString variable follows the ? question mark in the URL.  I need to get the value of that variable so I place the following code in my Customer_Edit.aspx Page_Load method:

string sCustomerId = Request.QueryString["customerid"];
int customerid = int.Parse(sCustomerId);

The next step is to write code to execute a query against the database and retrieve the Customer record that I need based on the customerid variable value.  To do that I will need to use two ADO.NET database classes that were not in the prior lesson.  The first one is the SqlCommand class which allows me to use parameters for queries and the second one is the SqlParameter class which is used to hold the value of the variables used as parameters.  These two additional classes are used in conjunction with the SqlDataAdapter and DataSet.

First let me show you what the entire block of code looks like to perform a database search with ADO.NET parameters and then I will talk about each specific section separately:

SqlDataAdapter dbadapter = null;
SqlCommand dbcmd = new SqlCommand();
SqlParameter dbparam = null;
DataSet dbdata = new DataSet();

using (SqlConnection dbconn = new SqlConnection(
 "Server=localhost;"
 + "Database=csharpuniversity;"
 + "User ID=sa;"
 + "Password=Sqlserverpass$123;"
 + "Trusted_Connection=False;"))
{
 dbcmd.CommandText = "select * from customer "
 + "where customer_id = @customer_id";

 dbparam = dbcmd.CreateParameter();
 dbparam.ParameterName = "customer_id";
 dbparam.DbType = DbType.Int32;
 dbparam.Value = customerid;
 dbcmd.Parameters.Add(dbparam);

 dbcmd.Connection = dbconn;

 dbadapter = new SqlDataAdapter(dbcmd);

 dbadapter.Fill(dbdata);
}

Now, I will go into each individual section and talk about what is going on with this code.  The declaration section contains a declaration for the four variables needed in this example:

SqlDataAdapter dbadapter = null;
SqlCommand dbcmd = new SqlCommand();
SqlParameter dbparam = null;
DataSet dbdata = new DataSet();

Notice that I had to instantiate the SqlCommand object.  This is necessary in order to be able to create an SqlParameter object further down in the code.  I also had to instantiate the DataSet object which is always necessary before you try to put data into it.

Next, I created the SqlConnection object inside the “using” keyword and passed the connection string for the database.  I’m not going to go over that again since it was covered in the prior lesson.

Then, I set the value of the CommandText property for the SqlCommand object:

dbcmd.CommandText = "select * from customer "
 + "where customer_id = @customer_id";

That is where I am able to specify the SQL query to use for the search operation.  Notice that I placed a variable named @customer_id inside the query literal text.  The @ symbol is used for Sql Server parameters in Select, Update, Insert and Delete operations.  Think of the @ variable as a placeholder that will get dynamically replaced with a value 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.  Not a big difference is it?

Now I need a way to specify the value of the @customer_id variable for my search query.  in order to do that I have to create an SqlParameter object:

dbparam = dbcmd.CreateParameter();
dbparam.ParameterName = "customer_id";
dbparam.DbType = DbType.Int32;
dbparam.Value = customerid;
dbcmd.Parameters.Add(dbparam);

Notice that I had to create the SqlParameter object by using a method in the SqlCommand class named CreateParameter().  I also had to specify the name of the parameter which must match exactly with what I put in my query text (customer_id).  I need to specify the type of the variable which is in Integer and assign a value to the parameter.  Notice that I assigned the value of the variable customerid which came from the QueryString.  I also need to add the parameter to the SqlCommand Parameters collection by using the Add() method.

After setting up the SqlParameter object, all I have to do is assign an SqlConnection object to the SqlCommand and then pass the SqlCommand to the SqlDataAdapter object which executes the query by using the Fill() method.

dbcmd.Connection = dbconn;

dbadapter = new SqlDataAdapter(dbcmd);

dbadapter.Fill(dbdata);

Not so bad is it?  You can think of the entire development process of using parameterized ADO.NET queries as follows:
1) Specify your SQL Select query 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 the 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 “where” clause.
3) Associate the SqlConnection object with the SqlCommand object and then pass the SqlCommand object to the SqlDataAdapter constructor.

One final thing that I am going to do is to take the data from the DataSet object and populate the TextBox controls on the web form with the associated data values from the database.

if (dbdata.Tables[0].Rows.Count > 0)
{
 txtFirstName.Text =
     dbdata.Tables[0].Rows[0]["first_name"].ToString();
 txtLastName.Text =
     dbdata.Tables[0].Rows[0]["last_name"].ToString();
 txtEmail.Text =
     dbdata.Tables[0].Rows[0]["email_address"].ToString();
}

Notice that I’m checking to see if any data was found that matches my query by using an “if” statement and checking the Rows collection of the DataTable object that is inside the DataSet.  To retrieve the data value for each TextBox control, I also have to reference the Rows collection.  Since there should be only one row returned because this is an exact match search based on a unique identifier column, I check the row number zero 0: Rows[0].  In order to reference a specific column in a DataSet object with only a single row you can use the following convention:
dbdata.Tables[0].Rows[0][”NAME OF COLUMN”]
And replace NAME OF COLUMN with your database table’s column name.  You also should replace the name of the DataSet variable dbdata and replace with your variable name.

Take a look at this video that shows me executing dynamic ADO.NET queries using parameters.

Stay tuned for the next lesson where I’m going to show you how to give the user the ability to Update the data back to the database.