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





    }
}
View Code

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

数据访问类:

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] ? "" : "") + "\t");
                        else if (dr[n] is DateTime)
                            Console.Write((((DateTime)dr[n]).ToShortDateString()) + "\t");
                        else
                            Console.Write(dr[n] + "\t");
                        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;
        }
    }
}
View Code

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

注:

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("编号" + "\t" + "用户名" + "\t" + "密码" + "\t" + "昵称" + "\t" + "性别" + "\t" + "出生日期" + "\t" + "民族" + "\t" + "班级" + "\n");
            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();
            

        }
    }
}
View Code

 


 

 

转载于:https://www.cnblogs.com/shadow-wolf/p/6117610.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值