将存储过程用于命令

 

在数据驱动的应用程序中，存储过程具有许多优势。利用存储过程，数据库操作可以封装在单个命令中，为获取最佳性能而进行优化并通过附加的安全性得到增强。尽管通过以 SQL 语句的形式传递存储过程名称后接参数自变量即可调用存储过程，但如果使用 ADO.NET DbCommand 对象的 Parameters 集合，则可以更加显式地定义存储过程参数并访问输出参数和返回值。

若要调用存储过程，请将 Command 对象的 CommandType 设置为 StoredProcedure。CommandType 一旦设置为 StoredProcedure，就可以使用 Parameters 集合来定义参数，如以下示例所示。

注意

OdbcCommand 要求您在调用存储过程时提供完整的 ODBC CALL 语法。

1. // Assumes that connection is a valid SqlConnection object.
3. SqlCommand salesCommand = new SqlCommand("SalesByCategory",
4.   connection);
5. salesCommand.CommandType = CommandType.StoredProcedure;
7. SqlParameter parameter = salesCommand.Parameters.Add(
8.   "@CategoryName", SqlDbType.NVarChar, 15);
9. parameter.Value = "Beverages";
11. connection.Open();
13. SqlDataReader reader = salesCommand.ExecuteReader();
15. Console.WriteLine(
16.   "{0}, {1}", reader.GetName(0), reader.GetName(1));
18. while (reader.Read())
19. {
20.   Console.WriteLine("{0}, ${1}", reader.GetString(0), 
21.     reader.GetDecimal(1));
22. }
24. reader.Close();
25. connection.Close();

Parameter 对象可以使用 Parameter 构造函数来创建，或通过调用 Command 的 Parameters 集合的 Add 方法来创建。Parameters.Add 会将构造函数参数或现有 Parameter 对象用作输入。在将 Parameter 的 Value 设置为空引用时，请使用 DBNull.Value。

对于 Input 参数之外的参数，必须设置 ParameterDirection 属性来指定参数类型是 InputOutput、Output 还是 ReturnValue。以下示例演示为各种提供程序创建 Input、Output 和 ReturnValue 参数之间的差异。

SqlClient 示例

1. // Assumes that connection is a valid SqlConnection object.
2. SqlCommand command = new SqlCommand("SampleProc", connection);
3. command.CommandType = CommandType.StoredProcedure;
5. SqlParameter parameter = command.Parameters.Add(
6.   "RETURN_VALUE", SqlDbType.Int);
7. parameter.Direction = ParameterDirection.ReturnValue;
9. parameter = command.Parameters.Add(
10.   "@InputParm", SqlDbType.NVarChar, 12);
11. parameter.Value = "Sample Value";
13. parameter = command.Parameters.Add(
14.   "@OutputParm", SqlDbType.NVarChar, 28);
15. parameter.Direction = ParameterDirection.Output;
17. connection.Open();
19. SqlDataReader reader = command.ExecuteReader();
21. Console.WriteLine(
22.   "{0}, {1}", reader.GetName(0), reader.GetName(1));
24. while (reader.Read())
25. {
26.   Console.WriteLine(
27.     "{0}, {1}", reader.GetInt32(0), reader.GetString(1));
28. }
30. reader.Close();
31. connection.Close();
33. Console.WriteLine(" @OutputParm: {0}", command.Parameters["@OutputParm"].Value);
34. Console.WriteLine("RETURN_VALUE: {0}", command.Parameters["RETURN_VALUE"].Value);

OleDb 示例

1. OleDbCommand command = new OleDbCommand("SampleProc", connection);
2. command.CommandType = CommandType.StoredProcedure;
4. OleDbParameter parameter = command.Parameters.Add(
5.   "RETURN_VALUE", OleDbType.Integer);
6. parameter.Direction = ParameterDirection.ReturnValue;
8. parameter = command.Parameters.Add(
9.   "@InputParm", OleDbType.VarChar, 12);
10. parameter.Value = "Sample Value";
12. parameter = command.Parameters.Add(
13.   "@OutputParm", OleDbType.VarChar, 28);
14. parameter.Direction = ParameterDirection.Output;
16. connection.Open();
18. OleDbDataReader reader = command.ExecuteReader();
20. Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1));
22. while (reader.Read())
23. {
24.   Console.WriteLine("{0}, {1}", reader.GetInt32(0), reader.GetString(1));
25. }
27. reader.Close();
28. connection.Close();
30. Console.WriteLine(" @OutputParm: {0}", command.Parameters["@OutputParm"].Value);
31. Console.WriteLine("RETURN_VALUE: {0}", command.Parameters["RETURN_VALUE"].Value);

Odbc 示例

1. OdbcCommand command = new OdbcCommand( _
2.   "{ ? = CALL SampleProc(?, ?) }", connection);
3. command.CommandType = CommandType.StoredProcedure;
5. OdbcParameter parameter = command.Parameters.Add( _
6.   "RETURN_VALUE", OdbcType.Int);
7. parameter.Direction = ParameterDirection.ReturnValue;
9. parameter = command.Parameters.Add( _
10.   "@InputParm", OdbcType.VarChar, 12);
11. parameter.Value = "Sample Value";
13. parameter = command.Parameters.Add( _
14.   "@OutputParm", OdbcType.VarChar, 28);
15. parameter.Direction = ParameterDirection.Output;
17. connection.Open();
19. OdbcDataReader reader = command.ExecuteReader();
21. Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1));
23. while (reader.Read())
24. {
25.   Console.WriteLine( _
26.     "{0}, {1}", reader.GetInt32(0), reader.GetString(1));
27. }
29. reader.Close();
30. connection.Close();
32. Console.WriteLine(" @OutputParm: {0}", command.Parameters["@OutputParm"].Value);
33. Console.WriteLine("RETURN_VALUE: {0}", command.Parameters["RETURN_VALUE"].Value);

对 SqlCommand 使用参数

在对 SqlCommand 使用参数时，添加到 Parameters 集合的参数的名称必须与存储过程中参数标记的名称相匹配。SQL Server .NET Framework 数据提供程序将存储过程中的参数视为命名参数并搜索匹配的参数标记。

SQL Server .NET Framework 数据提供程序不支持在向 SQL 语句或存储过程传递参数时使用问号 (?) 占位符。在这种情况下，必须使用命名参数，如以下示例所示，其中 @CustomerID 是命名参数。

复制代码

SELECT * FROM Customers WHERE CustomerID = @CustomerID

对 OleDbCommand 或 OdbcCommand 使用参数

在对 OleDbCommand 或 OdbcCommand 使用参数时，向 Parameters 集合中添加参数的顺序必须与存储过程中所定义的参数顺序相匹配。OLE DB .NET Framework 数据提供程序和 ODBC .NET Framework 数据提供程序将存储过程中的参数视为占位符，并且将按顺序应用这些参数值。另外，返回值参数必须是添加到 Parameters 集合中的第一批参数。

OLE DB .NET Framework 数据提供程序和 ODBC .NET Framework 数据提供程序不支持在向 SQL 语句或存储过程传递参数时使用命名参数。在此情况下，必须使用问号 (?) 占位符，如以下示例所示。

复制代码

SELECT * FROM Customers WHERE CustomerID = ?

因此，向 Parameters 集合添加 Parameter 对象的顺序必须直接对应于该参数的 ? 占位符的位置。

派生参数信息

参数也可以使用 CommandBuilder 类从存储过程派生。SqlCommandBuilder 和 OleDbCommandBuilder 类都提供了静态方法 DeriveParameters，该静态方法将自动使用存储过程中的参数信息填充 Command 对象的 Parameters 集合。请注意，DeriveParameters 将重写 Command 的任何现有参数信息。

派生参数信息时需要经历一个到数据源的附加行程，以获取参数信息。如果参数信息在设计时是已知的，则可以通过显式设置参数来提高应用程序的性能。

以下代码示例显示如何使用 CommandBuilder.DeriveParameters 来填充 Command 对象的 Parameters 集合。

1. // Assumes that connection is a valid SqlConnection object.
2. SqlCommand salesCommand = new SqlCommand("Sales By Year", connection);
3. salesCommand.CommandType = CommandType.StoredProcedure;
5. connection.Open();
6. SqlCommandBuilder.DeriveParameters(salesCommand);
7. connection.Close();