The SqlCommand Object
We execute SELECT, INSERT, UPDATE, and DELETE using the command object to the database. SqlCommand object represents a Transact-SQL statement or stored procedure to execute against a SQL Server database. In this article we would see the connected SqlCommand object. But later when we would discus the SqlDataAdapter, we would talk about disconnected way.
SqlCommand implements the IDbCommand interface. IDbCommand Interface, which has methods like ExecuteReader(), ExecuteNonQuery(), ExecuteScalar(), CreateParameter(), Cancel() and Prepare(). The interface also has properties like Connection, CommandTimeout, CommandType, CommandText, Parameters and Transaction. We shall be discussing all these properties and methods, so don't worry about that.
SqlCommand: Features the following methods for executing commands at a SQL Server database:
|
Item
|
Description
|
|
BeginExecuteNonQuery
|
Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand, generally executing commands such as INSERT, DELETE, UPDATE, and SET statements. Each call to BeginExecuteNonQuery must be paired with a call to EndExecuteNonQuery, which finishes the operation, typically on a separate thread.
|
|
BeginExecuteReader
|
Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand and retrieves one or more results sets from the server. Each call to BeginExecuteReader must be paired with a call to EndExecuteReader which finishes the operation, typically on a separate thread.
|
|
BeginExecuteXmlReader
|
Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand. Each call to BeginExecuteXmlReader must be paired with a call to EndExecuteXmlReader, which finishes the operation, typically on a separate thread, and returns an XmlReader object.
|
|
ExecuteReader
|
Executes commands that return rows. For increased performance, ExecuteReader invokes commands using the Transact-SQL sp_executesql system stored procedure. Therefore, ExecuteReader might not have the effect that you want if used to execute commands such as Transact-SQL SET statements.
|
|
ExecuteNonQuery
|
Executes commands such as Transact-SQL INSERT, DELETE, UPDATE, and SET statements.
|
|
ExecuteScalar
|
Retrieves a single value (for example, an aggregate value) from a database.
|
|
ExecuteXmlReader
|
Sends the CommandText to the Connection and builds an XmlReader object.
|
Creating SqlCommand Object:
There are various ways to create the object of SqlCommand object. We would be explaining most common ways to create it.
1. Create to use Select T-SQL.
SqlCommand cmd = new SqlCommand(“Select * from Customers, conn);
2. We can create without any argument, but set the properties later.
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Categories ORDER BY CategoryID";
cmd.CommandTimeout = 15;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
Properties:
CommandText:
It takes the transact-SQL statement or stored procedure to execute.
CommandTimeout:
The time in seconds is to wait for the command to execute. If SQL statement cannot be completed within the specified time, it would raise the timed out exception.
CommandType:
Set the CommandType property to StoredProcedure, you should set the CommandText property to the name of the stored procedure. The command executes this stored procedure when you call one of the Execute methods.
Set CommandType property to Text, when we want to run the SQL statement.
The Microsoft .NET Framework Data Provider for SQL Server does not support the question mark (?) placeholder for passing parameters to a SQL Statement or a stored procedure called with a CommandType of Text. In this case, named parameters must be used. We would talk about named parameter in another article.
How to use Command Object in Connected connection environment
1. Using ExecuteReader():
SqlConnection myConnection = new SqlConnection(myConnectionString);
SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while(myReader.Read())
{
Console.WriteLine(myReader.GetString(0));
}
myReader.Close();
//Implicitly closes the connection because CommandBehavior.CloseConnection was specified.
2. Using ExecuteNonQuery(): This method is used to insert, update or delete the records into database. It does return the number of records affected.
SqlCommand myCommand = new SqlCommand(myExecuteQuery, myConnection);
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
3. Using ExecuteScalar(): When database query requires to return a single value(e.g. an aggregate value).
SqlCommand myCommand = new SqlCommand(myScalarQuery, myConnection);
myCommand.Connection.Open();
string svalue = Convert.ToString(myCommand.ExecuteScalar());
//if value is of string type we can get it directly. It is very fast.
myConnection.Close();
Summary
An SqlCommand object allows querying and sending commands to a database. It has methods that are specialized for different commands. The ExecuteReader method returns an SqlDataReader object for viewing the results of a select query. For insert, update, and delete SQL commands, uses the ExecuteNonQuery method. For a single aggregated value from a query, the ExecuteScalar is the best choice.