/// <summary>
/// 增加一组查询参数
/// </summary>
/// <param name="parameters">输入参数</param>
/// <returns></returns>
public QueryContext <T> AddParameter(NpgsqlParameter[] parameters)
{
CheckNotNull.NotEmpty(parameters, nameof(parameters));
ParamList.AddRange(parameters);
return(this);
}
/// <summary>
/// 将当前更改保存到数据库
/// </summary>
/// <returns></returns>
public int SaveChange()
{
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);
}
}
CheckNotNull.NotEmpty(this.WhereList, "The delete operation must specify where conditions!");
this.ToString();
var affrows = 0;
try
{
affrows = base.ExecuteNonQuery(this.CommandText);
}
finally
{
base.ParamList.Clear();
}
return(affrows);
}
/// <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>
/// <param name="parameters">输入参数</param>
/// <returns></returns>
public QueryContext <T> AddParameter(NpgsqlParameter[] parameters)
{
if (parameters == null || parameters.Length == 0)
{
throw new ArgumentException("参数不能为空", "parameters");
}
ParamList.AddRange(parameters);
return(this);
}
public void Deserialize()
{
if (!Param.IsNullOrEmpty())
{
ParamList.AddRange(JsonConvert.DeserializeObject <List <string> >(Param));
}
if (!OtherParam.IsNullOrEmpty())
{
OtherParamList.AddRange(JsonConvert.DeserializeObject <List <object> >(OtherParam));
}
}
public ODAScript Merge(ODAScript Child)
{
if (Child.SqlScript.Length > 0)
{
SqlScript.Append(Child.SqlScript.ToString());
}
if (Child.TableList.Count > 0)
{
TableList.AddRange(Child.TableList.ToArray());
}
if (Child.ParamList.Count > 0)
{
ParamList.AddRange(Child.ParamList.ToArray());
}
return(this);
}
/// <summary>
/// 将当前更改保存到数据库
/// </summary>
/// <returns></returns>
public int SaveChange()
{
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);
}
}
if (this.setList.Count == 0)
{
throw new ArgumentException("fields to be updated must be provided!");
}
if (this.WhereList.Count == 0)
{
throw new ArgumentException("The update operation must specify where conditions!");
}
this.ToString();
int affrows = 0;
if (OnChanged != null)
{
this.CommandText += " RETURNING *;";
var objList = base.ByMaster().ExecuteReader <T>(this.CommandText);
affrows = objList.Count;
if (affrows > 0 && this.OnChanged != null)
{
OnChanged(objList[0]);
}
}
else
{
affrows = base.ExecuteNonQuery(this.CommandText);
}
return(affrows);
}
/// <summary>
/// 将当前更改保存到数据库
/// </summary>
/// <returns></returns>
public int SaveChange()
{
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);
}
}
if (this.WhereList.Count == 0)
{
throw new ArgumentException("The delete operation must specify where conditions!");
}
this.ToString();
return(base.ExecuteNonQuery(this.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);
}