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

于 2019-07-10 11:21:18 发布
阅读量3.5k 收藏 19
点赞数 6
分类专栏： C# 文章标签： C#与数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_44860582/article/details/95316422
版权

下面是自己用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
    }

 

老明宇.
关注
专栏目录
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值