Jitesh Byahut 11/23/2014 2152

Command Object In ADO.NET

Command object is used to perform database commands. Its helps to execute select, insert, update, delete, drop, alter statements or executing stored procedure or view. Its works on connection oriented architecture. The System.Data.Common.DBCommand class is the base class for all provider Command classes. All the command related to sql server is inside System.Data.SqlClient.SqlCommand class.

Before executing command object, you have specify connection string and open that connection.

using System.Data.SqlClient; 

 

SqlConnection con = new SqlConnection();

con.ConnectionString = "Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;";

SqlCommand cmd = new SqlCommand();

con.Open();

//

// Your sql command object logics

//

con.Close();

In above example, we worked with sql server database, but if you want to work with OleDb then specify the System.Data.OleDb namespace.

Properties of Command Class

CommandText

CommandText is used to get or set sql query, table name or stored procedure that execute.

CommandTimeout

Get or set the command timeout in seconds before terminating the execution. default timeout in 30 seconds.

CommandType

CommandType used to get or set command text that will execute. Options are Text, StoredProcedure and TableDirect.

Connection

Used to get or set connection object that will help to connect database.

Parameters

Parameters used to pass parameter name and that value for execution stored procedure.

Transaction

Used to get or set sql transaction that helps to execute queries of database. If you sql statments will throw error then you can rollback you privious data using transaction.

Below example demonstrate all the properties of command class:

using System.Data;

using System.Data.SqlClient;

SqlConnection con = new SqlConnection();

con.ConnectionString = "Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;";

con.Open();

SqlTransaction transaction = con.BeginTransaction();

try 

{

    SqlCommand cmd = new SqlCommand();

    // Set Connection

    cmd.Connection = con;

    // Set CommandType

    cmd.CommandType = CommandType.StoredProcedure;

    // Set CommandText

    cmd.CommandText = "sp_test";

    // Define Parameters

    cmd.Parameters.AddWithValue("@Name", "Jitesh");

    cmd.Parameters.AddWithValue("@Ponits", 100);

    // Set Transaction

    cmd.Transaction = transaction;

    // Set Timeout

    cmd.CommandTimeout = 35;

    // Execute Command

    cmd.ExecuteNonQuery();

 

    transaction.Commit();

}

catch (Exception Ex)

{

    transaction.Rollback();

}

con.Close();

Methods of Command Class

ExecuteNonQuery

ExecuteNonQuery execute command specified and returns the number of affected rows. Its return type is integer. It's very useful to execute insert, update and delete queries. Its returns total number of rows that are affected by specified query. It does not return any row or column values.

ExecuteScalar

ExecuteScalar executes the command specified and returns first column of the first row. Its return type is object that containing first column of the first row. It's very useful when you are working with retrieving single value, count or aggregate function like sum, avg.

ExecuteReader

ExecuteReader used to retrieve rows from database that return type is DataReader. DataReader will works with connection oriented architecture. It is read only forward only cursor. DataReader object is always remains connected to database when you are using it. It is read only means its used for only select not for update and delete sql statement. It is forward only means its move only forward, once you read the row you can not move backward.

ExecuteXmlReader

ExecuteXmlReader used to retrieve records from database in form of xml. Its return type is XmlReader that helps to represent records in xml format.



Please give your feedback for improving this page