public DbCommand GetReadQuery(SLORMContext ctx)
{
var dbCommand = new SqlCommand();
var statementList = new List <Statement>();
statementList.Add(selectStatementBuilder.GetStatement(ctx.ColumnsInTable, ctx.ColumnsToGroupBy, ctx.ColumnsToCount, ctx.ColumnsToSum));
statementList.Add(fromStatementBuilder.GetStatement(ctx.TableName));
statementList.Add(whereStatementBuilder.GetStatement(ctx.ColumnsInTable, ctx.ColumnsToFilter));
statementList.Add(groupByStatementBuilder.GetStatement(ctx.ColumnsInTable, ctx.ColumnsToGroupBy));
statementList.Add(orderByStatementBuilder.GetStatement(ctx.ColumnsInTable, ctx.ColumnsToOrderBy));
dbCommand.CommandText = string.Empty;
var statementParameters = new List <DBParameterKeyValue>();
foreach (var currentStatement in statementList)
{
dbCommand.CommandText += currentStatement.StatementText;
dbCommand.CommandText += " ";
foreach (var currentParameter in currentStatement.Parameters)
{
dbCommand.Parameters.AddWithValue(currentParameter.Key, currentParameter.Value);
}
}
return(dbCommand);
}
public async Task Query_CountMultipleExistentColumnWithSomeInexistent_ShouldReturnSameAsLinq()
{
// Arrange
var context = new SLORMContext(connection, TestTableManager.TableName);
context.GroupBy(nameof(SampleData.Name));
context.GroupBy(nameof(SampleData.Valid));
context.Count(nameof(SampleData.Name));
context.Count("randomColumn", "anotherInvalidColumn");
var expectedResult =
TestTableManager.TestData.GroupBy(t => new { t.Name, t.Valid })
.Select(group =>
{
return(new
{
group.Key,
Name = group.Count(),
});
});
// Act
var result = await context.Query();
// Assert
Assert.Equal(expectedResult.Count(), result.Rows.Count());
foreach (var currentRow in result.Rows)
{
var equivalentExpectedRow = expectedResult.First(e => e.Key.Name == currentRow.Values[0] && e.Key.Valid == bool.Parse(currentRow.Values[1]));
Assert.Equal(Convert.ToInt32(currentRow.Values[2]), equivalentExpectedRow.Name);
}
}
public async Task Query_WhenFilterTextContainsExcluding_ShouldReturnSameAsLinq(string filterContent)
{
// Arrange
var filter = new List <string> {
filterContent
};
var context = new SLORMContext(connection, TestTableManager.TableName);
context.GroupBy(nameof(SampleData.Name));
context.GroupBy(nameof(SampleData.Valid));
context.Filter(nameof(SampleData.Name), filter, Enums.FilterRigor.Contains, Enums.FilterMethod.Excluding);
var expectedResult = TestTableManager.TestData
.Where(t => !filter.Any(currentFilterValue => t.Name.Contains(currentFilterValue)))
.GroupBy(t => new { t.Name, t.Valid });
// Act
var result = await context.Query();
// Assert
Assert.Equal(expectedResult.Count(), result.Rows.Count());
foreach (var currentRow in result.Rows)
{
var equivalentExpectedRow = expectedResult.FirstOrDefault(e => e.Key.Name == currentRow.Values[0] && e.Key.Valid == bool.Parse(currentRow.Values[1]));
Assert.NotNull(equivalentExpectedRow);
}
}
public void Filter_WhenColumnToFilterAlreadyInFilterList_ShouldDuplicateFilter(string columnName, string value, FilterRigor rigor, FilterMethod method)
{
// Arrange
var values = new List <string> {
value, value
};
var connectionString = "Data Source=190.190.200.100,1433;Initial Catalog = myDataBase;User ID = myUsername;Password = myPassword;";
var columnsInTable = new string[] { "column1", "column2", "column3" };
var queryExecutorResolverMock = GetQueryExecutorResolverMockWithColumns(columnsInTable);
Lifecycle.Container.RegisterInstance(typeof(IQueryExecutorResolver), queryExecutorResolverMock);
var dbConnectionMock = new Mock <DbConnection>();
dbConnectionMock.Setup(conn => conn.ConnectionString).Returns(connectionString);
var ctx = new SLORMContext(dbConnectionMock.Object, connectionString);
var columnFilter = new ColumnFilterRequest(columnName, values, rigor, method);
ctx.Filter(columnFilter);
// Act
ctx.Filter(columnFilter);
// Assert
var filterRequestCount = ctx.ColumnsToFilter.Where(c => c.Column.Name == columnFilter.ColumnName && c.FilterMethod == columnFilter.FilterMethod && c.FilterRigor == columnFilter.FilterRigor).Count();
Assert.Equal(2, filterRequestCount);
}
public async Task Query_CountExistentColumn_ShouldReturnSameAsLinq()
{
// Arrange
var context = new SLORMContext(connection, TestTableManager.TableName);
context.GroupBy(nameof(SampleData.Name));
context.Count(nameof(SampleData.Name));
var expectedResult =
TestTableManager.TestData.GroupBy(t => t.Name)
.Select(group =>
{
return(new
{
group.Key,
Name = group.Count(),
});
});
// Act
var result = await context.Query();
// Assert
Assert.Equal(expectedResult.Count(), result.Rows.Count());
foreach (var currentRow in result.Rows)
{
var equivalentExpectedRow = expectedResult.First(e => e.Key == currentRow.Values[0]);
Assert.Equal(Convert.ToInt32(currentRow.Values[1]), equivalentExpectedRow.Name);
}
}
public void methodbeingtested_situationbeingtested_exceptedresult()
{
var conn = new SqlConnection(@"Server=(local)\SQLEXPRESS;Database=Logs;Trusted_Connection=True;");
var ctx = new SLORMContext(conn, "Logs");
ctx.GroupBy("EmissorId", "TipoLog", "colunaQUalquer");
ctx.Filter("EmissorId", "1", FilterRigor.Equals, FilterMethod.Including);
ctx.Filter("Mensagem", "Top", FilterRigor.Contains, FilterMethod.Including);
ctx.Filter("EmissorId", "1", FilterRigor.Equals, FilterMethod.Including);
ctx.Count("DataHora", "aaaa", "Id");
ctx.OrderBy("CorrelationId", OrderType.ASC);
ctx.Query();
}
public void Constructor_WhenSqlServerProvider_ShouldReturnInstance(string connectionString)
{
// Arrange
var queryExecutorResolverMock = GetSimpleQueryExecutorResolverMock();
Lifecycle.Container.RegisterInstance(typeof(IQueryExecutorResolver), queryExecutorResolverMock);
var dbConnectionMock = new Mock <DbConnection>();
dbConnectionMock.Setup(conn => conn.ConnectionString).Returns(connectionString);
// Act
var ctx = new SLORMContext(dbConnectionMock.Object, "my_table");
// Assert
Assert.NotNull(ctx);
}
public void Constructor_WhenMissingTableName_ShouldThrowException()
{
// Arrange
var connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password = myPassword;";
var queryExecutorResolverMock = GetSimpleQueryExecutorResolverMock();
Lifecycle.Container.RegisterInstance(typeof(IQueryExecutorResolver), queryExecutorResolverMock);
var dbConnectionMock = new Mock <DbConnection>();
dbConnectionMock.Setup(conn => conn.ConnectionString).Returns(connectionString);
// Act
var ctx = new SLORMContext(dbConnectionMock.Object, "my_table");
// Assert
Assert.NotNull(ctx);
}
public void Count_WhenColumnNameIsNull_ShouldThrowException()
{
// Arrange
var connectionString = "Data Source=190.190.200.100,1433;Initial Catalog = myDataBase;User ID = myUsername;Password = myPassword;";
var queryExecutorResolverMock = GetSimpleQueryExecutorResolverMock();
Lifecycle.Container.RegisterInstance(typeof(IQueryExecutorResolver), queryExecutorResolverMock);
var dbConnectionMock = new Mock <DbConnection>();
dbConnectionMock.Setup(conn => conn.ConnectionString).Returns(connectionString);
var ctx = new SLORMContext(dbConnectionMock.Object, connectionString);
// Act/Assert
Assert.Throws <ArgumentNullException>(() => ctx.Count(null));
}
public void GetTableName_WhenGet_ShouldReturnTableNameSetInConstructor(string tableName)
{
// Arrange
var connectionString = "Data Source=190.190.200.100,1433;Initial Catalog = myDataBase;User ID = myUsername;Password = myPassword;";
var queryExecutorResolverMock = GetSimpleQueryExecutorResolverMock();
Lifecycle.Container.RegisterInstance(typeof(IQueryExecutorResolver), queryExecutorResolverMock);
var dbConnectionMock = new Mock <DbConnection>();
dbConnectionMock.Setup(conn => conn.ConnectionString).Returns(connectionString);
// Act
var ctx = new SLORMContext(dbConnectionMock.Object, tableName);
// Assert
Assert.Equal(tableName, ctx.TableName);
}
public void Filter_WhenNullParameter_ShouldThrowException()
{
// Arrange
var connectionString = "Data Source=190.190.200.100,1433;Initial Catalog = myDataBase;User ID = myUsername;Password = myPassword;";
var columnsInTable = new string[] { "column1", "column2", "column3" };
var queryExecutorResolverMock = GetQueryExecutorResolverMockWithColumns(columnsInTable);
Lifecycle.Container.RegisterInstance(typeof(IQueryExecutorResolver), queryExecutorResolverMock);
var dbConnectionMock = new Mock <DbConnection>();
dbConnectionMock.Setup(conn => conn.ConnectionString).Returns(connectionString);
var ctx = new SLORMContext(dbConnectionMock.Object, connectionString);
// Act/Assert
Assert.Throws <ArgumentNullException>(() => ctx.Filter(null));
}
async Task Query_SumNumbers_ShouldReturnSameAsLinq()
{
// Arrange
var context = new SLORMContext(connection, TestTableManager.TableName);
context.GroupBy(nameof(SampleData.Name));
context.GroupBy(nameof(SampleData.Valid));
context.Sum(nameof(SampleData.Value));
context.Sum(nameof(SampleData.Value2));
context.Sum(nameof(SampleData.Value3));
context.Sum("ColumnThatDoesNotExist");
context.Sum(nameof(SampleData.DateTime)); // Not an number column
context.Sum(nameof(SampleData.Value3)); // Column Already added
var expectedResult =
TestTableManager.TestData.GroupBy(t => new { t.Name, t.Valid })
.Select(group =>
{
return(new
{
group.Key,
Name = group.Count(),
Value = group.Sum(t => t.Value),
Value2 = group.Sum(t => t.Value2),
Value3 = group.Sum(t => t.Value3),
});
});
// Act
var result = await context.Query();
// Assert
// Checking if db returned same number of rows as expected.
Assert.Equal(expectedResult.Count(), result.Rows.Count());
// Checking columnCount
Assert.Equal(5, result.Rows.First().Values.Count());
foreach (var currentRow in result.Rows)
{
var equivalentExpectedRow = expectedResult.First(e => e.Key.Name == currentRow.Values[0] && e.Key.Valid == bool.Parse(currentRow.Values[1]));
Assert.Equal(float.Parse(currentRow.Values[2]), equivalentExpectedRow.Value, 2);
Assert.Equal(double.Parse(currentRow.Values[3]), equivalentExpectedRow.Value2, 2);
Assert.Equal(int.Parse(currentRow.Values[4]), equivalentExpectedRow.Value3);
}
}
public void Count_WhenProvidedColumnIsInTable_ShouldAddColumnToCountList(string columnName)
{
// Arrange
var connectionString = "Data Source=190.190.200.100,1433;Initial Catalog = myDataBase;User ID = myUsername;Password = myPassword;";
var queryExecutorResolverMock = GetQueryExecutorResolverMockWithColumns(columnName);
Lifecycle.Container.RegisterInstance(typeof(IQueryExecutorResolver), queryExecutorResolverMock);
var dbConnectionMock = new Mock <DbConnection>();
dbConnectionMock.Setup(conn => conn.ConnectionString).Returns(connectionString);
var ctx = new SLORMContext(dbConnectionMock.Object, connectionString);
// Act
ctx.Count(columnName);
// Assert
var columnWasAddedToGroupByList = ctx.ColumnsToCount.Any(c => c.Name == columnName);
Assert.True(columnWasAddedToGroupByList);
}
public void Count_WhenColumnAlreadyCounted_ShouldNotDuplicate(string columnName)
{
// Arrange
var connectionString = "Data Source=190.190.200.100,1433;Initial Catalog = myDataBase;User ID = myUsername;Password = myPassword;";
var queryExecutorResolverMock = GetQueryExecutorResolverMockWithColumns(columnName);
Lifecycle.Container.RegisterInstance(typeof(IQueryExecutorResolver), queryExecutorResolverMock);
var dbConnectionMock = new Mock <DbConnection>();
dbConnectionMock.Setup(conn => conn.ConnectionString).Returns(connectionString);
var ctx = new SLORMContext(dbConnectionMock.Object, connectionString);
ctx.Count(columnName);
// Act
ctx.Count(columnName);
// Assert
var columnOcurrencesInCountList = ctx.ColumnsToCount.Where(c => c.Name == columnName).Count();
Assert.Equal(1, columnOcurrencesInCountList);
}
public async Task Query_SomeInvalidGroupByApplied_ShouldReturnSameResultsAsLinq()
{
// Arrange
var context = new SLORMContext(connection, TestTableManager.TableName);
context.GroupBy(nameof(SampleData.Name), nameof(SampleData.Valid), "someString", "ThatDoesNotExist", "a", "whatever");
var expectedResult = TestTableManager.TestData
.GroupBy(p => new { p.Name, p.Valid });
// Act
var results = await context.Query();
// Assert
Assert.Equal(expectedResult.Count(), results.Rows.Count());
for (var i = 0; i < results.Rows.Count(); i++)
{
var currentResultRow = results.Rows.ElementAt(i);
var currentExpectedRowExists = expectedResult
.Any(group => group.Key.Name == currentResultRow.Values[0] && group.Key.Valid == bool.Parse(currentResultRow.Values[1]));
Assert.True(currentExpectedRowExists);
}
}
public void GetTableDataFromRealSQLServer()
{
var conn = new SqlConnection(@"Server=(local)\SQLEXPRESS;Database=Logs;Trusted_Connection=True;");
var ctx = new SLORMContext(conn, "Logs");
//ctx.Select("minhaColunaQUalquer", "Id", "aaaa", "", "DataHora");
//var conn = new SqliteConnection("Data Source=:memory:;");
//conn.Open();
//var command = conn.CreateCommand();
//command.CommandText = "CREATE TABLE tabelaTop(Id int, nome varchar(32));";
//command.ExecuteNonQuery();
//command.CommandText = "INSERT INTO tabelaTop values (1, 'nome1');";
//command.ExecuteNonQuery();
//command.CommandText = "Select * from tabelaTop";
//var reader = command.ExecuteReader();
//while (reader.Read())
//{
// for (var i = 0; i < reader.FieldCount; i++)
// Console.WriteLine(reader[i]);
// Console.WriteLine("-----------");
//}
//reader.Close();
//command.CommandText = $@"select *
// from information_schema.columns
// where table_name = 'tabelaTop'
// order by ordinal_position";
//reader = command.ExecuteReader();
//while (reader.Read())
//{
// for (var i = 0; i < reader.FieldCount; i++)
// Console.WriteLine(reader[i]);
// Console.WriteLine("-----------");
//}
//conn.Close();
}
public async Task <QueryResult> Query(SLORMContext context, int timeoutInSeconds)
{
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
if (timeoutInSeconds <= 0)
{
throw new ArgumentOutOfRangeException(nameof(timeoutInSeconds));
}
string commandQuery = string.Empty;
DbParameterCollection queryParams = null;
try
{
using (var queryCommand = queryBuilder.GetReadQuery(context))
{
queryParams = queryCommand.Parameters;
commandQuery = queryCommand.CommandText;
await context.Connection.EnsureConnected();
queryCommand.Connection = (SqlConnection)context.Connection;
queryCommand.CommandTimeout = timeoutInSeconds;
var reader = await queryCommand.ExecuteReaderAsync();
var result = new QueryResult(reader);
return(result);
}
}
catch (SqlException ex)
{
throw new SLORMSQLException(ex, commandQuery, queryParams);
}
}