C# 使用SqlSugar搭建数据仓库
通过NuGet获取SqlSugar
SqlsugarClient访问类
public class SugarFactory { private static string strConnectionString = string.Empty; #region 数据库访问对象 /// <summary> /// 数据库访问对象 /// </summary> private static SqlSugarClient db = null; #endregion 数据库访问对象 #region 私有构造函数，禁止实例化 //私有构造函数，禁止实例化 private SugarFactory() { } #endregion 私有构造函数，禁止实例化 #region 初始化SqlSugarClient /// <summary> /// 初始化SqlSugarClient /// </summary> /// <returns>返回SqlSugarClient对象</returns> public static SqlSugarClient GetInstance() { if (strConnectionString == string.Empty) { strConnectionString = "Data Source=192.168.4.61;Initial Catalog=ReportServer;User ID=sa;Password=123456"; } db = new SqlSugarClient(new ConnectionConfig() { ConnectionString = strConnectionString, DbType = DbType.SqlServer, IsAutoCloseConnection = true }); return db; } #endregion 初始化SqlSugarClient }
数据访问仓库接口类
/// <summary> /// 数据访问仓库接口 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> public interface IRepositoryBase<TEntity> where TEntity : class { /// <summary> /// 获取数据库操作实例 /// </summary> /// <returns>返回连接对象</returns> SqlSugarClient GetInstance(); /// <summary> /// 插入实体 忽略NULL /// </summary> /// <param name="entity">实体对象</param> /// <returns>插入成功返回true</returns> bool Insert(TEntity entity); /// <summary> /// 插入实体,返回自增列的值 /// </summary> /// <param name="entity">实体对象</param> /// <returns>插入成功返回true</returns> int InsertReturnIdentity(TEntity entity); /// <summary> /// 插入实体，返回自增列的值 /// </summary> /// <param name="entity">实体对象</param> /// <returns>插入成功返回true</returns> long InsertReturnBigIdentity(TEntity entity); /// <summary> /// 根据唯一主键删除 /// </summary> /// <param name="keyValue">唯一主键</param> /// <returns>删除成功返回true</returns> bool Delete(object keyValue); /// <summary> /// 根据表达式删除 /// </summary> /// <param name="expression">表达式</param> /// <returns>删除成功返回true</returns> bool Delete(Expression<Func<TEntity, bool>> expression); /// <summary> /// 将实体对象更新到数据库 忽略属性为NULL /// </summary> /// <param name="entity">必需包含主键并且不能为匿名对象</param> /// <returns>更新成功返回true</returns> bool Update(TEntity entity); /// <summary> /// 更新或者写入 /// </summary> /// <param name="entity"></param> /// <returns></returns> TEntity UpdateOrInsert(TEntity entity); /// <summary> /// 检测主键对应的数据是否存在 /// </summary> /// <param name="ID">主键值</param> /// <returns>存在返回true,不存在返回false</returns> bool IsExist(int ID); /// <summary> /// 检测主键对应的数据是否存在 /// </summary> /// <param name="ID">主键值</param> /// <returns>存在返回true,不存在返回false</returns> bool IsExist(string ID); /// <summary> /// 根据主键查询对象实体 /// </summary> /// <param name="keyValue">主键</param> /// <returns>实体</returns> TEntity FindEntity(object keyValue); /// <summary> /// 根据条件查询第一条数据 /// </summary> /// <param name="expression">表达式</param> /// <returns>实体</returns> TEntity FindFirst(Expression<Func<TEntity, bool>> expression); /// <summary> /// 查找符合表达式的List集合 /// </summary> /// <param name="expression">表达式</param> /// <returns>实体集合</returns> List<TEntity> FindList(Expression<Func<TEntity, bool>> expression); /// <summary> /// 查询所有数据 /// </summary> /// <returns></returns> List<TEntity> FindAllList(); /// <summary> /// 查找符合表达式的List集合 /// </summary> /// <returns>实体集合</returns> List<TEntity> GetList(string strsql); /// <summary> /// 查找符合表达式的List集合 /// </summary> /// <param name="intTop">前几行</param> /// <param name="expression">表达式</param> /// <returns>实体集合</returns> List<TEntity> FindTopList(int intTop, Expression<Func<TEntity, bool>> expression); /// <summary> /// 获得记录数 /// </summary> /// <param name="pageWhere">查询条件</param> /// <param name="tableName">查询的表名</param> /// <returns>返回ListModel</returns> int GetListCount(string pageWhere, string tableName); /// <summary> /// 获得分页数据 /// </summary> /// <param name="pageSize">每页数量</param> /// <param name="pageIndex">第几页</param> /// <param name="pageWhere">查询条件</param> /// <param name="pageOrder">排序</param> /// <param name="tableName">查询的表名</param> /// <returns>返回ListModel</returns> List<TEntity> GetListPageData(int pageSize, int pageIndex, string pageWhere, string pageOrder, string tableName); /// <summary> /// 获取满足表达式的记录条数 /// </summary> /// <param name="expression">表达式</param> /// <returns></returns> int FindCount(Expression<Func<TEntity, bool>> expression); }
仓库基类
/// <summary> /// 仓储基类 /// </summary> /// <typeparam name="TEntity"></typeparam> public class RepositoryBase<TEntity> : IRepositoryBase<TEntity> where TEntity : class, new() { /// <summary> /// 获取数据库实例，公开只要是想在外部直接是用 /// </summary> /// <returns>返回连接对象</returns> public SqlSugarClient GetInstance() { return SugarFactory.GetInstance(); } /// <summary> /// 插入实体 忽略NULL /// 对于自增列的值赋值没有意义，赋值和不赋值运行效果一样 /// </summary> /// <param name="entity">实体对象</param> /// <returns>插入成功返回true</returns> public virtual bool Insert(TEntity entity) { try { using (var db = GetInstance()) { return db.Insertable(entity).ExecuteCommand() > 0; } } catch (Exception ex) { return false; } } /// <summary> /// 插入实体,返回自增列的值 /// 对于自增列的值赋值没有意义，赋值和不赋值运行效果一样 /// </summary> /// <param name="entity">实体对象</param> /// <returns>插入成功返回true</returns> public virtual int InsertReturnIdentity(TEntity entity) { try { using (var db = GetInstance()) { return db.Insertable(entity).ExecuteReturnIdentity(); } } catch (Exception ex) { return 0; } } /// <summary> /// 插入实体，返回自增列的值 /// 对于自增列的值赋值没有意义，赋值和不赋值运行效果一样 /// </summary> /// <param name="entity">实体对象</param> /// <returns>插入成功返回true</returns> public virtual long InsertReturnBigIdentity(TEntity entity) { try { using (var db = GetInstance()) { return db.Insertable(entity).ExecuteReturnBigIdentity(); } } catch (Exception ex) { return 0; } } /// <summary> /// 根据唯一主键删除 /// 主键不存在，为False /// </summary> /// <param name="keyValue">唯一主键</param> /// <returns>删除成功返回true</returns> public virtual bool Delete(object keyValue) { try { using (var db = GetInstance()) { return db.Deleteable<TEntity>().In(keyValue).ExecuteCommand() > 0; } } catch (Exception ex) { return false; } } /// <summary> /// 根据表达式删除 /// </summary> /// <param name="expression">表达式</param> /// <returns>删除成功返回true</returns> public virtual bool Delete(Expression<Func<TEntity, bool>> expression) { try { using (var db = GetInstance()) { return db.Deleteable<TEntity>().Where(expression).ExecuteCommand() > 0; } } catch (Exception ex) { return false; } } /// <summary> /// 将实体对象更新到数据库 忽略属性为NULL /// 主键在数据库里不存在则返回False /// 没有主键则返回False /// </summary> /// <param name="entity">必需包含主键并且不能为匿名对象</param> /// <returns>更新成功返回true</returns> public virtual bool Update(TEntity entity) { try { using (var db = GetInstance()) { return db.Updateable(entity).ExecuteCommand() > 0; } } catch (Exception ex) { return false; } } /// <summary> /// 将实体对象更新到数据库 忽略属性为NULL /// 修改主键不存在的，会新增（不包括主键的）该信息 /// 没有主键的修改，则新增该信息 /// </summary> /// <param name="entity">必需包含主键并且不能为匿名对象</param> /// <returns>更新成功返回true</returns> public virtual TEntity UpdateOrInsert(TEntity entity) { try { using (var db = GetInstance()) { return db.Saveable<TEntity>(entity).ExecuteReturnEntity(); } } catch (Exception ex) { return null; } } /// <summary> /// 检测主键对应的数据是否存在 /// </summary> /// <param name="ID">主键值</param> /// <returns>存在返回true,不存在返回false</returns> public bool IsExist(int ID) { bool ReturnResult = false; using (var db = GetInstance()) { List<TEntity> list = db.Queryable<TEntity>().In(new int[] { ID }).ToList(); if (list != null) { if (list.Count == 0) { ReturnResult = false; } else { ReturnResult = true; } } else { ReturnResult = false; } } return ReturnResult; } /// <summary> /// 检测主键对应的数据是否存在 /// </summary> /// <param name="ID">主键值</param> /// <returns>存在返回true,不存在返回false</returns> public bool IsExist(string ID) { bool ReturnResult = false; using (var db = GetInstance()) { List<TEntity> list = db.Queryable<TEntity>().In(new string[] { ID }).ToList(); if (list != null) { if (list.Count == 0) { ReturnResult = false; } else { ReturnResult = true; } } else { ReturnResult = false; } } return ReturnResult; } /// <summary> /// 根据主键查询对象实体 /// </summary> /// <param name="keyValue">主键</param> /// <returns>实体</returns> public virtual TEntity FindEntity(object keyValue) { try { using (var db = GetInstance()) { return db.Queryable<TEntity>().InSingle(keyValue); }; } catch (Exception ex) { return null; } } /// <summary> /// 根据条件查询第一条数据 /// </summary> /// <returns>实体</returns> public virtual TEntity FindFirst(Expression<Func<TEntity, bool>> expression) { try { using (var db = GetInstance()) { return db.Queryable<TEntity>().Where(expression).First(); }; } catch (Exception ex) { return null; } } /// <summary> /// 查找符合表达式的List集合 /// </summary> /// <param name="expression">表达式</param> /// <returns>实体集合</returns> public virtual List<TEntity> FindList(Expression<Func<TEntity, bool>> expression) { try { using (var db = GetInstance()) { return db.Queryable<TEntity>().Where(expression).ToList(); }; } catch (Exception ex) { return new List<TEntity>(); } } /// <summary> /// /// </summary> /// <param name="expression"></param> /// <returns></returns> public virtual List<TEntity> FindListReturnNull(Expression<Func<TEntity, bool>> expression) { try { using (var db = GetInstance()) { return db.Queryable<TEntity>().Where(expression).ToList(); }; } catch (Exception ex) { return null; } } /// <summary> /// 查找所有List集合 /// </summary> /// <returns>实体集合</returns> public virtual List<TEntity> FindAllList() { try { using (var db = GetInstance()) { return db.Queryable<TEntity>().ToList(); }; } catch (Exception ex) { return new List<TEntity>(); } } /// <summary> /// 查找符合条件的实体集合 /// </summary> /// <param name="strsql">sql语句</param> /// <returns>实体集合</returns> public virtual List<TEntity> GetList(string strsql) { try { using (var db = GetInstance()) { return db.Queryable<TEntity>().Where(strsql).ToList(); }; } catch (Exception ex) { return new List<TEntity>(); } } /// <summary> /// 查找符合表达式的List集合 /// </summary> /// <param name="intTop">前几行</param> /// <param name="expression">表达式</param> /// <returns>实体集合</returns> public virtual List<TEntity> FindTopList(int intTop, Expression<Func<TEntity, bool>> expression) { try { using (var db = GetInstance()) { return db.Queryable<TEntity>().Take(intTop).Where(expression).ToList(); }; } catch (Exception ex) { return new List<TEntity>(); } } #region 获得记录数 /// <summary> /// 获得记录数 /// 不是本表的也可以进行查询，但是建议只查本表的 /// /// </summary> /// <param name="pageWhere">查询条件</param> /// <param name="tableName">查询的表名 </param> /// <returns>返回ListModel</returns> public int GetListCount(string pageWhere, string tableName) { int ReturnVale = 0; using (var db = GetInstance()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from " + tableName); if (pageWhere.Trim() != "") { strSql.Append(" where " + pageWhere); } ReturnVale = db.Ado.GetInt(DataAdapterHelper.CreateCountSQL(tableName, pageWhere)); } catch { } } return ReturnVale; } #endregion /// <summary> /// 获得分页数据 /// pageSize小于0返回空，等于0返回全部 /// pageIndex小于1则返回第一页，大于最大页数返回最后一页 /// pageWhere必填项，不可为"",可写为1=1 /// pageOrder必填项，不可为"",可写为 1（等数字）或者 ID （可倒序排列 ID desc） /// tableName必填项，不可写其他表名 /// </summary> /// <param name="pageSize">每页数量</param> /// <param name="pageIndex">第几页</param> /// <param name="pageWhere">查询条件</param> /// <param name="pageOrder">排序</param> /// <param name="tableName">查询的表名</param> /// <returns>返回ListModel</returns> public List<TEntity> GetListPageData(int pageSize, int pageIndex, string pageWhere, string pageOrder, string tableName) { List<TEntity> ListData = new List<TEntity>(); using (var db = GetInstance()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from " + tableName); if (pageWhere.Trim() != "") { strSql.Append(" where " + pageWhere); } int totalCount = 0; totalCount = db.Ado.GetInt(DataAdapterHelper.CreateCountSQL(tableName, pageWhere)); ListData = db.Ado.SqlQuery<TEntity>(DataAdapterHelper.CreatePageSQL(totalCount, pageSize, pageIndex, strSql.ToString(), pageOrder, tableName)); } catch (Exception ex) { } } return ListData; } /// <summary> /// 获取满足表达式的记录条数 /// </summary> /// <param name="expression">表达式</param> /// <returns></returns> public int FindCount(Expression<Func<TEntity, bool>> expression) { try { using (var db = GetInstance()) { return db.Queryable<TEntity>().Count(expression); }; } catch (Exception ex) { return -1; } } }
DataAdapterHelper帮助类：
/// <summary> /// 单表管理帮助类 /// </summary> public class DataAdapterHelper { #region 获取记录总数SQL语句 /// <summary> /// 获取记录总数SQL语句 /// </summary> /// <param name="tableName">表名</param> /// <param name="pageWhere">条件</param> /// <returns>返回SQL语句</returns> public static string CreateCountSQL(string tableName, string pageWhere) { string ReturnValue = string.Empty; ReturnValue = SqlServerHelper.CreateCountSQL(tableName, pageWhere); return ReturnValue; } #endregion #region 获取分页SQL语句 /// <summary> /// 获取分页SQL语句 /// </summary> /// <param name="totalCount">记录总数</param> /// <param name="pageSize">每页记录数</param> /// <param name="pageIndex">当前页数</param> /// <param name="pageWhere">查询条件</param> /// <param name="pageOrder">排序</param> /// <param name="tableName">表名</param> /// <returns>返回SQL语句</returns> public static string CreatePageSQL(int totalCount, int pageSize, int pageIndex, string pageWhere, string pageOrder, string tableName) { string ReturnValue = string.Empty; ReturnValue = SqlServerHelper.CreatePageSQL(totalCount, pageSize, pageIndex, pageWhere, pageOrder, tableName); return ReturnValue; } #endregion }
SqlServerHelper帮助类：
/// <summary> /// 单表管理数据操作类 /// </summary> public class SqlServerHelper { #region 获取记录总数SQL语句 /// <summary> /// 获取记录总数SQL语句 /// </summary> /// <param name="tableName">表名</param> /// <param name="pageWhere">条件</param> /// <returns>返回SQL语句</returns> public static string CreateCountSQL(string tableName, string pageWhere) { return string.Format("SELECT COUNT(1) FROM {0} WHERE {1}", tableName, pageWhere); } #endregion #region 获取分页SQL语句 /// <summary> /// 获取分页SQL语句 /// </summary> /// <param name="totalCount">记录总数</param> /// <param name="pageSize">每页记录数</param> /// <param name="pageIndex">当前页数</param> /// <param name="pageWhere">查询条件</param> /// <param name="pageOrder">排序</param> /// <param name="tableName">表名</param> /// <returns>返回SQL语句</returns> public static string CreatePageSQL(int totalCount, int pageSize, int pageIndex, string pageWhere, string pageOrder, string tableName) { //计算总页数 pageSize = pageSize == 0 ? totalCount : pageSize; int pageCount = (totalCount + pageSize - 1) / pageSize; //检查当前页数 if (pageIndex < 1) { pageIndex = 1; } else if (pageIndex > pageCount) { pageIndex = pageCount; } //拼接SQL字符串，加上ROW_NUMBER函数进行分页 StringBuilder newSafeSql = new StringBuilder(); newSafeSql.AppendFormat("SELECT ROW_NUMBER() OVER(ORDER BY {0}) as row_number,", pageOrder); newSafeSql.Append(pageWhere.Substring(pageWhere.ToUpper().IndexOf("SELECT") + 6)); //拼接成最终的SQL语句 StringBuilder sbSql = new StringBuilder(); sbSql.Append("SELECT * FROM ("); sbSql.Append(newSafeSql.ToString()); sbSql.Append(") AS T"); sbSql.AppendFormat(" WHERE row_number between {0} and {1}", ((pageIndex - 1) * pageSize) + 1, pageIndex * pageSize); return sbSql.ToString(); } #endregion }
调用测试：
根据数据库创建表的对应实体类Student，查询调用：
RepositoryBase<Student> student= new RepositoryBase<Student>(); var model= student.FindAllList();
