/// <summary>
/// 将当前更改保存到数据库
/// </summary>
/// <returns></returns>
public int SaveChange()
{
string tableName = MyStagingUtils.GetMapping(typeof(T));
if (WhereExpressionList.Count > 0)
{
foreach (var item in WhereExpressionList)
{
DbExpressionVisitor expression = new DbExpressionVisitor();
expression.Visit(item.Body);
WhereList.Add(expression.SqlText.Builder.ToString().ToLower());
ParamList.AddRange(expression.SqlText.Parameters);
}
}
string cmdText = $"UPDATE {tableName} SET {string.Join(",", this.setList)} {"WHERE " + string.Join("\nAND ", WhereList)}";
int affrows = 0;
if (OnChanged != null)
{
cmdText += " RETURNING *;";
var objList = base.ByMaster().ExecuteReader <T>(cmdText);
affrows = objList.Count;
if (affrows > 0 && this.OnChanged != null)
{
OnChanged(objList[0]);
}
}
else
{
affrows = base.ExecuteNonQuery(cmdText);
}
return(affrows);
}
/// <summary>
/// 重写方法
/// </summary>
/// <returns></returns>
public override string ToString()
{
string tableName = MyStagingUtils.GetMapping(typeof(T));
this.CommandText = $"UPDATE {tableName} a SET {string.Join(",", this.setList)} {"WHERE " + string.Join("\nAND ", WhereList)}";
return(this.CommandText);
}
/// <summary>
/// 重写方法
/// </summary>
/// <returns></returns>
public override string ToString()
{
string tableName = MyStagingUtils.GetMapping(typeof(T));
this.CommandText = $"DELETE FROM {tableName} a {"WHERE " + string.Join("\nAND ", WhereList)}";
return(this.CommandText);
}
/// <summary>
/// 重写方法
/// </summary>
/// <returns></returns>
public override string ToString()
{
if (this.models.Count == 0)
{
throw new ArgumentOutOfRangeException("No items.");
}
base.ParamList.Clear();
string tableName = MyStagingUtils.GetMapping(typeof(T));
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append($"INSERT INTO {tableName}");
string fieldsName = string.Empty;
var fieldCollection = this.schema.SchemaSet;
foreach (var key in fieldCollection.Keys)
{
fieldsName += "\"" + key + "\",";
}
fieldsName = fieldsName.Remove(fieldsName.Length - 1, 1);
sqlBuilder.Append($"({fieldsName}) VALUES ");
for (int i = 0; i < models.Count; i++)
{
var mObj = this.models[i];
string piNames = string.Empty;
for (int j = 0; j < this.schema.Properties.Count; j++)
{
PropertyInfo pi = this.schema.Properties[j];
var key = pi.Name.ToLower();
if (fieldCollection.ContainsKey(key))
{
var piName = $"@{key}_{i}";
piNames += piName + ",";
var sm = fieldCollection[key];
base.AddParameter(piName, sm.DbType, pi.GetValue(mObj), sm.Size, sm.SpecificType);
}
}
piNames = piNames.Remove(piNames.Length - 1, 1);
sqlBuilder.Append($"({piNames}),");
}
sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
base.CommandText = sqlBuilder.ToString();
return(CommandText);
}
/// <summary>
/// 将当前更改保存到数据库
/// </summary>
/// <returns></returns>
public int SaveChange()
{
string tableName = MyStagingUtils.GetMapping(typeof(T));
if (WhereExpressionList.Count > 0)
{
foreach (var item in WhereExpressionList)
{
//PgSqlExpression expression = new PgSqlExpression();
//expression.ExpressionCapture(item.Body);
//WhereList.Add(expression.CommandText.ToString().ToLower());
//ParamList.AddRange(expression.Parameters);
DbExpressionVisitor expression = new DbExpressionVisitor();
expression.Visit(item.Body);
WhereList.Add(expression.SqlText.Builder.ToString().ToLower());
ParamList.AddRange(expression.SqlText.Parameters);
}
}
string cmdText = $"DELETE FROM {tableName} {"WHERE " + string.Join("\nAND ", WhereList)}";
return(base.ExecuteNonQuery(cmdText));
}
/// <summary>
/// 将查询命令和条件转换为 SQL 语句
/// </summary>
/// <returns></returns>
public override string ToString()
{
Type mastertype = typeof(T);
string tableName = MyStagingUtils.GetMapping(mastertype);
// master table
StringBuilder sqlText = new StringBuilder();
sqlText.AppendLine($"SELECT {string.Join(",", Fields)} FROM {tableName} {masterAlisName}");
// union
int _index = 2;
foreach (var item in UnionList)
{
DbExpressionVisitor expression = new DbExpressionVisitor
{
TypeMaster = item.MasterType,
AliasMaster = item.AlisName,
AliasUnion = item.UnionAlisName
};
expression.Visit(item.Body);
string unionTableName = MyStagingUtils.GetMapping(item.Model);
sqlText.AppendLine(item.UnionType.ToString().Replace("_", " ") + " " + unionTableName + " " + expression.AliasUnion + " ON " + expression.SqlText.Builder.ToString());
ParamList.AddRange(expression.SqlText.Parameters);
_index++;
}
// condition
if (WhereExpressionList.Count > 0)
{
foreach (var item in WhereExpressionList)
{
DbExpressionVisitor expression = new DbExpressionVisitor();
if (UnionList.Count == 0)
{
expression.TypeMaster = item.Model;
expression.AliasMaster = masterAlisName;
}
else
{
ExpressionUnionModel union = null;
if (item.UnionAlisName == null)
{
union = UnionList.FirstOrDefault(f => f.Model == item.Model);
}
else
{
union = UnionList.FirstOrDefault(f => f.Model == item.Model && f.UnionAlisName == item.UnionAlisName);
}
if (union == null && typeof(T) == item.Model)
{
expression.TypeMaster = item.Model;
expression.AliasMaster = masterAlisName;
}
else if (union != null)
{
expression.AliasMaster = union.AlisName;
expression.AliasUnion = union.UnionAlisName;
}
else
{
throw new NotSupportedException($"找不到 where {item.Body.ToString()}条件的表，不支持的表查询条件");
}
}
expression.Visit(item.Body);
WhereList.Add(expression.SqlText.Builder.ToString().ToLower());
ParamList.AddRange(expression.SqlText.Parameters);
}
}
if (WhereList.Count > 0)
{
sqlText.AppendLine("WHERE " + string.Join("\nAND ", WhereList));
}
if (!string.IsNullOrEmpty(GroupByText))
{
sqlText.AppendLine(GroupByText);
}
if (!string.IsNullOrEmpty(GroupByText) && !string.IsNullOrEmpty(HavingText))
{
sqlText.AppendLine(HavingText);
}
if (!string.IsNullOrEmpty(OrderByText))
{
sqlText.AppendLine(OrderByText);
}
if (!string.IsNullOrEmpty(LimitText))
{
sqlText.AppendLine(LimitText);
}
this.commandtext = sqlText.ToString();
return(this.commandtext);
}
/// <summary>
/// 重写方法
/// </summary>
/// <returns></returns>
public override string ToString()
{
if (this.models.Count == 0)
{
throw new ArgumentOutOfRangeException("No items.");
}
base.ParamList.Clear();
string tableName = MyStagingUtils.GetMapping(typeof(T));
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append($"INSERT INTO {tableName}");
string fieldsName = string.Empty;
var fieldCollection = this.schema.SchemaSet;
foreach (var field in fieldCollection)
{
fieldsName += "\"" + field.FieldName + "\",";
}
fieldsName = fieldsName.Remove(fieldsName.Length - 1, 1);
sqlBuilder.Append($"({fieldsName}) VALUES ");
for (int i = 0; i < models.Count; i++)
{
var mObj = this.models[i];
string piNames = string.Empty;
foreach (var field in fieldCollection)
{
PropertyInfo pi = this.schema.Properties.Where(f => f.Name.ToLower() == field.FieldName.ToLower()).FirstOrDefault();
if (pi == null)
{
throw new KeyNotFoundException($"数据库字段名称：{field.FieldName} 在实体对象 {mObj.GetType().Name} 上未找到对应属性！");
}
var piName = $"@{field.FieldName}_{i}";
piNames += piName + ",";
var value = pi.GetValue(mObj);
if (field.Primarykey || defaultValueField.ContainsKey(field.FieldName))
{
if (value == null ||
value.Equals(Guid.Empty) ||
zeroTime.Equals(value))
{
value = CreateDefaultValue(field);
}
}
base.AddParameter(piName, field.DbType, value, field.Size);
}
piNames = piNames.Remove(piNames.Length - 1, 1);
sqlBuilder.Append($"({piNames}),");
}
sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
base.CommandText = sqlBuilder.ToString();
return(CommandText);
}
