public void TestCreateSchema()
{
string schemaName = "s" + TestHelper.RandomString(9);
CreateSchemaTask.Create(schemaName);
Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if schema exists", $"select count(*) from sys.schemas where schema_name(schema_id) = '{schemaName}'"));
}
public void TestLogging()
{
CreateLogTablesTask.CreateLog();
CreateSchemaTask.Create("s" + TestHelper.RandomString(9));
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='CREATESCHEMA' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void TestAddDefaultFileGroup()
{
string fgName = TestHelper.RandomString(10) + "_FG";
Assert.AreEqual(0, SqlTask.ExecuteScalar <int>("FileGroup", $"select count(*) from sys.filegroups where name = '{fgName}' and is_default = 1"));
AddFileGroupTask.AddFileGroup(fgName, DBNameParameter, "5MB", "5MB", true);
Assert.AreEqual(1, SqlTask.ExecuteScalar <int>("FileGroup", $"select count(*) from sys.filegroups where name = '{fgName}' and is_default = 1"));
Assert.AreEqual(1, SqlTask.ExecuteScalar <int>("FileGroup", $"select count(*) from sys.sysfiles where name = '{fgName}'"));
}
public void TestCreateIndex()
{
string indexName = "ix_" + TestHelper.RandomString(5);
CreateIndexTask.Create(indexName, "test.Table1", new List <string>()
{
"key1", "key2"
});
Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if index exists", $"select count(*) from sys.indexes where name = '{indexName}'"));
}
public void TestExecuteNonQuery()
{
string propName = TestHelper.RandomString(10);
SqlTask.ExecuteNonQuery("Test add extended property", $"exec sp_addextendedproperty @name = N'{propName}', @value = 'Test';");
string asisCollation = SqlTask.ExecuteScalar("Get reference result", $"select value from fn_listextendedproperty('{propName}', default, default, default, default, default, default)").ToString();
Assert.AreEqual("Test", asisCollation);
SqlTask.ExecuteNonQuery("Drop extended property", $"exec sp_dropextendedproperty @name = N'{propName}'");
}
public void TestLogging()
{
CreateLogTablesTask.CreateLog();
CreateIndexTask.Create("ix_" + TestHelper.RandomString(5), "test.Table1", new List <string>()
{
"key1", "key2"
});
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='CREATEINDEX' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void TestExecuteNonQueryWithParameter()
{
string propName = TestHelper.RandomString(10);
var parameter = new List <QueryParameter> {
new QueryParameter("propName", "nvarchar(100)", propName)
};
SqlTask.ExecuteNonQuery("Test add extended property", $"exec sp_addextendedproperty @name = @propName, @value = 'Test';", parameter);
string asisCollation = SqlTask.ExecuteScalar("Get reference result", $"select value from fn_listextendedproperty(@propName, default, default, default, default, default, default)", parameter).ToString();
Assert.AreEqual("Test", asisCollation);
SqlTask.ExecuteNonQuery("Drop extended property", $"exec sp_dropextendedproperty @name = N'{propName}'");
}
public void TestCleanUpSchema()
{
string schemaName = "s" + TestHelper.RandomString(9);
SqlTask.ExecuteNonQuery("Create schema", $"create schema {schemaName}");
SqlTask.ExecuteNonQuery("Create schema", $"create table {schemaName}.Table1 ( Nothing int null )");
SqlTask.ExecuteNonQuery("Create schema", $"create view {schemaName}.View1 as select * from {schemaName}.Table1");
SqlTask.ExecuteNonQuery("Create schema", $"create procedure {schemaName}.Proc1 as select * from {schemaName}.Table1");
var objCountSql = new SqlTask("Count object", $@"select count(*) from sys.objects obj
inner join sys.schemas sch on sch.schema_id = obj.schema_id
where sch.name = '{schemaName}'");
Assert.AreEqual(3, objCountSql.ExecuteScalar <int>());
CleanUpSchemaTask.CleanUp(schemaName);
Assert.AreEqual(0, objCountSql.ExecuteScalar <int>());
}
public void CreateBigDataCSV()
{
using (FileStream stream = File.Open(FileName, FileMode.Create))
using (StreamWriter writer = new StreamWriter(stream)) {
string header = String.Join(",", TableDefinition.Columns.Select(col => col.Name));
writer.WriteLine(header);
for (int i = 0; i < NumberOfRows; i++)
{
string line = String.Join(",", TableDefinition.Columns.Select(col => {
int length = DataTypeConverter.GetStringLengthFromCharString(col.DataType);
return(TestHelper.RandomString(length));
}));
writer.WriteLine(line);
}
}
}
