有待改善的学生表。
界面
增
删
我刚开始想删一个以前存的,发现会报错,在SQL Server中输入对应的删除语句,发现是因为该学生有选课记录。
然后我就删除的新插入的那个学生,结果成功了,因为该学生还没有选课记录。
改
查
增删改查代码
private void buttonInsert_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();
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=SCHOOL;User ID=sa;Password=sql");//定义
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);//Form1_Load里的那条代码
}
private void buttonDelete_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=SCHOOL;User ID=sa;Password=sql");//定义
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);//Form1_Load里的那条代码
}
private void buttonUpdate_Click(object sender, EventArgs e)//根据学号修改姓名
{
String StuID = textBox1.Text.Trim();
String StuName = textBox2.Text.Trim();
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=SCHOOL;User ID=sa;Password=sql");//定义
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);//Form1_Load里的那条代码
}
private void buttonSelect_Click(object sender, EventArgs e)//根据学号查找
{
String StuID = textBox1.Text.Trim();
String conn = "Data Source=.;Initial Catalog=SCHOOL;User ID=sa;Password=sql";
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("查询语句有误,请认真检查SQL语句!");
}
finally
{
sqlConnection.Close();
}
}
完整代码↓
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 TestCURD
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: 这行代码将数据加载到表“sCHOOLDataSet.Student”中。您可以根据需要移动或删除它。
this.studentTableAdapter.Fill(this.sCHOOLDataSet.Student);
}
private void buttonInsert_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();
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=SCHOOL;User ID=sa;Password=sql");//定义
try
{
con.Open();//打开
string insertStr = "INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) " +
"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);//Form1_Load里的那条代码
}
private void buttonDelete_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=SCHOOL;User ID=sa;Password=sql");//定义
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);//Form1_Load里的那条代码
}
private void buttonUpdate_Click(object sender, EventArgs e)//根据学号修改姓名
{
String StuID = textBox1.Text.Trim();
String StuName = textBox2.Text.Trim();
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=SCHOOL;User ID=sa;Password=sql");//定义
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);//Form1_Load里的那条代码
}
private void buttonSelect_Click(object sender, EventArgs e)//根据学号查找
{
String StuID = textBox1.Text.Trim();
String conn = "Data Source=.;Initial Catalog=SCHOOL;User ID=sa;Password=sql";
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("查询语句有误,请认真检查SQL语句!");
}
finally
{
sqlConnection.Close();
}
}
private void buttonClose_Click(object sender, EventArgs e)
{
Application.Exit();
}
}
}