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