/// <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);
}
//Constructor
//Init' property values
public EntryObject()
{
EntryID = -1;
NameList.Add("_null");
TypeList.Add("_null");
WhereList.Add("_null");
ExtraList.Add("_null");
}
/// <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);
}
/// <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>
/// <param name="formatCommad">格式为{0}{1}的字符串</param>
/// <param name="pValue">{0}{1}对应的值</param>
/// <returns></returns>
public QueryContext <T> Where(string formatCommad, params object[] pValue)
{
if (pValue == null || pValue.Length == 0)
{
throw new ArgumentNullException("必须传递参数 pValue");
}
List <object> nameList = new List <object>();
foreach (var item in pValue)
{
string name = Guid.NewGuid().ToString("N");
this.AddParameter(name, item);
nameList.Add("@" + name);
}
formatCommad = string.Format(formatCommad, nameList.ToArray());
WhereList.Add(formatCommad);
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.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));
}
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 void WhereAdd(params object[] Degerler)
{
WhereList.Add(SqlExperOperatorIslem.WhereAdd(Degerler));
}
public void WhereAdd(CAR003E Property, Operand In_NotIn, params object[] Degerler_Parantez)
{
WhereList.Add(SqlExperOperatorIslem.WhereAdd(Enum.GetName(typeof(CAR003E), Property), In_NotIn, Degerler_Parantez));
}
public void WhereAdd(CAR003E Property, Islem islem, object Deger, Operand And_Or = Operand.AND)
{
WhereList.Add(SqlExperOperatorIslem.WhereAdd(Enum.GetName(typeof(CAR003E), Property), islem, Deger, And_Or));
}
public WhereList OR(string columnName, object betweenValue, object andValue)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(columnName, betweenValue, andValue);
}
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 void WhereAdd(EvrakIniE Property, object Deger, Operand And_Or = Operand.AND)
{
WhereList.Add(SqlExperOperatorIslem.WhereAdd(Enum.GetName(typeof(EvrakIniE), Property), Deger, And_Or));
}
/// <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);
}
public WhereList OR(IPhrase phrase)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(phrase);
}
public void WhereAdd(CAR005E Property, object deger, Operand and_Or = Operand.AND)
{
WhereList.Add(SqlExperOperatorIslem.WhereAdd(Enum.GetName(typeof(CAR005E), Property), deger, and_Or));
}
public WhereList OR(string literalExpression)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(literalExpression);
}
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 WhereList AND(IPhrase phrase, WhereComparison comparison, object value)
{
var wl = new WhereList();
wl.Add(this);
return wl.AND(phrase, comparison, value);
}
public WhereList OR(WhereList whereList)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(whereList);
}
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 WhereList OR(IPhrase phrase, WhereComparison comparison, object value, ValueObjectType valueType)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(phrase, comparison, value, valueType);
}
protected void btnSave_Click(object sender, EventArgs e)
{
if (!Page.IsValid)
{
return;
}
if (cStartDate.SelectedDate != null && cEndDate.SelectedDate != null)
{
WhereList w = new WhereList();
w.Add(new Where(WhereCondition.AND, Campaign.TableSchema.SchemaName, Campaign.Columns.StartDate, WhereComparision.LessThanOrEqual, cStartDate.SelectedDate));
w.Add(new Where(WhereCondition.AND, Campaign.TableSchema.SchemaName, Campaign.Columns.EndDate, WhereComparision.GreaterThanOrEqual, cStartDate.SelectedDate));
if (CampaignId != 0)
{
w.Add(new Where(WhereCondition.AND, Campaign.TableSchema.SchemaName, Campaign.Columns.CampaignId, WhereComparision.NotEqualsTo, CampaignId));
}
WhereList w1 = new WhereList();
w1.Add(new Where(WhereCondition.AND, Campaign.TableSchema.SchemaName, Campaign.Columns.StartDate, WhereComparision.LessThanOrEqual, cEndDate.SelectedDate));
w1.Add(new Where(WhereCondition.AND, Campaign.TableSchema.SchemaName, Campaign.Columns.EndDate, WhereComparision.GreaterThanOrEqual, cEndDate.SelectedDate));
if (CampaignId != 0)
{
w1.Add(new Where(WhereCondition.AND, Campaign.TableSchema.SchemaName, Campaign.Columns.CampaignId, WhereComparision.NotEqualsTo, CampaignId));
}
Query q = new Query(Campaign.TableSchema);
q.OR(w1);
q.OR(w);
if (q.GetCount() > 0)
{
Master.MessageCenter.DisplayErrorMessage(CampaignStrings.GetText(@"ErrorDate"));
return;
}
}
else
{
Master.MessageCenter.DisplayErrorMessage(CampaignStrings.GetText(@"ErrorDate"));
return;
}
Campaign campaign = Campaign.FetchByID(CampaignId);
if (campaign == null)
{
campaign = new Campaign();
}
campaign.IsDiscount = cbIsDiscount.Checked;
campaign.IsGift = cbIsGift.Checked;
campaign.StartDate = cStartDate.SelectedDate;
campaign.EndDate = cEndDate.SelectedDate;
campaign.DestinationCount = txtDestinationCount.Text != "" ? Convert.ToInt32(txtDestinationCount.Text) : 0;
campaign.DestinationSum = txtDestinationSum.Text != "" ? Convert.ToInt32(txtDestinationSum.Text) : 0;
campaign.CampaignName = txtCampaignName.Text;
campaign.Remarks = txtRemarks.Text;
campaign.PrecentDiscount = cbIsDiscount.Checked ? (txtPrecentDiscount.Text != "" ? Convert.ToInt32(txtPrecentDiscount.Text) : 0): 0;
campaign.ImplemationCount = txtImplemationCount.Text != "" ? Convert.ToInt32(txtImplemationCount.Text) : 0;
campaign.Save();
CampaignId = campaign.CampaignId;
if (IsNewMode)
{
string successMessage = CampaignStrings.GetText(@"MessageCampaignCreated");
string url = @"EditCampaign.aspx?CampaignId=" + CampaignId;
url += @"&message-success=" + Server.UrlEncode(successMessage);
Response.Redirect(url, true);
}
else
{
string successMessage = CampaignStrings.GetText(@"MessagCampaignUpdate");
string url = @"EditCampaign.aspx?CampaignId=" + CampaignId;
url += @"&message-success=" + Server.UrlEncode(successMessage);
Response.Redirect(url, true);
}
}