ADO.NET SqlCommand Class

If a .NET application (Web, Desktop, Console, etc.) has to retrieve data, the application needs to:

1. Connect to the Database
2. Prepare an SQL Command
3. Execute the Command
4. Retrieve the results and display in the application

This is the normal flow which is used by any .NET application, not only to retrieve data but also to insert, update or delete data. In our previous article Read here, we learned how to establish a connection to a SQL Server database properly and, at the same time, how to close the connection properly.

The next step is to execute the command on the server, and the SqlCommand class helps us to achieve this requirement. The SqlCommand class is used to prepare an SQL statement or Stored Procedure that we want to execute on a SQL Server database. In this article, we will discuss executing Transact-SQL statements on a SQL Server. In a later article, we will learn how to execute stored procedures using the SqlCommand class.

SqlCommand Signature

It is a sealed class, which means it cannot be inherited.

public sealed class SqlCommand : System.Data.Common.DbCommand, ICloneable, IDisposable

Constructors

This class provides the following constructors.

We can use any of the above versions of SqlCommand as per our convenience and requirement.

Important Methods of SqlCommand Class

Among all specified methods, the following are the most commonly used methods of the SqlCommand class:

• ExecuteReader - It is used when the T-SQL statement returns more than a single value. For example, if the query returns rows of data, such as all employee details.
• ExecuteNonQuery - It is used when you want to perform an Insert, Update, or Delete operation, like update or delete an employee detail or insert employee data.
• ExecuteScalar - It is used when the query returns a single(scalar) value. For example, queries that return the total number of rows in a table, or the employee who gets the highest salary, etc.

Let's do some practical demonstration:

We will be using the Employee table for our example. Execute the following SQL script using SQL Server Management Studio. The script creates:-

1. Test Database.
2. Employee table under Test Database. And
3. Populate it with some data

Create Database Test
Go
Use Test
Go
CREATE TABLE [dbo].[Employee] (
[Id]     INT          IDENTITY (1, 1) NOT NULL,
[Name]   VARCHAR (50) NULL,
[Salary] INT          NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC)
);
Go
SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (1, N'sachin', 100000)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (2, N'arjun', 23000)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (3, N'vikash', 30000)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (4, N'pocha', 43000)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (5, N'Nivedita', 24000)
SET IDENTITY_INSERT [dbo].[Employee] OFF
Go

We know there are multiple constructors available for the SqlCommand class; among all, two of them are widely used to create an instance of the SqlCommand class.

1. The parameterized constructor which takes two parameters:
The first parameter is a string, where we specify the command we want to execute on the server or the stored procedure name. The second parameter is of the connection type, where we specify the SqlConnection object.

protected void Page_Load(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection("data source=.; database=Test; integrated security=true"))
{
//Create an instance of the SqlCommand class, specifying the T-SQL command that
//We want to execute the connection object.
SqlCommand cmd = new SqlCommand("select * from Employee", con);
con.Open();
//As the T-SQL statement that we want to execute returns multiple rows of data, 
//Use ExecuteReader() method of the command object.
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
}
}

The sample code above executes a T-SQL statement that returns multiple rows of data using the ExecuteReader() method. In this example, we create an instance of the SqlCommand class in a single line by passing in the command text and the connection object. For this purpose, we are using an overloaded constructor of the SqlCommand class that takes two parameters: the command text and the connection.

2. The parameterless constructor of the SqlCommand class.
Here, we first instantiate the SqlCommand Class using the parameterless constructor. We then specify the command text and connection using the CommandText and Connection properties of the SqlCommand object, as shown below:

using (SqlConnection con = new SqlConnection("data source=.; database=Test; integrated security=true"))
{
//Create an instance of the SqlCommand class using the parameterless constructor
SqlCommand cmd = new SqlCommand();
//Specify the command we want to execute using the CommandText property
cmd.CommandText = "Select * from Employee";
//Specify the connection on which we want to execute the command
//using the Connection property
cmd.Connection = con;
connection.Open();
//As the T-SQL statement that we want to execute returns multiple rows of data,
//use ExecuteReader() method of the command object.
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
}
}

Example of ExecuteScalar()

In the example below, we are using the ExecuteScalar() method, as the T-SQL statement returns a single value:

protected void Page_Load(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection("data source=.; database=Test; integrated security=true"))
{
//Create an instance of the SqlCommand class, specifying the T-SQL command
//that we want to execute, and the connection object.
SqlCommand cmd = new SqlCommand("Select Count(Id) from Employee", con);
con.Open();
//As the T-SQL statement that we want to execute returns a single value,
//use ExecuteScalar() method of the command object.
//Since the return type of ExecuteScalar() is object, we are type casting to int datatype
int TotalRows = (int)cmd.ExecuteScalar();
Response.Write("Total Rows = " + TotalRows.ToString());
}
}

Example of ExecuteNonQuery()

The following examples perform the Insert, Update and Delete operations on a SQL Server database using the ExecuteNonQuery() method of the SqlCommand object:

protected void Insert()
{
using (SqlConnection con = new SqlConnection("data source=.; database= Test; integrated security=true"))
{
//Create an instance of the SqlCommand class, specifying the T-SQL command
//that we want to execute, and the connection object.
SqlCommand cmd = new SqlCommand("insert into Employee values ( 'Anurag',78000)", con);
connection.Open();
//Since we are performing an insert operation, use ExecuteNonQuery()
//method of the command object. ExecuteNonQuery() method returns 
//number of rows affected (inserted)
int rowsAffected = cmd.ExecuteNonQuery();
Response.Write("Inserted Rows = " + rowsAffected.ToString() + "
");
}
}

protected void Update()
{
using (SqlConnection con = new SqlConnection("data source=.; database= Test; integrated security=true"))
{
//Create an instance of the SqlCommand class
SqlCommand cmd = new SqlCommand();
//Set CommandText to the update query
cmd.CommandText = "update Employee set Name = @Name  where Id =@Id ";
cmd.Parameters.AddWithValue("@Name",txtName.Text);
cmd.Parameters.AddWithValue("@Id",txtId.Text);
//use ExecuteNonQuery() method to execute the update statement on the database
rowsAffected = cmd.ExecuteNonQuery();
Response.Write("Updated Rows = " + rowsAffected.ToString() + "
");
}
}

protected void  Delete(int Id)
{
using (SqlConnection con = new SqlConnection("data source=.; database= Test; integrated security=true"))
{
//Create an instance of the SqlCommand class, 
SqlCommand cmd = new SqlCommand();
//Set CommandText to the delete query. 
cmd.CommandText = "Delete from Employee where Id = Id ";
 //use ExecuteNonQuery() method to delete the row from the database
 rowsAffected = cmd.ExecuteNonQuery();
 Response.Write("Deleted Rows = " + rowsAffected.ToString() + "
");
 }
}