【2019-2020春学期】数据库实验3：CRUD 学习增、删、改、查操作数据库咸鱼3号-

实验目的：

学习增、删、改、查操作。

实验内容：

学习增删改查操作
目标效果：

实验步骤：

1、数据库数据准备：
需要在数据库里准备一张表，以便下面可以进行使用

2、创建新项目：

3、根据目标效果从工具箱添加控件：
（1）添加”DataGridView“，并且连接数据（具体步骤参考：数据库实验2）
（2）添加控件：

4、设置“增”按钮的点击事件：

private void Insert_Click(object sender, EventArgs e) { String StuID = textBox1.Text.Trim(); String StuName = textBox2.Text.Trim(); String StuSex = textBox3.Text.Trim(); String StuAge = textBox4.Text.Trim(); String StuSdept = textBox5.Text.Trim(); try {                 con.Open(); string insertStr = "INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) " + "VALUES('" + StuID + "','" + StuName + "','" + StuSex + "'," + StuAge + ",'" +  StuSdept + "')"; SqlCommand cmd = new SqlCommand(insertStr, con);                 cmd.ExecuteNonQuery(); } catch {                 MessageBox.Show("输入数据违反要求"); } finally {                 con.Dispose(); } this.studentTableAdapter.Fill(this.sCHOOLDataSet.Student); } 

注意事项：

string insertStr = "INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) " + "VALUES(" + StuID + "," + StuName + "," + StuSex + "," + StuAge + "," +  StuSdept + ")"; 

这么写是错误的，等价的SQL语句为：
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES(201215134,秦修,男,23,FM)
和数据库里定义的属性是不相符合的，要根据属性的要求添加单引号。

5、设置“删”的点击事件：

private void Delete_Click(object sender, EventArgs e) { try {                 con.Open(); string select_id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//选择的当前行第一列的值，也就是ID string delete_by_id = "delete from Student where Sno=" + select_id;//sql删除语句 SqlCommand cmd = new SqlCommand(delete_by_id, con);                 cmd.ExecuteNonQuery(); } catch {                 MessageBox.Show("请正确选择行!"); } finally {                 con.Dispose(); } this.studentTableAdapter.Fill(this.sCHOOLDataSet.Student); } 

注意事项：
在进行删除测试的时候，选择的数据有要求。由于在我的数据库中，有些数据和在另一张中也使用到了，这时候如果进行删除操作，是不能实现的。
6、设置“改”的点击事件：

private void Update_Click(object sender, EventArgs e) { String StuID = textBox1.Text.Trim(); String StuName = textBox2.Text.Trim(); try {                 con.Open(); string insertStr = "UPDATE Student SET Sname = '" + StuName + "' WHERE Sno = '" + StuID + "'"; SqlCommand cmd = new SqlCommand(insertStr, con);                 cmd.ExecuteNonQuery(); } catch {                 MessageBox.Show("输入数据违反要求!"); } finally {                 con.Dispose(); } this.studentTableAdapter.Fill(this.sCHOOLDataSet.Student); } 

7、设置“查”的点击事件：

private void Select_Click(object sender, EventArgs e) { String StuID = textBox1.Text.Trim(); String conn = "Data Source=.;Initial Catalog=SCHOOL;User ID=sa;Password=********"; SqlConnection sqlConnection = new SqlConnection(conn); try {                 sqlConnection.Open(); String select_by_id = "select * from Student where Sno='" + StuID + "'"; SqlCommand sqlCommand = new SqlCommand(select_by_id,sqlConnection); SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(); BindingSource bindingSource = new BindingSource();                 bindingSource.DataSource = sqlDataReader;                 dataGridView1.DataSource = bindingSource; } catch {                 MessageBox.Show("查询语句有误！"); } finally {                 sqlConnection.Close(); } this.studentTableAdapter.Fill(this.sCHOOLDataSet.Student); } 

8、设置“Close”的点击事件

private void Close_Click(object sender, EventArgs e) {             Application.Exit(); } 

运行效果：

（1）增加数据

增加数据失败：

（2）删除数据


未正确选择行时：

（3）修改数据


（4）查询数据

代码：

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace CRUD { public partial class Form1 : Form { SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=SCHOOL;User ID=sa;Password=*********"); public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { // TODO: 这行代码将数据加载到表“sCHOOLDataSet.Student”中。您可以根据需要移动或删除它。 this.studentTableAdapter.Fill(this.sCHOOLDataSet.Student); } private void textBox1_TextChanged(object sender, EventArgs e) { } private void Insert_Click(object sender, EventArgs e) { String StuID = textBox1.Text.Trim(); String StuName = textBox2.Text.Trim(); String StuSex = textBox3.Text.Trim(); String StuAge = textBox4.Text.Trim(); String StuSdept = textBox5.Text.Trim(); try {                 con.Open(); string insertStr = "INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) " + "VALUES('" + StuID + "','" + StuName + "','" + StuSex + "'," + StuAge + ",'" +  StuSdept + "')"; SqlCommand cmd = new SqlCommand(insertStr, con);                 cmd.ExecuteNonQuery(); } catch {                 MessageBox.Show("输入数据违反要求"); } finally {                 con.Dispose(); } this.studentTableAdapter.Fill(this.sCHOOLDataSet.Student); } private void Close_Click(object sender, EventArgs e) {             Application.Exit(); } private void Delete_Click(object sender, EventArgs e) { try {                 con.Open(); string select_id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//选择的当前行第一列的值，也就是ID string delete_by_id = "delete from Student where Sno=" + select_id;//sql删除语句 SqlCommand cmd = new SqlCommand(delete_by_id, con);                 cmd.ExecuteNonQuery(); } catch {                 MessageBox.Show("请正确选择行!"); } finally {                 con.Dispose(); } this.studentTableAdapter.Fill(this.sCHOOLDataSet.Student); } private void Update_Click(object sender, EventArgs e) { String StuID = textBox1.Text.Trim(); String StuName = textBox2.Text.Trim(); try {                 con.Open(); string insertStr = "UPDATE Student SET Sname = '" + StuName + "' WHERE Sno = '" + StuID + "'"; SqlCommand cmd = new SqlCommand(insertStr, con);                 cmd.ExecuteNonQuery(); } catch {                 MessageBox.Show("输入数据违反要求!"); } finally {                 con.Dispose(); } this.studentTableAdapter.Fill(this.sCHOOLDataSet.Student); } private void Select_Click(object sender, EventArgs e) { String StuID = textBox1.Text.Trim(); String conn = "Data Source=.;Initial Catalog=SCHOOL;User ID=sa;Password=************"; SqlConnection sqlConnection = new SqlConnection(conn); try {                 sqlConnection.Open(); String select_by_id = "select * from Student where Sno='" + StuID + "'"; SqlCommand sqlCommand = new SqlCommand(select_by_id,sqlConnection); SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(); BindingSource bindingSource = new BindingSource();                 bindingSource.DataSource = sqlDataReader;                 dataGridView1.DataSource = bindingSource; } catch {                 MessageBox.Show("查询语句有误！"); } finally {                 sqlConnection.Close(); } this.studentTableAdapter.Fill(this.sCHOOLDataSet.Student); } } } 

心得：

并不是很顺利，踩了一些坑