SqlParameter Class

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

Property	Description
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()
        {
            InitializeComponent();
        }

        private void btCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        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();
            cmd.CommandText = @"SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP
                                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";

            cmd.Parameters.Add(EmpNoParam);
            cmd.Parameters.Add(JobParam);

            cmd.Connection.Open();

            reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            DataTable dt = new DataTable();
            dt.Load(reader);

            dgDetails.DataSource =dt;
            cmd.Dispose();
            conn.Dispose();
        }
    }
}

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

myCommand.Parameters.AddRange(sqlParameters);

Thanks for Reading.