public void TestSelectDbOperation_010_SelectWithIsNullOperator()
{
// Initializes the Select Statement
SelectDbOperation select = new SelectDbOperation(new Table("testtable", "dbo", "0"))
{
Where = new WhereCollection()
{
new FilterExpression(new Field("field1"), FieldOperators.Equal, "Test", FieldAndOr.And),
new FilterExpression(new Field("field2"), FieldOperators.GreaterThan, 15, FieldAndOr.And),
new FilterExpression(new Field("field3"), FieldOperators.LessThan, 15, FieldAndOr.And),
new FilterExpression(new Field("field4"), FieldOperators.IsNull, null, FieldAndOr.None),
}
};
string statement = myDialect.Select(select);
string expectedStatement = "SELECT *\r\n" +
" FROM [dbo].[testtable] T_0\r\n" +
" WHERE\r\n" +
" [field1]='Test' AND\r\n" +
" [field2]>15 AND\r\n" +
" [field3]<15 AND\r\n" +
" [field4] IS NULL\r\n";
// Assertion
Assert.AreEqual <string>(expectedStatement, statement);
}
public void TestSelectDbOperation_006_SelectWithFieldAndGroupBy()
{
// Initializes the Select Statement
SelectDbOperation select = new SelectDbOperation(myDialect, new Table("testtable", "dbo", "0"))
{
SelectFields = new List <DisplayField>()
{
new DisplayField("field1", "0", "field1_alternate"),
new DisplayField("field2", "0"),
new AggregateField(AggregateFunctions.Max, "field3", "0"),
new AggregateField(AggregateFunctions.Min, "field4"),
new AggregateField(AggregateFunctions.CountDistinct, "field5", "0"),
new AggregateField(AggregateFunctions.Average, "field6", "0"),
new AggregateField(AggregateFunctions.Sum, "field7"),
},
GroupBy = true
};
string statement = select.GetStatement();
string expectedStatement = "SELECT \r\n" +
" T_0.[field1] field1_alternate,\r\n" +
" T_0.[field2],\r\n" +
" MAX(T_0.[field3]),\r\n" +
" MIN([field4]),\r\n" +
" COUNT(DISTINCT T_0.[field5]),\r\n" +
" AVG(T_0.[field6]),\r\n" +
" SUM([field7])\r\n" +
" FROM [dbo].[testtable] T_0\r\n" +
"GROUP BY\r\n" +
" T_0.[field1],\r\n" +
" T_0.[field2]\r\n";
// Assertion
Assert.AreEqual <string>(expectedStatement, statement);
}
public void TestSelectDbOperation_011_SelectComplexWithAllOptions()
{
// Initializes the Select Statement
SelectDbOperation select = new SelectDbOperation(new Table("testtable", "dbo", "0"))
{
SelectFields = new FieldCollection()
{
new DisplayField("field1", "0", "field1_alternate"),
new DisplayField("field2", "0"),
new AggregateField(Aggregates.Max, "field3", "0"),
new AggregateField(Aggregates.Min, "field4"),
new AggregateField(Aggregates.CountDistinct, "field5", "0"),
new AggregateField(Aggregates.Average, "field6", "0"),
new AggregateField(Aggregates.Sum, "field7"),
},
Where = new WhereCollection()
{
new FilterExpression(new Field("field1"), FieldOperators.Equal, "Test", FieldAndOr.And),
new FilterExpression(new Field("field2"), FieldOperators.GreaterThan, 15, FieldAndOr.And),
new FilterExpression(new Field("field3"), FieldOperators.LessThan, 15, FieldAndOr.And),
new FilterExpression(new Field("field4"), FieldOperators.Between, new string[] { "A01", "A02" }, FieldAndOr.And),
new FilterExpression(new Field("field5"), FieldOperators.IsNull, null, FieldAndOr.And),
new FilterExpression(new Field("field6"), FieldOperators.NotIsNull, null, FieldAndOr.And),
},
GroupBy = true,
OrderBy = new OrderByCollection()
{
new OrderByField("field1", "0", SortModes.Ascending),
new OrderByField("field2", SortModes.Descending),
},
};
string statement = myDialect.Select(select);
string expectedStatement = "SELECT \r\n" +
" T_0.[field1] field1_alternate,\r\n" +
" T_0.[field2],\r\n" +
" MAX(T_0.[field3]),\r\n" +
" MIN([field4]),\r\n" +
" COUNT(DISTINCT T_0.[field5]),\r\n" +
" AVG(T_0.[field6]),\r\n" +
" SUM([field7])\r\n" +
" FROM [dbo].[testtable] T_0\r\n" +
" WHERE\r\n" +
" [field1]='Test' AND\r\n" +
" [field2]>15 AND\r\n" +
" [field3]<15 AND\r\n" +
" [field4] BETWEEN 'A01' AND 'A02' AND\r\n" +
" [field5] IS NULL AND\r\n" +
" [field6] IS NOT NULL\r\n" +
"GROUP BY\r\n" +
" T_0.[field1],\r\n" +
" T_0.[field2]\r\n" +
"ORDER BY\r\n" +
" T_0.[field1],\r\n" +
" [field2] DESC\r\n";
// Assertion
Assert.AreEqual <string>(expectedStatement, statement);
}
/// <summary>
/// Returns a Data Reader based on the input sql command
/// </summary>
/// <param name="selectDbOperation">A <see cref="SelectDbOperation"/> with the statement to be executed</param>
/// <param name="connection">Reference to an existing <see cref="IDbConnection"/> to be used. If nothing is informed, the system will create one.</param>
/// <param name="transaction">Reference to an existing <see cref="IDbTransaction"/> to be used. If nothing is informed, the system will not consider transaction.</param>
/// <remarks>The Database Connection is closed once you call the <see cref="IDataReader.Close"/> method</remarks>
/// <returns>A <see cref="IDataReader"/> object to iterate thru the results.</returns>
public IDataReader ExecuteReader(SelectDbOperation selectDbOperation, IDbConnection connection = null, IDbTransaction transaction = null)
{
if (Dialect == null)
{
throw new Exception("Unable to identify a dialect to communicate with the database");
}
return(ExecuteReader(Dialect.Select(selectDbOperation)));
}
public void TestSelectDbOperation_001_SimpleSelect()
{
// Initializes the Select Statement
SelectDbOperation select = new SelectDbOperation(myDialect, new Table("testtable", "dbo", "0"));
string statement = select.GetStatement();
string expectedStatement = "SELECT *\r\n" +
" FROM [dbo].[testtable] T_0\r\n";
// Assertion
Assert.AreEqual <string>(expectedStatement, statement);
}
public void TestSelectDbOperation_005_SelectWithMultipleTables()
{
// Initializes the Select Statement
SelectDbOperation select = new SelectDbOperation(myDialect, new Table("testtable", "dbo", "0"), new Table("testtable2", "dbo", "1"));
string statement = select.GetStatement();
string expectedStatement = "SELECT *\r\n" +
" FROM \r\n" +
" [dbo].[testtable] T_0,\r\n" +
" [dbo].[testtable2] T_1\r\n";
// Assertion
Assert.AreEqual <string>(expectedStatement, statement);
}
/// <summary>
/// Executes a query that would return only one row and one column
/// </summary>
/// <param name="selectDbOperation">The <see cref="SelectDbOperation"/> to be executed</param>
/// <param name="connection">Reference to an existing <see cref="IDbConnection"/> to be used. If nothing is informed, the system will create one.</param>
/// <param name="transaction">Reference to an existing <see cref="IDbTransaction"/> to be used. If nothing is informed, the system will not consider transaction.</param>
/// <returns>A <see cref="object"/> containing the results</returns>
public object ExecuteScalar(SelectDbOperation selectDbOperation, IDbConnection connection = null, IDbTransaction transaction = null)
{
// Make sure configuration is valid
ValidateConfiguration();
// Variable to hold the connection
IDbConnection internalConnection = null;
try
{
// Use existing connection or create new
internalConnection = (connection ?? GetConnection());
using (IDbCommand command = internalConnection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = Dialect.Select(selectDbOperation);
command.CommandText = Dialect.GetFinalStatement(command.CommandText);
try
{
return(command.ExecuteScalar());
}
catch (Exception e)
{
throw new DatabaseOperationException(null, e, command.CommandText);
}
}
}
catch
{
throw;
}
finally
{
if (connection == null)
{
// Make sure to close the internal connection, if it was created on demand
if (internalConnection != null)
{
if ((internalConnection.State != ConnectionState.Broken) &&
(internalConnection.State != ConnectionState.Closed))
{
internalConnection.Close();
}
}
}
}
}
/// <summary>
/// Perform a Select Count into a table using specific filters
/// </summary>
/// <param name="dbContext">Reference to the Database context</param>
/// <param name="tableName">The table to search for data</param>
/// <param name="dbConnection">Reference to an existing database connection</param>
/// <param name="dbTransaction">Reference to an existing database transaction</param>
/// <param name="filterFields">Array of filter fields</param>
/// <returns>A <see cref="int"/> containing the Count of rows for a given select</returns>
public static int SelectCount(this IDbContext dbContext, string tableName, IDbConnection dbConnection, IDbTransaction dbTransaction, params FilterField[] filterFields)
{
var select = new SelectDbOperation(tableName)
{
SelectFields = new FieldCollection()
{
new AggregateField(Aggregates.Count, "0")
}
};
select.Where.AppendQueryFilters(filterFields, false);
return((int)dbContext.ExecuteScalar(select, dbConnection, dbTransaction));
}
/// <summary>
/// Returns instances of the <typeparamref name="TModel"/> based on pre-defined filters
/// </summary>
/// <param name="filters">A <see cref="List{T}"/> of <see cref="FilterField"/> containing the filters</param>
/// <param name="dbConnection">Reference to an existing database connection</param>
/// <param name="dbTransaction">Reference to an existing database transaction</param>
/// <returns>A <see cref="List{T}"/> of <typeparamref name="TModel"/> with the objects returned from the database</returns>
public virtual List <TModel> Get(List <FilterField> filters, IDbConnection dbConnection, IDbTransaction dbTransaction)
{
// Ensure Model is Valid
if (!IsModelValid)
{
throw new Exception("Model is invalid");
}
// List of TModel instance
var models = new List <TModel>();
// Retrieve List of TModel
var select = new SelectDbOperation(DatabaseTableAttribute.Name)
{
Where = new WhereCollection()
};
select.Where.AppendQueryFilters(filters, false);
using (var reader = DbContext.ExecuteReader(select))
{
while (reader.Read())
{
// Initializes the instance of the TModel
var model = Activator.CreateInstance(typeof(TModel));
// Load Data
foreach (var fieldInfo in Fields)
{
fieldInfo.Property.SetValue(model, reader.GetValueOrDefault(fieldInfo.Attribute.Name, fieldInfo.Attribute.Type));
}
// Make sure record can be added
if (OnBeforeRecordLoad((TModel)model, dbConnection, dbTransaction))
{
// Add to the resultset
models.Add((TModel)model);
// Calls the method to process a record that has already been inserted
OnAfterRecordLoad((TModel)model, dbConnection, dbTransaction);
}
}
}
return(models);
}
public void TestSelectDbOperation_004_SelectCountStart()
{
// Initializes the Select Statement
SelectDbOperation select = new SelectDbOperation(myDialect, new Table("testtable", "dbo", "0"))
{
SelectFields = new List <DisplayField>()
{
new AggregateField(AggregateFunctions.Count, "*")
}
};
string statement = select.GetStatement();
string expectedStatement = "SELECT \r\n" +
" COUNT(*)\r\n" +
" FROM [dbo].[testtable] T_0\r\n";
// Assertion
Assert.AreEqual <string>(expectedStatement, statement);
}
public void TestSelectDbOperation_003_SelectCountZero()
{
// Initializes the Select Statement
SelectDbOperation select = new SelectDbOperation(new Table("testtable", "dbo", "0"))
{
SelectFields = new FieldCollection()
{
new AggregateField(Aggregates.Count, "0")
}
};
string statement = myDialect.Select(select);
string expectedStatement = "SELECT \r\n" +
" COUNT(0)\r\n" +
" FROM [dbo].[testtable] T_0\r\n";
// Assertion
Assert.AreEqual <string>(expectedStatement, statement);
}
public void TestSelectDbOperation_002_SimpleSelectWithFields()
{
// Initializes the Select Statement
SelectDbOperation select = new SelectDbOperation(myDialect, new Table("testtable", "dbo", "0"))
{
SelectFields = new List <DisplayField>()
{
new DisplayField("field1", "0", "field1_alternate"),
new DisplayField("field2", "0", "field2_alternate"),
}
};
string statement = select.GetStatement();
string expectedStatement = "SELECT \r\n" +
" T_0.[field1] field1_alternate,\r\n" +
" T_0.[field2] field2_alternate\r\n" +
" FROM [dbo].[testtable] T_0\r\n";
// Assertion
Assert.AreEqual <string>(expectedStatement, statement);
}
public void TestSelectDbOperation_008_SelectWithWhereMultipleExpression()
{
// Initializes the Select Statement
SelectDbOperation select = new SelectDbOperation(new Table("testtable", "dbo", "0"))
{
Where = new WhereCollection()
{
new FilterExpression(new Field("field1"), FieldOperators.Equal, "Test", FieldAndOr.And),
new FilterExpression(new Field("field2"), FieldOperators.GreaterThan, 15, FieldAndOr.And),
new FilterExpression(new Field("field3"), FieldOperators.LessThan, 15, FieldAndOr.And),
new FilterExpression(new Field("field4"), FieldOperators.Equal, new DateTime(2020, 1, 1), FieldAndOr.And),
new GroupedFilterExpression(FieldAndOr.And)
{
new FilterExpression(new Field("field5"), FieldOperators.Equal, "Test", FieldAndOr.Or),
new FilterExpression(new Field("field5"), FieldOperators.Equal, "Test2", FieldAndOr.And),
},
new FilterExpression(new Field("field6"), FieldOperators.Equal, new DateTime(2020, 1, 1), FieldAndOr.And),
}
};
string statement = myDialect.Select(select);
string expectedStatement = "SELECT *\r\n" +
" FROM [dbo].[testtable] T_0\r\n" +
" WHERE\r\n" +
" [field1]='Test' AND\r\n" +
" [field2]>15 AND\r\n" +
" [field3]<15 AND\r\n" +
" [field4]='2020-01-01 00:00:00' AND\r\n" +
" (\r\n" +
" [field5]='Test' OR\r\n" +
" [field5]='Test2'\r\n" +
" ) AND\r\n" +
" [field6]='2020-01-01 00:00:00'\r\n";
// Assertion
Assert.AreEqual <string>(expectedStatement, statement);
}
/// <summary> /// Converts a Select Database Operation into a valid T-SQL Statement /// </summary> /// <param name="select">The <see cref="SelectDbOperation"/></param> /// <returns>A string containing the T-SQL</returns> public abstract string Select(SelectDbOperation select);
/// <summary>
/// Selects data from the database using the Microsoft SQL Server T-SQL Dialect
/// </summary>
/// <param name="select">An <see cref="SelectDbOperation"/> representing the Select to Perform</param>
/// <returns>A <see cref="string"/> containing the select statement to be executed</returns>
public override string Select(SelectDbOperation select)
{
return(SelectInto(select, null));
}
/// <summary>
/// Selects data from the database using the Microsoft SQL Server T-SQL Dialect. Appends the result into a table
/// </summary>
/// <param name="select">An <see cref="SelectDbOperation"/> representing the Select to Perform</param>
/// <param name="intoTable">The <see cref="Table"/> where the data should be inserted</param>
/// <returns>A <see cref="string"/> containing the select statement to be executed</returns>
public override string SelectInto(SelectDbOperation select, Table intoTable)
{
// Container for Query Parameters
StringBuilder sbSelect = new StringBuilder();
// Ensure there are tables on the operation
if (select.Table == null)
{
throw new Exception("There are no tables on the Select Statement");
}
if (select.Table.Length == 0)
{
throw new Exception("There are no tables on the Select Statement");
}
// Make sure Distinct and Group by are not activated at the same time
if ((select.Distinct) && (select.GroupBy))
{
throw new Exception($"Unable to make a Select Statement with both DISTINCT and GROUP BY at the same time");
}
// Check Group By Setup
if (select.GroupBy)
{
if ((select.SelectFields == null) || (select.SelectFields?.Count == 0))
{
throw new Exception($"When using Group By you need to inform the columns you want to group on the {nameof(select.SelectFields)} collection.");
}
}
// ====================================================================================
// SELECT FIELDS AREA
// ====================================================================================
// Display Fields
sbSelect.Append("SELECT ");
if (select.Distinct)
{
sbSelect.Append("DISTINCT ");
}
// Check all Non-Aggregate Fields
if (select.SelectFields?.Count > 0)
{
// For formatting purposes, add a line right after the select
sbSelect.AppendLine();
// Select Specific Fields
sbSelect.AppendLine(String.Join(",\r\n", from field in @select.SelectFields
select String.Concat(String.Empty.PadLeft(7, ' '),
this.FormatExpressionField(field))));
}
else
{
// Select All Fields
sbSelect.Append('*');
sbSelect.AppendLine();
}
// ====================================================================================
// INTO AREA
// ====================================================================================
// If a table was passed, use it for a Select Into
if (intoTable != null)
{
sbSelect.AppendLine(" INTO");
sbSelect.AppendLine(base.FormatTable(intoTable).PadLeft(7, ' '));
}
// ====================================================================================
// FROM AREA
// ====================================================================================
// From Statement
sbSelect.Append(" FROM ");
if (select.Table.Length == 1)
{
// Append Single Table
sbSelect.AppendLine(FormatTable(select.Table[0]));
}
else
{
// Append Multiple Tables Separated by a Comma
sbSelect.AppendLine();
sbSelect.AppendLine(String.Join(",\r\n", from tbl in @select.Table
select String.Concat(String.Empty.PadLeft(7, ' '), FormatTable(tbl))));
}
// ====================================================================================
// JOINS AREA
// ====================================================================================
//TODO: Implement
// ====================================================================================
// WHERE AREA
// ====================================================================================
if (select.Where?.Count > 0)
{
sbSelect.AppendLine(" WHERE");
sbSelect.Append(FormatExpressions(select.Where.ToArray(), 1));
}
// ====================================================================================
// GROUP BY AREA
// ====================================================================================
// Group By Fields
if (select.GroupBy)
{
sbSelect.AppendLine("GROUP BY");
if (select.SelectFields?.Count(field => field.GetType() != typeof(AggregateField)) > 0)
{
// Add all display fields but make sure to not have the alternate name on it
sbSelect.AppendLine(String.Join(",\r\n", from field in @select.SelectFields
where field.GetType() != typeof(AggregateField)
select String.Concat(String.Empty.PadLeft(9, ' '), FormatField(new DisplayField(((DisplayField)field).Name, ((DisplayField)field).TableAlias)))));
}
}
// ====================================================================================
// HAVING AREA
// ====================================================================================
//TODO: Implement
// ====================================================================================
// ORDER BY AREA
// ====================================================================================
if (select.OrderBy?.Count > 0)
{
sbSelect.AppendLine("ORDER BY");
sbSelect.AppendLine(String.Join(",\r\n", from orderByField in @select.OrderBy
select String.Concat(String.Empty.PadLeft(9, ' '),
FormatField(orderByField),
orderByField.SortMode == SortModes.Descending ? " DESC" : "")));
}
// Returns the Result of the Select Statement
return(sbSelect.ToString());
}
/// <summary> /// Converts a Select Into Database Operation into a valid T-SQL Statement /// </summary> /// <param name="select">The <see cref="SelectDbOperation"/></param> /// <param name="intoTable">The destination table</param> /// <returns>A string containing the T-SQL</returns> public abstract string SelectInto(SelectDbOperation select, Table intoTable);
