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.