/// <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);
}