Posted on 12/23/2020 4:31:13 AM by Admin

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 a 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 session, we will discuss about executing Transact-SQL statements on a SQL Server. In a later session we will learn about executing stored procedures using the SqlCommand class.

SqlCommand Signature

It is a sealed class so that 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 a string 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. Methods.

Method Description
BeginExecuteNonQuery() It is used to Initiate the asynchronous execution of the SQL statement described by this SqlCommand.
Cancel() It tries to cancel the execution of a SqlCommand.
Clone() It creates a new SqlCommand object that is a copy of the current instance.
CreateParameter() It creates a new instance of a SqlParameter object.
ExecuteReader() It is used to send the CommandText to the Connection and builds a SqlDataReader.
ExecuteXmlReader() It is used to send the CommandText to the Connection and builds an XmlReader object.
ExecuteScalar() It executes the query and returns the first column of the first row in the result set. Additional columns or rows are ignored.
Prepare() It is used to create a prepared version of the command by using the instance of SQL Server.
ResetCommandTimeout() It is used to reset the CommandTimeout property to its default value.

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 Store 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=Sample_Test_DB; integrated security=SSPI"))
    {
        //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(cmdText, 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 parameter less 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 example performs an Insert, Update and Delete operations on a SQL server database using the ExecuteNonQuery() method of the SqlCommand 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("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 an
        //integer, which specifies the number of rows inserted
        int rowsAffected = cmd.ExecuteNonQuery();
        Response.Write("Inserted Rows = " + rowsAffected.ToString() + "
"); //Set to CommandText to the update query. We are reusing the command object, //instead of creating a new command object cmd.CommandText = "update Employee set Name = "Ram" where Id =2 "; //use ExecuteNonQuery() method to execute the update statement on the database rowsAffected = cmd.ExecuteNonQuery(); Response.Write("Updated Rows = " + rowsAffected.ToString() + "
"); //Set to CommandText to the delete query. We are reusing the command object, //instead of creating a new command object cmd.CommandText = "Delete from Employee where Id = 4"; //use ExecuteNonQuery() method to delete the row from the database rowsAffected = cmd.ExecuteNonQuery(); Response.Write("Deleted Rows = " + rowsAffected.ToString() + "
"); } }