using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data.SqlClient;namespace DataGridView增删改查完整版.DataConnection{ public class DataConnection { private static string connstr = "server=.; database=mydb; user=sa; pwd=ray; "; public static SqlConnection Conn { get { return new SqlConnection(connstr); } } }} using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace DataGridView增删改查完整版.Model{ public class Info { private string _code; public string Code { get { return _code; } set { _code = value; } } private string _name; public string Name { get { return _name; } set { _name = value; } } private bool _gender; public bool Gender { get { return _gender; } set { _gender = value; } } private string _nation; public string Nation { get { return _nation; } set { _nation = value; } } private DateTime _birthday; public DateTime Birthday { get { return _birthday; } set { _birthday = value; } } //属性扩展 public string GenderName { get { return _gender ? "男" : "女"; } } public int Age { get { return DateTime.Now.Year - _birthday.Year; } } public string NationName { get { DataOperation.NationData nd = new DataOperation.NationData(); return nd.NationName(Nation); } } }}using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace DataGridView增删改查完整版.Model{ public class Nation { private string _code; public string Code { get { return _code; } set { _code = value; } } private string _name; public string Name { get { return _name; } set { _name = value; } } }} /* *InfoData */using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data.SqlClient;namespace DataGridView增删改查完整版.DataOperation{ public class InfoData { private SqlConnection _conn; private SqlCommand _cmd; private SqlDataReader _dr; public InfoData() { _conn = DataConnection.DataConnection.Conn; _cmd = _conn.CreateCommand(); } /// /// 查询Info表全部数据 /// /// Select() { _cmd.CommandText = "select *from Info"; _conn.Open(); _dr = _cmd.ExecuteReader(); List list = new List (); if (_dr.HasRows) { while (_dr.Read()) { Model.Info data = new Model.Info(); data.Code = _dr["Code"].ToString(); data.Name = _dr["Name"].ToString(); data.Gender = Convert.ToBoolean(_dr["Sex"]); data.Nation = _dr["Nation"].ToString(); data.Birthday = Convert.ToDateTime(_dr["Birthday"]); list.Add(data); } } _conn.Close(); return list; } /// /// 查询Info表指定数据 /// /// /// /// 多条件查询Info表中数据 /// /// 姓名 /// 性别 /// 年龄 /// 民族 /// List public List Select(string name,string gender,string nation) { string tjName = " 1=1 ", tjGender = " 1=1 ", tjNation = " 1=1 "; //输入姓名 if (name != "") { tjName = " Name like @name "; } //输入性别 if (gender != "") { tjGender = " Sex=@gender "; } bool genderBool; if (gender == "男") { genderBool = true; } else { genderBool = false; } //选择民族 string nationcode = ""; if (nation != "") { tjNation = " Nation=@nation "; DataOperation.NationData nda=new NationData(); nationcode = nda.NationCode(nation); } _cmd.CommandText = "select *from Info where" + tjName + "and" + tjGender+ "and" + tjNation; _cmd.Parameters.Clear(); _cmd.Parameters.AddWithValue("@name", "%" + name + "%"); _cmd.Parameters.AddWithValue("@gender", genderBool); _cmd.Parameters.AddWithValue("@nation", nationcode); _conn.Open(); _dr = _cmd.ExecuteReader(); List list = new List (); if (_dr.HasRows) { while (_dr.Read()) { Model.Info idata = new Model.Info(); idata.Code = _dr["Code"].ToString(); idata.Name = _dr["Name"].ToString(); idata.Gender = Convert.ToBoolean(_dr["Sex"]); idata.Nation = _dr["Nation"].ToString(); idata.Birthday = Convert.ToDateTime(_dr["Birthday"]); list.Add(idata); } } _conn.Close(); return list; } /// /// 向Info表中添加数据 /// /// /// /// /// /// public void Insert(string code,string name,string gender,string nation,string birthday) { DataOperation.NationData nda = new NationData(); bool genderBool; if (gender == "男") { genderBool = true; } else { genderBool = false; } _cmd.CommandText = "insert into Info values(@code,@name,@gender,@nation,@birthday)"; _cmd.Parameters.Clear(); _cmd.Parameters.AddWithValue("@code",code); _cmd.Parameters.AddWithValue("@name",name); _cmd.Parameters.AddWithValue("@gender", genderBool); _cmd.Parameters.AddWithValue("@nation",nda.NationCode(nation)); _cmd.Parameters.AddWithValue("@birthday",birthday); _conn.Open(); _cmd.ExecuteNonQuery(); _conn.Close(); } /// /// 修改Info表中数据 /// /// /// /// /// /// public void Update(string code, string name, bool gender, string nation, string birthday) { _cmd.CommandText = "update Info set Name=@name,Sex=@sex,Nation=@nation,Birthday=@birthday where Code=@code"; _cmd.Parameters.Clear(); _cmd.Parameters.AddWithValue("@code", code); _cmd.Parameters.AddWithValue("@name", name); _cmd.Parameters.AddWithValue("@sex", gender); _cmd.Parameters.AddWithValue("@nation", nation); _cmd.Parameters.AddWithValue("@birthday", birthday); _conn.Open(); _cmd.ExecuteNonQuery(); _conn.Close(); } public void Delete(string code) { _cmd.CommandText = "delete from Info where code=@code"; _cmd.Parameters.Clear(); _cmd.Parameters.AddWithValue("@code",code); _conn.Open(); _cmd.ExecuteNonQuery(); _conn.Close(); } }}/* * NationData */using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data.SqlClient;namespace DataGridView增删改查完整版.DataOperation{ public class NationData { private SqlConnection _conn; private SqlCommand _cmd; private SqlDataReader _dr; public NationData() { _conn = DataConnection.DataConnection.Conn; _cmd = _conn.CreateCommand(); } /// /// 民族代号转换民族名称 /// /// 代号 /// 名称 public string NationName(string code) { _cmd.CommandText = "select Name from Nation where Code=@code"; _cmd.Parameters.Clear(); _cmd.Parameters.AddWithValue("@code",code); _conn.Open(); _dr = _cmd.ExecuteReader(); if (_dr.HasRows) { _dr.Read(); return _dr[0].ToString(); } else { return null; } _conn.Close(); } /// /// 民族名称转换民族代号 /// /// 名称 /// 代号 public string NationCode(string name) { _cmd.CommandText = "select Code from Nation where Name=@name"; _cmd.Parameters.Clear(); _cmd.Parameters.AddWithValue("@name", name); _conn.Open(); _dr = _cmd.ExecuteReader(); if (_dr.HasRows) { _dr.Read(); return _dr[0].ToString(); } else { return null; } _conn.Close(); } /// /// 查询Nation表信息 /// /// Select() { List list = new List (); _cmd.CommandText = "select * from Nation"; _conn.Open(); _dr = _cmd.ExecuteReader(); if (_dr.HasRows) { while (_dr.Read()) { Model.Nation data = new Model.Nation(); data.Name = _dr["Name"].ToString(); list.Add(data); } } _conn.Close(); return list; } }} 主界面：
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;namespace DataGridView增删改查完整版{ public partial class Form1 : Form { public Form1() { InitializeComponent(); } //窗体加载执行事件 private void Form1_Load(object sender, EventArgs e) { DataOperation.InfoData ida = new DataOperation.InfoData(); dataGridView1.DataSource = ida.Select(); dataGridView1.ClearSelection(); DataOperation.NationData nda = new DataOperation.NationData(); cmbNation.DataSource = nda.Select(); cmbNation.DisplayMember = "Name"; cmbNation.ValueMember = "Code"; } //查询 private void btnSelect_Click(object sender, EventArgs e) { string name = txtName.Text; string gender; if (checkBox1.Checked) { gender = checkBox1.Text; if (checkBox2.Checked) { gender = ""; } } else if (checkBox2.Checked) { gender = checkBox2.Text; } else { gender = ""; } string nation = cmbNation.Text; if (cmbNation.Text == "请选择") { nation = ""; } DataOperation.InfoData data = new DataOperation.InfoData(); dataGridView1.DataSource = data.Select(name, gender, nation); dataGridView1.ClearSelection(); } //添加 private void btnAdd_Click(object sender, EventArgs e) { FormInsert f = FormInsert.NewInsert(); f.Show(); f.Focus(); } //删除 private void btnDel_Click(object sender, EventArgs e) { if (dataGridView1.SelectedRows.Count > 0) { MessageBoxButtons btn = MessageBoxButtons.YesNoCancel; if (MessageBox.Show("确定删除吗？", "删除数据", btn) == DialogResult.Yes) { Model.Info data = dataGridView1.SelectedRows[0].DataBoundItem as Model.Info; DataOperation.InfoData da = new DataOperation.InfoData(); da.Delete(data.Code); //刷新数据 dataGridView1.DataSource = da.Select(); dataGridView1.ClearSelection(); } } else { MessageBox.Show("没有选中任何项"); } } //修改 private void btnUpdate_Click(object sender, EventArgs e) { if (dataGridView1.SelectedRows.Count > 0) { Model.Info data = dataGridView1.SelectedRows[0].DataBoundItem as Model.Info; FormUpdate f = FormUpdate.NewUpdate(data.Code); f.Show(); f.Focus(); } else { MessageBox.Show("没有选中任何项"); } } //Timer刷新页面 public static int bs = 0; //定义成员变量用来接收刷新数据 private void timer1_Tick(object sender, EventArgs e) { if (bs == 1) { DataOperation.InfoData da = new DataOperation.InfoData(); dataGridView1.DataSource = da.Select(); bs = 0; dataGridView1.ClearSelection(); } } }}
添加界面：
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;namespace DataGridView增删改查完整版{ public partial class FormInsert : Form { private FormInsert() { InitializeComponent(); } private static FormInsert f = null; /// /// 返回对象的方法 /// ///
修改界面：
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;namespace DataGridView增删改查完整版{ public partial class FormUpdate : Form { //用来存储传递过来的主键值 private string Code = ""; private FormUpdate(string code) { InitializeComponent(); this.Code = code; } //用来存储该类的对象 private static FormUpdate u = null; /// /// 返回对象的方法 /// /// code ///
运行界面：
多条件查询：
添加：
删除：
修改：
