C#程序代码连接SQL Server数据库实现学生成绩信息管理系统（重置版）

书海shuhai

已于 2022-12-15 13:07:55 修改

阅读量3.9k

点赞数 11

文章标签：数据库 c# sqlserver

于 2022-10-10 07:30:47 首次发布

本文链接：https://blog.csdn.net/qq_60423778/article/details/127232046

版权

一、创建数据库表和配置SQL数据库连接信息

1、创建数据库表

先创建一个“学生成绩信息管理系统”数据库，再创建3张表，分别为GradeTable（学生成绩表）、StudentAccount（学生账号信息表）、TeacherAccount（教师账号信息表），设置好数据类型，如图所示。

在这里插入图片描述

2、配置数据库连接信息

新建一个C#控制台程序，找到App.config文件，在里面填写数据库连接信息，我这里的连接方式为windows身份验证，如图所示。

在这里插入图片描述

App.config里面的代码格式


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
    </startup>
	
	<connectionStrings>
		<add name="sqlserver" connectionString="server=SDFGHJ\SQLEXPRESS;
			 database=学生成绩信息管理系统;integrated security = true;"/>
	</connectionStrings>
	//SDFGHJ\SQLEXPRESS为SQL数据库的服务器名称。
	
</configuration>

调用该连接信息的方法示例

using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;
using System.Text;
using System.Security.Cryptography;

namespace ConsoleApp1NEW1
{
  class Program
    {
 		public static readonly string Stu = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;

        public static void Arr()
        {
            SqlConnection conn = new SqlConnection(Stu);
            conn.Open();	//打开数据库
            conn.Close();	//关闭数据库
        }
        
    	static void Main()
        {
           Arr();
           Console.ReadKey();
        }
}

二、配置程序代码

这次的代码量相对以往来说有点多，所以我写的时候创建了四个类，分别是：StudentAccount（学生账号信息管理类）、TeacherAccount（教师账号信息管理类）、Student（学生查看成绩信息类）、Teacher（教师管理成绩信息类）。

1、StudentAccount类

学生账号信息类里面的代码里面的代码：



    class StudentAccount    //学生账号信息类
    {
        public static readonly string Stu = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;

        private string MD5Encrypt(string rawPass, string salt)   //MD5加盐加密操作
        {
            MD5 md5 = MD5.Create();
            byte[] bs = Encoding.UTF8.GetBytes(rawPass + salt);
            byte[] hs = md5.ComputeHash(bs);
            StringBuilder stb = new StringBuilder();
            foreach (byte b in hs)
            {
                stb.Append(b.ToString("x2"));
            }
            return stb.ToString();
        }

        public static void Arr()
        {
            SqlConnection conn = new SqlConnection(Stu);
            conn.Open();
            conn.Close();
        }

        public void Login() //学生账号登录
        {
            Console.WriteLine("\n--------- 1.学生账号登录 ---------");
            SqlConnection conn = new SqlConnection(Stu);
            Console.Write("请输入账号：");
            string username = Console.ReadLine().Trim().Trim();
            Console.Write("请输入密码：");
            string password = Console.ReadLine().Trim().Trim();
            while (true)
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from StudentAccount where Number='" + username + "'";
                SqlDataReader sdt = cmd.ExecuteReader();
                if (Equals(username, ""))
                {
                    Console.Write("账号不得为空!\n请输入账号：");
                    username = Console.ReadLine().Trim().Trim();
                }
                else if (Equals(password, ""))
                {
                    Console.Write("密码不得为空!\n请输入密码");
                    password = Console.ReadLine().Trim();
                }
                else if (sdt.Read())    //读取到记录
                {
                    string result = sdt.GetString(sdt.GetOrdinal("password"));
                    if (MD5Encrypt(password, username).Equals(result))
                    {
                        Console.WriteLine("账号登录成功!");
                        conn.Close();         //关闭数据库连接
                        Student st = new Student();
                        st.Main();
                    }
                    else
                    {
                        Console.WriteLine("账号密码错误!\n");
                        conn.Close();
                        Login();
                    }
                }
                else
                {
                    YesOrNo();
                }
                conn.Close();
            }
        }

        private void YesOrNo()  //是否注册新账号
        {
            Console.WriteLine("\n账号不存在，是否选择注册一个新账号？\n1、是；2、否");
            string num = Console.ReadLine().Trim();
            Program st = new Program();
            switch (num)
            {
                case "1": Register(); break;
                case "2": st.Main2(); break;
                default: YesOrNo(); break;
            }
        }

        public void Register()      //学生账号注册
        {
            Console.WriteLine("\n--------- 1.学生账号注册 ---------");
            SqlConnection conn = new SqlConnection(Stu);
            conn.Open();
            Console.Write("请输入注册账号：");
            string username = Console.ReadLine().Trim();
            Console.Write("请输入账号密码：");
            string password = Console.ReadLine().Trim();
            Console.Write("请确认账号密码：");
            string repassword = Console.ReadLine().Trim();
            while (true)
            {
                string sql = "Select Number from StudentAccount where Number='" + username + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);      //判断账号是否已存在
                cmd.CommandType = CommandType.Text;
                SqlDataReader sdt = cmd.ExecuteReader();
                if (Equals(username, ""))
                {
                    Console.Write("注册账号不得为空!\n请输入注册账号：");
                    username = Console.ReadLine().Trim();
                }
                else if (Equals(password, ""))
                {
                    Console.Write("账号密码不得为空!\n请输入账号密码：");
                    password = Console.ReadLine().Trim();
                }
                else if (repassword.Equals("") || !password.Equals(repassword))
                {
                    Console.Write("请重新确认密码：");
                    repassword = Console.ReadLine().Trim();
                }
                else
                {
                    if (sdt.Read())
                    {
                        Console.WriteLine("该账号已注册,请重新注册账号!\n");
                        Register();
                    }
                    else
                    {
                        //插入的信息分别为账号，密文
                        Insert(username, MD5Encrypt(password, username));
                        //调用函数Insert()保存注册信息到数据库
                        conn.Close();
                    }
                }
                conn.Close();
            }
        }

        private void Insert(string txt1, string txt2)   //数据库插入注册成功后的学生账号信息
        {
            SqlConnection conn = new SqlConnection(Stu);
            Program st = new Program();
            conn.Open();
            try
            {
                string InsertStr = string.Format("insert into StudentAccount values('{0}','{1}')", txt1, txt2);
                SqlCommand comm = conn.CreateCommand();
                comm.CommandText = InsertStr;
                SqlCommand mycom = new SqlCommand(InsertStr, conn);
                mycom.ExecuteNonQuery();
                Console.WriteLine("账号注册成功!\n");
                st.Main2();
                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("账号注册失败!\n");
                Console.WriteLine(ex.Message);
                conn.Close();
                st.Main2();
            }
            conn.Close();
        }

        public void Change()   //修改学生账号密码
        {
            Console.WriteLine("\n--------- 2.账号登录修改 ---------");
            SqlConnection conn = new SqlConnection(Stu);
            conn.Open();
            Console.Write("请输入账号：");
            string username = Console.ReadLine().Trim();
            Console.Write("请输入旧密码：");
            string oldpassword = Console.ReadLine().Trim();
            Console.Write("请输入新密码：");
            string newpassword = Console.ReadLine().Trim();
            while (true)
            {
                string sql = "Select * from StudentAccount where Number='" + username + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);     //调用登录窗体Form1参数username，判断当前账号密码是否正确
                SqlDataReader reader = cmd.ExecuteReader();
                if (Equals(username, ""))
                {
                    Console.Write("账号不得为空!\n请输入账号：");
                    username = Console.ReadLine().Trim();
                }
                else if (Equals(oldpassword, ""))
                {
                    Console.Write("密码不得为空!\n请输入旧密码：");
                    oldpassword = Console.ReadLine().Trim();
                }
                else if (Equals(newpassword, ""))
                {
                    Console.Write("密码不得为空!\n请输入新密码：");
                    newpassword = Console.ReadLine().Trim();
                }
                else if (oldpassword.Equals(newpassword))
                {
                    Console.Write("新旧密码相同,请重新输入密码：");
                    oldpassword = Console.ReadLine().Trim();
                }
                else if (reader.Read())
                {
                    string result = reader.GetString(reader.GetOrdinal("password")); //读取到的密码
                    if (MD5Encrypt(oldpassword, username).Equals(result))
                    {
                        ChangeSave(username, MD5Encrypt(newpassword, username));
                        //调用Change()函数方法保存修改后的账号信息到数据库
                        conn.Close();
                    }
                    else
                    {
                        Console.WriteLine("账号密码错误!\n");
                        Change();
                    }
                }
                else
                {
                    Console.WriteLine("该账号不存在!\n");
                    Student sdt = new Student();
                    sdt.Main();
                }
                conn.Close();
            }
        }

        private void ChangeSave(string txt1, string txt2)     //数据库保存修改后的账号信息
        {
            SqlConnection conn = new SqlConnection(Stu);
            try
            {
                conn.Open();
                string InsertStr = string.Format("update StudentAccount set password='{0}' where Number='{1}';", txt2.ToString(), txt1.ToString());
                SqlCommand comm = conn.CreateCommand();
                SqlCommand mycom = new SqlCommand(InsertStr, conn);
                mycom.ExecuteNonQuery();
                Console.WriteLine("账号密码修改成功!请重新登录!\n");
                conn.Close();
                Program st = new Program();
                st.Main2();
            }
            catch (Exception ex)
            {
                Console.WriteLine("账号密码修改失败!\n");
                Console.WriteLine(ex.Message);
                conn.Close();
                Student st = new Student();
                st.Main();
            }
        }

        public void Cancel()    //注销学生账号
        {
            Console.WriteLine("\n--------- 3.注销当前账号 ---------");
            SqlConnection conn = new SqlConnection(Stu);
            Console.Write("请输入账号：");
            string username = Console.ReadLine().Trim();
            Console.Write("请输入密码：");
            string password = Console.ReadLine().Trim();
            while (true)
            {
                conn.Open();
                string sql = "Select password from StudentAccount where Number='" + username + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);     //判断当前账号密码是否正确
                cmd.CommandType = CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                if (Equals(username, ""))
                {
                    Console.Write("账号不得为空!\n请输入账号：");
                    username = Console.ReadLine().Trim();
                }
                else if (Equals(password, ""))
                {
                    Console.Write("账号密码不得为空!\n请输入账号密码：");
                    password = Console.ReadLine().Trim();
                }
                else if (reader.Read())
                {
                    string result = reader.GetString(reader.GetOrdinal("password"));
                    if (MD5Encrypt(password, username).Equals(result))
                    {
                        Del(username);   //调用方法，删除注销后的账号信息
                        conn.Close();   //关闭数据库对象连接
                    }
                    else
                    {
                        Console.WriteLine("账号密码错误!");
                        Cancel();
                    }
                }
                else
                {
                    Console.WriteLine("该账号不存在!\n");
                    Student sa = new Student();
                    sa.Main();
                }
                conn.Close();
            }
        }

        private void Del(string username)    //数据库删除注销后的账号信息
        {
            SqlConnection conn = new SqlConnection(Stu);
            conn.Open();
            try
            {
                string sql2 = "delete from StudentAccount where Number='" + username + "'";
                SqlCommand cmd1 = new SqlCommand(sql2, conn);
                cmd1.ExecuteNonQuery();
                conn.Close();
                Console.WriteLine("账号注销成功!\n");
                Program stu = new Program();
                stu.Main2();
            }
            catch (Exception ex)
            {
                Console.WriteLine("账号注销失败!\n");
                Console.WriteLine(ex.Message);
                conn.Close();
                Student stu = new Student();
                stu.Main();
            }
        }
    }

2、Student类

学生查看成绩信息类里面的代码：


class Student
    {
        public static readonly string Stu = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;

        public void Main()
        {
            Console.WriteLine("\n******************************");
            Console.WriteLine("******* 1.查询本人成绩 *******");
            Console.WriteLine("******* 2.修改账号密码 *******");
            Console.WriteLine("******* 3.注销当前账号 *******");
            Console.WriteLine("******* 4.返回登录界面 *******");
            Console.WriteLine("******* 0.退出当前程序 *******");
            Console.WriteLine("******************************");
            Console.Write("请输入选择：");
            string num = Console.ReadLine().Trim();
            StudentAccount st = new StudentAccount();
            Program sd = new Program();
            switch (num)
            {
                case "1": GradeFind(); break;
                case "2": st.Change(); break;
                case "3": st.Cancel(); break;
                case "4": sd.Main2(); break;
                case "0":
                    {
                        Console.WriteLine("程序已退出!");
                        Console.ReadKey();
                        Process.GetCurrentProcess().Kill();
                    }; break;
                default: Main(); break;
            }
        }

        private void GradeFind()    //查询自己成绩
        {
            Console.WriteLine("\n--------- 1.查询本人成绩 ---------");
            SqlConnection conn = new SqlConnection(Stu);
            Console.Write("请输入学号:");
            string num = Console.ReadLine();
            try
            {
                conn.Open();
                Console.WriteLine("------------------------------------------------------------------------------");
                Console.WriteLine("   学号   |   姓名   |   语文   |   数学   |   英语   |   总分   |   平均分   ");
                Console.WriteLine("------------------------------------------------------------------------------");
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from GradeTable where Number= '" + num + "'";
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    string number = reader.GetString(reader.GetOrdinal("Number"));
                    string name = reader.GetString(reader.GetOrdinal("Name"));
                    double chinese = reader.GetDouble(reader.GetOrdinal("Chinese"));
                    double math = reader.GetDouble(reader.GetOrdinal("Math"));
                    double english = reader.GetDouble(reader.GetOrdinal("English"));
                    double sum = chinese + math + english;
                    double aver = sum / 3.0;
                    Console.WriteLine("   {0}   |   {1}   |    {2}    |    {3}    |    {4}    |   {5:F2}  |   {6:F2}  ", number, name, chinese, math, english, sum, aver);
                    Console.WriteLine("------------------------------------------------------------------------------");
                    Console.ReadKey();
                    Main();
                }
                else
                {
                    Console.WriteLine("暂无成绩!\n");
                    Console.ReadKey();
                    Main();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                conn.Close();
            }
        }
    }

3、TeacherAccount类

教师账号信息类里面的代码：



 class TeacnerAccount    //教师账号管理
    {
        private static readonly string Stu = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;     //创建数据库连接

        private string MD5Encrypt(string rawPass, string salt)   //MD5加盐加密操作
        {
            MD5 md5 = MD5.Create();
            byte[] bs = Encoding.UTF8.GetBytes(rawPass + salt);
            byte[] hs = md5.ComputeHash(bs);
            StringBuilder stb = new StringBuilder();
            foreach (byte b in hs)
            {
                stb.Append(b.ToString("x2"));
            }
            return stb.ToString();
        }

        public void Login()     //教师账号登录
        {
            Console.WriteLine("\n--------- 2.教师账号登录 ---------");
            SqlConnection conn = new SqlConnection(Stu);
            Console.Write("请输入账号：");
            string username = Console.ReadLine().Trim().Trim();
            Console.Write("请输入密码：");
            string password = Console.ReadLine().Trim().Trim();
            while (true)
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from TeacherAccount where username='" + username + "'";
                SqlDataReader sdt = cmd.ExecuteReader();
                if (Equals(username, ""))
                {
                    Console.Write("账号不得为空!\n请输入账号：");
                    username = Console.ReadLine().Trim().Trim();
                }
                else if (Equals(password, ""))
                {
                    Console.Write("密码不得为空!\n请输入密码：");
                    password = Console.ReadLine().Trim();
                }
                else if (sdt.Read())    //读取到记录
                {
                    string result = sdt.GetString(sdt.GetOrdinal("password"));
                    if (MD5Encrypt(password, username).Equals(result))
                    {
                        Console.WriteLine("账号登录成功!");
                        conn.Close();         //关闭数据库连接
                        Teacher st = new Teacher();
                        st.Main();
                    }
                    else
                    {
                        Console.WriteLine("账号密码错误!\n");
                        conn.Close();
                        Login();
                    }
                }
                else
                {
                    YesOrNo();
                }
                conn.Close();
            }
        }

        private void YesOrNo()  //是否注册教师账号
        {
            Console.WriteLine("\n账号不存在，是否选择注册一个新账号？\n1、是；2、否");
            string num = Console.ReadLine().Trim();
            Program st = new Program();
            switch (num)
            {
                case "1": Register(); break;
                case "2": st.Main2(); break;
                default: YesOrNo(); break;
            }
        }

        public void Register()  //教师账号注册
        {
            Console.WriteLine("\n--------- 2.教师账号注册 ---------");
            SqlConnection conn = new SqlConnection(Stu);
            Console.Write("请输入注册账号：");
            string username = Console.ReadLine().Trim();
            Console.Write("请输入账号密码：");
            string password = Console.ReadLine().Trim();
            Console.Write("请确认账号密码：");
            string repassword = Console.ReadLine().Trim();
            while (true)
            {
                conn.Open();
                string sql = "Select username from TeacherAccount where username='" + username + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);      //判断账号是否已存在
                cmd.CommandType = CommandType.Text;
                SqlDataReader sdt = cmd.ExecuteReader();
                if (Equals(username, ""))
                {
                    Console.Write("注册账号不得为空!\n请输入注册账号：");
                    username = Console.ReadLine().Trim();
                }
                else if (Equals(password, ""))
                {
                    Console.Write("账号密码不得为空!\n请输入账号密码：");
                    password = Console.ReadLine().Trim();
                }
                else if (repassword.Equals("") || !password.Equals(repassword))
                {
                    Console.Write("请重新确认密码：");
                    repassword = Console.ReadLine().Trim();
                }
                else
                {
                    if (sdt.Read())
                    {
                        Console.WriteLine("该账号已注册,请重新注册账号!");
                        Register();
                    }
                    else
                    {
                        //插入的信息分别为账号，密文
                        Insert(username, MD5Encrypt(password, username));
                        //调用函数Insert()保存注册信息到数据库
                        conn.Close();
                    }
                }
                conn.Close();
            }
        }

        private void Insert(string txt1, string txt2)   //数据库插入注册成功后的账号信息
        {
            SqlConnection conn = new SqlConnection(Stu);
            conn.Open();
            Program st = new Program();
            try
            {
                string InsertStr = string.Format("insert into TeacherAccount values('{0}','{1}')", txt1, txt2);
                SqlCommand comm = conn.CreateCommand();
                comm.CommandText = InsertStr;
                SqlCommand mycom = new SqlCommand(InsertStr, conn);
                mycom.ExecuteNonQuery();
                Console.WriteLine("账号注册成功!\n");
                conn.Close();
                st.Main2();
            }
            catch (Exception ex)
            {
                Console.WriteLine("账号注册失败!\n");
                Console.WriteLine(ex.Message);
                conn.Close();
                st.Main2();
            }
            conn.Close();
        }

        public void Change()    //修改教师账号密码
        {
            Console.WriteLine("\n--------- 6.修改账号密码 ---------");
            SqlConnection conn = new SqlConnection(Stu);
            Console.Write("请输入账号：");
            string username = Console.ReadLine().Trim();
            Console.Write("请输入旧密码：");
            string oldpassword = Console.ReadLine().Trim();
            Console.Write("请输入新密码：");
            string newpassword = Console.ReadLine().Trim();
            while (true)
            {
                conn.Open();
                string sql = "Select password from TeacherAccount where username='" + username + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);     //调用登录窗体Form1参数username，判断当前账号密码是否正确
                SqlDataReader reader = cmd.ExecuteReader();
                if (Equals(username, ""))
                {
                    Console.Write("账号不得为空!\n请输入账号：");
                    username = Console.ReadLine().Trim();
                }
                else if (Equals(oldpassword, ""))
                {
                    Console.Write("密码不得为空!\n请输入旧密码：");
                    oldpassword = Console.ReadLine().Trim();
                }
                else if (Equals(newpassword, ""))
                {
                    Console.Write("密码不得为空!\n请输入新密码：");
                    newpassword = Console.ReadLine().Trim();
                }
                else if (oldpassword.Equals(newpassword))
                {
                    Console.Write("新旧密码相同,请重新输入密码：");
                    oldpassword = Console.ReadLine().Trim();
                }
                else if (reader.Read())
                {
                    string result = reader["password"].ToString();  //读取到的密码
                    if (MD5Encrypt(oldpassword, username).Equals(result))
                    {
                        ChangeSave(username, MD5Encrypt(newpassword, username));
                        //调用Change()函数方法保存修改后的账号信息到数据库
                        conn.Close();
                    }
                    else
                    {
                        Console.WriteLine("账号密码错误!");
                        Change();
                    }
                }
                else
                {
                    Console.WriteLine("该账号不存在!\n");
                    Teacher st = new Teacher();
                    st.Main();
                }
                conn.Close();
            }
        }

        private void ChangeSave(string txt1, string txt2)     //数据库保存修改后的账号信息
        {
            SqlConnection conn = new SqlConnection(Stu);
            conn.Open();
            Program st = new Program();
            try
            {
                string InsertStr = string.Format("update TeacherAccount set password='{0}' WHERE username='{1}';", txt2.ToString(), txt1.ToString());
                SqlCommand comm = conn.CreateCommand();
                SqlCommand mycom = new SqlCommand(InsertStr, conn);
                mycom.ExecuteNonQuery();
                Console.WriteLine("账号密码修改成功!\n");
                conn.Close();
                st.Main2();
            }
            catch (Exception ex)
            {
                Console.WriteLine("账号密码修改失败!\n");
                Console.WriteLine(ex.Message);
                conn.Close();
                st.Main2();
            }
        }

        public void Cancel()        //注销教师账号
        {
            Console.WriteLine("\n--------- 7.注销当前账号 ---------");
            SqlConnection conn = new SqlConnection(Stu);
            Console.Write("请输入账号：");
            string username = Console.ReadLine().Trim();
            Console.Write("请输入密码：");
            string password = Console.ReadLine().Trim();
            while (true)
            {
                conn.Open();
                string sql = "Select password from TeacherAccount where username='" + username + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);     //判断当前账号密码是否正确
                cmd.CommandType = CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                if (Equals(username, ""))
                {
                    Console.Write("账号不得为空!\n请输入账号：");
                    username = Console.ReadLine().Trim();
                }
                else if (Equals(password, ""))
                {
                    Console.Write("账号密码不得为空!\n请输入账号密码：");
                    password = Console.ReadLine().Trim();
                }
                else if (reader.Read())
                {
                    string result = reader.GetString(reader.GetOrdinal("password"));
                    if (MD5Encrypt(password, username).Equals(result))
                    {
                        Del(username);   //调用方法，删除注销后的账号信息
                        conn.Close();   //关闭数据库对象连接
                    }
                    else
                    {
                        Console.WriteLine("账号密码错误!");
                        Cancel();
                    }
                }
                else
                {
                    Console.WriteLine("该账号不存在!\n");
                    Teacher st = new Teacher();
                    st.Main();
                }
                conn.Close();
            }
        }

        private void Del(string username)    //数据库删除注销后的账号信息
        {
            SqlConnection conn = new SqlConnection(Stu);
            Program sdt = new Program();
            try
            {
                conn.Open();
                string sql2 = "delete from GradeTable where username='" + username + "'";
                SqlCommand cmd1 = new SqlCommand(sql2, conn);
                cmd1.ExecuteNonQuery();
                conn.Close();
                Console.WriteLine("账号注销成功!\n");
                sdt.Main2();
            }
            catch (Exception ex)
            {
                Console.WriteLine("账号注销失败!\n");
                Console.WriteLine(ex.Message);
                conn.Close();
                sdt.Main2();
            }
        }
    }

4、Teacher类

教师管理成绩信息类里面的代码


class Teacher       //教师管理学生成绩类
    {
        private static readonly string Stu = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;

        public void Main()
        {
            Console.WriteLine("\n******************************");
            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("******* 0.退出当前程序 *******");
            Console.WriteLine("******************************");
            Console.Write("请输入选择：");
            string num = Console.ReadLine().Trim();
            TeacnerAccount std = new TeacnerAccount();
            Program sd = new Program();
            switch (num)
            {
                case "1": GradeInput(); break;
                case "2": GradeOutput(); break;
                case "3": GradeFind(); break;
                case "4": GradeChange(); break;
                case "5": GradeDelete(); break;
                case "6": std.Change(); break;
                case "7": std.Cancel(); break;
                case "8": sd.Main2(); break;
                case "0":
                    {
                        Console.WriteLine("程序已退出!");
                        Console.ReadKey();
                        Process.GetCurrentProcess().Kill();
                    }; break;
                default: Main(); break;
            }
        }

        private void GradeInput()        //学生成绩输入
        {
            SqlConnection conn = new SqlConnection(Stu);
            Console.WriteLine("\n----------- 1.学生成绩输入 -----------");
            Console.Write("请输入学生学号：");
            string name = Console.ReadLine().Trim();
            Console.Write("请输入学生姓名：");
            string number = Console.ReadLine().Trim();
            Console.Write("请输入语文成绩：");
            string chinese = Console.ReadLine().Trim();
            Console.Write("请输入数学成绩：");
            string math = Console.ReadLine().Trim();
            Console.Write("请输入英语成绩：");
            string english = Console.ReadLine().Trim();
            while (true)
            {
                if (Equals(name, ""))
                {
                    Console.Write("学生姓名不得为空!\n请输入学生姓名：");
                    name = Console.ReadLine().Trim();
                }
                else if (Equals(number, ""))
                {
                    Console.Write("学生学号不得为空!\n请输入学生学号：");
                    number = Console.ReadLine().Trim();
                }
                else if (Equals(chinese, ""))
                {
                    Console.Write("语文成绩不得为空!\n请输入语文成绩：");
                    chinese = Console.ReadLine().Trim();
                }
                else if (Equals(math, ""))
                {
                    Console.Write("数学成绩不得为空!\n请输入数学成绩：");
                    math = Console.ReadLine().Trim();
                }
                else if (Equals(english, ""))
                {
                    Console.Write("英语成绩不得为空!\n请输入英语成绩：");
                    english = Console.ReadLine().Trim();
                }
                else
                {
                    conn.Open();
                    string sql = "Select Number from GradeTable where Number='" + number + "'";
                    SqlCommand cmd = new SqlCommand(sql, conn);      //判断该学生成绩信息是否已输入
                    cmd.CommandType = CommandType.Text;
                    SqlDataReader sdt = cmd.ExecuteReader();
                    switch (sdt.Read())
                    {
                        case true:
                            Console.WriteLine("该学生成绩信息已输入!");
                            Main();
                            break;
                        default:
                            Input(name, number, chinese, math, english);
                            break;
                    }
                    conn.Close();
                }
            }
        }

        private void Input(string name, string number, string chinese, string math, string english)  //保存输入的成绩到数据库
        {
            SqlConnection conn = new SqlConnection(Stu);
            try
            {
                conn.Open();
                string InsertStr = string.Format("insert into GradeTable values('{0}','{1}','{2}','{3}','{4}')", name, number, chinese, math, english);
                SqlCommand comm = conn.CreateCommand();
                comm.CommandText = InsertStr;
                SqlCommand mycom = new SqlCommand(InsertStr, conn);
                mycom.ExecuteNonQuery();
                Console.WriteLine("学生成绩输入成功!");
                conn.Close();
                Console.ReadKey();
                Main();
            }
            catch (Exception ex)
            {
                Console.WriteLine("学生成绩输入失败!");
                Console.WriteLine(ex.Message);
                Console.ReadKey();
                Main();
            }
        }

        private void GradeOutput()       //学生成绩输出
        {
            Console.WriteLine("\n****************************** 2.学生成绩输出 ********************************");
            Console.WriteLine("------------------------------------------------------------------------------");
            Console.WriteLine("   学号   |   姓名   |   语文   |   数学   |   英语   |   总分   |   平均分   ");
            Console.WriteLine("------------------------------------------------------------------------------");
            SqlConnection conn = new SqlConnection(Stu);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select* from GradeTable ";
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                string number = reader["Number"].ToString();
                string name = reader["Name"].ToString();
                double chinese = reader.GetDouble(reader.GetOrdinal("Chinese"));
                double math = reader.GetDouble(reader.GetOrdinal("Math"));
                double english = reader.GetDouble(reader.GetOrdinal("English"));
                double sum = chinese + math + english;
                double aver = sum / 3.0;
                Console.WriteLine("   {0}   |   {1}   |    {2}    |    {3}    |    {4}    |   {5:F2}  |   {6:F2}  ", number, name, chinese, math, english, sum, aver);
                Console.WriteLine("------------------------------------------------------------------------------");
            }
            Console.ReadKey();
            Main();
        }

        private void GradeFind()
        {
            Console.WriteLine("\n****************** 3.学生成绩查询 ********************");
            Console.Write("1、按姓名查询；2、按学号查询；0、返回\n请输入选择：");
            string num = Console.ReadLine().Trim();
            switch (num)
            {
                case "1":
                    {
                        Console.Write("请输入学生姓名：");
                        string name = Console.ReadLine().Trim();
                        SelectName(name);
                    }; break;
                case "2":
                    {
                        Console.Write("请输入学生学号：");
                        string number = Console.ReadLine().Trim();
                        SelectNumber(number);
                    }; break;
                case "0": Main(); break;
                default: GradeFind(); break;
            }
        }

        private void SelectName(string Name)    //按学生姓名查找成绩
        {
            SqlConnection conn = new SqlConnection(Stu);
            conn.Open();
            string sqlStr = "Select count(*) from GradeTable where Name='" + Name + "'";
            SqlCommand sqlcmd = new SqlCommand(sqlStr, conn);
            object count = sqlcmd.ExecuteScalar();
            switch ((int)count)
            {
                case 0:
                    {
                        Console.WriteLine("查无此人!");
                        Console.ReadKey();
                        Main();
                    }; break;
                case 1:
                    {
                        SelectNameShow(Name);
                        conn.Close();
                    }; break;
                default:
                    {
                        Console.WriteLine("该学生姓名有多个，请通过学号来查询!");
                        GradeFind();
                    }; break;
            }
            conn.Close();
            Main();
        }

        private void SelectNameShow(string Name)
        {
            SqlConnection conn = new SqlConnection(Stu);
            try
            {
                conn.Open();
                Console.WriteLine("------------------------------------------------------------------------------");
                Console.WriteLine("   学号   |   姓名   |   语文   |   数学   |   英语   |   总分   |   平均分   ");
                Console.WriteLine("------------------------------------------------------------------------------");
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from GradeTable where Name= '" + Name + "'";
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    string number = reader["Number"].ToString();
                    string name = reader["Name"].ToString();
                    double chinese = reader.GetDouble(reader.GetOrdinal("Chinese"));
                    double math = reader.GetDouble(reader.GetOrdinal("Math"));
                    double english = reader.GetDouble(reader.GetOrdinal("English"));
                    double sum = chinese + math + english;
                    double aver = sum / 3.0;
                    Console.WriteLine("   {0}   |   {1}   |    {2}    |    {3}    |    {4}    |   {5:F2}  |   {6:F2}  ", number, name, chinese, math, english, sum, aver);
                    Console.WriteLine("------------------------------------------------------------------------------");
                }
                Console.ReadKey();
                Main();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                conn.Close();
                Main();
            }
        }

        private void SelectNumber(string Number) //按学号查询成绩
        {
            SqlConnection conn = new SqlConnection(Stu);
            conn.Open();
            Console.WriteLine("------------------------------------------------------------------------------");
            Console.WriteLine("   学号   |   姓名   |   语文   |   数学   |   英语   |   总分   |   平均分   ");
            Console.WriteLine("------------------------------------------------------------------------------");
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select * from GradeTable where Number= '" + Number + "'";
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                string name = reader["Name"].ToString();
                string number = reader["Number"].ToString();
                double chinese = reader.GetDouble(reader.GetOrdinal("Chinese"));
                double math = reader.GetDouble(reader.GetOrdinal("Math"));
                double english = reader.GetDouble(reader.GetOrdinal("English"));
                double sum = chinese + math + english;
                double aver = sum / 3.0;
                Console.WriteLine("   {0}   |   {1}   |    {2}    |    {3}    |    {4}    |   {5:F2}  |   {6:F2}  ", number, name, chinese, math, english, sum, aver);
                Console.WriteLine("------------------------------------------------------------------------------");
            }
            else
            {
                Console.WriteLine("查无此人!");
                Console.ReadKey();
                GradeFind();
            }
            conn.Close();
            Console.ReadKey();
            Main();
        }

        private void GradeChange()   //学生成绩修改
        {
            Console.WriteLine("\n----------- 4.学生成绩修改 -----------");
            SqlConnection conn = new SqlConnection(Stu);
            conn.Open();
            Console.Write("请输入学生姓名：");
            string name = Console.ReadLine().Trim();
            Console.Write("请输入学生学号：");
            string number = Console.ReadLine().Trim();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select * from GradeTable where Name='" + name + "' and Number='" + number + "'";
            SqlDataReader sdt = cmd.ExecuteReader();
            while (true)
            {
                if (name.Equals(""))
                {
                    Console.WriteLine("姓名不得为空!\n请输入学生姓名：");
                    name = Console.ReadLine().Trim();
                }
                else if (Equals(number, ""))
                {
                    Console.Write("学号不得为空!\n请输入学生学号：");
                    number = Console.ReadLine().Trim();
                }
                else if (sdt.Read())
                {
                    AlterGrade(number);
                }
                else
                {
                    Console.WriteLine("查无此人!");
                    Console.ReadKey();
                    GradeChange();
                }
            }
        }

        private void AlterGrade(string number)    //选择要修改的课程科目
        {
            Console.Write("\n1、语文；2、数学；3、英语；0、返回\n请选择要修改成绩的学科：");
            string num = Console.ReadLine().Trim();
            switch (num)
            {
                case "1":
                    {
                        Console.Write("请输入修改后的语文成绩：");
                        string chinese = Console.ReadLine().Trim().Trim();
                        Alter(1, number, chinese);
                    }; break;
                case "2":
                    {
                        Console.Write("请输入修改后的数学成绩：");
                        string math = Console.ReadLine().Trim().Trim();
                        Alter(2, number, math);
                    }; break;
                case "3":
                    {
                        Console.Write("请输入修改后的英语成绩：");
                        string english = Console.ReadLine().Trim().Trim();
                        Alter(3, number, english);
                    }; break;
                case "0": Main(); break;
                default: AlterGrade(number); break;
            }
        }

        private void Alter(int num, string number, string item)    //
        {
            SqlConnection conn = new SqlConnection(Stu);
            conn.Open();
            if (num.Equals(1))
            {
                try
                {
                    string InsertStr = string.Format("update GradeTable set Chinese='{0}' WHERE Number='{1}';", item, number);
                    SqlCommand comm = conn.CreateCommand();
                    SqlCommand mycom = new SqlCommand(InsertStr, conn);
                    mycom.ExecuteNonQuery();
                    conn.Close();
                    Console.WriteLine("语文成绩修改成功!");
                    Console.ReadKey();
                    AlterGrade(number);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("成绩修改失败!");
                    Console.WriteLine(ex.Message);
                    conn.Close();
                    AlterGrade(number);
                }
            }

            else if (num.Equals(2))
            {
                try
                {
                    string InsertStr = string.Format("update GradeTable set Math='{0}' WHERE Number='{1}';", item, number);
                    SqlCommand comm = conn.CreateCommand();
                    SqlCommand mycom = new SqlCommand(InsertStr, conn);
                    mycom.ExecuteNonQuery();
                    conn.Close();
                    Console.WriteLine("数学成绩修改成功!");
                    Console.ReadKey();
                    AlterGrade(number);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("成绩修改失败!");
                    Console.WriteLine(ex.Message);
                    conn.Close();
                    AlterGrade(number);
                }
            }
            else if (num.Equals(3))
            {
                try
                {
                    string InsertStr = string.Format("update GradeTable set English='{0}' WHERE Number='{1}';", item, number);
                    SqlCommand comm = conn.CreateCommand();
                    SqlCommand mycom = new SqlCommand(InsertStr, conn);
                    mycom.ExecuteNonQuery();
                    conn.Close();
                    Console.WriteLine("英语成绩修改成功!");
                    Console.ReadKey();
                    AlterGrade(number);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("成绩修改失败!");
                    Console.WriteLine(ex.Message);
                    conn.Close();
                    AlterGrade(number);
                }
            }
        }

        private void GradeDelete()   //学生成绩删除
        {
            SqlConnection conn = new SqlConnection(Stu);
            conn.Open();
            Console.WriteLine("\n****************** 5.学生成绩删除 ********************");
            Console.Write("请输入学生姓名：");
            string name = Console.ReadLine().Trim();
            Console.Write("请输入学生学号：");
            string number = Console.ReadLine().Trim();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select * from GradeTable where Name='" + name + "' and Number='" + number + "'";
            SqlDataReader reader = cmd.ExecuteReader();
            while (true)
            {
                if (Equals(name, ""))
                {
                    Console.Write("姓名不得为空!\n请输入学生姓名：");
                    name = Console.ReadLine().Trim();
                }
                else if (number.Equals(""))
                {
                    Console.Write("学号不得为空!\n请输入学生学号：");
                    number = Console.ReadLine().Trim();
                }
                else if (reader.Read())
                {
                    DelGrade(number);
                }
                else
                {
                    Console.WriteLine("查无此人!");
                    conn.Close();
                    Main();
                }
            }
        }

        private void DelGrade(string number)    //从sql数据库里面输出学生成绩信息
        {
            SqlConnection conn = new SqlConnection(Stu);
            conn.Open();
            try
            {
                string sql2 = "delete from GradeTable where Number='" + number + "'";
                SqlCommand cmd1 = new SqlCommand(sql2, conn);
                cmd1.ExecuteNonQuery();
                Console.WriteLine("成绩信息删除成功!");
                conn.Close();
                Console.ReadKey();
                Main();
            }
            catch (Exception ex)
            {
                Console.WriteLine("成绩信息删除失败!");
                Console.WriteLine(ex.Message);
                conn.Close();
                Main();
            }
        }

    }

5、Program类

Program里面的代码


 class Program
    {
        private static void SelectLogin()
        {
            Console.WriteLine("\n****************************");
            Console.WriteLine("******** 1.学生登录 ********");
            Console.WriteLine("******** 2.教师登录 ********");
            Console.WriteLine("****************************");
            Console.Write("请选择:");
            string n = Console.ReadLine();
            StudentAccount st = new StudentAccount();
            TeacnerAccount sd = new TeacnerAccount();
            switch (n)
            {
                case "1": st.Login(); break;
                case "2": sd.Login(); break;
                default: SelectLogin(); break;
            }
        }

        private static void SelectRegister()
        {
            Console.WriteLine("\n********************************");
            Console.WriteLine("******** 1.学生账号注册 ********");
            Console.WriteLine("******** 2.教师账号注册 ********");
            Console.WriteLine("********************************");
            Console.Write("请选择:");
            string n = Console.ReadLine();
            StudentAccount st = new StudentAccount();
            TeacnerAccount sd = new TeacnerAccount();
            switch (n)
            {
                case "1": st.Register(); break;
                case "2": sd.Register(); break;
                default: SelectRegister(); break;
            }
        }

        public void Main2()
        {
            Console.WriteLine("     学生成绩信息管理系统 ");
            Console.WriteLine("****************************");
            Console.WriteLine("******** 1.账号登录 ********");
            Console.WriteLine("******** 2.账号注册 ********");
            Console.WriteLine("******** 0.退出程序 ********");
            Console.WriteLine("****************************");
            Console.Write("请输入选择：");
            string num = Console.ReadLine().Trim();
            switch (num)
            {
                case "1": SelectLogin(); break;
                case "2": SelectRegister(); break;
                case "0":
                    {
                        Console.WriteLine("程序已退出!");
                        Console.ReadKey();
                        Process.GetCurrentProcess().Kill();
                    }; break;
                default: Main2(); break;
            }
        }

        static void Main()
        {
            Program st = new Program();
            st.Main2();
        }
    }

运行效果截图

在这里插入图片描述

书海shuhai

关注

11
点赞
踩
79

收藏

觉得还不错? 一键收藏
打赏
3
评论
C#程序代码连接SQL Server数据库实现学生成绩信息管理系统（重置版）

先创建一个“学生成绩信息管理系统”数据库，再创建3张表，分别为GradeTable（学生成绩表）、StudentAccount（学生账号信息表）、TeacherAccount（教师账号信息表），设置好数据类型，如图所示。新建一个C#控制台程序，找到App.config文件，在里面填写数据库连接信息，我这里的连接方式为windows身份验证，如图所示。二、配置程序代码这次的代码量相对以往来说有点多，所以我写的时候创建了四个类，分别是：StudentAccount（学生账号信息管理类）、TeacherA
复制链接

扫一扫