Visual Studio 2022连接mysql数据库2，利用 ADO.NET技术实现一个表的CRUD

 关于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();

                // 将学生列表绑定到DataGridView
                dataGridViewStudents.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;
        }
    }
}

项目结构以及运行结果：