C#连接mysql基本实现方案

准备工作

1: 新建 studentmanagersystem 数据库
2：新建表：login student
   login:  name passwd
   student sNo sName age score sex

复制贴贴下面代码

具体操作

**（1）下载 MySql.Data.dll ⼀般在MySQL安装器内，有.NET相关的 **

（2）引⽤ MySql.Data.dll

在Visual Studio的“解决⽅案资源管理器”⾥ ，选择“引⽤”，右键“添加引⽤”，添加对应版本的MySql.Data.dll 动态库⽂件。 那么如何找到？：找到对应.NET版本（选择项⽬，右键属性，查看⽬标框架）⽂件路径的 MySql.Data.dll 动态库。本⼈项⽬的版本是 .net framework 4.8 对应路径：C:\Program Files (x86)\MySQL\Connector NET 8.0\Assemblies\v4.8\MySql.Data.dll

（3）连接MySQL数据

• 创建连接字符串——>MySqlConnection对象——>MySQLCommand对象

internal class MySqlOpHelper {
private string conStr = null;
private MySqlConnection msc = null;
private MySqlCommand msco = null;
private MySqlDataReader msdr= null;
// ⽤于系统登录 存储⽤户名和密码
public Dictionary<string, string> dics = null;
// ⽤于数据库查询后的数据对象List<StudentInfo>
public List<StudentInfo> stus = null;
public MySqlOpHelper(string conStr) {
this.conStr = conStr ?? throw new ArgumentNullException(nameof(conStr));
}
/// <summary>
/// 系统 登录
/// </summary>
/// <param name="opStr"></param>
public void OpLoginMySql(string queryStr) {
try {
msc = new MySqlConnection(conStr);
msc.Open();
msco = new MySqlCommand(queryStr, this.msc);
msdr = msco.ExecuteReader();
dics = new Dictionary<string, string>();
while (msdr.Read()) {
dics.Add(msdr[0].ToString(), msdr[1].ToString());
}
} catch {
MessageBox.Show("登录失败！", "⽤户登录", MessageBoxButtons.OK, MessageBoxIcon.Error);
} finally {
msco.Dispose();
msc.Close();
}
}
/// <summary>
/// 数据库操作 增删改
/// </summary>
/// <param name="opStr"></param>
public void OpAddDeleUpdateMySql(string opStr) {
try {
msc = new MySqlConnection(conStr);
msco = new MySqlCommand(opStr,this.msc);
msc.Open();
msco.ExecuteNonQuery();
MessageBox.Show("操作成功！", "数据库操作", MessageBoxButtons.OK, MessageBoxIcon.Information);
} catch {
MessageBox.Show("操作失败！", "数据库操作", MessageBoxButtons.OK, MessageBoxIcon.Error);
} finally {
msco.Dispose();
msc.Close();
}
}
/// <summary>
/// 数据库操作 查询
/// </summary>
/// <param name="queryStr"></param>
public void OpQueryMySql(string queryStr) {
try {
msc = new MySqlConnection(conStr);
msco = new MySqlCommand(queryStr, this.msc);
msc.Open();
msdr = msco.ExecuteReader();
stus = new List<StudentInfo>();
while (msdr.Read()) {
stus.Add(new StudentInfo(msdr[0].ToString(), msdr[1].ToString(), msdr[2].ToString(),
int.Parse(msdr[3].ToString()), int.Parse(msdr[4].ToString())));
}
//foreach(var i in stus) {
// MessageBox.Show(i.ToString());
//}
} catch {
MessageBox.Show("查询失败！", "数据库查询", MessageBoxButtons.OK, MessageBoxIcon.Error);
} finally {
msco.Dispose();
msc.Close();
}
}
}

登陆功能实现

/// <summary>
/// 数据库连接字符串
/// </summary>
private string connectStr = "server=127.0.0.1;port=3306;user=root;password=1234;database=studentmanagersystem";
/// <summary>
/// 数据库login表查询语句，⽤户登录
/// </summary>
private string operatorStr = "select name,passwd from login;";
/// <summary>
/// 登录 事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnLogin_Click(object sender, EventArgs e) {
LoginSystem();
}
/// <summary>
/// 登录⽅法
/// </summary>
private void LoginSystem() {
MySqlOpHelper msoph = new MySqlOpHelper(connectStr);
msoph.OpLoginMySql(operatorStr);
bool flag = false;
foreach (var i in msoph.dics) {
if (tbName.Text == i.Key && tbPasswd.Text == i.Value) {
flag = true;
new stumanager().Show();
this.Hide();
}
    }
if (flag == false) {
MessageBox.Show("登录失败！", "⽤户登录", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}

管理功能实现：

增、删、改

增加

// 信息输⼊窗⼝ 类
/// <summary>
/// 数据库连接字符串
/// </summary>
private string connectStr = "server=127.0.0.1;port=3306;user=root;password=1234;database=studentmanagersystem";
/// <summary>
/// 信息保存 事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSave_Click(object sender, EventArgs e) {
string addStr = $"insert into student(sNo,sName,sex,age,score) values('{tbSno.Text}','{tbSname.Text}'," +
$"'{tbSex.Text}',{int.Parse(tbAge.Text)},{int.Parse(tbScore.Text)});";
MySqlOpHelper msop=new MySqlOpHelper(connectStr);
msop.OpAddDeleUpdateMySql(addStr);
tbSno.Text = "";
tbSname.Text = "";
tbSex.Text = "";
tbAge.Text = "";
tbScore.Text = "";
}
// 管理界⾯——事件
/// <summary>
/// 增 事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAdd_Click(object sender, EventArgs e) {
new studentinput().Show();
}

删

/// <summary>
/// 删 事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>

private void btnDeleted_Click(object sender, EventArgs e) {
DeleteData();
}
/// <summary>
/// 删除⽅法
/// </summary>
private void DeleteData() {
// 获取选中的学⽣
StudentInfo currentStu = msop.stus[dgvShow.CurrentRow.Index];
//MessageBox.Show(currentStu.sNo);
string deleteStr = $"delete from student where sNo='{currentStu.sNo}';";
msop = new MySqlOpHelper(connectStr);
msop.OpAddDeleUpdateMySql(deleteStr);
}

改

/// <summary>
/// 修改 事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnUpdate_Click(object sender, EventArgs e) {
UpdateData();
}
/// <summary>
/// 修改⽅法
/// </summary>
private void UpdateData() {
StudentInfo selectedStu = msop.stus[dgvShow.CurrentRow.Index];
string updateStr = $"update student set sName='{dgvShow["sName", dgvShow.CurrentRow.Index].Value}'," +
$"sex='{dgvShow["sex", dgvShow.CurrentRow.Index].Value}'," +
$"age={dgvShow["age", dgvShow.CurrentRow.Index].Value}," +
$"score={dgvShow["score", dgvShow.CurrentRow.Index].Value} where sNo='{selectedStu.sNo}'";
msop = new MySqlOpHelper(connectStr);
msop.OpAddDeleUpdateMySql(updateStr);
}

查

/// <summary>
/// 数据库连接字符串
/// </summary>
private string connectStr = "server=127.0.0.1;port=3306;user=root;password=1234;database=studentmanagersystem";
/// <summary>
/// 数据库student表查询语句
/// </summary>
private string operatorStr = "select sNo,sName,sex,age,score from student;";
/// <summary>
/// ⽤于各种数据操作 ⾃定义类
/// </summary>
MySqlOpHelper msop = null;
/// <summary>
/// 显⽰数据
/// </summary>
private void ShowStudentInfo() {
msop = new MySqlOpHelper(connectStr);
// 获得msop.stus 这个对象集合 List<StudentInfo>
msop.OpQueryMySql(operatorStr);
// 数据源绑定
dgvShow.DataSource = msop.stus;
}
/// <summary>
/// 查询 事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnQuery_Click(object sender, EventArgs e) {
// 直接查询
if (tbByName.Text == "") {
ShowStudentInfo();
} else {
// 通过姓名查找
operatorStr = $"select sNo,sName,sex,age,score from student where sName='{tbByName.Text}';";
ShowStudentInfo();
operatorStr = "select sNo,sName,sex,age,score from student";
}
}

排序

/// <summary>
/// 按成绩排序 事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSort_Click(object sender, EventArgs e) {
if (msop != null) {
msop.OpQueryMySql(operatorStr);
msop.stus.Sort(delegate (StudentInfo stu1, StudentInfo stu2) {
return stu2.score.CompareTo(stu1.score);
});
dgvShow.DataSource = msop.stus;// 排序后，数据源重写绑定
} else {
MessageBox.Show("排序失败！", "数据排序", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}