Executing a Command (ADO.NET)

Article
11/16/2012

Each .NET Framework data provider included with the .NET Framework has its own command object that inherits from DbCommand. The .NET Framework Data Provider for OLE DB includes an OleDbCommand object, the .NET Framework Data Provider for SQL Server includes a SqlCommand object, the .NET Framework Data Provider for ODBC includes an OdbcCommand object, and the .NET Framework Data Provider for Oracle includes an OracleCommand object. Each of these objects exposes methods for executing commands based on the type of command and desired return value, as described in the following table.

Command	Return Value
ExecuteReader	Returns a DataReader object.
ExecuteScalar	Returns a single scalar value.
ExecuteNonQuery	Executes a command that does not return any rows.
ExecuteXMLReader	Returns an XmlReader. Available for a SqlCommand object only.

Each strongly typed command object also supports a CommandType enumeration that specifies how a command string is interpreted, as described in the following table.

CommandType	Description
Text	An SQL command defining the statements to be executed at the data source.
StoredProcedure	The name of the stored procedure. You can use the Parameters property of a command to access input and output parameters and return values, regardless of which Execute method is called. When using ExecuteReader, return values and output parameters will not be accessible until the DataReader is closed.
TableDirect	The name of a table.

Example

The following code example demonstrates how to create a SqlCommand object to execute a stored procedure by setting its properties. A SqlParameter object is used to specify the input parameter to the stored procedure. The command is executed using the ExecuteReader method, and the output from the SqlDataReader is displayed in the console window.

Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties. 
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties. 
        Dim parameter As New SqlParameter()
        parameter.ParameterName = "@CategoryName"
        parameter.SqlDbType = SqlDbType.NVarChar
        parameter.Direction = ParameterDirection.Input
        parameter.Value = categoryName

        ' Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter)

        ' Open the connection and execute the reader.
        connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then 
            Do While reader.Read()
                Console.WriteLine("{0}: {1:C}", _
                  reader(0), reader(1))
            Loop 
        Else
            Console.WriteLine("No rows returned.")
        End If 
    End Using 
End Sub

static void GetSalesByCategory(string connectionString, 
    string categoryName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "SalesByCategory";
        command.CommandType = CommandType.StoredProcedure;

        // Add the input parameter and set its properties.
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@CategoryName";
        parameter.SqlDbType = SqlDbType.NVarChar;
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = categoryName;

        // Add the parameter to the Parameters collection. 
        command.Parameters.Add(parameter);

        // Open the connection and execute the reader.
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
    }
}

Troubleshooting Commands

The .NET Framework Data Provider for SQL Server adds performance counters to enable you to detect intermittent problems related to failed command executions. For more information see Performance Counters (ADO.NET).

Executing a Command (ADO.NET)

Example

Troubleshooting Commands

See Also

Other Resources

Additional resources