Home > DeveloperSection > Blogs > Parameter Class in C#

Parameter Class in C#


C# C# 
Ratings:
2 Comment(s)
 2093  View(s)
Rate this:

Using Parameter 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

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.

    http://tempuri.org?link=new

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.


Parameter Class

By sandy taylor on   3 years ago
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.

Parameter Class in C#

By Mark Toy on   3 years ago
nice post

Don't want to miss updates? Please click the below button!

Follow MindStick