Working With Command Object in ADO.NET

In this article, we will learn how to work with the Command Object in ADO .NET and the Execute Methods in the Command Object.

 

Command Object

 

The command object is one of the basic components of ADO .NET.

1. The Command Object uses the connection object to execute SQL queries.
2. The queries can be in the Form of Inline text, Stored Procedures or direct Table access.
3. An important feature of Command object is that it can be used to execute queries and Stored Procedures with Parameters.
4. If a select query is issued, the result set it returns is usually stored in either a DataSet or a DataReader object.

Associated Properties of SqlCommand class

 

The properties associated with SqlCommand class are shown in the Table below.

 

Property	Type of Access	Description
Connection	Read/Write	The SqlConnection object that is used by the command object to execute SQL queries or Stored Procedure.
CommandText	Read/Write	Represents the T-SQL Statement or the name of the Stored Procedure.
CommandType	Read/Write	This property indicates how the CommandText property should be interpreted. The possible values are: Text (T-SQL Statement) StoredProcedure (Stored Procedure Name) TableDirect
CommandTimeout	Read/Write	This property indicates the time to wait when executing a particular command.   Default Time for Execution of Command is 30 Seconds.   The Command is aborted after it times out and an exception is thrown.

 

Now, Let us have a look at various Execute Methods that can be called from a Command Object.

 

Property	Description
ExecuteNonQuery	This method executes the command specifies and returns the number of rows affected.
ExecuteReader	The ExecuteReader method executes the command specified and returns an instance of SqlDataReader class.
ExecuteScalar	This method executes the command specified and returns the first column of first row of the result set. The remaining rows and column are ignored.
ExecuteXMLReader	This method executes the command specified and returns an instance of XmlReader class. This method can be used to return the result set in the form of an XML document

 

These are quite often methods in the Command objects. Let us now see each of these with an example

 

ExecuteNonQuery

1. The ExecuteNonQuery method is used to execute the command and return the number of rows affected.
2. The ExecuteNonQuery method cannot be used to return the result set.

Snippets working with ExecuteNonQuery

public void CallExecuteNonQuery()
{
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
    try
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "DELETE FROM EMP WHERE DEPTNO = 40";
        cmd.CommandType = CommandType.Text;
        conn.Open();
        Int32 RowsAffected = cmd.ExecuteNonQuery();
        MessageBox.Show(RowsAffected + " rows affected", "Message");
        cmd.Dispose();
        conn.Dispose();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Here we have configured a connection string for a Data Source in the App.config file. The content of the App.config file is shown below.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name ="connString" connectionString ="Data Source=ServerName;Initial Catalog=DatabaseName;User Id=User Name; Password=password;" providerName ="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

To reference this connection we need to add a reference to System.Configuration namespace from:

 

"Project" –> "Add Reference"

 

 

 

And add the namespace using System.Configuration;

 

In the code snippet, we create an instance of a SqlCommand class; it can be a SqlConnection class. We pass the command object the Connection, CommandText, CommandType etc. The connection is then opened, and the command is executed against the connection and the affected rows are returned by the ExecuteNonQuery Method. Then we dispose the command object and the connection object is created.

 

ExecuteReader Method

1. The DataReader object is a forward-only and read-only cursor.
2. It requires a live connection to the Data Source.
3. The DataReader object cannot be directly instantiated. Instead, we must call the ExecuteReader() Method of the command object to obtain a valid DataReader object.
   

   SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

Coding

public void CallExecuteReader()
{
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
    try
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "SELECT EMPNO,ENAME FROM EMP";
        cmd.CommandType = CommandType.Text;
        conn.Open();
        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                MessageBox.Show("Employee No: " + reader["EMPNO"].ToString() + " Name :" + reader["ENAME"].ToString());
            }
        }
        cmd.Dispose();
        conn.Dispose();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

The reader.HasRows property returns a boolean value indicating whether rows are returned by the method.

 

The reader.Read() is used to loop through the result set that is returned by the ExecuteReader method.

 

ExecuteScalar Method

1. The ExecuteScalar Method in SqlCommandObject returns the first column of the first row after executing the query against the Data Source.
2. If the result set contains more than one column or rows, it takes only the first column of the first row. All other values are ignored.
3. If the result set is empty it will return null.

Coding

public void CallExecuteScalar()
{
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
    try
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "SELECT SUM(SAL) SAL FROM EMP";
        cmd.CommandType = CommandType.Text;
        conn.Open();
        Int32 TotalSalary = Convert.ToInt32(cmd.ExecuteScalar());
        MessageBox.Show("Total Salary is : " + TotalSalary.ToString());
        cmd.Dispose();
        conn.Dispose();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

It is best to use ExecuteScalar Method when we use functions like SUM(),COUNT() etc. since it uses fewer resources than the ExecuteReader method.

 

ExecuteXmlReader

 

The execute reader method is flexible when we need the result set in the form of an XML document. The ExecuteXmlReader methods returns an Instance of XmlReader class.

 

Coding

public void CallExecuteRow()
{
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
    try
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM EMP FOR XML RAW('EMPLOYEE'), ROOT('EMP'), ELEMENTS", conn);
        conn.Open();
        XmlReader xmlreader = cmd.ExecuteXmlReader();
        XmlDocument xdoc = new XmlDocument();
        while (xmlreader.Read())
        {
            xdoc.Load(xmlreader);
        }
        xdoc.Save("D:\\Employees.xml");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Here we create an instance of the SqlConnection class and create a SqlCommand connection and pass the SqlCommand class the SQL Statement that returns the XML data. Using the XmlDocument class we load the XmlReader object and save it to the File System using the Save method.

 

Summary

 

In this article we have discussed the Command Object, one of the basic components of ADO .NET data management and its properties and methods that can be called from the Command Object.