public void TestCreateIndexWithInclude()
{
string indexName = "ix_" + TestHelper.RandomString(5);
CreateIndexTask.Create(indexName, "test.Table2", new List <string>()
{
"key1", "key2"
}, new List <string>()
{
"value1", "value2"
});
Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if index exists", $"select count(*) from sys.indexes where name = '{indexName}'"));
}
public void RemoveLogTables()
{
//Arrange
RemoveLogTablesTask.Remove(Connection);
CreateLogTablesTask.CreateLog(Connection);
//Act
RemoveLogTablesTask.Remove(Connection);
//Assert
Assert.True(SqlTask.ExecuteScalarAsBool(Connection, "Check if tables are deleted",
"SELECT CASE WHEN object_id('etl.LoadProcess') IS NULL THEN 1 ELSE 0 END"));
Assert.True(SqlTask.ExecuteScalarAsBool(Connection, "Check if tables are deleted",
"SELECT CASE WHEN object_id('etl.Log') IS NULL THEN 1 ELSE 0 END"));
}
public void TestCreatProcedureWithParameter()
{
List <ProcedureParameter> pars = new List <ProcedureParameter>()
{
new ProcedureParameter("Par1", "varchar(10)"),
new ProcedureParameter("Par2", "int", "7"),
};
CRUDProcedureTask.CreateOrAlter("test.Test2", "select 1 as Test", pars);
Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if proc exists", "select count(*) from sys.objects where type = 'P' and object_id = object_id('test.Test2')"));
Assert.AreEqual(SqlTask.ExecuteScalar <int>("Check if parameter exists"
, "select count(*) from sys.parameters where object_id = object_id('test.Test2')"), 2);
}
public void TestCreateTableWithPrimaryKey()
{
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("Key", "int", allowNulls: false, isPrimaryKey: true),
new TableColumn("value2", "datetime", allowNulls: true)
};
CreateTableTask.Create("test.Table3", columns);
Assert.AreEqual(2, SqlTask.ExecuteScalar <int>("Check if column exists", $"select count(*) from sys.columns where object_id = object_id('test.Table3')"));
Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if primary key exists", $"select count(*) from sys.key_constraints where parent_object_id = object_id('test.Table3')"));
Assert.AreEqual("pk_Table3_Key", SqlTask.ExecuteScalar("Check if primary key has correct naming", "select name from sys.key_constraints where parent_object_id = object_id('test.Table3')"));
Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if column is nullable", $"select case when is_nullable = 1 then 1 else 0 end from sys.columns where object_id = object_id('test.Table3') and name='value2'"));
}
public void TestCreateTableWithIdentity()
{
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value1", "int", allowNulls: false)
{
IsIdentity = true, IdentityIncrement = 1000, IdentitySeed = 50
}
};
CreateTableTask.Create("test.Table5", columns);
Assert.AreEqual(1, SqlTask.ExecuteScalar <int>("Check if column exists", $"select count(*) from sys.columns where object_id = object_id('test.Table5')"));
Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if column has identity"
, $@"select case when is_identity = 1 then 1 else 0 end from sys.columns cols inner join sys.types t on t.system_type_id = cols.system_type_id
where object_id = object_id('test.Table5') and cols.name = 'value1'"));
}
public void ExecuteScalarAsBool(IConnectionManager connection, string sqlBoolValue)
{
//Arrange
//Act
bool result = SqlTask.ExecuteScalarAsBool(connection,
"Test execute scalar as bool",
$"SELECT {sqlBoolValue} AS Bool");
//Assert
if (sqlBoolValue == "NULL")
{
Assert.False(result);
}
else
{
Assert.True(result);
}
}
public void TestCreateProcedure()
{
CRUDProcedureTask.CreateOrAlter("test.Test1", "select 1 as Test");
Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if proc exists", "select count(*) from sys.objects where type = 'P' and object_id = object_id('test.Test1')"));
}
public void TestExecuteScalarAsBool()
{
bool result = SqlTask.ExecuteScalarAsBool("Test execute scalar as bool", "select 1 as Bool");
Assert.IsTrue(result);
}
public void TestCreateView()
{
CRUDViewTask.CreateOrAlter("test.View1", "select 1 as Test");
Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if view exists", "select count(*) from sys.objects where type = 'V' and object_id = object_id('test.View1')"));
}