What is ADO.NET SqlCommand Class in C#?. The Complete ADO.NET Developer Course 2023 [Videos].

In this Video, I am going to discuss the ADO.NET SqlCommand Class in C# with Examples. Please read our previous Video where we discussed ADO.NET SqlConnection Class. As part of this Video, we are going to discuss the following pointers in detail which are related to C# SqlCommand object.

1. What is SqlCommand Class and its need in C#?
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 in C#?

The ADO.NET SqlCommand class in C# 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.

Constructors of ADO.NET SqlCommand Class in C#

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

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 is 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 are the same as the 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 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 ADO.NET SqlCommand Object in C#:

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

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

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

ExecuteReader Method of SqlCommand Object in C#:

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

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

Understanding the ADO.NET SqlCommand Object in C#:

In 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.

You can also create the SqlCommand object using the parameterless 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.

ExecuteScalar Method of SqlCommand Object in C#:

When your 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 in C#. 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.

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

ExecuteNonQuery Method of ADO.NET SqlCommand Object in C#:

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 in C#. Let us understand this with an example. The following example performs an Insert, Update and Delete operations using the ExecuteNonQuery() method.

Output:

So, in short, we can say that the SqlCommand Object in C# is used to prepare the command text (T-SQL statement or Stored Procedure) that you want to execute against the SQL Server database and also provides some methods (ExecuteReader, ExecuteScalar, and ExecuteNonQuery) to execute those commands.