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