public static void AddEmployeeSchedule(int EmployeeId, int ScheduleId)
{
SQLiteConnection conn = SchedulerDBContext.GetConnection();
string insertStatement = "INSERT INTO EmployeeSchedule" +
"(EmployeeId, ScheduleId)" +
"VALUES (@EmployeeId, @ScheduleId)";
SQLiteCommand insertCommand = new SQLiteCommand(insertStatement, conn);
insertCommand.Parameters.AddWithValue("@EmployeeId", EmployeeId);
insertCommand.Parameters.AddWithValue("@ScheduleId", ScheduleId);
try
{
conn.Open();
insertCommand.ExecuteNonQuery();
}
catch (SQLiteException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
///
/// </summary>
/// <returns>A list of Tuple(EmployeeId, ScheduleId)</returns>
public static List <Tuple <int, int> > GetAllEmployeeSchedules()
{
List <Tuple <int, int> > tList = new List <Tuple <int, int> >();
SQLiteConnection conn = SchedulerDBContext.GetConnection();
string selectStatement = "SELECT * FROM EmployeeSchedule";
SQLiteCommand selectCommand = new SQLiteCommand(selectStatement, conn);
try
{
conn.Open();
SQLiteDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Tuple <int, int> t = new Tuple <int, int>(
Convert.ToInt32(reader["EmployeeId"].ToString()),
Convert.ToInt32(reader["ScheduleId"].ToString())
);
tList.Add(t);
}
return(tList);
}
catch (SQLiteException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static Schedule GetScheduleById(int id)
{
Schedule schdl = new Schedule();
SQLiteConnection conn = SchedulerDBContext.GetConnection();
string selectStatement = "SELECT ScheduleId, month, day, year, starttime, endtime FROM Schedule WHERE ScheduleId = @ScheduleId";
SQLiteCommand selectCommand = new SQLiteCommand(selectStatement, conn);
selectCommand.Parameters.AddWithValue("@ScheduleId", id);
try
{
conn.Open();
SQLiteDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
schdl.ScheduleId = Convert.ToInt32(reader["ScheduleId"].ToString());
schdl.Month = Convert.ToInt32(reader["month"].ToString());
schdl.Day = Convert.ToInt32(reader["day"].ToString());
schdl.Year = Convert.ToInt32(reader["year"].ToString());
schdl.StartTime = (reader["starttime"].ToString());
schdl.EndTime = (reader["endtime"].ToString());
}
}
catch (SQLiteException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(schdl);
}
public static int AddSchedule(Schedule schdl)
{
SQLiteConnection conn = SchedulerDBContext.GetConnection();
string insertStatement = "INSERT INTO Schedule" +
"(month, day, year, starttime, endtime)" +
"VALUES (@month, @day, @year, @starttime, @endtime)";
string lastInsert = "SELECT last_insert_rowid()";
SQLiteCommand lastInsertCommand = new SQLiteCommand(lastInsert, conn);
SQLiteCommand insertCommand = new SQLiteCommand(insertStatement, conn);
insertCommand.Parameters.AddWithValue("@month", schdl.Month);
insertCommand.Parameters.AddWithValue("@day", schdl.Day);
insertCommand.Parameters.AddWithValue("@year", schdl.Year);
insertCommand.Parameters.AddWithValue("@starttime", schdl.StartTime);
insertCommand.Parameters.AddWithValue("@endtime", schdl.EndTime);
try
{
conn.Open();
insertCommand.ExecuteNonQuery();
int id = Convert.ToInt32(lastInsertCommand.ExecuteScalar());
return(id);
}
catch (SQLiteException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}