使用C# 操作存储过程，执行sql语句通用类

如何使用c# 操作存储过程，执行sql语句？

闲话不多说，直接上代码：

    /// <summary>
    /// sql通用类
    /// </summary>
    public class sqlhelper
    {

       首先配置连接字符串
        public static string connstr = configurationmanager.connectionstrings["connstring"].connectionstring;//connstring表示webconfig中的连接字符串

 

       执行存储过程不设置超时时间
        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="connstr">连接字符串</param>
        /// <param name="storedprocedurename">存储过程名称</param>
        /// <param name="responsebool">存储过程执行状态</param>
        /// <param name="responsemsg">执行存储过程状态描述</param>
        /// <param name="paramsobject">存储过程输入参数</param>
        /// <returns></returns>
        public static dataset sql_getstoredprocedurefunction(string connstr, string storedprocedurename, out bool responsebool, out string responsemsg, params parameterkeyvaluesentity[] paramsobject)
        {
            dataset responseds = new dataset();
            responsebool = true;
            responsemsg = "获取成功！";
            try
            {
                using (sqlconnection sqlconn = new sqlconnection(connstr))
                {
                    sqlconn.open();
                    using (sqlcommand sqlcmd = new sqlcommand(storedprocedurename, sqlconn))
                    {
                        sqlcmd.commandtype = commandtype.storedprocedure;
                        if (paramsobject.count() > 0)
                        {
                            for (int i = 0; i < paramsobject.count(); i++)
                            {
                                sqlparameter sqlparameter = new sqlparameter(paramsobject[i].key, paramsobject[i].value);
                                sqlcmd.parameters.add(sqlparameter);
                            }
                        }
                        sqldataadapter sda = new sqldataadapter(sqlcmd);
                        sda.fill(responseds);
                    }
                }
            }
            catch (exception e)
            {
                responsebool = false;
                responsemsg = $"查询存储过程时出现异常，存储过程：【{storedprocedurename}】\n 异常原因：【{e.message}】\n 异常详细信息：【{e.stacktrace}】！";
            }
            return responseds;
        }

 

        当存储过程执行时间太长时，存储过程的默认超时时间是30s，需要设置存储过程执行超时时间

        /// <summary>
        /// 调用存储过程  (自定义超时时间)
        /// </summary>
        /// <param name="connstr">连接字符串</param>
        /// <param name="storedprocedurename">存储过程名称</param>
        /// <param name="commandouttime">执行存储过程请求超时时间(单位：s)</param>
        /// <param name="responsebool">存储过程执行状态</param>
        /// <param name="responsemsg">执行存储过程状态描述</param>
        /// <param name="paramsobject">存储过程输入参数</param>
        /// <returns></returns>
        public static dataset sql_getstoredprocedurefunction(string connstr, string storedprocedurename, int commandouttime, out bool responsebool, out string responsemsg, params parameterkeyvaluesentity[] paramsobject)
        {
            dataset responseds = new dataset();
            responsebool = true;
            responsemsg = "获取成功！";
            try
            {
                using (sqlconnection sqlconn = new sqlconnection(connstr))
                {
                    sqlconn.open();
                    using (sqlcommand sqlcmd = new sqlcommand(storedprocedurename, sqlconn))
                    {
                        sqlcmd.commandtype = commandtype.storedprocedure;
                        sqlcmd.commandtimeout = commandouttime;
                        if (paramsobject.count() > 0)
                        {
                            sqlparameter[] sqlparameters = new sqlparameter[paramsobject.count()];
                            for (int i = 0; i < paramsobject.count(); i++)
                            {
                                sqlparameter sqlparameter = new sqlparameter(paramsobject[i].key, paramsobject[i].value);
                                sqlcmd.parameters.add(sqlparameter);
                            }
                        }
                        sqldataadapter sda = new sqldataadapter(sqlcmd);
                        sda.fill(responseds);
                    }
                }
            }
            catch (exception e)
            {
                responsebool = false;
                responsemsg = $"查询存储过程时出现异常，存储过程：【{storedprocedurename}】\n 异常原因：【{e.message}】\n 异常详细信息：【{e.stacktrace}】！";
            }
            return responseds;
        }

 

        执行sql语句，进行增删改操作

        /// <summary>
        /// 增删改数据
        /// </summary>
        /// <param name="sqlconnstr, ">数据库连接字符串</param>
        /// <param name="sql">执行的sql语句</param>
        /// <param name="paramsobject">输入参数</param>
        /// <returns></returns>
        public static int sqlexecutedata(string sqlconnstr, string sql, params parameterkeyvaluesentity[] paramsobject)
        {
            int count = 0;
            using (sqlconnection conn = new sqlconnection(sqlconnstr))
            {
                conn.open();
                sqlcommand cmd = new sqlcommand(sql, conn); //定义一个sql操作命令对象
                if (paramsobject.count() > 0)
                {
                    for (int i = 0; i < paramsobject.count(); i++)
                    {
                        sqlparameter sqlparameter = new sqlparameter(paramsobject[i].key, paramsobject[i].value);
                        cmd.parameters.add(sqlparameter);
                    }
                }

                count = cmd.executenonquery(); //执行语句
                conn.close(); //关闭连接
                cmd = null;
                conn.dispose(); //释放对象
            }

            return count;
        }

 

      当数据库中表关系及其复杂，并且数据量特别多的时候(一般情况下用缓存解决问题)，执行sql查询语句相当耗时，需要设置sql语句请求超时时间。

      执行sql查询语句，设置sql查询语句超时时间
        /// <summary>
        /// 执行sql脚本
        /// </summary>
        /// <param name="connstr">连接字符串</param>
        /// <param name="sqlscript">sql脚本</param>
        /// <param name="responsebool">执行状态</param>
        /// <param name="responsemsg">状态描述</param>
        /// <param name="commandouttime">执行sql语句请求超时时间(单位：s)</param>
        /// <param name="paramsobject">输入参数</param>
        /// <returns></returns>
        public static dataset sql_getstored(string connstr, string sqlscript, out bool responsebool, out string responsemsg, int commandouttime = 500, params parameterkeyvaluesentity[] paramsobject)
        {
            dataset responseds = new dataset();
            responsebool = true;
            responsemsg = "获取成功！";
            try
            {
                using (sqlconnection sqlconn = new sqlconnection(connstr))
                {
                    sqlconn.open();
                    using (sqlcommand sqlcmd = new sqlcommand(sqlscript, sqlconn))
                    {
                        sqlcmd.commandtype = commandtype.text;
                        sqlcmd.commandtimeout = commandouttime;
                        if (paramsobject.count() > 0)
                        {
                            for (int i = 0; i < paramsobject.count(); i++)
                            {
                                sqlparameter sqlparameter = new sqlparameter(paramsobject[i].key, paramsobject[i].value);
                                sqlcmd.parameters.add(sqlparameter);
                            }
                        }
                        sqldataadapter sda = new sqldataadapter(sqlcmd);
                        sda.fill(responseds);
                    }
                }
            }
            catch (exception e)
            {
                responsebool = false;
                responsemsg = $"查询存储过程时出现异常，sql脚本：【{sqlscript}】\n 异常原因：【{e.message}】\n 异常详细信息：【{e.stacktrace}】！";
            }
            return responseds;
        }

    

    入参实体建类
    /// <summary>
    /// 输入参数实体   参数名称(key)/参数值(value)
    /// </summary>
    public class parameterkeyvaluesentity
    {
        /// <summary>
        /// 参数名称
        /// </summary>
        public string key { get; set; }
        /// <summary>
        /// 参数值
        /// </summary>
        public object value { get; set; }
    }

 

 

 

 

 

 执行存储过程示例：

 public result 方法名(string 入参1,string 入参2, string 入参3)
        {
            try
            {            
                //定义输出参数
                result result = new result();
                //存储过程名称
                string procname = "存储过程名称";
                #region -- 执行存储过程获取数据
                //返回值状态
                bool responsebool = true;
                //返回值状态描述
                string responsemsg = string.empty;
                //存储过程输入参数实体
                parameterkeyvaluesentity[] parameterkeyvalue = new parameterkeyvaluesentity[]
                {
                new parameterkeyvaluesentity(){key="@存储过程入参1",value=赋值1},
                new parameterkeyvaluesentity(){key="@存储过程入参2",value=赋值2},
                new parameterkeyvaluesentity(){key="@存储过程入参3",value=赋值3},        
                };
                //使用sql通用类的方法执行存储过程
                dataset ds = sqlhelper.sql_getstoredprocedurefunction(connstr, procname, out responsebool, out responsemsg, parameterkeyvalue);
                if (!responsebool)
                {
                    result.code = "204";
                    result.msg = $"查询存储过程时出现异常，异常信息：{responsemsg}";
                    exceptionloghelper.writelog($"业务异常：存储过程名：{procname}---异常信息：{responsemsg}");//项目中的异常日志
                    return result;
                }
                datatable dt = ds.tables[0];            
                if (dt != null && dt.rows != null && dt.rows.count > 0)
                {
                    获取存储过程执行后的数据，给实体类赋值
                }
                #endregion
                result.data = loopbackdata;
                string json = jsonconvert.serializeobject(result.data);
                result = resulthelper.returnresultsuccess(json, typeof(jobject));
                return result;
            }
            catch (exception e)
            {
                exceptionloghelper.writelog($"业务异常：{e}");
                return resulthelper.returnresulterror($"异常信息：{e}");
            }
        }

 

       the end.......................