C#/.NET应用程序编程开发中如何执行SQL Server数据库的存储过程？

问题描述

C#/.NET应用程序编程开发中，如何执行SQL Server数据库中一个编写好的存储过程呢？

方案一

使用SqlCommand类的实例，在其构造函数中传入数据库存储过程的名称和SqlConnection连接实例，再指定SqlCommand的CommandType为：CommandType.StoredProcedure即可，示例代码如下：

using (var conn = new SqlConnection(connectionString))
    using (var command = new SqlCommand("ProcedureName", conn) { 
                             CommandType = CommandType.StoredProcedure }) {
       conn.Open();
       command.ExecuteNonQuery();
    }

方案二

如果你需要在C#中执行带参数的存储过程，则可以为SqlCommand的参数集合Parameters添加任意的参数，示例如下：

using (SqlConnection conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI")) {
    conn.Open();

    // 1. 创建一个SqlCommand实例，并指定需要执行的存储过程和数据库连接实例
    SqlCommand cmd  = new SqlCommand("CustOrderHist", conn);

    // 2. 设置CommandType类型为存储过程
    cmd.CommandType = CommandType.StoredProcedure;

    // 3. 向SqlCommand实例的参数集合添加参数
    cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));

    // 4. 执行SQL命令
    using (SqlDataReader rdr = cmd.ExecuteReader()) {
        while (rdr.Read())
        {
            Console.WriteLine("Product: {0,-35} Total: {1,2}",rdr["ProductName"],rdr["Total"]);
        }
    }
}

方案三

为了执行存储过程方法的复用性，我们可以封装一些执行存储过程的通用方法，如下：

private static string ConnString = ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString;
private SqlConnection Conn = new SqlConnection(ConnString);

public void ExecuteStoredProcedure(string procedureName)
{
    SqlConnection sqlConnObj = new SqlConnection(ConnString);

    SqlCommand sqlCmd = new SqlCommand(procedureName, sqlConnObj);
    sqlCmd.CommandType = CommandType.StoredProcedure;

    sqlConnObj.Open();
    sqlCmd.ExecuteNonQuery();
    sqlConnObj.Close();
}

public void ExecuteStoredProcedure(string procedureName, object model)
{
    var parameters = GenerateSQLParameters(model);
    SqlConnection sqlConnObj = new SqlConnection(ConnString);

    SqlCommand sqlCmd = new SqlCommand(procedureName, sqlConnObj);
    sqlCmd.CommandType = CommandType.StoredProcedure;

    foreach (var param in parameters)
    {
        sqlCmd.Parameters.Add(param);
    }

    sqlConnObj.Open();
    sqlCmd.ExecuteNonQuery();
    sqlConnObj.Close();
}

private List<SqlParameter> GenerateSQLParameters(object model)
{
    var paramList = new List<SqlParameter>();
    Type modelType = model.GetType();
    var properties = modelType.GetProperties();
    foreach (var property in properties)
    {
        if (property.GetValue(model) == null)
        {
            paramList.Add(new SqlParameter(property.Name, DBNull.Value));
        }
        else
        {
            paramList.Add(new SqlParameter(property.Name, property.GetValue(model)));
        }
    }
    return paramList;

}