C#结合MySQL的学生考勤系统如何校验扫码ID有效性并提示错误
提问内容
本人基于C#与MySQL开发学生考勤系统,搭配条码扫描设备采集学生身份ID。
- 学生基础信息存储在
std_info数据表中 - 考勤记录存储在
std_att数据表中
当前条码扫描、数据写入的基础流程可正常运行,条码扫描采集的内容为学生的NIBM ID编号。
待实现需求
当条码扫描出现偏差、或是人工录入了数据库中不存在的无效ID时,系统能够弹出明确的错误提示。
现有核心代码
1. 考勤数据写入逻辑
private void button3_Click(object sender, EventArgs e) { cmd = new MySqlCommand(); cmd.CommandText = "insert into std_att (nibm_id, nic, name, address, number, batch) SELECT * FROM `std_info` where nibm_id like '" + textBox1.Text + "%'"; if (textBox1.Text == "") { label10.Text = "*请扫描ID"; errorProvider1.Clear(); } else { con.Open(); cmd.Connection = con; cmd.ExecuteNonQuery(); con.Close(); MessageBox.Show("数据插入成功 ✔️"); string Query = "select * from std_att ;"; MySqlCommand MyCommand2 = new MySqlCommand(Query, con); MySqlDataAdapter MyAdapter = new MySqlDataAdapter(); MyAdapter.SelectCommand = MyCommand2; DataTable dTable = new DataTable(); MyAdapter.Fill(dTable); dataGridView1.DataSource = dTable; } }
2. 条码扫描模块逻辑
private void VideoCaptureDevice_NewFrame(object sender, AForge.Video.NewFrameEventArgs eventArgs) { Bitmap bitmap = (Bitmap)eventArgs.Frame.Clone(); BarcodeReader reader = new BarcodeReader(); var result = reader.Decode(bitmap); if (result != null) { textBox1.Invoke(new MethodInvoker(delegate () { cmd = new MySqlCommand(); cmd.CommandText = " SELECT * FROM `std_info` where nibm_id like '" + textBox1.Text + "%'"; cmd.CommandType = CommandType.Text; cmd.Connection = con; con.Open(); MySqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { textBox1.Text = result.ToString(); label3.Text = dr.GetValue(2).ToString(); label4.Text = dr.GetValue(1).ToString(); label8.Text = dr.GetValue(5).ToString(); } con.Close(); })); } pictureBox1.Image = bitmap; }
程序运行界面截图
解决方案
首先修正现有代码的逻辑缺陷,再增加无效ID校验逻辑:
核心调整点
- 去掉模糊查询
like 'xxx%',改为精确匹配=,避免误匹配多条学生记录 - 无论插入考勤还是扫描到条码,都先查询
std_info校验ID是否存在 - 统一用参数化查询替换字符串拼接,避免SQL注入风险
改造后的考勤数据写入逻辑
private void button3_Click(object sender, EventArgs e) { // 校验输入为空的情况 if (string.IsNullOrWhiteSpace(textBox1.Text)) { label10.Text = "*请扫描ID"; errorProvider1.SetError(textBox1, "ID不能为空"); return; } label10.Text = ""; errorProvider1.Clear(); try { con.Open(); // 第一步:校验ID是否存在 using (var checkCmd = new MySqlCommand("SELECT COUNT(1) FROM std_info WHERE nibm_id = @NibmId", con)) { checkCmd.Parameters.AddWithValue("@NibmId", textBox1.Text.Trim()); long count = (long)checkCmd.ExecuteScalar(); if (count == 0) { MessageBox.Show("无效ID:该NIBM ID未在系统学生信息中登记,请核对后重试", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); textBox1.Clear(); return; } } // 第二步:ID存在才执行考勤插入 using (var insertCmd = new MySqlCommand("insert into std_att (nibm_id, nic, name, address, number, batch) SELECT nibm_id, nic, name, address, number, batch FROM std_info WHERE nibm_id = @NibmId", con)) { insertCmd.Parameters.AddWithValue("@NibmId", textBox1.Text.Trim()); insertCmd.ExecuteNonQuery(); } MessageBox.Show("数据插入成功 ✔️"); // 刷新考勤列表 string Query = "select * from std_att"; MySqlCommand MyCommand2 = new MySqlCommand(Query, con); MySqlDataAdapter MyAdapter = new MySqlDataAdapter(); MyAdapter.SelectCommand = MyCommand2; DataTable dTable = new DataTable(); MyAdapter.Fill(dTable); dataGridView1.DataSource = dTable; } catch (Exception ex) { MessageBox.Show($"操作失败:{ex.Message}", "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { // 确保连接关闭,避免泄漏 if (con.State == ConnectionState.Open) con.Close(); } }
改造后的条码扫描模块逻辑
原有代码存在逻辑错误:扫描到条码后仍然用
textBox1的旧内容查询,必须先把扫描到的结果赋值给textBox1再查询
private void VideoCaptureDevice_NewFrame(object sender, AForge.Video.NewFrameEventArgs eventArgs) { Bitmap bitmap = (Bitmap)eventArgs.Frame.Clone(); BarcodeReader reader = new BarcodeReader(); var result = reader.Decode(bitmap); if (result != null) { string scannedId = result.ToString().Trim(); // 避免重复处理同一个条码 textBox1.Invoke(new MethodInvoker(delegate () { if (textBox1.Text == scannedId) return; textBox1.Text = scannedId; label3.Text = ""; label4.Text = ""; label8.Text = ""; label10.Text = ""; try { con.Open(); using (var cmd = new MySqlCommand("SELECT nic, name, batch FROM std_info WHERE nibm_id = @NibmId", con)) { cmd.Parameters.AddWithValue("@NibmId", scannedId); using (var dr = cmd.ExecuteReader()) { if (dr.Read()) { // 匹配到学生信息才显示 label4.Text = dr["nic"].ToString(); label3.Text = dr["name"].ToString(); label8.Text = dr["batch"].ToString(); } else { // 无效ID直接提示 MessageBox.Show($"扫描到的ID {scannedId} 无效:未找到对应学生信息", "扫描错误", MessageBoxButtons.OK, MessageBoxIcon.Error); textBox1.Clear(); } } } } catch (Exception ex) { MessageBox.Show($"扫描处理失败:{ex.Message}", "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { if (con.State == ConnectionState.Open) con.Close(); } })); } pictureBox1.Image = bitmap; }
额外优化建议
- 给数据库连接
con增加统一的异常处理和关闭逻辑,避免多次打开/关闭导致的连接泄漏 - 对条码扫描结果增加去重判断,避免同一个条码连续识别多次重复写入考勤
- 可根据业务需求增加“同一学生同一时段重复考勤”的校验逻辑
内容的提问来源于stack exchange,提问作者tuneKawaii UwU
相关产品推荐
相关产品推荐

