/// <summary>
/// Combines the specified SQL queries into a single SqlQuery.
/// </summary>
/// <param name="sqlQuery1">The first SQL query to be combined.</param>
/// <param name="sqlQuery2">The second SQL query to be combined.</param>
/// <returns>
/// An <see cref="SqlQuery" /> containing the combined command text and arguments.
/// </returns>
public SqlQuery Combine(SqlQuery sqlQuery1, SqlQuery sqlQuery2)
{
if (sqlQuery1 == null)
{
throw new ArgumentNullException("sqlQuery1");
}
if (sqlQuery2 == null)
{
throw new ArgumentNullException("sqlQuery2");
}
int argumentsCount = sqlQuery1.Arguments.Count + sqlQuery2.Arguments.Count;
var arguments = new SqlArgument[argumentsCount];
Array.Copy(sqlQuery1.ArgumentsArray, 0, arguments, 0, sqlQuery1.Arguments.Count);
if (sqlQuery2.Arguments.Count > 0)
{
Array.Copy(sqlQuery2.ArgumentsArray, 0, arguments, sqlQuery1.Arguments.Count, sqlQuery2.Arguments.Count);
}
var query2CommandText = this.IsStoredProcedureCall(sqlQuery2.CommandText)
? sqlQuery2.CommandText
: SqlUtility.RenumberParameters(sqlQuery2.CommandText, argumentsCount);
var commandText = sqlQuery1.CommandText + this.sqlCharacters.StatementSeparator + Environment.NewLine + query2CommandText;
var combinedQuery = new SqlQuery(commandText, arguments);
combinedQuery.Timeout = Math.Max(sqlQuery1.Timeout, sqlQuery2.Timeout);
return(combinedQuery);
}
public void TrueShouldBeReturned()
{
var sqlArgument1 = new SqlArgument(10, DbType.Int32);
var sqlArgument2 = (object)new SqlArgument(10, DbType.Int32);
Assert.True(sqlArgument1.Equals(sqlArgument2));
}
public void FalseShouldBeReturned()
{
var sqlArgument1 = new SqlArgument(10, DbType.Int32);
var sqlArgument2 = new object();
Assert.False(sqlArgument1.Equals(sqlArgument2));
}
public void FalseShouldBeReturned()
{
var sqlArgument1 = new SqlArgument(10, DbType.Int32);
var sqlArgument2 = new SqlArgument(20, DbType.Int32);
Assert.False(sqlArgument1 == sqlArgument2);
}
public override SqlQuery PageQuery(SqlQuery sqlQuery, PagingOptions pagingOptions)
{
if (sqlQuery == null)
{
throw new ArgumentNullException("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);
var commandText = string.IsNullOrEmpty(sqlString.OrderBy)
? sqlQuery.CommandText + " ORDER BY GETDATE()"
: sqlQuery.CommandText;
var stringBuilder = new StringBuilder(commandText)
.Replace(Environment.NewLine, string.Empty)
.Append(" OFFSET ")
.Append(this.SqlCharacters.GetParameterName(arguments.Length - 2))
.Append(" ROWS FETCH NEXT ")
.Append(this.SqlCharacters.GetParameterName(arguments.Length - 1))
.Append(" ROWS ONLY");
return new SqlQuery(stringBuilder.ToString(), arguments);
}
public void FalseShouldBeReturned()
{
var sqlArgument1 = new SqlArgument(10, DbType.Int32);
var sqlArgument2 = new object();
Assert.False(sqlArgument1.Equals(sqlArgument2));
}
public void TrueShouldBeReturned()
{
var sqlArgument1 = new SqlArgument(10, DbType.Int32);
var sqlArgument2 = (object)new SqlArgument(10, DbType.Int32);
Assert.True(sqlArgument1.Equals(sqlArgument2));
}
public void FalseShouldBeReturned()
{
var sqlArgument1 = new SqlArgument(10, DbType.Int32);
var sqlArgument2 = new SqlArgument(20, DbType.Int32);
Assert.False(sqlArgument1 == sqlArgument2);
}
public void TrueShouldBeReturned()
{
var sqlArgument1 = new SqlArgument(10, DbType.Int32);
var sqlArgument2 = new SqlArgument(20, DbType.Int32);
Assert.True(sqlArgument1 != sqlArgument2);
}
public override SqlQuery PageQuery(SqlQuery sqlQuery, PagingOptions pagingOptions)
{
if (sqlQuery == null)
{
throw new ArgumentNullException("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);
var commandText = string.IsNullOrEmpty(sqlString.OrderBy)
? sqlQuery.CommandText + " ORDER BY CURRENT_TIMESTAMP"
: sqlQuery.CommandText;
var stringBuilder = new StringBuilder(commandText)
.Replace(Environment.NewLine, string.Empty)
.Append(" OFFSET ")
.Append(this.SqlCharacters.GetParameterName(arguments.Length - 2))
.Append(" ROWS FETCH NEXT ")
.Append(this.SqlCharacters.GetParameterName(arguments.Length - 1))
.Append(" ROWS ONLY");
return(new SqlQuery(stringBuilder.ToString(), arguments));
}
protected override void BuildParameter(IDbDataParameter parameter, string parameterName, SqlArgument sqlArgument)
{
if (parameter == null)
{
throw new ArgumentNullException("parameter");
}
if (sqlArgument.DbType != DbType.Time)
{
// Work around for a bug in SqlClient where it thinks DbType.Time is a MetaType.MetaDateTime.
// Do not set the DbType, the SqlParameter will figure it out by reflecting over the value type.
parameter.DbType = sqlArgument.DbType;
}
parameter.Direction = ParameterDirection.Input;
parameter.ParameterName = parameterName;
parameter.Value = sqlArgument.Value ?? DBNull.Value;
}
public override SqlQuery PageQuery(SqlQuery sqlQuery, PagingOptions pagingOptions)
{
if (sqlQuery == null)
{
throw new ArgumentNullException("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 stringBuilder = new StringBuilder(sqlQuery.CommandText)
.Replace(Environment.NewLine, string.Empty)
.Append(" LIMIT ")
.Append(this.SqlCharacters.GetParameterName(arguments.Length - 2))
.Append(',')
.Append(this.SqlCharacters.GetParameterName(arguments.Length - 1));
return new SqlQuery(stringBuilder.ToString(), arguments);
}
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));
}
public override SqlQuery PageQuery(SqlQuery sqlQuery, PagingOptions pagingOptions)
{
if (sqlQuery == null)
{
throw new ArgumentNullException("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);
var whereClause = !string.IsNullOrEmpty(sqlString.Where) ? " WHERE " + sqlString.Where : string.Empty;
var orderByClause = !string.IsNullOrEmpty(sqlString.OrderBy) ? sqlString.OrderBy : "(SELECT NULL)";
var stringBuilder = new StringBuilder(sqlQuery.CommandText.Length * 2)
.AppendFormat(CultureInfo.InvariantCulture, "SELECT * FROM (SELECT {0},ROW_NUMBER() OVER(ORDER BY {1}) AS RowNumber FROM {2}{3}) AS [MicroLitePagedResults]", sqlString.Select, orderByClause, sqlString.From, whereClause)
.AppendFormat(CultureInfo.InvariantCulture, " WHERE (RowNumber >= {0} AND RowNumber <= {1})", this.SqlCharacters.GetParameterName(arguments.Length - 2), this.SqlCharacters.GetParameterName(arguments.Length - 1));
return new SqlQuery(stringBuilder.ToString(), arguments);
}
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));
}
public WhenCallingGetHashCodeAndTheValueIsNull()
{
this.sqlArgument = new SqlArgument(this.value, this.dbType);
}
public WhenCallingGetHashCode()
{
this.sqlArgument = new SqlArgument(this.value, this.dbType);
}
public void TrueShouldBeReturned()
{
var sqlArgument1 = new SqlArgument(10, DbType.Int32);
var sqlArgument2 = new SqlArgument(20, DbType.Int32);
Assert.True(sqlArgument1 != sqlArgument2);
}
/// <summary>
/// Builds the the IDbDataParameter using the specified name and value.
/// </summary>
/// <param name="parameter">The parameter to build.</param>
/// <param name="parameterName">The name for the parameter.</param>
/// <param name="sqlArgument">The <see cref="SqlArgument"/> for the parameter.</param>
protected virtual void BuildParameter(IDbDataParameter parameter, string parameterName, SqlArgument sqlArgument)
{
if (parameter == null)
{
throw new ArgumentNullException("parameter");
}
parameter.DbType = sqlArgument.DbType;
parameter.Direction = ParameterDirection.Input;
parameter.ParameterName = parameterName;
parameter.Value = sqlArgument.Value ?? DBNull.Value;
}
protected override void BuildParameter(IDbDataParameter parameter, string parameterName, SqlArgument sqlArgument)
{
if (parameter == null)
{
throw new ArgumentNullException("parameter");
}
if (sqlArgument.DbType != DbType.Time)
{
// Work around for a bug in SqlClient where it thinks DbType.Time is a MetaType.MetaDateTime.
// Do not set the DbType, the SqlParameter will figure it out by reflecting over the value type.
parameter.DbType = sqlArgument.DbType;
}
parameter.Direction = ParameterDirection.Input;
parameter.ParameterName = parameterName;
parameter.Value = sqlArgument.Value ?? DBNull.Value;
}
/// <summary>
/// Builds the the IDbDataParameter using the specified name and value.
/// </summary>
/// <param name="parameter">The parameter to build.</param>
/// <param name="parameterName">The name for the parameter.</param>
/// <param name="sqlArgument">The <see cref="SqlArgument"/> for the parameter.</param>
protected virtual void BuildParameter(IDbDataParameter parameter, string parameterName, SqlArgument sqlArgument)
{
if (parameter == null)
{
throw new ArgumentNullException("parameter");
}
parameter.DbType = sqlArgument.DbType;
parameter.Direction = ParameterDirection.Input;
parameter.ParameterName = parameterName;
parameter.Value = sqlArgument.Value ?? DBNull.Value;
}
public WhenCallingGetHashCodeAndTheValueIsNull()
{
this.sqlArgument = new SqlArgument(this.value, this.dbType);
}
/// <summary>
/// Combines the specified SQL queries into a single SqlQuery.
/// </summary>
/// <param name="sqlQuery1">The first SQL query to be combined.</param>
/// <param name="sqlQuery2">The second SQL query to be combined.</param>
/// <returns>
/// An <see cref="SqlQuery" /> containing the combined command text and arguments.
/// </returns>
public SqlQuery Combine(SqlQuery sqlQuery1, SqlQuery sqlQuery2)
{
if (sqlQuery1 == null)
{
throw new ArgumentNullException("sqlQuery1");
}
if (sqlQuery2 == null)
{
throw new ArgumentNullException("sqlQuery2");
}
int argumentsCount = sqlQuery1.Arguments.Count + sqlQuery2.Arguments.Count;
var arguments = new SqlArgument[argumentsCount];
Array.Copy(sqlQuery1.ArgumentsArray, 0, arguments, 0, sqlQuery1.Arguments.Count);
if (sqlQuery2.Arguments.Count > 0)
{
Array.Copy(sqlQuery2.ArgumentsArray, 0, arguments, sqlQuery1.Arguments.Count, sqlQuery2.Arguments.Count);
}
var query2CommandText = this.IsStoredProcedureCall(sqlQuery2.CommandText)
? sqlQuery2.CommandText
: SqlUtility.RenumberParameters(sqlQuery2.CommandText, argumentsCount);
var commandText = sqlQuery1.CommandText + this.sqlCharacters.StatementSeparator + Environment.NewLine + query2CommandText;
var combinedQuery = new SqlQuery(commandText, arguments);
combinedQuery.Timeout = Math.Max(sqlQuery1.Timeout, sqlQuery2.Timeout);
return combinedQuery;
}
public WhenCallingGetHashCode()
{
this.sqlArgument = new SqlArgument(this.value, this.dbType);
}
