/// <inheritdoc/>
public async Task <bool> UpdateEmployeeAsync(EmployeeTransferObject employee)
{
if (employee is null)
{
throw new ArgumentNullException(nameof(employee));
}
const string commandText =
@"UPDATE dbo.Employees SET LastName = @lastName, FirstName = @firstName, Title = @title, TitleOfCountesy = @titleOfCountesy, BirthDate = @birthDate, HireDate = @hireDate, Address = @address, City = @city, Region = @region, PostalCode = @postalCode, Country = @country, HomePhone = @homePhone, Extension = @extension, Photo = @photo, Notes = @notes, ReportsTo = @reportsTo, PhotoPath = @photoPath, Salary = @salary
WHERE EmployeeID = @employeeId
SELECT @@ROWCOUNT";
using (var command = new SqlCommand(commandText, this.connection))
{
AddSqlParameters(employee, command);
const string employeeId = "@employeeId";
command.Parameters.Add(employeeId, SqlDbType.Int);
command.Parameters[employeeId].Value = employee.Id;
this.OpenSqlConnectionIfItClose();
var result = await command.ExecuteScalarAsync();
return(((int)result) > 0);
}
}
/// <inheritdoc/>
public async Task <int> InsertEmployeeAsync(EmployeeTransferObject employee)
{
if (employee is null)
{
throw new ArgumentNullException(nameof(employee));
}
const string commandText =
@"INSERT INTO dbo.Employees (LastName, FirstName, Title, TitleOfCountesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath, Salary) OUTPUT Inserted.EmployeeID
VALUES (@lastName, @firstName, @title, @titleOfCountesy, @birthDate, @hireDate, @address, @city, @region, @postalCode, @country, @homePhone, @extension, @photo, @notes, @reportsTo, @photoPath, @salary)";
using (var command = new SqlCommand(commandText, this.connection))
{
AddSqlParameters(employee, command);
this.OpenSqlConnectionIfItClose();
var id = await command.ExecuteScalarAsync();
return((int)id);
}
}
private static void AddSqlParameters(EmployeeTransferObject employee, SqlCommand command)
{
const string employeeLastNameParameter = "@lastName";
command.Parameters.Add(employeeLastNameParameter, SqlDbType.NVarChar, 20);
command.Parameters[employeeLastNameParameter].IsNullable = true;
if (employee.LastName != null)
{
command.Parameters[employeeLastNameParameter].Value = employee.LastName;
}
else
{
command.Parameters[employeeLastNameParameter].Value = DBNull.Value;
}
const string employeeFirstNameParameter = "@firstName";
command.Parameters.Add(employeeFirstNameParameter, SqlDbType.NVarChar, 10);
command.Parameters[employeeFirstNameParameter].IsNullable = true;
if (employee.FirstName != null)
{
command.Parameters[employeeFirstNameParameter].Value = employee.FirstName;
}
else
{
command.Parameters[employeeFirstNameParameter].Value = DBNull.Value;
}
const string employeeTitleParameter = "@title";
command.Parameters.Add(employeeTitleParameter, SqlDbType.NVarChar, 30);
command.Parameters[employeeTitleParameter].IsNullable = true;
if (employee.Title != null)
{
command.Parameters[employeeTitleParameter].Value = employee.Title;
}
else
{
command.Parameters[employeeTitleParameter].Value = DBNull.Value;
}
const string employeeTitleOfCountesyParameter = "@titleOfCountesy";
command.Parameters.Add(employeeTitleOfCountesyParameter, SqlDbType.NVarChar, 25);
command.Parameters[employeeTitleOfCountesyParameter].IsNullable = true;
if (employee.TitleOfCountesy != null)
{
command.Parameters[employeeTitleOfCountesyParameter].Value = employee.TitleOfCountesy;
}
else
{
command.Parameters[employeeTitleOfCountesyParameter].Value = DBNull.Value;
}
const string employeeBirthDateParameter = "@birthDate";
command.Parameters.Add(employeeBirthDateParameter, SqlDbType.Date);
command.Parameters[employeeBirthDateParameter].Value = employee.BirthDate;
const string employeeHireDateParameter = "@hireDate";
command.Parameters.Add(employeeHireDateParameter, SqlDbType.Date);
command.Parameters[employeeHireDateParameter].Value = employee.HireDate;
const string employeeAddressParameter = "@address";
command.Parameters.Add(employeeAddressParameter, SqlDbType.NVarChar, 60);
command.Parameters[employeeAddressParameter].Value = employee.Address;
const string employeeCityParameter = "@city";
command.Parameters.Add(employeeCityParameter, SqlDbType.NVarChar, 15);
command.Parameters[employeeCityParameter].Value = employee.City;
const string employeeRegionParameter = "@region";
command.Parameters.Add(employeeRegionParameter, SqlDbType.NVarChar, 15);
command.Parameters[employeeRegionParameter].Value = employee.Region;
const string employeePostalCodeParameter = "@postalCode";
command.Parameters.Add(employeePostalCodeParameter, SqlDbType.NVarChar, 10);
command.Parameters[employeePostalCodeParameter].Value = employee.PostalCode;
const string employeeCountryParameter = "@country";
command.Parameters.Add(employeeCountryParameter, SqlDbType.NVarChar, 15);
command.Parameters[employeeCountryParameter].Value = employee.Country;
const string employeeHomePhoneParameter = "@homePhone";
command.Parameters.Add(employeeHomePhoneParameter, SqlDbType.NVarChar, 24);
command.Parameters[employeeHomePhoneParameter].Value = employee.HomePhone;
const string employeeExtensionParameter = "@extension";
command.Parameters.Add(employeeExtensionParameter, SqlDbType.NVarChar, 4);
command.Parameters[employeeExtensionParameter].Value = employee.Extension;
const string employeePhotoParameter = "@photo";
command.Parameters.Add(employeePhotoParameter, SqlDbType.Image);
command.Parameters[employeePhotoParameter].IsNullable = true;
if (employee.Photo != null)
{
command.Parameters[employeePhotoParameter].Value = employee.Photo;
}
else
{
command.Parameters[employeePhotoParameter].Value = DBNull.Value;
}
const string employeeNotesParameter = "@notes";
command.Parameters.Add(employeeNotesParameter, SqlDbType.Text);
command.Parameters[employeeNotesParameter].Value = employee.Notes;
const string employeeReportsToParameter = "@reportsTo";
command.Parameters.Add(employeeReportsToParameter, SqlDbType.Int);
command.Parameters[employeeReportsToParameter].Value = employee.ReportsTo;
const string employeePhotoPathParameter = "@photoPath";
command.Parameters.Add(employeePhotoPathParameter, SqlDbType.NVarChar, 255);
command.Parameters[employeePhotoPathParameter].Value = employee.PhotoPath;
const string employeeSalaryParameter = "@salary";
command.Parameters.Add(employeeSalaryParameter, SqlDbType.Float);
command.Parameters[employeeSalaryParameter].Value = employee.Salary;
}
public void SaveEmployee(EmployeeTransferObject request)
{
employeeList.Add(request);
}
