public bool IsDepartmentExists(DataAccess dataAccess, string name) { bool result = false; try { command = new SqlCommand(DEPARTMENT_NAME_CHECK, dataAccess.GetConnection()); command.Parameters.AddWithValue("@name", name); reader = command.ExecuteReader(); while (reader.Read()) { if (reader.GetInt32(reader.GetOrdinal("result")) == 1) { result = true; } else { result = false; } } reader.Close(); } catch (SqlException exc) { } return(result); }
public void DeleteEventRooms(DataAccess dataAccess, int eventId) { command = new SqlCommand(DELETE_EVENT_ROOMS, dataAccess.GetConnection()); command.Parameters.AddWithValue("@eventId", eventId); command.ExecuteNonQuery(); }
public void DeleteInformation(DataAccess dataAccess, int informationId) { command = new SqlCommand(DELETE, dataAccess.GetConnection()); command.Parameters.AddWithValue("@informationId", informationId); command.ExecuteNonQuery(); }
public List <InformationEntity> FetchAllInformations(DataAccess dataAccess) { List <InformationEntity> informations = new List <InformationEntity>(); try { command = new SqlCommand(FETCH_ALL_INFORMATIONS, dataAccess.GetConnection()); reader = command.ExecuteReader(); while (reader.Read()) { InformationEntity informationEntity = new InformationEntity(); informationEntity.Id = reader.GetInt32(reader.GetOrdinal("id")); informationEntity.Topic = reader.GetString(reader.GetOrdinal("topic")); informationEntity.Input = reader.GetString(reader.GetOrdinal("information")); informationEntity.IsActive = reader.GetBoolean(reader.GetOrdinal("isActivated")); DepartmentEntity departmentEntity = new DepartmentEntity(); departmentEntity.Id = reader.GetInt32(reader.GetOrdinal("id")); departmentEntity.Name = reader.GetString(reader.GetOrdinal("name")); informationEntity.DepartmentEntity = departmentEntity; informations.Add(informationEntity); } reader.Close(); } catch (SqlException exc) { } return(informations); }
public List <RoomEntity> FetchDepartmentRooms(DataAccess dataAccess, int departmentId) { List <RoomEntity> rooms = new List <RoomEntity>(); try { command = new SqlCommand(FECTH_DEPARTMENT_ROOMS, dataAccess.GetConnection()); command.Parameters.AddWithValue("@department_id", departmentId); reader = command.ExecuteReader(); while (reader.Read()) { RoomEntity roomEntity = new RoomEntity(); roomEntity.Id = reader.GetInt32(reader.GetOrdinal("id")); roomEntity.DepartmentId = reader.GetInt32(reader.GetOrdinal("department_id")); roomEntity.Identifier = reader.GetString(reader.GetOrdinal("identifier")); rooms.Add(roomEntity); } reader.Close(); } catch (SqlException exc) { } eventsCount = rooms.Count; return(rooms); }
public List <RoomEntity> FetchAllRooms(DataAccess dataAccess, int departmentId) { List <RoomEntity> rooms = new List <RoomEntity>(); try { command = new SqlCommand(FETCH_ALL_ROOMS, dataAccess.GetConnection()); command.Parameters.AddWithValue("@department_id", departmentId); reader = command.ExecuteReader(); while (reader.Read()) { RoomEntity roomEntity = new RoomEntity(); DepartmentEntity departmentEntity = new DepartmentEntity(); roomEntity.Id = reader.GetInt32(reader.GetOrdinal("id")); roomEntity.Identifier = reader.GetString(reader.GetOrdinal("identifier")); roomEntity.DepartmentId = reader.GetInt32(reader.GetOrdinal("department_id")); departmentEntity.Id = reader.GetInt32(reader.GetOrdinal("department_id")); departmentEntity.Name = reader.GetString(reader.GetOrdinal("name")); roomEntity.DepartmentEntity = departmentEntity; rooms.Add(roomEntity); } reader.Close(); } catch (SqlException exc) { } return(rooms); }
public List <EventEntity> FetchEventsByDepartmentAndHost(DataAccess dataAccess, int departmentId, string host) { List <EventEntity> events = new List <EventEntity>(); EventEntity eventEntity = new EventEntity(); try { command = new SqlCommand(FECTH_EVENTS_BY_DEPARTEMENT_AND_HOST, dataAccess.GetConnection()); command.Parameters.AddWithValue("@departmentId", departmentId); string hostString = string.Format("%{0}%", host); command.Parameters.AddWithValue("@host", hostString); reader = command.ExecuteReader(); while (reader.Read()) { int eventId = reader.GetInt32(reader.GetOrdinal("id")); if (eventEntity.Id != eventId) { eventEntity = new EventEntity(); eventEntity.Id = eventId; eventEntity.Topic = reader.GetString(reader.GetOrdinal("topic")); eventEntity.Host = reader.GetString(reader.GetOrdinal("host")); eventEntity.FromDate = reader.GetDateTime(reader.GetOrdinal("event_startdate")); eventEntity.ToDate = reader.GetDateTime(reader.GetOrdinal("event_enddate")); eventEntity.IsCanceled = reader.GetBoolean(reader.GetOrdinal("isCanceled")); DepartmentEntity departmentEntity = new DepartmentEntity(); departmentEntity.Id = reader.GetInt32(reader.GetOrdinal("department_id")); departmentEntity.Name = reader.GetString(reader.GetOrdinal("name")); eventEntity.DepartmentEntity = departmentEntity; RoomEntity re = new RoomEntity(); re.Id = reader.GetInt32(reader.GetOrdinal("room_id")); re.Identifier = reader.GetString(reader.GetOrdinal("identifier")); re.DepartmentEntity = departmentEntity; eventEntity.Rooms.Add(re); events.Add(eventEntity); } else { RoomEntity roomEntity = new RoomEntity(); roomEntity.Id = reader.GetInt32(reader.GetOrdinal("room_id")); roomEntity.Identifier = reader.GetString(reader.GetOrdinal("identifier")); eventEntity.Rooms.Add(roomEntity); } } reader.Close(); } catch (SqlException exc) { } eventsCount = events.Count; return(events); }
public bool Delete(DataAccess dataAccess, int id) { command = new SqlCommand(DELETE, dataAccess.GetConnection()); command.Parameters.AddWithValue("@id", id); isChanged = command.ExecuteNonQuery(); if (isChanged > 0) { return(true); } return(false); }
public EventEntity FetchById(DataAccess dataAccess, int id) { EventEntity eventEntity = null; try { command = new SqlCommand(FETCH_BY_ID, dataAccess.GetConnection()); command.Parameters.AddWithValue("@event_id", id); reader = command.ExecuteReader(); while (reader.Read()) { if (eventEntity == null) { eventEntity = new EventEntity(); eventEntity.Id = reader.GetInt32(reader.GetOrdinal("id")); eventEntity.Topic = reader.GetString(reader.GetOrdinal("topic")); eventEntity.Host = reader.GetString(reader.GetOrdinal("host")); eventEntity.FromDate = reader.GetDateTime(reader.GetOrdinal("event_startdate")); eventEntity.ToDate = reader.GetDateTime(reader.GetOrdinal("event_enddate")); eventEntity.IsCanceled = reader.GetBoolean(reader.GetOrdinal("isCanceled")); try { RoomEntity roomEntity = new RoomEntity(); roomEntity.Identifier = reader.GetString(reader.GetOrdinal("identifier")); roomEntity.Id = reader.GetInt32(reader.GetOrdinal("room_id")); roomEntity.DepartmentId = reader.GetInt32(reader.GetOrdinal("department_id")); eventEntity.Rooms.Add(roomEntity); } catch (SqlNullValueException exc) { // We need to ignore it, because a given event may be created without rooms and department. } } else { try { RoomEntity roomEntity = new RoomEntity(); roomEntity.Identifier = reader.GetString(reader.GetOrdinal("identifier")); roomEntity.Id = reader.GetInt32(reader.GetOrdinal("room_id")); roomEntity.DepartmentId = reader.GetInt32(reader.GetOrdinal("department_id")); eventEntity.Rooms.Add(roomEntity); } catch (SqlNullValueException exc) { // We need to ignore it, because a given event may be created without rooms and department. } } } reader.Close(); } catch (SqlException exc) { } return(eventEntity); }
public bool InsertDepartment(DataAccess dataAccess, string departmentName) { command = new SqlCommand(INSERT, dataAccess.GetConnection()); command.Parameters.AddWithValue("@name", departmentName); lastInsertedId = (int)command.ExecuteScalar(); if (lastInsertedId != null) { return(true); } return(false); }
public bool InsertRoom(DataAccess dataAccess, RoomEntity roomEntity) { command = new SqlCommand(INSERT, dataAccess.GetConnection()); command.Parameters.AddWithValue("@identifier", roomEntity.Identifier); command.Parameters.AddWithValue("@department", roomEntity.DepartmentId); int isChanged = (int)command.ExecuteNonQuery(); if (isChanged != 0) { return(true); } return(false); }
public bool Update_status(DataAccess dataAccess, int?informationId, bool isActivated) { command = new SqlCommand(UPDATE_STATUS, dataAccess.GetConnection()); command.Parameters.AddWithValue("@id", informationId); command.Parameters.AddWithValue("@isActivated", isActivated); int isChanged = command.ExecuteNonQuery(); if (isChanged == 1) { return(true); } return(false); }
public void InsertEventRooms(DataAccess dataAccess, List <int> rooms, int eventId) { try { command = new SqlCommand(INSERT_ROOMS_TO_EVENT, dataAccess.GetConnection()); command.Parameters.Add(new SqlParameter("@eventId", SqlDbType.Int)); command.Parameters.Add(new SqlParameter("@roomId", SqlDbType.Int)); foreach (int id in rooms) { command.Parameters[0].Value = eventId; command.Parameters[1].Value = id; command.ExecuteNonQuery(); } } catch (SqlException exc) { } }
public bool Insert(DataAccess dataAccess, EventEntity eventEntity) { command = new SqlCommand(INSERT, dataAccess.GetConnection()); command.Parameters.AddWithValue("@topic", eventEntity.Topic); command.Parameters.AddWithValue("@host", eventEntity.Host); command.Parameters.AddWithValue("@event_startdate", eventEntity.FromDate); command.Parameters.AddWithValue("@event_enddate", eventEntity.ToDate); lastInsertedId = (int)command.ExecuteScalar(); if (lastInsertedId != null) { return(true); } return(false); }
public bool InsertInformation(DataAccess dataAccess, InformationEntity informationsEntity) { command = new SqlCommand(INSERT, dataAccess.GetConnection()); command.Parameters.AddWithValue("@topic", informationsEntity.Topic); command.Parameters.AddWithValue("@information", informationsEntity.Input); command.Parameters.AddWithValue("@department", informationsEntity.DepartmentId); try { int isChanged = (int)command.ExecuteNonQuery(); if (isChanged != 0) { return(true); } } catch (SqlException exc) { } return(false); }
public bool Update(DataAccess dataAccess, EventEntity eventEntity) { command = new SqlCommand(UPDATE, dataAccess.GetConnection()); command.Parameters.AddWithValue("@id", eventEntity.Id); command.Parameters.AddWithValue("@topic", eventEntity.Topic); command.Parameters.AddWithValue("@host", eventEntity.Host); command.Parameters.AddWithValue("@event_startdate", eventEntity.FromDate); command.Parameters.AddWithValue("@event_enddate", eventEntity.ToDate); isChanged = command.ExecuteNonQuery(); if (isChanged == 1) { return(true); } return(false); }
public bool IsRoomExists(DataAccess dataAccess, string identifier, string department_id) { bool result = false; try { command = new SqlCommand(ROOM_NAME_CHECK, dataAccess.GetConnection()); command.Parameters.AddWithValue("@identifier", identifier); command.Parameters.AddWithValue("@department_id", department_id); reader = command.ExecuteReader(); while (reader.Read()) { if (reader.GetInt32(reader.GetOrdinal("result")) != 0) { result = true; } } reader.Close(); } catch (SqlException exc) { } return(result); }
public List <DepartmentEntity> FetchAllDepartments(DataAccess dataAccess) { List <DepartmentEntity> departments = new List <DepartmentEntity>(); try { command = new SqlCommand(FECTH_ALL_DEPARTMENTS, dataAccess.GetConnection()); reader = command.ExecuteReader(); while (reader.Read()) { DepartmentEntity departmentEntity = new DepartmentEntity(); departmentEntity.Id = reader.GetInt32(reader.GetOrdinal("id")); departmentEntity.Name = reader.GetString(reader.GetOrdinal("name")); departments.Add(departmentEntity); } reader.Close(); } catch (SqlException exc) { } eventsCount = departments.Count; return(departments); }
public static void PurgeInformation(DataAccess dataAccess) { SqlCommand commandPurge = new SqlCommand(PURGE, dataAccess.GetConnection()); commandPurge.ExecuteNonQuery(); }
public List <EventEntity> FetchAllEvents(DataAccess dataAccess) { List <EventEntity> events = new List <EventEntity>(); EventEntity eventEntity = new EventEntity(); try { command = new SqlCommand(FECTH_ALL_EVENTS, dataAccess.GetConnection()); reader = command.ExecuteReader(); while (reader.Read()) { int eventId = reader.GetInt32(reader.GetOrdinal("id")); if (eventEntity.Id != eventId) { eventEntity = new EventEntity(); eventEntity.Id = eventId; eventEntity.Topic = reader.GetString(reader.GetOrdinal("topic")); eventEntity.Host = reader.GetString(reader.GetOrdinal("host")); eventEntity.FromDate = reader.GetDateTime(reader.GetOrdinal("event_startdate")); eventEntity.ToDate = reader.GetDateTime(reader.GetOrdinal("event_enddate")); eventEntity.IsCanceled = reader.GetBoolean(reader.GetOrdinal("isCanceled")); try { DepartmentEntity departmentEntity = new DepartmentEntity(); departmentEntity.Id = reader.GetInt32(reader.GetOrdinal("department_id")); departmentEntity.Name = reader.GetString(reader.GetOrdinal("name")); eventEntity.DepartmentEntity = departmentEntity; RoomEntity re = new RoomEntity(); re.Id = reader.GetInt32(reader.GetOrdinal("room_id")); re.Identifier = reader.GetString(reader.GetOrdinal("identifier")); re.DepartmentEntity = departmentEntity; eventEntity.Rooms.Add(re); } catch (SqlNullValueException exc) { // We need to ignore it, because a given event may be created without rooms and department. } events.Add(eventEntity); } else { try { RoomEntity roomEntity = new RoomEntity(); roomEntity.Id = reader.GetInt32(reader.GetOrdinal("room_id")); roomEntity.Identifier = reader.GetString(reader.GetOrdinal("identifier")); eventEntity.Rooms.Add(roomEntity); } catch (SqlNullValueException exc) { // We need to ignore it, because a given event may be created without rooms and department. } } } } catch (SqlException exc) { } finally { reader.Close(); } eventsCount = events.Count; return(events); }