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