第十五章

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace MySchoolBase
{
    class DBOperation
    {
        //连接字符串  
        private const string conn = "Data Source=.;Initial Catalog=MySchool;Integrated Security=True";
        SqlConnection con = new SqlConnection(conn);
      
        static void Main(string[] args)
        {
              DBOperation dno = new DBOperation();
            dno.login();


            Console.ReadLine();
        }
        public void login()
        {
            Console.WriteLine("请输入用户名");
            string name = Console.ReadLine();
            Console.WriteLine("请输入密码");
            string mima = Console.ReadLine();
            bool r = Check(name, mima);
            if (r)
            {

                Console.WriteLine("登录成功");
                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: 退出");
                int n = int.Parse(Console.ReadLine());
                switch (n)
                {
                    case 1:
                        Amout();
                        break;
                    case 2:
                    case 3:
                        show();
                        break;
                    case 4:
                        show2();
                        break;
                    case 0:
                        break;

                }


            }
            else
            {

                Console.WriteLine("登录失败");


            }
        }
        public SqlDataReader show1(string stuName)
        {
            SqlConnection con = new SqlConnection(conn);
            con.Open();
            StringBuilder sb = new StringBuilder();
            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]");
            SqlCommand comm = new SqlCommand(sb.ToString(), con);
            return comm.ExecuteReader(CommandBehavior.CloseConnection);
        }
        public void show2()
        {
            StringBuilder sb = new StringBuilder();
            Console.WriteLine("请输入学生姓名");
            string stuno = Console.ReadLine();
            SqlDataReader reader =show1(stuno);
            if(reader==null)
            {
                Console.WriteLine("出现异常!");
                return;
            }
            Console.WriteLine("学号\t姓名\t性别\t年级\t联系电话\t地址\t出生日期\t邮箱");
            while(reader.Read())
            {
                Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\t{7}",reader["StudentNo"],reader["StudentName"],reader["Sex"]
                ,reader["GradeName"],reader["Phone"],reader["Address"],reader["BornDate"],reader["Email"]    );
            }
            reader.Close();
        }
        public string byName(string sutno)
        {
            SqlConnection con = new SqlConnection(conn);
            con.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]="+ sutno);
            SqlCommand comm = new SqlCommand(sb.ToString(),con);
            SqlDataReader reader = comm.ExecuteReader();
            string stuName = string.Empty;
            if(reader.Read())
            {
                stuName = Convert.ToString(reader["StudentName"]);
            }
            reader.Close();
            return stuName;
        }
        public void show()
        {
            Console.WriteLine("请输入学号");
            string sutno = Console.ReadLine();
            string stuname =byName(sutno);
            Console.WriteLine("学号是{0}的学生姓名{1}",sutno,stuname);
        }
        public bool Check(string name, string mima)
        {
            //创建数据库  
            SqlConnection con = new SqlConnection(conn);
            try
            {
                string stra = "SELECT COUNT(*) FROM Admin where LoginId='" + name + "' and LoginPwd='" + mima + "'";
                con.Open();
                SqlCommand comm = new SqlCommand(stra, con);
                int i = (int)comm.ExecuteScalar();


            }
            catch (Exception ex)
            {

                Console.WriteLine("出现异常" + ex.Message);
            }
            finally
            {
                con.Close();
                Console.WriteLine("关闭数据库");
            }
            return true;
        }
        public int Amout()
        {

            try
            {
                string stra = "SELECT COUNT(*) FROM Student";
                con.Open();
                SqlCommand comm = new SqlCommand(stra, con);
                int i = (int)comm.ExecuteScalar();
                Console.WriteLine("在校学生人数" + i);
                return i;
            }
            catch (Exception)
            {

                return -1;
            }
            con.Close();
        }
    }
}