Date:

Share:

Parameter and ParameterCollection in ADO.NET

Related Articles

In this article, I will discuss the parameter and ParameterCollection in ADO.NET. Basically, ADO.NET supports queries with parameters as well as queries without parameters. While a parameter-free query follows the following syntax,

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

In this case, we use a verbal word directly in the query. However, a query with a parameter uses the parameter name instead of the verb. In fact, a query with an overall parameter indicates a location that will be replaced with the value of the parameter at the time of execution. For example, a query with a parameter looks like this.

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

The benefits of queries with parameters

Queries with parameters help prevent SQL injections which is a very serious security issue. Moreover, queries with parameters function better because these queries result in smaller string values ​​that are sent to the database.

Creating queries with parameters in ADO.NET

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

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

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

Parameters

Basically, the SqlParameter class can be used to provide a parameter to an SqlCommand object. For example, look at Adding parameters() Function in the following program. While we add the parameters using ParameterCollection, we can also display each parameter using a loop. So, in the front loop, we refer to each parameter in the collection using an instance of the SqlParameter class.

Collection of parameters

Similarly, the SqlParameterCollection class refers to a set of parameters. Also, this set of parameters specifies the mapping of each parameter with a field name in the database table. Moreover, the Parameters property of SqlCommand has the SqlParameterCollection type. 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 that demonstrates a parameter and a collection of parameters in ADO.NET

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

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 databasedb1.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);
        
    
}

Productivity

Output of a program that demonstrates the parameter and collection of parameters in ADO.NET

Source

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Popular Articles