December 2017 : Online courses in ASP.NET MVC and Angular 4. Conducted by Bipin Joshi. Read more...
Registration for December 2017 batches of ASP.NET MVC / Core and Angular 4 online courses have already started. Conducted by Bipin Joshi. Book your seat today ! Click here for more details.

<%@ Page %>

ADO.NET Series - Parameterize Queries

Introduction

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.

Example

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
cnn.Open();

//set command properties
cmd.Connection=cnn;
cmd.CommandText=
"insert into employees(lastname,firstname) 
values(@lastname,@firstname)";

//set parameter values
SqlParameter p1=new SqlParameter();
p1.DbType=DbType.String;
p1.ParameterName="@lastname";
p1.Value="John";

SqlParameter p2=new SqlParameter();
p2.DbType=DbType.String;
p2.ParameterName="@firstname";
p2.Value="Smith";

//add to parameters collection
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);

//fire the query
cmd.ExecuteNonQuery();
//close connection
cnn.Close();
}
}
}

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.

Summary

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 a software consultant, an author and a yoga mentor having 22+ years of experience in software development. He also conducts online courses in ASP.NET MVC / Core and Design Patterns. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced the Yoga way of life he also teaches Meditation and Mindfulness to interested individuals. To know more about him click here.

Get connected : Twitter  Facebook  Google+  LinkedIn

Posted On : 01 June 2003


Tags : ADO.NET Data Access SQL Server