public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyRulesForSelect(DataSource);
if (m_FunctionArgumentValue != null)
{
sqlBuilder.ApplyArgumentValue(DataSource, OperationTypes.None, m_FunctionArgumentValue);
}
if (m_SelectClause == null)
{
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
}
//Support check
if (!Enum.IsDefined(typeof(SqlServerLimitOption), m_LimitOptions))
{
throw new NotSupportedException($"SQL Server does not support limit option {(LimitOptions)m_LimitOptions}");
}
if (m_LimitOptions == SqlServerLimitOption.TableSampleSystemRows || m_LimitOptions == SqlServerLimitOption.TableSampleSystemPercentage)
{
throw new NotSupportedException($"SQL Server does not support limit option {(LimitOptions)m_LimitOptions} with table-valued functions");
}
if (m_Seed.HasValue)
{
throw new NotSupportedException($"SQL Server does not setting a random seed for table-valued functions");
}
//Validation
if (m_Skip < 0)
{
throw new InvalidOperationException($"Cannot skip {m_Skip} rows");
}
if (m_Skip > 0 && !m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform a Skip operation with out a sort expression.");
}
if (m_Skip > 0 && m_LimitOptions != SqlServerLimitOption.Rows)
{
throw new InvalidOperationException($"Cannot perform a Skip operation with limit option {m_LimitOptions}");
}
if (m_Take <= 0)
{
throw new InvalidOperationException($"Cannot take {m_Take} rows");
}
if ((m_LimitOptions == SqlServerLimitOption.RowsWithTies || m_LimitOptions == SqlServerLimitOption.PercentageWithTies) && !m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform a WITH TIES operation without sorting.");
}
//SQL Generation
List <OleDbParameter> parameters;
var sql = new StringBuilder();
string topClause = null;
switch (m_LimitOptions)
{
case SqlServerLimitOption.Rows:
if (!m_SortExpressions.Any())
{
topClause = $"TOP (@fetch_row_count_expression) ";
}
break;
case SqlServerLimitOption.Percentage:
topClause = $"TOP (@fetch_row_count_expression) PERCENT ";
break;
case SqlServerLimitOption.PercentageWithTies:
topClause = $"TOP (@fetch_row_count_expression) PERCENT WITH TIES ";
break;
case SqlServerLimitOption.RowsWithTies:
topClause = $"TOP (@fetch_row_count_expression) WITH TIES ";
break;
}
if (m_SelectClause != null)
{
sql.Append($"SELECT {topClause} {m_SelectClause} ");
}
else
{
sqlBuilder.BuildSelectClause(sql, "SELECT " + topClause, null, null);
}
sqlBuilder.BuildFromFunctionClause(sql, $" FROM {m_Table.Name.ToQuotedString()} (", " ) ");
if (m_FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions));
sqlBuilder.BuildSoftDeleteClause(sql, " AND ", DataSource, null);
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE " + m_WhereClause);
sqlBuilder.BuildSoftDeleteClause(sql, " AND ", DataSource, null);
parameters = SqlBuilder.GetParameters <OleDbParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
sqlBuilder.BuildSoftDeleteClause(sql, " WHERE ", DataSource, null);
parameters = sqlBuilder.GetParameters();
}
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_SortExpressions, null);
switch (m_LimitOptions)
{
case SqlServerLimitOption.Rows:
if (m_SortExpressions.Any())
{
sql.Append(" OFFSET @offset_row_count_expression ROWS ");
parameters.Add(new OleDbParameter("@offset_row_count_expression", m_Skip ?? 0));
if (m_Take.HasValue)
{
sql.Append(" FETCH NEXT @fetch_row_count_expression ROWS ONLY");
parameters.Add(new OleDbParameter("@fetch_row_count_expression", m_Take));
}
}
else
{
parameters.Add(new OleDbParameter("@fetch_row_count_expression", m_Take));
}
break;
case SqlServerLimitOption.Percentage:
case SqlServerLimitOption.PercentageWithTies:
case SqlServerLimitOption.RowsWithTies:
parameters.Add(new OleDbParameter("@fetch_row_count_expression", m_Take));
break;
}
sql.Append(";");
return(new OleDbCommandExecutionToken(DataSource, "Query Function " + m_Table.Name, sql.ToString(), parameters));
}
public override CommandExecutionToken <NpgsqlCommand, NpgsqlParameter> Prepare(Materializer <NpgsqlCommand, NpgsqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyRulesForSelect(DataSource);
if (m_FunctionArgumentValue != null)
{
sqlBuilder.ApplyArgumentValue(DataSource, OperationTypes.None, m_FunctionArgumentValue);
}
if (m_SelectClause == null)
{
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
}
//Support check
if (!Enum.IsDefined(typeof(PostgreSqlLimitOption), m_LimitOptions))
{
throw new NotSupportedException($"PostgreSQL does not support limit option {(LimitOptions)m_LimitOptions}");
}
//Validation
if (m_Skip < 0)
{
throw new InvalidOperationException($"Cannot skip {m_Skip} rows");
}
if (m_Skip > 0 && m_LimitOptions != PostgreSqlLimitOption.Rows)
{
throw new InvalidOperationException($"Cannot perform a Skip operation with limit option {m_LimitOptions}");
}
if (m_Take <= 0)
{
throw new InvalidOperationException($"Cannot take {m_Take} rows");
}
//SQL Generation
List <NpgsqlParameter> parameters;
var sql = new StringBuilder();
if (m_SelectClause != null)
{
sql.Append($"SELECT {m_SelectClause} ");
}
else
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, null);
}
sqlBuilder.BuildFromFunctionClause(sql, $" FROM {m_Table.Name.ToQuotedString()} (", " ) ");
if (m_FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions));
sqlBuilder.BuildSoftDeleteClause(sql, " AND ", DataSource, null);
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE " + m_WhereClause);
sqlBuilder.BuildSoftDeleteClause(sql, " AND ", DataSource, null);
parameters = SqlBuilder.GetParameters <NpgsqlParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
sqlBuilder.BuildSoftDeleteClause(sql, " WHERE ", DataSource, null);
parameters = sqlBuilder.GetParameters();
}
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_SortExpressions, null);
switch (m_LimitOptions)
{
case PostgreSqlLimitOption.Rows:
sql.Append(" OFFSET @offset_row_count_expression");
parameters.Add(new NpgsqlParameter("@offset_row_count_expression", m_Skip ?? 0));
if (m_Take.HasValue)
{
sql.Append(" LIMIT @limit_row_count_expression");
parameters.Add(new NpgsqlParameter("@limit_row_count_expression", m_Take));
}
break;
}
sql.Append(";");
return(new PostgreSqlCommandExecutionToken(DataSource, "Query Function " + m_Table.Name, sql.ToString(), parameters));
}