十五章 上机练习 1,2,3,

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

namespace ConsoleApplication1
{
    class Program
    {
        private const string sql = "Data Source=.;Initial Catalog=MySchool;Integrated Security=True";

        StringBuilder sb = new StringBuilder(); 
        SqlConnection coon = new SqlConnection(sql);//链接数据量库 
     
        static void Main(string[] args)
        {

            Program p = new Program();

            Console.WriteLine("请输入用户名:");
            string name = Console.ReadLine();
            Console.WriteLine("请输入密码:");
            string pwd = Console.ReadLine();

            string aa = "";
            bool b = p.show("Admin", "0", ref aa);// admin 直接输入用户名  //返回值b
            if (b)
            {
                Console.WriteLine(aa);
                p.menu();
            }
            else
            {
                Console.WriteLine(aa);
            }
            Console.ReadLine();


        }
    
       public bool show(string name, string pwd, ref string aa)
        {
            SqlConnection connection = new SqlConnection(sql);//链接数据量库
           
            try
            {
               connection.Open();//打开数据库               
               Console.WriteLine("打开数据库");           
               sb.AppendLine(" SELECT ");
               sb.AppendLine(" * ");
               sb.AppendLine(" FROM ");
               sb.AppendLine(" [student] ");

//                string sql1 = @"SELECT COUNT(*)FROM [MySchool].[dbo].[Admin]
//                   where Loginid='" + name + "'and Loginpwd='" + pwd + "'";

                Console.WriteLine(sb);//输出链接

                SqlCommand bb = new SqlCommand(sb.ToString(), connection);//写内容 command语句
                                                                
                int a = (int)bb.ExecuteScalar();//强制转换

                if (a > 0)//如果有内容 大于0 
                {
                    aa = "登陆成功!";
                    return true;
                }
            }

            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine("数据库有问题");
            }


            finally
            {
                connection.Close();
                Console.WriteLine("关闭数据库");
            }

            aa = "登陆失败!";
            return false;

        }
       public int getstu()
       {
           SqlConnection cc = new SqlConnection(sql);

           try
           {
               string sql2 = "select count(*) from student";
               cc.Open();
               SqlCommand comm = new SqlCommand(sql2, cc);
               int iRet = (int)comm.ExecuteScalar();
               return iRet;
           }
           catch (Exception)
           {
               return -1;
           }
           finally
           {
               cc.Close();

           }

       }
       public void getlist()//获得学生名单
       {
           coon.Open();//打开数据库     
               StringBuilder sb1 = new StringBuilder();//循环读取DataReader
               sb1.AppendLine(" SELECT ");
               sb1.AppendLine(" [studentNo] ");
               sb1.AppendLine(" ,[studentName] ");
               sb1.AppendLine(" FROM ");
               sb1.AppendLine(" [student] ");
               SqlCommand bb = new SqlCommand(sb1.ToString(), coon);//写内容 command语句                
               SqlDataReader reader = bb.ExecuteReader();

               Console.WriteLine("------------------------");
               Console.WriteLine("学号\t  姓名");
               Console.WriteLine("------------------------");


               try
               {
                   while (reader.Read())
                   {
                       Console.WriteLine(reader["StudentNo"] + " " + reader["StudentName"]);
                   } reader.Close();
               }
               catch (Exception e)
               {

                   Console.WriteLine(e.Message);
               }
               finally
               {

                   coon.Close();
               }
      
       }

       public void getNo() {//按学号查找 学生姓名


           try
           {
               coon.Open();

               Console.WriteLine("请输入学号:");
               string hao = Console.ReadLine();
               
               StringBuilder sb2 = new StringBuilder();
               sb2.AppendLine(" SELECT ");
               sb2.AppendLine(" [studentNo] ");
               sb2.AppendLine(" ,[studentName] ");
               sb2.AppendLine(" FROM ");
               sb2.AppendLine(" [student] ");
               sb2.AppendLine(" WHERE ");
               sb2.AppendLine(" [studentNo]= " + hao);

               SqlCommand cc = new SqlCommand(sb2.ToString(), coon);//写内容 command语句 

               SqlDataReader reader = cc.ExecuteReader();
               string stuName = string.Empty;
               if (reader.Read())
               {
                   stuName = Convert.ToString(reader["studentName"]);
                   Console.WriteLine("学号是"+hao+"的学生姓名是:"+stuName);
               }
               reader.Close();

           }
           catch (Exception ex)
           {

               Console.WriteLine(ex.Message);
           }

           finally
           {
               coon.Close();
           }                                
       }
       public SqlDataReader getName(string stuName){

           try
           {
               coon.Open();
               StringBuilder sb3 = new StringBuilder();
               sb3.AppendLine(" SELECT ");
               sb3.AppendLine("   A.[StudentNo] ");
               sb3.AppendLine("  ,A.[StudentName] ");
               sb3.AppendLine("  ,A.[Sex] ");
               sb3.AppendLine("  ,B.[GradeName] ");
               sb3.AppendLine("  ,A.[Phone] ");
               sb3.AppendLine("  ,A.[BornDate] ");
               sb3.AppendLine("  ,A.[Email] ");
               sb3.AppendLine("  ,A.[Address] ");
               sb3.AppendLine(" from ");
               sb3.AppendLine("  [Student] as A,[Grade] as B");
               sb3.AppendLine(" Where ");
               sb3.AppendLine(" [StudentName] like '%" + stuName + "%'");
               sb3.AppendLine(" and ");
               sb3.AppendLine(" A.[GradeId] =B.[GradeId]");
               SqlCommand com = new SqlCommand(sb3.ToString(), coon);
               return com.ExecuteReader(CommandBehavior.CloseConnection);
           }
           catch (Exception e)
           {

               return null;
           }
       }
           public void getshowname(){ 
            StringBuilder sb3 = new StringBuilder();

            Console.WriteLine("请输入姓名:");
            string ming = Console.ReadLine();
            SqlDataReader reader = getName(ming);
            if(reader==null){
                Console.WriteLine("出现异常!!!");
                return;
            }
            Console.WriteLine("-----------------------------------------");
            Console.WriteLine("学号\t姓名\t性别\t年级\t联系电话\t地址\t出生日期\t邮箱");
               while(reader.Read()){
                   sb3.AppendFormat("{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"]);

                   Console.WriteLine(sb3.ToString());
               sb3.Length=0;             
               }
                  Console.WriteLine("-----------------------------------------");
               reader.Close();
           }         
       

        public void menu()
        {
            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("==============================================");
            
            int choice = Convert.ToInt16(Console.ReadLine());
            switch (choice)
            {
                case 1:
                    int shu = getstu();
                    if (shu == -1)
                    {
                        Console.WriteLine("输入有误");
                    }
                    else
                    {
                        Console.WriteLine("在校学生为" + shu + "人");
                    }
                    menu();
                    break;
                case 2:
                    getlist();
                    menu();
                    break;
                case 3:
                    getNo();
                    menu();
                    break;
                case 4:
                    getshowname();
                    menu();
                    break;                                          
                case 7:
                    //add();
                    //menu();
                    break;
                case 0:
                    break;
                default:
                    Console.WriteLine("输入错误!!!");
                    break;
            }
        }  
    }
}