public void Build_OneParameterWithTypeInt_CorrectResultSet()
{
var commandBuilder = new CommandBuilder();
var cmd = commandBuilder.Build(
ConnectionStringReader.GetSqlClient(),
"select * from [Sales].[Customer] where CustomerID=@Param",
new List <QueryParameterXml>()
{
new QueryParameterXml()
{
Name = "@Param",
SqlType = "int",
StringValue = "2"
}
}
);
cmd.Connection.Open();
var dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
Assert.That(dr.Read(), Is.True);
Assert.That(dr.GetValue(0), Is.EqualTo(2));
Assert.That(dr.Read(), Is.False);
}
public void Matches_TwoQueriesOfThreeSecondsSequential_SlowerThanSixSeconds()
{
var command = new SqlCommand();
command.Connection = new SqlConnection(ConnectionStringReader.GetSqlClient());
command.CommandText = "WAITFOR DELAY '00:00:03';SELECT 1;";
var command2 = new SqlCommand();
command2.Connection = new SqlConnection(ConnectionStringReader.GetSqlClient());
command2.CommandText = "WAITFOR DELAY '00:00:03';SELECT 1;";
var ctr = new EqualToConstraint(command2);
ctr = ctr.Sequential();
//Method under test
var chrono = DateTime.Now;
Assert.That(command, ctr);
var elapsed = DateTime.Now.Subtract(chrono);
Assert.That(elapsed.Seconds, Is.GreaterThanOrEqualTo(6));
}
public void Build_OneParameterWithTypeNvarchar50_CorrectResultSet()
{
var commandBuilder = new CommandBuilder();
var cmd = commandBuilder.Build(
ConnectionStringReader.GetSqlClient(),
"select * from [Sales].[SalesTerritory] where Name=@Param",
new List <QueryParameterXml>()
{
new QueryParameterXml()
{
Name = "@Param",
SqlType = "nvarchar(50)",
StringValue = "Canada"
}
}
);
cmd.Connection.Open();
var dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
Assert.That(dr.Read(), Is.True);
Assert.That(dr.GetValue(1), Is.EqualTo("Canada"));
Assert.That(dr.Read(), Is.False);
}
public void Parse_DontExecuteEffectivelyQuery()
{
var sqlCount = @"SELECT COUNT(*) from [HumanResources].[Department]";
var sql = @"DELETE from [HumanResources].[Department]";
var countBefore = ExecuteCount(sqlCount);
if (countBefore == 0) //If nothing was present we cannot assert
{
Assert.Inconclusive();
}
var cmd = new SqlCommand(sql, new SqlConnection(ConnectionStringReader.GetSqlClient()));
var qp = new QueryEngineFactory().GetParser(cmd);
var res = qp.Parse();
if (!res.IsSuccesful)//If syntax is incorrect we cannot assert
{
Assert.Inconclusive();
}
var countAfter = ExecuteCount(sqlCount);
Assert.That(countAfter, Is.EqualTo(countBefore));
}
public void Matches_MdxQueryWithNullComparedToSqlWithValue_NonMatching()
{
//Buiding object used during test
var sql = "SELECT 'CY 2010', 0 ";
var expectedQuery = new NBi.Core.Query.Query(sql, ConnectionStringReader.GetSqlClient());
var resolver = new FakeQueryResultSetResolver(expectedQuery, serviceLocator);
var builder = new ResultSetServiceBuilder();
builder.Setup(resolver);
var ctr = new EqualToConstraint(builder.GetService());
ctr.Using(
new SettingsOrdinalResultSet(
SettingsOrdinalResultSet.KeysChoice.AllExpectLast,
SettingsOrdinalResultSet.ValuesChoice.Last,
NumericAbsoluteTolerance.None
)
);
var mdx = "SELECT [Measures].[Amount] ON 0, [Date].[Calendar].[Calendar Year].&[2010] ON 1 FROM [Adventure Works]";
var query = new NBi.Core.Query.Query(mdx, ConnectionStringReader.GetAdomd());
var actualBuilder = new ResultSetServiceBuilder();
actualBuilder.Setup(new FakeQueryResultSetResolver(query, serviceLocator));
var actual = actualBuilder.GetService();
//Assertion
Assert.That(ctr.Matches(actual), Is.False);
}
public CurrencyLookup(string isoLanguage = "en")
: base("Unknown")
{
this.isoLanguage = isoLanguage;
ConnectionString = ConnectionStringReader.GetSqlClient();
CommandTimeOut = 300;
}
public void Matches_SqlQueryWithDateComparedToStringAnotherHour_NonMatching()
{
//Buiding object used during test
var expectedQuery = "SELECT 'CY 2010', CAST('2010-01-01' AS DATE)";
var expectedCmd = new SqlCommand(expectedQuery, new SqlConnection(ConnectionStringReader.GetSqlClient()));
var columns = new List <IColumnDefinition>();
columns.Add(new Column()
{
Index = 1, Role = ColumnRole.Value, Type = ColumnType.DateTime
});
var ctr = new EqualToConstraint(expectedCmd);
ctr.Using(
new SettingsResultSetComparisonByIndex(
SettingsResultSetComparisonByIndex.KeysChoice.AllExpectLast,
SettingsResultSetComparisonByIndex.ValuesChoice.Last,
columns
)
);
var query = "SELECT 'CY 2010', '1/01/2010 01:00:00'";
var cmd = new SqlCommand(query, new SqlConnection(ConnectionStringReader.GetSqlClient()));
//Call the method to test
var actual = ctr.Matches(cmd);
//Assertion
Assert.That(actual, Is.False);
}
public void Get_IGroupCommand_GroupCommand()
{
var command = Mock.Of <IResetCommand>(
m => m.ConnectionString == ConnectionStringReader.GetSqlClient() &&
m.TableName == "MyTableToTruncate"
);
var otherCommand = Mock.Of <IResetCommand>(
m => m.ConnectionString == ConnectionStringReader.GetSqlClient() &&
m.TableName == "MyOtherTableToTruncate"
);
var commands = new List <IDecorationCommand>();
commands.Add(command);
commands.Add(otherCommand);
var group = Mock.Of <IGroupCommand>(
g => g.Parallel == true &&
g.Commands == commands
);
var factory = new GroupCommandFactory();
var impl = factory.Get(group);
Assert.That(impl, Is.TypeOf <GroupCommand>());
}
public void Get_SequentialCommandArgs_SequentialCommand()
{
var command = Mock.Of <IResetCommandArgs>(
m => m.ConnectionString == ConnectionStringReader.GetSqlClient() &&
m.TableName == new LiteralScalarResolver <string>("MyTableToTruncate")
);
var otherCommand = Mock.Of <IResetCommandArgs>(
m => m.ConnectionString == ConnectionStringReader.GetSqlClient() &&
m.TableName == new LiteralScalarResolver <string>("MyOtherTableToTruncate")
);
var commands = new List <IDecorationCommandArgs>()
{
command, otherCommand
};
var group = Mock.Of <ISequentialCommandArgs>(
g => g.Commands == commands
);
var factory = new GroupCommandFactory();
var impl = factory.Instantiate(group);
Assert.That(impl, Is.TypeOf <SequentialCommand>());
}
public void Matches_MdxQueryWithNullComparedToSqlWithNull_Matching()
{
//Buiding object used during test
var expectedQuery = "SELECT 'CY 2010', NULL ";
var expectedCmd = new SqlCommand(expectedQuery, new SqlConnection(ConnectionStringReader.GetSqlClient()));
var resolver = new FakeQueryResultSetLoader(expectedCmd);
var builder = new ResultSetServiceBuilder();
builder.Setup(resolver);
var ctr = new EqualToConstraint(builder.GetService());
ctr.Using(
new SettingsIndexResultSet(
SettingsIndexResultSet.KeysChoice.AllExpectLast,
SettingsIndexResultSet.ValuesChoice.Last,
NumericAbsoluteTolerance.None
)
);
var query = "SELECT [Measures].[Amount] ON 0, [Date].[Calendar].[Calendar Year].&[2010] ON 1 FROM [Adventure Works]";
var cmd = new AdomdCommand(query, new AdomdConnection(ConnectionStringReader.GetAdomd()));
var actualBuilder = new ResultSetServiceBuilder();
actualBuilder.Setup(new FakeQueryResultSetLoader(cmd));
var actual = actualBuilder.GetService();
//Assertion
Assert.That(ctr.Matches(actual));
}
public void Matches_SqlQueryAndMembers_Succesful()
{
var sql = "select " +
"'Executive General and Administration' union select " +
"'Inventory Management' union select " +
"'Manufacturing' union select " +
"'Research and Development' union select " +
"'Quality Assurance' union select " +
"'Sales and Marketing' ";
var args = new QueryResultSetResolverArgs(new QueryResolverArgs(sql, ConnectionStringReader.GetSqlClient(), null, null, new TimeSpan(0, 0, 30), System.Data.CommandType.Text));
var factory = new ResultSetResolverFactory(new ServiceLocator());
var resolver = factory.Instantiate(args);
var discovery = new DiscoveryRequestFactory().Build(
ConnectionStringReader.GetAdomd()
, "Corporate"
, "Adventure Works"
, "Department"
, "Departments"
, null);
var ctr = new EquivalentToConstraint(resolver);
Assert.That(discovery, ctr);
}
public void Matches_SqlQueryAndMembers_Succesful()
{
var command = new SqlCommand();
command.Connection = new SqlConnection(ConnectionStringReader.GetSqlClient());
command.CommandText = "select " +
"'Executive General and Administration' union select " +
"'Inventory Management' union select " +
"'Manufacturing' union select " +
"'Research and Development' union select " +
"'Quality Assurance' union select " +
"'Sales and Marketing' ";
var discovery = new DiscoveryRequestFactory().Build(
ConnectionStringReader.GetAdomd()
, "Corporate"
, "Adventure Works"
, "Department"
, "Departments"
, null);
var ctr = new EquivalentToConstraint(command);
Assert.That(discovery, ctr);
}
public void Execute_InvalidPath_ThrowExternalDependency()
{
var args = new AssemblyQueryResolverArgs(
@"Acceptance\Resources\NBi.Testing.dll",
"NBi.Testing.Acceptance.Resources.AssemblyClass",
"GetTextSelectSql",
false,
new Dictionary <string, object>()
{
{ "prefix", "CY" }
},
ConnectionStringReader.GetSqlClient(),
new List <IQueryParameter>()
{
new QueryParameter("param", "10")
},
new List <IQueryTemplateVariable>()
{
new QueryTemplateVariableXml()
{
Name = "operator", Value = "not in"
}
},
10);
var resolver = new AssemblyQueryResolver(args);
Assert.Throws <ExternalDependencyNotFoundException>(() => resolver.Execute());
}
private AssemblyQueryResolverArgs BuildArgs()
{
return(new AssemblyQueryResolverArgs(
@"NBi.Testing.dll",
"NBi.Testing.Acceptance.Resources.AssemblyClass",
"GetTextSelectSql",
false,
new Dictionary <string, object>()
{
{ "prefix", "CY" }
},
ConnectionStringReader.GetSqlClient(),
new List <IQueryParameter>()
{
new QueryParameter("param", "10")
},
new List <IQueryTemplateVariable>()
{
new QueryTemplateVariableXml()
{
Name = "operator", Value = "not in"
}
},
10));
}
public void Matches_TwoQueriesOfThreeSecondsSequential_SlowerThanSixSeconds()
{
var command1 = new SqlCommand
{
Connection = new SqlConnection(ConnectionStringReader.GetSqlClient()),
CommandText = "WAITFOR DELAY '00:00:03';SELECT 1;"
};
var command2 = new SqlCommand
{
Connection = new SqlConnection(ConnectionStringReader.GetSqlClient()),
CommandText = "WAITFOR DELAY '00:00:03';SELECT 1;"
};
var loader = new FakeQueryResultSetLoader(command2);
var builder = new ResultSetServiceBuilder();
builder.Setup(loader);
BaseResultSetComparisonConstraint ctr = new EqualToConstraint(builder.GetService());
ctr = ctr.Sequential();
//Method under test
var chrono = DateTime.Now;
var actualBuilder = new ResultSetServiceBuilder();
actualBuilder.Setup(new FakeQueryResultSetLoader(command1));
var actual = actualBuilder.GetService();
Assert.That(actual, ctr);
var elapsed = DateTime.Now.Subtract(chrono);
Assert.That(elapsed.Seconds, Is.GreaterThanOrEqualTo(6));
}
public void Matches_MdxQueryWithNullComparedToSqlWithValue_NonMatching()
{
//Buiding object used during test
var expectedQuery = "SELECT 'CY 2010', 0 ";
var expectedCmd = new SqlCommand(expectedQuery, new SqlConnection(ConnectionStringReader.GetSqlClient()));
var ctr = new EqualToConstraint(expectedCmd);
ctr.Using(
new SettingsResultSetComparisonByIndex(
SettingsResultSetComparisonByIndex.KeysChoice.AllExpectLast,
SettingsResultSetComparisonByIndex.ValuesChoice.Last,
NumericAbsoluteTolerance.None
)
);
var query = "SELECT [Measures].[Amount] ON 0, [Date].[Calendar].[Calendar Year].&[2010] ON 1 FROM [Adventure Works]";
var cmd = new AdomdCommand(query, new AdomdConnection(ConnectionStringReader.GetAdomd()));
//Call the method to test
var actual = ctr.Matches(cmd);
//Assertion
Assert.That(actual, Is.False);
}
public void Matches_TwoQueriesOfThreeSecondsParallel_FasterThanSixSeconds()
{
var command = new SqlCommand();
command.Connection = new SqlConnection(ConnectionStringReader.GetSqlClient());
command.CommandText = "WAITFOR DELAY '00:00:03';SELECT 1;";
var command2 = new SqlCommand();
command2.Connection = new SqlConnection(ConnectionStringReader.GetSqlClient());
command2.CommandText = "WAITFOR DELAY '00:00:03';SELECT 1;";
BaseResultSetComparisonConstraint ctr = new EqualToConstraint(command2);
ctr = ctr.Parallel();
//Method under test
var chrono = DateTime.Now;
Assert.That(command, ctr);
var elapsed = DateTime.Now.Subtract(chrono);
Assert.That(elapsed.Seconds, Is.LessThan(6));
}
public void Execute_Args_ConnectionStringAssigned()
{
var resolver = new ExternalFileQueryResolver(BuildArgs());
var cmd = resolver.Execute();
Assert.That(cmd.Connection.ConnectionString, Is.Not.Null.And.Not.Empty);
Assert.That(cmd.Connection.ConnectionString, Is.EqualTo(ConnectionStringReader.GetSqlClient()));
}
public void GetExecutor_SqlNativeConnectionString_SqlNativeEngine()
{
var factory = new QueryEngineFactory();
var cmd = new SqlCommand("select @@version;", new SqlConnection(ConnectionStringReader.GetSqlClient()));
var executor = factory.GetExecutor(cmd);
Assert.IsInstanceOf <QuerySqlEngine>(executor);
}
public void Execute_Args_ConnectionStringAssigned()
{
var resolver = new EmbeddedQueryResolver(BuildArgs());
var query = resolver.Execute();
Assert.That(query.ConnectionString, Is.Not.Null.And.Not.Empty);
Assert.That(query.ConnectionString, Is.EqualTo(ConnectionStringReader.GetSqlClient()));
}
public void Instantiate_SqlConnection_GetDatabaseStructureDiscoveryFactory()
{
var connectionString = ConnectionStringReader.GetSqlClient();
var provider = new StructureDiscoveryFactoryProvider();
var factory = provider.Instantiate(connectionString);
Assert.That(factory, Is.TypeOf <RelationalStructureDiscoveryFactory>());
}
public void Parse_CorrectFields_Success()
{
var sql = "select [DepartmentID], Name from [HumanResources].[Department];";
var cmd = new NBi.Core.Query.Query(sql, ConnectionStringReader.GetSqlClient());
var qp = new ValidationEngineFactory().Instantiate(cmd);
var res = qp.Parse();
Assert.That(res.IsSuccesful, Is.True);
}
public void Parse_CorrectFields_Success()
{
var sql = "select [DepartmentID], Name from [HumanResources].[Department];";
var cmd = new SqlCommand(sql, new SqlConnection(ConnectionStringReader.GetSqlClient()));
var qp = new QueryEngineFactory().GetParser(cmd);
var res = qp.Parse();
Assert.That(res.IsSuccesful, Is.True);
}
public void SetupMethods()
{
//Build the fullpath for the file to read
Directory.CreateDirectory("Etl");
DiskOnFile.CreatePhysicalFile(@"Etl\Sample.dtsx", "NBi.Testing.Integration.SqlServer.IntegrationService.Resources.Sample.dtsx");
//Set environment variable
Environment.SetEnvironmentVariable("FirstJanuary2015", "2015-01-01", EnvironmentVariableTarget.User);
Environment.SetEnvironmentVariable("ConnStrAdvWorksCloud", ConnectionStringReader.GetSqlClient(), EnvironmentVariableTarget.User);
}
public void Execute_Query_IsExecuted()
{
var args = new QueryScalarResolverArgs(
new QueryResolverArgs("select 10;", ConnectionStringReader.GetSqlClient(), null, null, new TimeSpan(0, 0, 10), System.Data.CommandType.Text)
);
var resolver = new QueryScalarResolver <int>(args, new ServiceLocator());
Assert.That(resolver.Execute(), Is.EqualTo(10));
}
public void Execute_OneQueryHavingTimeout_ReturnTimeoutInfo()
{
var query = "WAITFOR DELAY '00:00:03';";
var cmd = new SqlCommand(query, new SqlConnection(ConnectionStringReader.GetSqlClient()));
var qp = new SqlPerformanceEngine(cmd.Connection, cmd);
var res = qp.Execute(new TimeSpan(0, 0, 1));
Assert.That(res.IsTimeOut, Is.True);
Assert.That(res.TimeOut.TotalMilliseconds, Is.EqualTo(1000));
}
public void Parse_WrongSyntax_Failed()
{
var sql = "SELECTION [DepartmentID], Name, WrongField1, WrongField2 from [HumanResources].[Department];";
var cmd = new SqlCommand(sql, new SqlConnection(ConnectionStringReader.GetSqlClient()));
var qp = new QueryEngineFactory().GetParser(cmd);
var res = qp.Parse();
Assert.That(res.IsSuccesful, Is.False);
Assert.That(res.Errors[0], Is.EqualTo("Incorrect syntax near 'SELECTION'."));
}
public void Parse_WrongTableName_Failed()
{
var sql = "SELECT * FROM WrongTableName;";
var cmd = new SqlCommand(sql, new SqlConnection(ConnectionStringReader.GetSqlClient()));
var qp = new QueryEngineFactory().GetParser(cmd);
var res = qp.Parse();
Assert.That(res.IsSuccesful, Is.False);
Assert.That(res.Errors[0], Is.EqualTo("Invalid object name 'WrongTableName'."));
}
public void Parse_WrongField_Failed()
{
var sql = "select [DepartmentID], Name, WrongField from [HumanResources].[Department];";
var cmd = new SqlCommand(sql, new SqlConnection(ConnectionStringReader.GetSqlClient()));
var qp = new QueryEngineFactory().GetParser(cmd);
var res = qp.Parse();
Assert.That(res.IsSuccesful, Is.False);
Assert.That(res.Errors[0], Is.EqualTo("Invalid column name 'WrongField'."));
}
public void Execute_WaitFor3SecondsTimeoutSetTo0_NoTimeOut()
{
var query = "WAITFOR DELAY '00:00:03';";
var cmd = new SqlCommand(query, new SqlConnection(ConnectionStringReader.GetSqlClient()));
cmd.CommandTimeout = 0;
var qe = new QuerySqlEngine(cmd);
Assert.DoesNotThrow(delegate { qe.Execute(); });
}
