<%@ Page %>

ADO.NET Series - Parameterize Queries


In the last couple of articles we saw how to use SQL queries and stored procedures using Command object. The queries we used were simple ones with no parameters. However, in real life you need parameterize queries in most of the cases. One common approach is to use string concatenation for forming the queries. However, ADO.NET provides a parameter class that should be used instead. This article tells you how to work with the parameter class to execute parameterize queries.

String concatenation vs. parameters

Many developers have habit of forming SQL statements using string concatenation. This is indeed quicker way to build queries. However, it has disadvantages of its own. Firstly, it is poor choice for huge queries that are being executed again again. Secondly, it is not a secured approach as clever person can execute unwanted SQL statements by some tricks (SQL injection attacks).

Parameter class comes handy here. Query formed using parameter class is more optimized than string concatenation approach. Also, for huge queries it becomes very to set parameter properties rather than string concatenation. Parameters are also more secure as compared to string concatenation approach.

How to use parameter class?

Parameter class comes in data provider specific versions i.e. SqlParameter and OleDbParameter. This class represents a single parameter of a query or stored procedure. In SQL data provider parameter is indicated by @param_name syntax. If you are using stored procedure this name must match with the stored procedure parameter name. If you are using OleDb data provider a parameter is represented by ? symbol. Here are some properties of parameter class:
  • ParameterName: Represents the parameter name.
  • Value: Represents the value of the parameter.
  • DbType: The data type of the parameter in terms of CLR.
  • SqlDbType: SQL Server specific data types
  • Precision: Precision for numeric data types.
  • Scale: Scale fir numeric data types.
  • Size: Size of the data that can be contained in the column for string columns.
  • SourceColumn: Used when working with DataSets (discussed in later articles of the series).
  • SourceVersion: Used when working with DataSets.


namespace ADONETSamples
class Sample
static void Main(string[] args)
//declare connection,command
SqlConnection cnn;
SqlCommand cmd;

//create connection
cnn=new SqlConnection("your_connection_string");
cmd=new SqlCommand();

//open connection

//set command properties
"insert into employees(lastname,firstname) 

//set parameter values
SqlParameter p1=new SqlParameter();

SqlParameter p2=new SqlParameter();

//add to parameters collection

//fire the query
//close connection

Let's examine the code:
  • We declared connection and command objects as usual.
  • We then set Connection property of SqlCommand to the connection we just created.
  • The CommandText property is set to the query (INSERT query in our case).
  • Note how we used parameters (@lastname and @firstname) here.
  • We then create instances of SqlParameter classes and set their ParameterName, Value and DbType properties.
  • Next, we add the parameters to the parameters collection of the SqlCommand object.
  • Finally, we execute the query and close the connection.
Note, that if you are using OleDb data provider then the order in which the parameters appear in the query and the order in which you add them in the parameters collection must be the same.


In this article we saw how to use parameter queries using parameter class. There are versions of parameter class depending on the data provider - SqlParameter and OleDbParameter. The parameter class provides an optimized and secure way to execute parameterize queries.

Bipin Joshi is an independent software consultant and trainer by profession specializing in Microsoft web development technologies. Having embraced the Yoga way of life he is also a yoga mentor, meditation teacher, and spiritual guide to his students. He is a prolific author and writes regularly about software development and yoga on his websites. He is programming, meditating, writing, and teaching for over 27 years. To know more about his private online courses on ASP.NET and meditation go here and here.

Posted On : 01 June 2003

Tags : ADO.NET Data Access SQL Server