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.ApplyDesiredColumns(materializer.DesiredColumns());
List <OleDbParameter> parameters;
var sql = new StringBuilder();
sql.Append("DELETE FROM " + m_Table.Name.ToQuotedString());
if (m_FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions));
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE " + m_WhereClause);
parameters = SqlBuilder.GetParameters <OleDbParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
parameters = sqlBuilder.GetParameters();
}
sql.Append(";");
if (m_Parameters != null)
{
parameters.AddRange(m_Parameters);
}
var deleteCommand = new AccessCommandExecutionToken(DataSource, "Delete from " + m_Table.Name, sql.ToString(), parameters);
var desiredColumns = materializer.DesiredColumns();
if (desiredColumns == Materializer.NoColumns)
{
return(deleteCommand);
}
var result = PrepareRead(desiredColumns);
result.NextCommand = deleteCommand;
return(result);
//var sqlBuilder2
// sqlBuilder.BuildSelectClause(sql, "SELECT ", null, " FROM " + m_Table.Name.ToQuotedString());
// sql.AppendLine(" WHERE " + m_WhereClause + ";");
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="AccessCommandExecutionToken" /></returns>
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.ApplyArgumentValue(DataSource, m_NewValues, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var sql = new StringBuilder($"UPDATE {m_Table.Name.ToQuotedString()}");
sqlBuilder.BuildSetClause(sql, " SET ", null, null);
sql.Append(" WHERE " + m_WhereClause);
sql.Append(";");
var parameters = sqlBuilder.GetParameters();
if (m_Parameters != null)
{
parameters.AddRange(m_Parameters);
}
var updateCommand = new AccessCommandExecutionToken(DataSource, "Update " + m_Table.Name, sql.ToString(), parameters).CheckUpdateRowCount(m_Options, m_ExpectedRowCount);
updateCommand.ExecutionMode = AccessCommandExecutionMode.NonQuery;
var desiredColumns = materializer.DesiredColumns();
if (desiredColumns == Materializer.NoColumns)
{
return(updateCommand);
}
if (m_Options.HasFlag(UpdateOptions.ReturnOldValues))
{
var result = PrepareRead(desiredColumns);
result.NextCommand = updateCommand;
return(result);
}
else
{
updateCommand.NextCommand = PrepareRead(desiredColumns);
return(updateCommand);
}
}
public override CommandExecutionToken <SQLiteCommand, SQLiteParameter> Prepare(Materializer <SQLiteCommand, SQLiteParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var sql = new StringBuilder();
if (sqlBuilder.HasReadFields)
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, " FROM " + m_Table.Name.ToQuotedString());
sql.AppendLine(" WHERE " + m_WhereClause + ";");
}
sql.Append("DELETE FROM " + m_Table.Name.ToQuotedString());
sql.AppendLine(" WHERE " + m_WhereClause + ";");
var parameters = sqlBuilder.GetParameters();
if (m_Parameters != null)
{
parameters.AddRange(m_Parameters);
}
return(new SQLiteCommandExecutionToken(DataSource, "Delete from " + m_Table.Name, sql.ToString(), parameters, lockType: LockType.Write));
}
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyTableType(DataSource, OperationTypes.Insert, m_TableType.Columns);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var sql = new StringBuilder();
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {m_Table.Name.ToQuotedString()} (", null, ")");
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Inserted.", null);
sqlBuilder.BuildSelectTvpForInsertClause(sql, " SELECT ", null, " FROM @ValuesParameter ");
sql.Append(";");
var parameters = sqlBuilder.GetParameters();
parameters.Add(new SqlParameter()
{
ParameterName = "@ValuesParameter",
Value = m_Source,
SqlDbType = SqlDbType.Structured,
TypeName = m_TableType.Name.ToQuotedString()
});
return(new SqlServerCommandExecutionToken(DataSource, "Insert batch into " + m_Table.Name, sql.ToString(), parameters));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>ExecutionToken<TCommand>.</returns>
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.ApplyArgumentValue(DataSource, m_NewValues, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var prefix = m_Options.HasFlag(UpdateOptions.ReturnOldValues) ? "Deleted." : "Inserted.";
var sql = new StringBuilder($"UPDATE {m_Table.Name.ToQuotedString()}");
sqlBuilder.BuildAnonymousSetClause(sql, " SET ", null, null);
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", prefix, null);
sql.Append(" WHERE " + m_WhereClause);
sql.Append(";");
var parameters = sqlBuilder.GetParameters();
parameters.AddRange(m_Parameters);
return(new OleDbCommandExecutionToken(DataSource, "Update " + m_Table.Name, sql.ToString(), parameters).CheckUpdateRowCount(m_Options, m_ExpectedRowCount));
}
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
//This sets up the sqlBuilder. We're not actually going to build the parameters now
sqlBuilder.ApplyArgumentValue(DataSource, m_SourceList[0], m_Options);
var sql = new StringBuilder();
bool identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {m_Table.Name.ToQuotedString()} ON;");
}
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {m_Table.Name.ToQuotedString()} (", null, ")", identityInsert);
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Inserted.", null);
sql.AppendLine("VALUES");
var parameters = new List <SqlParameter>();
for (var i = 0; i < m_SourceList.Count; i++)
{
var parameterSuffix = "_" + i;
var footer = (i == m_SourceList.Count - 1) ? ");" : "),";
sqlBuilder.OverrideArgumentValue(DataSource, AuditRules.OperationTypes.Insert, m_SourceList[i]);
sqlBuilder.BuildValuesClause(sql, "(", footer, identityInsert, parameterSuffix, parameters, Utilities.ParameterBuilderCallback);
}
var maxParams = DataSource.DatabaseMetadata.MaxParameters !.Value;
if (parameters.Count > maxParams)
{
var parametersPerRow = parameters.Count / m_SourceList.Count;
var maxRows = maxParams / parametersPerRow;
throw new InvalidOperationException($"Batch insert exceeds SQL Server's parameter limit of {DataSource.DatabaseMetadata.MaxParameters}. Supply a table type, break the call into batches of {maxRows}, use InsertMultipleBatch, or use BulkInsert");
}
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {m_Table.Name.ToQuotedString()} OFF;");
}
return(new SqlServerCommandExecutionToken(DataSource, "Insert batch into " + m_Table.Name, sql.ToString(), parameters));
}
public override CommandExecutionToken <MySqlCommand, MySqlParameter> Prepare(Materializer <MySqlCommand, MySqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
List <MySqlParameter> parameters;
var sql = new StringBuilder();
if (sqlBuilder.HasReadFields)
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, " FROM " + m_Table.Name.ToQuotedString());
if (m_FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions));
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE " + m_WhereClause);
}
sql.AppendLine(";");
}
sql.Append("DELETE FROM " + m_Table.Name.ToQuotedString());
if (m_FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions));
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE " + m_WhereClause);
parameters = SqlBuilder.GetParameters <MySqlParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
parameters = sqlBuilder.GetParameters();
}
sql.Append(";");
if (m_Parameters != null)
{
parameters.AddRange(m_Parameters);
}
return(new MySqlCommandExecutionToken(DataSource, "Delete from " + m_Table.Name, sql.ToString(), parameters));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="PostgreSqlCommandExecutionToken" /></returns>
public override CommandExecutionToken <NpgsqlCommand, NpgsqlParameter> Prepare(Materializer <NpgsqlCommand, NpgsqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
if (identityInsert)
{
throw new NotImplementedException("See issue 256. https://github.com/docevaad/Chain/issues/256");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
//This sets up the sqlBuilder. We're not actually going to build the parameters now
sqlBuilder.ApplyArgumentValue(DataSource, m_SourceList[0], m_Options);
var sql = new StringBuilder();
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {m_Table.Name.ToQuotedString()} (", null, ")", identityInsert);
sql.AppendLine("VALUES");
var parameters = new List <NpgsqlParameter>();
for (var i = 0; i < m_SourceList.Count; i++)
{
var parameterSuffix = "_" + i;
var footer = (i == m_SourceList.Count - 1) ? ")" : "),";
sqlBuilder.OverrideArgumentValue(DataSource, AuditRules.OperationTypes.Insert, m_SourceList[i]);
sqlBuilder.BuildValuesClause(sql, "(", footer, identityInsert, parameterSuffix, parameters, Utilities.ParameterBuilderCallback);
}
sqlBuilder.BuildSelectClause(sql, " RETURNING ", null, ";");
var maxParams = DataSource.DatabaseMetadata.MaxParameters !.Value;
if (parameters.Count > maxParams)
{
var parametersPerRow = parameters.Count / m_SourceList.Count;
var maxRows = maxParams / parametersPerRow;
throw new InvalidOperationException($"Batch insert exceeds PostgreSql's parameter limit of {DataSource.DatabaseMetadata.MaxParameters}. Break the call into batches of {maxRows} or use InsertMultipleBatch");
}
return(new PostgreSqlCommandExecutionToken(DataSource, "Insert batch into " + 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.ApplyDesiredColumns(materializer.DesiredColumns());
List <NpgsqlParameter> parameters;
var sql = new StringBuilder();
sql.Append("DELETE FROM " + m_Table.Name.ToQuotedString());
if (FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyFilterValue(FilterValue, FilterOptions));
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(WhereClause))
{
sql.Append(" WHERE " + WhereClause);
parameters = SqlBuilder.GetParameters <NpgsqlParameter>(ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
parameters = sqlBuilder.GetParameters();
}
sqlBuilder.BuildSelectClause(sql, " RETURNING ", null, null);
sql.Append(";");
if (Parameters != null)
{
parameters.AddRange(Parameters);
}
return(new PostgreSqlCommandExecutionToken(DataSource, "Delete from " + m_Table.Name, sql.ToString(), parameters).CheckDeleteRowCount(Options, ExpectedRowCount));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="SQLiteCommandExecutionToken" /></returns>
public override CommandExecutionToken <SQLiteCommand, SQLiteParameter> Prepare(Materializer <SQLiteCommand, SQLiteParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, m_NewValues, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var sql = new StringBuilder();
if (sqlBuilder.HasReadFields && m_Options.HasFlag(UpdateOptions.ReturnOldValues))
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, " FROM " + m_Table.Name.ToQuotedString());
sql.AppendLine(" WHERE " + m_WhereClause + ";");
}
sql.Append($"UPDATE {m_Table.Name.ToQuotedString()}");
sqlBuilder.BuildSetClause(sql, " SET ", null, null);
sql.Append(" WHERE " + m_WhereClause);
sql.AppendLine(";");
if (sqlBuilder.HasReadFields && !m_Options.HasFlag(UpdateOptions.ReturnOldValues))
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, " FROM " + m_Table.Name.ToQuotedString());
sql.AppendLine(" WHERE " + m_WhereClause + ";");
}
var parameters = sqlBuilder.GetParameters();
if (m_Parameters != null)
{
parameters.AddRange(m_Parameters);
}
return(new SQLiteCommandExecutionToken(DataSource, "Update " + m_Table.Name, sql.ToString(), parameters, lockType: LockType.Write).CheckUpdateRowCount(m_Options, m_ExpectedRowCount));
}
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.ApplyDesiredColumns(materializer.DesiredColumns());
var sql = new StringBuilder();
sql.Append("DELETE FROM " + m_Table.Name.ToQuotedString());
sql.Append(" WHERE " + m_WhereClause);
sqlBuilder.BuildSelectClause(sql, " RETURNING ", null, ";");
var parameters = sqlBuilder.GetParameters();
parameters.AddRange(m_Parameters);
return(new PostgreSqlCommandExecutionToken(DataSource, "Delete from " + m_Table.Name, sql.ToString(), parameters));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns></returns>
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);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
//Support check
if (!Enum.IsDefined(typeof(AccessLimitOption), m_LimitOptions))
{
throw new NotSupportedException($"Access does not support limit option {(LimitOptions)m_LimitOptions}");
}
//Validation
if (m_Take <= 0)
{
throw new InvalidOperationException($"Cannot take {m_Take} rows");
}
//SQL Generation
List <OleDbParameter> parameters;
var sql = new StringBuilder();
string topClause = null;
switch (m_LimitOptions)
{
case AccessLimitOption.RowsWithTies:
topClause = $"TOP {m_Take} ";
break;
}
if (m_SelectClause != null)
{
sql.Append($"SELECT {topClause} {m_SelectClause} ");
}
else
{
sqlBuilder.BuildSelectClause(sql, "SELECT " + topClause, null, null);
}
sql.Append(" FROM " + m_Table.Name.ToQuotedString());
if (m_FilterValue != null)
{
sql.Append(" WHERE (" + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions) + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE (" + m_WhereClause + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
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);
sql.Append(";");
return(new AccessCommandExecutionToken(DataSource, "Query " + m_Table.Name, sql.ToString(), parameters));
}
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyRulesForSelect(DataSource);
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}");
}
//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.TableSampleSystemRows || m_LimitOptions == SqlServerLimitOption.TableSampleSystemPercentage) && m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform random sampling when sorting.");
}
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 <SqlParameter> 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);
}
sql.Append(" FROM " + m_Table.Name.ToQuotedString());
switch (m_LimitOptions)
{
case SqlServerLimitOption.TableSampleSystemRows:
sql.Append($" TABLESAMPLE SYSTEM ({m_Take} ROWS) ");
if (m_Seed.HasValue)
{
sql.Append($"REPEATABLE ({m_Seed}) ");
}
break;
case SqlServerLimitOption.TableSampleSystemPercentage:
sql.Append($" TABLESAMPLE SYSTEM ({m_Take} PERCENT) ");
if (m_Seed.HasValue)
{
sql.Append($"REPEATABLE ({m_Seed}) ");
}
break;
}
if (m_FilterValue != null)
{
sql.Append(" WHERE (" + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions) + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE (" + m_WhereClause + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = SqlBuilder.GetParameters <SqlParameter>(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 SqlParameter("@offset_row_count_expression", m_Skip ?? 0));
if (m_Take.HasValue)
{
sql.Append(" FETCH NEXT @fetch_row_count_expression ROWS ONLY");
parameters.Add(new SqlParameter("@fetch_row_count_expression", m_Take));
}
}
else
{
parameters.Add(new SqlParameter("@fetch_row_count_expression", m_Take));
}
break;
case SqlServerLimitOption.Percentage:
case SqlServerLimitOption.PercentageWithTies:
case SqlServerLimitOption.RowsWithTies:
parameters.Add(new SqlParameter("@fetch_row_count_expression", m_Take));
break;
}
sql.Append(";");
return(new SqlServerCommandExecutionToken(DataSource, "Query " + m_Table.Name, sql.ToString(), parameters));
}
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 (AggregateColumns.IsEmpty)
{
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}");
}
//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.TableSampleSystemRows || m_LimitOptions == SqlServerLimitOption.TableSampleSystemPercentage) && m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform random sampling when sorting.");
}
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
var parameters = new List <OleDbParameter>();
var sql = new StringBuilder();
string?topClause = null;
switch (m_LimitOptions)
{
case SqlServerLimitOption.Rows:
if (!m_SortExpressions.Any())
{
topClause = $"TOP ({m_Take}) ";
}
break;
case SqlServerLimitOption.Percentage:
topClause = $"TOP ({m_Take}) PERCENT ";
break;
case SqlServerLimitOption.PercentageWithTies:
topClause = $"TOP ({m_Take}) PERCENT WITH TIES ";
break;
case SqlServerLimitOption.RowsWithTies:
topClause = $"TOP ({m_Take}) WITH TIES ";
break;
}
if (AggregateColumns.IsEmpty)
{
sqlBuilder.BuildSelectClause(sql, "SELECT " + topClause, null, null);
}
else
{
AggregateColumns.BuildSelectClause(sql, "SELECT ", DataSource, null);
}
sql.Append(" FROM " + m_Table.Name.ToQuotedString());
switch (m_LimitOptions)
{
case SqlServerLimitOption.TableSampleSystemRows:
sql.Append($" TABLESAMPLE SYSTEM ({m_Take} ROWS) ");
if (m_Seed.HasValue)
{
sql.Append($"REPEATABLE ({m_Seed}) ");
}
break;
case SqlServerLimitOption.TableSampleSystemPercentage:
sql.Append($" TABLESAMPLE SYSTEM ({m_Take} PERCENT) ");
if (m_Seed.HasValue)
{
sql.Append($"REPEATABLE ({m_Seed}) ");
}
break;
}
if (m_FilterValue != null)
{
sql.Append(" WHERE (" + sqlBuilder.ApplyAnonymousFilterValue(m_FilterValue, m_FilterOptions) + ")");
sqlBuilder.BuildAnonymousSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters.AddRange(sqlBuilder.GetParameters());
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE (" + m_WhereClause + ")");
sqlBuilder.BuildAnonymousSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = SqlBuilder.GetParameters <OleDbParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
sqlBuilder.BuildAnonymousSoftDeleteClause(sql, " WHERE ", DataSource, null);
parameters.AddRange(sqlBuilder.GetParameters());
}
if (AggregateColumns.HasGroupBy)
{
AggregateColumns.BuildGroupByClause(sql, " GROUP BY ", DataSource, null);
}
if (m_LimitOptions.RequiresSorting() && !m_SortExpressions.Any())
{
if (m_Table.HasPrimaryKey)
{
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_Table.PrimaryKeyColumns.Select(x => new SortExpression(x.SqlName)), null);
}
else if (StrictMode)
{
throw new InvalidOperationException("Limits were requested, but no primary keys were detected. Use WithSorting to supply a sort order or disable strict mode.");
}
}
else
{
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_SortExpressions, null);
}
switch (m_LimitOptions)
{
case SqlServerLimitOption.Rows:
if (m_SortExpressions.Any())
{
sql.Append(" OFFSET ? ROWS ");
parameters.Add(new OleDbParameter("@offset_row_count_expression", m_Skip ?? 0));
if (m_Take.HasValue)
{
sql.Append(" FETCH NEXT ? 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:
break;
}
sql.Append(";");
return(new OleDbCommandExecutionToken(DataSource, "Query " + m_Table.Name, sql.ToString(), parameters));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>
/// ExecutionToken<TCommand>.
/// </returns>
public override CommandExecutionToken <MySqlCommand, MySqlParameter> Prepare(Materializer <MySqlCommand, MySqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyRulesForSelect(DataSource);
if (AggregateColumns.IsEmpty)
{
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
}
//Support check
if (!Enum.IsDefined(typeof(MySqlLimitOption), m_LimitOptions))
{
throw new NotSupportedException($"MySQL 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 != MySqlLimitOption.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 == MySqlLimitOption.RandomSampleRows && m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform random sampling when sorting.");
}
//SQL Generation
List <MySqlParameter> parameters;
var sql = new StringBuilder();
if (AggregateColumns.IsEmpty)
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, null);
}
else
{
AggregateColumns.BuildSelectClause(sql, "SELECT ", DataSource, null);
}
sql.Append(" FROM " + m_Table.Name);
if (m_FilterValue != null)
{
sql.Append(" WHERE (" + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions) + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE (" + m_WhereClause + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = SqlBuilder.GetParameters <MySqlParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
sqlBuilder.BuildSoftDeleteClause(sql, " WHERE ", DataSource, null);
parameters = sqlBuilder.GetParameters();
}
if (AggregateColumns.HasGroupBy)
{
AggregateColumns.BuildGroupByClause(sql, " GROUP BY ", DataSource, null);
}
switch (m_LimitOptions)
{
case MySqlLimitOption.RandomSampleRows:
if (m_Seed.HasValue)
{
sql.Append($" ORDER BY RAND({m_Seed}) ");
}
else
{
sql.Append(" ORDER BY RAND() ");
}
break;
default:
if (m_LimitOptions.RequiresSorting() && !m_SortExpressions.Any())
{
if (m_Table.HasPrimaryKey)
{
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_Table.PrimaryKeyColumns.Select(x => new SortExpression(x.SqlName)), null);
}
else if (StrictMode)
{
throw new InvalidOperationException("Limits were requested, but no primary keys were detected. Use WithSorting to supply a sort order or disable strict mode.");
}
}
else
{
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_SortExpressions, null);
}
break;
}
switch (m_LimitOptions)
{
case MySqlLimitOption.Rows:
if (m_Skip.HasValue)
{
sql.Append(" LIMIT @offset_row_count_expression, @limit_row_count_expression");
parameters.Add(new MySqlParameter("@offset_row_count_expression", m_Skip));
parameters.Add(new MySqlParameter("@limit_row_count_expression", m_Take));
}
else
{
sql.Append(" LIMIT @limit_row_count_expression");
parameters.Add(new MySqlParameter("@limit_row_count_expression", m_Take));
}
break;
}
sql.Append(";");
return(new MySqlCommandExecutionToken(DataSource, "Query " + m_Table.Name, sql.ToString(), parameters));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>
/// ExecutionToken<TCommand>.
/// </returns>
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_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");
}
if ((m_LimitOptions == PostgreSqlLimitOption.TableSampleBernoulliPercentage || m_LimitOptions == PostgreSqlLimitOption.TableSampleSystemPercentage) && m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform random sampling when sorting.");
}
//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);
}
sql.Append(" FROM " + m_Table.Name);
switch (m_LimitOptions)
{
case PostgreSqlLimitOption.TableSampleSystemPercentage:
sql.Append($" TABLESAMPLE SYSTEM ({m_Take}) ");
if (m_Seed.HasValue)
{
sql.Append($"REPEATABLE ({m_Seed}) ");
}
break;
case PostgreSqlLimitOption.TableSampleBernoulliPercentage:
sql.Append($" TABLESAMPLE BERNOULLI ({m_Take}) ");
if (m_Seed.HasValue)
{
sql.Append($"REPEATABLE ({m_Seed}) ");
}
break;
}
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 " + m_Table.Name, sql.ToString(), parameters));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="AccessCommandExecutionToken" /></returns>
public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
SqlBuilder.CheckForOverlaps(m_NewValues, m_WhereArgumentValue, "The same parameter '{0}' appears in both the newValue object and the where clause argument. Rename the parameter in the where expression to resolve the conflict.");
SqlBuilder.CheckForOverlaps(m_NewValues, m_FilterValue, "The same parameter '{0}' appears in both the newValue object and the filter object. Use an update expression or where expression to resolve the conflict.");
SqlBuilder.CheckForOverlaps(m_UpdateArgumentValue, m_WhereArgumentValue, "The same parameter '{0}' appears in both the update expression argument and the where clause argument. Rename the parameter in the where expression to resolve the conflict.");
SqlBuilder.CheckForOverlaps(m_UpdateArgumentValue, m_FilterValue, "The same parameter '{0}' appears in both the update expression argument and the filter object. Use an update expression or where expression to resolve the conflict.");
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, m_NewValues, m_Options, false);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var parameters = new List <OleDbParameter>();
var sql = new StringBuilder("UPDATE " + m_Table.Name.ToQuotedString());
if (m_UpdateExpression == null)
{
sqlBuilder.BuildSetClause(sql, " SET ", null, null);
}
else
{
sql.Append(" SET " + m_UpdateExpression);
parameters.AddRange(SqlBuilder.GetParameters <OleDbParameter>(m_UpdateArgumentValue));
}
if (m_FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyAnonymousFilterValue(m_FilterValue, m_FilterOptions, true));
parameters = sqlBuilder.GetParameters(DataSource);
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE " + m_WhereClause);
parameters.AddRange(sqlBuilder.GetParameters(DataSource));
parameters.AddRange(SqlBuilder.GetParameters <OleDbParameter>(m_WhereArgumentValue));
}
else
{
parameters.AddRange(sqlBuilder.GetParameters(DataSource));
}
sql.Append(";");
if (m_Parameters != null)
{
parameters.AddRange(m_Parameters);
}
var updateCommand = new AccessCommandExecutionToken(DataSource, "Update " + m_Table.Name, sql.ToString(), parameters).CheckUpdateRowCount(m_Options, m_ExpectedRowCount);
updateCommand.ExecutionMode = AccessCommandExecutionMode.NonQuery;
var desiredColumns = materializer.DesiredColumns();
if (desiredColumns == Materializer.NoColumns)
{
return(updateCommand);
}
if (m_Options.HasFlag(UpdateOptions.ReturnOldValues))
{
var result = PrepareRead(desiredColumns);
result.NextCommand = updateCommand;
return(result);
}
else
{
updateCommand.NextCommand = PrepareRead(desiredColumns);
return(updateCommand);
}
}
public override CommandExecutionToken <MySqlCommand, MySqlParameter> Prepare(Materializer <MySqlCommand, MySqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
SqlBuilder.CheckForOverlaps(m_NewValues, m_WhereArgumentValue, "The same parameter '{0}' appears in both the newValue object and the where clause argument. Rename the parameter in the where expression to resolve the conflict.");
SqlBuilder.CheckForOverlaps(m_NewValues, m_FilterValue, "The same parameter '{0}' appears in both the newValue object and the filter object. Use an update expression or where expression to resolve the conflict.");
SqlBuilder.CheckForOverlaps(m_UpdateArgumentValue, m_WhereArgumentValue, "The same parameter '{0}' appears in both the update expression argument and the where clause argument. Rename the parameter in the where expression to resolve the conflict.");
SqlBuilder.CheckForOverlaps(m_UpdateArgumentValue, m_FilterValue, "The same parameter '{0}' appears in both the update expression argument and the filter object. Use an update expression or where expression to resolve the conflict.");
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, m_NewValues, m_Options, false);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var sql = new StringBuilder();
if (sqlBuilder.HasReadFields && m_Options.HasFlag(UpdateOptions.ReturnOldValues))
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, " FROM " + m_Table.Name.ToQuotedString());
if (m_FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions));
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE " + m_WhereClause);
}
sql.AppendLine(";");
}
var parameters = new List <MySqlParameter>();
sql.Append("UPDATE " + m_Table.Name.ToQuotedString());
if (m_UpdateExpression == null)
{
sqlBuilder.BuildSetClause(sql, " SET ", null, null);
}
else
{
sql.Append(" SET " + m_UpdateExpression);
parameters.AddRange(SqlBuilder.GetParameters <MySqlParameter>(m_UpdateArgumentValue));
}
if (m_FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions));
parameters.AddRange(sqlBuilder.GetParameters());
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE " + m_WhereClause);
parameters.AddRange(SqlBuilder.GetParameters <MySqlParameter>(m_WhereArgumentValue));
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
parameters = sqlBuilder.GetParameters();
}
sql.AppendLine(";");
if (sqlBuilder.HasReadFields && !m_Options.HasFlag(UpdateOptions.ReturnOldValues))
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, " FROM " + m_Table.Name.ToQuotedString());
if (m_FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions));
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE " + m_WhereClause);
}
sql.AppendLine(";");
}
if (m_Parameters != null)
{
parameters.AddRange(m_Parameters);
}
return(new MySqlCommandExecutionToken(DataSource, "Update " + m_Table.Name, sql.ToString(), parameters).CheckUpdateRowCount(m_Options, m_ExpectedRowCount));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns></returns>
public override CommandExecutionToken <SQLiteCommand, SQLiteParameter> Prepare(Materializer <SQLiteCommand, SQLiteParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyRulesForSelect(DataSource);
if (m_SelectClause == null)
{
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
}
//Support check
if (!Enum.IsDefined(typeof(SQLiteLimitOption), m_LimitOptions))
{
throw new NotSupportedException($"SQL Server 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 != SQLiteLimitOption.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 == SQLiteLimitOption.RandomSampleRows && m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform a random sampling when sorting.");
}
//SQL Generation
List <SQLiteParameter> parameters;
var sql = new StringBuilder();
if (m_SelectClause != null)
{
sql.Append($"SELECT {m_SelectClause} ");
}
else
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, null);
}
sql.Append(" FROM " + m_Table.Name);
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 <SQLiteParameter>(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 SQLiteLimitOption.Rows:
sql.Append(" LIMIT @fetch_row_count_expression OFFSET @offset_row_count_expression ");
parameters.Add(new SQLiteParameter("@fetch_row_count_expression", m_Take));
parameters.Add(new SQLiteParameter("@offset_row_count_expression", m_Skip ?? 0));
break;
case SQLiteLimitOption.RandomSampleRows:
sql.Append(" ORDER BY RANDOM() LIMIT @fetch_row_count_expression ");
parameters.Add(new SQLiteParameter("@fetch_row_count_expression", m_Take));
break;
}
sql.Append(";");
return(new SQLiteCommandExecutionToken(DataSource, "Query " + m_Table.Name, sql.ToString(), parameters, lockType: LockType.Read));
}
