static void Main(string[] args)
{
//1. Back up Employee Table
DatabaseBackupJob.BackUp();
//2. Drop Employee Table & Create it again
DatabaseCreateJob.Create();
//3. Read the Employees.csv using CSVHelper
EmployeeReader reader = new EmployeeReader();
List <Employee> employees = reader.Read(ConfigurationManager.AppSettings["DataFile"]);
//4. Insert each Employee into EmployeeTable
SqlBulkCopyJob.Copy(employees);
//5. Run All PLSQLs
/*
* CALCULATED TABLES
*
* STORED PROCEDURE
* CALCULATE_HEADCOUNT_BY_DEMOGRAPHICS
*
*
*
*
*
*/
}
public void TestEmployeeReader()
{
EmployeeReader reader = new EmployeeReader();
List <Employee> employees = reader.Read("../../../employees.csv");
Assert.IsNotNull(employees);
//var connectionString = ConfigurationManager.ConnectionStrings["dynamiconnection"].ConnectionString;
var connectionString = "data source = mauintapp01; initial catalog = Attrition; User ID = DashboardUser; Password = abc?123!; multipleactiveresultsets = True";
var dataTable = new DataTable();
dataTable.Columns.Add("EmployeeNumber");
dataTable.Columns.Add("FirstName");
dataTable.Columns.Add("LastName");
dataTable.Columns.Add("District");
dataTable.Columns.Add("DepartmentName");
dataTable.Columns.Add("Site");
dataTable.Columns.Add("OnSiteDepartment");
dataTable.Columns.Add("HireDate");
dataTable.Columns.Add("LocationName");
dataTable.Columns.Add("PositionName");
dataTable.Columns.Add("ManagerDisplayName");
dataTable.Columns.Add("TerminationDate");
dataTable.Columns.Add("EmploymentStatusName");
dataTable.Columns.Add("IsVoluntaryTermination");
dataTable.Columns.Add("TerminationRegretType");
dataTable.Columns.Add("EmployeeGender");
dataTable.Columns.Add("EmployeeBirthDate");
dataTable.Columns.Add("PrimaryAddressCity");
dataTable.Columns.Add("MaritialStatusName");
dataTable.Columns.Add("ShiftRotationName");
dataTable.Columns.Add("JobLevel");
Dictionary <int, Employee> map = new Dictionary <int, Employee>();
foreach (Employee employee in employees)
{
Console.WriteLine(" Row " + employees.IndexOf(employee) + 1);
if (!map.ContainsKey(employee.EmployeeNumber))
{
dataTable.Rows.Add(employee.EmployeeNumber, employee.FirstName, employee.LastName, employee.District, employee.DepartmentName, employee.Site ?? (object)DBNull.Value, employee.OnSiteDepartment, employee.HireDate, employee.LocationName, employee.PositionName, employee.ManagerDisplayName, employee.TerminationDate, employee.EmploymentStatusName, employee.IsVoluntaryTermination, employee.TerminationRegretType, employee.EmployeeGender, employee.EmployeeBirthDate, employee.PrimaryAddressCity, employee.MaritialStatusName, employee.ShiftRotationName, employee.JobLevel);
map.Add(employee.EmployeeNumber, employee);
}
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (var sqlBulk = new SqlBulkCopy(connectionString))
{
sqlBulk.DestinationTableName = "EmployeeTable";
sqlBulk.WriteToServer(dataTable);
}
}
}
public async Task <IActionResult> Remove(int employeeId)
{
var employee = await EmployeeReader.Read(employeeId);
if (employee.HasNoValue)
{
return(NotFound());
}
await EmployeeWriter.Remove(employee.Value);
return(Ok());
}
public async Task Read_When_EmployeeId_Exists_Should_Return_Employee(Employee employee)
{
var options = DbContextOptionsFactory.Create(nameof(Read_When_EmployeeId_Exists_Should_Return_Employee));
using (var context = new EmployeeManagerContext(options))
{
context.Add(employee);
await context.SaveChangesAsync();
}
using (var context = new EmployeeManagerContext(options))
{
var sut = new EmployeeReader(context);
var actual = await sut.Read(employee.Id);
actual.Should().NotBeNull();
actual.Value.Id.Should().Be(employee.Id);
}
}
public async Task Read_When_EmployeeId_Does_Not_Exist_Should_Return_Maybe_None(
Employee employee,
int someEmployeeId)
{
var options = DbContextOptionsFactory.Create(nameof(Read_When_EmployeeId_Does_Not_Exist_Should_Return_Maybe_None));
using (var context = new EmployeeManagerContext(options))
{
context.Add(employee);
await context.SaveChangesAsync();
}
using (var context = new EmployeeManagerContext(options))
{
var sut = new EmployeeReader(context);
var actual = await sut.Read(someEmployeeId);
actual.Should().Be(Maybe <Employee> .None);
}
}
public async Task Read_Should_Paginated_Employees(List <Employee> employees)
{
const int pageIndex = 1;
const int pageSize = 1;
var options = DbContextOptionsFactory.Create(nameof(Read_Should_Paginated_Employees));
using (var context = new EmployeeManagerContext(options))
{
context.AddRange(employees);
await context.SaveChangesAsync();
}
using (var context = new EmployeeManagerContext(options))
{
var sut = new EmployeeReader(context);
var actual = await sut.Read(pageIndex, pageSize);
actual.Count.Should().Be(pageSize);
}
}