调用存储过程时，最小化代码repeatednesses [英] Minimize code repeatednesses when calling Stored Procedures

问题描述

我用一定的方法体调用存储过程，用下面的示例代码：

I'm using a certain method body to call stored procedures, with the following sample code:

     public void StoredProcedureThatIsBeingcalled(int variable_1, int variable_2, out DataSet ds)
 {
     using (SqlConnection con = new SqlConnection(DatabaseConnectionString))
     {
         ds = new DataSet("DsToGoOut");
         using (SqlCommand cmd = new SqlCommand("StoredProcedureThatIsBeingcalled", DbConn.objConn))
         {
             cmd.CommandType = CommandType.StoredProcedure;


             cmd.Parameters.Add(new SqlParameter("@variable_1", variable_1));
             cmd.Parameters.Add(new SqlParameter("@variable_2", variable_2));
             try
             {
                 con.Open();
                 SqlDataAdapter objDataAdapter = new SqlDataAdapter();
                 objDataAdapter.SelectCommand = cmd;

                 objDataAdapter.Fill(ds);

                 con.Close();
             }
             catch (Exception ex)
             {

                 //sql_log_err
             }

         }
     }
 }

我有什么错误我最上面的代码中再重复一次我CS文件为每个不同的程序我打电话。

What bugs me I have most of the above code repeating time and time again in my cs file for every different procedure I call.

显然，我可以把它清除掉，并有一个函数被调用的程序名称作为变量，但我怎么养活它不同数量的参数（具有不同的数据类型 - 整型，字符串布尔 - 从来没有其他东西）吗？不同的程序我用

Obviously I can clear it up and have the one Function being called with the procedure name as a variable, but how do I feed it different number of Parameters (with different Data Types - int,string bool - never anything else) for the different procedures I use ?

我可以有不同的几个不同的功能（0-10）参数的数量，但我觉得有这样做的更好的办法？

I can have few different functions with different number of parameters(0-10), but I feel there is a better way of doing this ?

推荐答案

您可以使用辅助类来封装SQL参数，并创建一个单一的方法来处理所有的数据填补这样的：

You can use a helper class to encapsulate sql parameters and create a single method to handle all dataset fills like this:

Helper类：

private class SqlParamDefinition
{

    public SqlParamDefinition(string name, SqlDbType dbType, object value)
    {
        this.Name = name;
        this.DbType = dbType;
        this.Value = value;
    }

    public string Name { get; }
    public SqlDbType DbType { get; }

    public object Value { get; }


}

Execute方法（基于方法你贴）：

Execute method (based on the method you posted):

public DataSet ExecuteSelectProcedure(string procedeureName, params SqlParamDefinition[] parameters)
{
    var ds = new DataSet();
    using (var con = new SqlConnection(DatabaseConnectionString))
    {

        using (var cmd = new SqlCommand(procedeureName, DbConn.objConn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            for(int i = 0; i < parameters.Length; i++)
            {
                var param = parameters[i];
                cmd.Parameters.Add(new SqlParameter(param.Name, param.DbType).Value = param.Value);
            }

            try
            {
                con.Open();
                var objDataAdapter = new SqlDataAdapter();
                objDataAdapter.SelectCommand = cmd;

                objDataAdapter.Fill(ds);

                con.Close();
            }
            catch (Exception ex)
            {

                //sql_log_err
            }

        }
    }
    return ds;
}

调用示例：

Calling example:

var parameters = new SqlParamDefinition[]
{
    new SqlParamDefinition("@Param1", SqlDbType.VarChar, "value1"),
    new SqlParamDefinition("@Param2", SqlDbType.VarChar, "value2"),
    new SqlParamDefinition("@Param3", SqlDbType.Int, 123),
};

var ds = ExecuteSelectProcedure("Strong procedure name", parameters);

这篇关于调用存储过程时，最小化代码repeatednesses的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！