C#连接数据库
一、作业要求
1、请设计一个项目连接到自己的MySQL数据库,数据库包含至少三张表;
2、使用DataGridView控件显示表中的数据
3、实现基本的crud操作
二、功能说明
1.vs通过NuGet包管理器下载MySql.Data连接mysql数据库
2.使用控件DataGridView进行表中数据的显示。
查询:查看表中所有数据元素
增加:根据TextBox中填写的信息向表中插入记录,其中编号不能重复且不为空
删除:根据编号删除记录
修改:根据编号修改记录
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 MySql.Data.MySqlClient;
namespace MyDataBase
{
public partial class Form1 : Form
{
private MySqlConnectionStringBuilder builder=new MySqlConnectionStringBuilder();
private MySqlConnection connection;
public Form1()
{
InitializeComponent();
}
//获取连接,每次操作前需要打开连接
private void button1_Click(object sender, EventArgs e)
{
builder.UserID = "root";
builder.Password = "zz923";
builder.Server = "localhost";
builder.Database = "dbclass";
connection=new MySqlConnection(builder.ConnectionString);
connection.Open();
}
private void read_Click(object sender, EventArgs e)
{
//先清空数据
dataGridView1.Rows.Clear();
string sql = "select * from c_sharp";
textBox4.Text=sql;
MySqlCommand cmd = new MySqlCommand(sql, connection);
MySqlDataReader reader = cmd.ExecuteReader();
//设置dataGridView的列
dataGridView1.ColumnCount = 3;
dataGridView1.ColumnHeadersVisible = true;
//设置字体
DataGridViewCellStyle columnHeaderStyle = new DataGridViewCellStyle();
columnHeaderStyle.BackColor = Color.Beige;
columnHeaderStyle.Font = new Font("Verdana", 10, FontStyle.Bold);
dataGridView1.ColumnHeadersDefaultCellStyle = columnHeaderStyle;
dataGridView1.Columns[0].Name = "编号";
dataGridView1.Columns[1].Name = "姓名";
dataGridView1.Columns[2].Name = "年龄";
//根据查询结果像DataGridView中添加数据行
while (reader.Read())
{
int index = this.dataGridView1.Rows.Add();
this.dataGridView1.Rows[index].Cells[0].Value = reader.GetInt32("id");
this.dataGridView1.Rows[index].Cells[1].Value = reader.GetString("name");
this.dataGridView1.Rows[index].Cells[2].Value = reader.GetInt32("age");
}
connection.Close(); //关闭连接
}
private void create_Click(object sender, EventArgs e)
{
//字段为字符窜时要加上单引号 ''
string sql = "insert into c_sharp values ("+ textBox1.Text+",'"+textBox2.Text + "'" + ","+textBox3.Text+")";
excuteNoQuery(sql);
}
private void delete_Click(object sender, EventArgs e)
{
string sql = "delete from c_sharp where id="+textBox1.Text;
excuteNoQuery(sql);
}
//根据编号修改
private void update_Click(object sender, EventArgs e)
{
string sql = "update c_sharp set name="+"'"+textBox2.Text+"'"+",age="+textBox3.Text+" where id="+textBox1.Text;
excuteNoQuery(sql);
}
private void excuteNoQuery(string sql)
{
textBox4.Text = sql;
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.ExecuteNonQuery();
connection.Close();
}
}
}