public bool Insert(Department department)
{
using (var connection = new SqlConnection(this.connectionString))
{
connection.Open();
string query = @"SELECT count(*) FROM Departament c WHERE c.Id='" + department.Id + "'";
var command = new SqlCommand(query, connection);
int rows = (int)command.ExecuteScalar();
if (rows == 1)
{
string updateQuery =
string.Format(
"UPDATE Departament SET Name='{0} 'WHERE Id={1}",
department.Name,
department.Id);
var updateCommand = new SqlCommand(updateQuery, connection);
updateCommand.ExecuteNonQuery();
return true;
}
else if (rows == 0)
{
string insertQuery = string.Format(
"INSERT INTO Departament VALUES('{0}')",
department.Name);
var insertCommand = new SqlCommand(insertQuery, connection);
insertCommand.ExecuteNonQuery();
department.Id = this.GetLastId("Departament");
}
}
return true;
}
public Department GetDepratmentByID(int id)
{
Department dep = new Department();
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
string query = @"SELECT*
FROM DEPARTMENT
WHERE DEPID=" + id;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
dep.Name = (string)reader["NAME"];
dep.DEPID = (int)reader["DEPID"];
}
}
return dep;
}
public void CreateOrUpdateDepartment(Department department)
{
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string query = @"SELECT COUNT(*)
FROM DEPARTMENT
WHERE DEPID=" + department.DEPID;
connection.Open();
var command = new SqlCommand(query, connection);
int counter = (int)command.ExecuteScalar();
if (counter==1)
{
string updateQuery = @"UPDATE DEPARTMENT
SET NAME=@departmentName,DEPID=@departmentDEPID
WHERE DEPID="+department.DEPID;
var updateCommand = new SqlCommand(updateQuery, connection);
updateCommand.Parameters.AddWithValue("@departmentName", department.Name);
updateCommand.Parameters.AddWithValue("@departmentDEPID", department.DEPID);
updateCommand.ExecuteNonQuery();
}
else
{
string addQuery = @"INSERT INTO DEPARTMENT(NAME,DEPID)
VALUES (@departmentName,@departmentDEPID)";
var addCommand = new SqlCommand(addQuery, connection);
addCommand.Parameters.AddWithValue("@departmentName", department.Name);
addCommand.Parameters.AddWithValue("@departmentDEPID", department.DEPID);
addCommand.ExecuteNonQuery();
}
}
}
public List<Department> ListDepartments()
{
List<Department> depts = new List<Department>();
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
string query = @"SELECT*
FROM DEPARTMENT";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
Department dep = new Department();
dep.Name = (string)reader["NAME"];
dep.DEPID = (int)reader["DEPID"];
depts.Add(dep);
}
}
return depts;
}
public void UpdateOrInsert(Department department)
{
string query = $@"IF EXISTS (SELECT TOP 1 *
FROM Departments
WHERE DepartmentID = '{department.DepartmentId}')
BEGIN
UPDATE Departments
SET Name = '{department.Name}'
WHERE DepartmentID = '{department.DepartmentId}'
END
ELSE
BEGIN
SET IDENTITY_INSERT Departments ON
INSERT INTO Departments
VALUES ('{department.DepartmentId}', '{department.Name}')
SET IDENTITY_INSERT Departments OFF
END";
ExecuteNonQuery(query);
}