Executing a Command

  • Article

After establishing a connection to a data source, you can execute commands and return results from the data source using a Command object. You can create a command using the Command constructor, which takes optional arguments of an SQL statement to execute at the data source, a Connection object, and a Transaction object. You can also create a command for a particular Connection using the CreateCommand method of the Connection object. The SQL statement of the Command object can be queried and modified using the CommandText property.

The Command object exposes several Execute methods you can use to perform the intended action. When returning results as a stream of data, use ExecuteReader to return a DataReader object. Use ExecuteScalar to return a singleton value. Use ExecuteNonQuery to execute commands that do not return rows.

When using the Command object with a stored procedure, you may set the CommandType property of the Command object to StoredProcedure. With a CommandType of StoredProcedure, you may use the Parameters property of the Command to access input and output parameters and return values. The Parameters property can be accessed regardless of the Execute method called. However, when calling ExecuteReader, return values and output parameters will not be accessible until the DataReader is closed.

The following code example demonstrates how to format a Command object to return a list of Categories from the Northwind database.

SqlClient

Dim catCMD As SqlCommand = New SqlCommand("SELECT CategoryID, CategoryName FROM Categories", nwindConn)
[C#]
SqlCommand catCMD = new SqlCommand("SELECT CategoryID, CategoryName FROM Categories", nwindConn);

OleDb

Dim catCMD As OleDbCommand = New OleDbCommand("SELECT CategoryID, CategoryName FROM Categories", nwindConn)
[C#]
OleDbCommand catCMD = new OleDbCommand("SELECT CategoryID, CategoryName FROM Categories", nwindConn);

Performance Counters for Commands

The .NET Framework Data Provider for SQL Server adds a performance counter to enable you to detect intermittent problems related to failed command executions. You can access the "SqlClient: Total # failed commands" counter in Performance Monitor under the .NET CLR Data performance object to determine the total number of command executions that have failed for any reason.

Note   When using the .NET Framework Data Provider for SQL Server performance counters in conjunction with ASP.NET applications, only the _Global instance is available. As a result, the value returned by the performance counter is the sum of the counter values for all ASP.NET applications.

See Also

Using .NET Framework Data Providers to Access Data | Using Stored Procedures with a Command | Retrieving Data Using the DataReader | Obtaining a Single Value from a Database | Performing Database Operations and Modifying Data | OleDbCommand Class | OleDbDataReader Class | SqlCommand Class | SqlDataReader Class