ado.net 属性扩展 综合练习

实现数据查询，添加，删除，修改各项功能

业务逻辑层：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using 实体类_数据访问类练习.App_Code;

namespace 实体类_数据访问类练习
{
    class Program
    {
        static void Main(string[] args)
        {
            for (; ; )
            {
                Console.WriteLine("请输入您要选择的操作：1、查询 2、添加 3、删除 4、修改 0、退出 ");
                string operate = Console.ReadLine();
                //查询
                if (operate == "")
                {
                    Console.WriteLine("");
                    Console.Write("请输入您要查询的用户名（查询所有人员信息请输入*，退出请输入0。）:");
                    string operate_select = Console.ReadLine();
                    if (operate_select == "*")
                    {
                        //完整查询操作，创建泛型数组引用UsersDate的Select方法
                        List<Users> list = new UsersData().Select();
                        //List<Users> list = new List<Users>();
                        //UsersData ud = new UsersData();
                        //list = ud.Select();

                        //遍历数组
                        Console.WriteLine("");
                        Console.WriteLine("============================数据信息==============================");
                        Console.WriteLine("序号\t用户名\t密码\t昵称\t性别\t民族\t班级\t生日");
                        foreach (Users uuu in list)
                        {
                            //Console.WriteLine(uuu.Code + "\t" + uuu.Name + "\t" + uuu.Password + "\t" + uuu.Nick + "\t" + (uuu.Sex ? "男" : "女") + "\t" + uuu.Nation + "\t" + uuu.Class + "\t" + Convert.ToDateTime(uuu.Birthday).ToShortDateString());
                            Console.WriteLine(uuu.Code + "\t" + uuu.Name + "\t" + uuu.Password + "\t" + uuu.Nick + "\t" + uuu.SexStr + "\t" + uuu.NationName + "\t" + uuu.ClassName + "\t" + uuu.BirthdayStr);
                            Console.WriteLine("");
                        }
                    }
                    else if (operate_select == "")
                    {
                        break;
                    }
                    else
                    {

                        //完整查询操作，创建泛型数组引用UsersDate的Select方法
                        List<Users> list = new UsersData().Select(operate_select);
                        //List<Users> list = new List<Users>();
                        //UsersData ud = new UsersData();
                        //list = ud.Select();

                        //遍历数组
                        Console.WriteLine("");
                        Console.WriteLine("============================数据信息==============================");
                        Console.WriteLine("序号\t用户名\t密码\t昵称\t性别\t民族\t班级\t生日");
                        foreach (Users uuu in list)
                        {
                            //Console.WriteLine(uuu.Code + "\t" + uuu.Name + "\t" + uuu.Password + "\t" + uuu.Nick + "\t" + (uuu.Sex ? "男" : "女") + "\t" + uuu.Nation + "\t" + uuu.Class + "\t" + Convert.ToDateTime(uuu.Birthday).ToShortDateString());
                            Console.WriteLine(uuu.Code + "\t" + uuu.Name + "\t" + uuu.Password + "\t" + uuu.Nick + "\t" + uuu.SexStr + "\t" + uuu.NationName + "\t" + uuu.ClassName + "\t" + uuu.BirthdayStr);//引用扩展属性

                            Console.WriteLine("");
                        }
                    }
                }

                //添加用户操作
                else if (operate == "")
                {
                    Console.WriteLine("");
                    Users user = new Users();
                    Console.Write("请输入要添加的用户名：");
                    user.Name = Console.ReadLine();
                    Console.Write("请输入要添加的密码：");
                    user.Password = Console.ReadLine();
                    Console.Write("请输入要添加的昵称：");
                    user.Nick = Console.ReadLine();
                    Console.Write("请输入要添加的性别：");
                    user.Sex = Convert.ToBoolean(Console.ReadLine());
                    Console.Write("请输入要添加的民族：");
                    user.Nation = Console.ReadLine();
                    Console.Write("请输入要添加的班级：");
                    user.Class = Console.ReadLine();
                    Console.Write("请输入要添加的生日：");
                    user.Birthday = Convert.ToDateTime(Console.ReadLine());
                    if (new UsersData().Insert(user))//数据访问类中insert方法的返回值
                    {
                        Console.WriteLine("添加成功！");
                    }
                    else
                    {
                        Console.WriteLine("添加失败！");

                    }
                    Console.WriteLine("");

                }

                //删除用户操作
                else if (operate == "")
                {
                    Console.WriteLine("");
                    Console.Write("请输入你要删除的用户名：");
                    string operate_delete = Console.ReadLine();//用户操作
                    //判断是否插寻到此用户信息，返回TRUE表示有此用户信息
                    if (new UsersData().select(operate_delete))
                    {
                        Console.WriteLine("查询到此用户信息，是否删除（Y/N）：");
                        string yn = Console.ReadLine();//用户操作
                        if (yn.ToUpper() == "Y")
                        {
                            //判断删除此用户信息是否成功，返回TRUE表示删除成功
                            if (new UsersData().Delete(operate_delete))
                            {
                                Console.WriteLine("删除成功！");
                            }
                            //返回false表示删除失败
                            else
                            {
                                Console.WriteLine("删除失败！！");
                            }
                        }
                        else
                        {
                            Console.WriteLine("删除操作去取消！！");
                        }
                    }
                    //判断是否插寻到此用户信息，返回false表示无此用户信息
                    else
                    {
                        Console.WriteLine("未查询到此用户！！");
                    }
                    Console.WriteLine("");
                }

                //修改用户信息操作
                else if (operate == "")
                {
                    Console.WriteLine("");
                    for (; ; )
                    {
                        Console.Write("请输入要修改的用户名(输入0退出修改返回上一级)：");
                        string operate_updata = Console.ReadLine();
                        if(operate_updata=="")
                        {
                            break;
                        }
                        //查询是否有此用户，返回TRUE表示有此用户
                        if (new UsersData().select(operate_updata))
                        {

                            Console.WriteLine("查询到此用户信息，是否修改（Y/N）：");
                            string yn = Console.ReadLine();//用户操作
                        if (yn.ToUpper() == "Y")
                            {
                                Console.WriteLine ("请选择：*、修改用户所有信息；1、修改用户密码；2、修改用户昵称；3、修改用户性别；4、修改用户民族；5、修改用户班级；6、修改用户生日；0、结束修改");
                                string updata_opt = Console.ReadLine();//用户操作
                                //修改用户所有信息
                                if(updata_opt=="*")
                                {
                                    Users us = new Users();
                                    Console.Write("请输入要修改的密码：");
                                    us.Password = Console.ReadLine();
                                    Console.Write("请输入要修改的昵称：");
                                    us.Nick = Console.ReadLine();
                                    Console.Write("请输入要修改的性别：");
                                    us.Sex = Convert.ToBoolean(Console.ReadLine());
                                    Console.Write("请输入要修改的民族：");
                                    us.Nation = Console.ReadLine();
                                    Console.Write("请输入要修改的班级：");
                                    us.Class = Console.ReadLine();
                                    Console.Write("请输入要修改的生日：");
                                    us.Birthday = Convert.ToDateTime(Console.ReadLine());
                                    if (new UsersData().Update(us))
                                    {
                                        Console.WriteLine("修改成功！！");
                                    }
                                    else
                                    {
                                        Console.WriteLine("修改失败！！");
                                    }
                                }
                                    //修改用户密码
                                else if(updata_opt=="")
                                {

                                    Users us = new Users();
                                    Console.Write("请输入要修改的密码：");
                                    us.Password = Console.ReadLine();
                                    if (new UsersData().Update1(us))
                                    {
                                        Console.WriteLine("修改成功！！");
                                    }
                                    else
                                    {
                                        Console.WriteLine("修改失败！！");
                                    }
                                }
                                    //修改用户昵称
                                else if (updata_opt == "")
                                {

                                    Users us = new Users();
                                    Console.Write("请输入要修改的昵称：");
                                    us.Nick = Console.ReadLine();
                                    if (new UsersData().Update2(us))
                                    {
                                        Console.WriteLine("修改成功！！");
                                    }
                                    else
                                    {
                                        Console.WriteLine("修改失败！！");
                                    }
                                }
                                //修改性别
                                else if (updata_opt == "")
                                {

                                    Users us = new Users();
                                    Console.Write("请输入要修改的性别：");
                                    us.Sex = Convert.ToBoolean(Console.ReadLine());
                                    if (new UsersData().Update3(us))
                                    {
                                        Console.WriteLine("修改成功！！");
                                    }
                                    else
                                    {
                                        Console.WriteLine("修改失败！！");
                                    }
                                }
                                    //修改民族
                                else if (updata_opt == "")
                                {

                                    Users us = new Users();
                                    Console.Write("请输入要修改的民族：");
                                    us.Nation= Console.ReadLine();
                                    if (new UsersData().Update4(us))
                                    {
                                        Console.WriteLine("修改成功！！");
                                    }
                                    else
                                    {
                                        Console.WriteLine("修改失败！！");
                                    }
                                }
                                    //修改班级
                                else if (updata_opt == "")
                                {

                                    Users us = new Users();
                                    Console.Write("请输入要修改的班级：");
                                    us.Class = Console.ReadLine();
                                    if (new UsersData().Update5(us))
                                    {
                                        Console.WriteLine("修改成功！！");
                                    }
                                    else
                                    {
                                        Console.WriteLine("修改失败！！");
                                    }
                                }
                                    //修改生日
                                else if (updata_opt == "")
                                {

                                    Users us = new Users();
                                    Console.Write("请输入要修改的生日：");
                                    us.Birthday = Convert.ToDateTime(Console.ReadLine());
                                    if (new UsersData().Update6(us))
                                    {
                                        Console.WriteLine("修改成功！！");
                                    }
                                    else
                                    {
                                        Console.WriteLine("修改失败！！");
                                    }
                                }
                                //退出修改操作
                                else
                                {
                                    break;
                                }
                            }
                            else
                            {
                                Console.WriteLine("修改操作去取消！！");
                            }
                        }
                        //查询是否有此用户，返回false表示无此用户
                        else
                        {
                            Console.WriteLine("未查询到此用户！！");
                        }
                    }
                    Console.WriteLine("");
                }
                //结束操作,退出
                else
                {
                    break;
                }
                Console.WriteLine("");

            }
            Console.ReadLine();
        }
    }
}

实体类：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace 实体类_数据访问类练习.App_Code
{
    public class Users
    {
        SqlConnection conn = null;
        SqlCommand comm = null;
        public Users()
        {
            conn = new SqlConnection("server=.;database=Data1128;user=sa;pwd=123");
            comm = conn.CreateCommand();
        }

        private int _Code;
        /// <summary>
        /// code
        /// </summary>
        public int Code
        {
            get { return _Code; }
            set { _Code = value; }
        }
        private string _Name;
        /// <summary>
        /// 用户名
        /// </summary>
        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }
        private string _Password;
        /// <summary>
        /// 密码
        /// </summary>
        public string Password
        {
            get { return _Password; }
            set { _Password = value; }
        }
        private string _Nick;
        /// <summary>
        /// 昵称
        /// </summary>
        public string Nick
        {
            get { return _Nick; }
            set { _Nick = value; }
        }
        private bool _Sex;
        /// <summary>
        /// 性别
        /// </summary>
        public bool Sex
        {
            get { return _Sex; }
            set { _Sex = value; }
        }
        /// <summary>
        ///性别的扩展属性
        /// </summary>
        public string SexStr
        {
            get
            {
                return _Sex ? "男" : "女";
            }
        }
        private string _Nation;
        /// <summary>
        /// 民族
        /// </summary>
        public string Nation
        {
            get { return _Nation; }
            set { _Nation = value; }
        }
        /// <summary>
        /// 民族的扩展属性
        /// </summary>
        public string NationName
        {
            get
            {
                string end = "未填写";
                comm.CommandText = "select *from Nation where nationcode=@a";
                comm.Parameters.Clear();
                comm.Parameters.AddWithValue("@a", _Nation);
                conn.Open();
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    dr.Read();
                    end = dr["nationname"].ToString();
                }
                conn.Close();
                return end;
            }

        }

        private string _Class;
        /// <summary>
        /// 班级
        /// </summary>
        public string Class
        {
            get { return _Class; }
            set { _Class = value; }
        }
        /// <summary>
        /// 班级的扩展属性
        /// </summary>
        public string ClassName
        {
            get
            {
                string end = "未填写";
                comm.CommandText = "select *from Class where classcode=@a";
                comm.Parameters.Clear();
                comm.Parameters.AddWithValue("@a", _Class);
                conn.Open();
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    dr.Read();
                    end = dr["classname"].ToString();
                }

                conn.Close();

                return end;
            }
        }

        private DateTime _Birthday;
        /// <summary>
        /// 生日
        /// </summary>
        public DateTime Birthday
        {
            get { return _Birthday; }
            set { _Birthday = value; }
        }
        /// <summary>
        /// 生日的扩展属性
        /// </summary>
        public string BirthdayStr
        {
            get
            {
                return _Birthday.ToString("yyyy年MM月dd日");
            }
        }
    }
}

数据访问类

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

namespace 实体类_数据访问类练习.App_Code
{
    public class UsersData
    {
        SqlConnection conn = null;
        SqlCommand comm = null;
        public UsersData()
        {
            conn = new SqlConnection("server=.;database=Data1128;user=sa;pwd=123");
            comm = conn.CreateCommand();
        }
        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <returns></returns>
        public List<Users> Select()
        {
            List<Users> list = new List<Users>();
            //comm.CommandText = "select code,usename,password,nick,sex,(select nationname from Nation where nationcode=Users.nation) as 'nation' ,classname as 'class',birthday from Users join Class on Class.classcode=Users.class";
            comm.CommandText = "select * from Users";
            conn.Open();
            SqlDataReader dr = comm.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    Users u = new Users();
                    u.Code = Convert.ToInt32(dr["Code"]);
                    u.Name = dr["USENAME"].ToString();
                    u.Password = dr["Password"].ToString();
                    u.Nick = dr["Nick"].ToString();
                    u.Sex = Convert.ToBoolean(dr["Sex"]);
                    u.Nation = dr["Nation"].ToString();
                    u.Class = dr["Class"].ToString();
                    u.Birthday = Convert.ToDateTime(dr["Birthday"]);

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

        /// <summary>
        /// 查询单个用户的数据
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public List<Users> Select(string username)
        {
            List<Users> ulist = new List<Users>();
            //comm.CommandText = "select code,usename,password,nick,sex,(select nationname from Nation where nationcode=Users.nation) as 'nation' ,classname as 'class',birthday from Users join Class on Class.classcode=Users.class where usename='" + username + "'";
            comm.CommandText = "select *from Users where usename='" + username + "'";
            conn.Open();
            SqlDataReader dr = comm.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    Users u = new Users();
                    u.Code = Convert.ToInt32(dr["Code"]);
                    u.Name = dr["USEName"].ToString();
                    u.Password = dr["Password"].ToString();
                    u.Nick = dr["Nick"].ToString();
                    u.Sex = Convert.ToBoolean(dr["Sex"]);
                    u.Nation = dr["Nation"].ToString();
                    u.Class = dr["Class"].ToString();
                    u.Birthday = Convert.ToDateTime(dr["Birthday"]);

                    ulist.Add(u);
                }
            }
            conn.Close();
            return ulist;
        }
        /// <summary>
        /// 将数据添加到Users表中
        /// </summary>
        /// <param name="us">要添加到表中的Users对象</param>
        /// <returns>添加成功返回true,失败返回false</returns>
        public bool Insert(Users us)
        {
            bool ok=false;
            int count = ;
            comm.CommandText = "insert into Users values(@a,@b,@c,@d,@e,@f,@g)";
            comm.Parameters.Clear();
            comm.Parameters.AddWithValue("@a",us.Name);
            comm.Parameters.AddWithValue("@b", us.Password);
            comm.Parameters.AddWithValue("@c", us.Nick);
            comm.Parameters.AddWithValue("@d", us.Sex);
            comm.Parameters.AddWithValue("@e", us.Nation);
            comm.Parameters.AddWithValue("@f", us.Class);
            comm.Parameters.AddWithValue("@g", us.Birthday);
            try
            {
                conn.Open();
                count = comm.ExecuteNonQuery();
            }
            catch
            {
                ok = false;
            }
            finally
            {
                conn.Close();
            }
            if (count > )
                ok = true;
            else
                ok = false;
            return ok;
        }

        /// <summary>
        /// 完整删除和完整修改中，查询此用户是否存在
        /// </summary>
        /// <param name="username">要查询的Users对象</param>
        /// <returns>返回TRUE表示由此用户</returns>
        public bool select(string username)
        {
            bool has = false;
            comm.CommandText = "select *from Users where usename=@a";
            comm.Parameters.Clear();
            comm.Parameters.AddWithValue("@a",username);
            conn.Open();
            SqlDataReader dr = comm.ExecuteReader();
            if(dr.HasRows)
            {
                has = true;
            }
            conn.Close();
            return has;
        }
        /// <summary>
        /// 完整删除中查到用户后执行的删除操作
        /// </summary>
        /// <param name="uname">要删除的Users对象<</param>
        /// <returns>返回true表示删除成功</returns>
        public bool Delete(string uname)
        {
            bool have = false;
            int count_del = ;
            comm.CommandText = "delete from Users where usename=@a";
            comm.Parameters.Clear();
            comm.Parameters.AddWithValue("@a",uname);
            try
            {
                conn.Open();
                count_del = comm.ExecuteNonQuery();
            }
            catch
            {
                have = false;
            }
            finally
            {
                conn.Close();
            }
            if (count_del > )
                have = true;
            return have;
        }
        /// <summary>
        /// 完整修改用户所有信息中查到用户后执行的修改操作
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool Update(Users user)
        {
            bool ok = false;
            int count = ;
            comm.CommandText = "update Users set password=@b,nick=@c,sex=@d,nation=@e,class=@f,birthday=@g";
            comm.Parameters.Clear();
            comm.Parameters.AddWithValue("@b",user.Password);
            comm.Parameters.AddWithValue("@c", user.Nick);
            comm.Parameters.AddWithValue("@d", user.Sex);
            comm.Parameters.AddWithValue("@e", user.Nation);
            comm.Parameters.AddWithValue("@f", user.Class);
            comm.Parameters.AddWithValue("@g", user.Birthday);
            try
            {
                conn.Open();
                count = comm.ExecuteNonQuery();
            }
            catch
            {
                ok = false;
            }
            finally
            {
                conn.Close();
            }
            if(count>)
            {
                ok=true;
            }
            return ok;
        }
        /// <summary>
        ///  完整修改用户中查到用户后执行的单独修改密码操作
        /// </summary>
        /// <param name="user1"></param>
        /// <returns></returns>
        public bool Update1(Users user)
        {
            bool ok = false;
            int count = ;
            comm.CommandText = "update Users set password=@b";
            comm.Parameters.Clear();
            comm.Parameters.AddWithValue("@b", user.Password);
            try
            {
                conn.Open();
                count = comm.ExecuteNonQuery();
            }
            catch
            {
                ok = false;
            }
            finally
            {
                conn.Close();
            }
            if (count > )
            {
                ok = true;
            }
            return ok;
        }
        /// <summary>
        ///  完整修改用户中查到用户后执行的单独修改昵称操作
        /// </summary>
        /// <param name="user1"></param>
        /// <returns></returns>
        public bool Update2(Users user1)
        {
            bool ok = false;
            int count = ;
            comm.CommandText = "update Users set nick=@b";
            comm.Parameters.Clear();
            comm.Parameters.AddWithValue("@b", user1.Nick);
            try
            {
                conn.Open();
                count = comm.ExecuteNonQuery();
            }
            catch
            {
                ok = false;
            }
            finally
            {
                conn.Close();
            }
            if (count > )
            {
                ok = true;
            }
            return ok;
        }
        /// <summary>
        ///  完整修改用户中查到用户后执行的单独修改性别操作
        /// </summary>
        /// <param name="user1"></param>
        /// <returns></returns>
        public bool Update3(Users user1)
        {
            bool ok = false;
            int count = ;
            comm.CommandText = "update Users set sex=@b";
            comm.Parameters.Clear();
            comm.Parameters.AddWithValue("@b", user1.Sex);
            try
            {
                conn.Open();
                count = comm.ExecuteNonQuery();
            }
            catch
            {
                ok = false;
            }
            finally
            {
                conn.Close();
            }
            if (count > )
            {
                ok = true;
            }
            return ok;
        }
        /// <summary>
        ///  完整修改用户中查到用户后执行的单独修改民族操作
        /// </summary>
        /// <param name="user1"></param>
        /// <returns></returns>
        public bool Update4(Users user1)
        {
            bool ok = false;
            int count = ;
            comm.CommandText = "update Users set nation=@b";
            comm.Parameters.Clear();
            comm.Parameters.AddWithValue("@b", user1.Nation);
            try
            {
                conn.Open();
                count = comm.ExecuteNonQuery();
            }
            catch
            {
                ok = false;
            }
            finally
            {
                conn.Close();
            }
            if (count > )
            {
                ok = true;
            }
            return ok;
        }
        /// <summary>
        ///  完整修改用户中查到用户后执行的单独修改班级操作
        /// </summary>
        /// <param name="user1"></param>
        /// <returns></returns>
        public bool Update5(Users user1)
        {
            bool ok = false;
            int count = ;
            comm.CommandText = "update Users set class=@b";
            comm.Parameters.Clear();
            comm.Parameters.AddWithValue("@b", user1.Class);
            try
            {
                conn.Open();
                count = comm.ExecuteNonQuery();
            }
            catch
            {
                ok = false;
            }
            finally
            {
                conn.Close();
            }
            if (count > )
            {
                ok = true;
            }
            return ok;
        }
        /// <summary>
        ///  完整修改用户中查到用户后执行的单独修改生日操作
        /// </summary>
        /// <param name="user1"></param>
        /// <returns></returns>
        public bool Update6(Users user1)
        {
            bool ok = false;
            int count = ;
            comm.CommandText = "update Users set birthday=@b";
            comm.Parameters.Clear();
            comm.Parameters.AddWithValue("@b", user1.Birthday);
            try
            {
                conn.Open();
                count = comm.ExecuteNonQuery();
            }
            catch
            {
                ok = false;
            }
            finally
            {
                conn.Close();
            }
            if (count > )
            {
                ok = true;
            }
            return ok;
        }

    }
}