.net 数据库封装类（适用于增删改查sql语句和存储过程）

╰+攻爆jí腚メ 2022-05-19 08:35 24阅读 0赞

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Configuration;
    
    using System.Data;
    using System.Data.SqlClient;
    
    namespace ReportForms.DAL
    {
        static class sqlHelper
        {
            static string connstr = "";
    
            static sqlHelper()
            {
                //初始化连接字符串
                connstr = System.Configuration.ConfigurationManager.ConnectionStrings["连接字符串的名字"].ToString();
    
            }
    
    
            /// <summary>
            /// 查询所有（返回多表数据）
            /// </summary>
            /// <param name="sql">SQL语句</param>
            /// <param name="plist">参数（在用的时候要是没有参数就直接写null）</param>
            /// <returns>DataSet</returns>
            public static DataSet Search(string sql, List<SqlParameter> plist)
            {
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        if (plist != null && plist.Count != 0)
                        {
                            cmd.Parameters.AddRange(plist.ToArray());
                        }
    
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        return ds;
                    }
                }
            }
    
            /// <summary>
            /// 查询所有（返回单表数据）
            /// </summary>
            /// <param name="sql">SQL语句</param>
            /// <param name="plist"></param>
            /// <returns>DataTable</returns>
            public static DataTable Searchdt(string sql, List<SqlParameter> plist)
            {
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
    
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        if (plist != null && plist.Count != 0)
                        {
                            cmd.Parameters.AddRange(plist.ToArray());
                        }
    
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        DataTable dt = new DataTable();
                        sda.Fill(dt);
                        return dt;
                    }
                }
    
            }
    
    
            /// <summary>
            /// 查询首行首列
            /// </summary>
            /// <param name="sql">SQL语句</param>
            /// <param name="plist"></param>
            /// <returns>object</returns>
            public static object SearchScalar(string sql, List<SqlParameter> plist)
            {
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        if (plist != null && plist.Count != 0)
                        {
                            cmd.Parameters.AddRange(plist.ToArray());
                        }
                        return cmd.ExecuteScalar();
                    }
                }
            }
    
    
            /// <summary>
            /// 用于执行增删改
            /// </summary>
            /// <param name="sql">SQL语句</param>
            /// <param name="plist"></param>
            /// <returns>int</returns>
            public static int RunSQL(string sql, List<SqlParameter> plist)
            {
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
    
                        if (plist != null && plist.Count != 0)
                        {
                            cmd.Parameters.AddRange(plist.ToArray());
                        }
                        //默认让SqlTransaction对象为空
                        SqlTransaction trans = null;
                        //开启事务：标志事务的开始
                        trans = conn.BeginTransaction();
                        try
                        {
                            //将创建的SqlTransaction对象分配给要执行的sqlCommand的Transaction属性
                            cmd.Transaction = trans;
                            //执行sql如果添加成功放回1
                            int count = cmd.ExecuteNonQuery();
    
                            if (count > 0)
                            {
                                trans.Commit();
                            }
                            else
                            {
                                //事务回滚
                                trans.Rollback();
                            }
                            return count;
                        }
                        catch (Exception)
                        {
                            //如果某个环节出现问题，则将整个事务回滚
                            trans.Rollback();
                            return cmd.ExecuteNonQuery();
                        }
    
                    }
    
                }
            }
    
            /// <summary>
            /// 用于执行储过程（返回单表数据）
            /// </summary>
            /// <param name="procname">存储过程名称</param>
            /// <param name="plist"></param>
            /// <returns>DataTable</returns>
            public static DataTable ExecProcDataTable(string procname, List<SqlParameter> plist)
            {
                using (SqlConnection conn = new SqlConnection(connstr))
                {
    
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(procname, conn))
                    {
                        //设置类型为存储过程
                        cmd.CommandType = CommandType.StoredProcedure;
    
                        if (plist != null && plist.Count != 0)
                        {
                            cmd.Parameters.AddRange(plist.ToArray());
                        }
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        DataTable dt = new DataTable();
    
                        sda.Fill(dt);
                        return dt;
                    }
                }
            }
    
    
            /// <summary>
            /// 用于执行储过程（返回多表数据）
            /// </summary>
            /// <param name="procname">存储过程名称</param>
            /// <param name="plist"></param>
            /// <returns>DataSet</returns>
            public static DataSet ExecProcDataSet(string procname, List<SqlParameter> plist)
            {
                using (SqlConnection conn = new SqlConnection(connstr))
                {
    
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(procname, conn))
                    {
                        //设置类型为存储过程
                        cmd.CommandType = CommandType.StoredProcedure;
    
                        if (plist != null && plist.Count != 0)
                        {
                            cmd.Parameters.AddRange(plist.ToArray());
                        }
    
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        DataSet ds = new DataSet();
    
                        sda.Fill(ds);
                        return ds;
                    }
                }
            }
    
            /// <summary>
            /// 用于增删改的存储过程
            /// </summary>
            /// <param name="procname">存储过程名称</param>
            /// <param name="plist"></param>
            /// <returns>int</returns>
            public static int ExecProc(string procname, List<SqlParameter> plist)
            {
                using (SqlConnection conn = new SqlConnection(connstr))
                {
    
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(procname, conn))
                    {
                        using (SqlTransaction trans = conn.BeginTransaction())
                        {
                            try
                            {
                                //设置类型为存储过程
                                cmd.CommandType = CommandType.StoredProcedure;
    
                                if (plist != null && plist.Count != 0)
                                {
                                    cmd.Parameters.AddRange(plist.ToArray());
                                }
    
                                //将创建的SqlTransaction对象分配给要执行的sqlCommand的Transaction属性
                                cmd.Transaction = trans;
                                //执行sql如果添加成功放回1
                                int count = cmd.ExecuteNonQuery();
    
                                if (count > 0)
                                {
                                    trans.Commit();
                                }
                                else
                                {
                                    //事务回滚
                                    trans.Rollback();
                                }
                                return count;
                            }
                            catch (Exception)
                            {
                                //如果某个环节出现问题，则将整个事务回滚
                                trans.Rollback();
                                return cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
        }
    }

如何调用带输出参数的储过程：
    
                SqlParameter TotalRows = new SqlParameter("@TotalRowsCount", SqlDbType.Int);
                //设置他为输出参数
                TotalRows.Direction = ParameterDirection.Output;
                //存储过程所需参数
                List<SqlParameter> plist = new List<SqlParameter>(){
                    new SqlParameter("@startDate",this.deStartDate.EditValue),
                    new SqlParameter("@endDate",this.deEndDate.EditValue),
                    new SqlParameter("@rows",PageRowsCount),
                    new SqlParameter("@page",CurrentPage),
                    new SqlParameter("@Dep",this.txtGroup.Text),
                    new SqlParameter("@styleNo",this.txtStyleNo.Text),
                    new SqlParameter("@orderNo",this.txtOrderNo.Text),
                    TotalRows
                };
    
              DataTable  GetSumDataDT = sqlHelper.ExecProcDataTable("proc_report_selectNotFineSumData", plist);
    
              //得到输出参数的值
             int TotalRowsCount = Convert.ToInt32(TotalRows.Value);

如何调用没有输出参数的存储过程：
    
    
                //存储过程所需参数
                List<SqlParameter> plist = new List<SqlParameter>(){
                    new SqlParameter("@beginTime",beginTime),
                    new SqlParameter("@endTime",endTime),
                    new SqlParameter("@styleNo",this.txtStyleNo.Text),
                    new SqlParameter("@depName",this.txtGroup.Text),     
                };
    
                DataTable GetKanBanDT = ERPHelper.ExecProc("Sp_GetKanBan", plist);