public DataTable SelectSessions(SessionType session)
{
SelectedSessionType = session;
//DB connection
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
DataTable dt = new DataTable();
try
{
//sql query
string sql = "Select * from sessionCat where Type = @session";
//creating cmd using sql and conn
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
cmd.Parameters.AddWithValue("@session", session.ToString());
//creating sql data adapted using cmd
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return(dt);
}
private void loadData()
{
try
{
SQLiteConnection con = new Classes.SqliteHelper().GetSQLiteConnection();
//SqlConnection con = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = C: \Users\User\Desktop\Time Table MS\Time Table MS\Database1.mdf; Integrated Security = True");
con.Open();
String cmd = "SELECT * FROM WorkingDaysHours";
SQLiteCommand command = new SQLiteCommand(cmd, con);
dataGridView1.DataSource = null;
dataGridView1.Rows.Clear();
SQLiteDataAdapter dAdapter = new SQLiteDataAdapter(command);
DataSet ds = new DataSet();
dAdapter.Fill(ds);
dataGridView1.ReadOnly = true;
dataGridView1.DataSource = ds.Tables[0];
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
//get tha value from textBox
string keyword = txtSearch.Text;
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
SQLiteDataAdapter sda = new SQLiteDataAdapter("Select * from Session where Lecture1 LIKE '%" + keyword + "%' OR SubjectCode LIKE '%" + keyword + "%' OR SubjectName LIKE '%" + keyword + "%'", conn);
DataTable dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
if (txtBuildingName.Text == "" || txtRoomName.Text == "" || txtCapacity.Text == "")
{
MessageBox.Show("Please insert all fields!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
string roomType;
if (rbtLectureHall.Checked)
{
roomType = "Lecture Hall";
}
else
{
roomType = "Laboratory";
}
SQLiteConnection connection = new Classes.SqliteHelper().GetSQLiteConnection();
connection.Open();
SQLiteCommand command =
new SQLiteCommand("INSERT INTO [Location](Building, Room, Room_Type, Capacity) " + " VALUES ('" + txtBuildingName.Text + "','" + txtRoomName.Text + "','" + roomType + "','" + txtCapacity.Text + "')", connection);
int i = command.ExecuteNonQuery();
if (i != 0)
{
clear();
connection.Close();
MessageBox.Show("Data Saved");
ManageLocations frm2 = new ManageLocations();
frm2.Show();
this.Hide();
}
else
{
connection.Close();
MessageBox.Show("Data not Saved", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private DataTable GetSessionListData()
{
DataTable dt = new DataTable();
string connString = Classes.ConnectionStrings.TimeTableSystem;
using (SQLiteConnection con = new Classes.SqliteHelper().GetSQLiteConnection())
{
using (SQLiteCommand cmd = new SQLiteCommand("Select * from Session", con))
{
con.Open();
SQLiteDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
}
}
return(dt);
}
private void btn_Delete_Click(object sender, EventArgs e)
{
var confirmResult = MessageBox.Show("Are you sure to delete this student Group ??", "Confirm Delete!!", MessageBoxButtons.YesNo);
if (confirmResult == DialogResult.Yes)
{
SQLiteConnection con = new Classes.SqliteHelper().GetSQLiteConnection();
//SqlConnection con = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = C: \Users\User\Desktop\Time Table MS\Time Table MS\Database1.mdf; Integrated Security = True");
con.Open();
String cmd = "DELETE FROM WorkingDaysHours WHERE Id='" + ID + "';";
SQLiteCommand command = new SQLiteCommand(cmd, con);
int i = command.ExecuteNonQuery();
if (i != 0)
{
loadData();
numericUpDown1.ResetText();
mon_check.Checked = false;
tue_check.Checked = false;
wed_check.Checked = false;
thu_check.Checked = false;
fri_check.Checked = false;
sun_check.Checked = false;
sat_check.Checked = false;
comboBox1.ResetText();
comboBox2.ResetText();
con.Close();
MessageBox.Show("Data Deleted");
}
else
{
MessageBox.Show("Data not Deleted");
con.Close();
}
}
}
private void btnRemove_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count > 0)
{
DataGridViewRow selected = dataGridView1.SelectedRows[0];
if (selected != null)
{
//delete
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
DataTable dt = new DataTable();
try
{
String sessionID = selected.Cells[11].Value.ToString();
//sql query
string sql = "Delete from sessionCat where sessionID = @sessionID";
//creating cmd using sql and conn
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
cmd.Parameters.AddWithValue("@sessionID", sessionID);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
dataGridView1.DataSource = SelectSessions(SelectedSessionType);
MessageBox.Show("Deleted From Database !");
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
}
}
}
void add(string typename)
{
List <String> ids = new List <string>();
foreach (DataGridViewRow dr in DGVSession.Rows)
{
bool chkboxselected = Convert.ToBoolean(dr.Cells["checkBoxColumn"].Value);
if (chkboxselected)
{
ids.Add(dr.Cells[1].Value.ToString());
}
}
//if need items limitation
if (ids.Count < 2)
{
statusLable.Text = "Select More than 1 Session !";
return;
}
string connString = Classes.ConnectionStrings.TimeTableSystem;
// string connString = ConfigurationManager.ConnectionStrings["connstrng"].ConnectionString;
SQLiteConnection sqlconn = new Classes.SqliteHelper().GetSQLiteConnection();
// SQLiteConnection sqlconn2 = new Classes.SqliteHelper().GetSQLiteConnection();
try
{
sqlconn.Open();
String q1 = $"select * from sessionCat where LecID in ({String.Join(",", ids)})";
using (SQLiteCommand sqlcomm1 = new SQLiteCommand(q1, sqlconn))
{
using (SQLiteDataReader rdr = sqlcomm1.ExecuteReader())
if (rdr.Read())
{
statusLable.Text = "Duplicate data";
return;
}
}
String newid = "";
String q2 = $@"
select idChar, CAST(incremnt as varchar) [newid] from idNumbers where idChar = 'S';
update idNumbers set incremnt = incremnt + 1 where idChar = 'S';";
using (SQLiteCommand sqlcomm2 = new SQLiteCommand(q2, sqlconn))
{
using (SQLiteDataReader rdr2 = sqlcomm2.ExecuteReader())
if (rdr2.Read())
{
newid = rdr2["idChar"].ToString() + rdr2["newid"].ToString();
}
}
if (newid == "")
{
statusLable.Text = "Id genarate fail!";
return;
}
//sqlconn.Close();
string selectq = $@"
select id,[Lecture1],[Lecture2],[SubjectCode],[SubjectName]
,[GroupID],[Tag],[NoOfStudent],[Duration],'{typename}' [Type],'{newid}' [newid] from Session where id in ({ String.Join(",", ids)}); ";
SQLiteCommand sqlcomm4 = new SQLiteCommand(selectq, sqlconn);
//sqlconn.Open();
List <SessonData> ins_ids = new List <SessonData>();
using (SQLiteDataReader reader = sqlcomm4.ExecuteReader())
{
ins_ids = new List <SessonData>();
while (reader.Read())
{
ins_ids.Add(new SessonData()
{
id = reader["id"].ToString(),
lecture1 = reader["Lecture1"].ToString(),
lecture2 = reader["Lecture2"].ToString(),
SubjectCode = reader["SubjectCode"].ToString(),
SubjectName = reader["SubjectName"].ToString(),
GroupID = reader["GroupID"].ToString(),
Tag = reader["Tag"].ToString(),
NoOfStudents = reader["NoOfStudent"].ToString(),
Duration = reader["Duration"].ToString(),
Typename = typename,
NewId = newid
});
}
}
foreach (SessonData ssdata in ins_ids)
{
String sqlquery = $@"
INSERT INTO sessionCat
(LecID, Lecture1, Lecture2, SubjectCode, SubjectName
, GroupID, Tag, NoOfStudent, Duration, Type, SessionID)
values(@ID,@Lecture1,@Lecture2,@SubjectCode,@SubjectName,@GroupID,@Tag,@NoOfStudent,@Duration,@Type,@SessionID);
";
SQLiteCommand sqlcomm = new SQLiteCommand(sqlquery, sqlconn);
sqlcomm.Parameters.AddWithValue("@ID", ssdata.id);
sqlcomm.Parameters.AddWithValue("@Lecture1", ssdata.lecture1);
sqlcomm.Parameters.AddWithValue("@Lecture2", ssdata.lecture2);
sqlcomm.Parameters.AddWithValue("@SubjectCode", ssdata.SubjectCode);
sqlcomm.Parameters.AddWithValue("@SubjectName", ssdata.SubjectName);
sqlcomm.Parameters.AddWithValue("@GroupID", ssdata.GroupID);
sqlcomm.Parameters.AddWithValue("@Tag", ssdata.Tag);
sqlcomm.Parameters.AddWithValue("@NoOfStudent", ssdata.NoOfStudents);
sqlcomm.Parameters.AddWithValue("@Duration", ssdata.Duration);
sqlcomm.Parameters.AddWithValue("@Type", typename);
sqlcomm.Parameters.AddWithValue("@SessionID", newid);
sqlcomm.ExecuteNonQuery();
//sqlconn2.Open();
/* SQLiteTransaction tran = sqlconn2.BeginTransaction();
* sqlcomm.Transaction = tran;
* try
* {
* statusLable.Text = "Insert success";
* tran.Commit();
* }
* catch (Exception ex1)
* {
* if (tran != null)
* tran.Rollback();
* }
* finally
* {
* sqlconn2.Close();
* } */
//}
statusLable.Text = "Insert success";
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btn_Update_Click(object sender, EventArgs e)
{
int monday, tuesday, wednessday, thursday, friday, saturday, sunday;
monday = tuesday = wednessday = thursday = friday = saturday = sunday = 0;
if (mon_check.Checked)
{
monday = 1;
}
if (tue_check.Checked)
{
tuesday = 1;
}
if (wed_check.Checked)
{
wednessday = 1;
}
if (thu_check.Checked)
{
thursday = 1;
}
if (fri_check.Checked)
{
friday = 1;
}
if (sat_check.Checked)
{
saturday = 1;
}
if (sun_check.Checked)
{
sunday = 1;
}
SQLiteConnection con = new Classes.SqliteHelper().GetSQLiteConnection();
// SqlConnection con = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = C: \Users\User\Desktop\Time Table MS\Time Table MS\Database1.mdf; Integrated Security = True");
con.Open();
String cmd = "UPDATE WorkingDaysHours SET NoOfWorkingDays='" + numericUpDown1.Text + "', Monday='" + monday + "', Tuesday='" + tuesday + "', Wednesday='" + wednessday + "',Thursday='" + thursday + "',Friday='" + friday + "' ,Saturday='" + saturday + "',Sunday='" + sunday + "' ,StartTime='" + comboBox1.Text + "' ,StopTime='" + comboBox2.Text + "' WHERE Id='" + ID + "';";
SQLiteCommand command = new SQLiteCommand(cmd, con);
int i = command.ExecuteNonQuery();
if (i != 0)
{
loadData();
numericUpDown1.ResetText();
mon_check.Checked = false;
tue_check.Checked = false;
wed_check.Checked = false;
thu_check.Checked = false;
fri_check.Checked = false;
sun_check.Checked = false;
sat_check.Checked = false;
comboBox1.ResetText();
comboBox2.ResetText();
con.Close();
MessageBox.Show("Data Updated");
}
else
{
MessageBox.Show("Data not Updated");
con.Close();
}
}