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