ADO.NET SqlCommand Class in C# with Examples
Posted by Superadmin on November 15 2023 10:39:00

ADO.NET SqlCommand Class in C# with Examples

In this article, I am going to discuss the ADO.NET SqlCommand Class in C# with Examples. 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, which are related to the 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# stores and executes the SQL statement against the SQL Server database. As you can see in the image below, the SqlCommand class is a sealed class and is inherited from the DbCommand class and implements 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 initialize a new instance of the System.Data.SqlClient.SqlCommand class.

 

SqlCommand(string cmdText):

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

 

SqlCommand(string cmdText, SqlConnection connection):

It is used to initialize 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 initialize 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 initialize a new instance of the System.Data.SqlClient.SqlCommand class with specified command text, connection, transaction, and encryption settings. 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.

Example to understand the SqlCommand Object in ADO.NET

 

Please use the below SQL script to create a database called StudentDB and a table called Student with the required sample 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 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 sends 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 execute 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 ADO.NET SqlCommand Object in C#:

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

How to create an instance of the SqlCommand class

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 example below.

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 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. It will 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 the ExecuteScalar method is an object, so here, we need to typecast it into an integer type. If you execute the above program, you will get the following output.

 

ExecuteScalar Method of SqlCommand Object

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

When you want to perform Insert, Update, or Delete operations and return the number of rows affected by your query, you need to use the ExecuteNonQuery method of the SqlCommand object in C#. Let us understand this with an example. The following example performs 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

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.

Summary of ADO.NET SqlCommand Class:

The SqlCommand class in ADO.NET is critical for executing SQL commands and stored procedures against a data source, such as a SQL Server database. It’s part of the ADO.NET library and provides methods and properties to define, execute, and retrieve results from database commands. Here’s an overview of the SqlCommand class and its usage:

 

Import the Namespace:

To use the SqlCommand class, you need to include the System.Data.SqlClient namespace in your code:
using System.Data.SqlClient;

Create a SqlConnection:

Before using SqlCommand, you need an open SqlConnection object to execute commands. You can create and open a connection using the SqlConnection class described in the previous response.

Create and Execute Commands:

You can create an instance of SqlCommand and associate it with an open connection to execute various types of commands:

SELECT Query:
For executing SELECT queries and retrieving data:

string sqlQuery = ""select * from student"";
SqlCommand command = new SqlCommand(sqlQuery, connection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader["Name"] + ", " + reader["Email"] + ", " + reader["Mobile"]);
}
reader.Close();

Non-Query Command (INSERT, UPDATE, DELETE):
For executing commands that don’t return data (e.g., INSERT, UPDATE, DELETE):

string insertQuery = "Insert into Student values (105, 'Ramesh', 'Ramesh@dotnettutorial.net', '1122334455')";
SqlCommand insertCommand = new SqlCommand(insertQuery, connection);
int rowsAffected = insertCommand.ExecuteNonQuery();

Stored Procedures:
For executing stored procedures:

SqlCommand spCommand = new SqlCommand("StoredProcedureName", connection);
spCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parameter = new SqlParameter("@ParameterName", SqlDbType.VarChar);
parameter.Value = "ParameterValue";
spCommand.Parameters.Add(parameter);
// Execute the stored procedure
SqlDataReader spReader = spCommand.ExecuteReader();

Parameterized Queries:
It’s highly recommended to use parameterized queries to prevent SQL injection. You can add parameters to your command to safely pass values:

string sqlQuery = "SELECT * FROM Employees WHERE Department = @Department";
SqlCommand paramCommand = new SqlCommand(sqlQuery, connection);
paramCommand.Parameters.AddWithValue("@Department", "IT");
SqlDataReader paramReader = paramCommand.ExecuteReader();
Dispose of Resources:

Always close and dispose of resources when you’re done with them:
command.Dispose();
connection.Close();
connection.Dispose();

Error Handling:
Wrap your code with appropriate error handling to catch exceptions that might occur during database interactions.

Using Statement (Recommended):

To ensure that resources are properly disposed of, use the using statement:

 

using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sqlQuery, connection))
{
// Execute commands and retrieve data
} // Command is automatically disposed here
} // Connection is automatically closed and disposed here

The SqlCommand class provides methods and properties for a variety of use cases, allowing you to interact with an SQL Server database efficiently and securely.

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