综合防攻击、实体类、数据访问类进行的练习

主程序：

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.Write("请输入操作序号：1、查询 2、添加 3、删除 4、修改");
                string code = Console.ReadLine();//code为操作序号
                if (code == "1")//查询
                {
                    for (; ; )
                    {
                        Console.Write("请输入想要查询的用户名（输入*查看全部，输入#返回）：");
                        string code1 = Console.ReadLine();//code1为查询中的操作序号或所输的第一个用户名
                        if (code1 == "*")//如果输入* 查看全部数据
                        {
                            List<Users> se = new UsersData().Select();
                            Console.WriteLine("============================数据信息==============================");
                            Console.WriteLine("序号	用户名		密码	昵称	性别	生日		民族	班级");
                            foreach (Users uuu in se)
                            {
                                Console.WriteLine(uuu.Ids + "	" + uuu.UserName + "	" + uuu.PassWord + "	" + uuu.NickName + "	" + (uuu.Sex ? "男" : "女") + "	" + Convert.ToDateTime(uuu.Birthday).ToShortDateString() + "	" + uuu.Nation + "	" + uuu.Class);
                            }
                        }
                        else if (code1 == "#")//输入#返回
                        {
                            break;
                        }
                        else//如果输入的是第一个用户名
                        {
                            List<string> users = new List<string>();//users泛型集合用于存放多个用户名
                            int bia = 0;//标记变量 用于检测第一个用户名是否重复 如果不重复 将此用户名放入集合 标记变量+1 后续不再执行此部分
                            for (; ; )
                            {
                                if (bia == 0)
                                {
                                    if (new UsersData().Select(code1))
                                    {
                                        users.Add(code1);
                                        bia++;
                                    }
                                    else
                                    {
                                        Console.Write("查无此用户，请重新输入！");
                                        bia++;
                                        continue;
                                    }
                                }
                                Console.Write("请继续输入想要查询的用户名（输入0结束输入）：");
                                string names = Console.ReadLine();
                                if (names == "0")//如果输入0则跳出录入用户名的循环
                                {
                                    break;
                                }
                                else//如果输入的是其他用户名
                                {
                                    if (new UsersData().Select(names))//判断数据库中有无此用户名
                                    {
                                        int count = users.Count();
                                        int biao = 0;//标记变量biao
                                        for (int i = 0; i < count; i++)//集合中若已经有此用户名则biao+1
                                        {
                                            if (users[i] == names)
                                            {
                                                biao++;
                                            }
                                        }
                                        if (biao == 0)//biao为0，users集合中无此用户
                                        {
                                            users.Add(names);//将此用户名添加到users集合中
                                            continue;
                                        }
                                        else//users集合中已经有此用户名
                                        {
                                            Console.Write("输入用户名重复，请重新输入！");
                                            continue;
                                        }
                                    }
                                    else//数据库中没有此用户名
                                    {
                                        Console.WriteLine("查无此用户，请重新输入！");
                                    }
                                }
                            }
                            //用户名录入完毕，开始查询
                            if (users.Count() > 0)//users集合不为空
                            {
                                List<Users> us = new UsersData().Select(users);//设置us集合接受多个返回的数据库中的数据
                                Console.WriteLine("============================数据信息==============================");
                                Console.WriteLine("序号	用户名		密码	昵称	性别	生日		民族	班级");
                                foreach (Users uuu in us)//使用遍历将us集合中的数据全部打印
                                {
                                    Console.WriteLine(uuu.Ids + "	" + uuu.UserName + "	" + uuu.PassWord + "	" + uuu.NickName + "	" + (uuu.Sex ? "男" : "女") + "	" + Convert.ToDateTime(uuu.Birthday).ToShortDateString() + "	" + uuu.Nation + "	" + uuu.Class);
                                }
                            }
                            else//users集合为空
                            {
                                Console.WriteLine("没有查询任何用户信息！");
                                break;
                            }
                        }
                    }
                }
                else if (code == "2")//添加
                {
                    for (; ; )
                    {
                        Console.Write("请输入想要添加的用户名（输入#返回）：");
                        string user = Console.ReadLine();
                        if (user=="#")
                        {
                            break;
                        }
                        else if(!(new UsersData().Select(user)))//如果数据库中没有此用户
                        {
                            Users u = new UsersData().enter();//录入用户除用户名以外的所有信息，并储存到u中
                            u.UserName = user;//将刚才的用户名录入到u中
                            bool ok = new UsersData().Insert(u);
                            if (ok)
                            {
                                Console.WriteLine("添加数据成功！");
                                continue;
                            }
                            else
                                Console.WriteLine("添加失败！");
                        }
                        else//数据库中已有此用户名
                        {
                            Console.Write("输入用户名已存在，请重新输入！");
                            continue;
                        }
                    }
                }
                else if (code == "3")//删除
                {
                    for (; ; )
                    {
                        Console.Write("请输入想要删除的用户名（输入#返回）：");
                        string user = Console.ReadLine();
                        if(user=="#")
                        {
                            break;
                        }
                        else if (new UsersData().Select(user))
                        {
                            Users u = new UsersData().SelectOne(user);
                            Console.WriteLine("============================数据信息==============================");
                            Console.WriteLine("序号	用户名		密码	昵称	性别	生日		民族	班级");
                            Console.WriteLine(u.Ids + "	" + u.UserName + "	" + u.PassWord + "	" + u.NickName + "	" + (u.Sex ? "男" : "女") + "	" + Convert.ToDateTime(u.Birthday).ToShortDateString() + "	" + u.Nation + "	" + u.Class);
                            for (; ; )
                            {
                                Console.Write("是否确定删除此条数据（Y/N）：");
                                string yn = Console.ReadLine();
                                if (yn.ToUpper() == "Y")
                                {
                                    bool ok = new UsersData().Delete(user);
                                    if (ok)
                                    {
                                        Console.WriteLine("删除成功！");
                                        break;
                                    }
                                    else
                                    {
                                        Console.WriteLine("删除失败！");
                                        break;
                                    }
                                }
                                else if (yn.ToUpper() == "N")
                                {
                                    Console.WriteLine("已取消删除。");
                                    break;
                                }
                                else
                                {
                                    Console.Write("输入有误，请重新输入！");
                                    continue;
                                }
                            }
                        }
                        else
                        {
                            Console.Write("输入用户名不存在，请重新输入！");
                            continue;
                        }
                    }
                }
                else if (code == "4")//修改
                {
                    for (; ; )
                    {
                        Console.Write("请输入想要修改的用户名（输入#返回）：");
                        string user = Console.ReadLine();
                        if(user=="#")
                        {
                            break;
                        }
                        else if(new UsersData().Select(user))
                        {
                            Users u = new UsersData().SelectOne(user);
                            Console.WriteLine("============================数据信息==============================");
                            Console.WriteLine("序号	用户名		密码	昵称	性别	生日		民族	班级");
                            Console.WriteLine(u.Ids + "	" + u.UserName + "	" + u.PassWord + "	" + u.NickName + "	" + (u.Sex ? "男" : "女") + "	" + Convert.ToDateTime(u.Birthday).ToShortDateString() + "	" + u.Nation + "	" + u.Class);
                            for (; ; )
                            {
                                Console.Write("是否确定修改此条数据（Y/N）：");
                                string yn = Console.ReadLine();
                                if(yn.ToUpper()=="Y")
                                {
                                    Users us = new UsersData().enter();
                                    us.UserName = user;
                                    bool ok = new UsersData().Update(us);
                                    if (ok)
                                    {
                                        Console.WriteLine("修改成功！");
                                        break;
                                    }
                                    else
                                    {
                                        Console.WriteLine("修改失败！");
                                        break;
                                    }
                                }
                                else if (yn.ToUpper() == "N")
                                {
                                    Console.WriteLine("已取消修改。");
                                    break;
                                }
                                else
                                {
                                    Console.Write("输入有误，请重新输入！");
                                    continue;
                                }
                            }
                        }
                        else
                        {
                            Console.Write("输入用户名不存在，请重新输入！");
                            continue;
                        }
                    }
                }
                else
                {
                    Console.WriteLine("输入操作序号有误，请重新输入！");
                    continue;
                }
            }
        }
    }
}

实体类：

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

namespace 数据库操作.App_Code
{
    public class Users
    {
        private int _Ids;
        /// <summary>
        /// 用户ID
        /// </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.Linq;
using System.Text;
using System.Data.SqlClient;

namespace 数据库操作.App_Code
{
    public class UsersData
    {
        SqlConnection conn = null;
        SqlCommand cmd = null;
        public UsersData()
        {
            conn=new SqlConnection("server=.;database=Data0928;user=sa;pwd=123");
            cmd=conn.CreateCommand();
        }
        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <returns></returns>
        public List<Users> Select()
        { 
            List<Users> ud = new List<Users>();
            cmd.CommandText = "select ids,UserName,PassWord,NickName,Sex,Birthday,(select NationName from Nation where NationCode=Users.Nation) as 'Nation' ,ClassName as 'Class' from Users join Class on Class.ClassCode=Users.Class";
            conn.Open();
            SqlDataReader rd = cmd.ExecuteReader();
            if(rd.HasRows)
            {  
                while(rd.Read())
                {
                    Users user = new Users();
                    user.Ids =Convert.ToInt32(rd["ids"]);
                    user.UserName = rd["UserName"].ToString();
                    user.PassWord = rd["PassWord"].ToString();
                    user.NickName = rd["NickName"].ToString();
                    user.Sex = Convert.ToBoolean(rd["Sex"]);
                    user.Birthday = Convert.ToDateTime(rd["Birthday"]);
                    user.Nation = rd["Nation"].ToString();
                    user.Class = rd["Class"].ToString();
                    ud.Add(user);
                }
            }
            conn.Close();
            return ud;
        }
        /// <summary>
        /// 查询多个用户的数据
        /// </summary>
        /// <param name="users"></param>
        /// <returns></returns>
        public List<Users> Select(List<string> users)
        {
            List<Users> us = new List<Users>();
            int count = users.Count();
            for (int i = 0; i < count; i++)
            {
                cmd.CommandText = "select ids,UserName,PassWord,NickName,Sex,Birthday,(select NationName from Nation where NationCode=Users.Nation) as 'Nation' ,ClassName as 'Class' from Users join Class on Class.ClassCode=Users.Class where UserName=@uname";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@uname", users[i].ToString());
                conn.Open();
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        Users user = new Users();
                        user.Ids = Convert.ToInt32(rd["ids"]);
                        user.UserName = rd["UserName"].ToString();
                        user.PassWord = rd["PassWord"].ToString();
                        user.NickName = rd["NickName"].ToString();
                        user.Sex = Convert.ToBoolean(rd["Sex"]);
                        user.Birthday = Convert.ToDateTime(rd["Birthday"]);
                        user.Nation = rd["Nation"].ToString();
                        user.Class = rd["Class"].ToString();
                        us.Add(user);
                    }
                }
                conn.Close();
            }
            return us;
        }

        /// <summary>
        /// 判断有无此用户名
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool Select(string name)
        {
            bool has =false;
            cmd.CommandText="select * from Users where UserName=@user";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@user",name);
            conn.Open();
            SqlDataReader rd = cmd.ExecuteReader();
            if(rd.HasRows)
            {
                has = true;
            }
            conn.Close();
            return has;
        }

        /// <summary>
        /// 查询单行数据并返回Users对象
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public Users SelectOne(string name)
        {
            Users user = new Users();
            cmd.CommandText = "select ids,UserName,PassWord,NickName,Sex,Birthday,(select NationName from Nation where NationCode=Users.Nation) as 'Nation' ,ClassName as 'Class' from Users join Class on Class.ClassCode=Users.Class where UserName=@a";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a",name);
            conn.Open();
            SqlDataReader rd = cmd.ExecuteReader();
            if (rd.HasRows)
            {
                while (rd.Read())
                {
                    user.Ids = Convert.ToInt32(rd["ids"]);
                    user.UserName = rd["UserName"].ToString();
                    user.PassWord = rd["PassWord"].ToString();
                    user.NickName = rd["NickName"].ToString();
                    user.Sex = Convert.ToBoolean(rd["Sex"]);
                    user.Birthday = Convert.ToDateTime(rd["Birthday"]);
                    user.Nation = rd["Nation"].ToString();
                    user.Class = rd["Class"].ToString();
                }
            }
            conn.Close();
            return user;
        }

        /// <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; 
            }
                conn.Close();
            if (count > 0)
                ok = true;

            return ok;
        }

        /// <summary>
        /// 获取一个Users除用户名以外的数据
        /// </summary>
        /// <returns></returns>
        public Users enter()
        {
            Users u = new Users();
            for (; ; )
            {
                Console.Write("请输入密码（6到18位）：");
                string pwd1 = Console.ReadLine();
                if (pwd1.Length >= 6 && pwd1.Length <= 18)
                {
                    u.PassWord = pwd1;
                    break;
                }
                else
                {
                    Console.WriteLine("密码长度不正确，请重新输入！");
                    continue;
                }
            }
            Console.Write("请输入昵称：");
            u.NickName= Console.ReadLine();
            for (; ; )
            {
                Console.Write("请输入性别（请输入男女或者0、1）：");
                string sex1 = Console.ReadLine();
                if (sex1 == "0" || sex1 == "女")
                {
                    u.Sex = false;
                    break;
                }
                else if (sex1 == "男" || sex1 == "1")
                {
                    u.Sex = true;
                    break;
                }
                else
                {
                    Console.WriteLine("性别输入有误，请重新输入！");
                    continue;
                }
            }
            for (; ; )
            {
                Console.Write("请输入生日：");
                try
                {
                    u.Birthday = DateTime.Parse(Console.ReadLine());
                    break;
                }
                catch
                {
                    Console.WriteLine("生日日期输入有误，请重新输入！");
                    continue;
                }
            }
            for (; ; )
            {
                Console.Write("请输入民族：");
                string nation1 = Console.ReadLine();
                if (nation1 == "汉族" || nation1 == "汉")
                {
                    u.Nation = "N001";
                    break;
                }
                else if (nation1 == "满族" || nation1 == "满")
                {
                    u.Nation = "N002";
                    break;
                }
                else if (nation1 == "藏族" || nation1 == "藏")
                {
                    u.Nation = "N003";
                    break;
                }
                else if (nation1 == "彝族" || nation1 == "彝")
                {
                    u.Nation = "N004";
                    break;
                }
                else
                {
                    Console.WriteLine("输入民族有误，请重新输入！");
                    continue;
                }
            }
            for (; ; )
            {
                Console.Write("请输入班级：");
                string cla1 = Console.ReadLine();
                if (cla1 == "一班" || cla1 == "一")
                {
                    u.Class= "C001";
                    break;
                }
                else if (cla1 == "二班" || cla1 == "二")
                {
                    u.Class = "C002";
                    break;
                }
                else if (cla1 == "三班" || cla1 == "三")
                {
                    u.Class = "C003";
                    break;
                }
                else if (cla1 == "四班" || cla1 == "四")
                {
                    u.Class = "C004";
                    break;
                }
                else
                {
                    Console.WriteLine("输入班级有误，请重新输入！");
                    continue;
                }
            }
            return u;
        }

        /// <summary>
        /// 通过用户名删除一条数据
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool Delete(string name)
        {
            bool ok = false;
            cmd.CommandText = "delete from Users where UserName=@a";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a",name);
            conn.Open();
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            if (i > 0)
                ok = true;
            else
                ok = false;

            return ok;
        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="u"></param>
        /// <returns></returns>
        public bool Update(Users u)
        {
            bool ok = false;
            cmd.CommandText = "update Users set PassWord=@a,NickName=@b,Sex=@c,Birthday=@d,Nation=@e,Class=@f where UserName=@g";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a",u.PassWord);
            cmd.Parameters.AddWithValue("@b", u.NickName);
            cmd.Parameters.AddWithValue("@c", u.Sex);
            cmd.Parameters.AddWithValue("@d", u.Birthday);
            cmd.Parameters.AddWithValue("@e", u.Nation);
            cmd.Parameters.AddWithValue("@f", u.Class);
            cmd.Parameters.AddWithValue("@g", u.UserName);
            conn.Open();
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            if (i > 0)
                ok = true;
            else
                ok = false;

            return ok;
        }
    }
}