Parameter and ParameterCollection in ADO.NET


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.


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.


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.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");
            catch (SqlException ex)
        public void InsertRecords()
            int x = GetNextID();
            if (x == -1)
                x = 1100;
            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();

            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!");
                Console.WriteLine("Record Not Inserted!");
        public int GetNextID()
            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;
                Console.WriteLine("it is not null");
                i = (int)ob;
            return i;
        public void ShowRecords()
            string s = "select * from Student";
            cmd = new SqlCommand(s, c1);
            SqlDataReader dr = cmd.ExecuteReader();
                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()}");
        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 = "";
            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)

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


The Output of a Program Demonstrating the Parameter and ParameterCollection in ADO.NET
The Output of a Program Demonstrating the Parameter and ParameterCollection in ADO.NET

Further Reading

Parameter and ParameterCollection in ADO.NET

Database Manipulation Using DataGrid

Example of Button and Link Button Control in ASP.NET

Example of Chart Control in ASP.NET

Creating a DataTable from a DataReader in ASP.NET

Deleting a Record using DataGrid Control in ASP.NET

Edit a Record Using DataGrid Control in ASP.NET

Insert a Record Using ItemCommand Event in DataGrid

CRUD Operations with DataGrid in ASP.NET

Creating Columns in a DataGrid Control

XML Documents and DataSet in ASP.NET

Code Render Block in ASP.NET

ASP.NET Core Features and Advantages

Display Images Using DataList Control

Adding Images Using Image Control

Creating a Group of Radio Buttons Using RadioButtonList Control

Example of Button Control in ASP.NET

Using MD5 Hash Algorithm

ItemDataBound Event in DataList

More Features of DataList in ASP.NET

A Simple Example of Using a DataList Control in ASP.NET

Properties and Methods of DataList Control in ASP.NET

ASP.NET Practice Exercise

Exploring DataList Control in ASP.NET

Custom Validator Control in ASP.NET

Validation Summary Control in ASP.NET

Validation Controls Examples – RequiredFieldValidator, CompareValidator, and RangeValidator

An Example of Data Binding with RadioButtonList Control

Binding Data to Web Control in ADO.NET

Examples of AdRotator Control in ASP.NET

Examples of Validation Controls in ASP.NET

Overview of MVC architecture in ASP.NET


You may also like...