1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.ComponentModel;4 usingSystem.Data;5 usingSystem.Drawing;6 usingSystem.Linq;7 usingSystem.Text;8 usingSystem.Threading.Tasks;9 usingSystem.Windows.Forms;10
11 namespacesql_homework_end12 {13 public partial classmain : Form14 {15 publicmain()16 {17 InitializeComponent();18
19 }20 //绑定并显示相关信息
21 DataSet ds = newDataSet();22 DataTable dt = newDataTable();23 private void 学生信息ToolStripMenuItem_Click(objectsender, EventArgs e)24 {25 ds = sqlhelper.ExecuteDataSet("select * from tb_student");26 dt = ds.Tables[0];27 dataGridView1.DataSource =dt;28 }29
30 private void 课程信息ToolStripMenuItem_Click(objectsender, EventArgs e)31 {32 ds = sqlhelper.ExecuteDataSet("select * from tb_course");33 dt = ds.Tables[0];34 dataGridView1.DataSource =dt;35 }36
37 private void 选课信息ToolStripMenuItem_Click(objectsender, EventArgs e)38 {39 ds = sqlhelper.ExecuteDataSet("select * from tb_student_course");40 dt = ds.Tables[0];41 dataGridView1.DataSource =dt;42 dataGridView1.Columns["sc_id"].DisplayIndex = 0;43 }44 private void 管理员信息ToolStripMenuItem_Click(objectsender, EventArgs e)45 {46 ds = sqlhelper.ExecuteDataSet("select * from tb_admin");47 dt = ds.Tables[0];48 dataGridView1.DataSource =dt;49 }50 //添加按钮事件
51 private void btn_insert_Click(objectsender, EventArgs e)52 {53 if (dataGridView1.Columns[0].HeaderText=="student_num")54 {55 string sql = @"insert tb_student (student_num,student_name,student_password,student_sex56 ,student_age,student_dept)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" +
57 dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value58 + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[4].Value59 + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[5].Value + "')";60 sqlhelper.ExecuteNoQuery(sql);61
62 }63 else if (dataGridView1.Columns[0].HeaderText=="course_num")64 {65 string sql = @"insert tb_course (course_num,course_name,course_credit,course_semester)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "')";66 sqlhelper.ExecuteNoQuery(sql);67 }68 else if (dataGridView1.Columns[0].HeaderText=="sc_id")69 {70 try
71 {72 string sql = @"insert tb_student_course (sc_id,student_num,course_num,grade)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "')";73 sqlhelper.ExecuteNoQuery(sql);74 }75 catch(Exception)76 {77 MessageBox.Show("学号或姓名不存在,请重新添加。");78 }79
80 }81 else if (dataGridView1.Columns[0].HeaderText == "admin_id")82 {83 string sql = @"insert tb_admin (admin_id,admin_name,admin_password,remark)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "')";84 sqlhelper.ExecuteNoQuery(sql);85 }86 MessageBox.Show("添加成功");87 }88 //更新按钮事件
89 private void btn_update_Click(objectsender, EventArgs e)90 {91 if (dataGridView1.Columns[0].HeaderText == "student_num")92 {93 update("tb_student","student_num");94 }95 else if (dataGridView1.Columns[0].HeaderText == "course_num")96 {97 update("tb_course","course_num");98 }99 else if (dataGridView1.Columns[0].HeaderText == "admin_id")100 {101 update("tb_admin","admin_id");102 }103 else if (dataGridView1.Columns[0].HeaderText == "sc_id")104 {105 try
106 {107 update("tb_studnet_course","sc_id");108 }109 catch(Exception)110 {111 MessageBox.Show("学号或课程号不存在,请重新输入");112 throw;113 }114 }115 }116 //更新方法
117 private void update(string table,stringhead_id)118 {119 for (int i = 0; i < dataGridView1.RowCount; i++)120 {121 int id = Convert.ToInt32(dataGridView1.Rows[i].Cells[0].Value);122 for (int j = 1; j < dataGridView1.ColumnCount; j++)123 {124 if (dataGridView1.Columns[j].Visible == true)125 {126 string columnName =dataGridView1.Columns[j].Name.ToString();127 string sql = "update" + table + "set" + columnName + "='" + dataGridView1.Rows[i].Cells[j].Value + "' where" + head_id + "='" + id + "'";128 sqlhelper.ExecuteNoQuery(sql);129 }130 }131 }132 MessageBox.Show("更新成功");133 }134
135 //删除事件
136 private void btn_delete_Click(objectsender, EventArgs e)137 {138 if (dataGridView1.Columns[0].HeaderText == "student_num")139 {140 string sql = "delete from tb_student where student_num='" + dataGridView1.SelectedCells[0].Value + "'";141 sqlhelper.ExecuteNoQuery(sql);142 }143 else if (dataGridView1.Columns[0].HeaderText == "course_num")144 {145 string sql = "delete from tb_course where course_num='" + dataGridView1.SelectedCells[0].Value + "'";146 sqlhelper.ExecuteNoQuery(sql);147 }148 else if (dataGridView1.Columns[0].HeaderText == "sc_id")149 {150 string sql = "delete from tb_student_course where sc_id='" + dataGridView1.SelectedCells[0].Value + "'";151 sqlhelper.ExecuteNoQuery(sql);152 }153 else if (dataGridView1.Columns[0].HeaderText == "admin_id")154 {155 string sql = "delete from tb_admin where admin_id='" + dataGridView1.SelectedCells[0].Value + "'";156 sqlhelper.ExecuteNoQuery(sql);157 }158 MessageBox.Show("删除成功");159 }160
161 //界面载入显示身份和登录时间
162 private void main_Load(objectsender, EventArgs e)163 {164 lbl_username.Text = "Welcome," + login.GlobelValue + "";165 lbl_logintime.Text ="登录时间:" +DateTime.Now.ToString()+"";166 }167 }168 }