ADO.net 实体类 、数据访问类

程序分三层：界面层、业务逻辑层、数据访问层

比较规范的写程序方法，要把业务逻辑层和数据访问层分开，此时需要创建实体类和数据访问类

实体类：

例

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 增删改查.app_ado
{
    public class users
    {
        private int _Ids;

        /// <summary>
        /// ids
        /// </summary>
        public int Ids
        {
            get { return _Ids; }
            set { _Ids = value; }
        }
        private string _UserName;

        /// <summary>
        /// 用户名
        /// </summary>
        public string UserName
        {
            get { return _UserName; }
            set { _UserName = value; }
        }
        private string _PassWord;

        /// <summary>
        /// 密码
        /// </summary>
        public string PassWord
        {
            get { return _PassWord; }
            set { _PassWord = value; }
        }
        private string _NickName;

        /// <summary>
        /// 昵称
        /// </summary>
        public string NickName
        {
            get { return _NickName; }
            set { _NickName = value; }
        }
        private bool _Sex;

        /// <summary>
        /// 性别
        /// </summary>
        public bool Sex
        {
            get { return _Sex; }
            set { _Sex = value; }
        }
        private DateTime _Birthday;

        /// <summary>
        /// 生日
        /// </summary>
        public DateTime Birthday
        {
            get { return _Birthday; }
            set { _Birthday = value; }
        }
        private string _Nation;

        /// <summary>
        /// 民族
        /// </summary>
        public string Nation
        {
            get { return _Nation; }
            set { _Nation = value; }
        }
        private string _Class;

        /// <summary>
        /// 班级
        /// </summary>
        public string Class
        {
            get { return _Class; }
            set { _Class = value; }
        }





    }
}

创建一个类，把整个数据库表单的数据都封装一下

数据访问类：

例

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

namespace 增删改查.app_ado
{
    class userdata
    {
         SqlConnection conn = null;
        SqlCommand cmd = null;

        public userdata()
        {
            conn = new SqlConnection("server=.;database=Data0928;user=sa;pwd=123");
            cmd = conn.CreateCommand();
        }
        /// <summary>
        /// 查询所有信息
        /// </summary>
        public void select()
        {            
            cmd.CommandText = "select ids,username,password,nickname,sex,birthday,nationname,classname from users join nation on users.nation=nation.nationcode join class on users.class=class.classcode";
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {                    
                    int n = 0;
                    while (n < dr.FieldCount)
                    {
                        if (dr[n] is Boolean)
                            Console.Write(((Boolean)dr[n] ? "男" : "女") + "	");
                        else if (dr[n] is DateTime)
                            Console.Write((((DateTime)dr[n]).ToShortDateString()) + "	");
                        else
                            Console.Write(dr[n] + "	");
                        n++;
                    }
                    Console.WriteLine();
                }
            }
            conn.Close();
        }
        /// <summary>
        /// 添加信息
        /// </summary>
        /// <param name="u"></param>
        /// <returns></returns>
        public bool Insert(users u)
        {
            bool ok = false;
            int count = 0;

            cmd.CommandText = "insert into users values(@a,@b,@c,@d,@e,@f,@g)";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a", u.UserName);
            cmd.Parameters.AddWithValue("@b", u.PassWord);
            cmd.Parameters.AddWithValue("@c", u.NickName);
            cmd.Parameters.AddWithValue("@d", u.Sex);
            cmd.Parameters.AddWithValue("@e", u.Birthday);
            cmd.Parameters.AddWithValue("@f", u.Nation);
            cmd.Parameters.AddWithValue("@g", u.Class);
            try
            {
                conn.Open();
                count = cmd.ExecuteNonQuery();
            }
            catch { ok = false; }
            finally
            {
                conn.Close();
            }

            if (count > 0)
                ok = true;

            return ok;
        }
        /// <summary>
        /// 根据用户名查询
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public List<users> Select(string username)
        {
            List<users> list = new List<users>();
            cmd.CommandText = "select *from Users where UserName = @a";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@a", username);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
                while (dr.Read())
                {
                    users u = new users();
                    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();
                    u.Class = dr["Class"].ToString();

                    list.Add(u);
                }
            conn.Close();
            return list;
        }
        /// <summary>
        /// 根据列名查询信息
        /// </summary>
        /// <param name="lname"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public List<users> Select(string lname,string username)
        {
            List<users> list = new List<users>();
            cmd.CommandText = "select *from Users where "+lname+" = @a";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a", username);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
                while (dr.Read())
                {
                    users u = new users();
                    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();
                    u.Class = dr["Class"].ToString();

                    list.Add(u);
                }
            conn.Close();
            return list;
        }
        /// <summary>
        /// 判断是否有此用户名
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public bool select(string username)
        {
            bool has = false;
            cmd.CommandText = "select *from Users where UserName = @a";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@a", username);

            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
                has = true;

            conn.Close();
            return has;
        }
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="uname"></param>
        public bool Delete(string uname)
        {
            bool ok = false;
            int count = 0;
            cmd.CommandText = "delete from Users where UserName = @a";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@a", uname);
            try
            {
                conn.Open();
                count = cmd.ExecuteNonQuery();
            }
            catch { ok = false; }
            finally
            {
                conn.Close();
            }

            if (count > 0)
                ok = true;

            return ok;
        }
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="uname"></param>
        /// <returns></returns>
        public bool Update(string uname,string uname1,string uname2)
        {
            bool ok = false;
            int count = 0;
            cmd.CommandText = "update Users set "+uname1+"=@b where UserName = @a";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@a", uname);
            cmd.Parameters.Add("@b", uname2);
            try
            {
                conn.Open();
                count = cmd.ExecuteNonQuery();
            }
            catch { ok = false; }
            finally
            {
                conn.Close();
            }

            if (count > 0)
                ok = true;

            return ok;
        }
    }
}

相当于把所有需要从数据库取数据进行操作的部分，统一放入此类中，使用时可以调用，这样可以简化主程序，并且看起来调理更加清晰。

注：

1、一般约定，在项目里新建一个叫做App_xx的文件夹，将实体类和数据访问类统一放在其中

2、一般实体类使用想要进行操作的数据库中的表名来命名，数据库访问类用此表的表名后面加上Data来命名

3、数据访问类开头格式：

class userdata
    {
        SqlConnection conn = null;
        SqlCommand cmd = null;
 
        public userdata()
        {
            conn = new SqlConnection("server=.;database=Data0928;user=sa;pwd=123");
            cmd = conn.CreateCommand();
        }

4、匿名方法

例

List<Users> ulist = new UsersData().Select();

直接使用UserData类中的Select方法（查询表中所有数据），并赋值给叫做ulist的泛型集合，从而比较简便的获取到所有数据，节省代码

有些方法也可以不用赋值直接进行调用

 if (new userdata().select(uname))
综合练习

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using 增删改查.app_ado;

namespace 增删改查
{
    class Program
    {
        static void Main(string[] args)
        {
            #region//输出列表信息
            Console.WriteLine("========================列表信息============================");
            Console.Write("编号" + "	" + "用户名" + "	" + "密码" + "	" + "昵称" + "	" + "性别" + "	" + "出生日期" + "	" + "民族" + "	" + "班级" + "
");
            new userdata().select();
            #endregion
            Console.WriteLine("1.添加");
            Console.WriteLine("2.删除");
            Console.WriteLine("3.修改");
            Console.WriteLine("4.查询");
            Console.WriteLine("5.退出");
            for (; ; )
            {
                Console.Write("请选择您要进行的操作编号：");
                int bian = int.Parse(Console.ReadLine());
                //增加信息
                if (bian == 1)
                {
                    users user = new users();
                    Console.Write("请输入用户名：");
                    user.UserName = Console.ReadLine();
                    Console.Write("请输入密码：");
                    user.PassWord = Console.ReadLine();
                    Console.Write("请输入昵称：");
                    user.NickName = Console.ReadLine();
                    Console.Write("请输入性别：");
                    user.Sex = Convert.ToBoolean(Console.ReadLine());
                    Console.Write("请输入生日：");
                    user.Birthday = Convert.ToDateTime(Console.ReadLine());
                    Console.Write("请输入民族：");
                    user.Nation = Console.ReadLine();
                    Console.Write("请输入班级：");
                    user.Class = Console.ReadLine();
                    userdata ud = new userdata();
                    bool isok = ud.Insert(user);
                    if (isok)
                        Console.WriteLine("添加成功！");
                    else
                        Console.WriteLine("添加失败！");
                }
                    //删除信息
                else if (bian == 2)
                {
                    Console.Write("请输入要删除的用户名：");
                    string uname = Console.ReadLine();

                    if (new userdata().select(uname))
                    {
                        List<users> ulist = new userdata().Select(uname);
                        foreach (users uuu in ulist)
                        {
                            Console.WriteLine(uuu.Ids + "   " + uuu.UserName + "   " + uuu.PassWord + "   " + uuu.NickName + "   " + uuu.Sex + "   " + uuu.Birthday + "   " + uuu.Nation + "   " + uuu.Class);
                        }
                        Console.WriteLine("以上为此用户信息，是否要删除？(Y/N)");
                        string yn = Console.ReadLine();
                        if (yn.ToUpper() == "Y")
                        {
                            new userdata().Delete(uname);
                            Console.WriteLine("删除成功！");
                        }
                    }
                    else
                        Console.WriteLine("未找到此用户名！");
                }
                    //修改信息
                else if (bian == 3)
                {
                    Console.Write("请输入要修改的用户名：");
                    string uname = Console.ReadLine();

                    if (new userdata().select(uname))
                    {
                        Console.Write("请输入要修改的列名：");
                        string uname1 = Console.ReadLine();
                        Console.Write("请输入要修改的内容：");
                        string uname2 = Console.ReadLine();
                        List<users> ulist = new userdata().Select(uname);
                        foreach (users uuu in ulist)
                        {
                            Console.WriteLine(uuu.Ids + "   " + uuu.UserName + "   " + uuu.PassWord + "   " + uuu.NickName + "   " +((bool)uuu.Sex?"男":"女")+ "   " + ((DateTime)uuu.Birthday).ToShortDateString() + "   " + uuu.Nation + "   " + uuu.Class);
                        }
                        Console.WriteLine("以上为此用户信息，是否要修改？(Y/N)");
                        string yn = Console.ReadLine();
                        if (yn.ToUpper() == "Y")
                        {
                            new userdata().Update(uname, uname1, uname2);
                            Console.WriteLine("修改成功！");
                        }
                    }
                    else
                        Console.WriteLine("未找到此用户名！");

                }
                else if (bian == 4) //也可以调用 public List<users> Select(string lname,string username) 通过 输入列名 和此列名下的内容 来进行查询
                {
                    Console.Write("请输入要查询的用户名：");
                    string uname = Console.ReadLine();
                    if (new userdata().select(uname))
                    {                        
                        List<users> ulist = new userdata().Select(uname);
                        foreach (users uuu in ulist)
                        {
                            Console.WriteLine(uuu.Ids + "   " + uuu.UserName + "   " + uuu.PassWord + "   " + uuu.NickName + "   " + ((bool)uuu.Sex ? "男" : "女") + "   " + ((DateTime)uuu.Birthday).ToShortDateString() + "   " + uuu.Nation + "   " + uuu.Class);
                        }
                    }
                    else
                        Console.WriteLine("未找到此用户名！");
                }
                else if (bian == 5)
                    break;
                else
                    Console.WriteLine("请输入正确的操作编号！！");
            }


            Console.ReadLine();
            

        }
    }
}