C#学生模块增删改查
rikiemo 人气:0
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 text3_CRUD
{
public partial class Form1 : Form
{
//把连接数据库的字符串提取出来，就不用每次都要写，增加代码复用性
private string str = "data source=本地IP;initial catalog=数据库名;user ID=用户名;pwd=密码";
public Form1()
{
InitializeComponent();
}
private void TextBox5_TextChanged(object sender, EventArgs e)
{
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void Label10_Click(object sender, EventArgs e)
{
}
/// <summary>
/// 添加学生信息档案
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButAdd_Click(object sender, EventArgs e)
{
//获取各个文本框的数据
string name = txtname.Text;
string sex = txtsex.Text;
string college = txtcollege.Text;
string id = txtid.Text;
string grade = txtgrade.Text;
string phone = txtphone.Text;
string email = txtemail.Text;
string qq = txtqq.Text;
string room = txtroom.Text;
using (var conn = new SqlConnection(this.str))//定义一个数据库连接实例
{
conn.Open();//打开数据库
Form1 f = new Form1();//实例化Form1窗体对象
if (f.Existence(id, conn))//检查数据库 存不存在此条记录，存在则插入
{
SqlParameter[] para = new SqlParameter[]//构建存储过程的输入参数
{
new SqlParameter("@name",name),
new SqlParameter("@sex", sex),
new SqlParameter("@college", college),
new SqlParameter("@id", id),
new SqlParameter("@grade", grade),
new SqlParameter("@phone", phone),
new SqlParameter("@email", email),
new SqlParameter("@qq", qq),
new SqlParameter("@room", room),
};
string sql = "insert into Students values(@name, @sex, @college, @id, @grade, @phone, @email, @qq, @room);";//定义一个数据库操作指令集
SqlCommand com = new SqlCommand(sql, conn);//执行数据库操作指令
com.Parameters.AddRange(para);//將参数和命令对象的参数集合绑定
int result = (int)com.ExecuteNonQuery();//针对Connection执行的SQL语句，返回受影响的行数，result > 0则表示SQL语句执行成功
if (result > 0)
{
MessageBox.Show("添加成功！");//弹窗显示“添加成功”
this.Form1_Load_1(null, null);//刷新数据
}
else
{
MessageBox.Show("添加失败！");
}
}
else
{
MessageBox.Show("数据已经存在！");
}
conn.Close();//关闭数据库
//Application.Exit();//关闭整个应用程序
}
}
/// <summary>
/// 根据ID值判断数据表Students中是否存在这个人，存在返回false，不存在返回true
/// </summary>
/// <param name="id"></param>
/// <param name="conn"></param>
/// <returns></returns>
public bool Existence(string id, SqlConnection conn)
{
string txtStr = string.Format( "select id from Students where id = '{0}' " ,id);//定义一个数据库操作指令集
SqlDataAdapter sda = new SqlDataAdapter(txtStr, conn);//定义一个数据库适配器
DataSet ds = new DataSet();//定义数据集合
sda.Fill(ds);//填充数据集合
DataTable dt = ds.Tables[0];//將数据集合中的第一张表赋值给DataTable
if(dt.Rows.Count > 0) //count > 0表示有数据
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// 对数据库进行的动态查询，不管用户掌握的信息有多少都可以查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnSelect_Click(object sender, EventArgs e)
{
//获取各个文本框的数据
string name = txtname.Text;
string sex = txtsex.Text;
string college = txtcollege.Text;
string id = txtid.Text;
string grade = txtgrade.Text;
string phone = txtphone.Text;
string email = txtemail.Text;
string qq = txtqq.Text;
string room = txtroom.Text;
using(var conn = new SqlConnection(this.str))//定义一个数据库连接实例
{
conn.Open();//打开数据库
StringBuilder sb = new StringBuilder();//创建一个字符串变量
sb.Append("select name, sex, college, id, grade,phone, email, qq, room from Students where 1=1");
//判断用户有没有给出其它的查询条件，有则添加进sql语句
if (name != "")
{
sb.AppendFormat(" and name = '{0}'", name);
}
if (sex != "")
{
sb.AppendFormat(" and sex = '{0}'", sex);
}
if (college != "")
{
sb.AppendFormat(" and college = '{0}'", college);
}
if (id != "")
{
sb.AppendFormat(" and id = '{0}'", id);
}
if (grade != "")
{
sb.AppendFormat(" and grade = '{0}'", grade);
}
if (phone != "")
{
sb.AppendFormat(" and phone = '{0}'", phone);
}
if (email != "")
{
sb.AppendFormat(" and email = '{0}'", email);
}
if (qq != "")
{
sb.AppendFormat(" and qq = '{0}'", qq);
}
if (room != "")
{
sb.AppendFormat(" and room = '{0}'", room);
}
string sql = sb.ToString();
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();//定义一个数据集合
adapter.Fill(ds);//填充数据集合
dataGridView1.DataSource = ds.Tables[0];//把数据集合绑定到dataGridView上，dataGridView会以表格的形式显示出来
conn.Close();//关闭数据库
}
}
/// <summary>
/// 修改学生信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnUpdate_Click(object sender, EventArgs e)
{
//获取各个文本框的数据
string name = txtname.Text;
string sex = txtsex.Text;
string college = txtcollege.Text;
string id = txtid.Text;
string grade = txtgrade.Text;
string phone = txtphone.Text;
string email = txtemail.Text;
string qq = txtqq.Text;
string room = txtroom.Text;
//构建存储过程的输入参数
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@name",name),
new SqlParameter("@sex", sex),
new SqlParameter("@college", college),
new SqlParameter("@id", id),
new SqlParameter("@grade", grade),
new SqlParameter("@phone", phone),
new SqlParameter("@email", email),
new SqlParameter("@qq", qq),
new SqlParameter("@room", room)
};
using(var conn = new SqlConnection(this.str))
{
conn.Open();//打开数据库；
string sql = "update Students set name = @name, sex = @sex, college = @college, id = @id, grade = @grade, phone = @phone, email = @email, qq = @qq, room = @room where id = @id";
SqlCommand com = new SqlCommand(sql, conn);//执行数据库操作指令
com.Parameters.AddRange(para);//将参数和命令对象的参数集合绑定
int result = (int)com.ExecuteNonQuery();//查询返回的第一行第一列
if(result > 0)
{
MessageBox.Show("修改成功！");
this.Form1_Load_1(null, null);//修改完数据后，重新刷新属性Form1窗口，以查看变化的内容
conn.Close();//关闭数据库
}
}
//SqlDataAdapter sda = new SqlDataAdapter();
}
/// <summary>
/// 刷新DataGridView里的数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form1_Load_1(object sender, EventArgs e)
{
using (var conn = new SqlConnection(this.str))//定义一个数据库连接实例
{
conn.Open();//打开数据库
string sql = "select * from Students";//定义一个数据库操作指令
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);//定义数据库适配器
DataSet ds = new DataSet();//定义数据集合
sda.Fill(ds);//填充数据集合
dataGridView1.DataSource = ds.Tables[0];
conn.Close();//关闭数据库
}
}
private void DataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
}
/// <summary>
/// 选中DataGridView的行，这一行的数据返回到对应的文本框
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void DataGridView1_CellClick_1(object sender, DataGridViewCellEventArgs e)
{
//SelectedRows[0] 获取用户选定行的集合(选中的第一行就是0，一次类推)
//Cells["name"] 获取用于填充行的单元格集合(就是列) .Value就是它的值,最后ToString转字符串
txtname.Text = dataGridView1.SelectedRows[0].Cells["name"].Value.ToString();
txtsex.Text = dataGridView1.SelectedRows[0].Cells["sex"].Value.ToString();
txtcollege.Text = dataGridView1.SelectedRows[0].Cells["college"].Value.ToString();
txtid.Text = dataGridView1.SelectedRows[0].Cells["id"].Value.ToString();
txtgrade.Text = dataGridView1.SelectedRows[0].Cells["grade"].Value.ToString();
txtphone.Text = dataGridView1.SelectedRows[0].Cells["phone"].Value.ToString();
txtemail.Text = dataGridView1.SelectedRows[0].Cells["email"].Value.ToString();
txtqq.Text = dataGridView1.SelectedRows[0].Cells["qq"].Value.ToString();
txtroom.Text = dataGridView1.SelectedRows[0].Cells["room"].Value.ToString();
}
/// <summary>
/// 删除某个学生的所有数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnDelete_Click(object sender, EventArgs e)
{
using(var conn = new SqlConnection(this.str))//创建一个数据库连接实例
{
conn.Open();//连接数据库
string sql = string.Format("delete from Students where id = '{0}'", txtid.Text);//往数据库操作指令中传值
//如果传的值不是很多的话，就用这种方法；如果有很多就用SqlParameter[]
SqlCommand com = new SqlCommand(sql, conn);//执行数据库删除指令
int result = (int)com.ExecuteNonQuery();//返回结果，result > 0则为修改成功
if(result > 0)
{
MessageBox.Show("删除成功！");
this.Form1_Load_1(null, null);//刷新数据
conn.Close();//关闭数据库
}
}
}
/// <summary>
/// 对文本框进行清空处理，方便重新输入下一个学生信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnClear_Click(object sender, EventArgs e)
{
txtname.Text = null;
txtsex.Text = null;
txtcollege.Text = null;
txtid.Text = null;
txtgrade.Text = null;
txtphone.Text = null;
txtemail.Text = null;
txtqq.Text = null;
txtroom.Text = null;
}
}
}Students表
加载全部内容