Properties and Methods of the SqlCommand Class

Item	Description
Properties
CommandText	Contains the text of a SQL query
CommandTimeout	Contains the length of the timeout of a query, in seconds
CommandType	Specifies the type of command to be executed
Connection	Specifies the connection to the database
Parameters	Specifies a collection of parameters for the SQL query
Transaction	Specifies a transaction object, which enables developers to run queries in a transaction
Methods
Cancel()	Cancels the running query
CreateParameter()	Returns a new SQL parameter
ExecuteNonQuery()	Executes the CommandText property against the database and does not return a result set
ExecuteReader()	Executes the CommandText property and returns data in a DataReader object
ExecuteScalar()	Executes the CommandText property and returns a single value
ExecuteXmlReader()	Executes the CommandText property and returns data in an XMLDataReader object
ResetCommandTimeout()	Resets the CommandTimeout property for the query

SqlCommand.CommandText

Syntax

String CommandText

Description

The CommandText property is a string that contains the text of a database query.

Example

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Customers";

SqlCommand.CommandTimeout

Syntax

Int32 CommandTimeout

Description

The CommandTimeout property contains the number of seconds before the query will timeout. By default, CommandTimeout is set to 30 seconds.

Example

SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 50;

SqlCommand.CommandType

Syntax

CommandType CommandType

Description

The CommandType property contains the type of query being performed. CommandType is defined in the System.Data namespace. The CommandType property can be set to StoreProcedure, TableDirect, or Text.

Example

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;

SqlCommand.Connection

Syntax

SqlConnection Connection

Description

The Connection property is set to a valid SqlConnection object against which the query is to be performed. The easiest way to set the connection for the SqlCommand object is to explicitly create a SqlConnection object and assign it to the Connection property.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
      User Id=sa; Password=;Initial Catelog=northwind");
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

SqlCommand.Parameters

Syntax

SqlParameterCollection Parameters

Description

The Parameters property contains the names of a group of SQL parameters that are used to when calling a stored procedure that requires input or output parameters.

Example

//Create and Open Connection
SqlConnection conn = new SqlConnection("Data Source=localhost; 
     UID=sa; PWD=; Initial Catalog=northwind;");
conn.Open();

//Create Command
SqlCommand cmd = new SqlCommand("AddNewCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;

//Configure input parameters
SqlParameter param = new SqlParameter();
param = cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 20));
param.Direction = Parameter.Direction.Input;
param.Value = sName.Text;

//Configure output parameters
param = cmd.Parameters.Add(new SqlParameter("@retval", SqlDbType.Int, 4));
param.Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

SqlCommand.Transaction

Syntax

SqlTransaction Transaction

Description

The Transaction property contains the transaction object (if any) for the current command set.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
        User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();


SqlTransaction trans = conn.BeginTransaction(IsolationLevel.Serializable, "RemoveCustomers");


cmd = new SqlCommand("", conn);
cmd.Transaction = trans;

SqlCommand.Cancel

Syntax

Void Cancel()

Description

The Cancel() method cancels a running query.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
        User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", conn);
cmd.ExecuteNonQuery();
cmd.Cancel();

SqlCommand.CreateParameter()

Syntax

SqlParameter CreateParameter()

Description

The CreateParameter() method returns a new instance of a SQL parameter.

Example

SqlParameter param = cmd.CreateParameter();
param.ParameterName = "@CustomerID";
param.SqlDbType = SqlDbType.NVarChar;
param.Size = 5;
param.Value = "123";
param.Direction = ParameterDirection.Input;
cmd.Parameters.Add(param);

SqlCommand.ExecuteNonQuery()

Syntax

Int32 ExecuteNonQuery()

Description

The ExecuteNonQuery() method executes the command text against the database specified in the Connection object. This method is optimized for queries that do not return any information (for example, DELETE and UPDATE queries).

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();
SqlCommand cmd = new SqlCommand("DELETE FROM Customers 
         WHERE LastName='Jones'", conn);
cmd.ExecuteNonQuery();

SqlCommand.ExecuteReader()

Syntax

SqlDataReader ExecuteReader()
SqlDataReader ExecuteReader( CommandBehavior behavior )

Description

The ExecuteReader() method executes the command text against the database specified in the Connection object and returns a SqlDataReader object with the results of the query.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
          User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", conn);


SqlDataReader reader = cmd.ExecuteReader();

SqlCommand.ExecuteScalar()

Syntax

Object ExecuteScalar()

Description

The ExecuteScalar() method executes the command text against the database specified in the Connection object and returns a single object. The ExecuteScalar() method exists because it is wasteful to return a dataset for a single value. The overhead for the dataset would be much larger than the actual value being returned.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT count(*) FROM Customers", conn);
Int32 customerCount = (Int32)cmd.ExecuteScalar();


msg.Text = "There are "+customerCount.ToString()+" customers in the database.";

SqlCommand.ExecuteXmlReader()

Syntax

XmlReader ExecuteXmlReader()

Description

The ExecuteXmlReader() method executes the command text against the database specified in the Connection object and returns the result, set in an XmlReader object.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();


SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", conn);
XmlReader reader = cmd.ExecuteXmlReader();

SqlCommand.ResetCommandTimeout()

Syntax

Void ResetCommandTimeout()

Description

The ResetCommandTimeout() method resets the CommandTimeout property to the default value of 30 seconds.

Example

SqlCommand cmd = new SqlCommand("DELETE FROM Customers WHERE LastName='Jones'", conn);
cmd.CommandTimeout = 45;
cmd.ResetCommandTimeout();
//CommandTimeout is now reset to 30