15章上机123题

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

namespace ConsoleApplication2
{
   
         class DBOperation
    {
        private const string strConn = @"Data Source=.;Initial Catalog=MySchool;User ID=bdqn;Password=0";
        public bool CheckUserInfo(string userName,string pwd) {
            SqlConnection conn = new SqlConnection(strConn);
          try
          {
                string sql = @"SELECT count(*)FROM [MySchool].[dbo].[Admin] where LoginId='" + userName + "' and Loginpwd='" + pwd + "'";
                conn.Open();
                SqlCommand c = new SqlCommand(sql,conn);
                int i = (int)c.ExecuteScalar();
                if (i > 0)
                {
                    return true;
                }
              }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            conn.Close();
            return false;
        }
        public void cz() {
              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("======================================");
            int i=(int.Parse)(Console.ReadLine());
                  if(i==0){
                      break;
                  }
            switch(i){
                case 1: Console.WriteLine("查询学生人数:");
                  int s=GA();
                    if(s==-1){
                        Console.WriteLine("输入错误!");
                    }else{
                        Console.WriteLine(s);
                    }
                    break;
                case 2:
                    break;
                case 3: showname();
                    break;
                case 4: sname();
                    break;
                case 5:
                    break;
                case 6:
                    break;
                case 7: IG();
                    break;
                case 0: Console.WriteLine("已退出!!");
                    break;
                default:
                    continue;            }  
              }while(true);
                      
        }
        public int GA() {
            SqlConnection conn = new SqlConnection(strConn);
            string strSql = "SELECT COUNT(*)FROM [MySchool].[dbo].[Student]";
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(strSql, conn);
                int i = (int)comm.ExecuteScalar();
                return i;
            }
            catch (Exception)
            {                return -1;
            }
            finally {
                conn.Close();
            }
           
        }
        public string GB(string stuno) {
            SqlConnection conn = new SqlConnection(strConn);
            try
            {
                conn.Open();
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT ");
                sb.Append("   [StudentNo]");
                sb.Append("   ,[StudentName]");
                sb.Append("FROM");
                sb.Append("     [MySchool].[dbo].[Student]");
                sb.Append("WHERE ");
                sb.Append("     [StudentNo]=" + stuno);
                SqlCommand comm = new SqlCommand(sb.ToString(), conn);
                SqlDataReader r = comm.ExecuteReader();
                string stuname = string.Empty;
                if (r.Read())
                {
                    stuname = Convert.ToString(r["StudentName"]);
                }
                r.Close();
                return stuname;
            }
            catch (Exception)
            {
                return string.Empty;

            }
            finally
            {

                conn.Close();
            }
        }


        public void showname() {
            Console.WriteLine("输入学生学号:");
            string stuno = Console.ReadLine();
            string stuName = GB(stuno);
            if(stuName.Equals(string .Empty)){
                Console.WriteLine("出现异常!");
            }else{
                StringBuilder s = new StringBuilder();
                s.AppendFormat("学号是{0}的学生姓名为:{1}",stuno, stuName);
                Console.WriteLine(s);
            }
        }
        public SqlDataReader GR(string stuName) {
            try
            {
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                StringBuilder s = new StringBuilder();
                s.AppendLine(" select ");
                s.AppendLine("   A.[StudentNo]");
                s.AppendLine("   ,A.[studentName]");
                s.AppendLine("   ,A.[Sex]");
                s.AppendLine("   ,B.[GradeName]");
                s.AppendLine("   ,A.[Phone]");
                s.AppendLine("   ,A.[Address]");
                s.AppendLine("   ,A.[BornDate]");
                s.AppendLine("   ,A.[Email]");
                s.AppendLine(" FROM ");
                s.AppendLine("   [Student] as A,[Grade] as B");
                s.AppendLine(" WHERE ");
                s.AppendLine("   [StudentName] like '%" + stuName + "%'");
                s.AppendLine(" AND");
                s.AppendLine("   A.[GradeId]=B.[GradeId]");
                SqlCommand comm = new SqlCommand(s.ToString(), conn);
                return comm.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return null;
               
            }




        }
        public void sname() {
            StringBuilder sb = new StringBuilder();
            Console.WriteLine("输入学生姓名:");
            string stuNo = Console.ReadLine();
            SqlDataReader r = GR(stuNo);
            if(r==null){
                Console.WriteLine("出现异常!");
                return;
            }
            Console.WriteLine("******************************************");
            Console.WriteLine("学号\t姓名\t性别\t年级\t联系电话\t地址\t\t出生日期\t\t邮箱");
            while(r.Read()){
            sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\t{7}"
                ,r["StudentNo"],r["StudentName"],r["Sex"],r["GradeName"],r["Phone"],r["Address"],r["BornDate"],r["Email"]);
                Console.WriteLine(sb.ToString());
                sb.Length=0;
            }
            Console.WriteLine("******************************************");
            r.Close();

        }
        public int ig(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;
            }
            finally
            {
                conn.Close();
            }
        }
        public void IG() {
            Console.WriteLine("请输入待插入的年级名称:");
            string gradeName = Console.ReadLine();
            int i = ig(gradeName);
            if(i==1){
                Console.WriteLine("已存在改年级记录,插入失败!");
            }
            else{
                Console.WriteLine("插入成功!");
            }


        }

             
         }

}





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

namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            DBOperation s = new DBOperation();
            Console.WriteLine("请输入账号:");
            string a = Console.ReadLine();
            Console.WriteLine("请输入密码:");
            string c = Console.ReadLine();

            bool i = s.CheckUserInfo(a, c);
            if (i)
            {
                Console.WriteLine("登陆成功");
            }
            else
            {
                Console.WriteLine("登陆失败");
            }
            s.cz();
            s.GA();
            Console.ReadLine();


        }
    }
}