ADO.NET SqlCommand

ADO.NET SqlCommand Class

In this article, I am going to discuss the ADO.NET SqlCommand Class in detail. Please read our previous article where we discussed ADO.NET SqlConnection Class. As part of this article, we are going to discuss the following pointers in detail.

  1. What is SqlCommand Class and its need?
  2. How to create an instance of the SqlCommand class.
  3. Understanding the constructors and methods of SqlCommand Class.
  4. When to use ExecuteReader(), ExecuteScalar() and ExecuteNonQuery() methods of the SqlCommand object.
What is ADO.NET SqlCommand Class?

The ADO.NET SqlCommand class is used to store and execute the SQL statement against the SQL Server database. As you can see in the below image, the SqlCommand class is a sealed class and is inherited from the DbCommand class and implement the ICloneable interface. As a sealed class, it cannot be inherited.

ADO.NET SqlCommand Class

Constructors of ADO.NET SqlCommand Class in C#

The SqlCommand class in C# provides the following five constructors.

Constructors of ADO.NET SqlCommand Class in C#

Let us discuss each of these constructors in detail.

SqlCommand():

This constructor is used to initializes a new instance of the System.Data.SqlClient.SqlCommand class..

SqlCommand(string cmdText):

It is used to initializes a new instance of the System.Data.SqlClient.SqlCommand class with the text of the query. Here, the cmdText is the text of the query that we want to execute.

SqlCommand(string cmdText, SqlConnection connection):

It is used to initializes a new instance of the System.Data.SqlClient.SqlCommand class with the text of the query and a System.Data.SqlClient.SqlConnection. Here, the cmdText is the text of the query that we want to execute and the parameter connection is the connection to an instance of SQL Server.

SqlCommand(string cmdText, SqlConnection connection, SqlTransaction transaction):

It is used to initializes a new instance of the System.Data.SqlClient.SqlCommand class with the text of the query, a SqlConnection instance, and the SqlTransaction instance. Here, the parameter cmdText is the text of the query. The parameter connection a SqlConnection that represents the connection to an instance of SQL Server and the parameter transaction is the SqlTransaction in which the SqlCommand executes.

SqlCommand(string cmdText, SqlConnection connection, SqlTransaction transaction, SqlCommandColumnEncryptionSetting columnEncryptionSetting):

It is used to initializes a new instance of the System.Data.SqlClient.SqlCommand class with specified command text, connection, transaction, and encryption setting. We already discussed the first three parameters which is same as previous. Here, the fourth parameter i.e. columnEncryptionSetting is the encryption setting.

Methods of SqlCommand Class in C#

The SqlCommand class in C# provides the following methods.

  1. BeginExecuteNonQuery(): This method initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this System.Data.SqlClient.SqlCommand.
  2. Cancel(): This method tries to cancel the execution of a System.Data.SqlClient.SqlCommand.
  3. Clone(): This method creates a new System.Data.SqlClient.SqlCommand object that is a copy of the current instance.
  4. CreateParameter(): This method creates a new instance of a System.Data.SqlClient.SqlParameter object.
  5. ExecuteReader(): This method Sends the System.Data.SqlClient.SqlCommand.CommandText to the System.Data.SqlClient.SqlCommand.Connection and builds a System.Data.SqlClient.SqlDataReader.
  6. ExecuteScalar(): This method Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
  7. ExecuteNonQuery(): This method executes a Transact-SQL statement against the connection and returns the number of rows affected.
  8. Prepare(): This method creates a prepared version of the command on an instance of SQL Server.
  9. ResetCommandTimeout(): This method resets the CommandTimeout property to its default value.
Example to understand the SqlCommand Object in ADO.NET:

We are going to use the following student table to understand the SqlCommand object.

Example to understand the SqlCommand Object in ADO.NET

Please use below SQL script to create a database called StudentDB, a table called Student with the required test data.

CREATE DATABASE StudentDB;
GO

USE StudentDB;
GO

CREATE TABLE Student(
 Id INT PRIMARY KEY,
 Name VARCHAR(100),
 Email VARCHAR(50),
 Mobile VARCHAR(50)
)
GO

INSERT INTO Student VALUES (101, 'Anurag', 'Anurag@dotnettutorial.net', '1234567890')
INSERT INTO Student VALUES (102, 'Priyanka', 'Priyanka@dotnettutorial.net', '2233445566')
INSERT INTO Student VALUES (103, 'Preety', 'Preety@dotnettutorial.net', '6655443322')
INSERT INTO Student VALUES (104, 'Sambit', 'Sambit@dotnettutorial.net', '9876543210')
GO

Note: ExecuteReader, ExecuteNonQuery, and ExecuteScalar are the methods which are commonly used. Let us see three examples to understand these methods,

ExecuteReader method of SqlCommand Object:

As we already discussed this method is used to send the CommandText to the Connection and builds a SqlDataReader. When your T-SQL statement returns more than a single value (for example rows of data), then you need to use ExecuteReader method.

Let us understand this with an example. The following example uses the ExecuteReader method of SqlCommand object to executes the T-SQL statement which returns multiple rows of data.

using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConString = "data source=.; database=StudentDB; integrated security=SSPI";
                using (SqlConnection connection = new SqlConnection(ConString))
                {
                    // Creating SqlCommand objcet   
                    SqlCommand cm = new SqlCommand("select * from student", connection);

                    // Opening Connection  
                    connection.Open();

                    // Executing the SQL query  
                    SqlDataReader sdr = cm.ExecuteReader();
                    while (sdr.Read())
                    {
                        Console.WriteLine(sdr["Name"] + ",  " + sdr["Email"] + ",  " + sdr["Mobile"]);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }
            Console.ReadKey();
        }
    }
}

Once you execute the program, you will get the following output as expected.

ExecuteReader method of SqlCommand Object

Understanding the SqlCommand Object:

In the our example, we are creating an instance of the SqlCommand by using the constructor which takes two parameters as shown in the below image.The first parameter is the command text that we want to execute, and the second parameter is the connection object which provides the database details on which the command is going to execute.

How to create an instance of the SqlCommand class

You can also create the SqlCommand object using the parameter less constructor, and later you can specify the command text and connection using the CommandText and the Connection properties of the SqlCommand object as shown in the below example.

using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConString = "data source=.; database=StudentDB; integrated security=SSPI";
                using (SqlConnection connection = new SqlConnection(ConString))
                {
                    // Creating SqlCommand objcet 
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandText = "select * from student";
                    cmd.Connection = connection;

                    // Opening Connection  
                    connection.Open();

                    // Executing the SQL query  
                    SqlDataReader sdr = cmd.ExecuteReader();
                    while (sdr.Read())
                    {
                        Console.WriteLine(sdr["Name"] + ",  " + sdr["Email"] + ",  " + sdr["Mobile"]);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }
            Console.ReadKey();
        }
    }
}
ExecuteScalar Method of SqlCommand Object:

When you T-SQL query or stored procedure returns a single(i.e. scalar) value then you need to use the ExecuteScalar method of the SqlCommand object.

Let us understand this with an example. Now, we need to fetch the total number of records present in the Student table. As we know it is going to return a single value, so this is an ideal situation to use the ExecuteScalar method. The following example will retrieve the total number of records present in the Student table.

using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConString = "data source=.; database=StudentDB; integrated security=SSPI";
                using (SqlConnection connection = new SqlConnection(ConString))
                {
                    // Creating SqlCommand objcet 
                    SqlCommand cmd = new SqlCommand("select count(id) from student", connection);

                    // Opening Connection  
                    connection.Open();

                    // Executing the SQL query  
                    // Since the return type of ExecuteScalar() is object, we are type casting to int datatype
                    int TotalRows = (int)cmd.ExecuteScalar();

                    Console.WriteLine("TotalRows in Student Table :  " + TotalRows);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }
            Console.ReadKey();
        }
    }
}

The return type of ExecuteScalar method is object, so here we need to type cast it into integer type. Now, if you execute the above program, then you will get the following output.

ExecuteScalar Method of SqlCommand Object

ExecuteNonQuery Method of SqlCommand Object:

When you want to perform Insert, Update or Delete operations and want to return the number of rows affected by your query then you need to use the ExecuteNonQuery method of the SqlCommand object.

Let us understand this with an example. The following example performs an Insert, Update and Delete operations using the ExecuteNonQuery() method.

using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConString = "data source=.; database=StudentDB; integrated security=SSPI";
                using (SqlConnection connection = new SqlConnection(ConString))
                {

                    SqlCommand cmd = new SqlCommand("insert into Student values (105, 'Ramesh', 'Ramesh@dotnettutorial.net', '1122334455')", connection);

                    connection.Open();
                    int rowsAffected = cmd.ExecuteNonQuery();
                    Console.WriteLine("Inserted Rows = " + rowsAffected);

                    //Set to CommandText to the update query. We are reusing the command object, 
                    //instead of creating a new command object
                    cmd.CommandText = "update Student set Name = 'Ramesh Changed' where Id = 105";
                    rowsAffected = cmd.ExecuteNonQuery();
                    Console.WriteLine("Updated Rows = " + rowsAffected);

                    //Set to CommandText to the delete query. We are reusing the command object, 
                    //instead of creating a new command object
                    cmd.CommandText = "Delete from Student where Id = 105";
                   
                    rowsAffected = cmd.ExecuteNonQuery();
                    Console.WriteLine("Deleted Rows = " + rowsAffected);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }
            Console.ReadKey();
        }
    }
}

Output:

ExecuteNonQuery Method of SqlCommand Object

In the next article, I am going to discuss the ADO.NET SqlDataReader class in detail. Here, in this article, I try to explain the ADO.NET SqlCommand class with examples. I hope this article will helps you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

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