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;
        }
    }
}