/// <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());
}
