C#

Parameter and ParameterCollection in ADO.NET

In this post you will learn Parameter and ParameterCollection in ADO.NET. In a database application it is equally important to formulate the queries that don’t make your application vulnerable to security attacks like SQL injection. As a matter of fact when you provide the parameter as a part of the query itself, a malicious attacker can provide input in such a way that the query returns a true value always. For instance, suppose a user provide an input as follows.

' or '1' = 1

The above input will always result in a true value in the condition of the where clause of the query. As a result, the user not only can get access to the pages of the application but can also delete the data. Certainly, we need a way to match the exact value of the parameter provided by the user and parameterized queries enable us to do so.

Basically, a parameterized query is one that uses the Parameter Collection of a SqlCommand object, and all the parameters of the query need to be added to this collection. In this way, when the query runs, then the value of the parameter is compared literally with the value stored in the database. Hence, in no way, a user can compromise the database by using SQL Injection attacks.

To clarify let us take an example of a parameterized query that we use in the insert operation. Therefore, let us create a table with the name Student as shown below. The following database table consists of six fields. Therefore, we create a parameter for each field.

Structure of Student Table
Structure of Student Table

SqlParameter Class

The namespace System.Data.SqlClient provides us a class called SqlParameter that represents the parameter in the command object of an SQL query. Evidently, the class contains important properties like ParameterName, DbType, Size, and the Direction. In brief, the ParameterName is the name of the parameter that we use in the query whereas, the DbType and Size represent the data type of the parameter and its size respectively. On the other hand, the Direction property represents whether the parameter is input-only, output-only, or bidirectional.

SqlParameterCollection Class

The same namespace (System.Data.SqlClient) offers another important class called SqlParameterCollection that represents a collection of parameters for an object of SqlCommand. Also, mapping to the corresponding columns of the DataSet is also represented by the instance of this class. Although there are several methods offered by this class, the two important methods are Add() and addWithValue(). The usage of these two methods is shown in the following examples.

Example of Parameter and ParameterCollection

The following example shows how to insert a row in the above-mentioned Student table using a parameterized query. As shown in the InsertRecords() method, the insert query makes use of the parameters which are added using the AddWithValue() method. Also, the Parameters property of the command represents the parameter collection.

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:\Users\KAVITA\Documents\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();
        }
    }
}

Output

Using Parameters
Using Parameters

Using the Add() Method

In like manner, we can use Add() method to add a parameter to the Parameter Collection. Here, we pass the name of the parameter, its data type, and the size in the method call.

Adding Parameters

Similarly, we can add parameters in the command object for all of the six fields contained in the table. The following code example demonstrates how to add parameters using the Add() method. Also, how to add values to these parameters is also shown in the following code.

            cmd = new SqlCommand();
            cmd.Connection = c1;
            cmd.CommandText = "insert into Student values(@a, @b, @c, @d, @e, @f)";
            cmd.Parameters.Add("@a", SqlDbType.Int).Value = 1103;
            cmd.Parameters.Add("@b", SqlDbType.NVarChar, 50).Value = "C";
            cmd.Parameters.Add("@c", SqlDbType.NVarChar, 10).Value = "Course";
            cmd.Parameters.Add("@d", SqlDbType.NVarChar, 10).Value = "2021-2023";
            cmd.Parameters.Add("@e", SqlDbType.Int).Value =3;
            cmd.Parameters.Add("@f", SqlDbType.NVarChar, 100).Value = "abcd@gmail.com";

Accordingly, we can use either AddWithValue() method or Add() method for using parameterized queries. However, the Add() method provides us the benefit to provide the attributes such as the type and size of a parameter.

Summary

In this article I have explained the importance of Parameter and ParameterCollection that we can use to write the parameterized queries. As can be seen, it is straightforward to write parameterized queries in C# that prevent SQL Injection attacks on the database.


Related Topics

A Beginner’s Tutorial on WPF in C#

Everything about Tuples in C# and When to Use?

Linear Search and Binary Search in C#

Creating Jagged Arrays in C#

Learning Indexers in C#

Understanding Method Parameter Modifiers in C#

Object Initializers in C#

Examples of Static Constructors in C#

When should We Use Private Constructors?

C# Practice Questions

C# Basic Examples

Private and Static Constructors in C#

Constructors in C#

C# Arrays

C# Examples

How to Create a C# Console Application

Creating Navigation Window Application Using WPF in C#

LINQ To SQL Examples

Understanding the Concept of Nested Classes in C#

How to Setup a Connection with SQL Server Database in Visual Studio

Examples of Extension Methods in C#

Learning All Class Members in C#

Access Modifiers in C#

C# Root Class – Object

KeyValuePair and its Applications

IEnumerable and IEnumerator Interfaces

IEqualityComparer Interface

New Features in C# 9

Generic IList Interface and its Implementation in C#

Examples of Connected and Disconnected Approach in ADO.NET

Leave a Reply

Your email address will not be published. Required fields are marked *