创建命令与配置参数

一、命令对象与执行命令⚓︎

01 DbCommand⚓︎

建立与数据源的连接后，可以使用 DbCommand 对象来执行命令并从数据源中返回结果，可以使用命令构造函数之一为要使用的数据提供程序（Data Provider）创建命令。

每个数据提供程序都具有一个 DbCommand 对象¹ —— OleDbCommand、SqlCommand、OdbcCommand、OracleCommand。

DbCommand 对象具有几个重要属性：

属性	说明
`CommandText`	获取或设置要在数据源中执行的 Transact-SQL 语句、表名或存储过程。
`CommandType`	获取或设置一个值，该值指示解释 CommandText 属性的方式。
`Connection`	获取或设置 SqlCommand 的此实例使用的 SqlConnection。

CommandType 是一个枚举属性，可以通过该属性设置让命令字符串按何种规则被解释执行，枚举选项如下：

枚举项	说明
`CommandType.Text`	定义要在数据源处执行的语句的 SQL 命令（默认）
`CommandType.StoredProcedure`	存储过程名称
`CommandType.TableDirect`	表的名称

下列示例展示了 SqlCommand 对象的创建：

// 假设 sqlConnection 是有效连接
string queryString = "select * from student_info;";
SqlCommand cm = new SqlCommand(queryString, sqlConnection); 

// or
SqlCommand cm = new SqlCommand();
cm.CommandText = queryString;
cm.CommandType = CommanType.Text; // 执行SQL命令，默认不需要设置该属性，仅做演示使用
cm.Connection = sqlConnection

02 执行命令⚓︎

每个对象都根据命令的类型和所需的返回值公开用于执行命令的方法，如下表所述：

方法	说明
`ExecuteReader`	返回一个 `DataReader` 对象。
`ExecuteScalar`	返回一个标量值。
`ExecuteNonQuery`	执行不返回任何行的命令。
`ExecuteXMLReader`	返回 XmlReader。只用于 `SqlCommand` 对象。

当命令和参数准备就绪后，你可以通过 Command 对象的执行方法来执行命令，以完成与数据源的交互，下面显示了执行命令的示例：

connection.Open();
// 调用 ExecuteReader 方法
using (SqlDataReader reader = command.ExecuteReader()) 
{ 
    if (reader.HasRows) 
    { 
        while (reader.Read()) 
        { 
            // do something
            Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
        } 
    } 
    else 
    { 
        Console.WriteLine("No rows found."); 
    } 
    reader.Close(); 
}

二、参数对象与配置参数⚓︎

01 DbParameter⚓︎

通过提供类型检查和验证，命令对象可使用参数来将值传递给 SQL 语句或存储过程。比如：

// SQL中存在一个变量@Grade需要传值
string queryString = "SELECT * FROM dbo.Enrollment WHERE Grade = @Grade";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand cm = new SqlCommand(queryString, sqlConnection); 

    // 设置命令参数
    SqlParameter parameter = new SqlParameter();
    parameter.ParameterName = "@Grade";
    parameter.SqlDbType = System.Data.SqlDbType.Int;
    parameter.Value = 4;
    parameter.Direction = System.Data.ParameterDirection.Input;
    // 将参数传递给命令
    command.Parameters.Add(parameter);

    // executive command
}

Note

与命令文本不同，参数输入被视为文本值，而不是可执行代码。这样可帮助抵御“SQL 注入”攻击，这种攻击的攻击者会将命令插入 SQL 语句，从而危及服务器的安全。参数化命令还可提高查询执行性能，因为它们可帮助数据库服务器将传入命令与适当的缓存查询计划进行准确匹配。除具备安全和性能优势外，参数化命令还提供一种用于组织传递到数据源的值的便捷方法。

通过 DbParameter 对象的构造函数或 DbParameterCollection 对象的 Add() 方法可以用来完成参数传递过程。每个数据提供程序都具有一个 DbParameter 对象²和一个 DbParameterCollection 对象³。

上面的示例显示了使用 SqlParameter 创建和传递参数，下面的示例是 SqlParameterCollection 版本：

// SQL中存在一个变量@Grade需要传值
string queryString = "SELECT * FROM dbo.Enrollment WHERE Grade = @Grade";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand cm = new SqlCommand(queryString, sqlConnection); 

    // 设置命令参数并传递给Command对象
    command.Parameters.Add("@Grade", System.Data.SqlDbType.Int, 2).Value = 4;

    // executive command
}

显然，当有多个参数时，使用 DbParameterCollection 对象可以避免显式创建 DbParameter 实例，从而简化代码。

02 ParameterDirection⚓︎

在添加参数时，您必须为输入参数以外的参数提供一个 ParameterDirection 属性。下表列出了属性的枚举值：

枚举值	说明
`Input`	该参数为输入参数，这是默认值。
`InputOutput`	该参数可执行输入和输出。
`Output`	该参数为输出参数。
`ReturnValue`	该参数表示从某操作（如存储过程、内置函数或用户定义的函数）返回的值。

03 占位符说明⚓︎

其次，不同的数据源可能具有不同的参数占位符语法，数据提供程序以不同方式处理命名和指定参数和参数占位符。此语法是针对某个特定的数据源自定义的，如下表所述：

数据提供程序	占位符	说明	示例
SqlClient	@	以 `@参数名` 格式使用命名参数。	`SELECT * FROM dbo.Enrollment WHERE Grade = @Grade`
OleDb	?	使用由问号 (?) 指示的位置参数标记。	`SELECT * FROM Customers WHERE CustomerID = ?`
Odbc	?	使用由问号 (`?`) 指示的位置参数标记。	`SELECT * FROM Customers WHERE CustomerID = ?`
OracleClient	:	以 `:参数名` 格式使用命名参数	`SELECT * FROM TEST WHERE NAME=:NAME`

适用于 OLE DB 的 OleDbCommand 对象；适用于 SQL Server 的 SqlCommand 对象；适用于 ODBC 的 OdbcCommand 对象；适用于 Oracle 的 OracleCommand 对象。 ↩
适用于 OLE DB 的 OleDbParameter 对象；适用于 SQL Server 的 SqlParameter 对象；适用于 ODBC 的 OdbcParameter 对象；适用于 Oracle 的 OracleParameter 对象 ↩
适用于 OLE DB 的 OleDbParameterCollection 对象；适用于 SQL Server 的 SqlParameterCollection 对象；适用于 ODBC 的 OdbcParameterCollection 对象；适用于 Oracle 的 OracleParameterCollection 对象 ↩