How to Execute Parameterized SQL Statements: ADO.NET

As we know, SQL statements can be of simple type or may be parameterized. The parameterized query contains some parameters, which may be accepted through object of SqlParameter class. SqlParameter class is used to create the parameters used by the command object to execute the Sql queries.

Write the following code to select a record from the table groups which has a given name. The name is passed by the parameter i.e. Sql Parameter.

SqlConnection connection = new SqlConnection();
connection.ConnectionString = "Data Source= (LocalDb)\\v11.0; Initial Catalog=StockDb; Integrated Security=True";
connection.Open();
SqlCommand command = new SqlCommand("select * from Groups where code=@code", connection);
command.Parameters.AddWithValue("@code", 04);
SqlDataReader dr = command.ExecuteReader();

Look out the command object which have a variable name @code. It is the syntax of parameter used. In the next line, the parameter is added by using the function AddWithValue(). This method takes two parameter i.e. one for variable name (same as used in command object) and the second one is its value.

Now when we execute this code, a single record having code = 04 will be returned and can be accessed by data reader object dr.

We can check the rows as the same procedure as in previous post.

How to Execute SQL Statements: ADO.NET

We have learnt about ADO.NET object model. In that article we have created a SQL connection, open that connection and close that connection. To execute the SQL statements, we have to write them in between the open and close method of the above connection.

So to execute the SQL statement we have to, first create the connection, open that, write some code which will be executed and at the last execute it with ExecuteReader() method of SQLCommand class.

SqlCommand class is used to specify the Sql statement to be executed and the connection that need to be used. It can also be used to execute stored procedures. Following code will used to execute Sql statement in a command object:

SqlConnection connection = new SqlConnection();
connection.ConnectionString = "Data Source=(LocalDb)\v11.0; Initial Catalog=StockDb; Integrated Security=True";
connection.Open();
SqlCommand command = new SqlCommand("select * from Groups", connection);
SqlDataReader dr = command.ExecuteReader();

In the above code, when SqlCommand object will create, an object of SqlDataReader have been also created. And it is initialized with the function ExecuteReader(). Now all the data can be accessed one by one through dr object, one by one.

When we check the dataReader object through the breakpoint then there are some rows, as shown in following image:

See also: Parameterized Sql Queries