public void UpdateEmployee(EmployeeDetails emp) { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand( "UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, City = @City WHERE EmployeeID = @EmployeeID", con); cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 100)); cmd.Parameters["@FirstName"].Value = emp.FirstName; cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 100)); cmd.Parameters["@LastName"].Value = emp.LastName; cmd.Parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar, 100)); cmd.Parameters["@City"].Value = emp.City; cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4)); cmd.Parameters["@EmployeeID"].Value = emp.EmployeeID; try { con.Open(); cmd.ExecuteNonQuery(); } catch (SqlException) { throw new ApplicationException("Ошибка данных"); } finally { con.Close(); } }
public int InsertEmployee(EmployeeDetails emp) { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand( "INSERT INTO Employees (FirstName, LastName, City) OUTPUT INSERTED.EmployeeID VALUES (@FirstName, @LastName, @City)", con); cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 100)); cmd.Parameters["@FirstName"].Value = emp.FirstName; cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 100)); cmd.Parameters["@LastName"].Value = emp.LastName; cmd.Parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar, 100)); cmd.Parameters["@City"].Value = emp.City; try { con.Open(); int id = (int)cmd.ExecuteScalar(); return id; } catch (SqlException) { // Замените эту ошибку чем-то более специфичным. // Здесь также можно протоколировать ошибки throw new ApplicationException("Ошибка данных"); } finally { con.Close(); } }
public EmployeeDetails GetEmployee(int employeeID) { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("SELECT * FROM Employees WHERE EmployeeID = @EmployeeID", con); cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4)); cmd.Parameters["@EmployeeID"].Value = employeeID; try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow); // Получить новую строку reader.Read(); EmployeeDetails emp = new EmployeeDetails( (int)reader["EmployeeID"], (string)reader["FirstName"], (string)reader["LastName"], (string)reader["City"]); reader.Close(); return emp; } catch (SqlException) { throw new ApplicationException("Ошибка данных"); } finally { con.Close(); } }
public List<EmployeeDetails> GetEmployees() { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", con); // Создать коллекцию для всех записей о сотрудниках List<EmployeeDetails> employees = new List<EmployeeDetails>(); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { EmployeeDetails emp = new EmployeeDetails( (int)reader["EmployeeID"], (string)reader["FirstName"], (string)reader["LastName"], (string)reader["City"]); employees.Add(emp); } reader.Close(); return employees; } catch (SqlException) { throw new ApplicationException("Ошибка данных"); } finally { con.Close(); } }
public void DeleteEmployee(EmployeeDetails emp) { DeleteEmployee(emp.EmployeeID); }