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();