2021-05-21 仓库温控系统(Winform) 09 DAL层搭建

╰+攻爆jí腚メ 2023-01-23 02:54 10阅读 0赞

## 一：查询相关类 ##

public class BQuery<T>
    { 
            #region 查询
            /// <summary>
            /// 按条件查询获取实体信息（单个）
            /// </summary>
            /// <param name="strWhere"></param>
            /// <param name="strCols"></param>
            /// <param name="paras"></param>
            /// <returns></returns>
            public T GetModel(string strWhere, string strCols, params SqlParameter[] paras)
            { 
                    //生成查询语句
                    string selSql = CreateSql.CreateSelectSql<T>(strWhere, strCols);
                    //生成Reader对象
                    SqlDataReader reader = SqlHelper.ExecuteReader(selSql, 1, paras);
                    //转换为实体对象
                    T model = DbConvert.SqlDataReaderToModel<T>(reader, strCols);
                    //关闭阅读器
                    reader.Close();
                    return model;
            }
    
            /// <summary>
            /// 根据Id获取信息实体
            /// </summary>
            /// <param name="id"></param>
            /// <param name="strCols"></param>
            /// <returns></returns>
            public T GetById(int id, string strCols)
            { 
                    Type type = typeof(T);
                    //构建条件
                    string strWhere = $"[{type.GetPrimary()}]=@Id";
                    SqlParameter[] paras = {  new SqlParameter("@Id", id) };
                    return GetModel(strWhere, strCols, paras);
            }
    
            /// <summary>
            /// 根据条件判断存在
            /// </summary>
            /// <param name="strWhere"></param>
            /// <param name="paras"></param>
            /// <returns>true or false</returns>
            public bool Exists(string strWhere, params SqlParameter[] paras)
            { 
                    Type type = typeof(T);
                    string sql = $"SELECT COUNT(1) FROM {type.GetTName()} WHERE {strWhere}";
                    object val = SqlHelper.ExecuteScalar(sql, 1, paras);
                    if (val.GetInt() > 0)
                            return true;
                    else
                            return false;
            }
    
            /// <summary>
            /// 根据 名称 判断是否已存在
            /// </summary>
            /// <param name="sName">名称列名</param>
            /// <param name="vName">名称值</param>
            /// <param name="id"></param>
            /// <returns></returns>
            public bool ExistsByName(string sName, string vName)
            { 
                    string strWhere = $"{sName}=@{sName}";
                    strWhere += " and IsDeleted=0";//有效数据查询
                    SqlParameter[] paras = { 
                       new SqlParameter($"@{sName}", vName)
                   };
                    return Exists(strWhere, paras);
            }
    
            /// <summary>
            /// 同一级别下,检查是否同名
            /// </summary>
            /// <param name="sName">名称列名</param>
            /// <param name="vName">名称值</param>
            /// <param name="sParent">父级Id列名</param>
            /// <param name="parId">父级Id</param>
            /// <returns></returns>
            public bool ExistsByName(string sName, string vName, string sParent, int parId)
            { 
                    string strWhere = $"{sName}=@{sName}";
                    if (parId > 0)
                            strWhere += $" and {sParent}=@{sParent}";
                    strWhere += " and IsDeleted=0";
                    SqlParameter[] paras = { 
                            new SqlParameter($"@{sName}", vName),
                             new SqlParameter($"@{sParent}", parId)
                   };
                    return Exists(strWhere, paras);
            }
    
            /// <summary>
            /// 获取所有列表
            /// </summary>
            /// <param name="cols"></param>
            /// <param name="IsDeleted">删除标识值 0 1</param>
            /// <returns></returns>
            public List<T> GetModelList(string cols,int IsDeleted)
            { 
                    return GetModelList($"IsDeleted={IsDeleted}", cols);
            }
    
            //public List<T> GetDeletedModelList(string cols)
            //{ 
            // return GetModelList("IsDeleted=1", cols);
            //}
    
            /// <summary>
            /// 按条件查询返回实体列表 SqlDataReader---》查询效率高 
            /// </summary>
            /// <param name="strWhere">条件</param>
            /// <param name="strCols">查询字段</param>
            /// <param name="paras">参数数组</param>
            /// <returns>List<T></returns>
            public List<T> GetModelList(string strWhere, string strCols, params SqlParameter[] paras)
            { 
                    //if (string.IsNullOrEmpty(strWhere))
                    // strWhere = "1=1";
                    //生成查询语句
                    string selSql = CreateSql.CreateSelectSql<T>(strWhere, strCols);
                    //生成Reader
                    SqlDataReader reader = SqlHelper.ExecuteReader(selSql, 1, paras);
                    //转换为List<T>列表
                    List<T> list = DbConvert.SqlDataReaderToList<T>(reader, strCols);
                    //关闭阅读器
                    reader.Close();
                    return list;
            }
            /// <summary>
            /// 返回带行序号的列表
            /// </summary>
            /// <param name="strWhere"></param>
            /// <param name="strCols">strCols 不能包含Id</param>
            /// <param name="paras"></param>
            /// <returns></returns>
            public List<T> GetRowsModelList(string strWhere, string strCols, params SqlParameter[] paras)
            { 
                    if (string.IsNullOrEmpty(strWhere))
                            strWhere = "1=1";
                    //生成查询语句
                    string selSql = CreateSql.CreateRowsSelectSql<T>(strWhere, strCols);
                    //生成Reader
                    SqlDataReader reader = SqlHelper.ExecuteReader(selSql, 1, paras);
                    //转换为List<T>列表
                    List<T> list = DbConvert.SqlDataReaderToList<T>(reader, strCols + ",Id");
                    //关闭阅读器
                    reader.Close();
                    return list;
    
            }
    
            /// <summary>
            /// 执行sql语句或存储过程，返回DataTable
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="isProc"></param>
            /// <param name="listCols"></param>
            /// <param name="paras"></param>
            /// <returns></returns>
            public DataTable GetList(string sql, int isProc,  params SqlParameter[] paras)
            { 
                    DataTable dt = SqlHelper.GetDataTable(sql, isProc, paras);
                    return dt;
            }
    
            /// <summary>
            /// 执行sql语句或存储过程，返回DataSet 可以是多个结果集
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="isProc"></param>
            /// <param name="paras"></param>
            /// <returns></returns>
            public DataSet GetDs(string sql, int isProc, params SqlParameter[] paras)
            { 
                    DataSet ds = SqlHelper.GetDataSet(sql, isProc, paras);
                    return ds;
            }
    
            /// <summary>
            /// 分页查询
            /// </summary>
            /// <typeparam name="T1"></typeparam>
            /// <param name="sql"></param>
            /// <param name="strCols"></param>
            /// <param name="startIndex"></param>
            /// <param name="pageSize"></param>
            /// <returns></returns>
            public PageModel<T> GetPageList(string sql, string strCols, int startIndex, int pageSize)
            { 
                    SqlParameter[] paras ={ 
                    new SqlParameter("@sql",sql),
                    new SqlParameter("@startIndex",startIndex),
                    new SqlParameter("@endIndex",startIndex +pageSize -1)
                             };
                    DataSet ds = GetDs("proc_Page", 2, paras);
                    int total = (int)ds.Tables[0].Rows[0][0];
                    List<T> list = DbConvert.DataTableToList<T>(ds.Tables[1], strCols);
                    return new PageModel<T>() {  TotalCount = total, ReList = list };
            }
    
           
    
            #endregion
    }

## 二：增删改相关类 ##

public class BaseDAL<T> : BQuery<T> where T : class
    { 
    
            #region 添加
            /// <summary>
            /// 添加实体信息
            /// </summary>
            /// <param name="t"></param>
            /// <param name="strCols">插入列名字符串，若为空，则全插入</param>
            /// <returns></returns>
            public int Add(T t, string strCols, int isReturn)
            { 
                    if (t == null)
                            return 0;
                    //获取生成的sql和参数列表 sql Paras 参数数组 SqlParameter[] 
                    SqlModel insert = CreateSql.GetInsertSqlAndParas<T>(t, strCols, isReturn);
                    //执行sql命令
                    if (isReturn == 0)  //受影响 1 失败 0
                            return SqlHelper.ExecuteNonQuery(insert.Sql, 1, insert.SqlParaArray);
                    else
                    { 
                            //sql insert select @@identity 主键值
                            object oId = SqlHelper.ExecuteScalar(insert.Sql, 1, insert.SqlParaArray);
                            if (oId != null && oId.ToString() != "")
                                    return oId.GetInt();
                            else
                                    return 0;
                    }
            }
    
            /// <summary>
            /// 批量插入
            /// </summary>
            /// <param name="list"></param>
            /// <param name="strCols"></param>
            /// <returns></returns>
            public bool AddList(List<T> list, string strCols)
            { 
                    if (list == null || list.Count == 0)
                            return false;
                    List<CommandInfo> comList = new List<CommandInfo>();
                    foreach (T t in list)
                    { 
                            SqlModel insert = CreateSql.GetInsertSqlAndParas<T>(t, strCols, 0);//sql paras
                            CommandInfo com = new CommandInfo(insert.Sql, false, insert.SqlParaArray);
                            comList.Add(com);
                    }
                    return SqlHelper.ExecuteTrans(comList);
            }
            #endregion
    
            #region 修改
    
            /// <summary>
            /// 修改实体 以主键为条件定位
            /// </summary>
            /// <param name="t"></param>
            /// <param name="strCols">也包括Id列</param>
            /// <returns></returns>
            public bool Update(T t, string strCols)
            { 
                    if (t == null)
                            return false;
                    else
                            return Update(t, strCols, "");
            }
    
            /// <summary>
            /// 修改信息实体
            /// </summary>
            /// <param name="t"></param>
            /// <param name="strCols">要修改的列 标识列名</param>
            /// <param name="strWhere">另外附加条件 </param>
            /// <returns></returns>
            public bool Update(T t, string strCols, string strWhere, params SqlParameter[] paras)
            { 
                    if (t == null)
                            return false;
                    //获取生成的sql和参数列表
                    SqlModel update = CreateSql.GetUpdateSqlAndParas<T>(t, strCols, strWhere);
                    List<SqlParameter> listParas = update.SqlParaArray.ToList();
                    if (paras != null && paras.Length > 0)
                            listParas.AddRange(paras);
                    //执行sql命令
                    return SqlHelper.ExecuteNonQuery(update.Sql, 1, listParas.ToArray()) > 0;
            }
    
            /// <summary>
            /// 批量修改
            /// </summary>
            /// <param name="list"></param>
            /// <param name="strCols"></param>
            /// <returns></returns>
            public bool UpdateList(List<T> list, string strCols)
            { 
                    if (list == null || list.Count == 0)
                            return false;
                    List<CommandInfo> comList = new List<CommandInfo>();
                    foreach (T t in list)
                    { 
                            SqlModel update = CreateSql.GetUpdateSqlAndParas<T>(t, strCols, "");
                            CommandInfo com = new CommandInfo(update.Sql, false, update.SqlParaArray);
                            comList.Add(com);
                    }
                    return SqlHelper.ExecuteTrans(comList);
    
            }
            #endregion
    
            #region 删除
            /// <summary>
            /// 根据Id删除 这里id是主键 delType=1 真删除 0 假删除
            /// </summary>
            /// <param name="id"></param>
            /// <returns></returns>
            public bool Delete(int id, int delType, int isDeleted)
            { 
                    Type type = typeof(T);
                    string strWhere = $"[{type.GetPrimary()}]=@Id";
                    SqlParameter[] paras =
                    { 
                            new SqlParameter("@Id",id)
                        };
                    return Delete(delType, strWhere, isDeleted, paras);
            }
    
            /// <summary>
            /// 按条件删除数据(假删除，包含可以恢复) 真删除 ---Delete 
            /// </summary>
            /// <param name="actType">删除类型 0 假 1 真</param>
            /// <param name="strWhere">条件</param>
            /// <param name="isDeleted">删除标识值 0 1 2</param>
            /// <param name="paras">参数列表</param>
            /// <returns></returns>
            public bool Delete(int actType, string strWhere, int isDeleted, SqlParameter[] paras)
            { 
                    Type type = typeof(T);
                    string delSql = "";
                    //删除语句的生成
                    if (actType == 1)
                            delSql = CreateSql.CreateDeleteSql<T>(strWhere);
                    else
                            delSql = CreateSql.CreateLogicDeleteSql<T>(strWhere, isDeleted);
                    List<CommandInfo> list = new List<CommandInfo>();
                    //可能会批量的删除或修改 ----启用事务 ---一致性提交
                    list.Add(new CommandInfo()
                    { 
                            CommandText = delSql,
                            IsProc = false,
                            Paras = paras
                    });
                    return SqlHelper.ExecuteTrans(list);
            }
    
            /// <summary>
            /// 批量删除
            /// </summary>
            /// <param name="idList"></param>
            /// <returns></returns>
            public bool DeleteList(List<int> idList, int actType, int isDeleted)
            { 
                    Type type = typeof(T);
                    List<CommandInfo> comList = new List<CommandInfo>();
                    foreach (int id in idList)
                    { 
                            string strWhere = $"[{type.GetPrimary()}]=@Id";
                            string delSql = "";
                            if (actType == 1)
                                    delSql = CreateSql.CreateDeleteSql<T>(strWhere);
                            else
                                    delSql = CreateSql.CreateLogicDeleteSql<T>(strWhere, isDeleted);
                            SqlParameter[] paras ={ 
                                      new SqlParameter("@Id",id)
                                   };
                            CommandInfo com = new CommandInfo(delSql, false, paras);
                            comList.Add(com);
                    }
                    return SqlHelper.ExecuteTrans(comList);
            }
            #endregion
    
    
    }