/// <summary>
/// 生成实体类
/// </summary>
/// <param name="TableName">表名或视图名</param>
/// <param name="path">存放路劲</param>
/// <param name="nameSpace">命名空间</param>
public void CreatedModel(string TableName, string path, string nameSpace)
{
using (var db = SqlSugarHelper.GetInstance())
{
db.DbFirst.Where(TableName).CreateClassFile(path, nameSpace);
}
}
/// <summary>
/// 获取SqlSugar的SqlSugarClient
/// </summary>
/// <returns></returns>
public SqlSugarClient DbAccess()
{
using (var db = SqlSugarHelper.GetInstance())
{
return(db);
}
}
/// <summary>
/// 批量写入实体数据
/// </summary>
/// <param name="entitylist">实体类集合</param>
/// <returns></returns>
public long InsertBatch(List <T> entitylist)
{
using (var db = SqlSugarHelper.GetInstance())
{
var i = db.Insertable(entitylist.ToArray()).ExecuteCommand();
return(i);
}
}
/// <summary>
/// 根据主键查询单条数据（自定义返回值）
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="columns">自定义返回值表达式数</param>
/// <param name="pkValue">主键值</param>
/// <returns></returns>
public TResult FindById <TResult>(Expression <Func <T, TResult> > columns, object pkValue)
{
using (var db = SqlSugarHelper.GetInstance())
{
var list = db.Queryable <T>();
return(list.Select(columns).First());
}
}
/// <summary>
/// 更新自定义字段数据(不更新为NULL的列)
/// </summary>
/// <param name="entity"></param>
/// <param name="updateColumns">修改字段表达式树</param>
/// <param name="isNullColumn">是否不更新为NULL的字段</param>
/// <returns></returns>
public bool Update(T entity, Expression <Func <T, object> > updateColumns, bool isNullColumn = false)
{
using (var db = SqlSugarHelper.GetInstance())
{
var i = db.Updateable(entity).UpdateColumns(updateColumns).IgnoreColumns(ignoreAllNullColumns: isNullColumn).ExecuteCommand();
return(i > 0);
}
}
/// <summary>
/// 根据条件查询数据
/// </summary>
/// <param name="columns">自定义返回值表达式数</param>
/// <param name="predicate">条件表达式树</param>
/// <returns></returns>
public TResult FindByClause <TResult>(Expression <Func <T, TResult> > columns, Expression <Func <T, bool> > predicate)
{
using (var db = SqlSugarHelper.GetInstance())
{
var entity = db.Queryable <T>().Where(predicate).Select(columns).First();
return(entity);
}
}
/// <summary>
/// 根据条件查询数据
/// </summary>
/// <param name="predicate">条件表达式树</param>
/// <returns></returns>
public T FindByClause(Expression <Func <T, bool> > predicate)
{
using (var db = SqlSugarHelper.GetInstance())
{
var entity = db.Queryable <T>().First(predicate);
return(entity);
}
}
/// <summary>
/// 根据条件修改自定义字段数据（没有主键的情况下更新）
/// </summary>
/// <param name="entity">修改的实体</param>
/// <param name="updateColumns">需要修改的字段</param>
/// <param name="predicate">条件</param>
/// <returns></returns>
public bool UpdateWhere(T entity, Expression <Func <T, object> > updateColumns, Expression <Func <T, bool> > predicate)
{
using (var db = SqlSugarHelper.GetInstance())
{
var i = db.Updateable(entity).UpdateColumns(updateColumns).Where(predicate).ExecuteCommand();
return(i > 0);
}
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="entity">实体类</param>
/// <returns></returns>
public bool Delete(T entity)
{
using (var db = SqlSugarHelper.GetInstance())
{
var i = db.Deleteable(entity).ExecuteCommand();
return(i > 0);
}
}
/// <summary>
/// 根据主值查询单条数据
/// </summary>
/// <param name="pkValue">主键值</param>
/// <returns>泛型实体</returns>
public T FindById(object pkValue)
{
using (var db = SqlSugarHelper.GetInstance())
{
var entity = db.Queryable <T>().InSingle(pkValue);
return(entity);
}
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="where">过滤条件</param>
/// <returns></returns>
public bool Delete(Expression <Func <T, bool> > @where)
{
using (var db = SqlSugarHelper.GetInstance())
{
var i = db.Deleteable <T>(@where).ExecuteCommand();
return(i > 0);
}
}
/// <summary>
/// 删除指定ID集合的数据(批量删除)
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public bool DeleteByIds(object[] ids)
{
using (var db = SqlSugarHelper.GetInstance())
{
var i = db.Deleteable <T>().In(ids).ExecuteCommand();
return(i > 0);
}
}
/// <summary>
/// 查询所有数据(无分页,请慎用)
/// </summary>
/// <returns></returns>
public IEnumerable <T> FindAll()
{
using (var db = SqlSugarHelper.GetInstance())
{
var list = db.Queryable <T>().ToList();
return(list);
}
}
/// <summary>
/// 查询所有数据(无分页,请慎用，自定义返回值)
/// </summary>
/// <param name="columns">自定义返回值表达式数</param>
/// <returns></returns>
public IEnumerable <TResult> FindAll <TResult>(Expression <Func <T, TResult> > columns)
{
using (var db = SqlSugarHelper.GetInstance())
{
var list = db.Queryable <T>().Select(columns).ToList();
return(list);
}
}
/// <summary>
/// 更新实体数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool Update(T entity)
{
using (var db = SqlSugarHelper.GetInstance())
{
//这种方式会以主键为条件
var i = db.Updateable(entity).ExecuteCommand();
return(i > 0);
}
}
/// <summary>
/// 写入实体数据
/// </summary>
/// <param name="entity">实体类</param>
/// <returns></returns>
public long Insert(T entity)
{
using (var db = SqlSugarHelper.GetInstance())
{
//返回插入数据的标识字段值
var i = db.Insertable(entity).ExecuteCommand();
return(i);
}
}
/// <summary>
/// 根据条件查询总数
/// </summary>
/// <param name="predicate">条件表达式树</param>
/// <returns></returns>
public int FindCount(Expression <Func <T, bool> > predicate)
{
using (var db = SqlSugarHelper.GetInstance())
{
var count = db.Queryable <T>();
if (predicate != null)
{
count = count.Where(predicate);
}
return(count.Count());
}
}
/// <summary>
/// 根据条件查询数据(自定义返回值)
/// </summary>
/// <param name="columns">自定义返回值表达式数</param>
/// <param name="predicate">条件表达式树</param>
/// <param name="orderBy">排序</param>
/// <returns>泛型实体集合</returns>
public IEnumerable <TResult> FindListByClause <TResult>(Expression <Func <T, TResult> > columns, Expression <Func <T, bool> > predicate, string orderBy)
{
using (var db = SqlSugarHelper.GetInstance())
{
var entities = db.Queryable <T>();
if (predicate != null)
{
entities = entities.Where(predicate);
}
if (!string.IsNullOrEmpty(orderBy))
{
entities = entities.OrderBy(orderBy);
}
return(entities.Select(columns).ToList());
}
}
/// <summary>
/// 根据条件分页及查询总数
/// </summary>
/// <param name="predicate">条件表达式树</param>
/// <param name="page">第几页</param>
/// <param name="row">每页显示多少条</param>
/// <param name="orderBy">排序规则</param>
/// <param name="totalNumber">总数（返回值）</param>
/// <returns></returns>
public IEnumerable <T> FindPageListByClause(Expression <Func <T, bool> > predicate, int page, int row, string orderBy, ref int totalNumber)
{
using (var db = SqlSugarHelper.GetInstance())
{
var list = db.Queryable <T>();
if (predicate != null)
{
list = list.Where(predicate);
}
if (!string.IsNullOrEmpty(orderBy))
{
list = list.OrderBy(orderBy);
}
return(list.ToPageList(page, row, ref totalNumber));
}
}
/// <summary>
/// 根据条件分页及查询总数
/// </summary>
/// <param name="columns">自定义返回值表达式数</param>
/// <param name="predicate">条件表达式树</param>
/// <param name="page">第几页</param>
/// <param name="row">每页显示多少条</param>
/// <param name="orderBy">排序规则</param>
/// <param name="totalNumber">总数（返回值）</param>
/// <returns></returns>
public DataTable FindPageListByClauseData <TResult>(Expression <Func <T, TResult> > columns, Expression <Func <T, bool> > predicate, int page, int row, string orderBy, ref int totalNumber)
{
using (var db = SqlSugarHelper.GetInstance())
{
var list = db.Queryable <T>();
if (predicate != null)
{
list = list.Where(predicate);
}
if (!string.IsNullOrEmpty(orderBy))
{
list = list.OrderBy(orderBy);
}
return(list.Select(columns).ToDataTablePage(page, row, ref totalNumber));
}
}
/// <summary>
/// 获取所有的表
/// </summary>
/// <returns></returns>
public List <CreateCodeTable> GetAllTable()
{
using (var db = SqlSugarHelper.GetInstance())
{
var sql = string.Empty;
if (db.CurrentConnectionConfig.DbType == SqlSugar.DbType.SqlServer)
{
sql = @"SELECT * FROM INFORMATION_SCHEMA.TABLES";
}
else
{
var schema = DBConfigHelper.GetConfig("Schema");
sql = $"SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='{schema}'";
}
var entities = db.SqlQueryable <CreateCodeTable>(sql).ToList();
return(entities);
}
}
/// <summary>
/// 根据条件分页及查询总数
/// </summary>
/// <param name="predicate">条件表达式树</param>
/// <param name="page">第几页</param>
/// <param name="row">每页显示多少条</param>
/// <param name="orderBy">排序规则</param>
/// <param name="totalNumber">总数（返回值）</param>
/// <returns></returns>
public List <sys_menulist> GetMenuPageList(Expression <Func <sys_menu, bool> > predicate, int page, int row, string orderBy, ref int totalNumber)
{
using (var db = SqlSugarHelper.GetInstance())
{
var list = db.Queryable <sys_menu, sys_menu>((m, m1) => new object[] { JoinType.Left, m.Menu_ParentID == m1.Menu_ID });
if (predicate != null)
{
list = list.Where(predicate);
}
if (!string.IsNullOrEmpty(orderBy))
{
list = list.OrderBy(orderBy);
}
return(list.Select((m, m1) => new sys_menulist {
Menu_Url = m.Menu_Url, Menu_Num = m.Menu_Num, Parent_Name = m1.Menu_Name, Menu_Name = m.Menu_Name, Menu_ParentID = m.Menu_ParentID, Menu_IsShow = m.Menu_IsShow, Menu_CreateTime = m.Menu_CreateTime, Menu_Icon = m.Menu_Icon, Menu_ID = m.Menu_ID
}).ToPageList(page, row, ref totalNumber));
}
}
/// <summary>
/// 根据表获取列
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public List <CreateCodeFeild> GetColByTable(string table)
{
using (var db = SqlSugarHelper.GetInstance())
{
var sql = string.Empty;
if (db.CurrentConnectionConfig.DbType == SqlSugar.DbType.SqlServer)
{
sql = $"select a.COLUMN_NAME colname,case when a.COLUMN_NAME=b.COLUMN_NAME then '主键' end iskey,a.DATA_TYPE type,a.IS_NULLABLE is_null from INFORMATION_SCHEMA.COLUMNS a left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE b on a.TABLE_NAME=b.TABLE_NAME where a.TABLE_NAME='{table}' ";
}
else
{
var schema = DBConfigHelper.GetConfig("Schema");
sql = $"select a.COLUMN_NAME colname,case when a.COLUMN_NAME=b.COLUMN_NAME then '主键' end iskey,a.DATA_TYPE type,a.IS_NULLABLE is_null from INFORMATION_SCHEMA.COLUMNS a left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE b on a.TABLE_NAME=b.TABLE_NAME where a.TABLE_SCHEMA='{schema}' and a.TABLE_NAME='{table}'";
}
var entities = db.SqlQueryable <CreateCodeFeild>(sql).ToList();
return(entities);
}
}
/// <summary>
/// 获取菜单和功能树
/// </summary>
/// <returns></returns>
public List <sys_menulistfunc> GetMenuAndFunctionTree(string RoleID = "")
{
//菜单功能查询
if (!RoleID.ToGuid().Equals(Guid.Empty))
{
//角色功能查询
using (var db = SqlSugarHelper.GetInstance())
{
return(db.Queryable <sys_menu, sys_rolemenufunction>((m, rmf) => new object[] { JoinType.Left, m.Menu_ID == rmf.RoleMenuFunction_MenuID }).OrderBy(p => p.Menu_Num, SqlSugar.OrderByType.Asc).Select <sys_menulistfunc>(@"Menu_Name+'('+Menu_Num+')' name,Menu_ID id,Menu_ParentID pId,Menu_Num num,Menu_Url ur,'false' checked,null tag,'true' chkDisabled,
case when Menu_ParentID is not null then 'false' else 'true' end openname").ToList());
}
}
using (var db = SqlSugarHelper.GetInstance())
{
return(db.Queryable <sys_menu>().OrderBy(p => p.Menu_Num, SqlSugar.OrderByType.Asc).Select <sys_menulistfunc>(@"Menu_Name+'('+Menu_Num+')' name,Menu_ID id,Menu_ParentID pId,Menu_Num num,Menu_Url ur,'false' checkeds,null tag,'true' chkDisabled,
case when Menu_ParentID is not null then 'false' else 'true' end openname").ToList());
}
}
/// <summary>
/// 获取数据库中的所有表和字段
/// </summary>
/// <returns></returns>
public List <CreateCodeTableField> GetDatabaseAllTable()
{
using (var db = SqlSugarHelper.GetInstance())
{
var sql = string.Empty;
if (db.CurrentConnectionConfig.DbType == SqlSugar.DbType.SqlServer)
{
sql = @"select TABLE_NAME+' [表]' name,TABLE_NAME id,null pId from INFORMATION_SCHEMA.TABLES
union all
select case when CHARACTER_MAXIMUM_LENGTH is null then COLUMN_NAME+' [字段类型:'+DATA_TYPE+']'
when CHARACTER_MAXIMUM_LENGTH is not null then COLUMN_NAME+' [字段类型:'+DATA_TYPE+'('+CONVERT(varchar(10),CHARACTER_MAXIMUM_LENGTH)+')]' end
name,TABLE_NAME+'$~'+COLUMN_NAME id,TABLE_NAME from INFORMATION_SCHEMA.COLUMNS";
}
else
{
var schema = DBConfigHelper.GetConfig("Schema");
sql = $"select Concat(TABLE_NAME,' [表]') name,TABLE_NAME id,null pId from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='{schema}' union all select case when CHARACTER_MAXIMUM_LENGTH is null then Concat(COLUMN_NAME,' [字段类型:',DATA_TYPE,']') when CHARACTER_MAXIMUM_LENGTH is not null then Concat(COLUMN_NAME,' [字段类型:',DATA_TYPE,'(',CHARACTER_MAXIMUM_LENGTH,')]') end name,Concat(TABLE_NAME, '$~', COLUMN_NAME) id,TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = '{schema}'";
}
var entities = db.SqlQueryable <CreateCodeTableField>(sql).ToList();
return(entities);
}
}