WinForm连接数据库增删改查
对象属性
using System; using System.Collections.Generic; using System.Text; namespace LeikuObject { public class TelObject { private int _Code; public int Code { get { return _Code; } set { _Code = value; } } private string _Name; public string Name { get { return _Name; } set { _Name = value; } } private string _Sex; public string Sex { get { return _Sex; } set { _Sex = value; } } private string _Phone; public string Phone { get { return _Phone; } set { _Phone = value; } } } }
函数
using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using LeikuObject; namespace LeikuFunction { public class Function { SqlConnection conn; SqlCommand cmd; string sr = "server=.;database=lianxi;uid=sa;pwd=123"; public Function() { conn = new SqlConnection(sr);//通过构造函数连接数据库 cmd = conn.CreateCommand();//创建一个与SqlConnection(表示一个连接数据库的链接)和SqlCommand(表示要对数据库进行操作的SQL语句)相关联的的对象 } /// <summary> /// 查询 /// </summary> /// <returns></returns> public List<TelObject> select() { List<TelObject> list = null; cmd.CommandText = "select * from tel";//存储SQL语句 conn.Open();//打开数据库连接 SqlDataReader dr = cmd.ExecuteReader();//SqlDataReader 提供一种从数据库读取行的只进流方式 ExecuteReader:将 CommandText 发送到 Connection 并生成一个 SqlDataReader if (dr.HasRows)//判断dr中有没有数据 { list = new List<TelObject>(); while (dr.Read())//dr中读取到的行 返回true 读取的行会先存在dr中 { TelObject t = new TelObject();//这句如果放在while外面，就会只造了一个TelObject对象 只会带着一个对象的值存入list集合中 t.Code=int.Parse(dr["code"].ToString()); t.Name = dr["name"].ToString(); t.Sex = dr["sex"].ToString(); t.Phone = dr["iph"].ToString(); list.Add(t); } } cmd.Dispose();//销毁cmd conn.Close();//关闭数据库连接 return list; } /// <summary> /// 删除 /// </summary> /// <param name="code"></param> public void delete(int code) { cmd.CommandText = "delete from tel where code=@code";//使用@占位符 可以避免注入攻击 cmd.Parameters.Clear(); cmd.Parameters.Add("@code",code);//给占位符赋值 conn.Open(); cmd.ExecuteNonQuery();//执行SQL语句，并返回受影响的行 cmd.Dispose(); conn.Close(); } /// <summary> /// 单独查询 /// </summary> /// <param name="code"></param> /// <returns></returns> public TelObject select(int code) { TelObject t = null; cmd.CommandText = "select *from tel where code=@code"; cmd.Parameters.Clear(); cmd.Parameters.Add("@code", code); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { t = new TelObject(); if (dr.Read())//如果不加这一步，直接赋值，会报错？ { t.Code = int.Parse(dr["code"].ToString()); t.Name = dr["name"].ToString(); t.Sex = dr["sex"].ToString(); t.Phone = dr["iph"].ToString(); } } cmd.Dispose(); conn.Close(); return t; } /// <summary> /// 增加 /// </summary> /// <param name="t"></param> public void insert(TelObject t) { cmd.CommandText = "insert into tel values(@code,@name,@sex,@iph)"; cmd.Parameters.Clear(); cmd.Parameters.Add("@code", t.Code); cmd.Parameters.Add("@name", t.Name); cmd.Parameters.Add("@sex", t.Sex); cmd.Parameters.Add("@iph", t.Phone); conn.Open(); cmd.ExecuteNonQuery();//执行SQL语句，并返回受影响的行 cmd.Dispose(); conn.Close(); } /// <summary> /// 修改 /// </summary> /// <param name="t"></param> public void update(TelObject t) { cmd.CommandText = "update tel set name=@name,sex=@sex,iph=@iph where code=@code"; cmd.Parameters.Clear(); cmd.Parameters.Add("@code", t.Code); cmd.Parameters.Add("@name", t.Name); cmd.Parameters.Add("@sex", t.Sex); cmd.Parameters.Add("@iph", t.Phone); conn.Open(); cmd.ExecuteNonQuery();//执行SQL语句，并返回受影响的行 cmd.Dispose(); conn.Close(); } /// <summary> /// 模糊查询 /// </summary> /// <param name="name"></param> /// <param name="sex"></param> /// <returns></returns> public List<TelObject> select(string name,string sex) { List<TelObject> list = null; cmd.CommandText = "select * from tel where name like '%"+name+"%' and sex like '%"+sex+"%'"; //这个地方不能用占位符，不解，暂时用拼接字符串吧 conn.Open(); SqlDataReader dr = cmd.ExecuteReader();//SqlDataReader 提供一种从数据库读取行的只进流方式 ExecuteReader:将 CommandText 发送到 Connection 并生成一个 SqlDataReader if (dr.HasRows)//判断dr中有没有数据 { list = new List<TelObject>(); while (dr.Read())//dr中读取到的行 返回true 读取的行会先存在dr中 { TelObject t = new TelObject();//这句如果放在while外面，就会只造了一个TelObject对象 只会带着一个对象的值存入list集合中 t.Code = int.Parse(dr["code"].ToString()); t.Name = dr["name"].ToString(); t.Sex = dr["sex"].ToString(); t.Phone = dr["iph"].ToString(); list.Add(t); } } cmd.Dispose();//销毁cmd conn.Close();//关闭数据库连接 return list; } } }
From1
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using LeikuObject; using LeikuFunction; namespace WindowsFormsApplication3 { public partial class Form1 : Form//主窗体 { public Form1() { InitializeComponent(); } public static int time = 0;//定义静态成员 public void chushiselect(List<TelObject> list)//显示查询结果的函数 { listView1.Columns.Clear();//清空列名，为了防止连续重复调用函数，重复插入列名 listView1.Columns.Add("编号");//添加列名，第一列索引是0 Columns[0] 第二项Columns[1] listView1.Columns.Add("姓名"); listView1.Columns.Add("性别"); listView1.Columns.Add("电话"); listView1.Items.Clear();//清空行，为了防止连续重复调用函数，重复插入行 //TelObject t = new TelObject(); if (list!=null) { for (int i = 0; i < list.Count; i++) { listView1.Items.Add(list[i].Code.ToString());//添加行，行的第一列数据 //第一行添加第一列数据时不能这么写listView1.Items[i].SubItems.Add(list[i].Code.ToString()) //添加之前第一列是空的 Items[i].SubItems 空集合里索引0里的SubItems报错 因为index为null listView1.Items[i].SubItems.Add(list[i].Name);//这一行的，第二列数据，也就是Items这个集合里索引是0的数据 listView1.Items[i].SubItems.Add(list[i].Sex); listView1.Items[i].SubItems.Add(list[i].Phone); } } } private void btselect_Click(object sender, EventArgs e)//查询按钮 { time = 1; } private void btdelete_Click(object sender, EventArgs e)//删除按钮 { //MessageBox.Show(listView1.Items[0].SubItems[1].Text); 可以获取到这一行的第二列数据 //MessageBox.Show(listView1.SelectedItems[0].SubItems[1].Text); 可以获取到选中行的第二列数据 if (listView1.SelectedItems.Count>0)//判读是否选中了一行 { int code = int.Parse(listView1.SelectedItems[0].Text);//获取选中行的第一列数据 也可以写成listView1.SelectedItems[0].SubItems[0].Text new Function().delete(code); time = 1; } } private void btinsert_Click(object sender, EventArgs e)//增加按钮 { Form2 f = new Form2(); f.Show(); } private void timer1_Tick(object sender, EventArgs e)//时钟组件，一直在后台等待，当time值有变化，就执行操作 { if (time==0) { } else if (time==1) { List<TelObject> list = new Function().select(); chushiselect(list); } time = 0; } private void btupdate_Click(object sender, EventArgs e)//修改按钮 { if (listView1.SelectedItems.Count > 0) { int code = int.Parse(listView1.SelectedItems[0].Text); Form3 f = new Form3(code);//通过构造函数传值，把code传给Form3 f.Show(); } } private void btpreselect_Click(object sender, EventArgs e)//条件按钮 { if (txtmainname.Text.Length>0||txtmainsex.Text.Length>0) { string name = txtmainname.Text; string sex = txtmainsex.Text; List<TelObject> list = new Function().select(name, sex); chushiselect(list); } } } }
Form2
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using LeikuFunction; using LeikuObject; namespace WindowsFormsApplication3 { public partial class Form2 : Form//增加对话框 { public Form2() { InitializeComponent(); } private void btzengjia_Click(object sender, EventArgs e) { //判读文本框里是不是有内容 if (txtcode.Text.Length>0&&txtname.Text.Length>0&&txtsex.Text.Length>0&&txtphone.Text.Length>0) { TelObject t = new TelObject(); t.Code = int.Parse(txtcode.Text); t.Name = txtname.Text; t.Sex = txtsex.Text; t.Phone = txtphone.Text; new Function().insert(t); Close();//增加完信息关闭增加对话框 Form1.time = 1;//调用Form1中的静态成员，修改time值，为了重新查询数据库，把增加的信息显示出来 } else { MessageBox.Show("信息有误"); } } } }
Form3
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using LeikuObject; using LeikuFunction; namespace WindowsFormsApplication3 { public partial class Form3 : Form { private int Code; public Form3(int code) { InitializeComponent(); Code = code; } private void btxiugai_Click(object sender, EventArgs e)//修改按钮 { //判读修改后的文本中是否有内容 if (txtxname.Text.Length>0&&txtxsex.Text.Length>0&&txtxphone.Text.Length>0) { TelObject t = new TelObject(); t.Code = int.Parse(txtxcode.Text); t.Name = txtxname.Text; t.Sex = txtxsex.Text; t.Phone = txtxphone.Text; new Function().update(t); Close(); Form1.time = 1; } } private void Form3_Load(object sender, EventArgs e)//Form3窗体打开时就会显示的值 { TelObject t = new Function().select(Code); txtxcode.Text = t.Code.ToString(); txtxname.Text = t.Name; txtxsex.Text = t.Sex; txtxphone.Text = t.Phone; } } }
