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')")); }