用C#与数据库实现MySchoolBase系统

缺乏、安全感 2021-12-12 04:33 357阅读 0赞

## **下面是自己用C\#写的一个使用数据库实现MySchoolBase系统管理** ##

**（大神勿喷，初学者以借鉴为主）**

一共分为三个类分别是：Program（启动类），DBOperation（操作语句类），SchoolManager（显示语句类）

--------------------

***代码部分（如下）：***

> 既然使用了数据库那么using还是要记得导入的呦：
> 
> using System.Data.SqlClient;  
> using System.Data;

**Program（启动类）：**

class Program
        {
            static void Main(string[] args)
            {
                SchoolManager SchoolManager = new SchoolManager();
                SchoolManager.Login();
            }
        }

**DBOperation（操作语句类）：**

class DBOperation
        {
            string connString = "Data Source=.;Initial Catalog=Demo;User ID=sa;Pwd=sa";//连接数据库
    
            //判断输入用户账号密码是否正确
            #region  判断输入用户账号密码是否正确
            public bool CheckUserInfo(string username,string password) {
                bool flag = true;
                string connString = "Data Source=.;Initial Catalog=Demo;User ID="+ username + ";Pwd="+ password;//连接数据库
                SqlConnection connection = new SqlConnection(connString);
                try {
                    connection.Open();//打开数据库
                    flag = true;
                }
                catch (Exception) {
                    Console.WriteLine("发生异常！");
                    flag = false;
                }
                finally {
                    connection.Close();//关闭数据库
                }
                return flag;
            }
            #endregion
    
            //统计人数操作方法
            #region  统计人数操作方法
            public int GetStudentAmount() {
                SqlConnection connection = new SqlConnection(connString);
                try {
                    string str = "SELECT COUNT(*) FROM Student";
                    connection.Open();
                    SqlCommand command = new SqlCommand(str, connection);
                    int iRet = (int)command.ExecuteScalar();
                    return iRet;//正确人数
                }
                catch (Exception) {
                    return -1;//不正确
                }
                finally {
                    connection.Close();
                }
            }
            #endregion
    
            //查看学生名单操作
            #region  查看学生名单操作
            public SqlDataReader GetStudentList() {
                SqlConnection conncetion = new SqlConnection(connString);
                try {
                    conncetion.Open();
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("SELECT");
                    sb.AppendLine(" [StudentNo]");
                    sb.AppendLine(",[StudentName]");
                    sb.AppendLine(" FROM");
                    sb.AppendLine(" [Student]");
                    SqlCommand command = new SqlCommand(sb.ToString(), conncetion);
                    return command.ExecuteReader(CommandBehavior.CloseConnection);//在执行命令时，如果关闭关联的DataReader对象，则关联的Connection对象也将关闭
                }
                catch (Exception) {
                    return null;
                }
            }
            #endregion
    
            //新增年纪记录操作
            #region  新增年纪记录操作
            public int InsertGrade(string Name) {
                SqlConnection connection = new SqlConnection(connString);
                try {
                    connection.Open();
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("INSERT INTO");
                    sb.AppendLine(" [Grade]");
                    sb.AppendLine(" VALUES ");
                    sb.AppendLine(" ('"+ Name + "')");
                    SqlCommand command = new SqlCommand(sb.ToString(), connection);
                    return command.ExecuteNonQuery();
                }
                catch (Exception) {
                    return -1;
                }
                finally {
                    connection.Close();
                }
            }
            #endregion
    
            //按学号查询学生姓名操作
            #region  按学号查询学生姓名操作
            public string GetStudentNameByNo(string stuNo) {
                SqlConnection connection = new SqlConnection(connString);
                try {
                    connection.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 command = new SqlCommand(sb.ToString(), connection);
                    SqlDataReader reader = command.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 {
                    connection.Close();
                }
            }
            #endregion
    
            //按姓名查询学生信息操作
            #region  按姓名查询学生信息操作
            public SqlDataReader GetStudentInfoByName(string stuName) {
                SqlConnection connection = new SqlConnection(connString);
                try {
                    connection.Open();
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("SELECT");
                    sb.AppendLine(" A.[StudentNo],A.[StudentName],A.[Sex],B.[GradeName],A.[Phone],A.[Address],A.[BornDate],A.[Email]");
                    sb.AppendLine(" FROM");
                    sb.AppendLine(" [Student] AS A,[Grade] AS B");
                    sb.AppendLine(" WHERE");
                    sb.AppendLine(" A.[GradeId] = B.[GradeId]");
                    sb.AppendLine(" AND");
                    sb.AppendLine("A.[StudentName] LIKE '%"+ stuName+ "%'");
                    SqlCommand command = new SqlCommand(sb.ToString(), connection);
                    return command.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch (Exception e) {
                    Console.WriteLine("出现异常！",e.Message);
                    return null;
                }
            }
            #endregion
    
            //修改学生出生日期操作
            #region  修改学生出生日期操作
            public int UpdateStuBornDate(string stuNo,DateTime bornDate) {
                SqlConnection connection = new SqlConnection(connString);
                try {
                    connection.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 command = new SqlCommand(sb.ToString(), connection);
                    return command.ExecuteNonQuery();
                }
                catch (Exception e) {
                    Console.WriteLine(e.Message);
                    return -1;
                }
                finally {
                    connection.Close();
                }
            }
            #endregion
    
            //删除学生记录操作
            #region  删除学生记录操作
            public int DeleteStudent(string stuNo) {
                SqlConnection connection = new SqlConnection(connString);
                try
                {
                    connection.Open();
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("DELETE");
                    sb.AppendLine(" FROM");
                    sb.AppendLine(" Student");
                    sb.AppendLine(" WHERE");
                    sb.AppendLine(" [StudentNo] = '"+ stuNo + "'");
                    SqlCommand command = new SqlCommand(sb.ToString(), connection);
                    return command.ExecuteNonQuery();
                }
                catch (Exception e) {
                    Console.WriteLine(e.Message);
                    return -1;
                }
                finally {
                    connection.Close();
                }
            }
            #endregion
        }

**SchoolManager（显示语句类）：**

//业务处理
        class SchoolManager
        {
            //输入用户账户密码
            #region  输入用户账户密码
            public void Login() {
                Console.Write("请输入您的管理员用户名：");
                string username = Console.ReadLine();
                Console.Write("请输入您的密码：");
                string password = Console.ReadLine();
                DBOperation dboperation = new DBOperation();
                bool flag = dboperation.CheckUserInfo(username, password);
                if (flag)
                {
                    Console.WriteLine("登录成功！");
                    CaiDan();//菜单
                }
                else {
                    Console.WriteLine("对不起，登录失败！");
                }
            }
            #endregion
    
            //菜单
            #region  菜单
            public void CaiDan() {
                do { 
                    Console.WriteLine("==========请选择操作键==========");
                    Console.WriteLine("1.统计学生人数\n2.查看学生名单\n3.按学号查询学生姓名\n4.按姓名查询学生信息\n5.修改学生出生日期\n6.删除学生记录\n7.新增年纪记录\n0.退出");
                    Console.WriteLine("================================");
                    Console.Write("请输入您的选择：");
                    int xuanZe = int.Parse(Console.ReadLine());
                    switch (xuanZe) {
                        case 1:
                            //统计学生人数
                            TongJi();
                            continue;
                        case 2:
                            //查看学生名单
                            ShowStudentList();
                            continue;
                        case 3:
                            //按学号查询学生姓名
                            ShowStudentName();
                            continue;
                        case 4:
                            //按姓名查询学生信息
                            ShowStudentInfoByName();
                            continue;
                        case 5:
                            //修改学生出生日期
                            ShowStuBornDate();
                            continue;
                        case 6:
                            //删除学生记录
                            ShowDeleteStudent();
                            break;
                        case 7:
                            //新增年纪记录
                            InsertGrade();
                            continue;
                        case 0:
                            //退出
                            Console.WriteLine("欢迎下次光临明宇学校管理系统！");
                            break;
                        default:
                            Console.WriteLine("对不起，没有该选项！");
                            continue;
                    }
                } while (true);
            }
            #endregion
    
            //统计学生人数
            #region  统计学生人数
            public void TongJi() {
                DBOperation dboperation = new DBOperation();
                int shu = dboperation.GetStudentAmount();
                if (shu == -1)
                {
                    Console.WriteLine("人数获取不正确！");
                    return;
                }
                else {
                    Console.WriteLine("在校学生人数：{0}\n",shu);
                }
            }
            #endregion
    
            //查看学生名单
            #region  查看学生名单
            public void ShowStudentList() {
                DBOperation dboperation = new DBOperation();
                SqlDataReader reader = dboperation.GetStudentList();
                if (reader == null) {
                    Console.WriteLine("对不起，出现异常！");
                    return;
                }
                Console.WriteLine("----------------------");
                Console.WriteLine("学号\t\t姓名");
                Console.WriteLine("----------------------");
                StringBuilder sb = new StringBuilder();
                //循环读取数据
                while (reader.Read()) {
                    sb.AppendFormat("{0}\t{1}", reader["StudentNo"],reader["StudentName"]);
                    Console.WriteLine(sb);
                    sb.Length = 0;
                }
                Console.WriteLine("----------------------");
                //关闭 DataReader
                reader.Close();
            }
            #endregion
    
            //新增年纪记录
            #region  新增年纪记录
            public void InsertGrade() {
                DBOperation dboperation = new DBOperation();
                Console.Write("请输入待插入的年纪名称：");
                string name = Console.ReadLine();
                int iRet = dboperation.InsertGrade(name);
                if (iRet == -1)
                {
                    Console.WriteLine("对不起，出现异常！");
                }
                else {
                    Console.WriteLine("插入成功！");
                }
            }
            #endregion
    
            //按学号查询学生姓名
            #region  按学号查询学生姓名
            public void ShowStudentName() {
                DBOperation dboperation = new DBOperation();
                Console.Write("请输入学生学号：");
                string stuNo = Console.ReadLine();
                string stuName = dboperation.GetStudentNameByNo(stuNo);
                if (stuName.Equals(string.Empty))
                {
                    Console.WriteLine("对不起，出现异常！");
                }
                else {
                    StringBuilder sb = new StringBuilder();
                    sb.AppendFormat("学号是{0}的学生为：{1}\n",stuNo,stuName);
                    Console.WriteLine(sb);
                }
            }
            #endregion
    
            //按姓名查询学生信息
            #region  按姓名查询学生信息
            public void ShowStudentInfoByName() {
                DBOperation dboperation = new DBOperation();
                Console.Write("请输入学生姓名：");
                string stuName = Console.ReadLine();
                SqlDataReader reader = dboperation.GetStudentInfoByName(stuName);
                if (reader == null)
                {
                    Console.WriteLine("对不起，运行出现异常！");
                    return;
                }
                Console.WriteLine("*************************************************************************");
                Console.WriteLine("学号\t\t姓名\t性别\t年纪\t\t联系电话\t地址\t\t出生日期\t邮箱");
                Console.WriteLine("*************************************************************************");
                while (reader.Read()) {
                    StringBuilder sb = new StringBuilder();
                    sb.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(sb.ToString()+"\n");
                    sb.Length = 0;
                }
                reader.Close();
            }
            #endregion
    
            //修改学生出生日期
            #region  修改学生出生日期
            public void ShowStuBornDate() {
                DBOperation dboperation = new DBOperation();
                Console.Write("请输入学号：");
                string stuNo = Console.ReadLine();
                Console.Write("请输入修改后的生日：");
                string stuBornDate = Console.ReadLine();
                DateTime bornDate = Convert.ToDateTime(stuBornDate);
                int shu = dboperation.UpdateStuBornDate(stuNo, bornDate);
                if (shu == -1)
                {
                    Console.WriteLine("对不起，发生异常！");
                }
                else {
                    Console.WriteLine("修改成功！");
                }
            }
            #endregion
    
            //删除学生记录
            #region  删除学生记录
            public void ShowDeleteStudent() {
                DBOperation dboperation = new DBOperation();
                Console.Write("请输入学号：");
                string stuNo = Console.ReadLine();
                int shu = dboperation.DeleteStudent(stuNo);
                if (shu == -1)
                {
                    Console.WriteLine("对不起，删除失败！");
                }
                else {
                    Console.WriteLine("删除成功！");
                }
            }
            #endregion
        }