int IExecuteNonQuery.ExecuteNonQuery(DbCommand cmd, ParseSqlContext context)
{
int rst = 0;
if (this.selectNode.Count <= 0)
{
throw new ArgumentException("必须指定要新增赋值的列");
}
List <string> lstcolName = this.selectNode.Select(x => x.name).ToList();
string strCol = string.Join(",", lstcolName);
var lstParameter = this.selectNode.Select(x => context.CreateParameter(x.name, null, false)).ToArray();
var lstParameterName = lstParameter.Select(x => x.ParameterName).ToList();
string strParamter = string.Join(",", lstParameterName);
cmd.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", this.Name, strCol, strParamter);
var lstWrapper = lstParameter.Zip(lstcolName, (parameter, name) => new { parameter, name }).ToList();
foreach (var entity in lstEntity)
{
lstWrapper.ForEach(x => x.parameter.Value = DictMapHandlerInternal[x.name].GetValueFromInstance(entity, null));
cmd.Parameters.Clear();
cmd.Parameters.AddRange(lstParameter);
rst += cmd.ExecuteNonQuery();
}
return(rst);
}
int IExecuteNonQuery.ExecuteNonQuery(System.Data.Common.DbCommand cmd, ParseSqlContext context)
{
context.DbParameters.Clear();
string strwhere = string.Empty;
if (this.whereNode != null)
{
strwhere = " where " + this.whereNode.Parse(context);
}
cmd.CommandText = $"DELETE FROM {this.Name} {strwhere}";
cmd.Parameters.Clear();
cmd.Parameters.AddRange(context.DbParameters.ToArray());
return(cmd.ExecuteNonQuery());
}
protected virtual string Parse(ParseSqlContext context)
{
var lstselect = context.SelectNode.Select(x => x.Parse(context)).ToList();
string selectstr = $"{string.Join(",", lstselect)}";
string fromstr = $"{context.FromTable.Name} as {context.FromTable.NameNick}";
var lstjoin = context.JoinNode.Select(x => x.Parse(context)).ToList();
var joinstr = string.Empty;
if (lstjoin.Count > 0)
{
joinstr = string.Join("\r\n", lstjoin);
}
string wherestr = string.Empty;
if (context.WhereNode != null)
{
wherestr = $"where {context.WhereNode.Parse(context)}";
}
List <string> lstgroupby = context.GroupbyNode.Select(x => x.Parse(context)).ToList();
string groupbystr = string.Empty;
if (lstgroupby.Count > 0)
{
groupbystr = $"group by {string.Join(",", lstgroupby)}";
}
string havingstr = string.Empty;
if (context.Having != null)
{
havingstr = context.Having.Parse(context);
}
var lstorderby = context.OrderbyNode.Select(x => x.Parse(context)).ToList();
string orderbystr = string.Empty;
if (lstorderby.Count > 0)
{
orderbystr = $"order by {string.Join(",", lstorderby)}";
}
if (!context.SkipTake)
{
return($"select {selectstr} \r\n from {fromstr}\r\n{joinstr}\r\n{wherestr}\r\n{groupbystr}\r\n{havingstr}\r\n{orderbystr}");
}
string tmpRowIndex = "_theRowIndex";
string cmdstr = $"select {selectstr},ROW_NUMBER() OVER({orderbystr}) AS {tmpRowIndex} \r\n from {fromstr}\r\n{joinstr}\r\n{wherestr}\r\n{groupbystr}\r\n{havingstr}";
string cmdstrskitak = $"with htt AS(\r\n{cmdstr}),\r\n hbb AS(\r\n select COUNT(0) AS {this.rowCountFiledName} from htt)\r\n select htt.*,hbb.* from htt,hbb WHERE htt.{tmpRowIndex} BETWEEN {context.Skip} AND {context.Skip+context.Take-1}";
return(cmdstrskitak);
}
protected internal override void InitParseSqlContext(ParseSqlContext context, bool initLeftTable = false)
{
this.left.InitParseSqlContext(context, true);
this.right.InitParseSqlContext(context, false);
context.WhereNode = this.AddWhereNode(context.WhereNode, this.whereNode);
context.Having = this.AddWhereNode(context.Having, this.havingNode);
context.JoinNode.Add(new Node.JoinNode(this.right, this.joinwh, JoinOpt.Inner));
context.GroupbyNode.AddRange(this.groupbyNode);
context.SelectNode.AddRange(this.selectNode);
context.OrderbyNode.AddRange(this.orderbyNode);
if (this.skiprows >= 0 && this.takerows > 0)
{
context.Take = this.takerows;
context.Skip = this.skiprows;
context.SkipTake = true;
}
}
int IExecuteNonQuery.ExecuteNonQuery(System.Data.Common.DbCommand cmd, ParseSqlContext context)
{
if (this.whereNodeHandler == null)
{
throw new ArgumentException("必须指定where条件");
}
int rst = 0;
foreach (var value in lstEntity)
{
context.DbParameters.Clear();
string strwhere = this.whereNodeHandler(this, value).Parse(context);
cmd.CommandText = $"DELETE FROM {this.Name} where {strwhere}";
cmd.Parameters.Clear();
cmd.Parameters.AddRange(context.DbParameters.ToArray());
rst += cmd.ExecuteNonQuery();
}
return(rst);
}
int IExecuteNonQuery.ExecuteNonQuery(System.Data.Common.DbCommand cmd, ParseSqlContext context)
{
var lstParameter = this.dictSetColumn.Select(x => context.CreateParameter(x.Key, x.Value, false)).ToArray();
var lstsetcol = this.dictSetColumn.Zip(lstParameter, (x, y) => $"{x.Key}={y.ParameterName}").ToList();
var strset = string.Join(",", lstsetcol);
context.DbParameters.Clear();
string strwhere = string.Empty;
if (this.whereNode != null)
{
strwhere = " where " + this.whereNode.Parse(context);
}
cmd.CommandText = $"update {this.Name} set {strset} {strwhere}";
cmd.Parameters.Clear();
cmd.Parameters.AddRange(lstParameter);
cmd.Parameters.AddRange(context.DbParameters.ToArray());
return(cmd.ExecuteNonQuery());
}
public override string Parse(ParseSqlContext context)
{
string tmp = base.Parse(context);
string rst = string.Empty;
switch (this.fc)
{
case Azeroth.Nalu.SqlFunction.Sum:
rst = string.Format("SUM({0})", tmp);
break;
case Azeroth.Nalu.SqlFunction.Avg:
rst = string.Format("AVG({0})", tmp);
break;
case Azeroth.Nalu.SqlFunction.Count:
rst = string.Format("COUNT({0})", tmp);
break;
case Azeroth.Nalu.SqlFunction.Max:
rst = string.Format("MAX({0})", tmp);
break;
case Azeroth.Nalu.SqlFunction.Min:
rst = string.Format("MIN({0})", tmp);
break;
case Azeroth.Nalu.SqlFunction.Lower:
rst = string.Format("LOWER({0})", tmp);
break;
case Azeroth.Nalu.SqlFunction.Upper:
rst = string.Format("UPPER({0})", tmp);
break;
default:
throw new ArgumentException("未识别的函数");
}
return(rst);
}
protected internal virtual void InitParseSqlContext(ParseSqlContext context, bool initLeftTable = false)
{
this.NameNick = "T" + context.NextTableIndex();
context.Tables.Add(this);
context.WhereNode = this.AddWhereNode(context.WhereNode, this.whereNode);
context.Having = this.AddWhereNode(context.Having, this.havingNode);
if (initLeftTable)
{
context.FromTable = this;
}
context.GroupbyNode.AddRange(this.groupbyNode);
this.selectNode.ForEach(x => x.index = context.NextColIndex());
this.selectNode.ForEach(x => x.nameNick = "c" + x.index.ToString());
context.SelectNode.AddRange(this.selectNode);
context.OrderbyNode.AddRange(this.orderbyNode);
if (this.skiprows >= 0 && this.takerows > 0)
{
context.Take = this.takerows;
context.Skip = this.skiprows;
context.SkipTake = true;
}
}
public virtual int SaveChange(System.Data.IsolationLevel isolationLevel = System.Data.IsolationLevel.Unspecified)
{
using (var cnn = this.CreateConnection())
{
using (var cmd = cnn.CreateCommand())
{
cnn.Open();
var context = new ParseSqlContext(this.GetDbParameterNamePrefix(), cmd.CreateParameter);
cmd.Transaction = cnn.BeginTransaction(isolationLevel);
try
{
int effectrows = 0;
this.executeNonQueryDbSetCollection.ForEach(x => effectrows += x.ExecuteNonQuery(cmd, context));
cmd.Transaction.Commit();
return(effectrows);
}
catch (Exception)
{
cmd.Transaction.Rollback();
throw;
}
}
}
}
public virtual string Parse(ParseSqlContext context)
{
var str = $"{((ITable)this.table).NameNick}.{this.name}";
return(str);
}
public override string Parse(ParseSqlContext context)
{
return(this.userfunction(this, context));
}