目录
1.实验目的
1、请设计一个项目连接到自己的MySQL数据库,数据库包含至少三张表;
2、使用dataGridView控件显示表中的数据;
3、实现基本crud操作;
2.具体操作
2.1数据库连接VS2022的操作
环境:VS2022 MySQL-8.0.28
连接过程:MySQL官网下载Connector/NET 8.0.28,项目引用勾选 MySql.data,代码段增加using MySql.Data.MySqlClient;
详见 :https://blog.csdn.net/dushilang1001/article/details/122734055
(1)首先要下载mysql(这里就省略了,网上的教材很多)
(2)下载mysql 的驱动,要不然在VS中连接数据库时会发现没有mysql数据库。(尽管mysql你已经下载,也不会显示,因为你没有mysql对VS的相关驱动),如果驱动安装完成后,再次“数据库连接”会出现以下界面,会有“MySQL Database”选项。
整体的过程按照之前的教程做,遇到了问题:
当我去下载那两个驱动的时候,那两篇文章给的官网的链接都是最新版的,在我自己下载的过程,会显示报错,并且显示了需要的版本。
这时候我们就需要旧的版本。更换版本后就解决了这个问题。
2.2在数据库中建立三张表
直接用Nvaicat Premium手动建立的数据库
示例:
建表:
--学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
插入数据:
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
2.3使用DataGridView控件显示表中的数据;
首先创建DataGridView控件
然后将DataGridView控件与数据库关联然后进行以下操作:
1)我这个是已经添加过的,点击添加数据源
2)选择“数据库”--》“数据集”--》选择自己想要绑定的表格--》点击完成
选择之前连接的数据库
选择想要绑定的表格
3)实现框架
private void button4_Click(object sender, EventArgs e)
{
String table_name = textBox1.Text.Trim();
String Id = textBox2.Text.Trim();
String Name = textBox3.Text.Trim();
String Sex = textBox4.Text.Trim();
String Birth = dateTimePicker1.Value.ToString("yyyy-MM-dd");
string[] row = { Id, Name, Birth, Sex };
try
{
conn.Open();
String insertstr = "INSERT INTO Student (s_id,s_name,s_birth,s_sex) VALUES" + "(" + Id + "," + Name + "," + Birth + "," + Sex + ");";
MySqlCommand cmd = new MySqlCommand(insertstr, conn); //实例化数据库命令对象
cmd.ExecuteNonQuery(); //执行命令
}
catch { MessageBox.Show("输入数据有误,请输入有效数据!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); }
finally { conn.Close(); }
//显示到dataGridView
print_in_dataGridView();
}
4.实现基本crud操作(具体代码)
MySqlConnection conn = new MySqlConnection("Server=localhost;Database=test_database;uid=root;pwd=111111");
private void button1_Click(object sender, EventArgs e)
{
conn.Open();
if (conn.State == ConnectionState.Open)
{
MessageBox.Show("Connection Opened Successfully");
print_in_dataGridView();
}
}
3. 数据库连接
3.1数据库连接操作:
尝试连接数据库,连接成功后会出现弹窗提示。
private void print_in_dataGridView()
{
MySqlCommand mycom = conn.CreateCommand();
mycom.CommandText = "SELECT * FROM student ; ";
MySqlDataAdapter adap = new MySqlDataAdapter(mycom);
DataSet ds = new DataSet();
adap.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
private void button5_Click(object sender, EventArgs e)
{
string M_str_sqlcon = "server=localhost;user id=root;password=20010401;database=test"; //创建数据库连接对象
conn = new MySqlConnection(M_str_sqlcon);
try
{
//打开数据库连接
conn.Open();
MessageBox.Show("数据库已经连接了!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
print_in_dataGridView();
}
3.2 插入
完整填写四个数据后
点击插入,结果在dataGridView显示。
private void button_insert_Click(object sender, EventArgs e)
{
String StuID = textBox_sid.Text;
String StuName = textBox_sname.Text;
String StuBirth = textBox_sbirth.Text;
String StuSex = textBox_ssex.Text;
try
{
conn.Open();
String insertstr = "INSERT INTO Student (s_id,s_name,s_birth,s_sex) VALUES"+"("+StuID+","+StuName+","+StuBirth+","+StuSex+");";
MySqlCommand cmd = new MySqlCommand(insertstr, conn); //实例化数据库命令对象
cmd.ExecuteNonQuery(); //执行命令
}
catch{ MessageBox.Show("输入数据有误,请输入有效数据!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);}
finally { conn.Close(); }
//显示到dataGridView
print_in_dataGridView();
}
3.3 删除
根据学号删除一整行,结果在dataGridView显示。
private void button_delete_Click(object sender, EventArgs e)
{
try
{
conn.Open();
string select_id = textBox_sid.Text;//选择的当前行第一列的值,也就是ID
string delete_by_id = "delete from Student where s_id = " + "\"" + select_id + "\"";//sql删除语句,根据学号删除
MySqlCommand cmd = new MySqlCommand(delete_by_id, conn);
cmd.ExecuteNonQuery(); //执行命令
}
catch { MessageBox.Show("请正确选择行!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); }
finally { conn.Dispose(); }
print_in_dataGridView();
}
3.4修改
根据学号来进行修改(学号无法修改),其他属性可进行勾选(checkBox)后,在文字框(TextBox)内输入修改后的内容即可对表内数据进行修改,结果在dataGridView显示。
private void button_update_Click(object sender, EventArgs e)
{
int flag1 = 0, flag2 = 0;
try
{
conn.Open();//打开数据库
string updatestr = "UPDATE Student SET ";
String StuID = textBox_sid.Text;
String StuName = textBox_sname.Text;
String StuBirth = textBox_sbirth.Text;
String StuSex = textBox_ssex.Text;
if (checkBox_sbirth.Checked == true)
{
if (flag1 == 0)
{
updatestr += "s_birth = " +"\""+ StuBirth+"\"";
flag1 = 1;
}
else
updatestr += ", s_birth = " + "\"" + StuBirth + "\"";
}
if (checkBox_sname.Checked == true)
{
if (flag1 == 0)
{
updatestr += "s_name = " + "\"" + StuName + "\"";
flag1 = 1;
}
else
updatestr += ", s_name = " + "\"" + StuName + "\"";
}
if (checkBox_ssex.Checked == true)
{
if (flag1 == 0)
{
updatestr += "s_sex = " + "\""+ StuSex + "\"";
flag1 = 1;
}
else
updatestr += ", s_sex = " + "\"" + StuSex + "\"";
}
updatestr += " WHERE s_id = " + "\"" + StuID + "\"";
MySqlCommand cmd = new MySqlCommand(updatestr, conn);
cmd.ExecuteNonQuery();
}
catch
{
flag2 = 1;
MessageBox.Show("输入数据违反要求!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally{ conn.Close();}
print_in_dataGridView();
if (flag2 == 0)
{
MessageBox.Show("修改成功!", "Tips", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
}
3.5查询
private void button_select_Click(object sender, EventArgs e)
{
String StuID = textBox_sid.Text;
String StuName = textBox_sname.Text;
String StuSex = textBox_ssex.Text;
String StuBirth = textBox_sbirth.Text;
try
{
conn.Open();
String select_by_id = "select * from Student where ";
int flag1 = 0; //表示前面是否已经加了筛选条件,为1,则后面的条件需要加AND
if (checkBox_sid.Checked == true)
{
if (flag1 == 0)
{
select_by_id += "s_id =" + "\"" + StuID + "\"";
flag1 = 1;
}
else
select_by_id += "AND s_id =" + "\"" + StuID + "\"";
}
if (checkBox_sname.Checked == true)
{
if (flag1 == 0)
{
select_by_id += "s_name =" + "\"" + StuName + "\"";
flag1 = 1;
}
else
select_by_id += "AND s_name =" +"\"" + StuName + "\"";
}
if (checkBox_ssex.Checked == true)
{
if (flag1 == 0)
{
select_by_id += "s_sex =" + "\"" + StuSex + "\"";
flag1 = 1;
}
else
select_by_id += "AND s_sex =" + "\"" + StuSex + "\"";
}
if (checkBox_sbirth.Checked == true)
{
if (flag1 == 0)
{
select_by_id += "s_birth ="+ "\""+StuBirth + "\"";
flag1 = 1;
}
else
select_by_id += "AND Sage =" + "\"" + StuBirth + "\"";
}
MySqlCommand sqlCommand = new MySqlCommand(select_by_id, conn);
MySqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridView1.DataSource = bindingSource;
}
catch{MessageBox.Show("查询语句有误,请认真检查SQL语句!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);}
finally{conn.Close();}
}
4.部分界面展示
连接数据库:
删除(输入id后,点击删除即可):
5. 总结
实验当时存在的问题在下载mysql 的驱动时,在VS中连接数据库时会发现没有mysql数据库。(尽管mysql你已经下载,也不会显示,因为你没有mysql对VS的相关驱动),如果驱动安装完成后,再次“数据库连接”会出现以下界面,会有“MySQL Database”选项。在下载那两个驱动的时候,那两篇文章给的官网的链接都是最新版的,在自己下载的过程,会显示报错,并且显示了需要的版本。除此之外,本次作业在编写MySQL语句时只有在修改和查询时,MySQL的语句需要另定义整型对象Flag来进行实现,MySQL语句中,第一个条件前不需要符号,而之后的条件前需要使用“,”隔开,可以通过checkbox和标志变量(Flag)实现了这一点。