/// <summary>
/// 根据lambda表达式条件删除操作
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="exp"></param>
/// <returns></returns>
protected int DeleteT <T>(Expression <Func <T, bool> > exp) where T : class, new()
{
try
{
EntityInfo entityInfo = Caches.EntityInfoCache.Get(typeof(T));
StringBuilder deleteSql = new StringBuilder();
List <DbParameter> parms = new List <DbParameter>();
deleteSql.AppendFormat(" DELETE FROM {0} WHERE ", entityInfo.TableName);
if (exp != null)
{
SqlVisitor lambdaTranslator = new SqlVisitor(this.DataBase.DBType);
string where = lambdaTranslator.Translate(exp);
deleteSql.Append(where);
foreach (DbParameter parm in lambdaTranslator.Parameters)
{
parms.Add(parm);
}
return(DataBase.ExecuteSql(deleteSql.ToString(), parms.ToArray()));
}
else
{
throw new LambdaLossException("进行Delete操作时，lambda表达式为null");
}
}
catch (Exception ex)
{
throw ex;
}
}
public JoinQuery Where <T1, T2, T3>(Expression <Func <T1, T2, T3, bool> > where) where T1 : class, new() where T2 : class, new() where T3 : class, new()
{
SqlVisitor visitor = new SqlVisitor(DbContext.DataBase.DBType, callIndex++, WithAlias);
visitor.Translate(where);
WhereClause.AppendFormat("{0} AND ", visitor.SqlBuilder.ToString());;
Parameters.AddRange(visitor.Parameters);
return(this);
}
/// <summary>
/// 翻译ExistsSQL
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="selectSql"></param>
/// <param name="parms"></param>
/// <param name="exp"></param>
protected void GetExistsSQLByLambda <T>(StringBuilder selectSql, List <DbParameter> parms, Expression <Func <T, bool> > exp) where T : class, new()
{
EntityInfo entityInfo = Caches.EntityInfoCache.Get(typeof(T));
selectSql.AppendFormat("SELECT COUNT(1) CT FROM {0}", entityInfo.TableName);
if (exp != null)
{
SqlVisitor lambdaTranslator = new SqlVisitor(this.DataBase.DBType);
string where = lambdaTranslator.Translate(exp);
selectSql.AppendFormat(" WHERE {0}", where);
foreach (DbParameter parm in lambdaTranslator.Parameters)
{
parms.Add(parm);
}
}
}
/// <summary>
/// 添加查询
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="select"></param>
/// <returns></returns>
public JoinQuery Fields <TEntity>(Expression <Func <TEntity, dynamic> > select = null)
{
EntityInfo entityInfo = Caches.EntityInfoCache.Get(typeof(TEntity));
if (select == null)
{
SelectFields.AppendFormat("{0},", entityInfo.SelectFields);
}
else
{
SqlVisitor visitor = new SqlVisitor(DbContext.DataBase.DBType, callIndex++, WithAlias);
string fields = visitor.Translate(select);
SelectFields.Append(fields);
Parameters.AddRange(visitor.Parameters);
}
return(this);
}
/// <summary>
/// 根据指定字段更新
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <param name="updateFields"></param>
/// <param name="exp"></param>
/// <returns></returns>
protected int UpdateT <T>(T entity, List <string> updateFields, Expression <Func <T, bool> > exp) where T : class, new()
{
try
{
EntityInfo entityInfo = Caches.EntityInfoCache.Get(typeof(T));
StringBuilder updateSql = new StringBuilder();
List <DbParameter> parms = new List <DbParameter>();
updateSql.AppendFormat(" UPDATE {0} SET ", entityInfo.TableName);
StringBuilder updateValues = new StringBuilder();
StringBuilder whereClause = new StringBuilder();
foreach (PropertyInfo property in entityInfo.ColumnProperties.Where(m => updateFields.Contains(m.Name)))
{
object propertyValue = null;
if ((propertyValue = property.GetValue(entity, null)) != null)
{
updateValues.AppendFormat("{0}={1}{2},", entityInfo.Columns[property.Name], DataBase.ParameterPrefix, property.Name);
parms.Add(DataBase.GetDbParameter(property.Name, propertyValue));
}
else
{
updateValues.Append(entityInfo.Columns[property.Name] + "=null,");
}
}
updateSql.AppendFormat("{0} WHERE ", updateValues.ToString().TrimEnd(','));
if (exp != null)
{
SqlVisitor lambdaTranslator = new SqlVisitor(this.DataBase.DBType);
string where = lambdaTranslator.Translate(exp);
updateSql.Append(where);
foreach (DbParameter parm in lambdaTranslator.Parameters)
{
parms.Add(parm);
}
return(DataBase.ExecuteSql(updateSql.ToString(), parms.ToArray()));
}
else
{
throw new LambdaLossException("进行Update操作时，lambda表达式为null");
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void GetSQLByLambda <TIn, TResult>(StringBuilder selectSql, List <DbParameter> parms, Expression <Func <TIn, bool> > exp, Expression <Func <TIn, TResult> > select, SelectType selectType) where TIn : class, new()
{
EntityInfo entityInfo = Caches.EntityInfoCache.Get(typeof(TIn));
SqlVisitor selectTranslator = new SqlVisitor(this.DataBase.DBType, 0, VisitorType.SelectFields);
string fields = string.Empty;
if (select != null)
{
fields = selectTranslator.Translate(select);
foreach (DbParameter parm in selectTranslator.Parameters)
{
parms.Add(parm);
}
}
switch (selectType)
{
case SelectType.Distinct:
selectSql.AppendFormat("SELECT DISTINCT {0} FROM {1}", fields.TrimEnd(','), entityInfo.TableName);
break;
case SelectType.Max:
selectSql.AppendFormat("SELECT MAX({0}) FROM {1}", fields.TrimEnd(','), entityInfo.TableName);
break;
case SelectType.Min:
selectSql.AppendFormat("SELECT MIN({0}) FROM {1}", fields.TrimEnd(','), entityInfo.TableName);
break;
case SelectType.Count:
selectSql.AppendFormat("SELECT COUNT(1) CT FROM {0}", entityInfo.TableName);
break;
}
if (exp != null)
{
SqlVisitor lambdaTranslator = new SqlVisitor(this.DataBase.DBType, 1);
string where = lambdaTranslator.Translate(exp);
selectSql.AppendFormat(" WHERE {0}", where);
foreach (DbParameter parm in lambdaTranslator.Parameters)
{
parms.Add(parm);
}
}
}
/// <summary>
/// 排序
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="selectSql"></param>
/// <param name="parms"></param>
/// <param name="exp"></param>
/// <param name="orderby"></param>
protected void GetSQLByLambda <T>(StringBuilder selectSql, List <DbParameter> parms, Expression <Func <T, bool> > exp, Expression <Func <IEnumerable <T>, IOrderedEnumerable <T> > > orderby) where T : class, new()
{
EntityInfo entityInfo = Caches.EntityInfoCache.Get(typeof(T));
selectSql.AppendFormat("SELECT {0} FROM {1}", entityInfo.SelectFields, entityInfo.TableName);
if (exp != null)
{
SqlVisitor lambdaTranslator = new SqlVisitor(this.DataBase.DBType);
string where = lambdaTranslator.Translate(exp);
selectSql.AppendFormat(" WHERE {0}", where);
foreach (DbParameter parm in lambdaTranslator.Parameters)
{
parms.Add(parm);
}
}
if (orderby != null)
{
OrderByVisitor <T> orderByVisitor = new OrderByVisitor <T>();
string orderBy = orderByVisitor.Translate(orderby);
selectSql.AppendFormat(" ORDER BY {0}", orderBy);
}
}
/// <summary>
/// 准备查询语句
/// </summary>
/// <param name="selectSql"></param>
/// <param name="parms"></param>
/// <param name="filterItems"></param>
protected void GetSQLByLambda <TIn, TResult>(StringBuilder selectSql, List <DbParameter> parms, Expression <Func <TIn, bool> > exp, Expression <Func <TIn, TResult> > select) where TIn : class, new()
{
EntityInfo entityInfo = Caches.EntityInfoCache.Get(typeof(TIn));
SqlVisitor selectTranslator = new SqlVisitor(this.DataBase.DBType, 0);
string fields = selectTranslator.Translate(select);
foreach (DbParameter parm in selectTranslator.Parameters)
{
parms.Add(parm);
}
selectSql.AppendFormat("SELECT {0} FROM {1}", fields.TrimEnd(','), entityInfo.TableName);
if (exp != null)
{
SqlVisitor lambdaTranslator = new SqlVisitor(this.DataBase.DBType, 1);
string where = lambdaTranslator.Translate(exp);
selectSql.AppendFormat(" WHERE {0}", where);
foreach (DbParameter parm in lambdaTranslator.Parameters)
{
parms.Add(parm);
}
}
}
/// <summary>
/// 内连接 参数决定顺序
/// </summary>
/// <typeparam name="TLeft"></typeparam>
/// <typeparam name="TRight"></typeparam>
/// <param name="on"></param>
/// <returns></returns>
public JoinQuery InnerJoin <TLeft, TRight>(Expression <Func <TLeft, TRight, bool> > on) where TLeft : class, new() where TRight : class, new()
{
JoinRelation link = new JoinRelation {
JoinType = JoinType.Inner
};
SqlVisitor visitor = new SqlVisitor(DbContext.DataBase.DBType, callIndex++, WithAlias);
link.LeftTable = Caches.EntityInfoCache.Get(typeof(TLeft)).TableName;
if (WithAlias)
{
link.LeftTableAlias = on.Parameters[0].Name;
}
link.RightTable = Caches.EntityInfoCache.Get(typeof(TRight)).TableName;
if (WithAlias)
{
link.RightTableAlias = on.Parameters[1].Name;
}
link.OnSql = visitor.Translate(on);
Parameters.AddRange(visitor.Parameters);
JoinRelations.Add(link);
return(this);
}
protected int UpdateT <T>(Expression <Func <T, T> > updateExp, Expression <Func <T, bool> > exp) where T : class, new()
{
try
{
EntityInfo entityInfo = Caches.EntityInfoCache.Get(typeof(T));
StringBuilder updateSql = new StringBuilder();
List <DbParameter> parms = new List <DbParameter>();
updateSql.AppendFormat(" UPDATE {0} SET ", entityInfo.TableName);
SqlVisitor updateVisitor = new SqlVisitor(this.DataBase.DBType, 0, VisitorType.UpdateSet);
string updateSet = updateVisitor.Translate(updateExp);
foreach (DbParameter parm in updateVisitor.Parameters)
{
parms.Add(parm);
}
StringBuilder whereClause = new StringBuilder();
updateSql.AppendFormat("{0} WHERE ", updateSet.TrimEnd(','));
if (exp != null)
{
SqlVisitor lambdaTranslator = new SqlVisitor(this.DataBase.DBType, 1);
string where = lambdaTranslator.Translate(exp);
updateSql.Append(where);
foreach (DbParameter parm in lambdaTranslator.Parameters)
{
parms.Add(parm);
}
return(DataBase.ExecuteSql(updateSql.ToString(), parms.ToArray()));
}
else
{
throw new LambdaLossException("进行Update操作时，lambda表达式为null");
}
}
catch (Exception ex)
{
throw ex;
}
}
