When writing database queries, we may configure our SQL statements using Parameters. In this blog we will discuss how to write our SQL statement with parameters defined.

     Using Parameter helps guard against SQL injection. The use of Parameterized SQL statements represents the basics of ADO .NET programming.

Parameter Creation

Creating a Parameter is as simple as declaring an instance of SqlParameter Class. The SqlParameter class has properties associated with it. The properties of SqlParameter class are

ParameterName -Read/ Write property. Specifies the name of the SqlParameter

SqlDbType - Read/Write property. Specifies the size of the Parameter value

Size -This property specifies the direction of the Parameter such as Input, Output or InputOutput. Size is read/write property

Direction -This property maps a column from DataTable to the Parameter.

Value - This read/write property specifies the value that is passes to the parameter defined in the command

Coding Parameterized SQL Statement 
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Configuration;
namespace CSharpLearnings
    public partial class FrmParameterClass : Form
        public FrmParameterClass()
        private void btCancel_Click(object sender, EventArgs e)
        private void btLoad_Click(object sender, EventArgs e)
            SqlConnection conn = new SqlConnection();
            SqlDataReader reader;
            SqlParameter EmpNoParam;
            SqlParameter JobParam;
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
            SqlCommand cmd = new SqlCommand();
                                WHERE EMPNO =@EMPLOYEENO and JOB = @JOBDESC";
            cmd.Connection = conn;
            EmpNoParam = new SqlParameter();
            EmpNoParam.ParameterName = "@EMPLOYEENO";
            EmpNoParam.SqlDbType = SqlDbType.Int;
            EmpNoParam.Size = 10;
            EmpNoParam.Direction = ParameterDirection.Input;
            EmpNoParam.Value = 8957;
            JobParam = new SqlParameter();
            JobParam.ParameterName = "@JOBDESC";
            JobParam.SqlDbType = SqlDbType.VarChar;
            JobParam.Size = 20;
            JobParam.Direction = ParameterDirection.Input;
            JobParam.Value = "ENGINEER";
            reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            DataTable dt = new DataTable();
            dgDetails.DataSource =dt;


The program receives Employee No and Job Description as Input. The Parameters are created by instantiating the SqlParameter class and by specifying the object properties name, type, size, direction and value for each object.

     We add the parameters to the command object by calling the Add () Method of the Parameter collection.

     The result of executing this code gets the result that matches the Employee No and Job Desc.


Alternatively, you can add the Parameters to the Command object by calling the AddWithValue() Method too,

cmd.Parameters.AddWithValue("@EMPLOYEENO", 8957);

                cmd.Parameters.AddWithValue("@JOBDESC", "ENGINEER");

If you think of creating SqlParameter[] array class then, we slighty modify our code as this

            SqlParameter[] sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@EMPLOYEENO",SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(8957);
            sqlParameters[1] = new SqlParameter("@JOBDESC",SqlDbType.VarChar);
            sqlParameters[1].Value = Convert.ToString(“ENGINEER”);

And, you can add the Parameters to the Command object by Invoking


Thanks for Reading.

  Modified On Sep-18-2014 01:24:09 PM
  1. The Parameter class represents a parameter in a parametrized SQL query, a filtering expression, or a business object method call that an ASP.NET data source control uses to select, filter, or modify data. Parameter objects are contained in a ParameterCollection object. Parameter objects are evaluated at run time, to bind the values of the variables they represent to whatever method is used by a data source control to interact with data.

    Use classes that derive from Parameter with data source and data-bound controls to build Web-based data applications. These parameter classes are used by data source controls to bind specific kinds of values found in Web applications to placeholders in SQL query strings, business object method parameters, and more.

Leave Comment