Executing a Command
The Command object exposes several Execute methods that 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 create a SqlCommand object to return a list of categories from the Northwind sample database in SQL Server.
Example
' nwindConn is assumed to be a valid SqlConnection object.
Dim command As SqlCommand = New SqlCommand( _
"SELECT CategoryID, CategoryName FROM dbo.Categories", nwindConn)
// nwindConn is assumed to be a valid SqlConnection object.
SqlCommand command = new SqlCommand(
"SELECT CategoryID, CategoryName FROM dbo.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, it is recommended that only the _Global instance be used. As a result, the value returned by the performance counter is the sum of the counter values for all ASP.NET applications.
See Also
Concepts
Using Stored Procedures with a Command
Obtaining a Single Value from a Database