public void CreateOrAlterProcedureLogging() { //Arrange //Act CreateProcedureTask.CreateOrAlter(Connection, "dbo.Proc1", "SELECT 1 AS Test"); //Assert Assert.Equal(2, CountLogEntries("CREATEPROC")); }
public void CreateProcedure() { //Arrange //Act CreateProcedureTask.CreateOrAlter(Connection, "dbo.Proc1", "SELECT 1 AS Test"); //Assert Assert.Equal(1, RowCountTask.Count(Connection, "sys.objects", "type = 'P' AND object_id = object_id('dbo.Proc1')")); }
public void WaitForLongRunningProcedure() { //Arrange CreateProcedureTask.CreateOrAlter(SqlConnection, new ProcedureDefinition() { Name = "longrunning", Definition = @" WAITFOR DELAY '00:00:01'; SELECT @input + 10;", Parameter = new List <ProcedureParameter>() { new ProcedureParameter() { Name = "input", DataType = "INT" } // new ProcedureParameter() { Name ="result", Out = true, DataType="INT"} } }); var source = new MemorySource <MySimpleRow>(); source.DataAsList.Add(new MySimpleRow() { Col1 = 1, Col2 = "Test1" }); source.DataAsList.Add(new MySimpleRow() { Col1 = 2, Col2 = "Test2" }); source.DataAsList.Add(new MySimpleRow() { Col1 = 3, Col2 = "Test3" }); var dest = new MemoryDestination <MySimpleRow>(); var trans = new RowTransformation <MySimpleRow>( row => { row.Col1 = SqlTask.ExecuteScalar <int>(SqlConnection, "Read from procedure", $"EXEC longrunning @input = {row.Col1}") ?? 0; return(row); } ); //Act source.LinkTo(trans); trans.LinkTo(dest); source.Execute(); dest.Wait(); //Assert Assert.Collection <MySimpleRow>(dest.Data, row => Assert.Equal(11, row.Col1), row => Assert.Equal(12, row.Col1), row => Assert.Equal(13, row.Col1) ); }
public void AlterProcedure(IConnectionManager connection) { //Arrange CreateProcedureTask.CreateOrAlter(connection, "Proc2", "SELECT 1;"); IfProcedureExistsTask.IsExisting(connection, "Proc2"); //Act CreateProcedureTask.CreateOrAlter(connection, "Proc2", "SELECT 5;"); //Assert IfProcedureExistsTask.IsExisting(connection, "Proc2"); }
public void AlterProcedure() { //Arrange CreateProcedureTask.CreateOrAlter(Connection, "dbo.Proc2", "SELECT 1 AS Test"); Assert.Equal(1, RowCountTask.Count(Connection, "sys.objects", "type = 'P' AND object_id = object_id('dbo.Proc2') AND create_date = modify_date")); //Act CreateProcedureTask.CreateOrAlter(Connection, "dbo.Proc2", "SELECT 5 AS Test"); //Assert Assert.Equal(1, RowCountTask.Count(Connection, "sys.objects", "type = 'P' AND object_id = object_id('dbo.Proc2') AND create_date <> modify_date")); }
public void DropProcedure() { //Arrange CreateProcedureTask.CreateOrAlter(SqlConnection, "DropProc1", "SELECT 1"); Assert.True(IfExistsTask.IsExisting(SqlConnection, "DropProc1")); //Act DropProcedureTask.Drop(SqlConnection, "DropProc1"); //Assert Assert.False(IfExistsTask.IsExisting(SqlConnection, "DropProc1")); }
public void Drop(IConnectionManager connection) { //Arrange CreateProcedureTask.CreateOrAlter(connection, "DropProc1", "SELECT 1;"); Assert.True(IfProcedureExistsTask.IsExisting(connection, "DropProc1")); //Act DropProcedureTask.Drop(connection, "DropProc1"); //Assert Assert.False(IfProcedureExistsTask.IsExisting(connection, "DropProc1")); }
public void IfProcedureExists(IConnectionManager connection) { //Arrange var existsBefore = IfProcedureExistsTask.IsExisting(connection, "sp_test"); CreateProcedureTask.CreateOrAlter(connection, "sp_test", "SELECT 1;"); //Act var existsAfter = IfProcedureExistsTask.IsExisting(connection, "sp_test"); //Assert Assert.False(existsBefore); Assert.True(existsAfter); }
public void CreateProcedureWithParameter(IConnectionManager connection) { //Arrange List <ProcedureParameter> pars = new List <ProcedureParameter>() { new ProcedureParameter("Par1", "VARCHAR(10)"), new ProcedureParameter("Par2", "INT", "7"), }; //Act CreateProcedureTask.CreateOrAlter(connection, "Proc3", "SELECT 1;", pars); //Assert IfProcedureExistsTask.IsExisting(connection, "Proc3"); }
private void CreateTransferCompletedProcedure() { TransferCompletedForProcess = new CreateProcedureTask("etl.TransferCompletedForLoadProcess", $@"-- Set transfer completion date in load process UPDATE etl.LoadProcess SET TransferCompletedDate = getdate() WHERE LoadProcessKey = @LoadProcessKey " , new List <ProcedureParameter>() { new ProcedureParameter("LoadProcessKey", "int") }) { DisableLogging = true }; }
public void CreatProcedureWithProcedureObject(IConnectionManager connection) { //Arrange List <ProcedureParameter> pars = new List <ProcedureParameter>() { new ProcedureParameter("Par1", "varchar(10)"), new ProcedureParameter("Par2", "int", "7"), }; ProcedureDefinition procDef = new ProcedureDefinition("Proc4", "SELECT 1;", pars); //Act CreateProcedureTask.CreateOrAlter(connection, procDef); //Assert IfProcedureExistsTask.IsExisting(connection, "Proc4"); }
public void CreatProcedureWithParameter() { //Arrange List <ProcedureParameter> pars = new List <ProcedureParameter>() { new ProcedureParameter("Par1", "varchar(10)"), new ProcedureParameter("Par2", "int", "7"), }; //Act CreateProcedureTask.CreateOrAlter(Connection, "dbo.Proc3", "SELECT 1 AS Test", pars); //Assert Assert.Equal(1, RowCountTask.Count(Connection, "sys.objects", "type = 'P' AND object_id = object_id('dbo.Proc3')")); Assert.Equal(2, RowCountTask.Count(Connection, "sys.parameters", "object_id = object_id('dbo.Proc3')")); }
private void CreateAbortProcessProcedure() { AbortProcess = new CreateProcedureTask("etl.AbortLoadProcess", $@"-- Set entry in etlLoadProcess to aborted UPDATE etl.LoadProcess SET EndDate = getdate() , IsRunning = 0 , WasSuccessful = 0 , WasAborted = 1 , AbortMessage = @AbortMessage WHERE LoadProcessKey = @LoadProcessKey " , new List <ProcedureParameter>() { new ProcedureParameter("LoadProcessKey", "int"), new ProcedureParameter("AbortMessage", "nvarchar(4000)", ""), }) { DisableLogging = true }; }
private void CreateStartProcessProcedure() { StartProcess = new CreateProcedureTask("etl.StartLoadProcess", $@"-- Create entry in etlLoadProcess INSERT INTO etl.LoadProcess(StartDate, ProcessName, StartMessage, Source, IsRunning) SELECT getdate(),@ProcessName, @StartMessage,@Source, 1 as IsRunning SELECT @LoadProcessKey = SCOPE_IDENTITY()" , new List <ProcedureParameter>() { new ProcedureParameter("ProcessName", "nvarchar(100)"), new ProcedureParameter("StartMessage", "nvarchar(4000)", ""), new ProcedureParameter("Source", "nvarchar(20)", ""), new ProcedureParameter("LoadProcessKey", "int") { Out = true } }) { DisableLogging = true }; }
private void CreateEndProcessProcedure() { EndProcess = new CreateProcedureTask("etl.EndLoadProcess", $@"-- Set entry in etlLoadProcess to completed update etl.LoadProcess set EndDate = getdate() , IsRunning = 0 , WasSuccessful = 1 , WasAborted = 0 , EndMessage = @EndMessage where LoadProcessKey = @LoadProcessKey " , new List <ProcedureParameter>() { new ProcedureParameter("LoadProcessKey", "int"), new ProcedureParameter("EndMessage", "nvarchar(4000)", ""), }) { DisableLogging = true }; }