Various ways to add parameter with SqlCommand object

Various ways to add parameter with SqlCommand object

In this article we will learn various techniques to add parameter with SqlCommand object in C#. I hope you are familiar with parameter associated with SqlCommand object. If not, below few lines are for you.

Parameter is nothing but value passing technique to other function or component. We can pass parameter with SqlCommand object in C#. Parameters are very useful when we want to proof sqlinjection operation. The beauty of parameter is that it suppresses meaning of special character (like ‘– etc) and treats them as normal string. Hence it prevents sqlinjection.

Let’s see various techniques one by one with small example each one of them.

Using  AddWithValue() function

We can use AddWithValue() function to add parameter with value directly. In below we will understand with small example.

using System;
using System.Collections;
using System.Globalization;
using System.Data.SqlClient;
using System.Data;
 
namespace Test1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial
Catalog=test;Integrated Security=True";
            con.Open();
 
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from test where id=@id";
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("id", 1);
            Console.WriteLine("Example of AddWithValue()");
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                Console.WriteLine(rd["id"].ToString());
                Console.WriteLine(rd["name"].ToString());
                Console.WriteLine(rd["sal"].ToString());
            }
            Console.ReadLine();
        }
    }
}
 

Here is sample output

Add parameter by creating object of SqlParameter class

This is another technique to add parameter with Sqlcommand object. At first we will create parameter object with proper value, then we will assign that parameter with SqlCommand object.

using System;
using System.Collections;
using System.Globalization;
using System.Data.SqlClient;
using System.Data;
 
namespace Test1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial
Catalog=test;Integrated Security=True";
            con.Open();
 
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from test where id=@id";
            cmd.Connection = con;
 
            SqlParameter p = new SqlParameter();
            p.ParameterName = "id";
            p.Direction = ParameterDirection.Input;
            p.Value = 1;
            cmd.Parameters.Add(p);
 
 
            Console.WriteLine("Example of SqlParameter by creating object");
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                Console.WriteLine(rd["id"].ToString());
                Console.WriteLine(rd["name"].ToString());
                Console.WriteLine(rd["sal"].ToString());
            }
            Console.ReadLine();
        }
    }
}

Here is sample output

Create parameter object within Add() function and pass as argument

Here is small example to show how to do that. Here we will create SqlParameter object within Add() function as a actual parameter.

 static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial
Catalog=test;Integrated Security=True";
            con.Open();
 
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from test where id=@id";
            cmd.Connection = con;
 
            cmd.Parameters.Add(new SqlParameter("id", 1));
 
            Console.WriteLine("Example of SqlParameter by creating object within
Add()");
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                Console.WriteLine(rd["id"].ToString());
                Console.WriteLine(rd["name"].ToString());
                Console.WriteLine(rd["sal"].ToString());
            }
            Console.ReadLine();
        }

Here is sample output.