public Employee Add(Employee employee) { Int32 newEmployeeId = 0; const string sql = "INSERT INTO Employees VALUES (@FirstName, @LastName, @Email, @DepartmentId);" + "SELECT CAST(scope_identity() AS int)"; using (SqlConnection conn = new SqlConnection(_connectionString)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@FirstName", employee.FirstName); cmd.Parameters.AddWithValue("@LastName", employee.LastName); cmd.Parameters.AddWithValue("@Email", employee.Email); cmd.Parameters.AddWithValue("@DepartmentId", employee.DepartmentId); try { conn.Open(); newEmployeeId = (Int32)cmd.ExecuteScalar(); conn.Close(); } catch (Exception ex) { return null; } } employee.Id = (int)newEmployeeId; return employee; }
public void ShouldInsertAddressAsWellAsContactUsingSaveToDatabase() { var employee = new Employee { FirstName = "Quality", LastName = "Assurance", Email = "*****@*****.**", DepartmentId = 5, }; var address = new Address { StreetAddress = "1 Infinite Loop", StateId = 5, City = "Cupertino", ZipCode = "95014" }; employee.Addresses.Add(address); //Act _employeeRepository.Save(employee); //Assert employee.Id.Should().NotBe(0, "Identity should be assigned by the database"); employee.Addresses.Count().Should().NotBe(0); employee.Addresses[0].StreetAddress.Should().Be("1 Infinite Loop"); }
public void Save(Employee employee) { using (var transactionScope = new TransactionScope()) { if (employee.IsNew) { Add(employee); } else { Update(employee); } foreach (var address in employee.Addresses) { address.EmployeeId = employee.Id; if (address.IsNew) { Add(address); } else { Update(address); } } foreach (var roleId in employee.Roles.Select(x => x.Id).Distinct()) { Add(employee.Id, roleId); } transactionScope.Complete(); } }
public Employee Add(Employee employee) { try { employee.Id = _db.Query<int>("InsertNewEmployee", employee).Single(); } catch (SqlException slException) { return null; } return employee; }
public Employee Add(Employee employee) { const string sql = "INSERT INTO Employees (FirstName, LastName, Email, DepartmentId) VALUES (@FirstName, @LastName, @Email, @DepartmentId);" + "SELECT CAST(SCOPE_IDENTITY() as int)"; try { employee.Id = _db.QuerySql<int>(sql, employee).Single(); } catch (SqlException slException) { return null; } return employee; }
private long Add() { var stopwatch = new Stopwatch(); var employee = new Employee { FirstName = "TestAdd", LastName = "TestAdd", Email = "*****@*****.**", DepartmentId = 1 }; stopwatch.Start(); _employeeRepository.Add(employee); stopwatch.Stop(); return stopwatch.ElapsedMilliseconds; }
public IEnumerable<Employee> GetAll() { const string sqlQuery = "SELECT Id, FirstName, LastName, Email, DepartmentId FROM Employees"; var listOfEmployees = new List<Employee>(); using (var connection = new SqlConnection(_connectionString)) { var command = new SqlCommand(sqlQuery, connection); try { connection.Open(); var dataReader = command.ExecuteReader(); while (dataReader.Read()) { var tempEmployee = new Employee { Id = (int)dataReader[0], FirstName = (string)dataReader[1], LastName = (string)dataReader[2], Email = (string)dataReader[3], DepartmentId = (int)dataReader[4] }; listOfEmployees.Add(tempEmployee); } dataReader.Close(); connection.Close(); } catch (SqlException sqlException) { return null; } } return listOfEmployees; }
public void ShouldInsertEmployeeIntoDatabase() { //Arrange var employee = new Employee { FirstName = "Test", LastName = "Dummy", Email = "*****@*****.**", DepartmentId = 5, }; //Act _employeeRepository.Add(employee); //Assert employee.Id.Should().NotBe(0, "Identity should be assigned by the database"); }
private int InsertUserToBeModified() { var employee = new Employee { FirstName = "Modify", LastName = "Test", Email = "*****@*****.**", DepartmentId = 5, }; var address = new Address { StreetAddress = "1 Infinite Loop", StateId = 5, City = "Cupertino", ZipCode = "95014" }; employee.Addresses.Add(address); //Act _employeeRepository.Save(employee); //Assert employee.Id.Should().NotBe(0, "Identity should be assigned by the database"); employee.Addresses.Count().Should().NotBe(0); employee.Addresses[0].StreetAddress.Should().Be("1 Infinite Loop"); return employee.Id; }
private int InsertSimpleUserToBeModified() { //Arrange var employee = new Employee { FirstName = "Test", LastName = "Dummy", Email = "*****@*****.**", DepartmentId = 5, }; //Act _employeeRepository.Add(employee); //Assert employee.Id.Should().NotBe(0, "Identity should be assigned by the database"); return employee.Id; }
public void ShouldReturnNullIfInsertFails() { //Arrange var employee = new Employee { Id = 2, FirstName = "Fail" }; //Act var result = _employeeRepository.Add(employee); //Assert result.Should().BeNull(); }
public Employee Update(Employee employee) { const string sql = "UPDATE Employees " + "SET FirstName = @FirstName, " + " LastName = @LastName, " + " Email = @Email, " + " DepartmentId = @DepartmentId " + " WHERE Id = @Id"; _db.Execute(sql, employee); return employee; }
public Employee GetById(int employeeId) { Employee employee = null; const string sqlQuery = "Select Id, FirstName, LastName, Email, DepartmentId FROM Employees WHERE Id = @employeeId"; using (var connection = new SqlConnection(_connectionString)) { var command = new SqlCommand(sqlQuery, connection); command.Parameters.AddWithValue("@employeeId", employeeId); try { connection.Open(); var dataReader = command.ExecuteReader(); while (dataReader.Read()) { employee = new Employee { Id = (int)dataReader[0], FirstName = (string)dataReader[1], LastName = (string)dataReader[2], Email = (string)dataReader[3], DepartmentId = (int)dataReader[4] }; } dataReader.Close(); connection.Close(); } catch (SqlException sqlException) { return null; } } return employee; }
public Employee Update(Employee employee) { const string sql = "UPDATE Employees " + "SET FirstName = @FirstName, " + " LastName = @LastName, " + " Email = @Email, " + " DepartmentId = @DepartmentId " + " WHERE Id = @Id"; using (SqlConnection conn = new SqlConnection(_connectionString)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@FirstName", employee.FirstName); cmd.Parameters.AddWithValue("@LastName", employee.LastName); cmd.Parameters.AddWithValue("@Email", employee.Email); cmd.Parameters.AddWithValue("@DepartmentId", employee.DepartmentId); cmd.Parameters.AddWithValue("@Id", employee.Id); try { conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } catch (Exception ex) { return null; } } return employee; }
public Employee GetFullEmployeeInfo(int employeeId) { Employee employee = null; const string sqlForEmployee = "SELECT Id, FirstName, LastName, Email, DepartmentId FROM Employees WHERE Id = @EmployeeId;"; const string sqlForAddress = "SELECT Id, EmployeeId, StreetAddress, City, StateId, ZipCode FROM Addresses WHERE EmployeeId = @EmployeeId;"; const string sqlForRoles = "SELECT Role.Id, Role.Name FROM AssignedRoles, Role " + "WHERE Role.Id = AssignedRoles.RoleId AND " + "AssignedRoles.EmployeeId = @EmployeeId;"; using (SqlConnection conn = new SqlConnection(_connectionString)) { SqlCommand cmdForEmployee = new SqlCommand(sqlForEmployee, conn); cmdForEmployee.Parameters.AddWithValue("@EmployeeId", employeeId); conn.Open(); var dataReader = cmdForEmployee.ExecuteReader(); while (dataReader.Read()) { employee = new Employee { Id = (int)dataReader[0], FirstName = (string)dataReader[1], LastName = (string)dataReader[2], Email = (string)dataReader[3], DepartmentId = (int)dataReader[4] }; } dataReader.Close(); SqlCommand cmdForAddresses = new SqlCommand(sqlForAddress, conn); cmdForAddresses.Parameters.AddWithValue("@EmployeeId", employeeId); var addressReader = cmdForAddresses.ExecuteReader(); if (addressReader.HasRows) { while (addressReader.Read()) { var tempAddress = new Address { Id = (int)addressReader[0], EmployeeId = (int)addressReader[1], StreetAddress = (string)addressReader[2], City = (string)addressReader[3], StateId = (int)addressReader[4], ZipCode = (string)addressReader[5] }; if (employee != null) employee.Addresses.Add(tempAddress); } } addressReader.Close(); SqlCommand cmdForRoles = new SqlCommand(sqlForRoles, conn); cmdForRoles.Parameters.AddWithValue("@EmployeeId", employeeId); var assignedRolesReader = cmdForRoles.ExecuteReader(); if (assignedRolesReader.HasRows) { while (assignedRolesReader.Read()) { var tempRole = new Role { Id = (int)assignedRolesReader[0], Name = (string)assignedRolesReader[1] }; if (employee != null) employee.Roles.Add(tempRole); } } assignedRolesReader.Close(); conn.Close(); } return employee; }
public Employee Update(Employee employee) { _db.Execute("UpdateEmployeeInfo", employee); return employee; }