public override SqlQuery PageQuery(SqlQuery sqlQuery, PagingOptions pagingOptions)
{
if (sqlQuery is null)
{
throw new ArgumentNullException(nameof(sqlQuery));
}
var arguments = new SqlArgument[sqlQuery.Arguments.Count + 2];
Array.Copy(sqlQuery.ArgumentsArray, 0, arguments, 0, sqlQuery.Arguments.Count);
arguments[arguments.Length - 2] = new SqlArgument(pagingOptions.Offset, DbType.Int32);
arguments[arguments.Length - 1] = new SqlArgument(pagingOptions.Count, DbType.Int32);
var sqlString = SqlString.Parse(sqlQuery.CommandText, Clauses.OrderBy);
string commandText = string.IsNullOrEmpty(sqlString.OrderBy)
? sqlQuery.CommandText + " ORDER BY CURRENT_TIMESTAMP"
: sqlQuery.CommandText;
StringBuilder stringBuilder = new StringBuilder(commandText)
.Replace(Environment.NewLine, string.Empty)
.Append(" OFFSET ")
.Append(SqlCharacters.GetParameterName(arguments.Length - 2))
.Append(" ROWS FETCH NEXT ")
.Append(SqlCharacters.GetParameterName(arguments.Length - 1))
.Append(" ROWS ONLY");
return(new SqlQuery(stringBuilder.ToString(), arguments));
}
protected override string BuildInsertCommandText(IObjectInfo objectInfo)
{
if (objectInfo is null)
{
throw new ArgumentNullException(nameof(objectInfo));
}
string commandText = base.BuildInsertCommandText(objectInfo);
if (objectInfo.TableInfo.IdentifierStrategy == IdentifierStrategy.Sequence)
{
int firstParenthesisIndex = commandText.IndexOf('(') + 1;
commandText = commandText.Insert(
firstParenthesisIndex,
SqlCharacters.EscapeSql(objectInfo.TableInfo.IdentifierColumn.ColumnName) + ",");
int secondParenthesisIndex = commandText.IndexOf('(', firstParenthesisIndex) + 1;
commandText = commandText.Insert(
secondParenthesisIndex,
"GEN_ID(" + objectInfo.TableInfo.IdentifierColumn.SequenceName + ", 1),");
}
if (objectInfo.TableInfo.IdentifierStrategy != IdentifierStrategy.Assigned)
{
commandText += " RETURNING " + objectInfo.TableInfo.IdentifierColumn.ColumnName;
}
return(commandText);
}
public void CurrentIsVisibleAccrossThreads()
{
var sqlCharacters = new TestSqlCharacters();
SqlCharacters actual = null;
var handle = new AutoResetEvent(false);
var thread1 = new Thread(() =>
{
SqlCharacters.Current = sqlCharacters;
handle.Set();
});
var thread2 = new Thread(() =>
{
actual = SqlCharacters.Current;
handle.Set();
});
thread1.Start();
handle.WaitOne();
thread2.Start();
handle.WaitOne();
thread1.Abort();
thread2.Abort();
Assert.Same(sqlCharacters, actual);
}
protected override string BuildInsertCommandText(IObjectInfo objectInfo)
{
if (objectInfo is null)
{
throw new ArgumentNullException(nameof(objectInfo));
}
string commandText = base.BuildInsertCommandText(objectInfo);
if (objectInfo.TableInfo.IdentifierStrategy == IdentifierStrategy.Sequence)
{
commandText = "DECLARE @@id " + GetSqlType(objectInfo.TableInfo.IdentifierColumn) + ";"
+ "SELECT @@id = NEXT VALUE FOR " + objectInfo.TableInfo.IdentifierColumn.SequenceName + ";"
+ commandText;
int firstParenthesisIndex = commandText.IndexOf('(') + 1;
commandText = commandText.Insert(
firstParenthesisIndex,
SqlCharacters.EscapeSql(objectInfo.TableInfo.IdentifierColumn.ColumnName) + ",");
int secondParenthesisIndex = commandText.IndexOf('(', firstParenthesisIndex) + 1;
commandText = commandText.Insert(
secondParenthesisIndex,
"@@id,");
}
return(commandText);
}
internal StoredProcedureSqlBuilder(SqlCharacters sqlCharacters, string procedureName)
: base(sqlCharacters)
{
this.InnerSql.Append(sqlCharacters.StoredProcedureInvocationCommand)
.Append(' ')
.Append(procedureName)
.Append(' ');
}
internal StoredProcedureSqlBuilder(SqlCharacters sqlCharacters, string procedureName)
: base(sqlCharacters)
{
this.InnerSql.Append(sqlCharacters.StoredProcedureInvocationCommand)
.Append(' ')
.Append(procedureName)
.Append(' ');
}
/// <summary>
/// Initialises a new instance of the <see cref="SelectSqlBuilder"/> class with an optional list of columns to select.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
/// <param name="column">The column.</param>
internal SelectSqlBuilder(SqlCharacters sqlCharacters, string column)
: base(sqlCharacters)
{
this.InnerSql.Append("SELECT ");
if (column != null)
{
this.InnerSql.Append(this.SqlCharacters.EscapeSql(column));
}
}
/// <summary>
/// Initialises a new instance of the <see cref="SelectSqlBuilder"/> class with an optional list of columns to select.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
/// <param name="column">The column.</param>
internal SelectSqlBuilder(SqlCharacters sqlCharacters, string column)
: base(sqlCharacters)
{
this.InnerSql.Append("SELECT ");
if (column != null)
{
this.InnerSql.Append(this.SqlCharacters.EscapeSql(column));
}
}
/// <summary>
/// Creates a new select query with a single specified column or '*'.
/// </summary>
/// <param name="column">The column (or wildcard *) to be included in the query.</param>
/// <returns>The next step in the fluent sql builder.</returns>
/// <example>
/// Option 1, enter * followed by a table name.
/// <code>
/// var query = SqlBuilder.Select("*").From("Customers")...
///
/// // SELECT * FROM Customers
/// // will be generated
/// </code>
/// </example>
/// <example>
/// Option 2, enter * followed by a type in From, all mapped columns will be specified in the SQL.
/// <code>
/// var query = SqlBuilder.Select("*").From(typeof(Customer))...
///
/// // SELECT CustomerId, Name, DoB FROM Customers
/// // will be generated
/// </code>
/// </example>
public static IFunctionOrFrom Select(string column)
{
SqlCharacters sqlCharacters = SqlCharacters.Current;
if (column == sqlCharacters.SelectWildcard)
{
return(new SelectSqlBuilder(sqlCharacters));
}
return(new SelectSqlBuilder(sqlCharacters, column));
}
public IWhereSingleColumn AndWhere(string column)
{
if (string.IsNullOrEmpty(column))
{
throw new ArgumentException(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("column"));
}
Operand = " AND";
WhereColumnName = SqlCharacters.EscapeSql(column);
return(this);
}
public void CurrentIsVisibleAccrossTasks()
{
var sqlCharacters = new TestSqlCharacters();
SqlCharacters.Current = sqlCharacters;
SqlCharacters actual = null;
System.Threading.Tasks.Task.Factory.StartNew(
() => actual = SqlCharacters.Current,
System.Threading.Tasks.TaskCreationOptions.LongRunning).Wait();
Assert.Same(sqlCharacters, actual);
}
public IWhereSingleColumn Where(string column)
{
if (string.IsNullOrEmpty(column))
{
throw new ArgumentException(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("column"));
}
WhereColumnName = SqlCharacters.EscapeSql(column);
if (!AddedWhere)
{
InnerSql.Append(" WHERE");
AddedWhere = true;
}
return(this);
}
/// <summary>
/// Initialises a new instance of the <see cref="SelectSqlBuilder"/> class with an optional list of columns to select.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
/// <param name="columns">The columns.</param>
internal SelectSqlBuilder(SqlCharacters sqlCharacters, params string[] columns)
: base(sqlCharacters)
{
this.InnerSql.Append("SELECT ");
if (columns != null)
{
for (int i = 0; i < columns.Length; i++)
{
if (i > 0)
{
this.InnerSql.Append(',');
}
this.InnerSql.Append(this.SqlCharacters.EscapeSql(columns[i]));
}
}
}
/// <summary>
/// Initialises a new instance of the <see cref="SelectSqlBuilder"/> class with an optional list of columns to select.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
/// <param name="columns">The columns.</param>
internal SelectSqlBuilder(SqlCharacters sqlCharacters, params string[] columns)
: base(sqlCharacters)
{
this.InnerSql.Append("SELECT ");
if (columns != null)
{
for (int i = 0; i < columns.Length; i++)
{
if (i > 0)
{
this.InnerSql.Append(',');
}
this.InnerSql.Append(this.SqlCharacters.EscapeSql(columns[i]));
}
}
}
public void CurrentIsVisibleAccrossThreadPools()
{
var sqlCharacters = new TestSqlCharacters();
SqlCharacters.Current = sqlCharacters;
SqlCharacters actual = null;
var handle = new ManualResetEvent(false);
ThreadPool.QueueUserWorkItem(_ =>
{
actual = SqlCharacters.Current;
handle.Set();
});
handle.WaitOne();
Assert.Same(sqlCharacters, actual);
}
public override SqlQuery PageQuery(SqlQuery sqlQuery, PagingOptions pagingOptions)
{
if (sqlQuery is null)
{
throw new ArgumentNullException(nameof(sqlQuery));
}
var arguments = new SqlArgument[sqlQuery.Arguments.Count + 2];
Array.Copy(sqlQuery.ArgumentsArray, 0, arguments, 0, sqlQuery.Arguments.Count);
arguments[arguments.Length - 2] = new SqlArgument(pagingOptions.Offset, DbType.Int32);
arguments[arguments.Length - 1] = new SqlArgument(pagingOptions.Count, DbType.Int32);
StringBuilder stringBuilder = new StringBuilder(sqlQuery.CommandText)
.Replace(Environment.NewLine, string.Empty)
.Append(" LIMIT ")
.Append(SqlCharacters.GetParameterName(arguments.Length - 2))
.Append(',')
.Append(SqlCharacters.GetParameterName(arguments.Length - 1));
return(new SqlQuery(stringBuilder.ToString(), arguments));
}
/// <summary>
/// Initialises a new instance of the <see cref="SqlDialect"/> class.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
protected SqlDialect(SqlCharacters sqlCharacters)
{
this.sqlCharacters = sqlCharacters;
}
/// <summary>
/// Initialises a new instance of the <see cref="DeleteSqlBuilder"/> class with the starting command text 'DELETE FROM '.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
internal DeleteSqlBuilder(SqlCharacters sqlCharacters)
: base(sqlCharacters)
=> InnerSql.Append("DELETE");
/// <summary>
/// Initialises a new instance of the <see cref="SqlDialect"/> class.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
protected SqlDialect(SqlCharacters sqlCharacters)
{
this.sqlCharacters = sqlCharacters;
}
/// <summary>
/// Initialises a new instance of the <see cref="UpdateSqlBuilder"/> class with the starting command text 'UPDATE '.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
internal UpdateSqlBuilder(SqlCharacters sqlCharacters)
: base(sqlCharacters)
{
this.InnerSql.Append("UPDATE ");
}
/// <summary>
/// Initialises a new instance of the <see cref="UpdateSqlBuilder"/> class with the starting command text 'UPDATE '.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
internal UpdateSqlBuilder(SqlCharacters sqlCharacters)
: base(sqlCharacters)
{
this.InnerSql.Append("UPDATE ");
}
/// <summary>
/// Initialises a new instance of the <see cref="SqlBuilderBase"/> class.
/// </summary>
/// <param name="sqlCharacters">The SQL characters for the builder.</param>
protected SqlBuilderBase(SqlCharacters sqlCharacters)
{
this.sqlCharacters = sqlCharacters;
}
protected WriteSqlBuilderBase(SqlCharacters sqlCharacters)
: base(sqlCharacters)
{
}
/// <summary>
/// Initialises a new instance of the <see cref="InsertSqlBuilder"/> class with the starting command text 'INSERT INTO '.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
internal InsertSqlBuilder(SqlCharacters sqlCharacters)
: base(sqlCharacters)
{
this.InnerSql.Append("INSERT INTO ");
}
/// <summary>
/// Initialises a new instance of the <see cref="DbDriver" /> class.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
protected DbDriver(SqlCharacters sqlCharacters)
{
this.sqlCharacters = sqlCharacters;
}
/// <summary>
/// Initialises a new instance of the <see cref="DeleteSqlBuilder"/> class with the starting command text 'DELETE FROM '.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
internal DeleteSqlBuilder(SqlCharacters sqlCharacters)
: base(sqlCharacters)
{
this.InnerSql.Append("DELETE FROM ");
}
/// <summary>
/// Initialises a new instance of the <see cref="SqlBuilderBase"/> class.
/// </summary>
/// <param name="sqlCharacters">The SQL characters for the builder.</param>
protected SqlBuilderBase(SqlCharacters sqlCharacters)
{
this.sqlCharacters = sqlCharacters;
}
/// <summary> /// Initialises a new instance of the <see cref="SqlBuilderBase"/> class. /// </summary> /// <param name="sqlCharacters">The SQL characters for the builder.</param> protected SqlBuilderBase(SqlCharacters sqlCharacters) => SqlCharacters = sqlCharacters;
/// <summary>
/// Initialises a new instance of the <see cref="SelectSqlBuilder"/> class with the starting command text 'SELECT *'.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
internal SelectSqlBuilder(SqlCharacters sqlCharacters)
: base(sqlCharacters)
{
this.InnerSql.Append("SELECT *");
}
/// <summary> /// Initialises a new instance of the <see cref="SqlDialect"/> class. /// </summary> /// <param name="sqlCharacters">The SQL characters.</param> protected SqlDialect(SqlCharacters sqlCharacters) => SqlCharacters = sqlCharacters ?? throw new ArgumentNullException(nameof(sqlCharacters));
/// <summary>
/// Initialises a new instance of the <see cref="InsertSqlBuilder"/> class with the starting command text 'INSERT INTO '.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
internal InsertSqlBuilder(SqlCharacters sqlCharacters)
: base(sqlCharacters)
=> InnerSql.Append("INSERT INTO ");
/// <summary>
/// Initialises a new instance of the <see cref="SelectSqlBuilder"/> class with the starting command text 'SELECT *'.
/// </summary>
/// <param name="sqlCharacters">The SQL characters.</param>
internal SelectSqlBuilder(SqlCharacters sqlCharacters)
: base(sqlCharacters)
{
this.InnerSql.Append("SELECT *");
}
public override SqlQuery PageQuery(SqlQuery sqlQuery, PagingOptions pagingOptions)
{
if (sqlQuery is null)
{
throw new ArgumentNullException(nameof(sqlQuery));
}
var arguments = new SqlArgument[sqlQuery.Arguments.Count + 2];
Array.Copy(sqlQuery.ArgumentsArray, 0, arguments, 0, sqlQuery.Arguments.Count);
arguments[arguments.Length - 2] = new SqlArgument(pagingOptions.Offset + 1, DbType.Int32);
arguments[arguments.Length - 1] = new SqlArgument(pagingOptions.Offset + pagingOptions.Count, DbType.Int32);
var sqlString = SqlString.Parse(sqlQuery.CommandText, Clauses.Select | Clauses.From | Clauses.Where | Clauses.OrderBy);
string whereClause = !string.IsNullOrEmpty(sqlString.Where) ? " WHERE " + sqlString.Where : string.Empty;
string orderByClause = !string.IsNullOrEmpty(sqlString.OrderBy) ? sqlString.OrderBy : "(SELECT NULL)";
StringBuilder stringBuilder = new StringBuilder(sqlQuery.CommandText.Length * 2)
.AppendFormat(CultureInfo.InvariantCulture, "SELECT * FROM (SELECT {0},ROW_NUMBER() OVER(ORDER BY {1}) AS MicroLiteRowNumber FROM {2}{3}) AS [MicroLitePagedResults]", sqlString.Select, orderByClause, sqlString.From, whereClause)
.AppendFormat(CultureInfo.InvariantCulture, " WHERE (MicroLiteRowNumber >= {0} AND MicroLiteRowNumber <= {1})", SqlCharacters.GetParameterName(arguments.Length - 2), SqlCharacters.GetParameterName(arguments.Length - 1));
return(new SqlQuery(stringBuilder.ToString(), arguments));
}
/// <summary> /// Initialises a new instance of the <see cref="DbDriver" /> class. /// </summary> /// <param name="sqlCharacters">The SQL characters.</param> protected DbDriver(SqlCharacters sqlCharacters) => SqlCharacters = sqlCharacters ?? throw new ArgumentNullException(nameof(sqlCharacters));
