当前位置： 首页 > news >正文

Visual Studio 2022连接mysql数据库2，实现一个表的CRUD

news 2025/10/20 15:35:15

 关于Visual Studio 2022利用MySQL ODBC驱动连接mysql数据库参考我的另一篇文章：

Visual Studio 2022连接mysql数据库，解决System.Data.Odbc.OdbcException (0x80131937) - 雨花阁 - 博客园

依旧采用分层解耦的方式来编写代码

建库建表sql:

查看代码 
-- 创建学生管理数据库
CREATE DATABASE IF NOT EXISTS StudentDB;-- 使用StudentDB数据库
USE StudentDB;-- 创建学生表
CREATE TABLE IF NOT EXISTS student (id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键，自增name VARCHAR(100) NOT NULL,         -- 学生姓名，非空age INT NOT NULL,                   -- 学生年龄，非空gender VARCHAR(10) NOT NULL,        -- 性别，非空major VARCHAR(100) NOT NULL,        -- 专业，非空admission_date DATE NOT NULL        -- 入学日期，非空
);-- 插入示例数据（可选）
INSERT INTO student (name, age, gender, major, admission_date)
VALUES ('张三', 20, '男', '计算机科学', '2023-09-01'),('李四', 19, '女', '软件工程', '2023-09-01'),('王五', 21, '男', '数据科学', '2023-09-01'),('赵六', 20, '女', '人工智能', '2023-09-01'),('钱七', 22, '男', '网络工程', '2023-09-01');

在App.config中定义了一个连接字符串

<connectionStrings><add name="StudentDbConnectionString" connectionString="Dsn=mysql_ODBC_test;uid=root;" providerName="System.Data.Odbc" />
</connectionStrings>

StudentDbContext：

查看代码 
using System.Data.Odbc;
using System.Configuration;namespace WindowsFormsApp1.DataAccess
{/// <summary>/// 学生数据库上下文类 - 使用ODBC连接/// </summary>public class StudentDbContext{private readonly string _connectionString;/// <summary>/// 构造函数/// </summary>public StudentDbContext(){_connectionString = ConfigurationManager.ConnectionStrings["StudentDbConnectionString"].ConnectionString;}/// <summary>/// 获取数据库连接/// </summary>/// <returns>ODBC连接对象</returns>public OdbcConnection GetConnection(){return new OdbcConnection(_connectionString);}}
}

Student类

查看代码 
using System;
using System.ComponentModel.DataAnnotations;namespace WindowsFormsApp1.Models
{/// <summary>/// 学生实体类/// </summary>public class Student{/// <summary>/// 学生ID/// </summary>[Key]public int Id { get; set; }/// <summary>/// 学生姓名/// </summary>[Required][StringLength(50)]public string Name { get; set; }/// <summary>/// 学生年龄/// </summary>public int Age { get; set; }/// <summary>/// 学生性别/// </summary>[StringLength(10)]public string Gender { get; set; }/// <summary>/// 学生专业/// </summary>[StringLength(100)]public string Major { get; set; }/// <summary>/// 入学日期/// </summary>public DateTime AdmissionDate { get; set; }}
}

StudentRepository类

查看代码 
using System.Collections.Generic;
using System.Data.Odbc;
using WindowsFormsApp1.Models;
using System;namespace WindowsFormsApp1.DataAccess
{/// <summary>/// 学生仓储类，用于处理学生表的CRUD操作 - 使用ODBC/// </summary>public class StudentRepository{private readonly StudentDbContext _context;/// <summary>/// 构造函数/// </summary>public StudentRepository(){_context = new StudentDbContext();}/// <summary>/// 获取所有学生/// </summary>/// <returns>学生列表</returns>public List<Student> GetAllStudents(){var students = new List<Student>();using (var connection = _context.GetConnection()){connection.Open();var query = "SELECT id, name, age, gender, major, admission_date FROM student";using (var command = new OdbcCommand(query, connection)){using (var reader = command.ExecuteReader()){while (reader.Read()){students.Add(new Student{Id = reader.GetInt32(0),Name = reader.GetString(1),Age = reader.GetInt32(2),Gender = reader.GetString(3),Major = reader.GetString(4),AdmissionDate = reader.GetDateTime(5)});}}}}return students;}/// <summary>/// 根据ID获取学生/// </summary>/// <param name="id">学生ID</param>/// <returns>学生对象</returns>public Student GetStudentById(int id){Student student = null;using (var connection = _context.GetConnection()){connection.Open();var query = "SELECT id, name, age, gender, major, admission_date FROM student WHERE id = ?";using (var command = new OdbcCommand(query, connection)){command.Parameters.AddWithValue("@id", id);using (var reader = command.ExecuteReader()){if (reader.Read()){student = new Student{Id = reader.GetInt32(0),Name = reader.GetString(1),Age = reader.GetInt32(2),Gender = reader.GetString(3),Major = reader.GetString(4),AdmissionDate = reader.GetDateTime(5)};}}}}return student;}/// <summary>/// 添加学生/// </summary>/// <param name="student">学生对象</param>/// <returns>添加后的学生对象</returns>public Student AddStudent(Student student){using (var connection = _context.GetConnection()){connection.Open();var query = "INSERT INTO student (name, age, gender, major, admission_date) VALUES (?, ?, ?, ?, ?)";using (var command = new OdbcCommand(query, connection)){command.Parameters.AddWithValue("@name", student.Name);command.Parameters.AddWithValue("@age", student.Age);command.Parameters.AddWithValue("@gender", student.Gender);command.Parameters.AddWithValue("@major", student.Major);command.Parameters.AddWithValue("@admission_date", student.AdmissionDate);command.ExecuteNonQuery();}}return student;}/// <summary>/// 更新学生信息/// </summary>/// <param name="student">学生对象</param>/// <returns>更新后的学生对象</returns>public Student UpdateStudent(Student student){using (var connection = _context.GetConnection()){connection.Open();var query = "UPDATE student SET name = ?, age = ?, gender = ?, major = ?, admission_date = ? WHERE id = ?";using (var command = new OdbcCommand(query, connection)){command.Parameters.AddWithValue("@name", student.Name);command.Parameters.AddWithValue("@age", student.Age);command.Parameters.AddWithValue("@gender", student.Gender);command.Parameters.AddWithValue("@major", student.Major);command.Parameters.AddWithValue("@admission_date", student.AdmissionDate);command.Parameters.AddWithValue("@id", student.Id);command.ExecuteNonQuery();}}return student;}/// <summary>/// 删除学生/// </summary>/// <param name="id">学生ID</param>/// <returns>是否删除成功</returns>public bool DeleteStudent(int id){int rowsAffected;using (var connection = _context.GetConnection()){connection.Open();var query = "DELETE FROM student WHERE id = ?";using (var command = new OdbcCommand(query, connection)){command.Parameters.AddWithValue("@id", id);rowsAffected = command.ExecuteNonQuery();}}return rowsAffected > 0;}}
}

StudentService类

查看代码 
using System.Collections.Generic;
using WindowsFormsApp1.DataAccess;
using WindowsFormsApp1.Models;namespace WindowsFormsApp1.BusinessLogic
{/// <summary>/// 学生服务类，用于处理学生相关的业务逻辑/// </summary>public class StudentService{private readonly StudentRepository _studentRepository;/// <summary>/// 构造函数/// </summary>public StudentService(){_studentRepository = new StudentRepository();}/// <summary>/// 获取所有学生/// </summary>/// <returns>学生列表</returns>public List<Student> GetAllStudents(){return _studentRepository.GetAllStudents();}/// <summary>/// 根据ID获取学生/// </summary>/// <param name="id">学生ID</param>/// <returns>学生对象</returns>public Student GetStudentById(int id){return _studentRepository.GetStudentById(id);}/// <summary>/// 添加学生/// </summary>/// <param name="student">学生对象</param>/// <returns>添加后的学生对象</returns>public Student AddStudent(Student student){// 在这里可以添加业务逻辑验证if (student.Age < 16 || student.Age > 40){throw new System.Exception("学生年龄必须在16-40岁之间");}return _studentRepository.AddStudent(student);}/// <summary>/// 更新学生/// </summary>/// <param name="student">学生对象</param>/// <returns>是否更新成功</returns>public bool UpdateStudent(Student student){// 在这里可以添加业务逻辑验证if (student.Age < 16 || student.Age > 40){throw new System.Exception("学生年龄必须在16-40岁之间");}// 检查更新后的学生对象是否为空，不为空表示更新成功Student updatedStudent = _studentRepository.UpdateStudent(student);return updatedStudent != null;}/// <summary>/// 删除学生/// </summary>/// <param name="id">学生ID</param>/// <returns>是否删除成功</returns>public bool DeleteStudent(int id){// 在这里可以添加业务逻辑验证return _studentRepository.DeleteStudent(id);}}
}

Form1.cs

查看代码 
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using WindowsFormsApp1.BusinessLogic;
using WindowsFormsApp1.Models;namespace WindowsFormsApp1
{public partial class Form1 : Form{// 业务逻辑层服务private readonly StudentService _studentService;public Form1(){InitializeComponent();_studentService = new StudentService();}/// <summary>/// 窗体加载事件/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void Form1_Load(object sender, EventArgs e){// 加载学生列表LoadStudents();}/// <summary>/// 加载学生列表到DataGridView/// </summary>private void LoadStudents(){try{// 调用业务逻辑层获取所有学生List<Student> students = _studentService.GetAllStudents();// 将学生列表绑定到DataGridViewdataGridViewStudents.DataSource = students.Select(s => new{Id = s.Id,Name = s.Name,Age = s.Age,Gender = s.Gender,Major = s.Major,AdmissionDate = s.AdmissionDate.ToString("yyyy-MM-dd")}).ToList();// 设置列宽dataGridViewStudents.Columns["Id"].Width = 50;dataGridViewStudents.Columns["Name"].Width = 100;dataGridViewStudents.Columns["Age"].Width = 60;dataGridViewStudents.Columns["Gender"].Width = 80;dataGridViewStudents.Columns["Major"].Width = 150;dataGridViewStudents.Columns["AdmissionDate"].Width = 120;}catch (Exception ex){MessageBox.Show("加载学生列表失败：" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);}}/// <summary>/// 点击DataGridView中的行时，将数据填充到表单/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void dataGridViewStudents_CellClick(object sender, DataGridViewCellEventArgs e){if (e.RowIndex >= 0){DataGridViewRow row = dataGridViewStudents.Rows[e.RowIndex];int id = Convert.ToInt32(row.Cells["Id"].Value);try{// 调用业务逻辑层根据ID获取学生Student student = _studentService.GetStudentById(id);if (student != null){// 填充表单textBoxId.Text = student.Id.ToString();textBoxName.Text = student.Name;textBoxAge.Text = student.Age.ToString();comboBoxGender.Text = student.Gender;textBoxMajor.Text = student.Major;dateTimePickerAdmissionDate.Value = student.AdmissionDate;}}catch (Exception ex){MessageBox.Show("获取学生信息失败：" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);}}}/// <summary>/// 添加学生按钮点击事件/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void buttonAdd_Click(object sender, EventArgs e){try{// 验证输入if (!ValidateInput()){return;}// 创建学生对象Student student = new Student{Name = textBoxName.Text,Age = Convert.ToInt32(textBoxAge.Text),Gender = comboBoxGender.Text,Major = textBoxMajor.Text,AdmissionDate = dateTimePickerAdmissionDate.Value};// 调用业务逻辑层添加学生Student addedStudent = _studentService.AddStudent(student);// 显示成功消息MessageBox.Show("学生添加成功！", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);// 刷新学生列表LoadStudents();// 清空表单ClearForm();}catch (Exception ex){MessageBox.Show("添加学生失败：" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);}}/// <summary>/// 更新学生按钮点击事件/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void buttonUpdate_Click(object sender, EventArgs e){try{// 验证ID是否存在if (string.IsNullOrEmpty(textBoxId.Text)){MessageBox.Show("请先选择要更新的学生！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);return;}// 验证输入if (!ValidateInput()){return;}// 创建学生对象Student student = new Student{Id = Convert.ToInt32(textBoxId.Text),Name = textBoxName.Text,Age = Convert.ToInt32(textBoxAge.Text),Gender = comboBoxGender.Text,Major = textBoxMajor.Text,AdmissionDate = dateTimePickerAdmissionDate.Value};// 调用业务逻辑层更新学生bool updated = _studentService.UpdateStudent(student);if (updated){// 显示成功消息MessageBox.Show("学生更新成功！", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);// 刷新学生列表LoadStudents();// 清空表单ClearForm();}else{MessageBox.Show("学生更新失败，学生不存在！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);}}catch (Exception ex){MessageBox.Show("更新学生失败：" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);}}/// <summary>/// 删除学生按钮点击事件/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void buttonDelete_Click(object sender, EventArgs e){try{// 验证ID是否存在if (string.IsNullOrEmpty(textBoxId.Text)){MessageBox.Show("请先选择要删除的学生！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);return;}// 确认删除if (MessageBox.Show("确定要删除该学生吗？", "确认", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes){int id = Convert.ToInt32(textBoxId.Text);// 调用业务逻辑层删除学生bool deleted = _studentService.DeleteStudent(id);if (deleted){// 显示成功消息MessageBox.Show("学生删除成功！", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);// 刷新学生列表LoadStudents();// 清空表单ClearForm();}else{MessageBox.Show("学生删除失败，学生不存在！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);}}}catch (Exception ex){MessageBox.Show("删除学生失败：" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);}}/// <summary>/// 清空表单按钮点击事件/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void buttonClear_Click(object sender, EventArgs e){ClearForm();}/// <summary>/// 刷新列表按钮点击事件/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void buttonRefresh_Click(object sender, EventArgs e){LoadStudents();}/// <summary>/// 验证输入/// </summary>/// <returns></returns>private bool ValidateInput(){// 验证姓名if (string.IsNullOrEmpty(textBoxName.Text)){MessageBox.Show("请输入学生姓名！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);textBoxName.Focus();return false;}// 验证年龄int age;if (string.IsNullOrEmpty(textBoxAge.Text) || !int.TryParse(textBoxAge.Text, out age)){MessageBox.Show("请输入有效的年龄！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);textBoxAge.Focus();return false;}// 验证性别if (string.IsNullOrEmpty(comboBoxGender.Text)){MessageBox.Show("请选择学生性别！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);comboBoxGender.Focus();return false;}// 验证专业if (string.IsNullOrEmpty(textBoxMajor.Text)){MessageBox.Show("请输入学生专业！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);textBoxMajor.Focus();return false;}return true;}/// <summary>/// 清空表单/// </summary>private void ClearForm(){textBoxId.Text = string.Empty;textBoxName.Text = string.Empty;textBoxAge.Text = string.Empty;comboBoxGender.Text = string.Empty;textBoxMajor.Text = string.Empty;dateTimePickerAdmissionDate.Value = DateTime.Now;}}
}

项目结构以及运行结果：

 image

http://www.hskmm.com/?act=detail&tid=35042

相关文章：

  • Cypress 插件实战：让你的测试不再“偶尔掉链子”
  • Telnet发送邮件
  • 2025年实验室/手术室净化工程厂家推荐排行榜：涵盖无尘车间装修、洁净室建设、医院净化工程等全方位解决方案精选
  • 第五周第五天5.5
  • 软件设计中的需求分析——白日梦
  • 2025 石家庄全屋定制工厂推荐：河北森佰特木业，莫兰迪色系/工业风/意式/意式极简/中古风/侘寂风/现代简约/北欧风/奶油胡桃色全屋定制等风格任你选
  • 2025 钢制拖链源头厂家最新推荐排行榜：权威甄选优质品牌，破解选型难题助力企业精准采购
  • 机器学习可扩展性：从1到百万用户的架构演进
  • SOSDP
  • 2025年保洁公司推荐排行榜，驻场保洁/钟点保洁/开荒保洁/外包保洁/商场保洁/办公楼保洁/工厂保洁/医院保洁/企业保洁服务优选指南
  • 联通光猫烽火吉比特HG6145F获取超级密码
  • DBA必备脚本：Oracle获取绑定变量的字面SQL文本版版本替代
  • Newtonsoft.Json笔记 -JToken、JObject、JArray详解
  • 2025 最新活动跟拍直播公司推荐榜：广告影视圈权威评选，揭秘五大高性价比品牌覆盖西安及全国市场，会展 / 企业 / 赛事场景优选
  • 2025 年宣传片拍摄制作公司最新推荐排行榜：覆盖多领域优质服务商，助企业精准选靠谱合作伙伴
  • 02-02串口-USART模块
  • CF2110E Melody
  • 进化计算入门
  • 02-01串口理论知识
  • 赋能安全管控：NVR接入录像回放平台EasyCVR加油站监控应用场景与实际功能
  • .Net 自定义定时器
  • 2025年项目管理工具生态全景：技术主权与AI赋能的行业变革
  • 深度学习
  • ？
  • Microsoft 代理框架简介（预览版）：让每个开发人员都能轻松使用 AI 代理
  • 2025 年破碎机厂家最新推荐榜，聚焦企业技术实力与市场口碑深度解析圆锥/辊式/对辊/煤矸石/砂石破碎机厂家推荐
  • 站位3
  • AI 的能源危机：训练一个模型究竟要耗掉多少电？
  • 2025 年制砂机厂家最新推荐榜，聚焦企业技术实力与市场口碑深度解析高效/冲击式/砂石/新疆制砂机厂家推荐
  • 拆解3D Gaussian Splatting：原理框架、实战 demo 与自驾仿真落地探索！