/// <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> /// 生成用于更新的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> /// 生成用于插入的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 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); }