你的位置:首页 > ASP.net教程

[ASP.net教程]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

效果显示区:

主窗体:

 

删除:

添加:

修改:

查询:

 

 

※数据区别显示

//遍历datagridview里面行的集合,取出每一个行

foreach (DataGridViewRow row in dataGridView1.Rows)

{

//将该行里面绑定的数据项取出

Info data = row.DataBoundItem as Info;

//判断是不是男女

if (data.Sex)
{
}
}