C# 调用存储过程

用命令对象调用存储过程，就是定义存储过程的名称，给过程的每个参数添加参数定义。

下面的示例调用存储过程 GetBooksByPublisher，得到一家出版社的所有图书。这个存储过程接收一个参数。使用递归查询返回所请求的所有图书的记录： .

CREATE PROCEDURE [ProCSharp].[GetBooksByPublisher] 
  @publisher nvarchar(50) 
AS
  SELECT [Id], [Title], [Publisher], [ReleaseDate] FROM [ProCSharp].[Books] 
  WHERE [Publisher] = @publisher ORDER BY [ReleaseDate]

为了调用存储过程，SqlCommand 对象的 CommandText 设置为存储过程的名称，CommandType 设置为 CommandType.StoredProcedure。除此之外，该命令的调用类似于以前的方式。参数使用SqlCommand 对象的 CreateParameter 方法创建，也可以使用其他方法创建之前使用的参数。对于参数，填充 SqlDbType、ParameterName 和 Value 属性。因为存储过程返回记录，所以它通过调用方法 ExecuteReader 来调用：

private static void StoredProcedure(string publisher)
{
  using (var connection = new sqlConnection(GetConnectionString()))
  {
    SqlCommand command = connection.CreateCommand();
    command.CommandText = "[ProCSharp].[GetBooksByPublisher]"; 
    command.CommandType = CommandType.StoredProcedure;
    SqlParameter pl = command.CreateParameter(); 
    pl.SqlDbType = SqlDbType.NvarChar; 
    pl.ParameterName = "@Publisher"; 
    pl.Value = publisher;
    command.Parameters.Add(pl); 
    connection.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
      while (reader.Read())
      {
        int recursionLevel = (int)reader["Id"]; 
        string title = (string)reader["Title"]; 
        string pub = (string)reader["Publisher"];
        DateTime releaseDate = (DateTime)reader["ReleaseDate"]; 
        Console.WriteLine($"{title} - {pub}; {releaseDate:d}";
      }
    }
  }
}

运行应用程序，传递出版社 Wrox Press，得到如下所示的结果:

Professional C# (Beta 2 Edition) - Wrox Press; 6/1/2001 
Beginning C# - Wrox Press; 9/15/2001
Professional C# Web Services - Wrox Press; 12/1/2001 
Professional C# 2nd Edition - Wrox Press; 3/28/2002 
Beginning Visual C# - Wrox Press; 8/20/2002
Professional .NET Network Programming - Wrox Press; 10/1/2002 
ASP to ASP.NET Migration Handbook - Wrox Press; 2/1/2003 
Professional C# 3rd Edition - Wrox Press; 6/2/2004 
Professional C# 2005 -Wrox Press; 11/7/2005
Beginning Visual C# 2005 -Wrox Press; 11/7/2005
Professional C# 2005 with .NET 3.0 - Wrox Press; 6/12/2007 
Beginning Visual C# 2008 - Wrox Press; 5/5/2008 
Professional C# 2008 - Wrox Press; 5/24/2008
Professional C# 4 and .NET 4 - Wrox Press; 3/8/2010 
Beginning Visual C# 2010 - Wrox Press; 4/5/2010
Real World .NET, C#, and Silverlight - Wrox Press; 11/22/2011 
Professional C# 2012 and .NET 4.5 - Wrox Press; 10/18/2012 
Beginning Visual C# 2012 Programming - Wrox Press; 12/4/2012
Professional C# 5.0 and .NET 4.5.1 - Wrox Press; 2/9/2014 
Professional C# 6 and .NET Core 1.0 - Wrox Press; 4/11/2016 
Professional C# 7 and .NET Core 2.0 - Wrox Press; 4/2/2018

根据存储过程返回的内容，需要用ExecuteReader、ExecuteScalar 或 ExecuteNonQuery 调用存储过程。对于包含 Output 参数的存储过程，需要指定 SqlParameter 的 Direction 属性。默认情况下，Direction 是ParameterDirection. Input：

var pOut = new SqlParameter();
pOut.Direction = ParameterDirection.Output;

董川民