public int InsertEmployee(EmployeeTransferObject employeeTransferObject)
        {
            if (employeeTransferObject == null)
            {
                throw new ArgumentNullException(nameof(employeeTransferObject));
            }

            const string commandText =
                @"INSERT INTO dbo.Employees (LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath) OUTPUT Inserted.EmployeeID
VALUES (@lastName, @firstName, @title, @titleOfCourtesy, @birthDate, @hireDate, @address, @city, @region, @postalCode, @country, @homePhone, @extension, @photo, @notes, @reportsTo, @photoPath)";

            using (var command = new SqlCommand(commandText, this.connection))
            {
                AddSqlParameters(employeeTransferObject, command);

                var id = command.ExecuteScalar();
                return((int)id);
            }
        }
        public bool UpdateEmployee(EmployeeTransferObject employeeTransferObject)
        {
            if (employeeTransferObject == null)
            {
                throw new ArgumentNullException(nameof(employeeTransferObject));
            }

            const string commandText =
                @"UPDATE dbo.Employees SET LastName = @lastName, FirstName = @firstName, Title = @title, TitleOfCourtesy = @titleOfCourtesy, 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
WHERE EmployeeID = @employeeId
SELECT @@ROWCOUNT";

            using (var command = new SqlCommand(commandText, this.connection))
            {
                AddSqlParameters(employeeTransferObject, command);

                const string employeeId = "@employeeId";
                command.Parameters.Add(employeeId, SqlDbType.Int);
                command.Parameters[employeeId].Value = employeeTransferObject.Id;

                var result = command.ExecuteScalar();
                return(((int)result) > 0);
            }
        }
        private static void AddSqlParameters(EmployeeTransferObject employeeTransferObject, SqlCommand command)
        {
            const string lastNameParameter = "@lastName";

            command.Parameters.Add(lastNameParameter, SqlDbType.NVarChar, 20);
            command.Parameters[lastNameParameter].Value = employeeTransferObject.LastName;

            const string firstNameParameter = "@firstname";

            command.Parameters.Add(firstNameParameter, SqlDbType.NVarChar, 10);
            command.Parameters[firstNameParameter].Value = employeeTransferObject.FirstName;

            const string titleParameter = "@title";

            command.Parameters.Add(titleParameter, SqlDbType.NVarChar, 30);
            command.Parameters[titleParameter].IsNullable = true;

            if (employeeTransferObject.Title != null)
            {
                command.Parameters[titleParameter].Value = employeeTransferObject.Title;
            }
            else
            {
                command.Parameters[titleParameter].Value = DBNull.Value;
            }

            const string titleOfCourtesyParameter = "@titleOfCourtesy";

            command.Parameters.Add(titleOfCourtesyParameter, SqlDbType.NVarChar, 25);
            command.Parameters[titleOfCourtesyParameter].IsNullable = true;

            if (employeeTransferObject.TitleOfCourtesy != null)
            {
                command.Parameters[titleOfCourtesyParameter].Value = employeeTransferObject.TitleOfCourtesy;
            }
            else
            {
                command.Parameters[titleOfCourtesyParameter].Value = DBNull.Value;
            }

            const string birthDateParameter = "@birthDate";

            command.Parameters.Add(birthDateParameter, SqlDbType.DateTime);
            command.Parameters[birthDateParameter].IsNullable = true;

            if (employeeTransferObject.BirthDate != null)
            {
                command.Parameters[birthDateParameter].Value = employeeTransferObject.BirthDate;
            }
            else
            {
                command.Parameters[birthDateParameter].Value = DBNull.Value;
            }

            const string hireDateParameter = "@hireDate";

            command.Parameters.Add(hireDateParameter, SqlDbType.DateTime);
            command.Parameters[hireDateParameter].IsNullable = true;

            if (employeeTransferObject.HireDate != null)
            {
                command.Parameters[hireDateParameter].Value = employeeTransferObject.HireDate;
            }
            else
            {
                command.Parameters[hireDateParameter].Value = DBNull.Value;
            }

            const string addressParameter = "@address";

            command.Parameters.Add(addressParameter, SqlDbType.NVarChar, 60);
            command.Parameters[addressParameter].IsNullable = true;

            if (employeeTransferObject.Address != null)
            {
                command.Parameters[addressParameter].Value = employeeTransferObject.Address;
            }
            else
            {
                command.Parameters[addressParameter].Value = DBNull.Value;
            }

            const string cityParameter = "@city";

            command.Parameters.Add(cityParameter, SqlDbType.NVarChar, 15);
            command.Parameters[cityParameter].IsNullable = true;

            if (employeeTransferObject.City != null)
            {
                command.Parameters[cityParameter].Value = employeeTransferObject.City;
            }
            else
            {
                command.Parameters[cityParameter].Value = DBNull.Value;
            }

            const string regionParameter = "@region";

            command.Parameters.Add(regionParameter, SqlDbType.NVarChar, 15);
            command.Parameters[regionParameter].IsNullable = true;

            if (employeeTransferObject.Region != null)
            {
                command.Parameters[regionParameter].Value = employeeTransferObject.Region;
            }
            else
            {
                command.Parameters[regionParameter].Value = DBNull.Value;
            }

            const string postalCodeParameter = "@postalCode";

            command.Parameters.Add(postalCodeParameter, SqlDbType.NVarChar, 10);
            command.Parameters[postalCodeParameter].IsNullable = true;

            if (employeeTransferObject.PostalCode != null)
            {
                command.Parameters[postalCodeParameter].Value = employeeTransferObject.PostalCode;
            }
            else
            {
                command.Parameters[postalCodeParameter].Value = DBNull.Value;
            }

            const string countryParameter = "@country";

            command.Parameters.Add(countryParameter, SqlDbType.NVarChar, 15);
            command.Parameters[countryParameter].IsNullable = true;

            if (employeeTransferObject.Country != null)
            {
                command.Parameters[countryParameter].Value = employeeTransferObject.Country;
            }
            else
            {
                command.Parameters[countryParameter].Value = DBNull.Value;
            }

            const string homePhoneParameter = "@homePhone";

            command.Parameters.Add(homePhoneParameter, SqlDbType.NVarChar, 24);
            command.Parameters[homePhoneParameter].IsNullable = true;

            if (employeeTransferObject.HomePhone != null)
            {
                command.Parameters[homePhoneParameter].Value = employeeTransferObject.HomePhone;
            }
            else
            {
                command.Parameters[homePhoneParameter].Value = DBNull.Value;
            }

            const string extensionParameter = "@extension";

            command.Parameters.Add(extensionParameter, SqlDbType.NVarChar, 4);
            command.Parameters[extensionParameter].IsNullable = true;

            if (employeeTransferObject.Extension != null)
            {
                command.Parameters[extensionParameter].Value = employeeTransferObject.Extension;
            }
            else
            {
                command.Parameters[extensionParameter].Value = DBNull.Value;
            }

            const string photoParameter = "@photo";

            command.Parameters.Add(photoParameter, SqlDbType.Image);
            command.Parameters[photoParameter].IsNullable = true;

            if (employeeTransferObject.Photo != null)
            {
                command.Parameters[photoParameter].Value = employeeTransferObject.Photo;
            }
            else
            {
                command.Parameters[photoParameter].Value = DBNull.Value;
            }

            const string notesParameter = "@notes";

            command.Parameters.Add(notesParameter, SqlDbType.NText);
            command.Parameters[notesParameter].IsNullable = true;

            if (employeeTransferObject.Notes != null)
            {
                command.Parameters[notesParameter].Value = employeeTransferObject.Notes;
            }
            else
            {
                command.Parameters[notesParameter].Value = DBNull.Value;
            }

            const string reportsToParameter = "@reportsTo";

            command.Parameters.Add(reportsToParameter, SqlDbType.Int);
            command.Parameters[reportsToParameter].IsNullable = true;

            if (employeeTransferObject.ReportsTo != null)
            {
                command.Parameters[reportsToParameter].Value = employeeTransferObject.ReportsTo;
            }
            else
            {
                command.Parameters[reportsToParameter].Value = DBNull.Value;
            }

            const string photoPathParameter = "@photoPath";

            command.Parameters.Add(photoPathParameter, SqlDbType.NVarChar, 255);
            command.Parameters[photoPathParameter].IsNullable = true;

            if (employeeTransferObject.ReportsTo != null)
            {
                command.Parameters[photoPathParameter].Value = employeeTransferObject.ReportsTo;
            }
            else
            {
                command.Parameters[photoPathParameter].Value = DBNull.Value;
            }
        }