/// <summary> /// 更新单个模型 /// </summary> /// <param name="entity"></param> /// <param name="transaction"></param> /// <returns></returns> public int Update(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); 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); //参数对 sb_Sql.AppendFormat(" where {0}=@{0}", EntityIdentityFiledName);//主键 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 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(); } } }