protected override SqlCommandDescription GenerateSelect(SqlCommandGenerateContext context)
{
SelectInfo selectInfo = (SelectInfo)context.CommandInfo;
string tableName = context.TableName;
StringBuilder sqlBuilder = new StringBuilder();
SqlCommandDescription result = new SqlCommandDescription();
var generateContext = new GenerateContext(result, sqlBuilder);
sqlBuilder.Append("SELECT ");
if (!selectInfo.Fields.Any())
{
sqlBuilder.Append("*");
}
else
{
sqlBuilder.Append(selectInfo.Fields.Join(",", x => $"[{x}]"));
}
sqlBuilder.Append($" FROM [{tableName}]");
GenerateByConditions(ref generateContext, selectInfo.Conditions);
if (!selectInfo.Paging)
{
GenerateSqlByOrders(ref generateContext, selectInfo.Orders);
result.SqlCommand = sqlBuilder.ToString();
return(result);
}
if (!selectInfo.Orders.Any())
{
throw new NotImplementedException("分页查询必须具有排序字段");
}
StringBuilder shellBuilder = new StringBuilder();
shellBuilder.Append("SELECT * FROM ( SELECT *,ROW_NUMBER() OVER (");
generateContext.StringBuilder = shellBuilder;
GenerateSqlByOrders(ref generateContext, selectInfo.Orders);
shellBuilder.Append(" ) AS __RN__ FROM ( ");
shellBuilder.Append(sqlBuilder.ToString());
shellBuilder.Append($") t ) t WHERE t.__RN__ > @{Constant.PARAMETER_NAME_BEGIN_ROW_NUMBER} AND t.__RN__ <= @{Constant.PARAMETER_NAME_END_ROW_NUMBER}");
result.AddParameter(new SqlParameterInfo()
{
Name = Constant.PARAMETER_NAME_BEGIN_ROW_NUMBER
});
result.AddParameter(new SqlParameterInfo()
{
Name = Constant.PARAMETER_NAME_END_ROW_NUMBER
});
result.SqlCommand = shellBuilder.ToString();
return(result);
}
protected override SqlCommandDescription GenerateCount(SqlCommandGenerateContext context)
{
string tableName = context.TableName;
CountInfo deleteInfo = (CountInfo)context.CommandInfo;
StringBuilder sqlBuilder = new StringBuilder();
SqlCommandDescription description = new SqlCommandDescription();
GenerateContext generateContext = new GenerateContext(description, sqlBuilder);
sqlBuilder.Append($"SELECT COUNT(*) AS [{Constant.RESULT_FIELD_NAME_COUNT}] FROM [{tableName}]");
GenerateByConditions(ref generateContext, deleteInfo.ConditionInfos);
description.SqlCommand = sqlBuilder.ToString();
return(description);
}
protected override SqlCommandDescription GenerateDelete(SqlCommandGenerateContext context)
{
string tableName = context.TableName;
DeleteInfo deleteInfo = (DeleteInfo)context.CommandInfo;
StringBuilder sqlBuilder = new StringBuilder();
SqlCommandDescription description = new SqlCommandDescription();
GenerateContext generateContext = new GenerateContext(description, sqlBuilder);
sqlBuilder.Append($"DELETE FROM [{tableName}]");
GenerateByConditions(ref generateContext, deleteInfo.ConditionInfos);
description.SqlCommand = sqlBuilder.ToString();
return(description);
}
protected override SqlCommandDescription GenerateUpdate(SqlCommandGenerateContext context)
{
string tableName = context.TableName;
UpdateInfo updateInfo = (UpdateInfo)context.CommandInfo;
StringBuilder sqlBuilder = new StringBuilder();
SqlCommandDescription result = new SqlCommandDescription();
GenerateContext generateContext = new GenerateContext(result, sqlBuilder);
sqlBuilder.Append($"UPDATE [{tableName}] SET ");
string setCommand;
if (updateInfo.SetFields.Any())
{
setCommand = updateInfo.SetFields.Join(",", x =>
{
result.AddParameter(new SqlParameterInfo(x.Parameter));
return($"[{x.Field}] = @{x.Parameter}");
});
}
else
{
HashSet <string> conditionField = new HashSet <string>(updateInfo.Conditions.Select(x => x.Field));
HashSet <string> lowerCaseWithoutFields = new HashSet <string>(updateInfo.WithoutFields.Select(x => x.ToLower()));
setCommand = GetColumnNames(context)
.Where(x => !conditionField.Contains(x))
.Where(x => !lowerCaseWithoutFields.Contains(x.ToLower()))
.Join(",", x =>
{
result.AddParameter(new SqlParameterInfo(x));
return($"[{x}] = @{x}");
});
}
sqlBuilder.Append(setCommand);
GenerateByConditions(ref generateContext, updateInfo.Conditions);
result.SqlCommand = sqlBuilder.ToString();
return(result);
}
protected override SqlCommandDescription GenerateInsert(SqlCommandGenerateContext context)
{
SqlCommandDescription description = new SqlCommandDescription();
InsertInfo info = (InsertInfo)context.CommandInfo;
HashSet <string> lowerCaseWithoutFields = new HashSet <string>
(
info.WithoutFields.Select(x => x.ToLower())
);
IEnumerable <string> columnNames = GetColumnNames(context)
.Where(x => !lowerCaseWithoutFields.Contains(x.ToLower()));
string fields = columnNames.Join(",", x => $"[{x}]");
string values = columnNames.Join(",", x => $"@{x}");
foreach (var columnName in columnNames)
{
description.AddParameter(new SqlParameterInfo(columnName));
}
description.SqlCommand = $"INSERT INTO [{context.TableName}]({fields})VALUES({values});SELECT ISNULL(SCOPE_IDENTITY(),0) AS [Id]";
return(description);
}
private IEnumerable <string> GetColumnNames(SqlCommandGenerateContext context)
{
return(context.EntityType.GetProperties()
.Select(x => fieldNameProvider.Provide(x)));
}
