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

实验目的：

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

实验内容：

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

实验步骤：

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);
        }
    }
}

心得：

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