public virtual TranResult CreateSpecTSQL <TEntity>(LambdaExpression exp, List <Type> types = null, string prefix = null)
{
this.Reset();
Type type = typeof(TEntity);
TranResult tr = new TranResult();
tr.TableMapping = type.GetTableMapping();
this.AddTable(tr.TableMapping);
if (types != null)
{
foreach (var t in types)
{
this.AddTable(t.GetTableMapping());
}
}
if (exp != null)
{
if (prefix.IsNotNullAndEmpty())
{
this.Append(prefix + " ");
}
this.Visit(exp);
}
return(this.GetTranResult(tr));
}
public override void VisitTableExpression(TranResult tr, List <JoinTable> join = null)
{
if (join != null && join.Count > 0)
{
this.Append(" FROM ");
for (int i = 0; i < join.Count; i++)
{
this.Append("(");
}
this.Append(tr.TableMapping.Name + " ");
this.Append(" AS " + tr.TableMapping.AliasName + " ");
foreach (var item in join)
{
Type right = item.Expression.Parameters[1].Type;
TableMapping rightMt = right.GetTableMapping();
this.Append(item.JoinType.ToUpper() + " ");
this.Append(rightMt.Name + " ");
this.Append(" AS " + rightMt.AliasName + " ");
this.Append(" ON ");
BinaryExpression be = item.Expression.Body as BinaryExpression;
this.Visit(be.Left);
this.Append(" = ");
this.Visit(be.Right);
this.Append(") ");
}
}
else
{
this.Append(" FROM " + tr.TableMapping.Name + " ");
this.Append(" AS " + tr.TableMapping.AliasName + " ");
}
}
public virtual TranResult CreateSelectPageListTSQL <TEntity>(int pageIndex, int pageSize, LambdaExpression where, LambdaExpression order = null, LambdaExpression select = null, LambdaExpression group = null, List <JoinTable> join = null)
{
this.Reset();
Type type = typeof(TEntity);
TranResult tr = new TranResult();
tr.TableMapping = type.GetTableMapping();
this.AddTable(tr.TableMapping);
this.AddTable(join);
this.Append("SELECT TOP " + pageSize + " ");
this.VisitSelectExpression(select);
this.VisitTableExpression(tr, join);
this.Append(" WHERE ");
this.Append(tr.TableMapping.AliasName + ".");
this.Append(tr.TableMapping.PrimaryKey.Name + " NOT IN(SELECT TOP ");
this.Append(pageIndex * pageSize + " ");
this.Append(tr.TableMapping.AliasName + ".");
this.Append(tr.TableMapping.PrimaryKey.Name + " ");
this.VisitTableExpression(tr, join);
this.VisitWhereExpression(where);
this.VisitGroupByExpression(group);
this.VisitOrderByExpression(order);
this.Append(")");
if (where != null)
{
this.Append(" AND "); this.Visit(where);
}
this.VisitGroupByExpression(group);
this.VisitOrderByExpression(order);
return(this.GetTranResult(tr));
}
public virtual ATSqlCommand AddParameter <T>(T entity, bool isAddPrimaryKey = true)
{
Type type = typeof(T);
TranResult tr = new TranResult();
tr.TableMapping = type.GetTableMapping();
//不获取继承属性 为实例属性 为公开的
PropertyInfo[] pis = type.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public);
foreach (var item in pis)
{
if (isAddPrimaryKey)
{
if (item.IsIgnore() || (!item.PropertyType.IsValueType && item.PropertyType != typeof(string)))
{
continue;
}
}
else
{
if (item.IsIgnore() || (item.Name == tr.TableMapping.PrimaryKey.Name && tr.TableMapping.PrimaryKey.IsIdentity) || (!item.PropertyType.IsValueType && item.PropertyType != typeof(string)))
{
continue;
}
}
this.AddParameter(item.Name, item.GetValue(entity, null));
}
return(this);
}
protected TranResult GetTranResult(TranResult tr)
{
foreach (var table in this.tableNames)
{
this.sb.Replace(table.AliasName, "t" + table.Index);
}
tr.Parameter = this.parameter;
tr.TableNames = this.tableNames;
tr.CmdText = this.sb.ToString();
return(tr);
}
public virtual TranResult CreateDeleteTSQL <TEntity>(LambdaExpression where)
{
this.Reset();
Type type = typeof(TEntity);
TranResult tr = new TranResult();
tr.TableMapping = type.GetTableMapping();
this.AddTable(tr.TableMapping);
this.Append("DELETE ");
this.Append("FROM " + tr.TableMapping.Name + " ");
this.VisitWhereExpression(where);
return(this.GetModifyTranResult(tr));
}
public override int Excute()
{
using (var translator = DMObjectContainer.GetTSQLTranslator())
{
TranResult tr = translator.CreateDeleteTSQL <T1>(where.LambdaExpression);
ATSqlCommand cmd = new ATSqlCommand();
cmd.SetCmdText(tr.CmdText);
cmd.SetParameters(tr.Parameter.ToArray());
cmd.SetTrans(base.trans);
cmd.SetConnectionString(base.ConnectionString);
return(cmd.ExecuteNonQuery());
}
}
public virtual TranResult CreateSelectTSQL <TEntity>(LambdaExpression where, LambdaExpression select = null, List <JoinTable> join = null)
{
this.Reset();
Type type = typeof(TEntity);
TranResult tr = new TranResult();
tr.TableMapping = type.GetTableMapping();
this.AddTable(tr.TableMapping);
this.AddTable(join);
this.Append("SELECT TOP 1 ");
this.VisitSelectExpression(select);
this.VisitTableExpression(tr, join);
this.VisitWhereExpression(where);
return(this.GetTranResult(tr));
}
public override TranResult CreateAggregateTSQL <TEntity>(LambdaExpression where, LambdaExpression select = null, LambdaExpression group = null, List <JoinTable> join = null)
{
this.Reset();
Type type = typeof(TEntity);
TranResult tr = new TranResult();
tr.TableMapping = type.GetTableMapping();
this.AddTable(tr.TableMapping);
this.AddTable(join);
this.Append("SELECT ");
this.VisitSelectExpression(select);
this.VisitTableExpression(tr, join);
this.VisitWhereExpression(where);
this.VisitGroupByExpression(group);
return(this.GetTranResult(tr));
}
public TResult Excute()
{
using (var translator = DMObjectContainer.GetTSQLTranslator())
{
using (TranResult tr = translator.CreateAggregateTSQL <T1>(where.LambdaExpression, select.LambdaExpression, group.LambdaExpression, join.LambdaExpression))
{
using (ATSqlCommand cmd = new ATSqlCommand())
{
cmd.SetCmdText(tr.CmdText);
cmd.SetParameters(tr.Parameter.ToArray());
cmd.SetConnectionString(base.ConnectionString);
return(cmd.ExecuteScalar <TResult>());
}
}
}
}
public virtual TResult Single()
{
using (var translator = DMObjectContainer.GetTSQLTranslator())
{
using (TranResult tr = translator.CreateSelectTSQL <T1>(where.LambdaExpression, select.LambdaExpression, join.LambdaExpression))
{
using (ATSqlCommand cmd = new ATSqlCommand())
{
cmd.SetCmdText(tr.CmdText);
cmd.SetParameters(tr.Parameter.ToArray());
cmd.SetConnectionString(base.ConnectionString);
return(cmd.ToEntity <TResult>());
}
}
}
}
public virtual List <TResult> ToList(int top)
{
using (var translator = DMObjectContainer.GetTSQLTranslator())
{
using (TranResult tr = translator.CreateSelectListTSQL <T1>(top, where.LambdaExpression, order.LambdaExpression, select.LambdaExpression, group.LambdaExpression, join.LambdaExpression))
{
using (ATSqlCommand cmd = new ATSqlCommand())
{
cmd.SetCmdText(tr.CmdText);
cmd.SetParameters(tr.Parameter.ToArray());
cmd.SetConnectionString(base.ConnectionString);
return(cmd.ToList <TResult>());
}
}
}
}
public override TranResult CreateSelectPageListTSQL <TEntity>(int pageIndex, int pageSize, LambdaExpression where, LambdaExpression order = null, LambdaExpression select = null, LambdaExpression group = null, List <JoinTable> join = null)
{
this.Reset();
Type type = typeof(TEntity);
TranResult tr = new TranResult();
tr.TableMapping = type.GetTableMapping();
this.AddTable(tr.TableMapping);
this.AddTable(join);
this.Append("SELECT ");
this.VisitSelectExpression(select);
this.VisitTableExpression(tr, join);
this.VisitWhereExpression(where);
this.VisitGroupByExpression(group);
this.VisitOrderByExpression(order);
this.Append(" LIMIT " + pageIndex * pageSize + "," + pageSize);
return(this.GetTranResult(tr));
}
public virtual PageList <TResult> ToPageList(int pageIndex, int pageSize)
{
PageList <TResult> pl = new PageList <TResult>();
using (var translator = DMObjectContainer.GetTSQLTranslator())
{
Expression <Func <T1, int> > count = (p) => p.DMCount("1");
using (TranResult countTr = translator.CreateAggregateTSQL <T1>(where.LambdaExpression, count, group.LambdaExpression, join.LambdaExpression))
{
using (ATSqlCommand countCmd = new ATSqlCommand())
{
countCmd.SetCmdText(countTr.CmdText);
countCmd.SetParameters(countTr.Parameter.ToArray());
countCmd.SetConnectionString(base.ConnectionString);
if (group.LambdaExpression != null)
{
pl.Total = countCmd.ToDataTable().Rows.Count;
}
else
{
pl.Total = countCmd.ExecuteScalar <int>();
}
pl.Index = pageIndex;
pl.Size = pageSize;
}
}
if (pl.Total != 0)
{
using (TranResult tr = translator.CreateSelectPageListTSQL <T1>(pageIndex, pageSize, where.LambdaExpression, order.LambdaExpression, select.LambdaExpression, group.LambdaExpression, join.LambdaExpression))
{
using (ATSqlCommand cmd = new ATSqlCommand())
{
cmd.SetCmdText(tr.CmdText);
cmd.SetParameters(tr.Parameter.ToArray());
cmd.SetConnectionString(base.ConnectionString);
pl.List = cmd.ToList <TResult>();
}
}
}
return(pl);
}
}
public virtual TranResult CreateSelectListTSQL <TEntity>(int top, LambdaExpression where, LambdaExpression order = null, LambdaExpression select = null, LambdaExpression group = null, List <JoinTable> join = null)
{
this.Reset();
Type type = typeof(TEntity);
TranResult tr = new TranResult();
tr.TableMapping = type.GetTableMapping();
this.AddTable(tr.TableMapping);
this.AddTable(join);
this.Append("SELECT ");
if (top != 0)
{
this.Append("TOP " + top + " ");
}
this.VisitSelectExpression(select);
this.VisitTableExpression(tr, join);
this.VisitWhereExpression(where);
this.VisitGroupByExpression(group);
this.VisitOrderByExpression(order);
return(this.GetTranResult(tr));
}
public override int Excute()
{
switch (DMConfiguration.ProviderType)
{
case EnumProviderType.MySql:
case EnumProviderType.MsSql:
using (var translator = DMObjectContainer.GetTSQLTranslator())
{
int id = 0;
Type type = typeof(T1);
var tm = type.GetTableMapping();
var pk = type.GetProperty(tm.PrimaryKey.Name);
TranResult tr = translator.CreateInsertTSQL <T1>(this.t, select.LambdaExpression);
if (pk != null && tm.PrimaryKey.IsPrimaryKey && tm.PrimaryKey.IsIdentity)
{
tr.CmdText += translator.GetIdentitySQL();
}
ATSqlCommand cmd = new ATSqlCommand();
cmd.SetCmdText(tr.CmdText);
cmd.SetParameters(tr.Parameter.ToArray());
cmd.SetTrans(base.trans);
cmd.SetConnectionString(base.ConnectionString);
id = cmd.ExecuteScalar();
if (pk != null && tm.PrimaryKey.IsPrimaryKey && tm.PrimaryKey.IsIdentity)
{
pk.SetValue(t, id, null);
}
return(id);
}
case EnumProviderType.Access:
return(this.AccessExcute());
default:
throw new NotSupportedException("不支持的数据库类型");
}
}
private int AccessExcute()
{
int id = 0;
Type type = typeof(T1);
var tm = type.GetTableMapping();
var pk = type.GetProperty(tm.PrimaryKey.Name);
using (var translator = DMObjectContainer.GetTSQLTranslator())
{
if (base.trans == null)
{
using (var dmTrans = new DMTransaction(this.ConnectionString))
{
try
{
//这里使用事务主要是为了锁表 目的是为了获取自增ID
TranResult tr = translator.CreateInsertTSQL <T1>(this.t, select.LambdaExpression);
ATSqlCommand cmd = new ATSqlCommand();
cmd.SetCmdText(tr.CmdText);
cmd.SetParameters(tr.Parameter.ToArray());
cmd.SetTrans(dmTrans.BeginTransaction());
cmd.ExecuteScalar();
if (pk != null && tm.PrimaryKey.IsPrimaryKey && tm.PrimaryKey.IsIdentity)
{
var identitySQL = translator.GetIdentitySQL(tm.Name);
ATSqlCommand accessCmd = new ATSqlCommand();
accessCmd.SetCmdText(identitySQL);
accessCmd.SetTrans(dmTrans.BeginTransaction());
id = accessCmd.ExecuteScalar();
pk.SetValue(t, id, null);
}
dmTrans.Commit();
}
catch
{
dmTrans.Rollback();
throw;
}
}
}
else
{
TranResult tr = translator.CreateInsertTSQL <T1>(this.t, select.LambdaExpression);
ATSqlCommand cmd = new ATSqlCommand();
cmd.SetCmdText(tr.CmdText);
cmd.SetParameters(tr.Parameter.ToArray());
cmd.SetTrans(base.trans);
cmd.ExecuteScalar();
if (pk != null && tm.PrimaryKey.IsPrimaryKey && tm.PrimaryKey.IsIdentity)
{
var identitySQL = translator.GetIdentitySQL(tm.Name);
ATSqlCommand accessCmd = new ATSqlCommand();
accessCmd.SetCmdText(identitySQL);
accessCmd.SetTrans(base.trans);
id = accessCmd.ExecuteScalar();
pk.SetValue(t, id, null);
}
}
}
return(id);
}
public virtual TranResult CreateUpdateTSQL <TEntity>(TEntity entity, LambdaExpression where = null, LambdaExpression select = null)
{
this.Reset();
Type type = typeof(TEntity);
TranResult tr = new TranResult();
tr.TableMapping = type.GetTableMapping();
this.AddTable(tr.TableMapping);
this.Append("UPDATE ");
this.Append(tr.TableMapping.Name);
this.Append(" SET ");
Dictionary <string, object> dic = new Dictionary <string, object>();
if (select != null)
{
NewExpression ue = select.Body as NewExpression;
NewArrayExpression ne = ue.Arguments[0] as NewArrayExpression;
foreach (var item in ne.Expressions)
{
string name = string.Empty;
if (item.NodeType == ExpressionType.MemberAccess)
{
MemberExpression me = item as MemberExpression;
name = me.Member.Name;
}
else if (item.NodeType == ExpressionType.Convert)
{
UnaryExpression iue = item as UnaryExpression;
MemberExpression me = iue.Operand as MemberExpression;
name = me.Member.Name;
}
if (string.IsNullOrEmpty(name))
{
continue;
}
PropertyInfo pi = type.GetProperty(name, BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public);
if (pi == null || pi.IsIgnore() || (pi.Name == tr.TableMapping.PrimaryKey.Name && tr.TableMapping.PrimaryKey.IsIdentity) || (!pi.PropertyType.IsValueType && pi.PropertyType != typeof(string)))
{
continue;
}
if (pi.PropertyType.IsEnum)
{
dic.Add(pi.Name, (int)Enum.Parse(pi.PropertyType, pi.GetValue(entity, null).ToString()));
}
else
{
dic.Add(pi.Name, pi.GetValue(entity, null));
}
}
}
else
{
//不获取继承属性 为实例属性 为公开的
PropertyInfo[] pis = type.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public);
foreach (var item in pis)
{
if (item.IsIgnore() || (item.Name == tr.TableMapping.PrimaryKey.Name && tr.TableMapping.PrimaryKey.IsIdentity) || (!item.PropertyType.IsValueType && item.PropertyType != typeof(string)))
{
continue;
}
if (item.PropertyType.IsEnum)
{
dic.Add(item.Name, (int)Enum.Parse(item.PropertyType, item.GetValue(entity, null).ToString()));
}
else
{
dic.Add(item.Name, item.GetValue(entity, null));
}
}
}
int index = 0;
int count = dic.Count;
foreach (var item in dic)
{
this.Append(item.Key);
this.Append(" = ");
this.CreateParameter(item.Value);
if (index++ != count - 1)
{
this.Append(",");
}
}
this.VisitWhereExpression(where);
return(this.GetModifyTranResult(tr));
}
