/// <summary>
/// 生成用于更新的Sql命令
/// </summary>
public static DbCommand CreateUpdateMemberToNullCommand <T>(DbHelper db, GenericWhereEntity <T> whereEntity,
List <string> memberNames)
{
var dbColumnNames = EntityMappingTool.GetDbColumnNames(typeof(T), memberNames);
//生成Sql语句
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("UPDATE {0} SET", whereEntity.TableName);
for (var i = 0; i < dbColumnNames.Count; i++)
{
sqlBuilder.Append((i == 0) ? "" : ",");
sqlBuilder.AppendFormat("{0}.[{1}]=null", whereEntity.TableName, dbColumnNames[i]);
}
//WHERE
var whereSql = CreateWhereSql(whereEntity);
sqlBuilder.Append(" ").Append(whereSql);
//参数
var cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
FillSqlParameters(db, cmd, whereEntity);
return(cmd);
}
/// <summary>
/// 填充Sql参数
/// </summary>
public static void FillSqlParameters <T>(DbHelper db, DbCommand cmd, GenericWhereEntity <T> whereEntity)
{
for (var i = 0; i < whereEntity.WhereParameterNames.Count; i++)
{
db.AddInParameter(cmd, whereEntity.WhereParameterNames[i], whereEntity.WhereParameterTypes[i],
whereEntity.WhereParameterValues[i]);
}
}
private static string GetSubConditions <TA, TB>(GenericWhereEntity <TA> mainEntity,
GenericWhereEntity <TB> joinEntity, Expression joinExpression, string firstParameter)
{
if (joinExpression is MemberExpression)
{
MemberExpression me = (MemberExpression)joinExpression;
if (me.Expression.ToString() == firstParameter)
{
string dbColumnName = EntityMappingTool.GetDbColumnName(mainEntity.EntityType, me.Member.Name);
return(string.Format("{0}.[{1}]", mainEntity.TableName, dbColumnName));
}
else
{
string dbColumnName = EntityMappingTool.GetDbColumnName(joinEntity.EntityType, me.Member.Name);
return(string.Format("{0}.[{1}]", joinEntity.TableName, dbColumnName));
}
}
string opr;
BinaryExpression be = (BinaryExpression)joinExpression;
switch (be.NodeType)
{
case ExpressionType.Equal:
opr = "=";
break;
case ExpressionType.NotEqual:
opr = "<>";
break;
case ExpressionType.GreaterThan:
opr = ">";
break;
case ExpressionType.GreaterThanOrEqual:
opr = ">=";
break;
case ExpressionType.LessThan:
opr = "<";
break;
case ExpressionType.LessThanOrEqual:
opr = "<=";
break;
default:
throw new NotSupportedException("不支持连接条件类型：" + joinExpression.NodeType);
}
string left = GetSubConditions(mainEntity, joinEntity, be.Left, firstParameter);
string right = GetSubConditions(mainEntity, joinEntity, be.Right, firstParameter);
string con = string.Format("({0} {1} {2})", left, opr, right);
return(con);
}
public List<FoodTypeEntity> GetFoodTypeEntityList(FoodTypeEntity condition_entity)
{
GenericWhereEntity<FoodTypeEntity> where_entity = new GenericWhereEntity<FoodTypeEntity>();
if (condition_entity.FOOD_TYPE_ID != null)
where_entity.Where(n => (n.FOOD_TYPE_ID == condition_entity.FOOD_TYPE_ID));
if (condition_entity.FOOD_TYPE_NAME != null)
where_entity.Where(n => (n.FOOD_TYPE_NAME == condition_entity.FOOD_TYPE_NAME));
return EntityExecution.SelectAll(where_entity);
}
public List<EmployeeEntity> GetEmployeeEntityList(EmployeeEntity condition_entity)
{
GenericWhereEntity<EmployeeEntity> where_entity = new GenericWhereEntity<EmployeeEntity>();
if (condition_entity.EMPLOYEE_ID != null)
where_entity.Where(n => (n.EMPLOYEE_ID == condition_entity.EMPLOYEE_ID));
if (!string.IsNullOrEmpty(condition_entity.PASSWORD))
where_entity.Where(n => (n.PASSWORD == condition_entity.PASSWORD));
return EntityExecution.SelectAll(where_entity);
}
public List<FoodEntity> GetFoodEntityList(FoodEntity condition_entity)
{
GenericWhereEntity<FoodEntity> where_entity = new GenericWhereEntity<FoodEntity>();
if (condition_entity.FOOD_ID != null)
where_entity.Where(n => (n.FOOD_ID == condition_entity.FOOD_ID));
if (condition_entity.RESTAURANT_ID != null)
where_entity.Where(n => (n.RESTAURANT_ID == condition_entity.RESTAURANT_ID));
return EntityExecution.SelectAll(where_entity);
}
public List<RestaurantEntity> GetRestaurantEntityList(RestaurantEntity condition_entity)
{
GenericWhereEntity<RestaurantEntity> where_entity = new GenericWhereEntity<RestaurantEntity>();
if (condition_entity.RESTAURANT_ID != null)
where_entity.Where(n => (n.RESTAURANT_ID == condition_entity.RESTAURANT_ID));
if (condition_entity.RESTAURANT_NAME != null)
where_entity.Where(n => (n.RESTAURANT_NAME == condition_entity.RESTAURANT_NAME));
return EntityExecution.SelectAll(where_entity);
}
/// <summary>
/// 创建查询条件
/// </summary>
public static string CreateWhereSql <T>(GenericWhereEntity <T> theWhereEntity)
{
var dbTableName = EntityMappingTool.GetDbTableName(theWhereEntity.EntityType);
if (string.IsNullOrEmpty(dbTableName))
{
throw new EntitySqlException(string.Format("未给类型{0}设置数据表信息!", theWhereEntity.EntityType.FullName));
}
var tsqlBuffer = new StringBuilder(2048);
if (theWhereEntity.DisableTableAlias)
{
tsqlBuffer.Append(" FROM [").Append(dbTableName).Append("]");
}
else
{
tsqlBuffer.Append(" FROM [").Append(dbTableName).Append("] AS ").Append(theWhereEntity.TableName);
}
if (theWhereEntity.WhereExpressions.Count > 0)
{
tsqlBuffer.Append(" WHERE ");
//逐个语句查询，并合并参数
for (var i = 0; i < theWhereEntity.WhereExpressions.Count; i++)
{
var conditionBuilder =
new ConditionBuilderGeneric <T>(
(theWhereEntity.DisableTableAlias ? dbTableName : theWhereEntity.TableName), theWhereEntity);
conditionBuilder.Build(theWhereEntity.WhereExpressions[i]);
if (i > 0)
{
tsqlBuffer.Append(" AND ");
}
tsqlBuffer.Append(conditionBuilder.Condition);
if (conditionBuilder.Arguments != null && conditionBuilder.Arguments.Length > 0)
{
theWhereEntity.WhereParameterValues.AddRange(conditionBuilder.Arguments);
}
if (conditionBuilder.ParameterNames != null && conditionBuilder.ParameterNames.Length > 0)
{
theWhereEntity.WhereParameterNames.AddRange(conditionBuilder.ParameterNames);
}
if (conditionBuilder.DbTypes != null && conditionBuilder.DbTypes.Length > 0)
{
theWhereEntity.WhereParameterTypes.AddRange(conditionBuilder.DbTypes);
}
}
}
return(tsqlBuffer.ToString());
}
/// <summary>
/// 创建用于删除的Sql命令
/// </summary>
public static DbCommand CreatDeleteCommand <T>(DbHelper db, GenericWhereEntity <T> whereEntity)
{
//生成Sql语句
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("DELETE ", EntityMappingTool.GetDbTableName(typeof(T)));
sqlBuilder.Append(CreateWhereSql(whereEntity));
var cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
FillSqlParameters(db, cmd, whereEntity);
return(cmd);
}
public List <EmployeeEntity> GetEmployeeEntityList(EmployeeEntity condition_entity)
{
GenericWhereEntity <EmployeeEntity> where_entity = new GenericWhereEntity <EmployeeEntity>();
if (condition_entity.EMPLOYEE_ID != null)
{
where_entity.Where(n => (n.EMPLOYEE_ID == condition_entity.EMPLOYEE_ID));
}
if (!string.IsNullOrEmpty(condition_entity.PASSWORD))
{
where_entity.Where(n => (n.PASSWORD == condition_entity.PASSWORD));
}
return(EntityExecution.SelectAll(where_entity));
}
public List <RestaurantEntity> GetRestaurantEntityList(RestaurantEntity condition_entity)
{
GenericWhereEntity <RestaurantEntity> where_entity = new GenericWhereEntity <RestaurantEntity>();
if (condition_entity.RESTAURANT_ID != null)
{
where_entity.Where(n => (n.RESTAURANT_ID == condition_entity.RESTAURANT_ID));
}
if (condition_entity.RESTAURANT_NAME != null)
{
where_entity.Where(n => (n.RESTAURANT_NAME == condition_entity.RESTAURANT_NAME));
}
return(EntityExecution.SelectAll(where_entity));
}
public List <FoodEntity> GetFoodEntityList(FoodEntity condition_entity)
{
GenericWhereEntity <FoodEntity> where_entity = new GenericWhereEntity <FoodEntity>();
if (condition_entity.FOOD_ID != null)
{
where_entity.Where(n => (n.FOOD_ID == condition_entity.FOOD_ID));
}
if (condition_entity.RESTAURANT_ID != null)
{
where_entity.Where(n => (n.RESTAURANT_ID == condition_entity.RESTAURANT_ID));
}
return(EntityExecution.SelectAll(where_entity));
}
public List <FoodTypeEntity> GetFoodTypeEntityList(FoodTypeEntity condition_entity)
{
GenericWhereEntity <FoodTypeEntity> where_entity = new GenericWhereEntity <FoodTypeEntity>();
if (condition_entity.FOOD_TYPE_ID != null)
{
where_entity.Where(n => (n.FOOD_TYPE_ID == condition_entity.FOOD_TYPE_ID));
}
if (condition_entity.FOOD_TYPE_NAME != null)
{
where_entity.Where(n => (n.FOOD_TYPE_NAME == condition_entity.FOOD_TYPE_NAME));
}
return(EntityExecution.SelectAll(where_entity));
}
public List<TableEntity> GetTableEntityList(TableEntity condition_entity)
{
try
{
GenericWhereEntity<TableEntity> where_entity = new GenericWhereEntity<TableEntity>();
if (condition_entity.TABLE_ID != null)
where_entity.Where(n => (n.TABLE_ID == condition_entity.TABLE_ID));
if (condition_entity.RESTAURANT_ID != null)
where_entity.Where(n => (n.RESTAURANT_ID == condition_entity.RESTAURANT_ID));
return DianDao.ReadEntityList(where_entity);
}
catch (Exception ex)
{
throw new DianBizException("获取店的数据出错！", ex);
}
}
public List <OrderListEntity> GetOrderListEntityList(OrderListEntity condition_entity)
{
GenericWhereEntity <OrderListEntity> where_entity = new GenericWhereEntity <OrderListEntity>();
if (condition_entity.LIST_ID != null)
{
where_entity.Where(n => (n.LIST_ID == condition_entity.LIST_ID));
}
if (condition_entity.ORDER_ID != null)
{
where_entity.Where(n => (n.ORDER_ID == condition_entity.ORDER_ID));
}
if (condition_entity.FOOD_ID != null)
{
where_entity.Where(n => (n.FOOD_ID == condition_entity.FOOD_ID));
}
return(EntityExecution.SelectAll(where_entity));
}
/// <summary>
/// 创建成员查询的Sql语句
/// </summary>
public static string CreateSelectSql <T>(GenericWhereEntity <T> theWhereEntity, List <string> dbColumnNames = null,
int topCount = 0)
{
var dbTableName = EntityMappingTool.GetDbTableName(theWhereEntity.EntityType);
if (string.IsNullOrEmpty(dbTableName))
{
throw new EntitySqlException(string.Format("未给类型{0}设置数据表信息!", theWhereEntity.EntityType.FullName));
}
if (dbColumnNames == null)
{
dbColumnNames = EntityMappingTool.GetDbColumnNames(typeof(T));
}
var sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT ");
if (topCount > 0)
{
sqlBuilder.AppendFormat("TOP {0} ", topCount);
}
for (var i = 0; i < dbColumnNames.Count; i++)
{
if (i > 0)
{
sqlBuilder.Append(", ");
}
if (theWhereEntity.DisableTableAlias)
{
sqlBuilder.Append(string.Format("{0}.[{1}]", dbTableName, dbColumnNames[i]));
}
else
{
sqlBuilder.Append(string.Format("{0}.[{1}]", theWhereEntity.TableName, dbColumnNames[i]));
}
}
return(sqlBuilder.ToString());
}
/// <summary>
/// 生成用于更新的Sql命令
/// </summary>
public static DbCommand CreateUpdateCommand <T>(DbHelper db, T entity, GenericWhereEntity <T> whereEntity)
{
var entityType = typeof(T);
var notNullEntityFields = EntityInstanceTool.GetNotNullFields(entity);
var notNullDbCloumnNames = EntityMappingTool.GetDbColumnNames(entityType, notNullEntityFields);
var notNullDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType, notNullEntityFields);
var notNullEntityPropertys = EntityInstanceTool.GetNotNullEntityPropertys(entity);
//生成Sql语句
var parameterIndex = new List <string>();
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("UPDATE {0} SET ", whereEntity.TableName);
var firstColumn = true;
for (var i = 0; i < notNullDbCloumnNames.Count; i++)
{
var loopColumn = notNullDbCloumnNames[i];
sqlBuilder.Append(firstColumn ? "" : ",");
firstColumn = false;
sqlBuilder.AppendFormat("{0}.[{1}]=@{1}", whereEntity.TableName, loopColumn);
parameterIndex.Add(loopColumn);
}
//WHERE
var whereSql = CreateWhereSql(whereEntity);
sqlBuilder.Append(" ").Append(whereSql);
//参数
var cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
for (var i = 0; i < notNullDbCloumnNames.Count; i++)
{
db.AddInParameter(cmd, "@" + notNullDbCloumnNames[i], notNullDbColumnTypes[i],
notNullEntityPropertys[i].GetValue(entity, null));
}
FillSqlParameters(db, cmd, whereEntity);
return(cmd);
}
public List <TableEntity> GetTableEntityList(TableEntity condition_entity)
{
try
{
GenericWhereEntity <TableEntity> where_entity = new GenericWhereEntity <TableEntity>();
if (condition_entity.TABLE_ID != null)
{
where_entity.Where(n => (n.TABLE_ID == condition_entity.TABLE_ID));
}
if (condition_entity.RESTAURANT_ID != null)
{
where_entity.Where(n => (n.RESTAURANT_ID == condition_entity.RESTAURANT_ID));
}
return(DianDao.ReadEntityList(where_entity));
}
catch (Exception ex)
{
throw new DianBizException("获取店的数据出错！", ex);
}
}
public List <OrderMainEntity> GetOrderMainEntityList(OrderMainEntity condition_entity)
{
GenericWhereEntity <OrderMainEntity> where_entity = new GenericWhereEntity <OrderMainEntity>();
if (condition_entity.ORDER_ID != null)
{
where_entity.Where(n => (n.ORDER_ID == condition_entity.ORDER_ID));
}
if (condition_entity.RESTAURANT_ID != null)
{
where_entity.Where(n => (n.RESTAURANT_ID == condition_entity.RESTAURANT_ID));
}
if (condition_entity.TABLE_ID != null)
{
where_entity.Where(n => (n.TABLE_ID == condition_entity.TABLE_ID));
}
if (condition_entity.ORDER_FLAG != null)
{
where_entity.Where(n => (n.ORDER_FLAG == condition_entity.ORDER_FLAG));
}
return(EntityExecution.SelectAll(where_entity));
}
/// <summary>
/// 生成用于更新的Sql命令
/// </summary>
public static DbCommand CreateUpdateMemberToNullCommand <T>(DbHelper db, GenericWhereEntity <T> whereEntity,
string memberName)
{
var dbColumnName = EntityMappingTool.GetDbColumnName(typeof(T), memberName);
//生成Sql语句
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("UPDATE {0} SET {0}.[{1}]=null", whereEntity.TableName, dbColumnName);
//WHERE
var whereSql = CreateWhereSql(whereEntity);
sqlBuilder.Append(" ").Append(whereSql);
//参数
var cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
FillSqlParameters(db, cmd, whereEntity);
return(cmd);
}
public List<OrderListEntity> GetOrderListEntityList(OrderListEntity condition_entity)
{
GenericWhereEntity<OrderListEntity> where_entity = new GenericWhereEntity<OrderListEntity>();
if (condition_entity.LIST_ID != null)
where_entity.Where(n => (n.LIST_ID == condition_entity.LIST_ID));
if (condition_entity.ORDER_ID != null)
where_entity.Where(n => (n.ORDER_ID == condition_entity.ORDER_ID));
if (condition_entity.FOOD_ID != null)
where_entity.Where(n => (n.FOOD_ID == condition_entity.FOOD_ID));
return EntityExecution.SelectAll(where_entity);
}
public List<OrderMainEntity> GetOrderMainEntityList(OrderMainEntity condition_entity)
{
GenericWhereEntity<OrderMainEntity> where_entity = new GenericWhereEntity<OrderMainEntity>();
if (condition_entity.ORDER_ID != null)
where_entity.Where(n => (n.ORDER_ID == condition_entity.ORDER_ID));
if (condition_entity.RESTAURANT_ID != null)
where_entity.Where(n => (n.RESTAURANT_ID == condition_entity.RESTAURANT_ID));
if (condition_entity.TABLE_ID != null)
where_entity.Where(n => (n.TABLE_ID == condition_entity.TABLE_ID));
if (condition_entity.ORDER_FLAG != null)
where_entity.Where(n => (n.ORDER_FLAG == condition_entity.ORDER_FLAG));
return EntityExecution.SelectAll(where_entity);
}
/// <summary>
/// 创建成员查询的Sql语句
/// </summary>
public static string CreateSelectSql <T>(GenericWhereEntity <T> theWhereEntity, int topCount)
{
return(CreateSelectSql(theWhereEntity, null, topCount));
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="tableAlias">表的别名</param>
/// <param name="theEntity">实体</param>
public ConditionBuilderGeneric(string tableAlias, GenericWhereEntity <T> theEntity)
{
_TableAlias = tableAlias;
_TheWhereEntity = theEntity;
}
