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;
        }
Example #2
0
 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;
 }
Example #3
0
 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();
         }
     }
 }
Example #4
0
 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);
 }