一、作业题目
1)请设计一个项目连接到自己的MySQL数据库,数据库包含至少三张表;
2)使用dataGridView控件显示表中的数据;
3)实现基本crud操作;
二、功能描述
1)假设某软件数据库中关于其用户有三张表,分别命名为user-login、user-info、user-work。其中,user-login有用户名、密码两个字段,user-info有用户名、年龄、性别三个字段,user-work有用户名和工作两个字段。现用c#连接该数据库。
2)用dataGridView控件显示三张表的信息
3)以user-login表为例,实现表的crud(增删改查)操作。
三、核心代码
(1)显示数据库表
//数据库连接类
class DBConnection
{
private MySqlConnection connection;
private string connectionStr;
public DBConnection()
{
try
{
connectionStr = "server=localhost;User Id=root;password=7012;Database=test;SslMode = none;";
connection = new MySqlConnection(connectionStr);
}
catch (Exception exception)
{
throw new Exception("DBConnection Constructor:" + exception.Message);
}
}
public DataSet Select()
{
try
{
if (connection != null)
{
connection.Open();
}
string cmdStr = "Select * from user-login";
MySqlCommand sqlCmd = new MySqlCommand(cmdStr, connection);
MySqlDataAdapter sda = new MySqlDataAdapter(sqlCmd);
DataSet ds = new DataSet();
sda.Fill(ds, "user");
return ds;
}
catch (Exception exception)
{
throw new Exception("SelectMethod:" + exception.Message);
}
finally
{
connection.Close();
}
}
}
//数据显示
private void button1_Click(object sender, EventArgs e)
{
DBConnection conn = new DBConnection();
DataSet dataset = conn.Select();
if (dataset == null)
{
MessageBox.Show("没有数据");
}
dataGridView1.DataSource = dataset.Tables[0];
}
(2)表的插入操作
private void button2_Click(object sender, EventArgs e)
{
string username = textBox1.Text;
string password = textBox2.Text;
if (textBox1.Text == "" )
{
MessageBox.Show("用户名不能为空", "警告提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
textBox1.Focus();
return;
}
else
{
string connstr = "server=localhost;User Id=root;password=7012;Database=test;SslMode = none;";
try
{
MySqlConnection conn = new MySqlConnection(connstr);
conn.Open();
string sqlinsert = "insert into user-login values('" + username + "','" + password + "')";
MySqlCommand sc = new MySqlCommand(sqlinsert, conn);
sc.ExecuteNonQuery();
textBox1.Text = "";
textBox2.Text = "";
MessageBox.Show("数据已经添加成功", "温馨提示");
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
(3)表的删除操作
private void button3_Click(object sender, EventArgs e)
{
string username = textBox1.Text;
if (textBox1.Text == "")
{
MessageBox.Show("用户名不能为空", "警告提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
textBox1.Focus();
return;
}
else
{
string connstr = "server=localhost;User Id=root;password=7012;Database=test;SslMode = none;";
try
{
MySqlConnection conn = new MySqlConnection(connstr);
conn.Open();
string sqldelete = "Delete from user-login where username='" + username + "'";
MySqlCommand sc = new MySqlCommand(sqldelete, conn);
sc.ExecuteNonQuery();
textBox1.Text = "";
textBox2.Text = "";
MessageBox.Show("数据删除成功", "温馨提示");
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
(4)表的修改操作
private void button4_Click(object sender, EventArgs e)
{
string username = textBox1.Text;
string password = textBox2.Text;
if (textBox1.Text == "")
{
MessageBox.Show("用户名不能为空", "警告提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
textBox1.Focus();
return;
}
else
{
string connstr = "server=localhost;User Id=root;password=7012;Database=test;SslMode = none;";
try
{
MySqlConnection conn = new MySqlConnection(connstr);
conn.Open();
string sqlupdate = "update user-login set password=" + password + " where username='" + username + "'";
MySqlCommand sc = new MySqlCommand(sqlupdate, conn);
sc.ExecuteNonQuery();
textBox1.Text = "";
textBox2.Text = "";
MessageBox.Show("数据更新成功", "温馨提示");
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
(5)表的查询操作
private void button5_Click(object sender, EventArgs e)
{
string username = textBox1.Text;
if (textBox1.Text == "")
{
MessageBox.Show("用户名不能为空", "警告提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
textBox1.Focus();
return;
}
else
{
string connstr = "server=localhost;User Id=root;password=7012;Database=test;SslMode = none;";
try
{
MySqlConnection conn = new MySqlConnection(connstr);
conn.Open();
string sqlselect = "select * from user-login where username='" + username + "'";
MySqlCommand sc = new MySqlCommand(sqlselect, conn);
MySqlDataAdapter sda = new MySqlDataAdapter(sc);
DataSet ds = new DataSet();
sda.Fill(ds, "user-login");
dataGridView1.DataSource = ds.Tables[0];
sc.ExecuteNonQuery();
textBox1.Text = "";
textBox2.Text = "";
MessageBox.Show("数据查询成功", "温馨提示");
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
四、结果截图
(1)表的显示
点击查看按钮,dataGridView中显示原始表的内容
(2)表的插入操作
输入用户名小刘及其密码,将该条数据插入数据库,插入记录后点击查看,数据在窗体显示
(3)表的删除操作
删除用户张三对应的数据段,删除记录后点击查看,删除后的数据在窗体显示
(4)表的修改操作
将用户李四的密码修改,修改记录后点击查看,修改后数据在窗体显示
(5)表的查询操作
查询用户赵六的信息,点击查询记录按钮,查询结果在窗体显示
五、仓库链接
Gitee
总结
本次实验用高级语言成功连接了数据库,并编程实现了对数据库中表的增删改查操作,利用dataGridView控件将结果显示在Winform窗体中。通过本次实验,我学习了高级语言的基本数据库操作,为日后的学习打下了基础。