封装数据库之增删改查

_4_20封装数据库.App_Code { public class User { private int _Ids; public int Ids { get { return _Ids; } set { _Ids = value; } } private string _UserName; public string UserName { get { return _UserName; } set { _UserName = value; } } private string _PassWord; public string PassWord { get { return _PassWord; } set { _PassWord = value; } } private string _NickName; public string NickName { get { return _NickName; } set { _NickName = value; } } private bool _Sex; public bool Sex { get { return _Sex; } set { _Sex = value; } } private DateTime _Birthday; public DateTime Birthday { get { return _Birthday; } set { _Birthday = value; } } private string _Nation; public string Nation { get { return _Nation; } set { _Nation = value; } } } } 数据库成员变量

2、封装数据库操作

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace _4_20封装数据库.App_Code
{
    public class UserData
    {       //数据库引用
        SqlConnection conn = new SqlConnection("server=.;database=student;user=sa;pwd=123;");
        SqlCommand cmd = conn.CreateCommand();
        public List<User> select()
        {
            //定义集合为方法
            List<User> ulist = new List<User>();
            cmd.CommandText = "select*from User";
            conn.Open();
            SqlDataReader dr=cmd.ExecuteReader();
            //读取数据
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    //添加数据到集合
                    User u = new User();
                    u.Ids = Convert.ToInt32(dr["Ids"]);
                    u.UserName = dr["UserName"].ToString();
                    u.PassWord = dr["PassWord"].ToString();
                    u.NickName=dr["NickName"].ToString();
                    u.Sex=Convert.ToBoolean(dr["Sex"]) ;
                    u.Birthday = Convert.ToDateTime(dr["Birthday"]);
                    u.Nation=dr["Nation"].ToString();

                    ulist.Add(u);
                }
            }
            conn.Close();
            return ulist;
        }
    }
}

查询封装

3、调用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using _4_20封装数据库.App_Code;

namespace _4_20封装数据库
{
    class Program
    {
        static void Main(string[] args)
        {
            //实例化数据库类
            UserData ul=new UserData();
            //调用数据库类方法
            List<User> us=ul.select();
            //打印
            foreach (User a in us)
            {
                Console.WriteLine(a.Ids+"|"+a.UserName+"|"+a.PassWord+"|"+a.NickName+"|"+a.Sex+"|"+a.Birthday+"|"+a.Nation);
            }
        }
    }
}

调用

二、添加

2、封装数据库操作

 //添加方法
        public void insert(User a)
        {
            //添加数据
            cmd.CommandText = "insert into User values(@a,@b,@c,@d,@e,@f);";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a",a.UserName);
            cmd.Parameters.AddWithValue("@b",a.PassWord);
            cmd.Parameters.AddWithValue("@c",a.NickName);
            cmd.Parameters.AddWithValue("@d",a.Sex);
            cmd.Parameters.AddWithValue("@e",a.Birthday);
            cmd.Parameters.AddWithValue("@f",a.Nation);

            //数据库操作
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

添加

3、调用

User iu = new User();

            Console.Write("请输入用户名：");
            iu.UserName = Console.ReadLine(); 
            Console.Write("请输入密码：");
            iu.PassWord = Console.ReadLine(); 
            Console.Write("请输入昵称：");
            iu.NickName = Console.ReadLine(); 
            Console.Write("请输入性别：");
            iu.Sex =Convert.ToBoolean(Console.ReadLine()) ; 
            Console.Write("请输入生日：");
            iu.Birthday =Convert.ToDateTime(Console.ReadLine()) ; 
            Console.Write("请输入民族：");
            iu.Nation = Console.ReadLine();

            new UserData().insert(iu);
            Console.ReadLine();

调用 insert

三、删除/修改

2.1先查询

修改删除查询

public bool selectun(string a)
        {

            bool ts = false;
            cmd.CommandText = "select Ids from User where UserName=@a;";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a", a);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            //读取数据
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    ts = true;
                }
            }
            conn.Close();
            return ts;
        }

2.2删除

 public void delete(string a)
        {
            if (new UserData().selectun(a))
            {
                cmd.CommandText = "delete from User where UserName=@a;";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@a", a);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

删除

2.3修改

public void reset(string a)
        {
            if (new UserData().selectun(a))
            {
                User ue=new User();
                cmd.CommandText = "update User set PassWord=@b,NickName=@c,Sex=@d,Birthday=@e,Nation=@f where Ids=@a;";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@a", a);
                cmd.Parameters.AddWithValue("@b", ue.PassWord);
                cmd.Parameters.AddWithValue("@c", ue.NickName);
                cmd.Parameters.AddWithValue("@d", ue.Sex);
                cmd.Parameters.AddWithValue("@e", ue.Birthday);
                cmd.Parameters.AddWithValue("@f", ue.Nation);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

修改

3、应用

User ud = new User();
            Console.Write("请输入用户名：");
            ud.UserName = Console.ReadLine();

            new UserData().delete(ud.UserName);
            new UserData().reset(ud.UserName);

 

封装数据库之增删改查

标签：ram   isp   set   cut   com   stat   names   param   sed