实体类,数据访问类应用

一,表中某个数据进行重新排序

(1)数据访问类

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

namespace ConsoleApplication2.App_Code
{
    public class StudentsData
    {
        SqlConnection conn = null;
        SqlCommand cmd = null;

        public StudentsData()
        {
            conn = new SqlConnection("server=.;database=Data0216;user=sa;pwd=123");
            cmd = conn.CreateCommand();
        }

        public void ResetNumber()
        {
            List<Students> slist = new List<Students>();
            cmd.CommandText = "select *from Students";
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                Students s = new Students();
                s.Ids = Convert.ToInt32(dr["Ids"]);
                s.Scode = dr["Scode"].ToString();
                s.Sname = dr["Sname"].ToString();

                slist.Add(s);
            }
            conn.Close();

            int count = 1;
            foreach (Students sss in slist)
            {
                sss.Scode = "S" + count.ToString("000");
                count++;
            }

            conn.Open();
            foreach (Students ss in slist)
            {
                cmd.CommandText = "Update Students set Scode=@a where Ids = @b";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@a", ss.Scode);
                cmd.Parameters.AddWithValue("@b", ss.Ids);
                cmd.ExecuteNonQuery();
            }
            conn.Close();

        }

(2)实体类

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

namespace ConsoleApplication2.App_Code
{
    public class Students
    {
        private int _Ids;

        public int Ids
        {
            get { return _Ids; }
            set { _Ids = value; }
        }
        private string _Scode;

        public string Scode
        {
            get { return _Scode; }
            set { _Scode = value; }
        }
        private string _Sname;

        public string Sname
        {
            get { return _Sname; }
            set { _Sname = value; }
        }


    }
}

(3)主界面

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

namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            new StudentsData().ResetNumber();
           Console.ReadKey();
        }
    }
}

二.比较完善的增删改查

User表

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

namespace ConsoleApplication2.APP_Code
{
    public class Users
    {
        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; }
        }
        public string Sexie
        {
            get { return _Sex ? "男" : "女"; }
        }
        private DateTime _Birthday;

        public DateTime Birthday
        {
            get { return _Birthday; }
            set { _Birthday = value; }
        }
        public string Birthdayie
        {
            get { return _Birthday.ToString("yyyy年MM月dd日"); }
        }
        public int Age
        {
            get { return DateTime.Now.Year - _Birthday.Year; }
        }
        private String _Nation;

        public String Nation
        {
            get { return _Nation; }
            set { _Nation = value; }
        }
        public string Naname
        {
            get { return new NationDate().CeleNa(_Nation); }
        }

    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace ConsoleApplication2.APP_Code
{
    public class UsersData
    {

        // 数据库连接
        SqlConnection conn = null;
        SqlCommand cmd = null;

        public UsersData()
        {
            conn = new SqlConnection("server=.;database=ADO;user=sa;pwd=123;");
            cmd = conn.CreateCommand();
        }

        //数据库数据查询
        public List<Users> Celect()
        {
            List<Users> li = new List<Users>();
            cmd.CommandText = "select*from Users";
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                Users n = new Users();
                n.Ids = Convert.ToInt32(dr["Ids"]);
                n.Username = dr["Username"].ToString();
                n.Password = dr["Password"].ToString();
                n.Nickname = dr["Nickname"].ToString();
                n.Sex = Convert.ToBoolean(dr["Sex"]);
                n.Birthday = Convert.ToDateTime(dr["Birthday"]);
                n.Nation = dr["Nation"].ToString();
                li.Add(n);
            }
            conn.Close();
            return li;
        }

        //数据库数据添加
        public int Add(Users mm)
        {
            conn.Open();
            cmd.CommandText = "insert into Users values(@a,@b,@c,@d,@e,@f);";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a", mm.Username);
            cmd.Parameters.AddWithValue("@b", mm.Password);
            cmd.Parameters.AddWithValue("@c", mm.Nickname);
            cmd.Parameters.AddWithValue("@d", mm.Sex);
            cmd.Parameters.AddWithValue("@e", mm.Birthday);
            cmd.Parameters.AddWithValue("@f", mm.Nation);
            int a = cmd.ExecuteNonQuery();
            conn.Close();
            return a;
        }

        //数据库数据删除 
        //1.查询数据库中有没有该用户名
        public bool Cele(string c)
        {
            bool ce;
            cmd.CommandText = "select @a from Users";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a", c);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            dr.Read();
            if (dr[0].ToString() != "")
            {
                ce = true;
            }
            else
            {
                ce = false;
            }
            conn.Close();

            return ce;
        }
        //2.如果有该数据,执行删除
        public int del(string d)
        {
            cmd.CommandText = "delete from Users where Username=@a;";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a", d);
            conn.Open();
            int z = cmd.ExecuteNonQuery();
            conn.Close();
            return z;
        }
        //数据库数据修改
        //上面判断有没有该用户名
        //如果有改用户名,执行修改
        public int Change(Users u)
        {
            cmd.CommandText = "update Users set Password=@a,Nickname=@b,Sex=@c,Birthday=@d,Nation=@e where Username=@f;";
            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.Username);
            conn.Open();
            int zz = cmd.ExecuteNonQuery();
            conn.Close();
            return zz;

        }




    }
}

Nation表

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

namespace ConsoleApplication2.APP_Code
{
    public class Nation
    {
        private String _Nationcode;

        public String Nationcode
        {
            get { return _Nationcode; }
            set { _Nationcode = value; }
        }
        private string _Nationname;

        public string Nationname
        {
            get { return _Nationname; }
            set { _Nationname = value; }
        }

    }
}

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

namespace ConsoleApplication2.APP_Code
{
    public class NationDate
    {
        // 数据库连接
        SqlConnection conn = null;
        SqlCommand cmd = null;

        public NationDate()
        {
            conn = new SqlConnection("server=.;database=ADO;user=sa;pwd=123;");
            cmd = conn.CreateCommand();
        }
        //查询Nation表
        public string CeleNa(string a)
        {
            string mz;
            cmd.CommandText = "select Nationname from Nation where Nationcode=@a;";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a", a);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            dr.Read();
            if (dr[0].ToString() != "")
            {
                mz = dr[0].ToString();
            }
            else
            {
                mz = "<暂无>";
            }
            conn.Close();
            return mz;
        }



    }
}

其他:

(1)判断:

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

namespace ConsoleApplication2.APP_Code
{
    public class judge
    {
        //性别判断
        public int pdxb(string n)
        {
            int a = 0;
            if (n == "男")
                return a = 1;
            else
                return a;
        }
        //生日判断
        public bool dt(string b)
        {
            try
            {
                Convert.ToDateTime(b);
                return true;
            }
            catch
            {
                return false;
            }
        }
        //民族判断
        public string mz(string c)
        {
            if (c == "汉族")
            {
                return "N001";
            }
            else if (c == "满族")
            {
                return "N002";
            }
            else if (c == "维吾尔族")
            {
                return "N003";
            }
            else if (c == "回族")
            {
                return "N004";
            }
            else if (c == "藏族")
            {
                return "N005";
            }
            else
            {
                return "N001";
            }
        }

    }
}

(2)主界面:

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

namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            while (true)
            {
                #region 每次开始展示表数据
                //调用UserData类并实例化
                UsersData hh = new UsersData();
                //调用UserData中查询Celete的方法,并用泛型集合接收
                List<Users> hq = hh.Celect();
                //遍历并打印数据

                Console.WriteLine("现有表的数据内容:");
                Console.WriteLine("序号" + "	" + "用户名" + "	" + "密码" + "	" + "昵称" + "	" + "性别" + "	" + "生日" + "	" + "年龄" + "	" + "民族");
                foreach (Users i in hq)
                {
                    Console.WriteLine(i.Ids + "	" + i.Username + "	" + i.Password + "	" + i.Nickname + "	" + i.Sexie + "	" + i.Birthdayie + "	" + i.Age + "	" + i.Naname);
                }
                Console.WriteLine("按任意键继续.");
                Console.ReadKey();
                #endregion
                //功能开始执行
                Console.Write("请输入你要进行的操作(添加 / 修改 / 删除):");
                string x = Console.ReadLine();

                //判断用户选择的哪种功能

                //如果是添加数据
                #region
                if (x == "添加")
                {
                    //实例化users对象
                    Users mm = new Users();
                    //开始询问用户要添加数据
                    Console.Write("请输入用户名:");
                    mm.Username = Console.ReadLine();
                    Console.Write("请输入密码:");
                    mm.Password = Console.ReadLine();
                    Console.Write("请输入昵称:");
                    mm.Nickname = Console.ReadLine();
                    Console.Write("请输入性别:");
                    string pd = Console.ReadLine();
                    judge h = new judge();
                    mm.Sex = Convert.ToBoolean(h.pdxb(pd));
                    while (true)
                    {
                        Console.Write("请输入生日:");
                        string bir = Console.ReadLine();
                        judge h1 = new judge();
                        bool ab = h1.dt(bir);
                        if (ab)
                        {
                            mm.Birthday = Convert.ToDateTime(bir);
                            break;
                        }
                        else
                        {
                            Console.Write("输入日期不正确,");
                        }
                    }
                    Console.Write("请输入民族:");
                    string s = Console.ReadLine();
                    judge h2 = new judge();
                    mm.Nation = h2.mz(s);
                    //调用UserData类并实例化
                    UsersData hh1 = new UsersData();
                    //调用UserData中查询add的方法,并判断是否添加成功
                    int js = hh1.Add(mm);
                    if (js > 0)
                    {
                        Console.WriteLine("数据添加成功!");
                    }

                }
                #endregion
                //如果是删除数据
                #region
                else if (x == "删除")
                {
                    string de = "";
                    while (true)
                    {
                        Console.Write("请输入你要删除数据的用户名:");
                        string yhm = Console.ReadLine();
                        //判断有没有这个用户名
                        //调用UsersData类并实例化
                        UsersData hh2 = new UsersData();
                        bool xx = hh2.Cele(yhm);
                        if (xx)
                        {
                            de = yhm;
                            UsersData hh3 = new UsersData();
                            int cd = hh3.del(de);
                            Console.WriteLine("数据删除成功,本次共删除" + cd + "行");
                            break;
                        }
                        else
                        {
                            Console.Write("没有该用户名,");
                        }
                    }
                }
                #endregion
                //如果是修改数据
                if (x == "修改")
                {
                    while (true)
                    {
                        Users u = new Users();
                        Console.Write("请输入你要修改数据的用户名");
                        string yhm1 = Console.ReadLine();
                        //判断有没有这个用户名
                        //调用UsersData类并实例化
                        UsersData hh2 = new UsersData();
                        bool xx = hh2.Cele(yhm1);
                        if (xx)
                        {
                            u.Username = yhm1;
                            Console.Write("请输入修改后的密码:");
                            u.Password = Console.ReadLine().ToString();
                            Console.Write("请输入修改后的昵称:");
                            u.Nickname = Console.ReadLine().ToString();
                            Console.Write("请输入修改后的性别:");
                            string pd2 = Console.ReadLine().ToString();
                            judge h5 = new judge();
                            u.Sex = Convert.ToBoolean(h5.pdxb(pd2));
                            while (true)
                            {
                                Console.Write("请输入修改后的生日:");
                                string bir1 = Console.ReadLine();
                                judge h3 = new judge();
                                bool abc = h3.dt(bir1);
                                if (abc)
                                {
                                    u.Birthday = Convert.ToDateTime(bir1);
                                    break;
                                }
                                else
                                {
                                    Console.Write("输入日期不正确,");
                                }
                            }
                            Console.Write("请输入修改后的民族:");
                            string ss = Console.ReadLine();
                            judge h4 = new judge();
                            u.Nation = h4.mz(ss);
                            UsersData xhx = new UsersData();
                            int sx = xhx.Change(u);
                            if (sx > 0)
                            {
                                Console.WriteLine("数据修改成功!");
                                break;
                            }
                        }
                        else
                        {
                            Console.Write("没有该用户名,");
                        }
                    }
                }






            }
        }
    }
}