/// <inheritdoc />
public override SqlCommand MakeCreateTempTableCommand(TableSchema tableSchema)
{
EnsureValidSchemaForTempTables(tableSchema);
var sql = new SqlCommandBuilder("CREATE TABLE ").Append(tableSchema.Name).AppendLine();
sql.AppendLine("(");
var isFirst = true;
foreach (var column in tableSchema.Columns)
{
if (!isFirst)
{
sql.AppendLine(",");
}
sql.Append(new string(' ', 4));
sql.Append(column.ColumnName);
sql.Append(" ").Append(GetColumnType(column));
isFirst = false;
}
sql.AppendLine();
sql.Append(");");
return(sql.ToCommand(null));
}
/// <inheritdoc />
public override SqlCommand MakeCreateTempTableCommand(TableSchema tableSchema)
{
if (tableSchema.Columns.IsEmpty)
{
throw new ArgumentException("Temporary tables must have columns");
}
var sql = new SqlCommandBuilder("CREATE TEMP TABLE ").Append(tableSchema.Name).AppendLine();
sql.AppendLine("(");
var isFirst = true;
foreach (var column in tableSchema.Columns)
{
if (!isFirst)
{
sql.AppendLine(",");
}
sql.Append(new string(' ', 4));
sql.Append(column.ColumnName);
sql.Append(" ").Append(GetColumnType(column));
isFirst = false;
}
sql.AppendLine();
sql.Append(")");
return(sql.ToCommand(null));
}
public override SqlCommand MakeGetFirstNCommand(int take, string conditions, object parameters, string orderBy, TableSchema tableSchema)
{
var sql = new SqlCommandBuilder("SELECT ").AppendSelectPropertiesClause(tableSchema.Columns);
sql.AppendClause("FROM ").Append(tableSchema.Name);
sql.AppendClause(conditions);
if (!string.IsNullOrWhiteSpace(orderBy))
{
sql.AppendClause("ORDER BY ").Append(orderBy);
}
sql.AppendLine().Append("LIMIT ").Append(take);
return(sql.ToCommand(parameters));
}
/// <inheritdoc />
public override SqlCommand MakeGetPageCommand(Page page, string conditions, object parameters, string orderBy, TableSchema tableSchema)
{
if (string.IsNullOrWhiteSpace(orderBy))
{
throw new ArgumentException("orderBy cannot be empty");
}
var sql = new SqlCommandBuilder("SELECT ").AppendSelectPropertiesClause(tableSchema.Columns);
sql.AppendClause("FROM ").Append(tableSchema.Name);
sql.AppendClause(conditions);
sql.AppendClause("ORDER BY ").Append(orderBy);
sql.AppendLine().AppendFormat("SKIP {0} TAKE {1}", page.FirstItemIndex, page.PageSize);
return(sql.ToCommand(parameters));
}
public override SqlCommand MakeGetFirstNCommand(int take, string conditions, object parameters, string orderBy, TableSchema tableSchema)
{
Ensure.NotNull(conditions, nameof(conditions));
var sql = new SqlCommandBuilder("SELECT ").AppendSelectPropertiesClause(tableSchema.Columns);
sql.AppendClause("FROM ").Append(tableSchema.Name);
sql.AppendClause(conditions);
if (!string.IsNullOrWhiteSpace(orderBy))
{
sql.AppendClause("ORDER BY ").Append(orderBy);
}
sql.AppendLine().AppendFormat("TAKE {0}", take);
return(new SqlCommand(sql.ToString()));
}
/// <inheritdoc />
protected override void CreateLastInsertedRowCommand(SqlCommandBuilder commandBuilder, Row row)
{
if (commandBuilder == null)
{
throw new ArgumentNullException(nameof(commandBuilder));
}
if (row == null)
{
throw new ArgumentNullException(nameof(row));
}
var idField = Layout.Identifier.Single();
commandBuilder.AppendLine($"SELECT * FROM {FQTN} WHERE {Storage.EscapeFieldName(idField)} = LASTVAL();");
}
/// <inheritdoc />
protected override void CreateLastInsertedRowCommand(SqlCommandBuilder commandBuilder, Row row)
{
var idField = Layout.Identifier.Single();
commandBuilder.AppendLine($"SELECT * FROM {FQTN} WHERE {Storage.EscapeFieldName(idField)} = (SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]);");
}