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)); }