public void TestAlterProcedure()
{
CRUDProcedureTask.CreateOrAlter("test.Test3", "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.Test3') and create_date = modify_date"));
CRUDProcedureTask.CreateOrAlter("test.Test3", "select 5 as Test");
Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if proc exists", "select count(*) from sys.objects where type = 'P' and object_id = object_id('test.Test3') and create_date <> modify_date"));
}
public void TestLogging()
{
CreateLogTablesTask.CreateLog();
CRUDProcedureTask.CreateOrAlter("test.Test4", "select 1 as Test");
Assert.AreEqual(4, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='CRUDPROC' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
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 Start()
{
//Basics
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;"));
DropDatabaseTask.Drop("DemoDB");
CreateDatabaseTask.Create("DemoDB");
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;Initial Catalog=DemoDB;"));
CreateSchemaTask.Create("demo");
CreateTableTask.Create("demo.table1", new List <TableColumn>()
{
new TableColumn(name: "key", dataType: "int", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn(name: "value", dataType: "nvarchar(100)", allowNulls: true)
});
SqlTask.ExecuteNonQuery("Insert data",
$@"insert into demo.table1 (value) select * from (values ('Ein Text'), ('Noch mehr Text')) as data(v)");
int count = RowCountTask.Count("demo.table1").Value;
Debug.WriteLine($"Found {count} entries in demo table!");
//Truncate:
//TruncateTableTask.Truncate("demo.table1");
//Batch processing / Go keyword:
//SqlTask.ExecuteNonQuery("sql with go keyword", @"insert into demo.table1 (value) select '####';
//go 2");
ControlFlow.CurrentDbConnection = new SMOConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;Initial Catalog=DemoDB;"));
SqlTask.ExecuteNonQuery("sql with go keyword", @"insert into demo.table1 (value) select '####';
go 2");
AddFileGroupTask.AddFileGroup("FGName", "DemoDB", "200MB", "10MB", isDefaultFileGroup: true);
CRUDProcedureTask.CreateOrAlter("demo.proc1", "select 1 as test");
}
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')"));
}