/// <summary>
/// 生成用于更新的Sql命令
/// </summary>
public static DbCommand CreateUpdateMemberToNullCommand <T>(Database db, GenericWhereEntity <T> whereEntity,
List <string> memberNames)
{
var dbColumnNames = EntityMappingTool.GetDbColumnNames(typeof(T), memberNames);
//生成Sql语句
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("UPDATE {0} SET", whereEntity.TableName);
for (int i = 0; i < dbColumnNames.Count; i++)
{
sqlBuilder.Append((i == 0) ? "" : ",");
sqlBuilder.AppendFormat("{0}.[{1}]=null", whereEntity.TableName, dbColumnNames[i]);
}
//WHERE
string whereSql = SqlCreator.CreateWhereSql(whereEntity);
sqlBuilder.Append(" ").Append(whereSql);
//参数
DbCommand cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
FillSqlParameters(db, cmd, whereEntity);
return(cmd);
}
/// <summary>
/// 从DataReader实例化一个Entity对
/// </summary>
/// <typeparam name="TA">实体类的类型TA</typeparam>
/// <typeparam name="TB">实体类的类型TB</typeparam>
/// <param name="reader">DataReader</param>
/// <param name="entityPropertysA">已经排好序的实体类的属性集合TA</param>
/// <param name="entityPropertysB">已经排好序的实体类的属性集合TB</param>
/// <returns></returns>
public static GenericPairEntity <TA, TB> FillOnePairEntity <TA, TB>(IDataReader reader)
where TA : class, new()
where TB : class, new()
{
List <PropertyInfo> entityPropertyInfoA = EntityMappingTool.GetEntityPropertyInfos(typeof(TA));
List <PropertyInfo> entityPropertyInfoB = EntityMappingTool.GetEntityPropertyInfos(typeof(TB));
GenericPairEntity <TA, TB> pair = new GenericPairEntity <TA, TB>();
pair.EntityA = new TA();
pair.EntityB = new TB();
int offset = 0;
for (int i = 0; i < entityPropertyInfoA.Count; i++)
{
if (reader.IsDBNull(offset + i))
{
continue;
}
entityPropertyInfoA[i].SetValue(pair.EntityA, reader.GetValue(offset + i), null);
}
offset = entityPropertyInfoA.Count;
for (int i = 0; i < entityPropertyInfoB.Count; i++)
{
if (reader.IsDBNull(offset + i))
{
continue;
}
entityPropertyInfoB[i].SetValue(pair.EntityB, reader.GetValue(offset + i), null);
}
return(pair);
}
/// <summary>
/// 判断Entity实例中有没有设置主键值
/// </summary>
public static bool HasPrimaryKeyValue <T>(T entity)
{
if (!EntityMappingTool.HasPrimaryKey(typeof(T)))
{
return(false);
}
List <string> notNullEntityFields = GetNotNullFields(entity);
if (notNullEntityFields == null)
{
return(false);
}
var primaryKeyEntityFieldNames = EntityMappingTool.GetPrimaryKeyOfEntityField(typeof(T));
for (int i = 0; i < primaryKeyEntityFieldNames.Count; i++)
{
if (!notNullEntityFields.Any(n => n == primaryKeyEntityFieldNames[i]))
{
return(false);
}
}
return(true);
}
/// <summary>
/// 创建用于删除的Sql命令
/// </summary>
public static DbCommand CreatDeleteCommand <T>(Database db, T entity)
{
Type entityType = typeof(T);
List <string> primaryKeyEntityFieldNames = EntityMappingTool.GetPrimaryKeyOfEntityField(entityType);
List <string> primaryKeyDbCloumnNames = EntityMappingTool.GetDbColumnNames(entityType,
primaryKeyEntityFieldNames);
List <DbType> primaryKeyDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType,
primaryKeyEntityFieldNames);
List <PropertyInfo> primaryKeyPropertyInfos = EntityMappingTool.GetEntityPropertyInfos(entityType,
primaryKeyEntityFieldNames);
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("DELETE FROM [{0}] WHERE ", EntityMappingTool.GetDbTableName(entityType));
for (int i = 0; i < primaryKeyEntityFieldNames.Count; i++)
{
sqlBuilder.Append((i > 0) ? " AND " : "");
sqlBuilder.AppendFormat("([{0}]=@{0})", primaryKeyDbCloumnNames[i]);
}
//参数
DbCommand cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
for (int i = 0; i < primaryKeyEntityFieldNames.Count; i++)
{
db.AddInParameter(cmd, "@" + primaryKeyDbCloumnNames[i], primaryKeyDbColumnTypes[i],
primaryKeyPropertyInfos[i].GetValue(entity, null));
}
return(cmd);
}
/// <summary>
/// 生成用于更新的Sql命令
/// </summary>
public static DbCommand CreateUpdateCommand <T>(Database db, T entity)
{
Type entityType = typeof(T);
List <string> primaryKeyEntityFieldNames = EntityMappingTool.GetPrimaryKeyOfEntityField(entityType);
List <string> primaryKeyDbCloumnNames = EntityMappingTool.GetDbColumnNames(entityType,
primaryKeyEntityFieldNames);
List <DbType> primaryKeyDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType,
primaryKeyEntityFieldNames);
List <string> notNullEntityFields = EntityInstanceTool.GetNotNullFields(entity);
List <string> notNullDbCloumnNames = EntityMappingTool.GetDbColumnNames(entityType, notNullEntityFields);
List <DbType> notNullDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType, notNullEntityFields);
List <PropertyInfo> notNullEntityPropertys = EntityInstanceTool.GetNotNullEntityPropertys(entity);
//生成Sql语句
List <string> parameterIndex = new List <string>();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("UPDATE [").Append(EntityMappingTool.GetDbTableName(entityType)).Append("] SET ");
bool firstColumn = true;
for (int i = 0; i < notNullDbCloumnNames.Count; i++)
{
string loopColumn = notNullDbCloumnNames[i];
//当前模式主键不更新
if (primaryKeyDbCloumnNames.Contains(loopColumn))
{
continue;
}
sqlBuilder.Append(firstColumn ? "" : ",");
firstColumn = false;
sqlBuilder.AppendFormat("[{0}]=@{0}", loopColumn);
parameterIndex.Add(loopColumn);
}
//WHERE
sqlBuilder.Append(" WHERE ");
for (int i = 0; i < primaryKeyDbCloumnNames.Count; i++)
{
sqlBuilder.Append((i > 0) ? " AND " : "");
sqlBuilder.AppendFormat("([{0}]=@{0})", primaryKeyDbCloumnNames[i]);
parameterIndex.Add(primaryKeyDbCloumnNames[i]);
}
DbCommand cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
for (int i = 0; i < parameterIndex.Count; i++)
{
int pIndex = notNullDbCloumnNames.IndexOf(parameterIndex[i]);
db.AddInParameter(cmd, "@" + notNullDbCloumnNames[pIndex], notNullDbColumnTypes[pIndex],
notNullEntityPropertys[pIndex].GetValue(entity, null));
}
return(cmd);
}
/// <summary>
/// 创建查询条件
/// </summary>
public static string CreateWhereSql <T>(GenericWhereEntity <T> theWhereEntity)
{
string dbTableName = EntityMappingTool.GetDbTableName(theWhereEntity.EntityType);
if (string.IsNullOrEmpty(dbTableName))
{
throw new EntitySqlException(string.Format("未给类型{0}设置数据表信息!", theWhereEntity.EntityType.FullName));
}
StringBuilder 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 (int i = 0; i < theWhereEntity.WhereExpressions.Count; i++)
{
ConditionBuilderGeneric <T> 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>(Database db, GenericWhereEntity <T> whereEntity)
{
//生成Sql语句
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("DELETE ", EntityMappingTool.GetDbTableName(typeof(T)));
sqlBuilder.Append(SqlCreator.CreateWhereSql(whereEntity));
DbCommand cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
FillSqlParameters(db, cmd, whereEntity);
return(cmd);
}
/// <summary>
/// 检索出Entity实例中有设置值的属性
/// </summary>
/// <typeparam name="T">实体类的类型</typeparam>
/// <param name="entity">实体类的实例</param>
/// <param name="entityPropertyInfos">实体类对应的属性</param>
/// <returns>Entity实例中有设置值的属性</returns>
public static List <PropertyInfo> GetNotNullEntityPropertys <T>(T entity)
{
List <PropertyInfo> entityPropertyInfos = EntityMappingTool.GetEntityPropertyInfos((typeof(T)));
List <PropertyInfo> notNullEntityPropertys = new List <PropertyInfo>(entityPropertyInfos.Count);
for (int i = 0; i < entityPropertyInfos.Count; i++)
{
if (entityPropertyInfos[i].GetValue(entity, null) != null)
{
notNullEntityPropertys.Add(entityPropertyInfos[i]);
}
}
return(notNullEntityPropertys);
}
/// <summary>
/// 从DataReader实例化一个Entity
/// </summary>
/// <typeparam name="T">实体类的类型</typeparam>
/// <param name="reader">DataReader</param>
/// <param name="entityPropertyInfos">已经排好序的实体类的属性集合</param>
/// <returns></returns>
public static T FillOneEntity <T>(IDataReader reader) where T : class, new()
{
List <PropertyInfo> entityPropertyInfo = EntityMappingTool.GetEntityPropertyInfos(typeof(T));
T entity = new T();
for (int i = 0; i < entityPropertyInfo.Count; i++)
{
if (reader.IsDBNull(i))
{
continue;
}
entityPropertyInfo[i].SetValue(entity, reader.GetValue(i), null);
}
return(entity);
}
/// <summary>
/// 生成用于插入的Sql命令
/// </summary>
public static DbCommand CreateInsertCommand <T>(Database db, T entity)
{
var entityType = typeof(T);
List <string> notNullEntityFields = EntityInstanceTool.GetNotNullFields(entity);
List <string> notNullDbCloumnNames = EntityMappingTool.GetDbColumnNames(entityType, notNullEntityFields);
List <DbType> notNullDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType, notNullEntityFields);
List <PropertyInfo> notNullEntityPropertys = EntityInstanceTool.GetNotNullEntityPropertys(entity);
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("INSERT INTO [").Append(EntityMappingTool.GetDbTableName(entity.GetType())).Append("] (");
for (int i = 0; i < notNullDbCloumnNames.Count; i++)
{
if (i > 0)
{
sqlBuilder.Append(", ");
}
sqlBuilder.Append("[").Append(notNullDbCloumnNames[i]).Append("]");
}
sqlBuilder.Append(") VALUES (");
for (int i = 0; i < notNullDbCloumnNames.Count; i++)
{
if (i > 0)
{
sqlBuilder.Append(", ");
}
sqlBuilder.Append("@").Append(notNullDbCloumnNames[i]);
}
sqlBuilder.Append(")");
DbCommand cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
for (int i = 0; i < notNullDbCloumnNames.Count; i++)
{
db.AddInParameter(cmd, "@" + notNullDbCloumnNames[i], notNullDbColumnTypes[i],
notNullEntityPropertys[i].GetValue(entity, null));
}
return(cmd);
}
/// <summary>
/// 创建成员查询的Sql语句
/// </summary>
public static string CreateSelectSql <T>(GenericWhereEntity <T> theWhereEntity, List <string> dbColumnNames = null,
int topCount = 0)
{
string 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));
}
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT ");
if (topCount > 0)
{
sqlBuilder.AppendFormat("TOP {0} ", topCount);
}
for (int 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>(Database db, T entity, GenericWhereEntity <T> whereEntity)
{
Type entityType = typeof(T);
List <string> notNullEntityFields = EntityInstanceTool.GetNotNullFields(entity);
List <string> notNullDbCloumnNames = EntityMappingTool.GetDbColumnNames(entityType, notNullEntityFields);
List <DbType> notNullDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType, notNullEntityFields);
List <PropertyInfo> notNullEntityPropertys = EntityInstanceTool.GetNotNullEntityPropertys(entity);
//生成Sql语句
List <string> parameterIndex = new List <string>();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("UPDATE {0} SET ", whereEntity.TableName);
bool firstColumn = true;
for (int i = 0; i < notNullDbCloumnNames.Count; i++)
{
string loopColumn = notNullDbCloumnNames[i];
sqlBuilder.Append(firstColumn ? "" : ",");
firstColumn = false;
sqlBuilder.AppendFormat("{0}.[{1}]=@{1}", whereEntity.TableName, loopColumn);
parameterIndex.Add(loopColumn);
}
//WHERE
string whereSql = SqlCreator.CreateWhereSql(whereEntity);
sqlBuilder.Append(" ").Append(whereSql);
//参数
DbCommand cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
for (int i = 0; i < notNullDbCloumnNames.Count; i++)
{
db.AddInParameter(cmd, "@" + notNullDbCloumnNames[i], notNullDbColumnTypes[i],
notNullEntityPropertys[i].GetValue(entity, null));
}
FillSqlParameters(db, cmd, whereEntity);
return(cmd);
}
/// <summary>
/// 生成用于更新的Sql命令
/// </summary>
public static DbCommand CreateUpdateMemberToNullCommand <T>(Database db, GenericWhereEntity <T> whereEntity,
string memberName)
{
string dbColumnName = EntityMappingTool.GetDbColumnName(typeof(T), memberName);
//生成Sql语句
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("UPDATE {0} SET {0}.[{1}]=null", whereEntity.TableName, dbColumnName);
//WHERE
string whereSql = SqlCreator.CreateWhereSql(whereEntity);
sqlBuilder.Append(" ").Append(whereSql);
//参数
DbCommand cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
FillSqlParameters(db, cmd, whereEntity);
return(cmd);
}