public void CreateOrUpdateEmployee(Employee employee)
{
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connectionString))
{
int counter = 0;
string query = @"SELECT COUNT(*)
FROM EMPLOYEE
WHERE EMPLOYEEID =" + employee.Id;
conn.Open();
var command = new SqlCommand(query, conn);
counter = (int)command.ExecuteScalar();
if (counter == 1)
{
string updateQuery = @"UPDATE EMPLOYEE
SET EMPLOYEEID = @employeeId,NAME=@employeeName,EMAIL=@employeeEmail,[DATE OF BIRTH]=@employeeBirthDate,MANAGERID=@employeeManagerId,DEPID=@employeeDepartmentId
WHERE EMPLOYEEID=" +employee.Id;
var updateCommand = new SqlCommand(updateQuery, conn);
updateCommand.Parameters.AddWithValue("@employeeId", employee.Id);
updateCommand.Parameters.AddWithValue("@employeeName", employee.Name);
updateCommand.Parameters.AddWithValue("@employeeEmail", employee.Email);
updateCommand.Parameters.AddWithValue("@employeeBirthDate", employee.BirthDate);
updateCommand.Parameters.AddWithValue("@employeeManagerId", employee.ManagerId);
updateCommand.Parameters.AddWithValue("@employeeDepartmentId", employee.DepartmentId);
updateCommand.ExecuteNonQuery();
}
else
{
string addQuery = @"INSERT INTO EMPLOYEE(EMPLOYEEID,NAME,EMAIL,[DATE OF BIRTH],MANAGERID,DEPID)
VALUES(@employeeId,@employeeName,@employeeEmail,@employeeBirthDate,@employeeManagerId,@employeeDepartmentId)";
var addCommand = new SqlCommand(addQuery, conn);
addCommand.Parameters.AddWithValue("@employeeId", employee.Id);
addCommand.Parameters.AddWithValue("@employeeName", employee.Name);
addCommand.Parameters.AddWithValue("@employeeEmail", employee.Email);
addCommand.Parameters.AddWithValue("@employeeBirthDate", employee.BirthDate);
addCommand.Parameters.AddWithValue("@employeeManagerId", employee.ManagerId);
addCommand.Parameters.AddWithValue("@employeeDepartmentId", employee.DepartmentId);
addCommand.ExecuteNonQuery();
}
}
}
public bool Insert(Employee employee)
{
using (var connection = new SqlConnection(this.connectionString))
{
connection.Open();
string query = @"SELECT count(*) FROM Employee c WHERE c.Id='" + employee.Id + "'";
var command = new SqlCommand(query, connection);
int rows = (int)command.ExecuteScalar();
if (rows == 1)
{
string managerId = employee.ManagerId?.ToString();
if (employee.ManagerId == null)
{
managerId = "NULL";
}
string departmentId = employee.DepartmentId?.ToString();
if (employee.DepartmentId == null)
{
departmentId = "NULL";
}
var birthDate = employee.DateOfBirth.Date.ToString("yyyyMMdd HH:mm:ss");
string updateQuery =
string.Format(
"UPDATE Employee SET Name='{0}',Email='{1}', DateOfBirth='{2}', ManagerId={3}, DepartamentId={4} WHERE Id={5}",
employee.Name,
employee.Email,
birthDate,
managerId,
departmentId,
employee.Id);
var updateCommand = new SqlCommand(updateQuery, connection);
updateCommand.ExecuteNonQuery();
return true;
}
else if (rows == 0)
{
string managerId = employee.ManagerId?.ToString();
if (employee.ManagerId == null)
{
managerId = "NULL";
}
string departmentId = employee.DepartmentId?.ToString();
if (employee.DepartmentId == null)
{
departmentId = "NULL";
}
string insertQuery = string.Format(
"INSERT INTO Employee VALUES('{0}','{1}','{2}',{3}, {4})",
employee.Name,
employee.Email,
employee.DateOfBirth,
managerId,
departmentId);
var insertCommand = new SqlCommand(insertQuery, connection);
insertCommand.ExecuteNonQuery();
employee.Id = this.GetLastId("Employee");
}
}
return true;
}
public List<Employee> ListEmployee()
{
List<Employee> employees = new List<Employee>();
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
string query = @"SELECT*
FROM[EMPLOYEE]";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
Employee emp = new Employee();
emp.Id = (int)reader["EMPLOYEEID"];
emp.Name = (string)reader["NAME"];
emp.Email = GetStringOrNull(reader["EMAIL"]);
emp.BirthDate = (DateTime)reader["DATE OF BIRTH"];
if (reader["DEPID"] == DBNull.Value)
{
emp.DepartmentId = null;
}
else
{
emp.DepartmentId = (int)reader["DEPID"];
}
if (reader["MANAGERID"] == DBNull.Value)
{
emp.DepartmentId = null;
}
else
{
emp.DepartmentId = (int)reader["MANAGERID"];
}
employees.Add(emp);
}
}
return employees;
}
public void UpdateOrInsert(Employee employee)
{
string query = $@"IF EXISTS (SELECT TOP 1 *
FROM Employees
WHERE EmployeeID = '{employee.EmployeeId}'
BEGIN
UPDATE Employees
SET FirstName = '{employee.FirstName}'
SET LastName = '{employee.LastName}'
SET Email = '{employee.Email}'
SET DateOfBirth = '{employee.DateOfBirth}'
SET ManagerID = '{employee.ManagerId}'
SET DepartmentID = '{employee.DepartmentId}'
WHERE EmployeeID = '{employee.EmployeeId}'
END
ELSE
BEGIN
SET IDENTITY_INSERT Employees ON
INSERT INTO Employees
VALUES ('{employee.EmployeeId}', '{employee.FirstName}', '{employee.LastName}', '{employee.Email}', '{employee.DateOfBirth}', '{employee.ManagerId}', '{employee.DepartmentId}')
SET IDENTITY_INSERT Employees OFF
END";
ExecuteNonQuery(query);
}
public List<Employee> GetEmployees()
{
List<Employee> rows = new List<Employee>();
string query = @"SELECT * FROM Employees";
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
var cmd = new SqlCommand(query, connection);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Employee emp = new Employee(
(int)reader["EmployeeID"],
(string)reader["FirstName"],
(string)reader["LastName"],
(string)GetObjectOrNull(reader["Email"]),
(DateTime)reader["DateOfBirth"],
(int?)GetObjectOrNull(reader["MANAGER"]),
(int?)GetObjectOrNull(reader["Department"])
);
rows.Add(emp);
}
}
}
return rows;
}