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