October 17, 2021

Programmingempire

In this article, I will discuss Parameter and ParameterCollection in ADO.NET. Basically, ADO.NET supports both parameterized as well as non-parameterized queries. While non-parameterized query follows the following syntax,

select * from <table-name> where <field-name> = 'value';

In this case, we use a literal directly in the query. However, a parameterized query uses the parameter name in place of the literal. In fact, a parameterized query includes a placeholder that will be replaced by the parameter value at the time of execution. For example, a parameterized query looks as follows.

select * from <table-name> where <field-name> = 'placeholder';

Advantages of Parameterized Queries

Parameterized queries help in preventing SQL injections which is a very serious security issue. Moreover, parameterized queries perform better since these queries result in smaller string values that will be sent to the database.

Creating Parameterized Queries in ADO.NET

In order to create a parameterized query in ADO.NET, we need to use the ParameterCollection as given below.

SqlCommand c=new SqlCommand("select * from mytable where fieldname = @f");
c.Parameters.AddWithValue("@f", value);

In the above statements, @f refers to the parameter, and value refers to the value of that parameter.

Parameters

Basically, the SqlParameter class can be used to provide a parameter to an object of SqlCommand. For instance, look at the AddingParameters() function in the following program. While we add the parameters using the ParameterCollection, we can also display each parameter using a loop. So, in the foreach loop, we are referring to each parameter in the collection using an instance of the SqlParameter class.

ParameterCollection

Likewise, the SqlParameterCollection class refers to a parameter collection. Also, this parameter collection specifies the mapping of each parameter with a field name in the database table. Further, the Parameters property of SqlCommand has the type SqlParameterCollection. In order to specify the value of the parameter, we can use the AddWithValue() method. Accordingly, this method adds a parameter to the command object.

A Program Demonstrating Parameter and ParameterCollection in ADO.NET

The following code shows how to use parameters in the insert statement of SQL.

using System;
using System.Data;
using System.Data.SqlClient;
namespace ParametersExample
{
    class Program
    {
        SqlConnection c1;
        SqlCommand cmd;
        static void Main(string[] args)
        {
            Program ob = new Program();
            ob.AddingParameters();
            ob.CreateACollection();
           // ob.InsertRecords();
        }
        public void Connect()
        {
            c1 = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\path to the database\db1.mdf;Integrated Security=True;Connect Timeout=30");
            try
            {
                c1.Open();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        public void InsertRecords()
        {
            int x = GetNextID();
            if (x == -1)
                x = 1100;
            else
                x++;
            Console.WriteLine($"Next Student ID: {x}");
            Console.WriteLine("Enter Student Name: ");
            string s1 = Console.ReadLine();

            Console.WriteLine("Enter Course: ");
            string s2 = Console.ReadLine(); 

            Console.WriteLine("Enter Batch: ");
            string s3 = Console.ReadLine();

            Console.WriteLine("Enter Sem: ");
            int x1 = Int32.Parse(Console.ReadLine());

            Console.WriteLine("Enter Email ID: ");
            string s4 = Console.ReadLine();

            Connect();
            string str = "insert into Student values(@sid, @sname, @course, @batch, @sem, @emailid)";
            cmd = new SqlCommand(str, c1);
            cmd.Parameters.AddWithValue("@sid", x);
            cmd.Parameters.AddWithValue("@sname", s1);
            cmd.Parameters.AddWithValue("@course", s2);
            cmd.Parameters.AddWithValue("@batch", s3);
            cmd.Parameters.AddWithValue("@sem", x1);
            cmd.Parameters.AddWithValue("@emailid", s4);

            int n = cmd.ExecuteNonQuery();
            if (n > 0)
                Console.WriteLine("Record Inserted Successfully!");
            else
                Console.WriteLine("Record Not Inserted!");
            c1.Close();
            ShowRecords();
        }
        public int GetNextID()
        {
            Connect();
            int i = 0;
            string mycommand = "select max(student_id) from Student";
            cmd = new SqlCommand(mycommand, c1);
            object ob=cmd.ExecuteScalar();

            if (ob is DBNull)
            {
                i = -1;
            }
            else
            {
                Console.WriteLine("it is not null");
                i = (int)ob;
            }
            c1.Close();
            return i;
        }
        public void ShowRecords()
        {
            Connect();
            string s = "select * from Student";
            cmd = new SqlCommand(s, c1);
            SqlDataReader dr = cmd.ExecuteReader();
            while(dr.Read())
            {
                Console.WriteLine($"Student ID: {dr[0].ToString()}, Name: {dr[1].ToString()}");
                Console.WriteLine($"Course: {dr[2].ToString()}, Batch: {dr[3].ToString()}");
                Console.WriteLine($"Sem: {dr[4].ToString()}, Email ID: {dr[5].ToString()}");
                Console.WriteLine();
            }
            c1.Close();
        }
        public void AddingParameters()
        {
            cmd = new SqlCommand();
            cmd.Parameters.Add("@id1", SqlDbType.Int).Value=11;
            cmd.Parameters.Add("@sn", SqlDbType.NVarChar, 30).Value="A";

            SqlParameter p1 = new SqlParameter("@emailid", SqlDbType.NVarChar, 100);
            p1.Value = "a@gmail.com";
            cmd.Parameters.Add(p1);
            cmd.Parameters.Add("@crs", SqlDbType.NVarChar).Value = "MCA";
            cmd.Parameters.Add("@bt", SqlDbType.NVarChar).Value = "2021-2024";

            foreach (SqlParameter p in cmd.Parameters)
                Console.WriteLine(p+" "+p.DbType+" "+p.Direction);
            foreach (SqlParameter p in cmd.Parameters)
                Console.WriteLine(p.Value);
        }

        public void CreateACollection()
        {
            SqlParameterCollection coll = cmd.Parameters;
            foreach (SqlParameter p in coll)
                Console.WriteLine(p + " " + p.DbType + " " + p.Direction);
            foreach (SqlParameter p in coll)
                Console.WriteLine(p.Value);
        }
    }
}

Output

Programmingempire

Advantages of Parameterized Queries

Creating Parameterized Queries in ADO.NET

Parameters

ParameterCollection

Further Reading

You may also like...

Example of Button and Link Button Control in ASP.NET

Validation Controls Examples – RequiredFieldValidator, CompareValidator, and RangeValidator

CRUD Operations with DataGrid in ASP.NET