Command object in ADO.net :

May 11, 2011

Command objects execute SQL batch statements or stored procedures over an open Connection. Command objects can return one or more resultsets, subsets of a resultset, a single row, a single scalar value, an XmlDataReader object, or the RowsAffected value for table updates.
The Command Object required an instance of a Connection Object for executing the SQL statements i.e. for retrieving data or executes an SQL statement against a Data Source, you have to create a Connection Object and open a connection to the Data Source, and assign the open connection to the connection property of the Command Object.
When command object returns data then we can use Datareader to access that data. Command objects support an optional collection of Parameter objects to execute parameterized queries or stored procedures.

Command objects Methods:

ExecuteNonQuery:

This is one of the most frequently used method in SqlCommand Object and is used for executing statements that do not return result set. ExecuteNonQuery() performs Data Definition tasks as well as Data Manipulation tasks also. The Data Definition tasks like creating Stored Procedures and Views perform by ExecuteNonQuery() . Also Data Manipulation tasks like Insert , Update and Delete perform by ExecuteNonQuery().

SqlConnection conn = new SqlConnection("connection_string"); SqlCommand com = new SqlCommand("delete * from Table_Name where condition", conn); conn.Open(); int i= com.ExecuteNonQuery(); conn.Close();

ExecuteReader:

This method in SqlCommand Object sends the SQL statements to Connection Object and populates a SqlDataReader Object based on the SQL statement. When the ExecuteReader method in SqlCommand Object executes it instantiate a SqlClient.SqlDataReader Object.

SqlConnection conn = new SqlConnection("connection_string"); SqlCommand com = new SqlCommand("select * from Table_Name", conn); conn.Open(); SqlDataReader dr = com.ExecuteReader(); while (dr.Read()) { Response.Write(dr[0].ToString());//It will access First element of the row } conn.Close();

ExecuteScalar:

This method in SqlCommand object is used when result of the query returns single value. That value can be Date, Integer or String but it can return only single valur.

SqlConnection conn = new SqlConnection("connection_string"); SqlCommand com = new SqlCommand("select count(*) from Table_Name", conn); conn.Open(); int i= com.ExecuteScalar(); conn.Close();