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

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            while (true)
            {
                //学生信息展示
                new StudentData().print();
                //用户输入操作
                Console.Write("请输入您要继续的操作（输入数字1为新增，输入数字2为修改，输入数字3为删除）：");
                string Do = Console.ReadLine();
                //添加
                #region 添加
                if (Do == "1")
                {
                    try
                    {//用户输入信息
                        Student sti = new Student();
                        sti.Code = new StudentData().bianhao();
                        Console.WriteLine("学生编号为：" + sti.Code);
                        //循环输入姓名
                        sti.Name = new StudentData().name();
                        //循环输入性别
                        sti.Sex = new StudentData().sex();
                        //循环输入生日
                        sti.Birthday = new StudentData().birthday();
                        //循环输入专业
                        sti.SubjectCode = new StudentData().subject();

                        for (; ; )
                        {
                            //打印添加内容
                            Console.WriteLine("编号\t姓名\t性别\t年龄\t生日\t专业");
                            Console.WriteLine(sti.Code + "\t" + sti.Name + "\t" + sti.Sexstr + "\t" + sti.Birthdaystr + "\t" + sti.SubjectName);
                            Console.Write("以上为新增学生的信息，是否确定添加？（Y/N）：");
                            string yn = Console.ReadLine();
                            //调用方法添加
                            if (yn == "Y")
                            {
                                new StudentData().insert(sti);
                                //清空前数据
                                Console.Clear();
                                Console.WriteLine("添加成功！");
                                break;
                            }
                            else if (yn == "N")
                            {
                                //清空前数据
                                Console.Clear();
                                Console.WriteLine("未添加成功！");
                                break;
                            }
                            else
                            {
                                Console.WriteLine("请正确输入指令！");
                                continue;
                            }
                        }
                        continue;
                    }
                    catch
                    {
                        //清空前数据
                        Console.Clear();
                        Console.WriteLine("添加失败，请重新输入！");
                        continue;
                    }
                }
                #endregion
                //修改
                #region 修改
                else if (Do == "2")
                {
                    Console.Write("请输入修改学生编号：");
                    string sc = Console.ReadLine();
                    Student stupate = new Student();
                    StudentData stu = new StudentData();
                    if (stu.codesel(sc))
                    {
                        stupate.Code = sc;
                        try
                        {
                            List<Student> ulist = stu.selectcode(sc);
                            foreach (Student st in ulist)
                            {
                                Console.WriteLine(st.Code + "学生当前姓名为：" + st.Name);
                                stupate.Name = stu.name();
                                Console.WriteLine(st.Code + "学生当前性别为：" + st.Sexstr);
                                stupate.Sex = stu.sex();
                                Console.WriteLine(st.Code + "学生当前生日为：" + st.Birthdaystr);
                                stupate.Birthday = stu.birthday();
                                Console.WriteLine(st.Code + "学生当前专业为：" + st.SubjectName);
                                stupate.SubjectCode = stu.subject();
                            }
                        }
                        catch
                        {
                            //清除
                            Console.Clear();
                            Console.WriteLine("输入失败，请重新输入！");
                            continue;
                        }
                    }
                    else
                    {
                        //清除
                        Console.Clear();
                        Console.WriteLine("输入编号有误，请重新输入！");
                        continue;
                    }

                    for (; ; )
                    {
                        //打印修改内容
                        Console.WriteLine("编号\t姓名\t性别\t年龄\t生日\t专业");
                        Console.WriteLine(stupate.Code + "\t" + stupate.Name + "\t" + stupate.Sexstr + "\t" + stupate.Birthdaystr + "\t" + stupate.SubjectName);
                        Console.Write("以上为查询学生的信息，是否确定修改？（Y/N）：");
                        string yn = Console.ReadLine();
                        //调用方法修改
                        if (yn == "Y")
                        {
                            new StudentData().update(stupate);
                            //清空前数据
                            Console.Clear();
                            Console.WriteLine("添加成功！");
                            break;
                        }
                        else if (yn == "N")
                        {
                            //清空前数据
                            Console.Clear();
                            Console.WriteLine("未添加成功！");
                            break;
                        }
                        else
                        {
                            Console.WriteLine("请正确输入指令！");
                            continue;
                        }
                    }

                    continue;

                }
                #endregion
                //删除
                #region 删除
                else if (Do == "3")
                {
                    Console.Write("请输入学生编号：");
                    string a = Console.ReadLine();
                    StudentData std = new StudentData();
                    if (std.codesel(a))
                    {
                        List<Student> dlist = std.selectcode(a);
                        foreach (Student st in dlist)
                        {
                            Console.WriteLine("编号\t姓名\t性别\t年龄\t生日\t专业");
                            Console.WriteLine(st.Code + "\t" + st.Name + "\t" + st.Sexstr + "\t" + st.Birthdaystr + "\t" + st.SubjectName);
                        }

                        for (; ; )
                        {
                            Console.Write("以上为查询学生的信息，是否确定删除？（Y/N）：");
                            string yn = Console.ReadLine();
                            //调用方法删除
                            if (yn == "Y")
                            {
                                std.delete(a);
                                //清空前数据
                                Console.Clear();
                                Console.WriteLine("删除成功！");
                                break;
                            }
                            else if (yn == "N")
                            {
                                //清空前数据
                                Console.Clear();
                                Console.WriteLine("删除失败");
                                break;
                            }
                            else
                            {
                                Console.WriteLine("请正确输入指令！");
                                continue;
                            }
                        }
                        continue;
                    }

                    else
                    {
                        Console.Clear();
                        Console.WriteLine("输入编号有误，请重新输入！");
                        continue;
                    }
                }
                #endregion
                //错误提醒
                else
                {
                    //清除
                    Console.Clear();
                    Console.WriteLine("请正确输入操作数字！！");
                    continue;
                }
                Console.ReadLine();
            }
        }
    }
}

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

namespace ConsoleApplication4.App_Code
{
    public class StudentData
    {
        //数据库定义变量
        SqlConnection conn = null;
        SqlCommand cmd = null;
        //构造函数
        public StudentData()
        {
            conn = new SqlConnection("server=.;database=ADO;user=sa;pwd=123456");
            cmd = conn.CreateCommand();
        }
        //查询全部方法创建
        public List<Student> SelectAll()
        {
            List<Student> slist = new List<Student>();
            cmd.CommandText = "select*from Student order by Code asc";
            try
            {
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Student st = new Student();
                    st.Code = dr["Code"].ToString();
                    st.Name = dr["Name"].ToString();
                    st.Sex = Convert.ToBoolean(dr["Sex"]);
                    st.Birthday = Convert.ToDateTime(dr["Birthday"]);
                    st.SubjectCode = dr["SubjectCode"].ToString();

                    slist.Add(st);
                }
            }
            catch
            {
                Student st = new Student();
                st.Code = "数据加载失败！！";
                st.Name = "StudentError500";
                slist.Add(st);
            }
            finally
            {
                conn.Close();
            }
            return slist;
        }
        //查询打印全部方法
        public void print()
        {
            Console.WriteLine("编号\t姓名\t性别\t年龄\t生日\t专业");
            //遍历数据打印
            StudentData st = new StudentData();
            List<Student> sd = st.SelectAll();
            int count = 0;
            int sum = 0;
            foreach (Student s in sd)
            {
                Console.WriteLine(s.Code + "\t" + s.Name + "\t" + s.Sexstr + "\t" + s.Age + "\t" + s.Birthdaystr + "\t" + s.SubjectName);
                count++;
                sum += s.Age;
            }
            double avg = sum / count;
            Console.WriteLine("统计：总人数为" + count + "人，平均年龄为" + avg + "岁。");
        }
        //编号查询打印方法
        public List<Student> selectcode(string a)
        {
            List<Student> slist = new List<Student>();
            cmd.CommandText = "select *from Student where Code=@Code";
            cmd.Parameters.AddWithValue("@Code", a);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                Student st = new Student();
                dr.Read();
                st.Code = dr["Code"].ToString();
                st.Name = dr["Name"].ToString();
                st.Sex = Convert.ToBoolean(dr["Sex"]);
                st.Birthday = Convert.ToDateTime(dr["Birthday"]);
                st.SubjectCode = dr["SubjectCode"].ToString();

                slist.Add(st);
            }
            conn.Close();
            return slist;
        }
        //查询是否存在编号
        public bool codesel(string a)
        {
            bool end = false;
            cmd.CommandText = "select *from Student where Code=@a";
            cmd.Parameters.AddWithValue("@a", a);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                end = true;
            }
            conn.Close();
            return end;
        }
        //添加
        public void insert(Student st)
        {
            cmd.CommandText = "insert into Student values(@a,@b,@c,@d,@e)";
            cmd.Parameters.AddWithValue("@a", st.Code);
            cmd.Parameters.AddWithValue("@b", st.Name);
            cmd.Parameters.AddWithValue("@c", st.Sex);
            cmd.Parameters.AddWithValue("@d", st.Birthday);
            cmd.Parameters.AddWithValue("@e", st.SubjectCode);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        //添加条件方法
        //姓名判断
        public string name()
        {
            string end = null;
            for (; ; )
            {
                Console.Write("请输入姓名（不能为空）：");
                string a = Console.ReadLine();
                if (a == null)
                {
                    Console.WriteLine("输入姓名有误请重新输入！");
                    continue;
                }
                else
                {
                    end = a;
                    break;
                }
            }
            return end;
        }
        //性别判断
        public bool sex()
        {
            bool end = false;
            for (; ; )
            {
                Console.Write("请输入性别（男/女）：");
                string a = Console.ReadLine();
                if (a == "男")
                {
                    end = true;
                    break;
                }
                else if (a == "女")
                {
                    break;
                }
                else
                {
                    Console.WriteLine("输入有误请重新输入！");
                }
            }
            return end;
        }
        //生日判断
        public DateTime birthday()
        {
            DateTime end = new DateTime();
            for (; ; )
            {
                Console.Write("请输入生日（如：2000/12/12）：");
                string a = Console.ReadLine();
                try
                {
                    end = Convert.ToDateTime(a);
                    break;
                }
                catch
                {
                    Console.WriteLine("输入生日有误请重新输入！");
                }
            }
            return end;
        }
        //专业判断
        public string subject()
        {
            string end = null;
            for (; ; )
            {
                Console.Write("请输入专业：");
                string a = Console.ReadLine();
                //调用方法判断专业s
                if (new SubjectData().selectsub(a))
                {
                    end = new SubjectData().selectsubC(a);
                    break;
                }
                else
                {
                    Console.WriteLine("没有此专业");
                }
            }
            return end;
        }
        //编号自动生成方法
        public string bianhao()
        {
            string a = null;
            cmd.CommandText = "select Code from Student order by Code desc";
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Read();
                string b = dr[0].ToString().Substring(1);
                a = "S" + ((Convert.ToInt32(b) + 1).ToString("000"));
            }
            conn.Close();
            return a;
        }
        //修改
        public void update(Student st)
        {
            cmd.CommandText = "update Student set Name=@b,Sex=@c,Birthday=@d,SubjectCode=@e where Code=@uCode";
            cmd.Parameters.AddWithValue("@uCode", st.Code);
            cmd.Parameters.AddWithValue("@b", st.Name);
            cmd.Parameters.AddWithValue("@c", st.Sex);
            cmd.Parameters.AddWithValue("@d", st.Birthday);
            cmd.Parameters.AddWithValue("@e", st.SubjectCode);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        //删除
        public void delete(string a)
        {
            cmd.CommandText = "delete from Student where Code=@dcode";
            cmd.Parameters.AddWithValue("@dcode",a);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }







    }
}

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

namespace ConsoleApplication4.App_Code
{
    public class Subject
    {
        private string _SubjectCode;

        public string SubjectCode
        {
            get { return _SubjectCode; }
            set { _SubjectCode = value; }
        }
        private string _SubjectName;

        public string SubjectName
        {
            get { return _SubjectName; }
            set { _SubjectName = value; }
        }
    }
}

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

namespace ConsoleApplication4.App_Code
{
    public class SubjectData
    {
        SqlConnection conn = null;
        SqlCommand cmd = null;
        public SubjectData()
        {
            conn = new SqlConnection("server=.;database=ADO;user=sa;pwd=123456;");
            cmd = conn.CreateCommand();
        }
        //由编号查询专业名称
        public string SubselectAll(string SubCode)
        {
            string end = "<暂无>";
            cmd.CommandText = "select *from Subject where SubjectCode = @a";
            cmd.Parameters.AddWithValue("@a",SubCode);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            try
            { 
                if (dr.HasRows)
                {
                    dr.Read();
                    end = dr["SubjectName"].ToString();
                }
            }
            catch { }
            finally { conn.Close(); }
            return end;
        }
        //由名称查询专业编号
        //是否有
        public bool selectsub(string bsub)
        {
            bool end = false;
            cmd.CommandText = "select SubjectCode from Subject where SubjectName like ‘%"+bsub+"%‘ ";
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                end = true;
            }
            conn.Close();
            return end;
        }
        //返回查询编号
        public string selectsubC(string ssub)
        {
            string end = "暂无";
            cmd.CommandText = "select SubjectCode from Subject where SubjectName like ‘%"+ssub+"%‘ ";
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Read();
                end = dr["SubjectCode"].ToString();
            }
            conn.Close();
            return end;
        }
    }
}

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

namespace ConsoleApplication4.App_Code
{
    public class Student
    {
        private string _Code;

        public string Code
        {
            get { return _Code; }
            set { _Code = value; }
        }
        private string _Name;

        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }
        private bool _Sex;

        public bool Sex
        {
            get { return _Sex; }
            set { _Sex = value; }
        }
        //性别扩展
        public string Sexstr
        {
            get { return _Sex ? "男" : "女"; }
        }
        private DateTime _Birthday;

        public DateTime Birthday
        {
            get { return _Birthday; }
            set { _Birthday = value; }
        }
        //生日扩展
        public string Birthdaystr
        {
            get { return _Birthday.ToString("yyyy年MM月dd日"); }
        }
        //年龄扩展
        public int Age
        {
            get { return DateTime.Now.Year - _Birthday.Year; }
        }
        private string _SubjectCode;

        public string SubjectCode
        {
            get { return _SubjectCode; }
            set { _SubjectCode = value; }
        }
        //扩展专业名称
        public string SubjectName
        {
            get { return new SubjectData().SubselectAll(_SubjectCode); }
        }
    }
}