十五章上机代码

 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 str = @"Data Source=.;Initial Catalog=MySchool;Integrated Security=True";

        static void Main(string[] args)
        {
            Program p = new Program();

            p.ss();
            p.caidan();
           
            Console.ReadLine();

        }
        public void ss()
        {
            Console.WriteLine("请输入登录名:");
            string userName = Console.ReadLine();
            Console.WriteLine("请输入密码:");
            string pwd = Console.ReadLine();
            string strMsg = "用来保存信息";

            bool y = sss(userName, pwd, ref strMsg);

            if (y)
            {
                Console.WriteLine(strMsg);
            }
            else
            {
                Console.WriteLine("错误");
            }

        }
        public void caidan()
        {  
             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("8.退出");
                Console.WriteLine("====================================");
                string  q = Console.ReadLine();
                switch (q)
                {
                    case "1":
                        GetudentAmount();
                        caidan();
                        break;
                    case "2":
                        GetStudentList();
                        caidan();
                        break;
                    case "3":
                        ShowStudentName();
                        caidan();
                        break;
                    case "4":
                        ShowStudentInfoByName();
                        caidan();
                        break;
                    case "5":
                        UpdateStuBornDate();
                        caidan();
                        break;
                    case "6":
                        DeleteStuInfo();
                        caidan();
                        break;
                    case "7":
                        InsertNewGrade();
                        caidan();
                        break;
                    case "8":
                            break;
                }
           
        }
      
    
        public bool sss(string userName, string pwd, ref string strMsg)
        {

            SqlConnection c = new SqlConnection(str);

            try
            {
                c.Open();
                string r = "SELECT COUNT(*) FROM [MySchool].[dbo].[Admin] where LoginId='" + userName + "'and LoginPwd='" + pwd + "'";
                Console.WriteLine(r);
                SqlCommand e = new SqlCommand(r, c);
                int i = (int)e.ExecuteScalar();
                if (i > 0)
                {
                    strMsg = "登陆成功,确实有这个信息";
                    return true;
                }
            }
            catch (Exception ex)
            {
                strMsg = "登录失败";
                Console.WriteLine(ex.Message);
                return false;
            }
            finally
            {
                Console.WriteLine("苏琳琳大美女");
                c.Close();
            }
            return false;

        }
        //case 1  上机5
        public int GetudentAmount()
        {
            SqlConnection conn = new SqlConnection(str);
            try
            {
                string strsql = "select count(*) from Student";
                conn.Open();
                SqlCommand comm = new SqlCommand(strsql, conn);
                int iret = (int)comm.ExecuteScalar();
                Console.WriteLine("学生人数为："+iret);
                return iret;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return -1;
            }
            finally
            {
                conn.Close();
            }
        }


        //case 2  示例
        public void GetStudentList()
        {
          
            SqlConnection b = new SqlConnection(str);

            try
            {

                b.Open();

                StringBuilder sb = new StringBuilder();
                sb.Append(" SELECT ");
                sb.Append(" * ");
                sb.Append(" FROM ");
                sb.Append(" Student ");
                SqlCommand comm = new SqlCommand(sb.ToString(), b);
                SqlDataReader ready = comm.ExecuteReader();

                Console.WriteLine("---------------------");
                Console.WriteLine("学号\t  姓名");
                Console.WriteLine("---------------------");
                StringBuilder sb1 = new StringBuilder();
                while (ready.Read())
                {
                    Console.WriteLine(ready["StudentNo"] + "    " + ready["StudentName"]);
                    sb1.Length = 0;
                }
                Console.WriteLine("---------------------------------");


            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

            }
            finally
            {
                Console.WriteLine("肯定输入");
                b.Close();
            }
        }
        //case 3  上机1
        public string GetStudentNameByNo(string stuNo)
        {
         
            SqlConnection conn = new SqlConnection(str);
            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);
                SqlCommand comm = new SqlCommand(sb.ToString(), conn);
                SqlDataReader reader = comm.ExecuteReader();
                string stuName = string.Empty;
                if (reader.Read())
                {
                    stuName = Convert.ToString(reader["studentName"]);
                }
                reader.Close();
                return stuName;

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return string.Empty;


            }
            finally
            {
                conn.Close();
            }

        }
        public void ShowStudentName()
        {
            Console.WriteLine("请输入学生学号:");
            string stuNo = Console.ReadLine();
            string stuName = GetStudentNameByNo(stuNo);
            if (stuName.Equals(string.Empty))
            {
                Console.WriteLine("出现异常");
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("学号{0}的学生姓名为:{1}", stuNo, stuName);
                Console.WriteLine(sb);
            }
        }

        //case 4  上机2
        public SqlDataReader GetstudentInfoByName(string stuName)
        {
           
            SqlConnection conn = new SqlConnection(str);
            try
            {
               
                conn.Open();
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("SELECT");
                sb.AppendLine("               A.[StudentNo]");
                sb.AppendLine("               ,A.[StudentName]");
                sb.AppendLine("               ,A.[Sex]");
                sb.AppendLine("               ,A.[GradeId]");
                sb.AppendLine("              ,A.[Phone]");
                sb.AppendLine("              , A.[Address]");
                sb.AppendLine("               ,A.[BornDate]");
                sb.AppendLine("               ,A.[Email]");
                sb.AppendLine("FROM");
                sb.AppendLine("               [Student] as A,[Grade] as B");
                sb.AppendLine(" WHERE");
                sb.AppendLine("               [StudentName] like '%" + stuName + "%'");
                sb.AppendLine(" and ");
                sb.AppendLine("               A.[GradeId]=B.[GradeId]");
                SqlCommand comm = new SqlCommand(sb.ToString(), conn);
                return comm.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception a)
            {
                Console.WriteLine(a.Message);
                return null;
            }

        }

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

            }
            Console.WriteLine("-------------------------------------");
            reader.Close();
        }

       //上机3

        public int InserNewGrade(string gradeName) {
            SqlConnection conn = new SqlConnection(str);
            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 e)
            {

                Console.WriteLine(e.Message);
                return-1;
            }
            conn.Close();
        }
        public void InsertNewGrade(){

            Console.WriteLine("请输入待插入的年级名称");
            string gradeName = Console.ReadLine();
            int iret = InserNewGrade(gradeName);
            if (iret == -1)
            {
                Console.WriteLine("出现异常");
            }
            else
            {
                Console.WriteLine("插入成功");
            }
        }

        //上机4
        public int UpdateStuBornDate(string bornDate,string stuNo) {

                 SqlConnection conn = new SqlConnection(str);
            try
            {
                
                conn.Open();
                StringBuilder sb = new StringBuilder();
                sb.AppendLine(" UPDATE ");
                sb.AppendLine("     [Student] ");
                sb.AppendLine("  SET  ");
                sb.AppendLine("     [BornDate]='" + bornDate + "'");
                sb.AppendLine("  WHERE  ");
                sb.AppendLine("     [StudentNo]= " + stuNo);
                SqlCommand comm = new SqlCommand(sb.ToString(),conn);
                return comm.ExecuteNonQuery();
            }
            catch (Exception d)
            {
                Console.WriteLine(d.Message);
                return -1;

            }
            finally {
                conn.Close();
            }
        }

        public void UpdateStuBornDate() {
            try
            {
                Console.WriteLine("请输入学号：");
                string stuNo = Console.ReadLine();
                Console.WriteLine("请输入修改后的生日（xxxx-xx-xx）：");
                string stuBornDate = Console.ReadLine();
                DateTime dtStuDate = Convert.ToDateTime(stuBornDate);
                int iret = UpdateStuBornDate(stuBornDate, stuNo);
                if (iret == -1)
                {

                    Console.WriteLine("异常发生");
                }
                else {
                    Console.WriteLine("修改成功");
                }
            }
            catch (Exception f)
            {
                Console.WriteLine(f.Message);
                Console.WriteLine("输入错误");
                
            }
        
        }

        //上机5
        public int DeleteStudentInfo(string stuNo) {

            SqlConnection conn = new SqlConnection(str);
            try
            {
                conn.Open();
                //DeleteStudentrefkey(stuNo);
                StringBuilder sb = new StringBuilder();
                sb.AppendLine(" DELETE FROM");
                sb.AppendLine("             [Student]");
                sb.AppendLine(" WHERE ");
                sb.AppendLine("             [StudentNo]='" + stuNo + "'");
                SqlCommand comm = new SqlCommand(sb.ToString(), conn);
                return comm.ExecuteNonQuery();
            }
            catch (Exception a)
            {
                Console.WriteLine(a.Message);
                return -1;
            }
            finally {
                conn.Close();
            }
        }
        public void DeleteStuInfo()
        {
            Console.WriteLine("请输入学号：");
            string stuNo = Console.ReadLine();
            string stuName = GetStudentNameByNo(stuNo);
            Console.WriteLine("确实要删除学号为{0}，姓名是{1}的学生记录吗？（y/n）", stuNo, stuName);
            string result = Console.ReadLine();
            if (!result.ToUpper().Equals("y"))
            {
                int iret = DeleteStudentInfo(stuNo);
                if (iret == -1)
                {

                    Console.WriteLine("删除失败");
                }
                else
                {
                    Console.WriteLine("删除成功");
                }

            }
        }

    }
}