/// <summary>
/// 删除符合条件的实体
/// </summary>
/// <param name="predicate"></param>
/// <returns></returns>
public int DeleteByCondition(Expression <Func <TElement, bool> > predicate, IDbTransaction transaction = null)
{
TElement entity = new TElement();
string tableInDbName;
System.Reflection.PropertyInfo[] propertys;
string[] filelds;
string[] paras;
var sqlFieldMapping = ResolveEntity(entity, true, out tableInDbName, out propertys, out filelds, out paras);
if (filelds.Length <= 1)
{
//除主键后 没有其他字段
return(-1);
throw new Exception("未指定除主键后其他字段！");
}
//解析查询条件
var whereStr = "1=1";
if (null != predicate)
{
whereStr = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, sqlFieldMapping, wrapperChar: this.FieldWrapperChar);
}
StringBuilder sb_Sql = new StringBuilder();
sb_Sql.AppendFormat("delete from {0} ", tableInDbName);
if (null != predicate)
{
sb_Sql.AppendFormat("where {0} ", whereStr);
}
var sqlCmd = sb_Sql.ToString();
try
{
this.SqlOutPutToLogAsync(sqlCmd);
using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
{
var result = conn.Execute(sqlCmd, null, transaction);
return(result);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 删除一个实体
/// </summary>
/// <param name="entity"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public int Delete(TElement entity, IDbTransaction transaction = null)
{
string tableInDbName;
System.Reflection.PropertyInfo[] propertys;
string[] filelds;
string[] paras;
var sqlFieldMapping = ResolveEntity(entity, true, out tableInDbName, out propertys, out filelds, out paras);
var identityKey = sqlFieldMapping.Filelds.Where(x => x.FieldColumnName == EntityIdentityFiledName).FirstOrDefault();
if (null == identityKey)
{
//除主键后 没有其他字段
return(-1);
throw new Exception("未指定主键字段！");
}
var primaryValue = ReflectionHelper.GetPropertyValue(entity, identityKey.PropertyName);
StringBuilder sb_Sql = new StringBuilder();
sb_Sql.AppendFormat("delete from {0} ", tableInDbName);
sb_Sql.AppendFormat(" where {0}={1};", EntityIdentityFiledName, primaryValue);
var sqlCmd = sb_Sql.ToString();
//清理构建器
sb_Sql.Clear();
sb_Sql = null;
try
{
this.SqlOutPutToLogAsync(sqlCmd, entity);
using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
{
var result = conn.Execute(sqlCmd, null, transaction);
return(result);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 插入 实体
/// </summary>
/// <typeparam name="TElement"></typeparam>
/// <param name="entity">实体对象</param>
/// <param name="transaction">db事务</param>
/// <returns></returns>
public long Insert(TElement entity, IDbTransaction transaction = null)
{
string tableInDbName;
System.Reflection.PropertyInfo[] propertys;
string[] filelds;
string[] paras;
ResolveEntity(entity, true, out tableInDbName, out propertys, out filelds, out paras);
///不含主键的属性
var noIdentityPropertys = propertys.Remove(x => x.Name == EntityIdentityFiledName);
var noIdentityFileds = filelds.Remove(x => x == EntityIdentityFiledName);
var noIdentityParas = paras.Remove(x => x.ToLower() == string.Format("@{0}", EntityIdentityFiledName.ToLower()));
string splitor = string.Format("{0},{0}", this.FieldWrapperChar);
var fieldSplitString = string.Concat(this.FieldWrapperChar, string.Join(splitor, noIdentityFileds), this.FieldWrapperChar); //返回逗号分隔的字符串 例如：`ProvinceCode`,`ProvinceName`
var parasSplitString = string.Join(",", noIdentityParas); //参数 数组 的逗号分隔
StringBuilder sb_Sql = new StringBuilder();
sb_Sql.Append(string.Format("insert into {0}(", tableInDbName));
sb_Sql.Append(string.Format("{0})", fieldSplitString));
sb_Sql.Append(" values (");
sb_Sql.Append(string.Format("{0})", parasSplitString));
sb_Sql.Append(";select @@IDENTITY;");
var sqlCmd = sb_Sql.ToString();
///清理掉字符串拼接构造器
sb_Sql.Clear();
sb_Sql = null;
this.SqlOutPutToLogAsync(sqlCmd, entity);
using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
{
var result = conn.ExecuteScalar <long>(sqlCmd, entity, transaction);
return(result);
}
}
/// <summary>
///执行命令 并返回首列结果
/// </summary>
/// <param name="cmdText"></param>
/// <param name="commandParameters"></param>
/// <param name="cmdType"></param>
/// <returns></returns>
public object ExecuteScalar(string cmdText, DbParameter[] commandParameters, CommandType cmdType = CommandType.Text)
{
object val = null;
this.SqlOutPutToLogAsync(cmdText, commandParameters);
using (DbConnection conn = DatabaseFactory.GetDbConnection(this._dbConfig))
{
DbCommand cmd = DatabaseFactory.GetDbDbCommand(this._dbConfig);
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
return(val);
}
/// <summary>
/// 执行命令 并返回一个可读的Reader
/// </summary>
/// <param name="cmdText"></param>
/// <param name="commandParameters"></param>
/// <param name="cmdType"></param>
/// <returns></returns>
public DbDataReader ExecuteReader(string cmdText, DbParameter[] commandParameters, CommandType cmdType = CommandType.Text)
{
this.SqlOutPutToLogAsync(cmdText, commandParameters);
try
{
DbConnection conn = DatabaseFactory.GetDbConnection(this._dbConfig);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
DbCommand cmd = DatabaseFactory.GetDbDbCommand(this._dbConfig);
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(rdr);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行分页查询的核心方法：支持单表和多表分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition">执行条件</param>
/// <returns></returns>
public override PagedSqlDataResult <T> PageQuery <T>(PagedSqlCondition condition)
{
PagedSqlDataResult <T> pageData = null;
if (null == condition)
{
return(pageData);
}
string errMsg = "";
if (!condition.IsValid(out errMsg))
{
throw new Exception("分页查询错误：" + errMsg);
}
//记录打出日志
this.SqlOutPutToLogAsync(Contanst.PageSql_Call_Name, condition);
try
{
//判断结果集类型
if (null == condition.TableOptions)
{
//简单sql语句类型判断
if (condition.TableNameOrSqlCmd.ToLower().Contains("select") && condition.TableNameOrSqlCmd.ToLower().Contains("from"))
{
condition.TableOptions = PageTableOptions.SqlScripts;
}
else
{
condition.TableOptions = PageTableOptions.TableOrView;
}
}
//动态查询需要包装外层
if (condition.TableOptions == PageTableOptions.SqlScripts)
{
//condition.TableNameOrSqlCmd = condition.TableNameOrSqlCmd.Replace("'", "''");
condition.TableNameOrSqlCmd = string.Format(" ( {0} ) as tmpTable ", condition.TableNameOrSqlCmd);
}
//完整的sql参数转化
var fullPagerSqlParas = new DynamicParameters();
fullPagerSqlParas.Add("@PageIndex", condition.PageNumber - 1);
fullPagerSqlParas.Add("@PageSize", condition.PageSize);
fullPagerSqlParas.Add("@PrimaryKey", condition.PrimaryKey); //主键
fullPagerSqlParas.Add("@TableNameOrSqlCmd", condition.TableNameOrSqlCmd); //将查询结果结合作为分页的表数据
fullPagerSqlParas.Add("@SortField", condition.SortField);
fullPagerSqlParas.Add("@SelectFields", condition.SelectFields);
fullPagerSqlParas.Add("@ConditionWhere", condition.ConditionWhere);
fullPagerSqlParas.Add("@IsDesc", condition.IsDesc == true ? 1 : 0);
string pageSqlTemplate = PagerSQLProcedure.PAGE_SQL_CORE;
string pagerSql = string.Empty;
if (null != condition.SqlParameters)
{
//参数列表字符串--声明
//参数列表 -逗号分隔
string[] sqlParaToken = null;
string parasStr = this.GetParamSqlTokenToSqlParas(condition.TableNameOrSqlCmd, out sqlParaToken);
string paraKeyValueString = string.Empty;
string paraDeinfine = this.GetSqlServerParamDefineString(condition.SqlParameters, sqlParaToken, out paraKeyValueString);
string defWithWraper = string.Format("N'{0}'", paraDeinfine);
string defDotWraper = string.Format("N',{0}'", paraDeinfine);
if (!string.IsNullOrEmpty(paraDeinfine))
{
pagerSql = string.Format(pageSqlTemplate, " ", defWithWraper, defDotWraper, paraKeyValueString);//需要带参数
}
else
{
pagerSql = string.Format(pageSqlTemplate, "--", "N''", "N''", " ");//不用带参数
}
//将参数整体注入到动态参数
fullPagerSqlParas.AddDynamicParams(condition.SqlParameters);
}
else
{
//fullPagerSqlParas.Add("@IsParamQuery", false);
pagerSql = string.Format(pageSqlTemplate, "--", "N''", "N''", " ");//不用带参数
}
//string
using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
{
//多部分结果
var multiResult = conn.QueryMultiple(pagerSql, fullPagerSqlParas);
if (null != multiResult)
{
pageData = multiResult.ReadFirstOrDefault <PagedSqlDataResult <T> >();//分页信息
if (null != pageData)
{
pageData.DataList = multiResult.Read <T>().AsList();//结果行
}
}
//---------废弃的存储过程----------
////////////////////var dataList = conn.Query<T>(pagerSql, fullPagerSqlParas, commandType: CommandType.Text).AsList();
////////////////////if (dataList.IsNotEmpty())
////////////////////{
//////////////////// pageData = new PagedSqlDataResult<T>(dataList);
//////////////////// //查询完毕后 根据输出参数 返回总记录数 总页数
//////////////////// pageData.TotalRows = fullPagerSqlParas.Get<int>("@TotalRecords");
//////////////////// pageData.TotalPages = fullPagerSqlParas.Get<int>("@TotalPageCount");
////////////////////}
}
}
catch (Exception ex)
{
throw ex;
}
return(pageData);
}
/// <summary>
/// 通过特定的条件查询出元素集合
/// </summary>
/// <param name="predicate"></param>
/// <returns></returns>
public List <TElement> GetElementsByCondition(Expression <Func <TElement, bool> > predicate)
{
TElement entity = new TElement();
string tableInDbName;
System.Reflection.PropertyInfo[] propertys;
string[] filelds;
string[] paras;
var sqlFieldMapping = ResolveEntity(entity, false, out tableInDbName, out propertys, out filelds, out paras);
if (filelds.Length <= 1)
{
//除主键后 没有其他字段
return(null);
throw new Exception("未指定除主键后其他字段！");
}
//获取字段
//List<string> fieldAlias = new List<string>();
//foreach (var item in sqlFieldMapping.Filelds)
//{
// var ailasName = string.Format("{0} as {1}", item.FieldColumnName, item.PropertyName);
// fieldAlias.Add(ailasName);
//}
//获取字段
var fieldSplitString = "*";//entity.GetSqlQueryFieldsWithAlias();// String.Join(",", fieldAlias);//返回逗号分隔的字符串 例如：ProvinceCode,ProvinceName,Submmary
//解析查询条件
string whereStr = "1=1";
if (null != predicate)
{
whereStr = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, sqlFieldMapping);
}
StringBuilder sb_Sql = new StringBuilder();
sb_Sql.AppendFormat("select {0} ", fieldSplitString);
sb_Sql.AppendFormat(" from {0} ", tableInDbName);
sb_Sql.AppendFormat(" where {0};", whereStr);
var sqlCmd = sb_Sql.ToString();
sb_Sql.Clear();
sb_Sql = null;
List <TElement> dataLst = null;
try
{
this.SqlOutPutToLogAsync(sqlCmd);
using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
{
dataLst = conn.Query <TElement>(sqlCmd).AsList();
}
}
catch (Exception ex)
{
throw ex;
}
return(dataLst);
}
/// <summary>
/// 更新元素 通过 符合条件的
/// （更新机制为，模型载体设置的值的字段会被更新掉，不设置值 不更新）
/// </summary>
/// <param name="entity"></param>
/// <param name="predicate"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public int UpdateByCondition(TElement entity, Expression <Func <TElement, bool> > predicate, IDbTransaction transaction = null)
{
string tableInDbName;
System.Reflection.PropertyInfo[] propertys;
string[] filelds;
string[] paras;
var sqlFieldMapping = ResolveEntity(entity, true, out tableInDbName, out propertys, out filelds, out paras);
if (filelds.Length <= 1)
{
//除主键后 没有其他字段
return(-1);
throw new Exception("未指定除主键后其他字段！");
}
StringBuilder sb_FiledParaPairs = new StringBuilder("");
///解析要更新的列
var settedValueDic = entity.GetSettedValuePropertyDic();
foreach (var item in settedValueDic)
{
var keyProperty = item.Key;
//var value = item.Value;
if (keyProperty != EntityIdentityFiledName)
{
string fieldName = ResolveLambdaTreeToCondition.SearchPropertyMappingField(sqlFieldMapping, keyProperty);
sb_FiledParaPairs.AppendFormat("{1}{0}{1}=@{2},", fieldName, this.FieldWrapperChar, keyProperty);
}
}
//移除最后一个逗号
var str_FiledParaPairs = sb_FiledParaPairs.ToString();
str_FiledParaPairs = str_FiledParaPairs.Remove(str_FiledParaPairs.Length - 1);
StringBuilder sb_Sql = new StringBuilder();
sb_Sql.Append(string.Format("update {0} set ", tableInDbName)); //Set Table
sb_Sql.Append(str_FiledParaPairs); //参数对
if (null != predicate)
{
string where = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, sqlFieldMapping);
sb_Sql.Append(" where "); //解析条件
sb_Sql.Append(where); //条件中带有参数=值的 拼接字符串
}
var sqlCmd = sb_Sql.ToString();
///清理字符串构建
sb_FiledParaPairs.Clear();
sb_FiledParaPairs = null;
sb_Sql.Clear();
sb_Sql = null;
this.SqlOutPutToLogAsync(sqlCmd, entity);
using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
{
var result = conn.Execute(sqlCmd, entity, transaction);
return(result);
}
}
/// <summary>
/// 单次批量多次插入多个实体
/// (注意：sqlbuck插入，高效率sqlbuck方式插入)
/// </summary>
/// <param name="entities"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public bool InsertMulitiEntities(IEnumerable <TElement> entities, IDbTransaction transaction = null)
{
var result = -1;
var count_entities = entities.Count();
if (count_entities <= 0)
{
return(false);
}
string tableInDbName;
System.Reflection.PropertyInfo[] propertys;
string[] filelds;
string[] paras;
ResolveEntity(entities.First(), true, out tableInDbName, out propertys, out filelds, out paras);
try
{
this.SqlOutPutToLogAsync("InsertMulitiEntities", entities);
///不含主键的属性
var noIdentityPropertys = propertys.Remove(x => x.Name.ToLower() == EntityIdentityFiledName.ToLower());
using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
if (null == transaction)
{
transaction = conn.BeginTransaction();
}
using (var bulk = new SqlBulkCopy(conn as SqlConnection, SqlBulkCopyOptions.Default, (SqlTransaction)transaction))
{
bulk.BulkCopyTimeout = 120;//命令超时时间
bulk.BatchSize = 1000;
//指定写入的目标表
bulk.DestinationTableName = tableInDbName;
//数据源中的列名与目标表的属性的映射关系
//bulk.ColumnMappings.Add("ip", "ip");
//bulk.ColumnMappings.Add("port", "port");
//bulk.ColumnMappings.Add("proto_name", "proto_name");
//bulk.ColumnMappings.Add("strategy_id", "strategy_id");
//init mapping
foreach (var pi in noIdentityPropertys)
{
bulk.ColumnMappings.Add(pi.Name, pi.Name);
}
DataTable dt = SqlDataTableExtensions.ConvertListToDataTable <TElement>(entities, ref noIdentityPropertys);//数据源数据
//DbDataReader reader = dt.CreateDataReader();
bulk.WriteToServer(dt);
if (null != transaction)
{
transaction.Commit();
}
}
}
result = 1;
}
catch (Exception ex)
{
if (null != transaction)
{
transaction.Rollback();
}
//抛出Native 异常信息
throw ex;
}
var isSuccess = result > 0 ? true : false;
return(isSuccess);
}
/// <summary>
/// 执行分页查询的核心方法：支持单表和多表分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition">执行条件</param>
/// <returns></returns>
public override PagedSqlDataResult <T> PageQuery <T>(PagedSqlCondition condition)
{
PagedSqlDataResult <T> pageData = null;
if (null == condition)
{
return(pageData);
}
string errMsg = "";
if (!condition.IsValid(out errMsg))
{
throw new Exception("分页查询错误：" + errMsg);
}
//记录打出日志
this.SqlOutPutToLogAsync(Contanst.PageSql_Call_Name, condition);
try
{
if (condition.PageNumber <= 0)
{
condition.PageNumber = 1;
}
//判断结果集类型
if (null == condition.TableOptions)
{
//简单sql语句类型判断
if (condition.TableNameOrSqlCmd.ToLower().Contains("select") && condition.TableNameOrSqlCmd.ToLower().Contains("from"))
{
condition.TableOptions = PageTableOptions.SqlScripts;
}
else
{
condition.TableOptions = PageTableOptions.TableOrView;
}
}
//动态查询需要包装外层
if (condition.TableOptions == PageTableOptions.SqlScripts)
{
//condition.TableNameOrSqlCmd = condition.TableNameOrSqlCmd.Replace("'", "''");
string sqlCmd = condition.TableNameOrSqlCmd.Trim();
if (sqlCmd[sqlCmd.Length - 1] == ';')
{
sqlCmd = sqlCmd.TrimEnd(';');
}
condition.TableNameOrSqlCmd = string.Format(" ( {0} ) as tmpTable ", condition.TableNameOrSqlCmd);
}
//# 分页sql 字符串
string countSql = string.Format("select count(*) as TotalRows, 0 as TotalPages from {0} {1} ;", condition.TableNameOrSqlCmd, condition.ConditionWhere);
string bodySql = string.Concat(
"select "
, condition.SelectFields
, " from "
, condition.TableNameOrSqlCmd
, condition.ConditionWhere
, " order by "
, condition.SortField.IsNullOrEmpty() ? condition.PrimaryKey : condition.SortField
, condition.IsDesc ? " DESC " : " ASC "
, " limit "
, (condition.PageNumber - 1) * condition.PageSize
, ','
, condition.PageSize
);
string pagerSql = string.Concat(countSql, bodySql, ";");
//string
using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
{
//多部分结果
var multiResult = conn.QueryMultiple(pagerSql, condition.SqlParameters);
if (null != multiResult)
{
pageData = multiResult.ReadFirstOrDefault <PagedSqlDataResult <T> >();//分页信息
if (pageData.TotalRows > 0)
{
//计算分页总页数
if (pageData.TotalRows < condition.PageSize)
{
pageData.TotalPages = 1;
}
else
{
if (pageData.TotalRows % condition.PageSize > 0)
{
pageData.TotalPages = pageData.TotalRows / condition.PageSize + 1;
}
else
{
pageData.TotalPages = pageData.TotalRows / condition.PageSize;
}
}
}
if (null != pageData)
{
pageData.DataList = multiResult.Read <T>().AsList();//结果行
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(pageData);
}
/// <summary>
/// Sql 语句批量执行
/// </summary>
/// <param name="SqlCmdList"></param>
/// <returns></returns>
public bool SqlBatchExcute(Dictionary <string, DbParameter[]> sqlCmdList)
{
bool result = false;
if (null == sqlCmdList || sqlCmdList.Count <= 0)
{
return(result);
}
using (DbConnection conn = DatabaseFactory.GetDbConnection(this._dbConfig))
{
conn.Open();
using (DbTransaction trans = conn.BeginTransaction())
{
try
{
var entor = sqlCmdList.GetEnumerator();
StringBuilder sbSqlCmdBagList = new StringBuilder();
var sqlParasBagList = new List <DbParameter>();
int pos = 0;
while (entor.MoveNext() || pos >= sqlCmdList.Count)
{
//到达sql参数上限或者达到命令列表边界 执行sql 包
if (sqlParasBagList.Count >= MAX_SQL_PARAS_LIMIT || pos >= sqlCmdList.Count)
{
using (DbCommand cmd = conn.CreateCommand())
{
//到达最大参数临界的时候 执行sql 批语句
cmd.Transaction = trans;
cmd.CommandText = sbSqlCmdBagList.ToString();
cmd.Parameters.AddRange(sqlParasBagList.ToArray());
cmd.ExecuteNonQuery();
}
//重置
sbSqlCmdBagList.Clear();
sqlParasBagList.Clear();
if (pos >= sqlCmdList.Count)
{
break;//超出临界 跳出循环
}
}
var cmdPair = entor.Current;
if (string.IsNullOrEmpty(cmdPair.Key))
{
continue;
}
string sqlCmd = cmdPair.Key;
DbParameter[] sqlParas = cmdPair.Value;
if (!sqlCmd.TrimEnd().EndsWith(";"))
{
sqlCmd += ";";
}
//处理sql 命令，和参数，对sql命令中的@ 和参数中的@的进行匹配识别
if (null == sqlParas || sqlParas.Length <= 0)
{
sbSqlCmdBagList.Append(sqlCmd);
}
else
{
//按照计数游标 对参数进行处理
foreach (DbParameter itemDbPara in sqlParas)
{
var paraName = itemDbPara.ParameterName;
string nameWithPos = string.Concat(paraName, pos);
itemDbPara.ParameterName = nameWithPos;
sqlCmd = sqlCmd.Replace(paraName, nameWithPos);
}
sbSqlCmdBagList.Append(sqlCmd);//追加格式化 后缀的sql 命令
sqlParasBagList.AddRange(sqlParas);
}
pos += 1;//计数后移
}
trans.Commit();
result = true;
}
catch (Exception ex)
{
result = false;
trans.Rollback();
throw ex;
}
}
}
return(result);
}
/// <summary>
/// 获取此上下文的新的db连接对象
/// </summary>
/// <returns></returns>
public IDbConnection GetCurrentDbConnection()
{
return(DatabaseFactory.GetDbConnection(this._dbConfig));
}