How To Define SQL Parameters In C# (VS2005)

Hi there...

This post is dedicated to programmers (beginners) who want to use SQL statements when coding in C#. Before I continue, let me ask you something. Do you know what a parameter in an SQL statement is?

A parameter is just like a placeholder or variable where you define the location for the actual value in the SQL statement. Since it works like a variable, the value can be changed anytime during the SQL execution. Examples are as follow:

SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;

UPDATE Employees SET FirstName=@FirstName WHERE EmployeeID = @EmployeeID;

DELETE Employees WHERE EmployeeID = @EmployeeID;

INSERT INTO Employees (EmployeeID, FirstName, LastName) VALUES (@EmployeeID, @FirstName, @LastName);

In the above examples, @EmployeeID, @FirstName and @LastName are called parameters. A parameter represents a value that you need to set (or assigned) before you execute the particular SQL statement that contains it.

The question now is how are we going to do it in C#? Let's assume you want to use a SELECT statement in the SqlCommand object.

You got to follow the steps below:

1. Define an SQL Connection object, example:
   SqlConnection conNorthwind = new SqlConnection("server=localhost;uid=sa;pwd=;database=northwind;");
2. Define an SQL Command object, example:
   SqlCommand cmdEmployees = new SqlCommand("SELECT * FROM Employees WHERE EmployeeID = @EmployeeID", conNorthwind);
3. Define a parameter and pass the value from a textbox to it. Then, add it to the SqlCommand parameters collection, example:
   SqlParameter paraEmployeeID = new SqlParameter("@EmployeeID", txtEmployeeID.Text);
   cmdEmployees.Parameters.Add(paraEmployeeID);
4. Open the SqlConnection, example:
   conNorthwind.Open();
   
5. Now you may pass the command object to an SqlDataReader or SqlDataAdapter.
   To pass it to an SqlDataReader, you may do the following:
   SqlDataReader drEmployees = cmdEmployees.ExecuteReader();
   To pass it to an SqlDataAdapter, do the following:
   SqlDataAdapter daEmployees = new SqlDataAdapter(cmdEmployees);
   
6. If you pass the command object to an SqlDataReader using the ExecuteReader method, you can display the retrieved rows directly in a GridView (web control) using the following code:
   GridView1.DataSource = drEmployees;
   GridView1.DataBind();
   
7. If you pass the command object to an SqlDataAdapter, you have to define a dataset also since a DataAdapter can only work with a DataSet. Assuming that you want to display the result in the same web control (GridView). You may write the following codes:
   Dataset dsNorthwind = new DataSet();
   //"Employees" is just a datatable name. It can be replaced with any name.
   daEmployees.Fill(dsNorthwind, "Employees");
   GridView1.DataSource = dsNorthwind.Tables["Employees"];
   GridView1.DataBind();
8. Finally, close the connection as follows:
   conNorthwind.Close();

Notes: If you are using an SqlDataAdapter, it is not necessary to open and close the connection explicitly. This is because the SqlDataAdapter can open and close the connection implicitly.