一个函数用于填充sql命令参数并运行存储过程

本文关键字：命令 sql 参数 存储过程 运行 填充 用于 一个 函数 | 更新日期: 2023-09-27 18:01:20

在我的应用程序中，我必须执行许多存储过程。

我必须写很多代码来做这个。

例如:

_oconn.ConnectionString = _connectionString;
_oconn.Open();
SqlCommand ocmd = new SqlCommand();
ocmd.Connection = _oconn;
ocmd.CommandText = "procname";
ocmd.CommandType = CommandType.StoredProcedure;
ocmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = 'ABS';
ocmd.Parameters.Add("@ID", SqlDbType.Int).Value = 123;
var da = new SqlDataAdapter(ocmd);
da.Fill(Obj);
_oconn.Close();

我为任何具有不同参数的过程编写了这段代码，

是否有任何方法来编写一个函数，它做我所有的过程?

一个函数用于填充sql命令参数并运行存储过程

不能为不同的存储过程编写相同的函数

但是我认为最好的做法是

// this is common function for all datatable
        public DataTable ExecuteDataTable(SqlParameterCollection ObjParams, string StoredProcedureName)
        {
            DataTable Obj = new DataTable();
            try
            {
                SqlConnection _oconn = new SqlConnection();
                _oconn.ConnectionString = _connectionString;
                _oconn.Open();
                SqlCommand ocmd = new SqlCommand();
                ocmd.Connection = _oconn;
                ocmd.CommandText = StoredProcedureName;
                ocmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter item in ObjParams)
                    ocmd.Parameters.Add(item);

                var da = new SqlDataAdapter(ocmd);
                da.Fill(Obj);
                _oconn.Close();
            }
            catch (Exception)
            {
                throw;
            }
            return Obj;
        }

        // You can write every diffrenet Procedure with different functions 
        public DataTable GetStudents()
        {
            SqlParameterCollection objParams = new SqlParameterCollection();
            objParams.Add(new SqlParameter("@Name", "ABS"));
            objParams.Add(new SqlParameter("@ID", 123));
            var StudentsTable = ExecuteDataTable(objParams, "procname");
        }

可以创建一个方法，例如:

private DataSet ExecuteSQLSPDS(string strConnectString, string strSchema, string strStoredProcedureName, ArrayList arlParamName, ArrayList arlParamValue, ref string strErrMsg)
{
    SqlConnection cnResult = new SqlConnection(strConnectString);
    SqlDataAdapter daResult = new SqlDataAdapter();
    DataSet ds = new DataSet();
    try
    {
        daResult.SelectCommand = new SqlCommand();
        daResult.SelectCommand.CommandType = CommandType.StoredProcedure;
        daResult.SelectCommand.CommandText = strSchema + "." + strStoredProcedureName;
        daResult.SelectCommand.Connection = cnResult;
        daResult.SelectCommand.CommandTimeout = 0;
        //Form Param
        for (int i = 0; i < arlParamName.Count; i++)
        {
            daResult.SelectCommand.Parameters.Add(new SqlParameter(arlParamName[i].ToString(), arlParamValue[i].ToString()));
        }
        cnResult.Open();
        daResult.Fill(ds);
    }
    catch (Exception e)
    {
        strErrMsg = e.Message;
    }
    finally
    {
        if (cnResult.State.ToString() == System.Data.ConnectionState.Open.ToString())
            cnResult.Close();
        daResult.Dispose();
    }
    return ds;
}
然后调用方法: 
DataSet ds = ExecuteSQLSPDS(ConnectString, Schema, StoredProcedureName, arlParamName, arlParamValue, ref ErrMsg);
相关文章：