第十五章

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;//SQL SEVER .NET 数据程序提供程序命名空间

namespace 第十五章
{
    class Program
    {

        private const string strconn = @"Data Source=.;Initial Catalog=MySchool;
                        Integrated Security=True";
        static void Main(string[] args)
        {

            Program p = new Program();
            p.delu();



        }
        public bool chech(string name, string pwd, ref string Msg)
        {
            SqlConnection conn = new SqlConnection(strconn);//局部变量
            try
            {
                // SqlConnection conn = new SqlConnection(strconn);成员变量
                conn.Open();
                string str = @"SELECT COUNT(*) FROM [MySchool].[dbo].[Admin]
                        where LoginId='" + name + "'and LoginPwd='" + pwd + "'";
                Console.WriteLine(str);
                SqlCommand comm = new SqlCommand(str, conn);
                int o = (int)comm.ExecuteScalar(); //强制转换为 int 类型
                if (o > 0)
                {
                    Msg = "登陆成功";//引用传参 可以不写
                    return true;
                }
                Console.WriteLine(o);
            }
            catch (Exception ex)
            {

                Console.WriteLine("出现异常" + ex.Message);

            }
            finally
            {
                conn.Close();
            }

            return false;
        }
        public bool delu() 登陆账户和密码
        {
            Console.WriteLine("请输入账号:");
            string a = Console.ReadLine();
            Console.WriteLine("请输入密码:");
            string C = Console.ReadLine();
            string Msg = " ";

            bool b = chech(a, C, ref Msg);
            if (b)
            {
                show();
                Console.WriteLine(Msg);
                return true;


            }
            else
            {
                Console.WriteLine("你登陆失败!!!");
                Console.WriteLine("请重新输入");
                return false;
            }


        }
        public void show() /创建一个菜单//
        {
            do
            {
                Console.WriteLine("=======请选择操作键======");
                Console.WriteLine("1. 统计学生人数");
                Console.WriteLine("2. 查看学生名单");
                Console.WriteLine("3. 按学号查询学生姓名");
                Console.WriteLine("4. 按姓名查询学生信息");
                Console.WriteLine("5. 修改学生出生日期");
                Console.WriteLine("6. 删除学生记录");
                Console.WriteLine("7. 新增年级记录");
                Console.WriteLine("0. 退出");
                Console.WriteLine("=========================");
                Console.WriteLine("请选择:");
                int num = int.Parse(Console.ReadLine());
                switch (num)
                {
                    case 1:
                        show1();//统计学生人数
                        continue;
                    case 2:
                        chaxunxuesheng();//查看学生名单"
                        continue;
                    case 3:
                        show111();//按学号查询学生姓名
                        continue; ;
                    case 4:
                        show222();//按姓名查
                        continue;
                    case 5:
                        shengri();//修改生日
                        continue;
                    case 6:
                        shanchu();//删除记录
                        continue;
                    case 7:
                        tianjia();//新增年级记录
                        continue;
                    case 0:

                        break;
                    default:
                        Console.WriteLine("输入错误");
                        continue;
                }
                break;
            } while (true);


        }
        public void show1() /查询学生人数
        {
            SqlConnection conn = new SqlConnection(strconn);
            try
            {
                string str = "select count(*) from Student";
                conn.Open();
                SqlCommand comm = new SqlCommand(str, conn);
                int i = (int)comm.ExecuteScalar();
                Console.WriteLine("学生总人数为:" + i);

            }
            catch (Exception)
            {

                Console.WriteLine("出现异常");
            }
            finally
            {
                conn.Close();
            }

        }
        public void chaxunxuesheng() /查询学生名单
        {
            SqlConnection conn = new SqlConnection(strconn);
            try
            {
                conn.Open();
                StringBuilder sb = new StringBuilder();
                sb.AppendLine(" select ");
                sb.AppendLine(" * ");
                sb.AppendLine("  from ");
                sb.AppendLine("  student");
                SqlCommand comm = new SqlCommand(sb.ToString(), conn);

                SqlDataReader re = comm.ExecuteReader();
                while (re.Read())
                {
                    Console.WriteLine(re["studentno"] + " " + re["studentname"]);

                }
                re.Close();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
        }
        public string anxuehao(string stuNo)//按学号查询学生姓名!!!!
        {
            SqlConnection conn = new SqlConnection(strconn);
            try
            {
                conn.Open();
                StringBuilder sb = new StringBuilder();
                sb.AppendLine(" select ");
                sb.AppendLine(" studentNo,  ");
                sb.AppendLine("   studentName  ");
                sb.AppendLine(" from   ");
                sb.AppendLine("    student ");
                sb.AppendLine(" where ");
                sb.AppendLine("  studentNo= " + stuNo);
                //Console.WriteLine(sb.ToString());
                //Console.ReadLine();
                SqlCommand comm = new SqlCommand(sb.ToString(), conn);
                SqlDataReader reader = comm.ExecuteReader();
                string stuName = string.Empty;/Empty  为NULL值
                if (reader.Read())
                {
                    stuName = Convert.ToString(reader["studentName"]);
                }
                reader.Close();
                return stuName;
            }
            catch (Exception)
            {

                return string.Empty;
            }
        }
        public void show111() //输出查询按学号查询学生姓名!!!!
        {
            Console.WriteLine("请输入学生学号:");
            string stuNo = Console.ReadLine();
            string stuName = anxuehao(stuNo);
            Console.WriteLine("学号是{0}的学生姓名为:{1}", stuNo, stuName);
        }
        public string anXingMing(string stuName)
        {
            try
            {
                SqlConnection conn = new SqlConnection(strconn);
                conn.Open();
                StringBuilder sb = new StringBuilder();
                //这是一个包含模糊查询的SQL语句
                sb.AppendLine(" select ");
                sb.AppendLine("  studentNO,  ");
                sb.AppendLine("      studentName  ");
                sb.AppendLine("      ,sex ");
                sb.AppendLine("    ,gradeName ");
                sb.AppendLine("   ,phone ");
                sb.AppendLine("  , address  ");
                sb.AppendLine("  ,bornDate ");
                sb.AppendLine("  ,email ");
                sb.AppendLine("  from ");
                sb.AppendLine("  student,grade ");
                sb.AppendLine(" where  ");
                sb.AppendLine("  studentName like '%" + stuName + "%'");
                sb.AppendLine(" and  ");
                sb.AppendLine("   student.gradeid=grade.gradeid ");
                // Console.WriteLine(sb.ToString());
                SqlCommand comm = new SqlCommand(sb.ToString(), conn);
                SqlDataReader reader = comm.ExecuteReader();
                string stuName1 = string.Empty;/Empty  为NULL值
                //Console.WriteLine("学号\t姓名\t性别\t年级\t电话\t地址\t日期\t邮箱\t");
                while (reader.Read())
                {
                    Console.WriteLine("学号\t姓名\t性别\t年级\t电话\t地址\t日期\t邮箱\t");
                    stuName1 = Convert.ToString(reader["studentno"] + "\t" + reader["studentname"] + "\t" +
                       reader["sex"] + "\t" + reader["gradeName"] + "\t" + reader["phone"] + "\t" + reader["address"]
                       + "\t" + reader["borndate"] + "\t" + reader["email"] + "\t");
                    Console.WriteLine(stuName1);
                }

                Console.ReadLine();
                comm.Clone();
                return stuName1;

            }
            catch (Exception e)
            {

                Console.WriteLine(e.Message);

            }
            return stuName;
        }
        public void show222()
        {
            Console.WriteLine("请输入学生姓名:");
            string s = Console.ReadLine();
            string stuName = anXingMing(s);
            Console.WriteLine(stuName);
        }
        public int InsertGrade(string gradeName)
        {
            SqlConnection conn = new SqlConnection(strconn);
            try
            {
                conn.Open();
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("INSERT INTO");
                sb.AppendLine("        [Grade]");
                sb.AppendLine("VALUES");
                sb.AppendLine("         ('" + gradeName + "')");
                SqlCommand comm = new SqlCommand(sb.ToString(), conn);
                return comm.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return -1;
            }
            conn.Close();
        }
        public void tianjia()
        {
            Console.WriteLine("请输入待插入的年级名称:");
            string s = Console.ReadLine();
            int i = InsertGrade(s);
            if (i == 1)
            {
                Console.WriteLine("插入失败");
                Console.WriteLine("年级已经存在");
            }
            else 
            {
                Console.WriteLine("插入成功");
            }
        }
        public void shengri()
        {
            Console.WriteLine("请输入学号:");
            string StudentNo = Console.ReadLine();
            Console.WriteLine("请输入修改后的生日:");
            string riqi = Console.ReadLine();
            string s = "Data Source=.;Initial Catalog=MySchool;Integrated Security=True";
            SqlConnection a = new SqlConnection(s);
            a.Open();
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("update Student set BornDate='" + riqi + "' where StudentNo='" + StudentNo + "'");
                SqlCommand m = new SqlCommand(sb.ToString(), a);
                int i = m.ExecuteNonQuery();
                if (i == 0)
                {
                    Console.WriteLine("修改失败");
                }
                else
                {
                    Console.WriteLine("修改成功");
                }
                Console.ReadLine();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                a.Close();
            }
        }
        public void shanchu()
        {
            Console.WriteLine("请输入学号:");
            string studentNo = Console.ReadLine();
            string s = "Data Source=.;Initial Catalog=MySchool;Integrated Security=True";
            SqlConnection a = new SqlConnection(s);
            a.Open();
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("delete from Student where StudentNo='" + studentNo + "'");
                SqlCommand m = new SqlCommand(sb.ToString(), a);
                int i = m.ExecuteNonQuery();
                if (i == 0)
                {
                    Console.WriteLine("删除失败");
                }
                else
                {
                    Console.WriteLine("删除成功");
                }
                Console.ReadLine();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                a.Close();
            }
        }  

    }
}