一.实验目的
1、请设计一个项目连接到自己的MySQL数据库,数据库包含至少三张表;
2、使用dataGridView控件显示表中的数据;
3、实现基本crud操作;
二.实验内容
下载三个驱动:1.mysql odbc驱动 2.visual studio连接mysql工具 3.mysql数据库.net开发驱动
驱动介绍:
1.MySQL Connector/ODBC 有时也可以叫做 MyODBC,用户可以用ODBC (Open Database Connectivity,开放数据库互联)数据库连接Mysql的服务器。比如说,用户可以使用Windows或者Unix平台中的应用程序,如微软的 Access,Excel和Borland 的Delphi去连接数据库服务器。
2.mysql for visual studio(VS连接MySQL工具)是一款visual studio连mysql工具,它可以直接集成到Server Explorer中,为设置新连接和处理数据库对象提供无缝体验,但是想让VS访问到MySQL仅仅mysql for visual studio是不够的,还需要下载mysql connector net。
3.MySQL Connector/Net 是 MySQL 官方的 .NET 驱动程序,MySQL 官方的 .NET 客户端开发包。mysql 数据库最新版dotnet数据库连接驱动。
下载好后打开安装包:
点击next后选择typical即可。然后就可以安装成功啦。其他的下载和这个一样
现在我们重启visual studio,在菜单栏里面选择视图,然后打开服务器资源管理器,选择数据连接,点击添加连接
我们可以输入相关信息,根据mysql
然后先测试连接
这样就完成了连接,属性界面会显示详细信息,服务器资源管理器会显示数据库信息。
核心代码:
private void button5_Click(object sender, EventArgs e)
{
string M_str_sqlcon = "server=localhost;user id=root;password=自己mysql的密码;database=使用的数据库"; //创建数据库连接对象
conn = new MySqlConnection(M_str_sqlcon);
try
{
//打开数据库连接
conn.Open();
MessageBox.Show("数据库已经连接了!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
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();
}
private void button3_Click(object sender, EventArgs e)
{
try
{
conn.Open();
string select_id = textBox2.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();
}
private void button2_Click(object sender, EventArgs e)
{
int flag1 = 0, flag2 = 0;
try
{
conn.Open();//打开数据库
string updatestr = "UPDATE Student SET ";
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 };
if (checkBox_birth.Checked == true)
{
if (flag1 == 0)
{
updatestr += "s_birth = " + "\"" + Birth + "\"";
flag1 = 1;
}
else
updatestr += ", s_birth = " + "\"" + Birth + "\"";
}
if (checkBox_name.Checked == true)
{
if (flag1 == 0)
{
updatestr += "s_name = " + "\"" + Name + "\"";
flag1 = 1;
}
else
updatestr += ", s_name = " + "\"" + Name + "\"";
}
if (checkBox_sex.Checked == true)
{
if (flag1 == 0)
{
updatestr += "s_sex = " + "\"" + Sex + "\"";
flag1 = 1;
}
else
updatestr += ", s_sex = " + "\"" + Sex + "\"";
}
updatestr += " WHERE s_id = " + "\"" + Id + "\"";
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);
}
}
private void button1_Click(object sender, EventArgs e)
{
string table_name = textBox1.Text.Trim();
string name = textBox3.Text.Trim();
string date = dateTimePicker1.Text;
string sex = textBox4.Text.Trim();
if (table_name == null && (table_name != "教师") && (table_name!="学生"))
{
MessageBox.Show("请正确填写表格名");
}
else
{
string sql = "select * from " + table_name;
mda = new MySqlDataAdapter(sql, conn);
ds = new DataSet();
mda.Fill(ds, table_name);
//显示数据
dataGridView1.DataSource = ds.Tables[table_name];
conn.Close();
}
}