winform窗体（六）——DataGridView控件及通过此控件中实现增删改查

DataGridView：显示数据表，通过此控件中可以实现连接数据库，实现数据的增删改查

一、后台数据绑定：
    List<xxx> list = new List<xxx>();
    dataGridView1.DataSource = list;
      //设置不自动生成列，此属性在属性面板中没有
      dataGridView1.AutoGenerateColumns = false;
      //取消加载默认选中第一行
      dataGridView1.ClearSelection();

二、前台：
      小三角箭头，取消可编辑，添加，删除功能；
      Columns集合属性中，添加列
      HeaderText中设置显示的文本
      DataPropertyName设置绑定的字段名或数据库列名
      SelectionMode --设置选择方式，FullRowSelect只能选中行
      MultiSelect --是否可以选中多行内容

三、取值：
      取出选中的单元格的值：
      dataGridView1.SelectedCells中放着全部选中的单元格
      if(dataGridView1.SelectedCells.Count > 0)
      {
              MessageBox.Show(dataGridView1.SelectedCells[0].Value.ToString());
      }
      取出选中的行内容：
      if(dataGridView1.SelectedRows.Count > 0)
      {
             MessageBox.Show(dataGridView1.SelectedRows[0].Cells[0].ToString());
      }
      获取用于填充行绑定的对象：
      //行对象使用属性：DataBoundItem
      student sss = dataGridView1.SelectedRows[0].DataBoundItem as student;

四、删除加确认
      MessageBoxButtons btn = MessageBoxButtons.YesNoCancel;
      if (MessageBox.Show("确定要删除么？", "删除数据", btn) == DialogResult.Yes)
      {
      }

五、多条件查询

如果用户什么都不输入，或者文本框是空，这时候是查询所有

//做两个恒成立的条件
string tj1 = " 1=1 ";
string tj2 = " 1=1 ";

//根据用户输入来改变条件
//如果用户输入了姓名
            if (name != "")
            {
                tj1 = " Name like @name ";
            }

//如果用户输入了民族
            if (nation != "")
            {
                tj2 = " Nation = @nation ";
            }

//拼接成完整条件
string ztj = " where "+tj1+" and "+tj2;

六、例子

通过控件DataGridView，连接数据库（表Student和表Sclass），实现对Student表的增删改查

namespace WindowsFormsApplication2
{
public class DBConnect
{
private static string connstring = "server=.;database=xuesheng;user=sa;pwd=123";
public static SqlConnection Conn
{
get
{
return new SqlConnection(connstring);
}
}
}
}

DBConnect.cs

namespace WindowsFormsApplication2
{
public class Student
{
private string sno;

public string Sno
{
get { return sno; }
set { sno = value; }
}
private string sname;

public string Sname
{
get { return sname; }
set { sname = value; }
}
private string ssex;

public string Ssex
{
get { return ssex; }
set { ssex = value; }
}
private DateTime sbirthday;

public DateTime Sbirthday
{
get { return sbirthday; }
set { sbirthday = value; }
}
private string sclass;

public string Sclass
{
get { return sclass; }
set { sclass = value; }
}
private string SclassName
{
get
{
SclassDA da = new SclassDA();
return da.SclassName(this.sclass);
}
}
}
}

Student.cs

namespace WindowsFormsApplication2
{
public class Sclass
{
private string cno;

public string Cno
{
get { return cno; }
set { cno = value; }
}

private string name;

public string Name
{
get { return name; }
set { name = value; }
}
}
}

Sclass.cs

namespace WindowsFormsApplication2
{
public class StudentDA
{
private SqlConnection _conn;
private SqlCommand _cmd;
private SqlDataReader _dr;
public StudentDA()
{
_conn = DBConnect.Conn;
_cmd = _conn.CreateCommand();
}
//查询
public List<Student> Select()
{
List<Student> list = new List<Student>();
_cmd.CommandText = "select * from Student";
_conn.Open();

_dr = _cmd.ExecuteReader();

if (_dr.HasRows)
{
while (_dr.Read())
{
Student data = new Student();
data.Sno = _dr[0].ToString();
data.Sname= _dr[1].ToString();
data.Ssex = _dr[2].ToString();
data.Sbirthday =Convert.ToDateTime( _dr[3]);
data.Sclass = _dr[4].ToString();

list.Add(data);
}
}
_conn.Close();

return list;
}
public Student Select(string sno)
{

_cmd.CommandText = "select * from Student where Sno=@sno";
_cmd.Parameters.Clear();
_cmd.Parameters.AddWithValue("@sno",sno);
_conn.Open();

_dr = _cmd.ExecuteReader();
Student data = new Student();
if (_dr.HasRows)
{
_dr.Read();
data.Sno = _dr[0].ToString();
data.Sname = _dr[1].ToString();
data.Ssex = _dr[2].ToString();
data.Sbirthday = Convert.ToDateTime(_dr[3]);
data.Sclass = _dr[4].ToString();
}
_conn.Close();

return data;
}
//删除
public void Delete(string sno)
{
_cmd.CommandText = "delete from Student where Sno=@sno";
_cmd.Parameters.Clear();
_cmd.Parameters.AddWithValue("@sno",sno);
_conn.Open();
_cmd.ExecuteNonQuery();
_conn.Close();
}
//多条件查询
public List<Student> Select(string sname,string sclass)
{
//做两个恒等条件
string tj1 = " 1=1 ";
string tj2 = " 1=1 ";
//根据用户输入改变条件
//用户输入了姓名
if(sname !="")
{
tj1 = " Sname like @sname ";
}
//用户输入了班级
if(sclass !="")
{
tj2 = " Class = @sclass ";
}
//拼接成完整的条件
string tj = " where " + tj1 + " and " + tj2;
List<Student> list = new List<Student>();
_cmd.CommandText = "select * from Student"+tj;
_cmd.Parameters.Clear();
_cmd.Parameters.AddWithValue("@sname","%"+sname+"%");
_cmd.Parameters.AddWithValue("@sclass",sclass);
_conn.Open();

_dr = _cmd.ExecuteReader();

if (_dr.HasRows)
{
while (_dr.Read())
{
Student data = new Student();
data.Sno = _dr[0].ToString();
data.Sname = _dr[1].ToString();
data.Ssex = _dr[2].ToString();
data.Sbirthday = Convert.ToDateTime(_dr[3]);
data.Sclass = _dr[4].ToString();

list.Add(data);
}
}
_conn.Close();

return list;
}
//修改
public void Update(string sno, string sname, string ssex, DateTime sbirthday,string sclass)
{
_cmd.CommandText = "update Student set Sname=@sname,Ssex=@ssex,Sbirthday=@sbirthday,Class=@sclass where Sno = @sno";
_cmd.Parameters.Clear();
_cmd.Parameters.AddWithValue("@sno", sno);
_cmd.Parameters.AddWithValue("@sname", sname);
_cmd.Parameters.AddWithValue("@ssex", ssex);
_cmd.Parameters.AddWithValue("@sbirthday", sbirthday);
_cmd.Parameters.AddWithValue("@sclass", sclass);

_conn.Open();
_cmd.ExecuteNonQuery();
_conn.Close();
}
//添加
public bool Add(string sno, string sname,string ssex,DateTime sbirthday,string sclass)
{
_cmd.CommandText = "insert into Student values(@sno,@sname,@ssex,@sbirthday,@sclass)";
_cmd.Parameters.Clear();
_cmd.Parameters.AddWithValue("@sno", sno);
_cmd.Parameters.AddWithValue("@sname", sname);
_cmd.Parameters.AddWithValue("@ssex", ssex);
_cmd.Parameters.AddWithValue("@sbirthday", sbirthday);
_cmd.Parameters.AddWithValue("@sclass", sclass);

_conn.Open();
int n = _cmd.ExecuteNonQuery();
_conn.Close();

if (n > 0)
{
return true;
}
else
{
return false;
}
}

}
}

StudentDA.cs

namespace WindowsFormsApplication2
{
public class SclassDA
{
private SqlConnection _conn;
private SqlCommand _cmd;
private SqlDataReader _dr;
public SclassDA()
{
_conn = DBConnect.Conn;
_cmd = _conn.CreateCommand();
}
public List<Sclass> Select()
{
List<Sclass> list = new List<Sclass>();
_cmd.CommandText = "select * from Sclass";
_conn.Open();

_dr = _cmd.ExecuteReader();

if (_dr.HasRows)
{
while (_dr.Read())
{
Sclass data = new Sclass();
data.Cno = _dr[0].ToString();
data.Name= _dr[1].ToString();
list.Add(data);
}
}
_conn.Close();

return list;
}
public string SclassName(string cno)
{
string name = "95031";
_cmd.CommandText = "select Name from Sclass where Cno=@cno";
_cmd.Parameters.AddWithValue("@cno", cno);

_conn.Open();

_dr = _cmd.ExecuteReader();

if (_dr.HasRows)
{
_dr.Read();
name = _dr[0].ToString();
}

_conn.Close();
return name;
}
}
}

SclassDA.cs

namespace WindowsFormsApplication2
{
public partial class Form1 : Form
{
public static int bs = 0;
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
StudentDA da = new StudentDA();
//绑定数据源
dataGridView1.DataSource = da.Select();
//取消自动显示列
dataGridView1.AutoGenerateColumns = false;

//取消选中第一行
dataGridView1.ClearSelection();
//给下拉列表绑定值
SclassDA nda = new SclassDA();
cmbbanji.DataSource = nda.Select();
cmbbanji.DisplayMember = "Name";
cmbbanji.ValueMember = "Cno";
}

//删除
private void txtshan_Click(object sender, EventArgs e)
{
MessageBoxButtons btn = MessageBoxButtons.YesNoCancel;
if (MessageBox.Show("确定要删除么？", "删除数据", btn) == DialogResult.Yes)
{
//取出选中行里面绑定的对象
Student data = dataGridView1.SelectedRows[0].DataBoundItem as Student;

StudentDA da = new StudentDA();
da.Delete(data.Sno);
dataGridView1.DataSource = da.Select();
}
}

//修改
private void txtxiu_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count > 0)
{
//取出选中项的主键值
Student data = dataGridView1.SelectedRows[0].DataBoundItem as Student;
//打出修改窗体
XiuGai xg = XiuGai.NewXiuGai(data.Sno);
//显示窗体
xg.Show();
//xg.Owner = this;
//让修改窗体获得焦点
xg.Focus();
}
else
{
MessageBox.Show("没有选中任何项！");
}
}

//查询
private void txtcha_Click(object sender, EventArgs e)
{
//取数据
string sname = txtxing.Text;
string sclass = cmbbanji.SelectedValue.ToString();
//根据条件查询，结果交给datagirdview显示
StudentDA da = new StudentDA();
dataGridView1.DataSource = da.Select(sname,sclass);
dataGridView1.AutoGenerateColumns = false;
}

//时钟工具间隔执行的事件，刷新主窗体
private void timer1_Tick(object sender, EventArgs e)
{
if (bs == 1)
{
StudentDA da = new StudentDA();
dataGridView1.DataSource = da.Select();
bs = 0;
}
//if (Convert.ToInt32(this.Tag) == 1)
//{
// StudentDA da = new StudentDA();
// dataGridView1.DataSource = da.Select();
// this.Tag = 0;
//}

}

//添加
private void txttian_Click(object sender, EventArgs e)
{

//打出修改窗体
XiuGai xg = new XiuGai();
//显示窗体
xg.Show();
//xg.Owner = this;
//让修改窗体获得焦点
xg.Focus();
}
}
}

Form1.cs

namespace WindowsFormsApplication2
{
public partial class XiuGai : Form
{
//用来存储传递过来的主键值
private string Sno = "";
//用来存储该类的对象
private static XiuGai xg = null;
public XiuGai()
{
InitializeComponent();
}
public XiuGai(string sno)
{
InitializeComponent();
this.Sno = sno;
}

private void XiuGai_Load(object sender, EventArgs e)
{
if (Sno != "")
{
txtsno.ReadOnly = true;
//给下拉列表绑定值
SclassDA nda = new SclassDA();
cmbsclass.DataSource = nda.Select();
cmbsclass.DisplayMember = "Name";
cmbsclass.ValueMember = "Cno";
//对界面内容进行初始化
StudentDA da = new StudentDA();
Student data = da.Select(Sno);
txtsno.Text = data.Sno;
txtsname.Text = data.Sname;
bool sex = (data.Ssex == "男" ? true : false);
rdnan.Checked = sex;
rdnv.Checked = !sex;

txtsbirthday.Text = data.Sbirthday.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
SclassDA nda = new SclassDA();
cmbsclass.DataSource = nda.Select();
cmbsclass.DisplayMember = "Name";
cmbsclass.ValueMember = "Cno";
}

}
//返回对象的方法
public static XiuGai NewXiuGai(string sno)
{
if (xg == null || xg.IsDisposed)
{
xg = new XiuGai(sno);
}

return xg;
}

//修改
private void button1_Click(object sender, EventArgs e)
{
//获取数据
string _sno = txtsno.Text;
string _sname = txtsname.Text;
string _ssex=(rdnan.Checked==true?"男":"女");
DateTime _sbirthday = Convert.ToDateTime(txtsbirthday.Text);
string _sclass = cmbsclass.SelectedValue.ToString();

StudentDA ida = new StudentDA();
ida.Update(_sno, _sname, _ssex, _sbirthday, _sclass);

//this.Owner.Tag = 1;
//给Form1的成员变量bs赋值
Form1.bs = 1;
this.Close();
}

//查询
private void button2_Click(object sender, EventArgs e)
{
string _sno = txtsno.Text;
string _sname = txtsname.Text;
string _ssex = (rdnan.Checked == true ? "男" : "女");
DateTime _sbirthday = Convert.ToDateTime(txtsbirthday.Text);
string _sclass = cmbsclass.SelectedValue.ToString();

StudentDA ida = new StudentDA();
ida.Add(_sno, _sname, _ssex, _sbirthday, _sclass);

//this.Owner.Tag = 1;
Form1.bs = 1;
this.Close();
}
}
}

XiuGai.cs

效果显示区：

主窗体：