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