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
};
}
