欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-07-11 11:31:47
如何使用C# 操作存储过程,执行sql语句? 闲话不多说,直接上代码: /// /// Sql通用类 /// public class SqlHelper { 首先配置连接字符串 public static string connStr = Configur ......

如何使用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.......................