C#第十四章使用ADO.NET查询和操作数据上机练习1-5

C#第十四章上机练习1-5

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

namespace DataReader
{
          
   
    class Program
    {
          
   
        static void Main(string[] args)
        {
          
   
            Class1 s = new Class1();  
            Console.WriteLine("请输入用户名:");
            string name = Console.ReadLine();   //输入用户名
            Console.WriteLine("请输入密码:");
            string pass = Console.ReadLine();   //输入密码
            string mess = "";       
            //调用方法并传实参
            s.UserBool(name, pass, ref mess);

            Console.ReadLine();
        }
    }
}
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataReader
{
          
   
    class Class1
    {
          
        
        string sql = "Data Source=.;Initial Catalog=MySchool;User ID=teng;Password=1314";     
        #region 显示菜单
        public void shwo()
        {
          
   
            int num = 0; 
            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.退出");
                num = int.Parse(Console.ReadLine());      
                switch (num)
                {
          
   
                    case 1:
                        GetStudentAmost();      
                        continue;
                    case 2:
                        GetStudnetTwo();
                        continue;
                    case 3:
                        ShowStudentName();
                        continue;
                    case 4:
                        ShowStudentInfoByName();
                        continue;
                    case 5:
                        UpdateStuBornDate();
                        continue;
                    case 6:
                        ShwoShanChu();
                        continue;
                    case 7:
                        InputGrade();
                        continue;
                    case 0:
                        break;
                    default:
                        Console.WriteLine("输入无效!");
                        continue;
                }
                break;
            } while (true);        
        }
        #endregion  
        #region 登录密码
        public bool UserBool(string name, string pass, ref string mess)
        {
          
           
            SqlConnection cn = new SqlConnection(sql);
            try
            {
          
             
                string strSql = "select COUNT(*) from Admin where LoginId=" + name + " and LoginPwd=" + pass + "";       
                cn.Open();             
                SqlCommand cm = new SqlCommand(strSql, cn);
                int iRet = (int)cm.ExecuteScalar(); 
                if (iRet == 1)
                {
          
   
                    mess = "登陆成功!";
                    Console.WriteLine(mess);
                    shwo();     
                    return true;
                }
                else
                {
          
   
                    mess = "登录失败!";
                    Console.WriteLine(mess);
                    return false;
                }
            }
            catch (Exception)
            {
          
   
                mess = "发生异常!";
                return false;
            }
            finally
            {
          
   
                
                cn.Close();
            }
        }
        #endregion  
        #region 统计学生人数
        public int GetStudentAmost()
        {
          
          
           SqlConnection conn = new SqlConnection(sql);
            try
            {
          
             
                string strSql = "select COUNT(*) from Student";         
                conn.Open();
                SqlCommand com = new SqlCommand(strSql, conn);
                int num = (int)com.ExecuteScalar();      
                Console.WriteLine("在校学生人数:" + num);
                return num;
            }
            catch
            {
          
   
                return -1;
            }
            finally
            {
          
             
                conn.Close();
            }
        }
        #endregion
        #region 查看学生名单
        public void GetStudnetTwo()
        {
          
   
            SqlConnection con = new SqlConnection(sql);
            SqlDataReader strSql = null;
            try
            {
          
   
                con.Open();//打开链接
                //拼写查询语句
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("select");
                sb.AppendLine("         [StudentNo]");
                sb.AppendLine("        ,[StudentName]");
                sb.AppendLine("from");
                sb.AppendLine("         [Student]");
                SqlCommand comm = new SqlCommand(sb.ToString(), con);
                strSql = comm.ExecuteReader();
                Console.WriteLine("------------------------");
                Console.WriteLine(" 学号	姓名");
                Console.WriteLine("------------------------");
                StringBuilder sb1 = new StringBuilder();
                //循环读取DataReader
                while (strSql.Read())
                {
          
   
                    sb1.AppendFormat("{0}	{1}", strSql["StudentNo"], strSql["StudentName"]);
                    Console.WriteLine(sb1);
                    sb1.Length = 0;
                }
                Console.WriteLine("------------------------------");
            }
            catch (Exception)
            {
          
   
                Console.WriteLine("数据库操作错误!");
            }
            finally
            {
          
   
                strSql.Close(); 
                con.Close();     //关闭连接
            }
        }
        #endregion
        #region 查询学生姓名
        public SqlDataReader GetStudentList()
        {
          
   
            SqlConnection conn = new SqlConnection(sql);
            try
            {
          
   
                conn.Open();
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("select");
                sb.AppendLine("             StudentNo");
                sb.AppendLine("             ,StudentName");
                sb.AppendLine("from");
                sb.AppendLine("             Student");
                SqlCommand comm = new SqlCommand(sb.ToString(), conn);
                return comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception)
            {
          
   
                return null;
            }
        }
        //输出学生名单
        public void ShowStudentList()
        {
          
   
            SqlDataReader reader = GetStudentList();
            if (reader == null)
            {
          
   
                Console.WriteLine("出现异常!");
                return;
            }
            Console.WriteLine("------------------");
            Console.WriteLine("学号	姓名");
            Console.WriteLine("------------------");
            StringBuilder sb = new StringBuilder();
            while (reader.Read())
            {
          
   
                sb.AppendFormat("{0}	{1}", reader["StudentNo"], reader["StudentName"]);
                Console.WriteLine(sb);
                sb.Length = 0;
            }
            Console.WriteLine("--------------------");
            reader.Close();
        }
        #endregion
        #region 按学号查询学生姓名
        public string GetStudentByNo(string stuNo)
        {
          
   
            SqlConnection conn = new SqlConnection(sql);
            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 e)
            {
          
   
                Console.WriteLine(e.Message);
                return string.Empty;
            }
            finally
            {
          
   
                conn.Close();
            }
        }
        public void ShowStudentName()
        {
          
   
            Console.WriteLine("请输入学生学号:");
            string stuNo = Console.ReadLine();
            string stuName = GetStudentByNo(stuNo);
            if (stuName.Equals(string.Empty))
            {
          
   
                Console.WriteLine("出现异常!");
            }
            else
            {
          
   
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("学号是{0}的学生姓名为:{1}", stuNo, stuName);
                Console.WriteLine(sb);
            }
        }
        #endregion 按学号查询学生姓名
        #region 按姓名查询学生信息
        public SqlDataReader GetStudentInfoBy(string stuName)
        {
          
   
            try
            {
          
   
                SqlConnection conn = new SqlConnection(sql);
                conn.Open();
                StringBuilder sb = new StringBuilder();
                //这是一个包含模糊查询和连接查询额SQL语句
                sb.AppendLine("select");
                sb.AppendLine("          A.[StudentNo]");
                sb.AppendLine("         ,A.[StudentName]");
                sb.AppendLine("         ,A.[Sex]");
                sb.AppendLine("         ,B.[GradeName]");
                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 e)
            {
          
   
                Console.WriteLine(e);
                return null;
            }
        }
        public void ShowStudentInfoByName()
        {
          
   
            StringBuilder sb = new StringBuilder();
            Console.WriteLine("请输入学生姓名:");
            string stuName = Console.ReadLine();
            SqlDataReader reader = GetStudentInfoBy(stuName);
            if (reader == null)
            {
          
   
                Console.WriteLine("出现异常!");
                return;
            }
            Console.WriteLine("--------------------------------------------------------------------");
            Console.WriteLine("学号	姓名	性别	年级	联系电话	地址		出生日期		邮箱");
            while (reader.Read())
            {
          
   
                sb.AppendFormat("{0}	{1}	{2}	{3}	{4}	{5}		{6}		{7}",
                    reader["StudentNo"], reader["StudentName"], reader["Sex"], reader["GradeName"],
                    reader["Phone"], reader["Address"], reader["BornDate"], reader["Email"]);
                Console.WriteLine(sb.ToString());
                sb.Length = 0;
            }
            Console.WriteLine("--------------------------------------------------------------------");
            reader.Close();
        }
        #endregion
        #region 修改学生出生日期
        public int UpdateStuBornDate(string bornDate, string stuNo)
        {
          
   
            SqlConnection conn = new SqlConnection(sql);
            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)
            {
          
   
                return -1;//若有异常则返回-1
            }
            finally
            {
          
   
                conn.Close();
            }
        }
        public void UpdateStuBornDate()
        {
          
   
            try
            {
          
   
                Console.WriteLine("请输入学号:");
                string stuNo = Console.ReadLine();
                Console.WriteLine("请输入修改后的生日(XXXX-XX-XX):");
                string borndate = Console.ReadLine();
                DateTime dtStuDate = Convert.ToDateTime(borndate);
                int iRet = UpdateStuBornDate(borndate, stuNo);
                if (iRet == -1)
                {
          
   
                    Console.WriteLine("异常错误!");
                }
                else
                {
          
   
                    Console.WriteLine("修改成功!");
                }
            }
            catch (Exception)
            {
          
   
                Console.WriteLine("输入错误!");
            }
        }
        #endregion

        #region 删除学生记录
        public int StuSanCu(string stuNo)
        {
          
   
            SqlConnection conn = new SqlConnection(sql);
            try
            {
          
   
                conn.Open();
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("delete");
                sb.AppendLine("         [Student]");
                sb.AppendLine("where");
                sb.AppendLine("         [StudentNo]=" + stuNo + "");
                SqlCommand comm = new SqlCommand(sb.ToString(), conn);
                return comm.ExecuteNonQuery();
            }
            catch (Exception)
            {
          
   
                return -1;
            }
            finally
            {
          
   
                conn.Close();
            }
       }
        public void ShwoShanChu()
        {
          
   
            Console.WriteLine("请输入学号:");
            string stuNo = Console.ReadLine();

            string stuName = GetStudentByNo(stuNo);
            Console.WriteLine("确定要删除学号是{0},姓名{1}为的学生记录(y/n):", stuNo, stuName);
            string nn = Console.ReadLine();
            if (nn.Equals("y"))
            {
          
   
                int iRet = StuSanCu(stuNo);
                if (iRet != 1)
                {
          
   
                    Console.WriteLine("删除失败");
                }
                else
                {
          
   
                    Console.WriteLine("删除成功!");
                }
            }
            else
            {
          
   
                Console.WriteLine("你已取消删除!");
            }
        }
        #endregion
        #region 新增年级记录
        public int InaertGrade(string gradeName)
        {
          
   
            SqlConnection conn = new SqlConnection(sql);
            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 InputGrade()
        {
          
   
            Console.WriteLine("请输入待插入的年级名称:");
            string gradeName = Console.ReadLine();
            int iRet = InaertGrade(gradeName);
            if (iRet == -1)
            {
          
   
                Console.WriteLine("出现异常!");
            }
            else
            {
          
   
                Console.WriteLine("插入成功!");
            }
        }
        #endregion
    }
}
经验分享 程序员 微信小程序 职场和发展