自己做项目的时候封装的SqlSugar操作类,备份以后用…
简介:
SqlSugar :是一款高性能(达到ADO.NET最高性能水平)
SqlSugar :是除EF外拉姆达解析最完善的ORM,多表 、UnionALL、 交叉子查询、真实的批量操作和分页
SqlSugar :用法简单,功能齐全
SqlSugar :对数据库结构没太多要求支持多主键、多自增列、支持异步
SqlSugar :支持分布式二级缓存(CRUD可以自动清除缓存)和支持读写分离功能大型项目必备
SqlSugar支持.Net 4.0+ 和 .Net Core
官网:http://www.codeisbug.com
**
源码分享个大家.Net Core和.Net 二个版本 **
提取码:最下面
#region << 版 本 注 释 >>
/*----------------------------------------------------------------
* 类 名 称 :OrderByClause
* 类 描 述 :
* 作 者 :xnlzg
* 创建时间 :2018/11/7 14:12:53
* 更新时间 :2018/11/7 14:12:53
* 说 明 :
* 版 本 号 :v1.0.0.0
*******************************************************************
* Copyright @ xnlzg 2018. All rights reserved.
*******************************************************************
//----------------------------------------------------------------*/
#endregion
namespace XN.Common
{
/// <summary>
/// 排序实体
/// </summary>
public class OrderByClause
{
/// <summary>
/// 排序字段
/// </summary>
public string Sort { get; set; }
/// <summary>
/// 排序类型
/// </summary>
public OrderSequence Order { get; set; }
}
}
#region << 版 本 注 释 >>
/*----------------------------------------------------------------
* 类 名 称 :OrderSequence
* 类 描 述 :
* 作 者 :xnlzg
* 创建时间 :2018/11/7 14:12:32
* 更新时间 :2018/11/7 14:12:32
* 说 明 :
* 版 本 号 :v1.0.0.0
*******************************************************************
* Copyright @ xnlzg 2018. All rights reserved5wbk.
*******************************************************************
//----------------------------------------------------------------*/
#endregion
namespace XN.Common
{
/// <summary>
/// 排序枚举
/// </summary>
public enum OrderSequence
{
/// <summary>
/// 正序
/// </summary>
Asc,
/// <summary>
/// 倒序
/// </summary>
Desc
}
}
#region << 版 本 注 释 >>
/*----------------------------------------------------------------
* 类 名 称 :QueryCondition
* 类 描 述 :
* 作 者 :xnlzg
* 创建时间 :2018/11/7 14:14:29
* 更新时间 :2018/11/7 14:14:29
* 说 明 :
* 版 本 号 :v1.0.0.0
*******************************************************************
* Copyright @ xnlzg 2018. All rights reserved.
*******************************************************************
//----------------------------------------------------------------*/
#endregion
namespace XN.Common
{
/// <summary>
/// 查询
/// </summary>
public class QueryCondition
{
/// <summary>
/// 字段名称
/// </summary>
public string Key { get; set; }
/// <summary>
/// 查询操作
/// </summary>
public QueryOperator Operator { get; set; }
/// <summary>
/// 值
/// </summary>
public object Value { get; set; }
}
}
#region << 版 本 注 释 >>
/*----------------------------------------------------------------
* 类 名 称 :QueryDescriptor
* 类 描 述 :
* 作 者 :xnlzg
* 创建时间 :2018/11/7 14:15:04
* 更新时间 :2018/11/7 14:15:04
* 说 明 :
* 版 本 号 :v1.0.0.0
*******************************************************************
* Copyright @ xnlzg 2018. All rights reserved.
*******************************************************************
//----------------------------------------------------------------*/
#endregion
using System.Collections.Generic;
namespace XN.Common
{
/// <summary>
/// 查询集合
/// </summary>
public class QueryDescriptor
{
/// <summary>
/// 行数
/// </summary>
public int PageSize { get; set; }
/// <summary>
/// 页码
/// </summary>
public int PageIndex { get; set; }
/// <summary>
/// 排序
/// </summary>
public List<OrderByClause> OrderBys { get; set; }
/// <summary>
/// 条件
/// </summary>
public List<QueryCondition> Conditions { get; set; }
}
}
#region << 版 本 注 释 >>
/*----------------------------------------------------------------
* 类 名 称 :QueryOperator
* 类 描 述 :
* 作 者 :xnlzg
* 创建时间 :2018/11/7 14:14:03
* 更新时间 :2018/11/7 14:14:03
* 说 明 :
* 版 本 号 :v1.0.0.0
*******************************************************************
* Copyright @ xnlzg 2018. All rights reserved.
*******************************************************************
//----------------------------------------------------------------*/
#endregion
namespace XN.Common
{
/// <summary>
/// 查询条件枚举
/// </summary>
public enum QueryOperator
{
/// <summary>
/// 相等
/// </summary>
Equal,
/// <summary>
/// 匹配
/// </summary>
Like,
/// <summary>
/// 大于
/// </summary>
GreaterThan,
/// <summary>
/// 大于或等于
/// </summary>
GreaterThanOrEqual,
/// <summary>
/// 小于
/// </summary>
LessThan,
/// <summary>
/// 小于或等于
/// </summary>
LessThanOrEqual,
/// <summary>
/// 等于集合
/// </summary>
In,
/// <summary>
/// 不等于集合
/// </summary>
NotIn,
/// <summary>
/// 左边匹配
/// </summary>
LikeLeft,
/// <summary>
/// 右边匹配
/// </summary>
LikeRight,
/// <summary>
/// 不相等
/// </summary>
NoEqual,
/// <summary>
/// 为空或空
/// </summary>
IsNullOrEmpty,
/// <summary>
/// 不为空
/// </summary>
IsNot,
/// <summary>
/// 不匹配
/// </summary>
NoLike,
/// <summary>
/// 时间段 值用 "|" 隔开
/// </summary>
DateRange
}
}
#region 开发单位:xnlzg 版权所有Copyright (C) 2011
/***-------------------------------------------------------------------------------------
命名空间:OPPO.DBUtility.Sugar
文 件 名:BaseDbContext
创建时间:2018/6/8 10:15:10
创 建 人:xnlzg
修改时间:
修 改 人:
说 明:
-------------------------------------------------------------------------------------***/
#endregion
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using SqlSugar;
namespace XN.Common
{
/// <summary>
/// 数据库操作类
/// </summary>
public class BaseDbContext
{
/// <summary>
/// 注意当前方法的类不能是静态的 public static class这么写是错误的
/// </summary>
public static SqlSugarClient Db
{
get
{
var connMain = ConfigurationManager.ConnectionStrings["ConnMain"];
var connFrom = ConfigurationManager.ConnectionStrings["ConnFrom"];
return InitDataBase(connFrom == null
? new List<string> { connMain.ToString() }
: new List<string> { connMain.ToString(), connFrom.ToString() });
}
}
/// <summary>
/// 获得SqlSugarClient
/// </summary>
/// <param name="serverIp">服务器IP或文件路径</param>
/// <param name="user">用户名</param>
/// <param name="pass">密码</param>
/// <param name="dataBase">数据库</param>
public static SqlSugarClient GetIntance(string serverIp, string user, string pass, string dataBase)
{
var listConn = new List<string>();
switch ((DbType)ConfigurationManager.ConnectionStrings["DbType"].ObjToInt())
{
case DbType.SqlServer:
listConn.Add($"server={serverIp};user id={user};password={pass};persistsecurityinfo=True;database={dataBase}");
break;
case DbType.MySql:
listConn.Add($"Server={serverIp};Database={dataBase};Uid={user};Pwd={pass};");
break;
case DbType.Oracle:
listConn.Add($"Server=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={serverIp})(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={dataBase})));User Id={user};Password={pass};Persist Security Info=True;Enlist=true;Max Pool Size=300;Min Pool Size=0;Connection Lifetime=300");
break;
case DbType.PostgreSQL:
listConn.Add($"PORT=5432;DATABASE={dataBase};HOST={serverIp};PASSWORD={pass};USER ID={user}");
break;
case DbType.Sqlite:
listConn.Add($"Data Source={serverIp};Version=3;Password={pass};");
break;
}
return InitDataBase(listConn);
}
/// <summary>
/// 初始化数据库连接
/// </summary>
/// <param name="listConn">连接字符串</param>
private static SqlSugarClient InitDataBase(List<string> listConn)
{
var connStr = "";//主库
var slaveConnectionConfigs = new List<SlaveConnectionConfig>();//从库集合
for (var i = 0; i < listConn.Count; i++)
{
if (i == 0)
{
connStr = listConn[i];//主数据库连接
}
else
{
slaveConnectionConfigs.Add(new SlaveConnectionConfig()
{
HitRate = i * 2,
ConnectionString = listConn[i]
});
}
}
//如果配置了 SlaveConnectionConfigs那就是主从模式,所有的写入删除更新都走主库,查询走从库,
//事务内都走主库,HitRate表示权重 值越大执行的次数越高,如果想停掉哪个连接可以把HitRate设为0
var db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = connStr,
DbType = (DbType)ConfigurationManager.ConnectionStrings["DbType"].ObjToInt(),
IsAutoCloseConnection = true,
SlaveConnectionConfigs = slaveConnectionConfigs,
IsShardSameThread = true
});
db.Ado.CommandTimeOut = 30000;//设置超时时间
db.Aop.OnLogExecuted = (sql, pars) => //SQL执行完事件
{
//LogHelper.WriteLog($"执行时间:{db.Ado.SqlExecutionTime.TotalMilliseconds}毫秒 \r\nSQL如下:{sql} \r\n参数:{GetParams(pars)} ", "SQL执行");
};
db.Aop.OnLogExecuting = (sql, pars) => //SQL执行前事件
{
if (db.TempItems == null) db.TempItems = new Dictionary<string, object>();
};
db.Aop.OnError = (exp) =>//执行SQL 错误事件
{
//LogHelper.WriteLog($"SQL错误:{exp.Message}\r\nSQL如下:{exp.Sql}", "SQL执行");
throw new Exception(exp.Message);
};
db.Aop.OnExecutingChangeSql = (sql, pars) => //SQL执行前 可以修改SQL
{
return new KeyValuePair<string, SugarParameter[]>(sql, pars);
};
db.Aop.OnDiffLogEvent = (it) => //可以方便拿到 数据库操作前和操作后的数据变化。
{
//var editBeforeData = it.BeforeData;
//var editAfterData = it.AfterData;
//var sql = it.Sql;
//var parameter = it.Parameters;
//var data = it.BusinessData;
//var time = it.Time;
//var diffType = it.DiffType;//枚举值 insert 、update 和 delete 用来作业务区分
//你可以在这里面写日志方法
};
return db;
}
/// <summary>
/// 获取参数信息
/// </summary>
/// <param name="pars"></param>
/// <returns></returns>
private static string GetParams(SugarParameter[] pars)
{
return pars.Aggregate("", (current, p) => current + $"{p.ParameterName}:{p.Value}, ");
}
}
}
#region << 版 本 注 释 >>
/*----------------------------------------------------------------
* 类 名 称 :ISqlRepository
* 类 描 述 :
* 作 者 :xnlzg
* 创建时间 :2018/7/23 15:56:57
* 更新时间 :2018/7/23 15:56:57
* 说 明 :
* 版 本 号 :v1.0.0.0
*******************************************************************
* Copyright @ xnlzg 2018. All rights reserved.
*******************************************************************
//----------------------------------------------------------------*/
#endregion
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq.Expressions;
using SqlSugar;
using XN.Common;
namespace OPPO.DBUtility
{
/// <summary>
/// SqlSugar操作接口类
/// </summary>
public interface ISugerHandler : IDisposable
{
#region 事务
/// <summary>
/// 事务操作
/// 注意:代码段里面如果调用本身类其它方法或其它类方法必须带着var db = SugerHandler.Instance()这个db走,不带着走事务回滚会不成功
/// </summary>
/// <param name="serviceAction">代码段</param>
/// <param name="level">事务级别</param>
void InvokeTransactionScope(Action serviceAction, IsolationLevel level = IsolationLevel.ReadCommitted);
#endregion
#region 数据库管理
/// <summary>
/// 添加列
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="column">列信息</param>
/// <returns></returns>
bool AddColumn(string tableName, DbColumnInfo column);
/// <summary>
/// 添加主键
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="columnName">列名</param>
/// <returns></returns>
bool AddPrimaryKey(string tableName, string columnName);
/// <summary>
/// 备份数据库
/// </summary>
/// <param name="databaseName">数据库名</param>
/// <param name="fullFileName">文件名</param>
/// <returns></returns>
bool BackupDataBase(string databaseName, string fullFileName);
/// <summary>
/// 备份表
/// </summary>
/// <param name="oldTableName">旧表名</param>
/// <param name="newTableName">行表名</param>
/// <param name="maxBackupDataRows">行数</param>
/// <returns></returns>
bool BackupTable(string oldTableName, string newTableName, int maxBackupDataRows = int.MaxValue);
/// <summary>
/// 创建表
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="columns">列集合</param>
/// <param name="isCreatePrimaryKey">是否创建主键</param>
/// <returns></returns>
bool CreateTable(string tableName, List<DbColumnInfo> columns, bool isCreatePrimaryKey = true);
/// <summary>
/// 删除列
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="columnName">列名</param>
/// <returns></returns>
bool DropColumn(string tableName, string columnName);
/// <summary>
/// 删除约束
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="constraintName">约束名</param>
/// <returns></returns>
bool DropConstraint(string tableName, string constraintName);
/// <summary>
/// 删除表
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
bool DropTable(string tableName);
/// <summary>
/// 获取列信息
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="isCache">是否缓存</param>
/// <returns></returns>
List<DbColumnInfo> GetColumnInfosByTableName(string tableName, bool isCache = true);
/// <summary>
/// 获取自增列
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
List<string> GetIsIdentities(string tableName);
/// <summary>
/// 获取主键
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
List<string> GetPrimaries(string tableName);
/// <summary>
/// 获取表集合
/// </summary>
/// <param name="isCache">是否缓存</param>
/// <returns></returns>
List<DbTableInfo> GetTableInfoList(bool isCache = true);
/// <summary>
/// 获取视图集合
/// </summary>
/// <param name="isCache">是否缓存</param>
/// <returns></returns>
List<DbTableInfo> GetViewInfoList(bool isCache = true);
/// <summary>
/// 检测列是否存在
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="column">列名</param>
/// <returns></returns>
bool IsAnyColumn(string tableName, string column);
/// <summary>
/// 检测约束
/// </summary>
/// <param name="constraintName">约束名称</param>
/// <returns></returns>
bool IsAnyConstraint(string constraintName);
/// <summary>
/// 检测是否有任何系统表权限
/// </summary>
/// <returns></returns>
bool IsAnySystemTablePermissions();
/// <summary>
/// 检测表是否存在
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="isCache">是否缓存</param>
/// <returns></returns>
bool IsAnyTable(string tableName, bool isCache = true);
/// <summary>
/// 检测列是否自增列
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="column">列名</param>
/// <returns></returns>
bool IsIdentity(string tableName, string column);
/// <summary>
/// 检测列是否主键
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="column">列名</param>
/// <returns></returns>
bool IsPrimaryKey(string tableName, string column);
/// <summary>
/// 重置列名
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="oldColumnName">旧列名</param>
/// <param name="newColumnName">新列名</param>
/// <returns></returns>
bool RenameColumn(string tableName, string oldColumnName, string newColumnName);
/// <summary>
/// 重置表数据
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
bool TruncateTable(string tableName);
/// <summary>
/// 修改列信息
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="column">列信息</param>
/// <returns></returns>
bool UpdateColumn(string tableName, DbColumnInfo column);
/// <summary>
/// 获取数据库时间
/// </summary>
/// <returns>返回值</returns>
DateTime GetDataBaseTime();
#endregion
#region 新增
/// <summary>
/// 新增
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 </param>
/// <returns>操作影响的行数</returns>
int Add<T>(T entity) where T : class, new();
/// <summary>
/// 新增
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entitys">泛型集合</param>
/// <returns>操作影响的行数</returns>
int Add<T>(List<T> entitys) where T : class, new();
/// <summary>
/// 新增
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="keyValues">字典集合(Key:列名 Value:值)</param>
/// <returns>操作影响的行数</returns>
int Add<T>(Dictionary<string, object> keyValues) where T : class, new();
/// <summary>
/// 新增
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 </param>
/// <returns>返回实体</returns>
T AddReturnEntity<T>(T entity) where T : class, new();
/// <summary>
/// 新增
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 </param>
/// <returns>返回自增列</returns>
int AddReturnIdentity<T>(T entity) where T : class, new();
/// <summary>
/// 新增
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 </param>
/// <returns>返回bool</returns>
bool AddReturnBool<T>(T entity) where T : class, new();
/// <summary>
/// 新增
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entitys">泛型集合</param>
/// <returns>返回bool</returns>
bool AddReturnBool<T>(List<T> entitys) where T : class, new();
#endregion
#region 修改
/// <summary>
/// 修改(主键是更新条件)
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象(必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]),如果是联合主键,请使用Where条件 </param>
/// <param name="lstIgnoreColumns">不更新的列</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
int Update<T>(T entity, List<string> lstIgnoreColumns = null, bool isLock = true) where T : class, new();
/// <summary>
/// 修改(主键是更新条件)
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entitys"> 实体对象集合(必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]),如果是联合主键,请使用Where条件 </param>
/// <param name="lstIgnoreColumns">不更新的列</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
int Update<T>(List<T> entitys, List<string> lstIgnoreColumns = null, bool isLock = true) where T : class, new();
/// <summary>
/// 修改
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 </param>
/// <param name="where"> 条件 </param>
/// <param name="lstIgnoreColumns">不更新的列</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
int Update<T>(T entity, Expression<Func<T, bool>> where , List<string> lstIgnoreColumns = null,
bool isLock = true) where T : class, new();
/// <summary>
/// 修改
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="update"> 实体对象 </param>
/// <param name="where"> 条件 </param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
int Update<T>(Expression<Func<T, T>> update, Expression<Func<T, bool>> where = null, bool isLock = true) where T : class, new();
/// <summary>
/// 修改
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="keyValues">字典集合(Key:列名 Value:值)</param>
/// <param name="where"> 条件 </param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
int Update<T>(Dictionary<string, object> keyValues, Expression<Func<T, bool>> where = null, bool isLock = true)
where T : class, new();
/// <summary>
/// 批量修改需要更新的列
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entitys"> 实体对象(必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]),如果是联合主键,请使用Where条件 </param>
/// <param name="updateColumns">更新指定列</param>
/// <param name="wherecolumns">条件(为空则以主键更新,反之需要把wherecolumns中的列加到UpdateColumns中)</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
int UpdateColumns<T>(List<T> entitys, Expression<Func<T, object>> updateColumns,
Expression<Func<T, object>> wherecolumns = null, bool isLock = true) where T : class, new();
/// <summary>
/// 修改 通过RowVer及主键Code 更新
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 </param>
/// <param name="lstIgnoreColumns">不更新的列</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
int UpdateRowVer<T>(T entity, List<string> lstIgnoreColumns = null, bool isLock = true)where T : class, new();
/// <summary>
/// 修改 通过RowVer及主键Code 更新
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="update"> 实体对象 </param>
/// <param name="where"> 更新条件 </param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
int UpdateRowVer<T>(Expression<Func<T, T>> update, Dictionary<string, object> where, bool isLock = true) where T : class, new();
#endregion
#region 删除
/// <summary>
/// 删除 通过主键数据
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="primaryKeyValues">主键值</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
int DeleteByPrimary<T>(List<object> primaryKeyValues, bool isLock = true) where T : class, new();
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象(必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]),如果是联合主键,请使用Where条件 </param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
int Delete<T>(T entity, bool isLock = true) where T : class, new();
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 (必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]),如果是联合主键,请使用Where条件</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
int Delete<T>(List<T> entity, bool isLock = true) where T : class, new();
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="where"> 条件 </param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
int Delete<T>(Expression<Func<T, bool>> where, bool isLock = true) where T : class, new();
/// <summary>
/// 通过多值(主键)删除数据集
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="inValues">数据集合</param>
/// <returns></returns>
int DeleteIn<T>(List<dynamic> inValues) where T : class, new();
#endregion
#region 查询
#region 数据源
/ <summary>
/ 查询数据源
/ </summary>
/ <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/ <returns>数据源</returns>
//ISugarQueryable<T> Queryable<T>() where T : class, new();
#endregion
#region 多表查询
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (t1,t2) => new object[] {JoinType.Left,t1.UserNo==t2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (t1, t2) => new { Id =t1.UserNo, Id1 = t2.UserNo}</param>
/// <param name="whereLambda">查询表达式 (t1, t2) =>t1.UserNo == "")</param>
/// <returns></returns>
List<TResult> QueryMuch<T, T2, TResult>(
Expression<Func<T, T2, object[]>> joinExpression,
Expression<Func<T, T2, TResult>> selectExpression,
Expression<Func<T, T2, bool>> whereLambda = null) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式</param>
/// <param name="selectExpression">返回表达式</param>
/// <param name="whereLambda">查询表达式</param>
/// <returns></returns>
List<TResult> QueryMuch<T, T2, T3, TResult>(
Expression<Func<T, T2, T3, object[]>> joinExpression,
Expression<Func<T, T2, T3, TResult>> selectExpression,
Expression<Func<T, T2, T3, bool>> whereLambda = null) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式</param>
/// <param name="selectExpression">返回表达式</param>
/// <param name="whereLambda">查询表达式</param>
/// <returns></returns>
List<TResult> QueryMuch<T, T2, T3, T4, TResult>(
Expression<Func<T, T2, T3, T4, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, TResult>> selectExpression,
Expression<Func<T, T2, T3, T4, bool>> whereLambda = null) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式</param>
/// <param name="selectExpression">返回表达式</param>
/// <param name="whereLambda">查询表达式</param>
/// <returns></returns>
List<TResult> QueryMuch<T, T2, T3, T4, T5, TResult>(
Expression<Func<T, T2, T3, T4, T5, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, TResult>> selectExpression,
Expression<Func<T, T2, T3, T4, T5, bool>> whereLambda = null) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="whereLambda">查询表达式 (w1, w2) =>w1.UserNo == "")</param>
/// <returns>值</returns>
List<TResult> QueryMuch<T, T2, T3, T4, T5, T6, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, TResult>> selectExpression,
Expression<Func<T, T2, T3, T4, T5, T6, bool>> whereLambda = null) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="T7">实体7</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="whereLambda">查询表达式 (w1, w2) =>w1.UserNo == "")</param>
/// <returns>值</returns>
List<TResult> QueryMuch<T, T2, T3, T4, T5, T6, T7, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, T7, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, TResult>> selectExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, bool>> whereLambda = null) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="T7">实体7</typeparam>
/// <typeparam name="T8">实体8</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="whereLambda">查询表达式 (w1, w2) =>w1.UserNo == "")</param>
/// <returns>值</returns>
List<TResult> QueryMuch<T, T2, T3, T4, T5, T6, T7, T8, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, TResult>> selectExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, bool>> whereLambda = null) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="T7">实体7</typeparam>
/// <typeparam name="T8">实体8</typeparam>
/// <typeparam name="T9">实体9</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="whereLambda">查询表达式 (w1, w2) =>w1.UserNo == "")</param>
/// <returns>值</returns>
List<TResult> QueryMuch<T, T2, T3, T4, T5, T6, T7, T8, T9, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, T9, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, T9, TResult>> selectExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, T9, bool>> whereLambda = null) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (t1,t2) => new object[] {JoinType.Left,t1.UserNo==t2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (t1, t2) => new { Id =t1.UserNo, Id1 = t2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
List<TResult> QueryMuchDescriptor<T, T2, TResult>(
Expression<Func<T, T2, object[]>> joinExpression,
Expression<Func<T, T2, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
List<TResult> QueryMuchDescriptor<T, T2, T3, TResult>(
Expression<Func<T, T2, T3, object[]>> joinExpression,
Expression<Func<T, T2, T3, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
List<TResult> QueryMuchDescriptor<T, T2, T3, T4, TResult>(
Expression<Func<T, T2, T3, T4, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
List<TResult> QueryMuchDescriptor<T, T2, T3, T4, T5, TResult>(
Expression<Func<T, T2, T3, T4, T5, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
List<TResult> QueryMuchDescriptor<T, T2, T3, T4, T5, T6, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="T7">实体7</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
List<TResult> QueryMuchDescriptor<T, T2, T3, T4, T5, T6, T7, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, T7, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="T7">实体7</typeparam>
/// <typeparam name="T8">实体8</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
List<TResult> QueryMuchDescriptor<T, T2, T3, T4, T5, T6, T7, T8, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new();
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="T7">实体7</typeparam>
/// <typeparam name="T8">实体8</typeparam>
/// <typeparam name="T9">实体9</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
List<TResult> QueryMuchDescriptor<T, T2, T3, T4, T5, T6, T7, T8, T9, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, T9, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, T9, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new();
#endregion
#region 查询
/// <summary>
/// 查询-返回自定义数据
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <typeparam name="TResult">返回值类型</typeparam>
/// <param name="expression">返回值表达式</param>
/// <param name="whereLambda">查询表达式</param>
/// <returns>值</returns>
TResult QuerySelect<T, TResult>(Expression<Func<T, TResult>> expression,Expression<Func<T, bool>> whereLambda = null) where T : class, new();
/// <summary>
/// 查询-返回自定义数据
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <typeparam name="TResult">返回值类型</typeparam>
/// <param name="expression">返回值表达式</param>
/// <param name="whereLambda">查询表达式</param>
/// <returns>值</returns>
List<TResult> QuerySelectList<T, TResult>(Expression<Func<T, TResult>> expression,Expression<Func<T, bool>> whereLambda = null) where T : class, new();
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="whereLambda">查询表达式</param>
/// <returns></returns>
T Query<T>(Expression<Func<T, bool>> whereLambda = null) where T : class, new();
/// <summary>
/// 查询集合
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="whereLambda">过滤条件</param>
/// <returns>实体</returns>
List<T> QueryWhereList<T>(Expression<Func<T, bool>> whereLambda = null) where T : class, new();
/// <summary>
/// 查询集合
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="query">过滤条件</param>
/// <returns>实体</returns>
List<T> QueryList<T>(QueryDescriptor query = null) where T : class, new();
/// <summary>
/// 查询集合
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="sql">sql</param>
/// <returns>实体</returns>
List<T> QuerySqlList<T>(string sql) where T : class, new();
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="query">过滤条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>实体</returns>
List<T> QueryPageList<T>(QueryDescriptor query, out int totalCount) where T : class, new();
/// <summary>
/// 通过多值查询数据集
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="inFieldName">字段名</param>
/// <param name="inValues">数据集合</param>
/// <returns></returns>
List<T> In<T>(string inFieldName, List<dynamic> inValues) where T : class, new();
/// <summary>
/// 通过多值(主键)查询数据集
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="values">主键数据集合</param>
/// <returns></returns>
List<T> In<T>(List<dynamic> values) where T : class, new();
/// <summary>
/// 查询集合
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="whereLambda">查询表达式</param>
/// <returns>实体</returns>
DataTable QueryDataTable<T>(Expression<Func<T, bool>> whereLambda = null) where T : class, new();
/// <summary>
/// 查询集合
/// </summary>
/// <param name="sql">sql</param>
/// <returns>实体</returns>
DataTable QueryDataTable(string sql);
/// <summary>
/// 查询单个值
/// </summary>
/// <param name="sql">sql</param>
/// <returns>单个值</returns>
object QuerySqlScalar(string sql);
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="query">过滤条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>DataTable</returns>
DataTable QueryDataTablePageList<T>(QueryDescriptor query, out int totalCount) where T : class, new();
#endregion
#region Mapper
/// <summary>
/// Mapper查询
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="mapperAction">操作(it, cache) =>
/// {
/// var all = cache.GetListByPrimaryKeys(vmodel => vmodel.Id); //一次性查询出所要的外键引用数据
/// it.School = all.FirstOrDefault(i => i.Id == it.Id); //一对一
/// it.Schools = all.Where(i => i.Id == it.Id).ToList(); //一对多
/// /*用C#处理你想要的结果*/
/// it.Name = it.Name == null ? "null" : it.Name;
/// }
/// </param>
/// <param name="query">过滤条件</param>
/// <returns></returns>
List<T> QueryMapper<T>(Action<T> mapperAction, QueryDescriptor query = null) where T : class, new();
/// <summary>
/// Mapper查询
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="mapperAction">操作(it, cache) =>
/// {
/// var all = cache.GetListByPrimaryKeys(vmodel => vmodel.Id); //一次性查询出所要的外键引用数据
/// it.School = all.FirstOrDefault(i => i.Id == it.Id); //一对一
/// it.Schools = all.Where(i => i.Id == it.Id).ToList(); //一对多
/// /*用C#处理你想要的结果*/
/// it.Name = it.Name == null ? "null" : it.Name;
/// }
/// </param>
/// <param name="whereLambda">过滤条件</param>
/// <returns></returns>
List<T> QueryMapper<T>(Action<T> mapperAction, Expression<Func<T, bool>> whereLambda = null) where T : class, new();
#endregion
#region 存储过程
/// <summary>
/// 查询存储过程
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="parameters">参数</param>
/// <returns>DataSet</returns>
DataSet QueryProcedureDataSet(string procedureName, List<SqlParameter> parameters);
/// <summary>
/// 查询存储过程
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="parameters">参数</param>
/// <returns>DataTable</returns>
DataTable QueryProcedure(string procedureName, List<SqlParameter> parameters);
/// <summary>
/// 查询存储过程
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="parameters">参数</param>
/// <returns>单个值</returns>
object QueryProcedureScalar(string procedureName, List<SqlParameter> parameters);
#endregion
#region 分组
/// <summary>
/// 分组
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="groupByLambda">分组表达式</param>
/// <param name="whereLambda">查询表达式</param>
/// <returns>值</returns>
List<T> GroupBy<T>(Expression<Func<T, object>> groupByLambda,Expression<Func<T, bool>> whereLambda = null) where T : class, new();
/// <summary>
/// 分组-返回自定义数据
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <typeparam name="TResult">返回值类型</typeparam>
/// <param name="expression">返回值表达式</param>
/// <param name="groupByLambda">分组表达式</param>
/// <param name="whereLambda">查询表达式</param>
/// <returns>值</returns>
List<TResult> GroupBy<T, TResult>(Expression<Func<T, TResult>> expression,
Expression<Func<T, object>> groupByLambda, Expression<Func<T, bool>> whereLambda = null)
where T : class, new();
#endregion
#region Json
/// <summary>
/// 查询集合
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="whereLambda">查询表达式</param>
/// <returns>Json</returns>
string QueryJson<T>(Expression<Func<T, bool>> whereLambda = null) where T : class, new();
#endregion
#region 其它
/// <summary>
/// 查询前多少条数据
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/// <param name="whereLambda">查询表达式</param>
/// <param name="num">数量</param>
/// <returns></returns>
List<T> Take<T>(Expression<Func<T, bool>> whereLambda, int num) where T : class, new();
/// <summary>
/// 查询单条数据
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/// <param name="whereLambda">查询表达式</param>
/// <returns></returns>
T First<T>(Expression<Func<T, bool>> whereLambda) where T : class, new();
/// <summary>
/// 是否存在
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/// <param name="whereLambda">查询表达式</param>
/// <returns></returns>
bool IsExist<T>(Expression<Func<T, bool>> whereLambda) where T : class, new();
/// <summary>
/// 合计
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/// <param name="field">字段</param>
/// <returns></returns>
int Sum<T>(string field) where T : class, new();
/// <summary>
/// 最大值
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/// <typeparam name="TResult">返回类型</typeparam>
/// <param name="field">字段</param>
/// <returns></returns>
TResult Max<T, TResult>(string field) where T : class, new();
/// <summary>
/// 最小值
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/// <typeparam name="TResult">返回类型</typeparam>
/// <param name="field">字段</param>
/// <returns></returns>
TResult Min<T, TResult>(string field) where T : class, new();
/// <summary>
/// 平均值
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/// <param name="field">字段</param>
/// <returns></returns>
int Avg<T>(string field) where T : class, new();
/// <summary>
/// 生成流水号
/// </summary>
/// <param name="key">列名</param>
/// <param name="prefix">前缀</param>
/// <param name="fixedLength">流水号长度</param>
/// <param name="dateFomart">日期格式(yyyyMMdd) 为空前缀后不加日期,反之加</param>
/// <returns></returns>
string CustomNumber<T>(string key, string prefix = "", int fixedLength = 4, string dateFomart = "")where T : class, new();
/// <summary>
/// 生成流水号
/// </summary>
/// <param name="key">列名</param>
/// <param name="num">数量</param>
/// <param name="prefix">前缀</param>
/// <param name="fixedLength">流水号长度</param>
/// <param name="dateFomart">日期格式(yyyyMMdd) 为空前缀后不加日期,反之加</param>
/// <returns></returns>
List<string> CustomNumber<T>(string key,int num, string prefix = "", int fixedLength = 4, string dateFomart = "") where T : class, new();
#endregion
#endregion
}
}
#region << 版 本 注 释 >>
/*----------------------------------------------------------------
* 类 名 称 :SugerHandler
* 类 描 述 :
* 作 者 :xnlzg
* 创建时间 :2018/7/23 15:52:47
* 更新时间 :2018/7/23 15:52:47
* 说 明 :
using ( var db = SugarHandler.Instance())
{
db.BeginTran();
var userInfo = db.Query<SysUsers>(m => m.UserName == userLogin.UserName);
db.CommitTran();
}
* 版 本 号 :v1.0.0.0
*******************************************************************
* Copyright @ xnlzg 2018. All rights reserved.
*******************************************************************
//----------------------------------------------------------------*/
#endregion
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq.Expressions;
using OPPO.DBUtility;
using SqlSugar;
namespace XN.Common
{
/// <summary>
/// SqlSugar调用类
/// </summary>
public class SugerHandler
{
#region 初始化
/// <summary>
/// 初始化
/// </summary>
/// <returns>值</returns>
public static ISugerHandler Instance()
{
return new SugerRepository();
}
/// <summary>
/// 初始化
/// </summary>
/// <param name="serverIp">服务器IP</param>
/// <param name="user">用户名</param>
/// <param name="pass">密码</param>
/// <param name="dataBase">数据库</param>
/// <returns>值</returns>
public static ISugerHandler Instance(string serverIp, string user, string pass, string dataBase)
{
var suger = new SugerRepository();
suger.Instance(serverIp, user, pass, dataBase);
return suger;
}
#endregion
}
/// <summary>
/// SqlSugar操作类型
/// </summary>
public class SugerRepository : ISugerHandler
{
/// <summary>
/// 数据库连接对象
/// </summary>
public SqlSugarClient DbContext { get; set; } = BaseDbContext.Db;
/// <summary>
/// 初始化
/// </summary>
/// <param name="serverIp">服务器IP</param>
/// <param name="user">用户名</param>
/// <param name="pass">密码</param>
/// <param name="dataBase">数据库</param>
/// <returns>值</returns>
public void Instance(string serverIp, string user, string pass, string dataBase)
{
DbContext = BaseDbContext.GetIntance(serverIp, user, pass, dataBase);
}
#region 事务
/// <summary>
/// 事务操作
/// 注意:代码段里面如果调用本身类其它方法或其它类方法必须带着var db = SugerHandler.Instance()这个db走,不带着走事务回滚会不成功
/// </summary>
/// <param name="serviceAction">代码段</param>
/// <param name="level">事务级别</param>
public void InvokeTransactionScope(Action serviceAction, IsolationLevel level = IsolationLevel.ReadCommitted)
{
try
{
DbContext.Ado.BeginTran(level);
serviceAction();
DbContext.Ado.CommitTran();
}
catch (Exception ex)
{
DbContext.Ado.RollbackTran();
throw new Exception(ex.Message);
}
finally
{
Dispose();
}
}
#endregion
#region 数据库管理
/// <summary>
/// 添加列
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="column">列信息</param>
/// <returns>值</returns>
public bool AddColumn(string tableName, DbColumnInfo column)
{
return DbContext.DbMaintenance.AddColumn(tableName, column);
}
/// <summary>
/// 添加主键
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="columnName">列名</param>
/// <returns>值</returns>
public bool AddPrimaryKey(string tableName, string columnName)
{
return DbContext.DbMaintenance.AddPrimaryKey(tableName, columnName);
}
/// <summary>
/// 备份数据库
/// </summary>
/// <param name="databaseName">数据库名</param>
/// <param name="fullFileName">文件名</param>
/// <returns>值</returns>
public bool BackupDataBase(string databaseName, string fullFileName)
{
return DbContext.DbMaintenance.BackupDataBase(databaseName, fullFileName);
}
/// <summary>
/// 备份表
/// </summary>
/// <param name="oldTableName">旧表名</param>
/// <param name="newTableName">行表名</param>
/// <param name="maxBackupDataRows">行数</param>
/// <returns>值</returns>
public bool BackupTable(string oldTableName, string newTableName, int maxBackupDataRows = int.MaxValue)
{
return DbContext.DbMaintenance.BackupTable(oldTableName, newTableName, maxBackupDataRows);
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="columns">列集合</param>
/// <param name="isCreatePrimaryKey">是否创建主键</param>
/// <returns>值</returns>
public bool CreateTable(string tableName, List<DbColumnInfo> columns, bool isCreatePrimaryKey = true)
{
return DbContext.DbMaintenance.CreateTable(tableName, columns, isCreatePrimaryKey);
}
/// <summary>
/// 删除列
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="columnName">列名</param>
/// <returns>值</returns>
public bool DropColumn(string tableName, string columnName)
{
return DbContext.DbMaintenance.DropColumn(tableName, columnName);
}
/// <summary>
/// 删除约束
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="constraintName">约束名</param>
/// <returns>值</returns>
public bool DropConstraint(string tableName, string constraintName)
{
return DbContext.DbMaintenance.DropConstraint(tableName, constraintName);
}
/// <summary>
/// 删除表
/// </summary>
/// <param name="tableName"></param>
/// <returns>值</returns>
public bool DropTable(string tableName)
{
return DbContext.DbMaintenance.DropTable(tableName);
}
/// <summary>
/// 获取列信息
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="isCache">是否缓存</param>
/// <returns>值</returns>
public List<DbColumnInfo> GetColumnInfosByTableName(string tableName, bool isCache = true)
{
return DbContext.DbMaintenance.GetColumnInfosByTableName(tableName, isCache);
}
/// <summary>
/// 获取自增列
/// </summary>
/// <param name="tableName">表名</param>
/// <returns>值</returns>
public List<string> GetIsIdentities(string tableName)
{
return DbContext.DbMaintenance.GetIsIdentities(tableName);
}
/// <summary>
/// 获取主键
/// </summary>
/// <param name="tableName">表名</param>
/// <returns>值</returns>
public List<string> GetPrimaries(string tableName)
{
return DbContext.DbMaintenance.GetPrimaries(tableName);
}
/// <summary>
/// 获取表集合
/// </summary>
/// <param name="isCache">是否缓存</param>
/// <returns>值</returns>
public List<DbTableInfo> GetTableInfoList(bool isCache = true)
{
return DbContext.DbMaintenance.GetTableInfoList(isCache);
}
/// <summary>
/// 获取视图集合
/// </summary>
/// <param name="isCache">是否缓存</param>
/// <returns>值</returns>
public List<DbTableInfo> GetViewInfoList(bool isCache = true)
{
return DbContext.DbMaintenance.GetViewInfoList(isCache);
}
/// <summary>
/// 检测列是否存在
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="column">列名</param>
/// <returns>值</returns>
public bool IsAnyColumn(string tableName, string column)
{
return DbContext.DbMaintenance.IsAnyColumn(tableName, column);
}
/// <summary>
/// 检测约束
/// </summary>
/// <param name="constraintName">约束名称</param>
/// <returns>值</returns>
public bool IsAnyConstraint(string constraintName)
{
return DbContext.DbMaintenance.IsAnyConstraint(constraintName);
}
/// <summary>
/// 检测是否有任何系统表权限
/// </summary>
/// <returns>值</returns>
public bool IsAnySystemTablePermissions()
{
return DbContext.DbMaintenance.IsAnySystemTablePermissions();
}
/// <summary>
/// 检测表是否存在
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="isCache">是否缓存</param>
/// <returns>值</returns>
public bool IsAnyTable(string tableName, bool isCache = true)
{
return DbContext.DbMaintenance.IsAnyTable(tableName, isCache);
}
/// <summary>
/// 检测列是否自增列
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="column">列名</param>
/// <returns>值</returns>
public bool IsIdentity(string tableName, string column)
{
return DbContext.DbMaintenance.IsIdentity(tableName, column);
}
/// <summary>
/// 检测列是否主键
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="column">列名</param>
/// <returns>值</returns>
public bool IsPrimaryKey(string tableName, string column)
{
return DbContext.DbMaintenance.IsPrimaryKey(tableName, column);
}
/// <summary>
/// 重置列名
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="oldColumnName">旧列名</param>
/// <param name="newColumnName">新列名</param>
/// <returns>值</returns>
public bool RenameColumn(string tableName, string oldColumnName, string newColumnName)
{
return DbContext.DbMaintenance.RenameColumn(tableName, oldColumnName, newColumnName);
}
/// <summary>
/// 重置表数据
/// </summary>
/// <param name="tableName">表名</param>
/// <returns>值</returns>
public bool TruncateTable(string tableName)
{
return DbContext.DbMaintenance.TruncateTable(tableName);
}
/// <summary>
/// 修改列信息
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="column">列信息</param>
/// <returns>值</returns>
public bool UpdateColumn(string tableName, DbColumnInfo column)
{
return DbContext.DbMaintenance.UpdateColumn(tableName, column);
}
/// <summary>
/// 获取数据库时间
/// </summary>
/// <returns>返回值</returns>
public DateTime GetDataBaseTime()
{
return DbContext.GetDate();
}
#endregion
#region 新增
/// <summary>
/// 新增
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 </param>
/// <returns>操作影响的行数</returns>
public int Add<T>(T entity) where T : class, new()
{
try
{
var result = DbContext.Insertable(entity).ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 新增
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entitys">泛型集合</param>
/// <returns>操作影响的行数</returns>
public int Add<T>(List<T> entitys) where T : class, new()
{
try
{
var result = DbContext.Insertable(entitys).ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 新增
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="keyValues">字典集合(Key:列名 Value:值)</param>
/// <returns>操作影响的行数</returns>
public int Add<T>(Dictionary<string, object> keyValues) where T : class, new()
{
try
{
var result = DbContext.Insertable<T>(keyValues).ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 新增
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 </param>
/// <returns>返回实体</returns>
public T AddReturnEntity<T>(T entity) where T : class, new()
{
try
{
var result = DbContext.Insertable(entity).ExecuteReturnEntity();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 新增
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 </param>
/// <returns>返回自增列</returns>
public int AddReturnIdentity<T>(T entity) where T : class, new()
{
try
{
var result = DbContext.Insertable(entity).ExecuteReturnIdentity();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 新增
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 </param>
/// <returns>返回bool</returns>
public bool AddReturnBool<T>(T entity) where T : class, new()
{
try
{
var result = DbContext.Insertable(entity).ExecuteCommand()>0;
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 新增
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entitys">泛型集合</param>
/// <returns>返回bool</returns>
public bool AddReturnBool<T>(List<T> entitys) where T : class, new()
{
try
{
var result = DbContext.Insertable(entitys).ExecuteCommand() > 0;
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion
#region 修改
/// <summary>
/// 修改(主键是更新条件)
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象(必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]),如果是联合主键,请使用Where条件 </param>
/// <param name="lstIgnoreColumns">不更新的列</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
public int Update<T>(T entity, List<string> lstIgnoreColumns = null, bool isLock = true) where T : class, new()
{
try
{
IUpdateable<T> up = DbContext.Updateable(entity);
if (lstIgnoreColumns != null && lstIgnoreColumns.Count > 0)
{
up = up.IgnoreColumns(lstIgnoreColumns.Contains);
}
if (isLock)
{
up = up.With(SqlWith.UpdLock);
}
var result = up.ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 修改(主键是更新条件)
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entitys"> 实体对象集合(必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]),如果是联合主键,请使用Where条件 </param>
/// <param name="lstIgnoreColumns">不更新的列</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
public int Update<T>(List<T> entitys, List<string> lstIgnoreColumns = null, bool isLock = true) where T : class, new()
{
try
{
IUpdateable<T> up = DbContext.Updateable(entitys);
if (lstIgnoreColumns != null && lstIgnoreColumns.Count > 0)
{
up = up.IgnoreColumns(lstIgnoreColumns.Contains);
}
if (isLock)
{
up = up.With(SqlWith.UpdLock);
}
var result = up.ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 修改
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 </param>
/// <param name="where"> 条件 </param>
/// <param name="lstIgnoreColumns">不更新的列</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
public int Update<T>(T entity, Expression<Func<T, bool>> where, List<string> lstIgnoreColumns = null, bool isLock = true) where T : class, new()
{
try
{
IUpdateable<T> up = DbContext.Updateable(entity);
if (lstIgnoreColumns != null && lstIgnoreColumns.Count > 0)
{
up = up.IgnoreColumns(lstIgnoreColumns.Contains);
}
up = up.Where(where);
if (isLock)
{
up = up.With(SqlWith.UpdLock);
}
var result = up.ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 修改
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="update"> 实体对象 </param>
/// <param name="where"> 条件 </param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
public int Update<T>(Expression<Func<T, T>> update, Expression<Func<T, bool>> where=null, bool isLock = true) where T : class, new()
{
try
{
IUpdateable<T> up = DbContext.Updateable<T>().UpdateColumns(update);
if (where != null)
{
up = up.Where(where);
}
if (isLock)
{
up = up.With(SqlWith.UpdLock);
}
var result = up.ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 修改
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="keyValues">字典集合(Key:列名 Value:值)</param>
/// <param name="where"> 条件 </param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
public int Update<T>(Dictionary<string, object> keyValues, Expression<Func<T, bool>> where = null, bool isLock = true) where T : class, new()
{
try
{
IUpdateable<T> up = DbContext.Updateable<T>(keyValues);
if (where != null)
{
up = up.Where(where);
}
if (isLock)
{
up = up.With(SqlWith.UpdLock);
}
var result = up.ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 批量修改需要更新的列
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entitys"> 实体对象(必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]),如果是联合主键,请使用Where条件 </param>
/// <param name="updateColumns">更新指定列</param>
/// <param name="wherecolumns">条件(为空则以主键更新,反之需要把wherecolumns中的列加到UpdateColumns中)</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
public int UpdateColumns<T>(List<T> entitys, Expression<Func<T, object>> updateColumns, Expression<Func<T, object>> wherecolumns = null, bool isLock = true) where T : class, new()
{
try
{
IUpdateable<T> up = DbContext.Updateable(entitys).UpdateColumns(updateColumns);
if (wherecolumns != null)
{
up = up.WhereColumns(wherecolumns);
}
if (isLock)
{
up = up.With(SqlWith.UpdLock);
}
var result = up.ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 修改 通过RowVer及主键Code 更新
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 </param>
/// <param name="lstIgnoreColumns">不更新的列</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
public int UpdateRowVer<T>(T entity, List<string> lstIgnoreColumns = null, bool isLock = true) where T : class, new()
{
try
{
Type ts = entity.GetType();
var rowVerProperty = ts.GetProperty("RowVer");
if (rowVerProperty == null)
{
throw new Exception("Column RowVer Not Exist");
}
if (rowVerProperty.GetValue(entity, null) == null)
{
throw new Exception("RowVer Value Is Null");
}
var codeProperty = ts.GetProperty("Code");
if (codeProperty == null)
{
throw new Exception("Column Code Not Exist");
}
if (codeProperty.GetValue(entity, null) == null)
{
throw new Exception("Code Value Is Null");
}
var rowVerValue = int.Parse(rowVerProperty.GetValue(entity, null).ToString());
var codeValue = codeProperty.GetValue(entity, null).ToString();
var sqlWhere = $" RowVer={rowVerValue} AND Code='{codeValue}'";
rowVerProperty.SetValue(entity, rowVerValue + 1, null);
IUpdateable<T> up = DbContext.Updateable(entity);
if (lstIgnoreColumns != null && lstIgnoreColumns.Count > 0)
{
up = up.IgnoreColumns(lstIgnoreColumns.Contains);
}
up = up.Where(sqlWhere);
if (isLock)
{
up = up.With(SqlWith.UpdLock);
}
var result = up.ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 修改
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="update"> 实体对象 </param>
/// <param name="where"> 更新条件 </param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
public int UpdateRowVer<T>(Expression<Func<T, T>> update, Dictionary<string, object> where, bool isLock = true) where T : class, new()
{
try
{
if (!where.ContainsKey("RowVer"))
{
throw new Exception("Column RowVer Not Exist");
}
if (where["RowVer"] == null)
{
throw new Exception("RowVer Value Is Null");
}
if (update.Body.ToString().IndexOf("RowVer", StringComparison.Ordinal)==-1)
{
throw new Exception("Column RowVer Update Is Null");
}
var sqlWhere = "";
foreach (var item in where)
{
sqlWhere +=string.IsNullOrWhiteSpace(sqlWhere)? $" {item.Key}='{item.Value}'" : $" and {item.Key}='{item.Value}'";
}
IUpdateable<T> up = DbContext.Updateable<T>().UpdateColumns(update).Where(sqlWhere);
if (isLock)
{
up = up.With(SqlWith.UpdLock);
}
var result = up.ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion
#region 删除
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="primaryKeyValues">主键值</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
public int DeleteByPrimary<T>(List<object> primaryKeyValues, bool isLock = true) where T : class, new()
{
try
{
var result = isLock ?
DbContext.Deleteable<T>().In(primaryKeyValues).With(SqlWith.RowLock).ExecuteCommand()
: DbContext.Deleteable<T>().In(primaryKeyValues).ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 (必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]),如果是联合主键,请使用Where条件</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
public int Delete<T>(T entity, bool isLock = true) where T : class, new()
{
try
{
var result = isLock ?
DbContext.Deleteable(entity).With(SqlWith.RowLock).ExecuteCommand()
: DbContext.Deleteable(entity).ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="entity"> 实体对象 (必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]),如果是联合主键,请使用Where条件</param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
public int Delete<T>(List<T> entity, bool isLock = true) where T : class, new()
{
try
{
var result = isLock ?
DbContext.Deleteable(entity).With(SqlWith.RowLock).ExecuteCommand()
: DbContext.Deleteable(entity).ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="where"> 条件 </param>
/// <param name="isLock"> 是否加锁 </param>
/// <returns>操作影响的行数</returns>
public int Delete<T>(Expression<Func<T, bool>> where, bool isLock = true) where T : class, new()
{
try
{
var result = isLock ?
DbContext.Deleteable<T>().Where(where).With(SqlWith.RowLock).ExecuteCommand()
: DbContext.Deleteable<T>().Where(where).ExecuteCommand();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 通过多值(主键)删除数据集
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="inValues">数据集合</param>
/// <returns>值</returns>
public int DeleteIn<T>(List<dynamic> inValues) where T : class, new()
{
return DbContext.Deleteable<T>().With(SqlWith.RowLock).In(inValues).ExecuteCommand();
}
#endregion
#region 查询
#region 数据源
/ <summary>
/ 查询数据源
/ </summary>
/ <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/ <returns>数据源</returns>
//public ISugarQueryable<T> Queryable<T>() where T : class, new()
//{
// return DbContext.Queryable<T>();
//}
#endregion
#region 多表查询
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (t1,t2) => new object[] {JoinType.Left,t1.UserNo==t2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (t1, t2) => new { Id =t1.UserNo, Id1 = t2.UserNo}</param>
/// <param name="whereLambda">查询表达式 (t1, t2) =>t1.UserNo == "")</param>
/// <returns>值</returns>
public List<TResult> QueryMuch<T, T2, TResult>(
Expression<Func<T, T2, object[]>> joinExpression,
Expression<Func<T, T2, TResult>> selectExpression,
Expression<Func<T, T2, bool>> whereLambda = null) where T : class, new()
{
if (whereLambda == null)
{
return DbContext.Queryable(joinExpression).Select(selectExpression).ToList();
}
return DbContext.Queryable(joinExpression).Where(whereLambda).Select(selectExpression).ToList();
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="whereLambda">查询表达式 (w1, w2) =>w1.UserNo == "")</param>
/// <returns>值</returns>
public List<TResult> QueryMuch<T, T2, T3, TResult>(
Expression<Func<T, T2, T3, object[]>> joinExpression,
Expression<Func<T, T2, T3, TResult>> selectExpression,
Expression<Func<T, T2, T3, bool>> whereLambda = null) where T : class, new()
{
if (whereLambda == null)
{
return DbContext.Queryable(joinExpression).Select(selectExpression).ToList();
}
return DbContext.Queryable(joinExpression).Where(whereLambda).Select(selectExpression).ToList();
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="whereLambda">查询表达式 (w1, w2) =>w1.UserNo == "")</param>
/// <returns>值</returns>
public List<TResult> QueryMuch<T, T2, T3, T4, TResult>(
Expression<Func<T, T2, T3, T4, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, TResult>> selectExpression,
Expression<Func<T, T2, T3, T4, bool>> whereLambda = null) where T : class, new()
{
if (whereLambda == null)
{
return DbContext.Queryable(joinExpression).Select(selectExpression).ToList();
}
return DbContext.Queryable(joinExpression).Where(whereLambda).Select(selectExpression).ToList();
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="whereLambda">查询表达式 (w1, w2) =>w1.UserNo == "")</param>
/// <returns>值</returns>
public List<TResult> QueryMuch<T, T2, T3, T4, T5, TResult>(
Expression<Func<T, T2, T3, T4, T5,object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5,TResult>> selectExpression,
Expression<Func<T, T2, T3, T4, T5, bool>> whereLambda = null) where T : class, new()
{
if (whereLambda == null)
{
return DbContext.Queryable(joinExpression).Select(selectExpression).ToList();
}
return DbContext.Queryable(joinExpression).Where(whereLambda).Select(selectExpression).ToList();
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="whereLambda">查询表达式 (w1, w2) =>w1.UserNo == "")</param>
/// <returns>值</returns>
public List<TResult> QueryMuch<T, T2, T3, T4, T5,T6, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, TResult>> selectExpression,
Expression<Func<T, T2, T3, T4, T5, T6, bool>> whereLambda = null) where T : class, new()
{
if (whereLambda == null)
{
return DbContext.Queryable(joinExpression).Select(selectExpression).ToList();
}
return DbContext.Queryable(joinExpression).Where(whereLambda).Select(selectExpression).ToList();
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="T7">实体7</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="whereLambda">查询表达式 (w1, w2) =>w1.UserNo == "")</param>
/// <returns>值</returns>
public List<TResult> QueryMuch<T, T2, T3, T4, T5, T6, T7, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, T7, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, TResult>> selectExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, bool>> whereLambda = null) where T : class, new()
{
if (whereLambda == null)
{
return DbContext.Queryable(joinExpression).Select(selectExpression).ToList();
}
return DbContext.Queryable(joinExpression).Where(whereLambda).Select(selectExpression).ToList();
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="T7">实体7</typeparam>
/// <typeparam name="T8">实体8</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="whereLambda">查询表达式 (w1, w2) =>w1.UserNo == "")</param>
/// <returns>值</returns>
public List<TResult> QueryMuch<T, T2, T3, T4, T5, T6, T7, T8, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, TResult>> selectExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, bool>> whereLambda = null) where T : class, new()
{
if (whereLambda == null)
{
return DbContext.Queryable(joinExpression).Select(selectExpression).ToList();
}
return DbContext.Queryable(joinExpression).Where(whereLambda).Select(selectExpression).ToList();
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="T7">实体7</typeparam>
/// <typeparam name="T8">实体8</typeparam>
/// <typeparam name="T9">实体9</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="whereLambda">查询表达式 (w1, w2) =>w1.UserNo == "")</param>
/// <returns>值</returns>
public List<TResult> QueryMuch<T, T2, T3, T4, T5, T6, T7, T8, T9, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, T9, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, T9, TResult>> selectExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, T9, bool>> whereLambda = null) where T : class, new()
{
if (whereLambda == null)
{
return DbContext.Queryable(joinExpression).Select(selectExpression).ToList();
}
return DbContext.Queryable(joinExpression).Where(whereLambda).Select(selectExpression).ToList();
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (t1,t2) => new object[] {JoinType.Left,t1.UserNo==t2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (t1, t2) => new { Id =t1.UserNo, Id1 = t2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
public List<TResult> QueryMuchDescriptor<T, T2, TResult>(
Expression<Func<T, T2, object[]>> joinExpression,
Expression<Func<T, T2, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new()
{
var up = DbContext.Queryable(joinExpression).Select(selectExpression).MergeTable();
if (query.Conditions != null)
{
var conds = ParseCondition(query.Conditions);
up = up.Where(conds);
}
if (query.OrderBys != null)
{
var orderBys = ParseOrderBy(query.OrderBys);
up = up.OrderBy(orderBys);
}
totalCount = 0;
if (query.PageSize == 0)
{
var datas = up.ToList();
totalCount = datas.Count;
return datas;
}
else
{
var datas = up.ToPageList(query.PageIndex, query.PageSize, ref totalCount);
return datas;
}
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
public List<TResult> QueryMuchDescriptor<T, T2, T3, TResult>(
Expression<Func<T, T2, T3, object[]>> joinExpression,
Expression<Func<T, T2, T3, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new()
{
var up = DbContext.Queryable(joinExpression).Select(selectExpression).MergeTable();
if (query.Conditions != null)
{
var conds = ParseCondition(query.Conditions);
up = up.Where(conds);
}
if (query.OrderBys != null)
{
var orderBys = ParseOrderBy(query.OrderBys);
up = up.OrderBy(orderBys);
}
totalCount = 0;
if (query.PageSize == 0)
{
var datas = up.ToList();
totalCount = datas.Count;
return datas;
}
else
{
var datas = up.ToPageList(query.PageIndex, query.PageSize, ref totalCount);
return datas;
}
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
public List<TResult> QueryMuchDescriptor<T, T2, T3, T4, TResult>(
Expression<Func<T, T2, T3, T4, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new()
{
var up = DbContext.Queryable(joinExpression).Select(selectExpression).MergeTable();
if (query.Conditions != null)
{
var conds = ParseCondition(query.Conditions);
up = up.Where(conds);
}
if (query.OrderBys != null)
{
var orderBys = ParseOrderBy(query.OrderBys);
up = up.OrderBy(orderBys);
}
totalCount = 0;
if (query.PageSize == 0)
{
var datas = up.ToList();
totalCount = datas.Count;
return datas;
}
else
{
var datas = up.ToPageList(query.PageIndex, query.PageSize, ref totalCount);
return datas;
}
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
public List<TResult> QueryMuchDescriptor<T, T2, T3, T4, T5, TResult>(
Expression<Func<T, T2, T3, T4, T5, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new()
{
var up = DbContext.Queryable(joinExpression).Select(selectExpression).MergeTable();
if (query.Conditions != null)
{
var conds = ParseCondition(query.Conditions);
up = up.Where(conds);
}
if (query.OrderBys != null)
{
var orderBys = ParseOrderBy(query.OrderBys);
up = up.OrderBy(orderBys);
}
totalCount = 0;
if (query.PageSize == 0)
{
var datas = up.ToList();
totalCount = datas.Count;
return datas;
}
else
{
var datas = up.ToPageList(query.PageIndex, query.PageSize, ref totalCount);
return datas;
}
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
public List<TResult> QueryMuchDescriptor<T, T2, T3, T4, T5, T6, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new()
{
var up = DbContext.Queryable(joinExpression).Select(selectExpression).MergeTable();
if (query.Conditions != null)
{
var conds = ParseCondition(query.Conditions);
up = up.Where(conds);
}
if (query.OrderBys != null)
{
var orderBys = ParseOrderBy(query.OrderBys);
up = up.OrderBy(orderBys);
}
totalCount = 0;
if (query.PageSize == 0)
{
var datas = up.ToList();
totalCount = datas.Count;
return datas;
}
else
{
var datas = up.ToPageList(query.PageIndex, query.PageSize, ref totalCount);
return datas;
}
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="T7">实体7</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
public List<TResult> QueryMuchDescriptor<T, T2, T3, T4, T5, T6, T7, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, T7, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new()
{
var up = DbContext.Queryable(joinExpression).Select(selectExpression).MergeTable();
if (query.Conditions != null)
{
var conds = ParseCondition(query.Conditions);
up = up.Where(conds);
}
if (query.OrderBys != null)
{
var orderBys = ParseOrderBy(query.OrderBys);
up = up.OrderBy(orderBys);
}
totalCount = 0;
if (query.PageSize == 0)
{
var datas = up.ToList();
totalCount = datas.Count;
return datas;
}
else
{
var datas = up.ToPageList(query.PageIndex, query.PageSize, ref totalCount);
return datas;
}
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="T7">实体7</typeparam>
/// <typeparam name="T8">实体8</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
public List<TResult> QueryMuchDescriptor<T, T2, T3, T4, T5, T6, T7, T8, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new()
{
var up = DbContext.Queryable(joinExpression).Select(selectExpression).MergeTable();
if (query.Conditions != null)
{
var conds = ParseCondition(query.Conditions);
up = up.Where(conds);
}
if (query.OrderBys != null)
{
var orderBys = ParseOrderBy(query.OrderBys);
up = up.OrderBy(orderBys);
}
totalCount = 0;
if (query.PageSize == 0)
{
var datas = up.ToList();
totalCount = datas.Count;
return datas;
}
else
{
var datas = up.ToPageList(query.PageIndex, query.PageSize, ref totalCount);
return datas;
}
}
/// <summary>
///查询-多表查询
/// </summary>
/// <typeparam name="T">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="T7">实体7</typeparam>
/// <typeparam name="T8">实体8</typeparam>
/// <typeparam name="T9">实体9</typeparam>
/// <typeparam name="TResult">返回对象</typeparam>
/// <param name="joinExpression">关联表达式 (join1,join2) => new object[] {JoinType.Left,join1.UserNo==join2.UserNo}</param>
/// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo}</param>
/// <param name="query">查询条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>值</returns>
public List<TResult> QueryMuchDescriptor<T, T2, T3, T4, T5, T6, T7, T8, T9, TResult>(
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, T9, object[]>> joinExpression,
Expression<Func<T, T2, T3, T4, T5, T6, T7, T8, T9, TResult>> selectExpression,
QueryDescriptor query, out int totalCount) where T : class, new()
{
var up = DbContext.Queryable(joinExpression).Select(selectExpression).MergeTable();
if (query.Conditions != null)
{
var conds = ParseCondition(query.Conditions);
up = up.Where(conds);
}
if (query.OrderBys != null)
{
var orderBys = ParseOrderBy(query.OrderBys);
up = up.OrderBy(orderBys);
}
totalCount = 0;
if (query.PageSize == 0)
{
var datas = up.ToList();
totalCount = datas.Count;
return datas;
}
else
{
var datas = up.ToPageList(query.PageIndex, query.PageSize, ref totalCount);
return datas;
}
}
#endregion
#region 查询
/// <summary>
/// 查询-返回自定义数据
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <typeparam name="TResult">返回值类型</typeparam>
/// <param name="expression">返回值表达式</param>
/// <param name="whereLambda">查询表达式</param>
/// <returns>值</returns>
public TResult QuerySelect<T,TResult>( Expression<Func<T, TResult>> expression,Expression<Func<T, bool>> whereLambda = null) where T : class, new()
{
var datas = DbContext.Queryable<T>().With(SqlWith.NoLock).Where(whereLambda).Select(expression).First();
return datas;
}
/// <summary>
/// 查询-返回自定义数据
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <typeparam name="TResult">返回值类型</typeparam>
/// <param name="expression">返回值表达式</param>
/// <param name="whereLambda">查询表达式</param>
/// <returns>值</returns>
public List<TResult> QuerySelectList<T, TResult>(Expression<Func<T, TResult>> expression, Expression<Func<T, bool>> whereLambda = null) where T : class, new()
{
var datas = DbContext.Queryable<T>().With(SqlWith.NoLock).Where(whereLambda).Select(expression).ToList();
return datas;
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="whereLambda">查询表达式</param>
/// <returns>值</returns>
public T Query<T>(Expression<Func<T, bool>> whereLambda = null) where T : class, new()
{
ISugarQueryable<T> up = DbContext.Queryable<T>().With(SqlWith.NoLock);
if (whereLambda != null)
{
up = up.Where(whereLambda);
}
return up.First();
}
/// <summary>
/// 查询集合
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="whereLambda">过滤条件</param>
/// <returns>实体</returns>
public List<T> QueryWhereList<T>(Expression<Func<T, bool>> whereLambda = null) where T : class, new()
{
ISugarQueryable<T> up = DbContext.Queryable<T>().With(SqlWith.NoLock);
if (whereLambda != null)
{
up = up.Where(whereLambda);
}
return up.ToList();
}
/// <summary>
/// 查询集合
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="query">过滤条件</param>
/// <returns>实体</returns>
public List<T> QueryList<T>(QueryDescriptor query=null) where T : class, new()
{
ISugarQueryable<T> up = DbContext.Queryable<T>().With(SqlWith.NoLock);
if (query != null)
{
if (query.Conditions != null)
{
var conds = ParseCondition(query.Conditions);
up = up.Where(conds);
}
if (query.OrderBys != null)
{
var orderBys = ParseOrderBy(query.OrderBys);
up = up.OrderBy(orderBys);
}
}
return up.ToList();
}
/// <summary>
/// 查询集合
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="sql">sql</param>
/// <returns>实体</returns>
public List<T> QuerySqlList<T>(string sql) where T : class, new()
{
return DbContext.SqlQueryable<T>(sql).ToList();
}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="query">过滤条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>实体</returns>
public List<T> QueryPageList<T>(QueryDescriptor query, out int totalCount) where T : class, new()
{
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
var listDatas = DbContext.Queryable<T>();
if (query.Conditions != null)
{
var conds = ParseCondition(query.Conditions);
listDatas = listDatas.Where(conds);
}
if (query.OrderBys != null)
{
var orderBys = ParseOrderBy(query.OrderBys);
listDatas = listDatas.OrderBy(orderBys);
}
totalCount = 0;
if (query.PageSize == 0)
{
var datas = listDatas.ToList();
totalCount = datas.Count;
return datas;
}
else
{
var datas = listDatas.ToPageList(query.PageIndex, query.PageSize, ref totalCount);
return datas;
}
}
/// <summary>
/// 查询集合-多值
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="inFieldName">字段名</param>
/// <param name="inValues">数据集合</param>
/// <returns>值</returns>
public List<T> In<T>(string inFieldName, List<dynamic> inValues) where T : class, new()
{
return DbContext.Queryable<T>().In(inFieldName, inValues).ToList();
}
/// <summary>
/// 查询集合-通过多值(主键)
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="values">主键数据集合</param>
/// <returns>值</returns>
public List<T> In<T>(List<dynamic> values) where T : class, new()
{
return DbContext.Queryable<T>().In(values).ToList();
}
/// <summary>
/// 查询集合
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="whereLambda">查询表达式</param>
/// <returns>实体</returns>
public DataTable QueryDataTable<T>(Expression<Func<T, bool>> whereLambda = null) where T : class, new()
{
ISugarQueryable<T> up = DbContext.Queryable<T>().With(SqlWith.NoLock);
if (whereLambda != null)
{
up = up.Where(whereLambda);
}
return up.ToDataTable();
}
/// <summary>
/// 查询集合
/// </summary>
/// <param name="sql">sql</param>
/// <returns>实体</returns>
public DataTable QueryDataTable(string sql)
{
return DbContext.Ado.GetDataTable(sql);
}
/// <summary>
/// 查询单个值
/// </summary>
/// <param name="sql">sql</param>
/// <returns>单个值</returns>
public object QuerySqlScalar(string sql)
{
return DbContext.Ado.GetScalar(sql);
}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="query">过滤条件</param>
/// <param name="totalCount">总行数</param>
/// <returns>DataTable</returns>
public DataTable QueryDataTablePageList<T>(QueryDescriptor query, out int totalCount) where T : class, new()
{
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
var listDatas = DbContext.Queryable<T>();
if (query.Conditions != null)
{
var conds = ParseCondition(query.Conditions);
listDatas = listDatas.Where(conds);
}
if (query.OrderBys != null)
{
var orderBys = ParseOrderBy(query.OrderBys);
listDatas = listDatas.OrderBy(orderBys);
}
totalCount = 0;
var datas = listDatas.ToDataTablePage(query.PageIndex, query.PageSize, ref totalCount);
return datas;
}
#endregion
#region Mapper
/// <summary>
/// Mapper查询 一对多和一对一
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="mapperAction">操作(it, cache) =>
/// {
/// var all = cache.GetListByPrimaryKeys(vmodel => vmodel.Id); //一次性查询出所要的外键引用数据
/// it.School = all.FirstOrDefault(i => i.Id == it.Id); //一对一
/// it.Schools = all.Where(i => i.Id == it.Id).ToList(); //一对多
/// /*用C#处理你想要的结果*/
/// it.Name = it.Name == null ? "null" : it.Name;
/// }
/// </param>
/// <param name="query">过滤条件</param>
/// <returns></returns>
public List<T> QueryMapper<T>(Action<T> mapperAction,QueryDescriptor query = null) where T : class, new()
{
ISugarQueryable<T> up = DbContext.Queryable<T>();
if (query != null)
{
if (query.Conditions != null)
{
var conds = ParseCondition(query.Conditions);
up = up.Where(conds);
}
if (query.OrderBys != null)
{
var orderBys = ParseOrderBy(query.OrderBys);
up = up.OrderBy(orderBys);
}
}
var datas = up.Mapper(mapperAction).ToList();
return datas;
}
/// <summary>
/// Mapper查询 一对多和一对一
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="mapperAction">操作(it, cache) =>
/// {
/// var all = cache.GetListByPrimaryKeys(vmodel => vmodel.Id); //一次性查询出所要的外键引用数据
/// it.School = all.FirstOrDefault(i => i.Id == it.Id); //一对一
/// it.Schools = all.Where(i => i.Id == it.Id).ToList(); //一对多
/// /*用C#处理你想要的结果*/
/// it.Name = it.Name == null ? "null" : it.Name;
/// }
/// </param>
/// <param name="whereLambda">过滤条件</param>
/// <returns></returns>
public List<T> QueryMapper<T>(Action<T> mapperAction, Expression<Func<T, bool>> whereLambda = null) where T : class, new()
{
ISugarQueryable<T> up = DbContext.Queryable<T>();
if (whereLambda != null)
{
up = up.Where(whereLambda);
}
var datas = up.Mapper(mapperAction).ToList();
return datas;
}
#endregion
#region 分组
/// <summary>
/// 分组
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="groupByLambda">分组表达式</param>
/// <param name="whereLambda">查询表达式</param>
/// <returns>值</returns>
public List<T> GroupBy<T>(Expression<Func<T, object>> groupByLambda, Expression<Func<T, bool>> whereLambda = null) where T : class, new()
{
var datas = DbContext.Queryable<T>().With(SqlWith.NoLock).Where(whereLambda).GroupBy(groupByLambda).ToList();
return datas;
}
/// <summary>
/// 分组-返回自定义数据
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <typeparam name="TResult">返回值类型</typeparam>
/// <param name="expression">返回值表达式</param>
/// <param name="groupByLambda">分组表达式</param>
/// <param name="whereLambda">查询表达式</param>
/// <returns>值</returns>
public List<TResult> GroupBy<T, TResult>(Expression<Func<T, TResult>> expression, Expression<Func<T, object>> groupByLambda, Expression<Func<T, bool>> whereLambda = null) where T : class, new()
{
var datas = DbContext.Queryable<T>().With(SqlWith.NoLock).Where(whereLambda).GroupBy(groupByLambda).Select(expression).ToList();
return datas;
}
#endregion
#region 存储过程
/// <summary>
/// 查询存储过程
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="parameters">参数</param>
/// <returns>DataSet</returns>
public DataSet QueryProcedureDataSet(string procedureName, List<SqlParameter> parameters)
{
var listParams = new List<SugarParameter>();
foreach (var p in parameters)
{
listParams.Add(new SugarParameter(p.ParameterName, p.Value, null, p.Direction));
}
var datas = DbContext.Ado.UseStoredProcedure().GetDataSetAll(procedureName, listParams);
return datas;
}
/// <summary>
/// 查询存储过程
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="parameters">参数</param>
/// <returns>DataTable</returns>
public DataTable QueryProcedure(string procedureName, List<SqlParameter> parameters)
{
var listParams = new List<SugarParameter>();
foreach (var p in parameters)
{
listParams.Add(new SugarParameter(p.ParameterName,p.Value,null,p.Direction));
}
var datas = DbContext.Ado.UseStoredProcedure().GetDataTable(procedureName, listParams);
return datas;
}
/// <summary>
/// 查询存储过程
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="parameters">参数</param>
/// <returns>单个值</returns>
public object QueryProcedureScalar(string procedureName, List<SqlParameter> parameters)
{
var listParams = new List<SugarParameter>();
foreach (var p in parameters)
{
listParams.Add(new SugarParameter(p.ParameterName, p.Value, null, p.Direction));
}
var datas = DbContext.Ado.UseStoredProcedure().GetScalar(procedureName, listParams);
return datas;
}
#endregion
#region Json
/// <summary>
/// 查询集合
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="whereLambda">查询表达式</param>
/// <returns>Json</returns>
public string QueryJson<T>(Expression<Func<T, bool>> whereLambda = null) where T : class, new()
{
ISugarQueryable<T> up = DbContext.Queryable<T>().With(SqlWith.NoLock);
if (whereLambda != null)
{
up = up.Where(whereLambda);
}
return up.ToJson();
}
#endregion
#region 其它
/// <summary>
/// 查询前多少条数据
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/// <param name="whereLambda">查询表达式</param>
/// <param name="num">数量</param>
/// <returns>值</returns>
public List<T> Take<T>(Expression<Func<T, bool>> whereLambda, int num) where T : class, new()
{
var datas = DbContext.Queryable<T>().With(SqlWith.NoLock).Where(whereLambda).Take(num).ToList();
return datas;
}
/// <summary>
/// 查询单条数据
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/// <param name="whereLambda">查询表达式</param>
/// <returns>值</returns>
public T First<T>(Expression<Func<T, bool>> whereLambda) where T : class, new()
{
var datas = DbContext.Queryable<T>().With(SqlWith.NoLock).Where(whereLambda).First();
return datas;
}
/// <summary>
/// 是否存在
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/// <param name="whereLambda">查询表达式</param>
/// <returns>值</returns>
public bool IsExist<T>(Expression<Func<T, bool>> whereLambda) where T : class, new()
{
var datas = DbContext.Queryable<T>().Any(whereLambda);
return datas;
}
/// <summary>
/// 合计
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/// <param name="field">字段</param>
/// <returns>值</returns>
public int Sum<T>(string field) where T : class, new()
{
var datas = DbContext.Queryable<T>().Sum<int>(field);
return datas;
}
/// <summary>
/// 最大值
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/// <typeparam name="TResult">返回类型</typeparam>
/// <param name="field">字段</param>
/// <returns>值</returns>
public TResult Max<T, TResult>(string field) where T : class, new()
{
var datas = DbContext.Queryable<T>().Max<TResult>(field);
return datas;
}
/// <summary>
/// 最小值
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/// <typeparam name="TResult">返回类型</typeparam>
/// <param name="field">字段</param>
/// <returns>值</returns>
public TResult Min<T, TResult>(string field) where T : class, new()
{
var datas = DbContext.Queryable<T>().Min<TResult>(field);
return datas;
}
/// <summary>
/// 平均值
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型</typeparam>
/// <param name="field">字段</param>
/// <returns>值</returns>
public int Avg<T>(string field) where T : class, new()
{
var datas = DbContext.Queryable<T>().Avg<int>(field);
return datas;
}
/// <summary>
/// 生成流水号
/// </summary>
/// <param name="key">列名</param>
/// <param name="prefix">前缀</param>
/// <param name="fixedLength">流水号长度</param>
/// <param name="dateFomart">日期格式(yyyyMMdd) 为空前缀后不加日期,反之加</param>
/// <returns></returns>
public string CustomNumber<T>(string key, string prefix = "", int fixedLength = 4, string dateFomart = "") where T : class, new()
{
var listNumber = CustomNumber<T>(key, 1,prefix, fixedLength, dateFomart);
return listNumber[0];
}
/// <summary>
/// 生成流水号
/// </summary>
/// <param name="key">列名</param>
/// <param name="prefix">前缀</param>
/// <param name="fixedLength">流水号长度</param>
/// <param name="dateFomart">日期格式(yyyyMMdd) 为空前缀后不加日期,反之加</param>
/// <param name="num">数量</param>
/// <returns></returns>
public List<string> CustomNumber<T>(string key,int num, string prefix = "", int fixedLength = 4,string dateFomart = "") where T : class, new()
{
List<string> numbers = new List<string>();
var dateValue = dateFomart == "" ? "" : DateTime.Now.ToString(dateFomart);
var fix = prefix.ToUpper() + dateValue;
var maxValue = DbContext.Queryable<T>().Where(key + " LIKE '" + fix + "%' AND LEN(" + key + ")=" + (fix.Length + fixedLength)).Select(key).Max<string>(key);
if (maxValue == null)
{
for (var i = 0; i < num; i++)
{
var tempNumber = fix + (i + 1).ToString().PadLeft(fixedLength, '0');
numbers.Add(tempNumber);
}
}
else
{
if (maxValue.Substring(0, maxValue.Length - fixedLength) == prefix + dateValue)
{
var tempLast = maxValue.Substring(maxValue.Length - fixedLength);
for (var i = 0; i < num; i++)
{
var tempNumber = fix + (int.Parse(tempLast) + i + 1).ToString().PadLeft(fixedLength, '0');
numbers.Add(tempNumber);
}
}
else
{
for (var i = 0; i < num; i++)
{
var tempNumber = fix + (i + 1).ToString().PadLeft(fixedLength, '0');
numbers.Add(tempNumber);
}
}
}
return numbers;
}
#endregion
#endregion
#region 私有方法
/// <summary>
/// 过滤条件转换
/// </summary>
/// <param name="contitons">过滤条件</param>
/// <returns>值</returns>
private List<IConditionalModel> ParseCondition(List<QueryCondition> contitons)
{
var conds = new List<IConditionalModel>();
contitons.Insert(0, new QueryCondition
{
Operator = QueryOperator.Equal,
Key = "1",
Value = "1"
});
foreach (var con in contitons)
{
if (con.Key.Contains(","))
{
conds.Add(ParseKeyOr(con));
}
else if (con.Operator == QueryOperator.DateRange)
{
conds.AddRange(ParseRange(con,con.Operator));
}
else
{
conds.Add(new ConditionalModel()
{
FieldName = con.Key,
ConditionalType = (ConditionalType)(int)con.Operator,
FieldValue = con.Value.ToString()
});
}
}
return conds;
}
/// <summary>
/// 转换Or条件
/// </summary>
/// <param name="condition">过滤条件</param>
/// <returns>值</returns>
private ConditionalCollections ParseKeyOr(QueryCondition condition)
{
var objectKeys = condition.Key.Split(',');
var conditionalList = new List<KeyValuePair<WhereType, ConditionalModel>>();
var num = 0;
foreach (var objKey in objectKeys)
{
if (num == 0)
{
var cond = new KeyValuePair<WhereType, ConditionalModel>
(WhereType.And, new ConditionalModel()
{
FieldName = objKey,
ConditionalType = (ConditionalType)(int)condition.Operator,
FieldValue = condition.Value.ToString()
});
conditionalList.Add(cond);
}
else
{
var cond = new KeyValuePair<WhereType, ConditionalModel>
(WhereType.Or, new ConditionalModel()
{
FieldName = objKey,
ConditionalType = (ConditionalType)(int)condition.Operator,
FieldValue = condition.Value.ToString()
});
conditionalList.Add(cond);
}
num++;
}
return new ConditionalCollections { ConditionalList = conditionalList };
}
/// <summary>
/// 转换区域
/// </summary>
/// <param name="condition">过滤条件</param>
/// <param name="queryOperator">条件类型</param>
/// <returns>值</returns>
private List<ConditionalModel> ParseRange(QueryCondition condition, QueryOperator queryOperator)
{
var objectValue = condition.Value.ToString().Split('|');
var conditionalList = new List<ConditionalModel>();
if (objectValue.Length == 2)
{
var startValue = objectValue[0];
var endValue = objectValue[1];
if (queryOperator == QueryOperator.DateRange)
{
if (startValue.IndexOf(":", StringComparison.Ordinal) == -1)
{
startValue = startValue + " 00:00:00";
}
if (endValue.IndexOf(":", StringComparison.Ordinal) == -1)
{
endValue = endValue + " 23:59:59";
}
}
if (!string.IsNullOrWhiteSpace(objectValue[0]))
{
conditionalList.Add(new ConditionalModel()
{
FieldName = condition.Key,
ConditionalType = ConditionalType.GreaterThanOrEqual,
FieldValue = startValue
});
}
if (!string.IsNullOrWhiteSpace(objectValue[1]))
{
conditionalList.Add(new ConditionalModel()
{
FieldName = condition.Key,
ConditionalType = ConditionalType.LessThanOrEqual,
FieldValue = endValue
});
}
}
return conditionalList;
}
/// <summary>
/// 排序转换
/// </summary>
/// <param name="orderBys">排序</param>
/// <returns>值</returns>
private string ParseOrderBy(List<OrderByClause> orderBys)
{
var conds = "";
foreach (var con in orderBys)
{
switch (con.Order)
{
case OrderSequence.Asc:
conds += $"{con.Sort} asc,";
break;
case OrderSequence.Desc:
conds += $"{con.Sort} desc,";
break;
default:
throw new ArgumentOutOfRangeException();
}
}
return conds.TrimEnd(',');
}
#endregion
/// <inheritdoc />
/// <summary>
/// 释放
/// </summary>
public void Dispose()
{
DbContext.Ado.Dispose();
DbContext.Dispose();
}
}
}
5wbk
存储过程参数自定义类型用发