/// <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;
ResolveEntity(entity, false, out tableInDbName, out propertys, out filelds, out paras);
if (filelds.Length <= 1)
{
//除主键后 没有其他字段
return(null);
throw new Exception("未指定除主键后其他字段！");
}
//获取字段
string splitor = string.Format("{0},{0}", this.FieldWrapperChar);
var fieldSplitString = string.Concat(this.FieldWrapperChar, string.Join(splitor, filelds), this.FieldWrapperChar);//返回逗号分隔的字符串 例如：`ProvinceCode`,`ProvinceName`
//解析查询条件
string whereStr = "1=1";
if (null != predicate)
{
whereStr = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, wrapperChar: this.FieldWrapperChar);
}
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="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>
/// 分页获取元素集合-ORM
/// 注意：mysql 的存储过程 ，调用，不能输出参数。BUG.即使关闭了datareader 也不能关闭 依然isclosed =false
/// </summary>
/// <param name="pageIndex">页索引</param>
/// <param name="pageSize">页大小</param>
/// <param name="totalRecords">总记录数</param>
/// <param name="totalPages">总页数</param>
/// <param name="predicate">条件</param>
/// <param name="sortField">排序字段</param>
/// <param name="rule">排序规则</param>
/// <returns></returns>
public List <TElement> GetElementsByPagerAndCondition(int pageIndex, int pageSize, out int totalRecords, out int totalPages, Expression <Func <TElement, bool> > predicate, string sortField, OrderRule rule = OrderRule.ASC)
{
List <TElement> dataLst = new List <TElement>();
TElement entity = new TElement();
string tableInDbName;
System.Reflection.PropertyInfo[] propertys;
string[] filelds;
string[] paras;
ResolveEntity(entity, false, out tableInDbName, out propertys, out filelds, out paras);
if (filelds.Length <= 1)
{
totalRecords = -1;
totalPages = -1;
//除主键后 没有其他字段
return(null);
throw new Exception("未指定除主键后其他字段！");
}
//获取字段
string splitor = string.Format("{0},{0}", this.FieldWrapperChar);
var fieldSplitString = string.Concat(this.FieldWrapperChar, string.Join(splitor, filelds), this.FieldWrapperChar);//返回逗号分隔的字符串 例如：`ProvinceCode`,`ProvinceName`
//解析查询条件
var whereStr = "1=1";
if (null != predicate)
{
whereStr = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, wrapperChar: this.FieldWrapperChar);
}
//调用分页存储过程
StringBuilder sb_Sql = new StringBuilder();
sb_Sql.Append(Contanst.PageSql_Call_Name);
var sqlCmd = sb_Sql.ToString();
var sqlParas = new DynamicParameters();
sqlParas.Add("@PageIndex", pageIndex); //页索引
sqlParas.Add("@PageSize", pageSize); //页大小
sqlParas.Add("@TableName", tableInDbName); //表名称
sqlParas.Add("@SelectFields", fieldSplitString); //查询的字段
sqlParas.Add("@PrimaryKey", EntityIdentityFiledName); //查询的表的主键
sqlParas.Add("@ConditionWhere", whereStr); //查询条件
sqlParas.Add("@SortField", sortField); //排序字段
sqlParas.Add("@IsDesc", (int)rule); //倒排序 正排序
sqlParas.Add("@TotalRecords", DbType.Int32, direction: ParameterDirection.Output); //总记录数（可选参数）
sqlParas.Add("@TotalPageCount", DbType.Int32, direction: ParameterDirection.Output); //总页数（输出参数
try
{
this.SqlOutPutToLogAsync(sqlCmd, sqlParas);
using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
{
dataLst = conn.Query <TElement>(sqlCmd, sqlParas, commandType: CommandType.StoredProcedure).AsList();
}
//查询完毕后 根据输出参数 返回总记录数 总页数
totalRecords = sqlParas.Get <int>("@TotalRecords");
totalPages = sqlParas.Get <int>("@TotalPageCount");
}
catch (Exception ex)
{
//抛出Native 异常信息
throw ex;
}
finally
{
//清理字符串构建
sb_Sql.Clear();
sb_Sql = null;
propertys = null;
filelds = null;
paras = null;
}
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;
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)
{
sb_FiledParaPairs.AppendFormat("{1}{0}{1}=@{0},", keyProperty, this.FieldWrapperChar);
}
}
//移除最后一个逗号
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, wrapperChar: this.FieldWrapperChar);
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>
/// 统计 符合条件的行数
/// </summary>
/// <param name="predicate"></param>
/// <returns></returns>
public int Count(Expression<Func<TElement, bool>> predicate)
{
TElement entity = new TElement();
string tableInDbName;
System.Reflection.PropertyInfo[] propertys;
string[] filelds;
string[] paras;
ResolveEntity(entity, false,out tableInDbName, out propertys, out filelds, out paras);
if (filelds.Length <= 1)
{
//除主键后 没有其他字段
throw new Exception("未指定除主键后其他字段！");
}
//解析查询条件
string whereStr = "1=1";
if (null != predicate)
{
whereStr = ResolveLambdaTreeToCondition.ConvertLambdaToCondition<TElement>(predicate, FieldWrapperChar);
}
StringBuilder sb_Sql = new StringBuilder();
sb_Sql.AppendFormat("SELECT COUNT(*) FROM {0} ", tableInDbName);
if (!string.IsNullOrEmpty(whereStr))
{
sb_Sql.AppendFormat("WHERE {0} ", whereStr);
}
var sqlCmd = sb_Sql.ToString();
//清理字符串构建
sb_Sql.Clear();
sb_Sql = null;
DbDataReader reader = null;
try
{
reader = this.ExecuteReader(sqlCmd, null, CommandType.Text);
if (reader.Read())
{
var colValue = reader[0].ToString().ToInt();
return colValue;
}
else
{
throw new Exception("can not excute sql cmd: " + sqlCmd);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (null!=reader)
{
reader.Close();
}
}
}
/// <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="predicate"></param>
/// <param name="specialColumn"></param>
/// <returns></returns>
public decimal Sum(Expression <Func <TElement, bool> > predicate, Fields <TElement> specialColumn)
{
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)
{
//除主键后 没有其他字段
throw new Exception("未指定除主键后其他字段！");
}
//解析查询条件
string whereStr = "1=1";
if (null != predicate)
{
whereStr = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, sqlFieldMapping, FieldWrapperChar);
}
StringBuilder sb_Sql = new StringBuilder();
sb_Sql.AppendFormat("SELECT SUM({2}{0}{2}) FROM {1} ", specialColumn.Container_Fileds.FirstOrDefault(), tableInDbName, FieldWrapperChar);
if (!string.IsNullOrEmpty(whereStr))
{
sb_Sql.AppendFormat("WHERE {0} ", whereStr);
}
var sqlCmd = sb_Sql.ToString();
//清理字符串构建
sb_Sql.Clear();
sb_Sql = null;
DbDataReader reader = null;
try
{
reader = this.ExecuteReader(sqlCmd, null, CommandType.Text);
if (reader.Read())
{
if (reader[0].ToString().IsEmpty())
{
return(0);
}
var colValue = reader.GetFieldValue <decimal>(0);
return(colValue);
}
else
{
throw new Exception("can not excute sql cmd: " + sqlCmd);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (null != reader)
{
reader.Close();
}
}
}