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()
{
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.
Anonymous User
25-Feb-2013Use 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.
Anonymous User
25-Feb-2013