public string ToSQL()
{
if (this.models.Count == 0)
{
throw new ArgumentOutOfRangeException("No items.");
}
Parameters.Clear();
string tableName = MyStagingUtils.GetMapping(typeof(T), ProviderType.MySql);
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append($"INSERT INTO {tableName}");
string fieldsName = string.Empty;
var properties = MyStagingUtils.GetDbFields(typeof(T));
foreach (var p in properties)
{
fieldsName += $"`{p.Name}`,";
}
fieldsName = fieldsName.Remove(fieldsName.Length - 1, 1);
sqlBuilder.Append($"({fieldsName}) VALUES ");
for (int i = 0; i < models.Count; i++)
{
string valueString = string.Empty;
foreach (var pi in properties)
{
var paramName = $"@{pi.Name}_{i}";
var value = pi.GetValue(models[i]);
var pk = pi.GetCustomAttribute <PrimaryKeyAttribute>();
var hasPK = pk != null;
if (hasPK && pk.AutoIncrement)
{
valueString += "default,";
}
else
{
valueString += paramName + ",";
if (hasPK || defaultValueField.ContainsKey(pi.Name.ToLower()))
{
if (value == null || value.Equals(Guid.Empty) || zeroTime.Equals(value))
{
value = GetDefaultValue(pi);
}
}
Parameters.Add(new MySqlParameter(paramName, value));
}
}
valueString = valueString.Remove(valueString.Length - 1, 1);
sqlBuilder.Append($"({valueString}),");
}
sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
sqlBuilder.Append(";");
CommandText = sqlBuilder.ToString();
return(CommandText);
}
/// <summary>
/// 将当前更改保存到数据库
/// </summary>
/// <returns></returns>
public T SaveChange()
{
DeExpression();
CheckNotNull.NotEmpty(setList, "Fields to be updated must be provided!");
CheckNotNull.NotEmpty(WhereConditions, "The update operation must specify where conditions!");
this.ToSQL();
this.CommandText += " RETURNING *;";
var properties = MyStagingUtils.GetDbFields(typeof(T));
using var reader = dbContext.ByMaster().Execute.ExecuteDataReader(CommandType.Text, CommandText, this.Parameters.ToArray());
try
{
reader.Read();
T obj = (T)Activator.CreateInstance(typeof(T));
foreach (var pi in properties)
{
var value = reader[pi.Name];
if (value != DBNull.Value)
{
pi.SetValue(obj, value);
}
}
return(obj);
}
finally
{
Clear();
}
}
public T Add(T model)
{
this.models.Add(model);
this.ToSQL();
CommandText += " RETURNING *;";
var properties = MyStagingUtils.GetDbFields(typeof(T));
using var reader = dbContext.ByMaster().Execute.ExecuteDataReader(CommandType.Text, CommandText, this.Parameters.ToArray());
try
{
reader.Read();
T obj = (T)Activator.CreateInstance(typeof(T));
foreach (var pi in properties)
{
var value = reader[pi.Name];
if (value != DBNull.Value)
{
pi.SetValue(obj, value);
}
}
return(obj);
}
finally
{
this.Clear();
}
}
private void SerializeField(TableInfo table, Type type)
{
var properties = MyStagingUtils.GetDbFields(type);
foreach (var pi in properties)
{
var fi = new DbFieldInfo();
fi.Name = pi.Name;
var customAttributes = pi.GetCustomAttributes();
var genericAttrs = customAttributes.Select(f => f.GetType()).ToArray();
var pk = pi.GetCustomAttribute <PrimaryKeyAttribute>();
fi.PrimaryKey = pk != null;
if (fi.PrimaryKey)
{
fi.AutoIncrement = pk.AutoIncrement;
}
if (pi.PropertyType.Name == "Nullable`1")
{
fi.NotNull = false;
fi.CsType = pi.PropertyType.GenericTypeArguments[0].Name;
}
else
{
fi.CsType = pi.PropertyType.Name;
if (pi.PropertyType == typeof(string))
{
fi.NotNull = fi.PrimaryKey || genericAttrs.Where(f => f == typeof(RequiredAttribute)).FirstOrDefault() != null;
}
else
{
fi.NotNull = pi.PropertyType.IsValueType;
}
}
var columnAttribute = customAttributes.Where(f => f.GetType() == typeof(ColumnAttribute)).FirstOrDefault();
if (columnAttribute != null)
{
var colAttribute = ((ColumnAttribute)columnAttribute);
fi.DbType = fi.DbTypeFull = colAttribute.TypeName;
}
else
{
fi.DbType = PgsqlType.GetDbType(fi.CsType.Replace("[]", ""));
fi.DbTypeFull = GetFullDbType(fi);
}
fi.IsArray = fi.CsType.Contains("[]");
table.Fields.Add(fi);
}
}
public T Add(T model)
{
this.models.Add(model);
this.ToSQL();
var properties = MyStagingUtils.GetDbFields(typeof(T));
// 检查自增
PropertyInfo autoIncrement = null;
foreach (var pi in properties)
{
var pk = pi.GetCustomAttribute <PrimaryKeyAttribute>();
if (pk != null && pk.AutoIncrement)
{
autoIncrement = pi;
break;
}
}
try
{
if (autoIncrement != null)
{
this.CommandText += "\n SELECT LAST_INSERT_ID();";
}
using var reader = dbContext.ByMaster().Execute.ExecuteDataReader(CommandType.Text, CommandText, this.Parameters.ToArray());
if (autoIncrement != null)
{
reader.Read();
var value = reader[0];
value = Convert.ChangeType(value, autoIncrement.PropertyType);
autoIncrement.SetValue(model, value);
}
return(model);
}
finally
{
this.Clear();
}
}
private void SerializeField(TableInfo table, Type type)
{
var properties = MyStagingUtils.GetDbFields(type);
foreach (var pi in properties)
{
var fi = new DbFieldInfo();
fi.Name = pi.Name;
var customAttributes = pi.GetCustomAttributes();
var genericAttrs = customAttributes.Select(f => f.GetType()).ToArray();
if (pi.PropertyType.Name == "Nullable`1")
{
fi.NotNull = false;
fi.CsType = pi.PropertyType.GenericTypeArguments[0].Name;
}
else
{
fi.CsType = pi.PropertyType.Name;
if (pi.PropertyType == typeof(string))
{
fi.NotNull = genericAttrs.Where(f => f == typeof(RequiredAttribute) || f == typeof(PrimaryKeyAttribute)).FirstOrDefault() != null;
}
else
{
fi.NotNull = pi.PropertyType.IsValueType;
}
}
fi.PrimaryKey = genericAttrs.Where(f => f == typeof(PrimaryKeyAttribute)).FirstOrDefault() != null;
if (fi.PrimaryKey)
{
var pk = pi.GetCustomAttribute <PrimaryKeyAttribute>();
fi.AutoIncrement = pk.AutoIncrement;
}
var columnAttribute = customAttributes.Where(f => f.GetType() == typeof(ColumnAttribute)).FirstOrDefault();
if (columnAttribute != null)
{
var colAttribute = ((ColumnAttribute)columnAttribute);
fi.DbType = fi.DbTypeFull = colAttribute.TypeName;
if (colAttribute.TypeName != "char(36)" && colAttribute.TypeName != "tinyint(1)")
{
var zero = colAttribute.TypeName.IndexOf("(");
if (zero > 0)
{
fi.DbType = colAttribute.TypeName.Substring(0, zero);
}
}
}
else
{
fi.DbTypeFull = GetFullDbType(fi);
fi.DbType = MysqlType.GetDbType(fi.CsType);
if (fi.DbType == "varchar" || fi.DbType == "char")
{
fi.DbTypeFull = $"{fi.DbType}(255)";
}
}
table.Fields.Add(fi);
}
}
public void GetDbFields()
{
var pis = MyStagingUtils.GetDbFields(typeof(UserModel));
Assert.Equal(10, pis.Count);
}
