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