/// <summary> /// 新增Dto模型 /// </summary> /// <param name="model"></param> /// <returns></returns> public static string InsertDtoModel(this AttrBaseModel model) { StringBuilder sql = new StringBuilder(); StringBuilder values = new StringBuilder(); object[] Mainobj = model.GetType().GetCustomAttributes(typeof(InsertTableAttribute), true); if (Mainobj == null || Mainobj.Length != 1) { throw new AttrSqlException("未定义新增表,请检查Dto特性配置!"); } InsertTableAttribute mainTable = Mainobj[0] as InsertTableAttribute; sql.Append($"INSERT INTO {mainTable.GetInsertTableName()}"); sql.Append("("); foreach (var prop in model.GetType().GetProperties()) { if (prop.IsDefined(typeof(DbFiledMappingAttribute), true)) { DbFiledMappingAttribute dbFiledMappingAttribute = prop.GetCustomAttributes(typeof(DbFiledMappingAttribute), true)[0] as DbFiledMappingAttribute; sql.Append($"`{dbFiledMappingAttribute.GetDbFieldName()}`,"); values.Append($"@{dbFiledMappingAttribute.GetDbFieldName()},"); } } sql.Remove(sql.Length - 1, 1); sql.Append(")"); sql.Append("VALUES"); sql.Append("("); sql.Append($"{values.ToString()}"); sql.Remove(sql.Length - 1, 1); sql.Append(")"); return(sql.ToString()); }
/// <summary> /// 新增Dto模型返回主键 /// </summary> /// <param name="model"></param> /// <returns></returns> public async virtual Task <long> Insert(AttrBaseModel model) { long newIncreaseKet = 0; await this.TryCatch(async() => { string sql = model.InsertDtoModel(); newIncreaseKet = await DbExtend.ExecuteNonQueryByKey(Context, sql, model, Tran); return(sql); }); return(newIncreaseKet); }
/// <summary> /// 获取错误信息 /// </summary> /// <param name="model"></param> /// <param name="num">表示第几个不允许为空的属性</param> /// <returns></returns> internal static string GetErrorMsg(this AttrBaseModel model, int num = 1) { int flag = 1; StringBuilder builder = new StringBuilder(); foreach (var prop in model.GetType().GetProperties()) { if (!prop.IsDefined(typeof(NotAllowRepeatAttribute), true)) { continue; } if (flag == num) { NotAllowRepeatAttribute noRepeat = prop.GetCustomAttributes(typeof(NotAllowRepeatAttribute), true)[0] as NotAllowRepeatAttribute; builder.Append($"{noRepeat.GetMsg()}"); break; } ++flag; } return(builder.ToString()); }
/// <summary> /// 根据主键查询该名称是否重复 /// </summary> /// <param name="model"></param> /// <param name="checkSqltag"></param> /// <returns></returns> internal static string NotAllowKeySql(this AttrBaseModel model, int checkSqltag = 1) { string SoftDeleteField = string.Empty; long SoftDeleteFieldValue = 1; int attrNum = 0; StringBuilder builder = new StringBuilder(); foreach (var prop in model.GetType().GetProperties()) { //对没有标记NotAllowRepeatAttribute特性的字段不操作 if (prop.GetCustomAttributes(typeof(NotAllowRepeatAttribute), true).Length == 0) { continue; } else { //找到要检查的特性 if (++attrNum == checkSqltag) { object[] obj = prop.GetCustomAttributes(typeof(NotAllowRepeatAttribute), true); NotAllowRepeatAttribute table = obj[0] as NotAllowRepeatAttribute; if (string.IsNullOrEmpty(table.GetPrimaryKey())) { throw new AttrSqlException("未设置NotAllowRepeatAttribute特性的表主键字段,请检查特性标记!"); } if (string.IsNullOrEmpty(table.GetTableName())) { throw new AttrSqlException("未设置NotAllowRepeatAttribute特性的表名称,请检查特性标记!"); } var FieldNames = table.GetDbFieldNames(); if (FieldNames == null || FieldNames.Length == 0) { builder.Append($"SELECT {table.GetPrimaryKey()} FROM {table.GetTableName()} "); builder.Append($"WHERE ({table.GetPrimaryKey()}=@{table.GetPrimaryKey()} "); builder.Append($"AND {table.GetDbFieldName()}=@{prop.Name} "); } else { builder.Append($"SELECT {table.GetPrimaryKey()} FROM {table.GetTableName()} "); builder.Append($"WHERE ({table.GetPrimaryKey()}=@{table.GetPrimaryKey()} AND "); for (int i = 0; i < FieldNames.Length; i++) { if (i + 1 != FieldNames.Length) { builder.Append($"{FieldNames[i]} = @{FieldNames[i]} AND "); } else { builder.Append($"{FieldNames[i]} = @{FieldNames[i]} "); } } } //添加软删除字段的 if (string.IsNullOrEmpty(SoftDeleteField) && !string.IsNullOrEmpty(table.GetSoftDeleteFieldName())) { SoftDeleteField = table.GetSoftDeleteFieldName(); SoftDeleteFieldValue = table.GetSoftDeleteFieldValue(); } //补上where条件的括号以及软删除字段 if (builder.ToString().Contains("WHERE")) { builder.Append($" )"); } if (!string.IsNullOrEmpty(SoftDeleteField)) { builder.Append($" AND {SoftDeleteField} = {SoftDeleteFieldValue}"); } break; } } } return(builder.ToString()); }
/// <summary> /// 校验指定字段的值是否重复 /// 直接返回查询的sql语句 /// </summary> /// <param name="model"></param> /// <returns></returns> internal static List <string> NotAllowRepeatSql(this AttrBaseModel model) { string SoftDeleteField = string.Empty; long SoftDeleteFieldValue = 1; List <string> checkSqlCollect = new List <string>(); StringBuilder builder = new StringBuilder(); foreach (var prop in model.GetType().GetProperties()) { //对没有标记NotAllowRepeatAttribute特性的字段不操作 if (prop.GetCustomAttributes(typeof(NotAllowRepeatAttribute), true).Length == 0) { continue; } else { object[] obj = prop.GetCustomAttributes(typeof(NotAllowRepeatAttribute), true); //设定一个字段只允许标记一个该特性,所以不会出现多个 NotAllowRepeatAttribute table = obj[0] as NotAllowRepeatAttribute; if (string.IsNullOrEmpty(table.GetPrimaryKey())) { throw new AttrSqlException("未设置NotAllowRepeatAttribute特性的表主键字段,请检查特性标记!"); } if (string.IsNullOrEmpty(table.GetTableName())) { throw new AttrSqlException("未设置NotAllowRepeatAttribute特性的表名称,请检查特性标记!"); } var FieldNames = table.GetDbFieldNames(); if (FieldNames == null || FieldNames.Length == 0) { builder.Append($"SELECT {table.GetPrimaryKey()} FROM {table.GetTableName()} "); builder.Append($"WHERE ({table.GetDbFieldName()}=@{prop.Name} "); } else { builder.Append($"SELECT {table.GetPrimaryKey()} FROM {table.GetTableName()} "); builder.Append($"WHERE ("); for (int i = 0; i < FieldNames.Length; i++) { if (i + 1 != FieldNames.Length) { builder.Append($"{FieldNames[i]} = @{FieldNames[i]} AND "); } else { builder.Append($"{FieldNames[i]} = @{FieldNames[i]} "); } } } //添加软删除字段的 if (string.IsNullOrEmpty(SoftDeleteField) && !string.IsNullOrEmpty(table.GetSoftDeleteFieldName())) { SoftDeleteField = table.GetSoftDeleteFieldName(); SoftDeleteFieldValue = table.GetSoftDeleteFieldValue(); } //补上where条件的括号以及软删除字段 if (builder.ToString().Contains("WHERE")) { builder.Append($" )"); } if (!string.IsNullOrEmpty(SoftDeleteField)) { builder.Append($" AND {SoftDeleteField} = {SoftDeleteFieldValue}"); } checkSqlCollect.Add(builder.ToString()); SoftDeleteField = string.Empty; builder.Clear(); } } return(checkSqlCollect); }
/// <summary> /// 根据Dto模型特性配置生成sql /// </summary> /// <param name="dto"></param> /// <param name="IngnorIntDefault"></param> /// <returns></returns> public static string UpdateFieldByDtoAttribute <T>(this AttrBaseModel dto, bool IngnorIntDefault = true) where T : AttrBaseModel { StringBuilder UpdateField = new StringBuilder(); StringBuilder WhereField = new StringBuilder(); object[] Mainobj = typeof(T).GetCustomAttributes(typeof(UpdateTableAttribute), true); if (Mainobj == null || Mainobj.Length != 1) { throw new AttrSqlException("未定义更新的表,请检查Dto特性配置!"); } UpdateTableAttribute mainTable = Mainobj[0] as UpdateTableAttribute; UpdateField.Append($"Update {mainTable.GetUpdateTableName()} SET "); WhereField.Append($" Where 1=1 "); foreach (var prop in dto.GetType().GetProperties()) { if (prop.IsDefined(typeof(DbFiledMappingAttribute), true)) { DbFiledMappingAttribute dbFiledMappingAttribute = prop.GetCustomAttributes(typeof(DbFiledMappingAttribute), true)[0] as DbFiledMappingAttribute; bool isnull = false; if (!dbFiledMappingAttribute.GetIsAllowEmpty()) { //判断当前属性是否有值(主要针对string、int和数组类型) object objvalue = prop.GetValue(dto, null); if (objvalue == null) { continue; } if (objvalue is string && string.IsNullOrEmpty((string)objvalue)) { isnull = true; } else if ((objvalue is int && (int)objvalue == 0) || (objvalue is long && (long)objvalue == 0) || (objvalue is byte && (byte)objvalue == 0)) { if (IngnorIntDefault) { isnull = true; } } else if (objvalue is DateTime && (DateTime)objvalue == default(DateTime)) { isnull = true; } else if (objvalue is Array) { Array array = (Array)objvalue; if (array == null || array.Length == 0) { isnull = true; } } } //对有值的进行操作 if (!isnull) { if (dbFiledMappingAttribute.GetIsCondition()) { WhereField.Append($" And {dbFiledMappingAttribute.GetDbFieldName()}=@{prop.Name} "); } else { UpdateField.Append($" {dbFiledMappingAttribute.GetDbFieldName()}=@{prop.Name},"); } } } } if (UpdateField.ToString() == $"Update {mainTable.GetUpdateTableName()} SET ") { throw new AttrSqlException("未定义更新字段,请检查Dto特性配置!"); } else { UpdateField.Remove(UpdateField.Length - 1, 1); } if (WhereField.ToString() != $" Where 1=1 ") { UpdateField.Append(WhereField.ToString()); } return(UpdateField.ToString()); }
/// <summary> /// 根据实体配置为更新条件获取需要更新的字段 /// </summary> /// <param name="entity"></param> /// <param name="IngnorIntDefault"></param> /// <returns></returns> internal static string UpdateFieldByEntityCondition(this AttrBaseModel dto, AttrEntityBase entity, bool IngnorIntDefault = true) { StringBuilder sql = new StringBuilder(); sql.Append($"Update {entity.GetType().Name} SET "); List <string> condition = new List <string>(); foreach (var prop in dto.GetType().GetProperties()) { if (prop.IsDefined(typeof(UpdateConditionFieldAttribute), true)) { condition.Add(prop.Name); } else { //判断实体是否标记了AllowUpdateEmpty属性 //if (prop.IsDefined(typeof(AllowUpdateEmptyAttribute), true)) //{ // sql.Append($"{prop.Name} = @{prop.Name},"); // continue; //} bool isnull = false; //判断当前属性是否有值(主要针对string、int和数组类型) object objvalue = prop.GetValue(dto, null); if (objvalue == null) { continue; } if (objvalue is string && string.IsNullOrEmpty((string)objvalue)) { isnull = true; } else if ((objvalue is int && (int)objvalue == 0) || (objvalue is long && (long)objvalue == 0) || (objvalue is byte && (byte)objvalue == 0)) { if (IngnorIntDefault) { isnull = true; } } else if (objvalue is DateTime && (DateTime)objvalue == default(DateTime)) { isnull = true; } else if (objvalue is Array) { Array array = (Array)objvalue; if (array == null || array.Length == 0) { isnull = true; } } //对有值的进行操作 if (!isnull) { sql.Append($"{prop.Name} = @{prop.Name},"); } } } if (sql.ToString() == $"Update {entity.GetType().Name} SET ") { return(string.Empty); } sql = sql.Remove(sql.Length - 1, 1); if (condition.Count == 0) { throw new AttrSqlException($"未找到{entity.GetType().Name}表的更新条件,更新失败!"); } sql.Append(" WHERE "); for (var i = 0; i < condition.Count; i++) { sql.Append($"{condition[i]} = @{condition[i]}"); if (i + 1 < condition.Count) { sql.Append(" AND "); } } return(sql.ToString()); }