private void resetToolStripMenuItem_Click(object sender, EventArgs e) { int count = 0; foreach (DataGridViewRow row in dgvScore.SelectedRows) { string name = row.Cells[0].Value.ToString().Trim(); string query = string.Format("insert into TB_IT_EDU_REDO (r_user, r_datetime) values (N'{0}', '{1}')", name, DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")); DataServiceEdu.GetInstance().ExecuteNonQuery(query); string delText = string.Format("delete from TB_IT_EDU_HISTORY where h_user = N'{0}'", name); string delText1 = string.Format("delete from TB_IT_EDU_RECORD where r_user = N'{0}'", name); string delText2 = string.Format("delete from TB_IT_EDU_REPORT where r_user = N'{0}'", name); DataServiceEdu.GetInstance().ExecuteNonQuery(delText); DataServiceEdu.GetInstance().ExecuteNonQuery(delText1); DataServiceEdu.GetInstance().ExecuteNonQuery(delText2); count += 1; } MessageBox.Show(count + " records have been reset"); LoadData(); }
private void button1_Click(object sender, EventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName); foreach (DataRow row in table.Rows) { string question = row.ItemArray[0].ToString().Trim(); string category = row.ItemArray[1].ToString().Trim(); string questionjp = row.ItemArray[2].ToString().Trim(); string categoryjp = row.ItemArray[3].ToString().Trim(); string answer = row.ItemArray[4].ToString().Trim(); string answerjp = row.ItemArray[5].ToString().Trim(); string correct = row.ItemArray[6].ToString().Trim(); string id = row.ItemArray[7].ToString().Trim(); string query = string.Format("if not exists (select * from TB_IT_EDU_QUESTION where q_question = N'{0}') insert into TB_IT_EDU_QUESTION (q_question, q_questionjp, q_type, q_typejp)" + " values (N'{0}', N'{1}', N'{2}', N'{3}')", question, category, questionjp, categoryjp); DataServiceEdu.GetInstance().ExecuteNonQuery(query); string text = string.Format("insert into TB_IT_EDU_ANSWER (a_answer, a_answerjp, a_correct, a_questionid) values (N'{0}', N'{1}', '{2}', '{3}')", answer, answerjp, correct, id); DataServiceEdu.GetInstance().ExecuteNonQuery(text); } } }
private void LoadNonSubmittedData() { DataTable table = new DataTable(); string[] headers = { "staff", "dt", "score", "company" }; foreach (string header in headers) { table.Columns.Add(header); } string query = "select u_staff, u_company from TB_USER where not exists (select * from TB_IT_EDU_RECORD where r_user = u_staff)"; using (IDataReader reader = DataServiceEdu.GetInstance().ExecuteReader(query)) { while (reader.Read()) { string user = reader.GetString(0).Trim(); string company = reader.GetString(1).Trim(); table.Rows.Add(user, "-", "-", company); } } dgvScore.DataSource = table; }
private void btnUpdate_Click(object sender, EventArgs e) { List <UserData> list = new List <UserData>(); string query = "select u_staffid, u_staff, u_company from TB_USER"; using (IDataReader reader = DataServiceEdu.GetInstance().ExecuteReader(query)) { while (reader.Read()) { string staffid = reader.GetString(0).Trim(); string name = reader.GetString(1).Trim(); string company = reader.GetString(2).Trim(); list.Add(new UserData { id = staffid, name = name, company = company }); } } foreach (UserData data in list) { string q1 = string.Format("update TB_IT_EDU_RECORD set r_company = '{0}' where r_user = N'{1}'", data.company, data.name); DataServiceEdu.GetInstance().ExecuteNonQuery(q1); string q2 = string.Format("update TB_IT_EDU_REPORT set r_company = '{0}' where r_user = N'{1}'", data.company, data.name); DataServiceEdu.GetInstance().ExecuteNonQuery(q2); } }
public static Int32 GetQuestionId(string content) { string query = string.Format("select q_id from TB_IT_EDU_QUESTION where q_question = N'{0}'", content); //string query = "select top 1 q_id from TB_QUESTION order by q_id desc"; object result = DataServiceEdu.GetInstance().ExecuteScalar(query); return((int)result); }
private void LoadData() { DataTable table = new DataTable(); string query = "select r_user as staff, r_datetime as dt, r_score as score, r_company as company from TB_IT_EDU_RECORD, TB_USER where r_user = u_staff"; SqlDataAdapter sda = new SqlDataAdapter(query, DataServiceEdu.GetInstance().Connection); sda.Fill(table); dgvScore.DataSource = table; }
private void LoadData(string company) { string query = string.Format("select u_staffid as id, u_staff as name, u_company as company from TB_USER where u_company like '%{0}%'", company); DataTable table = new DataTable(); SqlDataAdapter sda = new SqlDataAdapter(query, DataServiceEdu.GetInstance().Connection); sda.Fill(table); dgvUser.DataSource = table; }
private void GetKdhkSubmitted() { string query = "select count(*), sum(cast(r_score as int)) from TB_IT_EDU_RECORD where r_company = 'KDHK' group by r_company"; using (IDataReader reader = DataServiceEdu.GetInstance().ExecuteReader(query)) { while (reader.Read()) { _kdhkS = reader.GetInt32(0); _kdhkSum = reader.GetInt32(1); } } }
private List <string> QuestionList() { List <string> list = new List <string>(); string query = "select q_question from TB_IT_EDU_QUESTION where q_id != 7"; using (IDataReader reader = DataServiceEdu.GetInstance().ExecuteReader(query)) { while (reader.Read()) { list.Add(reader.GetString(0).Trim()); } } return(list); }
private void btnUpload_Click(object sender, EventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { DataTable tb1 = ImportExcel2007.TranslateToTable(ofd.FileName, "KDTHK"); foreach (DataRow row in tb1.Rows) { string id = row.ItemArray[0].ToString().Trim(); string name = row.ItemArray[1].ToString().Trim(); string query = string.Format("insert into TB_USER (u_staffid, u_staff, u_company) values ('{0}', N'{1}', '{2}')", id, name, "KDTHK"); DataServiceEdu.GetInstance().ExecuteNonQuery(query); } DataTable tb2 = ImportExcel2007.TranslateToTable(ofd.FileName, "KDHK"); foreach (DataRow row in tb2.Rows) { string id = row.ItemArray[0].ToString().Trim(); string name = row.ItemArray[1].ToString().Trim(); string query = string.Format("insert into TB_USER (u_staffid, u_staff, u_company) values ('{0}', N'{1}', '{2}')", id, name, "KDHK"); DataServiceEdu.GetInstance().ExecuteNonQuery(query); } DataTable tb3 = ImportExcel2007.TranslateToTable(ofd.FileName, "KDAS"); foreach (DataRow row in tb3.Rows) { string id = row.ItemArray[0].ToString().Trim(); string name = row.ItemArray[1].ToString().Trim(); string query = string.Format("insert into TB_USER (u_staffid, u_staff, u_company) values ('{0}', N'{1}', '{2}')", id, name, "KDAS"); DataServiceEdu.GetInstance().ExecuteNonQuery(query); } } }
private void LoadData(List <string> list) { DataTable table = new DataTable(); string[] headers = { "question", "kdthk", "kdthkno", "kdhk", "kdhkno", "kdas", "kdasno", "total" }; foreach (string header in headers) { table.Columns.Add(header); } List <ResultList> resultList = new List <ResultList>(); foreach (string question in list) { table.Rows.Add(question, "0", "0", "0", "0", "0", "0", "0"); } foreach (DataRow row in table.Rows) { int totalCount = 0; int correctCount = 0; int questionId = EducationUtil.GetQuestionId(row.ItemArray[0].ToString().Trim()); string query = string.Format("select count(*), sum(case when r_correct = 'Yes' then 1 else 0 end), r_company from TB_IT_EDU_REPORT where r_questionid = '{0}' group by r_company", questionId); using (IDataReader reader = DataServiceEdu.GetInstance().ExecuteReader(query)) { while (reader.Read()) { int total = reader.GetInt32(0); int correct = reader.GetInt32(1); double rate = (double)correct / (double)total; rate = Math.Round(rate, 2) * 100; string company = reader.GetString(2).Trim(); totalCount = totalCount + total; correctCount = correctCount + correct; if (company == "KDTHK") { row["kdthk"] = correct + "/" + total + " (" + rate + "%)"; row["kdthkno"] = total; } if (company == "KDHK") { row["kdhk"] = correct + "/" + total + " (" + rate + "%)"; row["kdhkno"] = total; } if (company == "KDAS") { row["kdas"] = correct + "/" + total + " (" + rate + "%)"; row["kdasno"] = total; } } } //Debug.WriteLine("Correct Count: " + correctCount); int kdthk = Convert.ToInt32(row["kdthkno"]); int kdhk = Convert.ToInt32(row["kdhkno"]); int kdas = Convert.ToInt32(row["kdasno"]); int sum = kdthk + kdhk + kdas; double totalRate = (double)correctCount / (double)sum; totalRate = Math.Round(totalRate, 2) * 100; row["total"] = correctCount + "/" + sum + " (" + totalRate + "%)"; } dgvResult.DataSource = table; }
private int kdhkTotal() { string query = "select count(*) from TB_USER where u_company = 'KDHK'"; return((int)DataServiceEdu.GetInstance().ExecuteScalar(query)); }