Various ways to add parameter with SqlCommand object

Sourav.Kayal
Posted by in ADO.NET category on for Beginner level | Points: 250 | Views : 3652 red flag

Different ways to attach 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.


conclusion

In this article we have discuss various ways to add parameter with SqlCommand object. It is always good practice to use parameter associated with SqlCommand object to prevent sql injection attack. If we use parameter to pass value(s) through query , it will suppress all general meaning of special character(like ' , -- " etc) from input string.


Page copy protected against web site content infringement by Copyscape

About the Author

Sourav.Kayal
Full Name: Sourav Kayal
Member Level: Silver
Member Status: Member,MVP
Member Since: 6/20/2013 2:09:01 AM
Country: India
Read my blog here http://ctrlcvprogrammer.blogspot.in/
http://www.dotnetfunda.com
I am .NET developer working for HelixDNA Technologies,Bangalore in healthcare domain. Like to learn new technology and programming language. Currently working in ASP.NET ,C# and other microsoft technologies.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)