/// <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);
}
private bool CheckWhere(OleDbConnection connection)
{
var reader = ADOSQLHelper.ExecuteReader(connection, string.Format("Select {0},{1},{2} from {3}", CheckFieldName, Key, string.Join(",", WhereFields), TableName));
if (reader != null)
{
var str = string.Empty;
var error = string.Empty;
var array = new string[WhereFields.Length];
while (reader.Read())
{
str = reader[0].ToString();
for (var i = 0; i < WhereFields.Length; i++)
{
array[i] = reader[i + 2].ToString();
}
var key = string.Join(Split, array);
if (WhereList.Contains(key))
{
if (!Values.Contains(str))
{
error = string.Format("{0}对应的‘{1}’不正确", reader[1].ToString(), str);
_questions.Add(new Question {
Code = "3201", Name = Name, Project = CheckProject.值符合性, TableName = TableName, BSM = reader[1].ToString(), Description = error
});
}
}
}
return(true);
}
return(false);
}
public Query Where(string tableName, string columnName, WhereComparision comparison, object value)
{
if (_ListWhere == null) _ListWhere = new WhereList();
_ListWhere.Clear();
_ListWhere.Add(new Where(WhereCondition.AND, tableName, columnName, comparison, value));
return this;
}
public Query Where(string columnName, object value)
{
if (_ListWhere == null) _ListWhere = new WhereList();
_ListWhere.Clear();
_ListWhere.Add(new Where(WhereCondition.AND, columnName, ValueObjectType.ColumnName, WhereComparision.EqualsTo, value, ValueObjectType.Value));
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);
}
public Query Where(Where where, bool clearWhereList)
{
if (_ListWhere == null) _ListWhere = new WhereList();
if (clearWhereList) _ListWhere.Clear();
_ListWhere.Add(where);
return this;
}
//Constructor
//Init' property values
public EntryObject()
{
EntryID = -1;
NameList.Add("_null");
TypeList.Add("_null");
WhereList.Add("_null");
ExtraList.Add("_null");
}
public Delete(Format format, string tableName, string tableAlias = "")
{
this.Query = Enums.SqlQuery.Delete;
this.TableName = tableName;
this.TableAlias = tableAlias;
this.Format = format;
this.Where = new WhereList(this.Format);
}
public Query AddWhere(WhereList whereList)
{
if (_ListWhere == null) _ListWhere = new WhereList();
foreach (Where where in whereList)
{
_ListWhere.Add(where);
}
return this;
}
public Update(Format parameters, string tableName, string tableAlias = "")
{
this.Query = Enums.SqlQuery.Update;
this.Format = parameters;
this.TableName = tableName;
this.TableAlias = tableAlias;
this.Sets = new SetList(this.Format);
this.Where = new WhereList(this.Format);
}
public void AndLess()
{
WhereList w = new WhereList(global::SqlBuilder.Format.MsSQL);
w.Less("a", "b", "c");
string result = w.GetSql();
string sql = "[a]<@a AND [b]<@b AND [c]<@c";
Assert.AreEqual(sql, result);
}
public void RawSimple1()
{
WhereList w = new WhereList(global::SqlBuilder.Format.MsSQL);
w.Raw("[a] IS NULL AND [b]=2 AND [c] NOT LIKE '%text%'");
string result = w.GetSql();
string sql = "[a] IS NULL AND [b]=2 AND [c] NOT LIKE '%text%'";
Assert.AreEqual(sql, result);
}
public void IsNotNullSimple()
{
WhereList w = new WhereList(global::SqlBuilder.Format.MsSQL);
w.IsNotNULL("a", "b", "c");
string result = w.GetSql();
string sql = "[a] IS NOT NULL AND [b] IS NOT NULL AND [c] IS NOT NULL";
Assert.AreEqual(sql, result);
}
public void WhereParenthesis4()
{
WhereList w = new WhereList(global::SqlBuilder.Format.MsSQL);
w.RawParenthesis("[a] IS NULL OR [b] IS NULL");
string result = w.GetSql();
string sql = "([a] IS NULL OR [b] IS NULL)";
Assert.AreEqual(sql, result);
}
public void InSimple3()
{
WhereList w = new WhereList(global::SqlBuilder.Format.MsSQL);
w.In("id_user", "SELECT id FROM tab_users");
string result = w.GetSql();
string sql = "[id_user] IN (SELECT id FROM tab_users)";
Assert.AreEqual(sql, result);
}
public void AndEqualSimple1()
{
WhereList w = new WhereList(global::SqlBuilder.Format.MsSQL);
w.Equal("a", "b", "c");
string result = w.GetSql();
string sql = "[a]=@a AND [b]=@b AND [c]=@c";
Assert.AreEqual(sql, result);
}
/// <summary>
/// 该方法没有对sql注入进行参数化过滤
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public QueryContext <T> Where(string expression)
{
if (string.IsNullOrEmpty(expression))
{
throw new ArgumentNullException("必须传递参数 expression");
}
WhereList.Add(expression);
return(this);
}
public void ComboAndEqualAndNotEqual()
{
WhereList w = new WhereList(global::SqlBuilder.Format.MsSQL);
w.EqualValue("a", "1");
w.NotEqualValue("b", "2");
string result = w.GetSql();
string sql = "[a]=1 AND [b]!=2";
Assert.AreEqual(sql, result);
}
public Select(IFormatter parameters, string tableAlias = "")
{
Query = SqlQuery.Select;
Formatter = parameters;
TableAlias = tableAlias;
Columns = new ColumnsListAggregation(Formatter);
Join = new JoinList(Formatter);
Where = new WhereList(Formatter);
OrderBy = new OrderByList(Formatter);
GroupBy = new GroupByList(Formatter, Columns);
}
public void ComboAndGreaterAndLess()
{
WhereList w = new WhereList(global::SqlBuilder.Format.MsSQL);
w.Greater("a");
w.Less("b");
string result = w.GetSql();
string sql = "[a]>@a AND [b]<@b";
Assert.AreEqual(sql, result);
}
public void RawSimple2()
{
WhereList w = new WhereList(global::SqlBuilder.Format.MsSQL);
w.Raw("[a] NOT LIKE '%text%'");
w.Equal("id");
string result = w.GetSql();
string sql = "[a] NOT LIKE '%text%' AND [id]=@id";
Assert.AreEqual(sql, result);
}
public void WhereParenthesis4()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
WhereList w = new WhereList(SqlBuilder.DefaultFormatter);
w.RawParenthesis("[a] IS NULL OR [b] IS NULL");
string result = w.GetSql();
string sql = "([a] IS NULL OR [b] IS NULL)";
Assert.AreEqual(sql, result);
}
public void IsNotNullSimple()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
WhereList w = new WhereList(SqlBuilder.DefaultFormatter);
w.IsNotNULL("a", "b", "c");
string result = w.GetSql();
string sql = "[a] IS NOT NULL AND [b] IS NOT NULL AND [c] IS NOT NULL";
Assert.AreEqual(sql, result);
}
public void RawSimple1()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
WhereList w = new WhereList(SqlBuilder.DefaultFormatter);
w.Raw("[a] IS NULL AND [b]=2 AND [c] NOT LIKE '%text%'");
string result = w.GetSql();
string sql = "[a] IS NULL AND [b]=2 AND [c] NOT LIKE '%text%'";
Assert.AreEqual(sql, result);
}
public void RawSimple1()
{
SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
var w = new WhereList(SuperSql.DefaultFormatter);
w.Raw("[a] IS NULL AND [b]=2 AND [c] NOT LIKE '%text%'");
var result = w.GetSql();
var sql = "[a] IS NULL AND [b]=2 AND [c] NOT LIKE '%text%'";
Assert.Equal(sql, result);
}
public void AndNotEqualParamValue()
{
WhereList w = new WhereList(global::SqlBuilder.Format.MsSQL);
w.NotEqualValue("a", "1");
w.NotEqualValue("b", "2");
w.NotEqualValue("c", "3");
string result = w.GetSql();
string sql = "[a]!=1 AND [b]!=2 AND [c]!=3";
Assert.AreEqual(sql, result);
}
public void WhereParenthesis4()
{
SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
var w = new WhereList(SuperSql.DefaultFormatter);
w.RawParenthesis("[a] IS NULL OR [b] IS NULL");
var result = w.GetSql();
var sql = "([a] IS NULL OR [b] IS NULL)";
Assert.Equal(sql, result);
}
public void AndEqualSimple2()
{
SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
var w = new WhereList(SuperSql.DefaultFormatter);
w.Equal("a", "b", "c");
var result = w.GetSql("t");
var sql = "[t].[a]=@a AND [t].[b]=@b AND [t].[c]=@c";
Assert.Equal(sql, result);
}
public void AndGreaterParam()
{
WhereList w = new WhereList(global::SqlBuilder.Format.MsSQL);
w.GreaterValue("a", "1");
w.GreaterValue("b", "2");
w.GreaterValue("c", "3");
string result = w.GetSql();
string sql = "[a]>1 AND [b]>2 AND [c]>3";
Assert.AreEqual(sql, result);
}
public void AndEqualSimple1()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
WhereList w = new WhereList(SqlBuilder.DefaultFormatter);
w.Equal("a", "b", "c");
string result = w.GetSql();
string sql = "[a]=@a AND [b]=@b AND [c]=@c";
Assert.AreEqual(sql, result);
}
public void InSimple1()
{
WhereList w = new WhereList(global::SqlBuilder.Format.MsSQL);
w.Equal("a");
w.In("b", "1", "2", "3");
w.Equal("c");
string result = w.GetSql();
string sql = "[a]=@a AND [b] IN (1, 2, 3) AND [c]=@c";
Assert.AreEqual(sql, result);
}
public void AndLessParam()
{
WhereList w = new WhereList(global::SqlBuilder.Format.MsSQL);
w.LessValue("a", "1");
w.LessValue("b", "2");
w.LessValue("c", "3");
string result = w.GetSql();
string sql = "[a]<1 AND [b]<2 AND [c]<3";
Assert.AreEqual(sql, result);
}
public void AndEqualGreater()
{
SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
var w = new WhereList(SuperSql.DefaultFormatter);
w.EqualGreater("a", "b", "c");
var result = w.GetSql();
var sql = "[a]>=@a AND [b]>=@b AND [c]>=@c";
Assert.Equal(sql, result);
}
public void InSimple2()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
WhereList w = new WhereList(SqlBuilder.DefaultFormatter);
w.In("id_user", "SELECT id FROM tab_users");
string result = w.GetSql();
string sql = "[id_user] IN (SELECT id FROM tab_users)";
Assert.AreEqual(sql, result);
}
public Query Having(WhereCondition condition, WhereList whereList)
{
return Having(new Where(condition, whereList));
}
public Where(WhereList whereList)
{
First = whereList;
}
private void BuildJoin(StringBuilder sb, ConnectorBase connection)
{
if (_ListJoin != null)
{
foreach (Join join in _ListJoin)
{
switch (join.JoinType)
{
case JoinType.InnerJoin:
sb.Append(@" INNER JOIN ");
break;
case JoinType.LeftJoin:
sb.Append(@" LEFT JOIN ");
break;
case JoinType.RightJoin:
sb.Append(@" RIGHT JOIN ");
break;
case JoinType.LeftOuterJoin:
sb.Append(@" LEFT OUTER JOIN ");
break;
case JoinType.RightOuterJoin:
sb.Append(@" RIGHT OUTER JOIN ");
break;
case JoinType.FullOuterJoin:
sb.Append(@" FULL OUTER JOIN ");
break;
}
if (join.RightTableSchema != null)
{
if (join.RightTableSchema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(join.RightTableSchema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(join.RightTableSchema.Name));
}
else
{
sb.Append('(');
if (join.RightTableSql is Query)
{
sb.Append(((Query)join.RightTableSql).BuildCommand(connection));
}
else
{
sb.Append(join.RightTableSql.ToString());
}
sb.Append(')');
}
sb.Append(' ');
if (join.RightTableAlias != null)
{
sb.Append(join.RightTableAlias);
}
else
{
sb.Append(join.RightTableSchema != null ? join.RightTableSchema.Name : @"");
}
if (join.Pairs.Count > 1)
{
sb.Append(@" ON ");
WhereList wl = new WhereList();
foreach (WhereList joins in join.Pairs)
{
wl.AddRange(joins);
}
wl.BuildCommand(sb, connection, this, join.RightTableSchema, join.RightTableAlias == null ? join.RightTableSchema.Name : join.RightTableAlias);
}
else if (join.Pairs.Count == 1)
{
sb.Append(@" ON ");
join.Pairs[0].BuildCommand(sb, connection, this, join.RightTableSchema, join.RightTableAlias == null ? join.RightTableSchema.Name : join.RightTableAlias);
}
}
}
}
/// <summary>
/// Adds a where condition. Does not remove any existing conditions.
/// </summary>
/// <param name="where"></param>
/// <returns>Current <typeparamref name="Query"/> object</returns>
public Query Where(Where where)
{
if (_ListWhere == null) _ListWhere = new WhereList();
_ListWhere.Add(where);
return this;
}
public WhereList AND(IPhrase phrase, WhereComparison comparison, object value)
{
var wl = new WhereList();
wl.Add(this);
return wl.AND(phrase, comparison, value);
}
public Query Having(Where where)
{
if (_ListHaving == null) _ListHaving = new WhereList();
_ListHaving.Add(where);
return this;
}
public Query Where(WhereList whereList)
{
return Where(new Where(WhereCondition.AND, whereList));
}
public WhereList OR(IPhrase phrase, WhereComparison comparison, object value, ValueObjectType valueType)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(phrase, comparison, value, valueType);
}
public WhereList OR(IPhrase phrase, WhereComparison comparison, string tableName, string columnName)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(phrase, comparison, tableName, columnName);
}
public WhereList OR(string literalExpression)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(literalExpression);
}
public WhereList OR(IPhrase phrase)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(phrase);
}
public WhereList OR(string columnName, object columnValue)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(columnName, columnValue);
}
public WhereList OR(object thisObject, ValueObjectType thisObjectType, WhereComparison comparison, object thatObject, ValueObjectType thatObjectType)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(thisObject, thisObjectType, comparison, thatObject, thatObjectType);
}
public WhereList AND(string tableName, string columnName, object betweenValue, object andValue)
{
var wl = new WhereList();
wl.Add(this);
return wl.AND(tableName, columnName, betweenValue, andValue);
}
public Query AND(WhereList whereList)
{
return Where(new Where(WhereCondition.AND, whereList), false);
}
public WhereList OR(WhereList whereList)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(whereList);
}
public Query OR(WhereList whereList)
{
return Where(new Where(WhereCondition.OR, whereList), false);
}
public WhereList OR(string tableName, string columnName, WhereComparison comparison, object columnValue)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(tableName, columnName, comparison, columnValue);
}
public Query Having(WhereList whereList)
{
return Having(WhereCondition.AND, whereList);
}
public WhereList OR(string tableName, string columnName, WhereComparison comparison, string otherTableName, string otherColumnName)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(tableName, columnName, comparison, otherTableName, otherColumnName);
}
public Query AddWhere(WhereCondition condition, WhereList whereList)
{
return Where(new Where(condition, whereList), false);
}
public WhereList OR(string columnName, object betweenValue, object andValue)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(columnName, betweenValue, andValue);
}
public Query AddWhere(string tableName, string columnName, WhereComparision comparison, string otherTableName, string otherColumnName)
{
if (_ListWhere == null) _ListWhere = new WhereList();
_ListWhere.Add(new Where(WhereCondition.AND, tableName, columnName, comparison, otherTableName, otherColumnName));
return this;
}
public Where(WhereCondition condition, WhereList whereList)
{
Condition = condition;
First = whereList;
}
