ADO.NET SqlCommand Class

If a .NET application (Web, Desktop, Console etc.) has to retrieve data, then 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 learnt , how to establish a connection to SQL server database properly and at the same time we also learnt to close the connection properly .

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

SqlCommand Signature

It is a sealed class means it cannot be inherited.


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

Constructors

This class provides the following constructors.

Constructor	Description
SqlCommand()	It is used to initialize a new instance of the SqlCommand class.
SqlCommand(String)	It is used to initialize a new instance of the SqlCommand class with T-Sql statement as parameter.
SqlCommand(String, SqlConnection)	It is used to initialize a new instance of the SqlCommand class. It takes two parameters, first is query string and second is connection string.
SqlCommand(String, SqlConnection, SqlTransaction)	It is used to initialize a new instance of the SqlCommand class. It takes three parameters query, connection and transaction string respectively.
SqlCommand(String, SqlConnection, SqlTransaction, SqlCommandColumnEncryptionSetting)	It Initializes a new instance of the SqlCommand class with specified command text, connection, transaction, and encryption setting.

We can use any of the above version of Sql Command as per our convenient and requirement.

Important Methods of SqlCommand Class

Method	Description
BeginExecuteNonQuery()	use it to asynchronously execute the command
CreateParameter()	use it to create a new instance of a SqlParameter object.
ExecuteReader()	Use it to fetch multiple rows from Sql Server
ExecuteScalar()	Use it to fetch a single value from server, It executes the query and returns the first column of the first row in the result set, Additional columns or rows are simply ignored.

Among all specified methods, 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 like 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 highest salary etc.

Let's do some practical demonstration

We will be using 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
&emsp 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 SqlCommand Class , among all, two of them are widely used to create an instance of SqlCommand Class.

1.The Parameterized constructor which takes two parameter
The first parameter is a string here we specify the command we want to execute on server or the Stored Procedure Name and the second parameter is of 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 SqlCommand class, specifying the T-SQL command that
        //we want to execute, and the connection object.
        SqlCommand cmd = new SqlCommand("select * from Employee", con);
        con.Open();
        //As the T-SQL statement that we want to execute return 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 are creating an instance of SqlCommand class, in just one 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 2 parameters first the command Text, and, second the connection.

2. The parameter less constructor of SqlCommand Class.
Here , we first instantiate the SqlCommand Class using the parameter less constructor then later 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 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 return multiple rows of data,
        //use ExecuteReader() method of the command object.
        GridView1.DataSource = cmd.ExecuteReader();
        GridView1.DataBind();
    }
}

Example of ExecuteScalar()

Return the count of all Employee in table.

In the example below, we are using 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 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 return 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()

Insert ,Update and Delete operation on Employee table

The following examples perform an 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 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 SqlCommand class, 
        SqlCommand cmd = new SqlCommand();

        //Set to 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 SqlCommand class, 
        SqlCommand cmd = new SqlCommand();
        
        //Set to 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() + "");
    }
}