/// <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 DbCommand CreatDeleteCommand <T>(DbHelper db, T entity) { var entityType = typeof(T); var primaryKeyEntityFieldNames = EntityMappingTool.GetPrimaryKeyOfEntityField(entityType); var primaryKeyDbCloumnNames = EntityMappingTool.GetDbColumnNames(entityType, primaryKeyEntityFieldNames); var primaryKeyDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType, primaryKeyEntityFieldNames); var primaryKeyPropertyInfos = EntityMappingTool.GetEntityPropertyInfos(entityType, primaryKeyEntityFieldNames); var sqlBuilder = new StringBuilder(); sqlBuilder.AppendFormat("DELETE FROM [{0}] WHERE ", EntityMappingTool.GetDbTableName(entityType)); for (var i = 0; i < primaryKeyEntityFieldNames.Count; i++) { sqlBuilder.Append((i > 0) ? " AND " : ""); sqlBuilder.AppendFormat("([{0}]=@{0})", primaryKeyDbCloumnNames[i]); } //参数 var cmd = db.GetSqlStringCommand(sqlBuilder.ToString()); for (var i = 0; i < primaryKeyEntityFieldNames.Count; i++) { db.AddInParameter(cmd, "@" + primaryKeyDbCloumnNames[i], primaryKeyDbColumnTypes[i], primaryKeyPropertyInfos[i].GetValue(entity, null)); } 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() { var entityPropertyInfoA = EntityMappingTool.GetEntityPropertyInfos(typeof(TA)); var entityPropertyInfoB = EntityMappingTool.GetEntityPropertyInfos(typeof(TB)); var pair = new GenericPairEntity <TA, TB>(); pair.EntityA = new TA(); pair.EntityB = new TB(); var offset = 0; for (var 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 (var 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); } var notNullEntityFields = GetNotNullFields(entity); if (notNullEntityFields == null) { return(false); } var primaryKeyEntityFieldNames = EntityMappingTool.GetPrimaryKeyOfEntityField(typeof(T)); for (var i = 0; i < primaryKeyEntityFieldNames.Count; i++) { if (!notNullEntityFields.Any(n => n == primaryKeyEntityFieldNames[i])) { return(false); } } return(true); }
/// <summary> /// 生成用于更新的Sql命令 /// </summary> public static DbCommand CreateUpdateCommand <T>(DbHelper db, T entity) { var entityType = typeof(T); var primaryKeyEntityFieldNames = EntityMappingTool.GetPrimaryKeyOfEntityField(entityType); var primaryKeyDbCloumnNames = EntityMappingTool.GetDbColumnNames(entityType, primaryKeyEntityFieldNames); var primaryKeyDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType, primaryKeyEntityFieldNames); 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.Append("UPDATE [").Append(EntityMappingTool.GetDbTableName(entityType)).Append("] SET "); var firstColumn = true; for (var i = 0; i < notNullDbCloumnNames.Count; i++) { var 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 (var i = 0; i < primaryKeyDbCloumnNames.Count; i++) { sqlBuilder.Append((i > 0) ? " AND " : ""); sqlBuilder.AppendFormat("([{0}]=@{0})", primaryKeyDbCloumnNames[i]); parameterIndex.Add(primaryKeyDbCloumnNames[i]); } var cmd = db.GetSqlStringCommand(sqlBuilder.ToString()); for (var i = 0; i < parameterIndex.Count; i++) { var 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) { 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); }
/// <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) { var entityPropertyInfos = EntityMappingTool.GetEntityPropertyInfos((typeof(T))); var notNullEntityPropertys = new List <PropertyInfo>(entityPropertyInfos.Count); for (var 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() { var entityPropertyInfo = EntityMappingTool.GetEntityPropertyInfos(typeof(T)); var entity = new T(); for (var 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>(DbHelper db, T entity) { 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); var sqlBuilder = new StringBuilder(); sqlBuilder.Append("INSERT INTO [").Append(EntityMappingTool.GetDbTableName(entity.GetType())).Append("] ("); for (var i = 0; i < notNullDbCloumnNames.Count; i++) { if (i > 0) { sqlBuilder.Append(", "); } sqlBuilder.Append("[").Append(notNullDbCloumnNames[i]).Append("]"); } sqlBuilder.Append(") VALUES ("); for (var i = 0; i < notNullDbCloumnNames.Count; i++) { if (i > 0) { sqlBuilder.Append(", "); } sqlBuilder.Append("@").Append(notNullDbCloumnNames[i]); } sqlBuilder.Append(")"); 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)); } return(cmd); }
/// <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); }
/// <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); }