数据库第十五章上机1

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
namespace ConsoleApplication4
{
    class Program
    {
        private string conn = "Data Source=.;Initial Catalog=MySchool;Integrated Security=True";

        static void Main(string[] args)
        {
            Program a = new Program();
            a.showwww();
            Console.ReadLine();
        }

        public bool show(string name, string pass)
        {
            SqlConnection con = new SqlConnection(conn);
            try
            {
                con.Open();
                string ss = @"SELECT COUNT(*) AS 个数 FROM [MySchool].[dbo].[Admin]
                 where LoginId= '" + name + "' and LoginPwd='" + pass + "'";
                SqlCommand comm = new SqlCommand(ss, con);
                int i = (int)comm.ExecuteScalar();
                if (i > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                con.Close();
            }

            return false;
        }

        public void showw()
        {
            do
            {
                Console.WriteLine("=======请选择操作键======");
                Console.WriteLine("1. 统计学生人数");
                Console.WriteLine("2. 查看学生名单");
                Console.WriteLine("3. 按学号查询学生姓名");
                Console.WriteLine("4. 按姓名查询学生信息");
                Console.WriteLine("5. 修改学生出生日期");
                Console.WriteLine("6. 删除学生记录");
                Console.WriteLine("0. 退出");
                Console.WriteLine("=========================");
                Console.WriteLine("请选择:");
                int num = int.Parse(Console.ReadLine());
                switch (num)
                {
                    case 1:
                        showww();
                        continue;
                    case 2:
                        Console.WriteLine("2. 查看学生名单");
                        continue;
                    case 3:
                        xue();
                        continue; ;
                    case 4:
                        show222();
                        continue;
                    case 5:
                        Console.WriteLine("5. 修改学生出生日期");
                        continue;
                    case 6:
                        Console.WriteLine("6. 删除学生记录");
                        continue;
                    case 0:
                        Console.WriteLine("感谢使用!");
                        break;
                    default:
                        Console.WriteLine("输入错误");
                        continue;
                }
                break;
            } while (true);

        }
        public void showww()
        {
            SqlConnection con = new SqlConnection(conn);

            try
            {
                con.Open();
                string sss = " SELECT COUNT(*)  FROM [MySchool].[dbo].[Student]";
                SqlCommand comm = new SqlCommand(sss, con);
                int i = (int)comm.ExecuteScalar();
                if (i > 0)
                {
                    Console.WriteLine("人数为:" + i + "个");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

            }
            finally
            {
                con.Close();
            }
        }

        public void showwww()
        {
            Console.WriteLine("输入用户名:");
            string name = Console.ReadLine();
            Console.WriteLine("请输入密码:");
            string mima = Console.ReadLine();
            bool b = show(name, mima);
            if (b)
            {
                Console.WriteLine("登陆成功");
                showw();
            }
            else
            {
                Console.WriteLine("登录失败");
            }

        }
        public string Getgg(string stuno)
        {
            SqlConnection con = new SqlConnection(conn);
            try
            {
                con.Open();
                StringBuilder sb = new StringBuilder();
                sb.Append("select * from Student");
                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;

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return string.Empty;
            }
            finally
            {
                con.Close();
            }
        }
        public void xue()
        {
            Console.WriteLine("请输入学号:");
            string xuehao = Console.ReadLine();
            string stuname = Getgg(xuehao);
            if (stuname.Equals(string.Empty))
            {
                Console.WriteLine("出现异常!");
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                Console.WriteLine("学号" + "\t\t" + "姓名");
                Console.WriteLine(xuehao + "\t\t" + stuname);
            }

        }
        public string anXingMing(string stuName)
        {
            try
            {
                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);
                SqlDataReader reader = comm.ExecuteReader();
                string stuName1 = string.Empty;/Empty  为NULL值

                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);
        }
    }
}