执行命令Executing a Command

包含在 .NET Framework 中的每个 .NET Framework 数据提供程序都拥有自己的继承自 DbCommand 的命令对象。Each .NET Framework data provider included with the .NET Framework has its own command object that inherits from DbCommand. 适用于 OLE DB 的 .NET Framework 数据提供程序包括一个 OleDbCommand 对象,适用于 SQL Server 的 .NET Framework 数据提供程序包括一个 SqlCommand 对象,适用于 ODBC 的 .NET Framework 数据提供程序包括一个 OdbcCommand 对象,适用于 Oracle 的 .NET Framework 数据提供程序包括一个 OracleCommand 对象。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 返回一个 DataReader 对象。Returns a DataReader object.
ExecuteScalar 返回一个标量值。Returns a single scalar value.
ExecuteNonQuery 执行不返回任何行的命令。Executes a command that does not return any rows.
ExecuteXMLReader 返回 XmlReaderReturns an XmlReader. 只用于 SqlCommand 对象。Available for a SqlCommand object only.

每个强类型命令对象还支持指定如何解释命令字符串的 CommandType 枚举,如下表所述。Each strongly typed command object also supports a CommandType enumeration that specifies how a command string is interpreted, as described in the following table.

CommandTypeCommandType 描述Description
Text 定义要在数据源处执行的语句的 SQL 命令。An SQL command defining the statements to be executed at the data source.
StoredProcedure 存储过程的名称。The name of the stored procedure. 您可以使用某一命令的 Parameters 属性访问输入和输出参数,并返回值(无论调用哪种 Execute 方法)。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. 当使用 ExecuteReader 时,在关闭 DataReader 后才能访问返回值和输出参数。When using ExecuteReader, return values and output parameters will not be accessible until the DataReader is closed.
TableDirect 表的名称。The name of a table.

示例Example

下面的代码示例演示如何创建 SqlCommand 对象以通过设置其属性执行存储过程。The following code example demonstrates how to create a SqlCommand object to execute a stored procedure by setting its properties. SqlParameter 对象用于指定存储过程的输入参数。A SqlParameter object is used to specify the input parameter to the stored procedure. 使用 ExecuteReader 方法执行此命令,并在控制台窗口中显示 SqlDataReader 的输出。The command is executed using the ExecuteReader method, and the output from the SqlDataReader is displayed in the console window.

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();
        using (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();
        }
    }
}
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()
        Using 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 Using
End Sub

命令疑难解答Troubleshooting Commands

用于 SQL Server 的 .NET Framework 数据提供程序添加了性能计数器,使您能够检测与失败的命令执行相关的间歇性问题。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.

请参阅See also