Back to: ADO.NET Tutorial For Beginners and Professionals
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 C# SqlCommand object.
- What is SqlCommand Class and its need in C#?
- How to create an instance of the SqlCommand class.
- Understanding the constructors and methods of SqlCommand Class.
- 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 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 text of the query that 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.
- BeginExecuteNonQuery(): This method initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this System.Data.SqlClient.SqlCommand.
- Cancel(): This method tries to cancel the execution of a System.Data.SqlClient.SqlCommand.
- Clone(): This method creates a new System.Data.SqlClient.SqlCommand object is a copy of the current instance.
- CreateParameter(): This method creates a new instance of a System.Data.SqlClient.SqlParameter object.
- ExecuteReader(): This method Sends the System.Data.SqlClient.SqlCommand.CommandText to the System.Data.SqlClient.SqlCommand.Connection and builds a System.Data.SqlClient.SqlDataReader.
- 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.
- ExecuteNonQuery(): This method executes a Transact-SQL statement against the connection and returns the number of rows affected.
- Prepare(): This method creates a prepared version of the command on an instance of SQL Server.
- 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, 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 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 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.
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.
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. 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 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 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:
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.
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 in C# with examples. I hope this C# SqlCommand article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.
