public static int GetKDHGCount() { string query = "select count(*) from TB_STAFF where st_company = 'KDHK'"; object result = DataServiceEducation.GetInstance().ExecuteScalar(query); return((int)result); }
private void btnSave_Click(object sender, EventArgs e) { string mgtno = txtRef.Text; string content = txtContent.Text; string contentjp = txtContentJp.Text; string ansA = txtAnsA.Text; string ansB = txtAnsB.Text; string ansC = txtAnsC.Text; string ansD = txtAnsD.Text; string ansJpA = txtAnsJpA.Text; string ansJpB = txtAnsJpB.Text; string ansJpC = txtAnsJpC.Text; string ansJpD = txtAnsJpD.Text; string correctA = ckbAnsA.Checked ? "True" : "False"; string correctB = ckbAnsB.Checked ? "True" : "False"; string correctC = ckbAnsC.Checked ? "True" : "False"; string correctD = ckbAnsD.Checked ? "True" : "False"; string qText = string.Format("update TB_QUESTION set q_mgtno = '{0}', q_content = N'{1}'" + ", q_contentjp = N'{2}' where q_id = '{3}'", mgtno, content, contentjp, _id); DataServiceEducation.GetInstance().ExecuteNonQuery(qText); UpdateAnsA(ansA, ansJpA, correctA, _id); UpdateAnsB(ansB, ansJpB, correctB, _id); UpdateAnsC(ansC, ansJpC, correctC, _id); UpdateAnsD(ansD, ansJpD, correctD, _id); MessageBox.Show("Record has been saved"); this.DialogResult = DialogResult.OK; }
public static int GetEducation() { string query = "select top 1 e_id from TB_EDUCATION where e_enabled = 'True' order by e_id desc"; object result = DataServiceEducation.GetInstance().ExecuteScalar(query); return((int)result); }
private void LoadData(string filter, int educationId) { DataTable table = new DataTable(); string[] headers = { "company", "staffid", "name", "select" }; foreach (string header in headers) { table.Columns.Add(header); } string query = filter == "Failed" ? string.Format("select st_company as company, st_staffid as staffid, st_name as name from TB_STAFF, TB_RECORD" + " where st_name = r_name and r_result = 'Failed' and r_locked = 'locked' and r_educationid = '{0}'", educationId) : string.Format("select st_company as company, st_staffid as staffid, st_name as name from TB_STAFF" + " where not exists (select * from TB_RECORD where st_name = r_name and r_educationid = '{0}')", educationId); using (IDataReader reader = DataServiceEducation.GetInstance().ExecuteReader(query)) { while (reader.Read()) { string company = reader.GetString(0); string staffId = reader.GetString(1); string name = reader.GetString(2); table.Rows.Add(new object[] { company, staffId, name, "False" }); } } dgvFailedList.DataSource = table; }
public static int GetKDHGRecordCount(int educationid) { string query = string.Format("select count(*) from TB_RECORD where r_company = 'kdhk' and r_educationid = '{0}'", educationid); object result = DataServiceEducation.GetInstance().ExecuteScalar(query); return((int)result); }
public static int GetEducationId(string title) { string query = string.Format("select e_id from TB_EDUCATION where e_title = N'{0}'", title); object result = DataServiceEducation.GetInstance().ExecuteScalar(query); return((int)result); }
private void tsbtnUpload_Click(object sender, EventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { DataTable tmptable = new DataTable(); tmptable = ImportExcel2007.TranslateToTable(ofd.FileName); foreach (DataRow row in tmptable.Rows) { string staffid = row.ItemArray[0].ToString(); staffid = staffid.ToLower(); string name = staffid.StartsWith("hk") ? AdUtil.getUsernameByUserId(staffid, "kmhk.local") : AdUtil.getUsernameByUserId(staffid, "kmas.local"); string company = staffid.StartsWith("hk") ? "KDTHK" : staffid.StartsWith("as") ? "KDAS" : "KDHK"; string query = string.Format("if exists (select * from TB_STAFF where st_staffid = '{0}')" + " update TB_STAFF set st_name = N'{1}', st_company = '{2}' where st_staffid = '{0}' else" + " insert into TB_STAFF (st_staffid, st_name, st_company) values ('{0}', N'{1}', '{2}')", staffid, name, company); DataServiceEducation.GetInstance().ExecuteNonQuery(query); } } MessageBox.Show("Record has been saved."); }
private void LoadData() { dgvQuestion.Rows.Clear(); DataTable table = new DataTable(); string query = "select e_id, e_title, e_from, e_to, e_enabled, e_notice from TB_EDUCATION"; SqlDataAdapter sda = new SqlDataAdapter(query, DataServiceEducation.GetInstance().Connection); sda.Fill(table); foreach (DataRow row in table.Rows) { string id = row.ItemArray[0].ToString(); string title = row.ItemArray[1].ToString(); string from = row.ItemArray[2].ToString(); string to = row.ItemArray[3].ToString(); string enabled = row.ItemArray[4].ToString(); string notice = row.ItemArray[5].ToString(); Image img = enabled == "True" ? Properties.Resources.tick_icon : Properties.Resources.cross_icon; string kdthk = EducationUtil.GetKDTHKRecordCount(Convert.ToInt32(id)) + " / " + EducationUtil.GetKDTHKCount(); string kdas = EducationUtil.GetKDASRecordCount(Convert.ToInt32(id)) + " / " + EducationUtil.GetKDASCount(); string kdhg = EducationUtil.GetKDHGRecordCount(Convert.ToInt32(id)) + " / " + EducationUtil.GetKDHGCount(); dgvQuestion.Rows.Add(id, title, from, to, enabled, img, notice, kdthk, kdas, kdhg); } }
private void UpdateAnsD(string ansD, string ansjpD, string correct, string id) { string query = string.Format("update TB_ANSWER set a_answer = N'{0}', a_answerjp = N'{1}'" + ", a_correct = '{2}' where a_questionid = '{3}' and a_symbol = 'D.'", ansD, ansjpD, correct, id); DataServiceEducation.GetInstance().ExecuteNonQuery(query); }
private void LoadData(string company) { DataTable table = new DataTable(); string query = string.Format("select st_staffid as staffid, st_name as name, st_company as company from TB_STAFF where st_company like '%{0}%'", company); SqlDataAdapter sda = new SqlDataAdapter(query, DataServiceEducation.GetInstance().Connection); sda.Fill(table); dgvStaff.DataSource = table; }
public static bool IsUserExists(string staffid) { string query = string.Format("select count(*) from TB_STAFF where st_staffid = '{0}'", staffid); object result = DataServiceEducation.GetInstance().ExecuteScalar(query); if (result is DBNull || (int)result == 0) { return(false); } return(true); }
public static bool IsEducationOpened() { string query = "select count(*) from TB_EDUCATION where e_enabled = 'True'"; object result = DataServiceEducation.GetInstance().ExecuteScalar(query); if (result is DBNull || (int)result == 0) { return(false); } return(true); }
public static bool IsTitleExist(string title) { string query = string.Format("select count(*) from TB_EDUCATION where e_title = N'{0}'", title); object result = DataServiceEducation.GetInstance().ExecuteScalar(query); if (result is DBNull || (int)result == 0) { return(false); } return(true); }
private void LoadData() { string query = "select q_id as id, q_content as content, q_contentjp as contentjp" + ", q_mgtno as mgtno from TB_QUESTION order by q_mgtno"; DataTable table = new DataTable(); SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(query, DataServiceEducation.GetInstance().Connection); sda.Fill(table); dgvQuestion.DataSource = table; }
private void SearchData(string type) { DataTable table = new DataTable(); string query = string.Format("select q_id as id, q_content as content, t_type as type" + " from TB_QUESTION, TB_MASTER_TYPE where q_type = t_tag and q_type like '%{0}%'", type); SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(query, DataServiceEducation.GetInstance().Connection); sda.Fill(table); dgvQuestion.DataSource = table; }
private void enableToolStripMenuItem_Click(object sender, EventArgs e) { int id = Convert.ToInt32(dgvQuestion.SelectedRows[0].Cells[0].Value); string query = "update TB_EDUCATION set e_enabled = 'False'"; DataServiceEducation.GetInstance().ExecuteNonQuery(query); string text = string.Format("update TB_EDUCATION set e_enabled = 'True' where e_id = '{0}'", id); DataServiceEducation.GetInstance().ExecuteNonQuery(text); this.LoadData(); }
private void tsbtnDelete_Click(object sender, EventArgs e) { switch (MessageBox.Show("Delete user " + dgvStaff.SelectedRows[0].Cells[0].Value.ToString() + " ? All related record will be deleted.", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question)) { case DialogResult.Yes: string query = string.Format("delete from TB_STAFF where st_staffid = '{0}'", dgvStaff.SelectedRows[0].Cells[0].Value.ToString()); DataServiceEducation.GetInstance().ExecuteNonQuery(query); MessageBox.Show("Record has been saved"); this.LoadData(""); break; case DialogResult.No: break; } }
private void LoadData(string source) { DataTable table = new DataTable(); string[] headers = { "education", "company", "name", "score", "id" }; foreach (string header in headers) { table.Columns.Add(header); } string query = string.Format("select e_title, st_company, st_name, r_score, st_id" + " from TB_EDUCATION, TB_RECORD, TB_STAFF where e_id = r_educationid and r_name = st_name and (e_title like '%{0}%' or st_company like '%{0}%')", source); using (IDataReader reader = DataServiceEducation.GetInstance().ExecuteReader(query)) { while (reader.Read()) { string title = reader.GetString(0); string company = reader.GetString(1); string name = reader.GetString(2); string score = reader.GetString(3); int id = reader.GetInt32(4); table.Rows.Add(new object[] { title, company, name, score, id }); } } string text = string.Format("select e_title, st_company, st_name, st_id from TB_EDUCATION, TB_STAFF, TB_RECORD" + " where (e_title like '%{0}%' or st_company like '%{0}%') and r_educationid = e_id and not exists" + " (select * from TB_RECORD where r_name = st_name and (e_title like '%{0}%' or st_company like '%{0}%'))", source); using (IDataReader reader = DataServiceEducation.GetInstance().ExecuteReader(text)) { while (reader.Read()) { string title = reader.GetString(0); string company = reader.GetString(1); string name = reader.GetString(2); int id = reader.GetInt32(3); table.Rows.Add(new object[] { title, company, name, "Waiting", id }); } } DataTable tb = DeleteDuplicateFromDataTable(table, "id"); dgvRecord.DataSource = tb; }
private void LoadAnswer(string id) { string query = string.Format("select a_symbol, a_answer, a_answerjp, a_correct from TB_ANSWER where a_questionid = '{0}'", id); using (IDataReader reader = DataServiceEducation.GetInstance().ExecuteReader(query)) { while (reader.Read()) { string symbol = reader.GetString(0); string answer = reader.GetString(1); string answerjp = reader.GetString(2); string correct = reader.GetString(3); if (symbol == "A.") { txtAnsA.Text = answer; txtAnsJpA.Text = answerjp; ckbAnsA.Checked = correct == "True" ? true : false; ckbAnsJpA.Checked = correct == "True" ? true : false; } else if (symbol == "B.") { txtAnsB.Text = answer; txtAnsJpB.Text = answerjp; ckbAnsB.Checked = correct == "True" ? true : false; ckbAnsJpB.Checked = correct == "True" ? true : false; } else if (symbol == "C.") { txtAnsC.Text = answer; txtAnsJpC.Text = answerjp; ckbAnsC.Checked = correct == "True" ? true : false; ckbAnsJpC.Checked = correct == "True" ? true : false; } else { txtAnsD.Text = answer; txtAnsJpD.Text = answerjp; ckbAnsD.Checked = correct == "True" ? true : false; ckbAnsJpD.Checked = correct == "True" ? true : false; } } } }
public static List <int> GetAllTypeId() { List <int> list = new List <int>(); string query = "select t_id from TB_MASTER_TYPE"; using (IDataReader reader = DataServiceEducation.GetInstance().ExecuteReader(query)) { while (reader.Read()) { int id = reader.GetInt32(0); list.Add(id); } } return(list); }
private void LoadQuestion(string id) { string query = string.Format("select q_mgtno, q_content, q_contentjp from TB_QUESTION where q_id = '{0}'", id); using (IDataReader reader = DataServiceEducation.GetInstance().ExecuteReader(query)) { while (reader.Read()) { string mgtno = reader.GetString(0); string content = reader.GetString(1); string contentjp = reader.GetString(2); txtRef.Text = mgtno; txtContent.Text = content; txtContentJp.Text = contentjp; } } }
private void btnSave_Click(object sender, EventArgs e) { if (EducationUtil.IsUserExists(txtStaffId.Text)) { MessageBox.Show("User already exists."); return; } string staffId = txtStaffId.Text; string userName = lblUsername.Text; string company = lblCompany.Text; string query = string.Format("insert into TB_STAFF (st_staffid, st_name, st_company)" + " values ('{0}', N'{1}', '{2}')", staffId, userName, company); DataServiceEducation.GetInstance().ExecuteNonQuery(query); MessageBox.Show("Record has been saved."); this.DialogResult = DialogResult.OK; }