学生信息管理系统
包含内容: 源码,全套工具
作者QQ549710689
下载Demo
1．系统概述
本次是做一个学生管理系统，在学了数据库之后，希望通过这个系统把所学知识应用与实际项目中去。加强自己的熟练程度。 开发背景：具备数据库，C#的相关知识储备，掌握基本的html知识。
用途：仅作练习用。通过一门语言，将数据库展现出来。
2．功能需求分析
系统概念层设计：功能用例图如下
1. 登录功能：用于身份认证
2. 数据操作：信息查询，数据的增删改。
a) 数据操作里，管理员具备所有的操作权限。学生具有选课和查看的功能。以及可以修改自己的个人信息。
3. UI设计
3．数据库设计
E-R图 1
首先，学生表（tb_student）设计如下
列名 | 数据类型 | 是否为空(默认未是) | 主键（默认为否） |
Student_name | Nvarchar (15) | ||
Student_num | int | 否 | 是 |
Student_password | Nvarchar(30) | ||
Student_sex | Nvarchar(5) | ||
Student_age | smallint | ||
Student_dept | Nvarchar(20) |
tb_course
列名 | 数据类型 | 是否为空 | 主键 |
Course_num | int | 否 | 是 |
Course_name | Nvarchar(30) | ||
Course_credit | smallint | ||
Course_semester | smallint |
tb_student_course
列名 | 数据类型 | 是否为空 | 主键 |
Sc_id | int | 否 | 是 |
Student_num | int | 外键 | |
Course_num | int | 外键 | |
grade | smallint |
tb_admin
列名 | 数据类型 | 是否为空 | 主键 |
Admin_id | smallint | 否 | 是 |
admin_name | Nvarchar(20) | ||
Admin_password | Nvarchar(40) | ||
remark | Nvarcahr(10) |
一.配置数据库连接
首先，第一步配置好数据库连接：
1.在app.config文件里添加连接字符串，如下代码：
<?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> </configSections> <connectionStrings> <add name="sql_homework_end.Properties.Settings.sql_homework_endConnectionString" connectionString="Data Source=.;Initial Catalog=sql_homework_end;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" /> </startup> </configuration>
2.建立数据库公共类：
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
namespace sql_homework_end
{
class sqlhelper
{
private static string connStr = ConfigurationManager.ConnectionStrings["sql_homework_end.Properties.Settings.sql_homework_endConnectionString"].ConnectionString;
/// <summary>
/// 返回受影响的数据行数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteNoQuery(string sql)
{
using (SqlConnection conn=new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText = sql;
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 返回一个数据集
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string sql)
{
using (SqlConnection xonn=new SqlConnection(connStr))
{
xonn.Open();
using (SqlCommand cmd = xonn.CreateCommand())
{
cmd.CommandText = sql;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset;
}
}
}
public static object ExecuteScalar(string sql)
{
using (SqlConnection conn=new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText = sql;
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// md5加密
/// </summary>
/// <param name="strPwd"></param>
/// <returns></returns>
public static string GetMD5(string strPwd)
{
string pwd = "";
//实例化一个md5对象
MD5 md5 = MD5.Create();
// 加密后是一个字节类型的数组
byte[] s = md5.ComputeHash(Encoding.UTF8.GetBytes(strPwd));
//翻转生成的MD5码
s.Reverse();
//通过使用循环，将字节类型的数组转换为字符串，此字符串是常规字符格式化所得
//只取MD5码的一部分，这样恶意访问者无法知道取的是哪几位
for (int i = 3; i < s.Length - 1; i++)
{
//将得到的字符串使用十六进制类型格式。格式后的字符是小写的字母，如果使用大写（X）则格式后的字符是大写字符
//进一步对生成的MD5码做一些改造
pwd = pwd + (s[i] < 198 ? s[i] + 28 : s[i]).ToString("X");
}
return pwd;
}
}
}二.功能设计
1.登录功能
界面预览：
功能有：身份验证，退出。公共变量传值。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace sql_homework_end
{
public partial class login : Form
{
public login()
{
InitializeComponent();
}
//界面传值
public static string GlobelValue; // 注意，必须申明为static变量
//登录按钮事件
private void btn_login_Click(object sender, EventArgs e)
{
if (cmb_identity.Text.ToString()=="管理员")
{
verify_identidy("tb_admin","admin_name");
main a = new main();
a.Show();
}
else if (cmb_identity.Text.ToString()=="学生")
{
verify_identidy("tb_student","student_name");
studnet s = new studnet();
s.Show();
}
}
//身份验证的函数
private void verify_identidy(string table,string name)
{
DataSet ds = new DataSet();
ds = sqlhelper.ExecuteDataSet("select * from "+table+" where "+name+" = '" + tbx_loginname.Text + "'");
DataTable dt = new DataTable();
dt = ds.Tables[0];
if (dt.Rows.Count != 0)
{
GlobelValue = tbx_loginname.Text;
this.Visible = false; //隐藏当前窗体
}
else
{
MessageBox.Show("用户名不存在，请重新输入");
}
}
//退出按钮
private void btn_close_Click(object sender, EventArgs e)
{
this.Close();
}
}
}2.主界面-管理员
预览：
管理员信息：
学生信息：
主要使用控件：datagridview
相关代码：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace sql_homework_end
{
public partial class main : Form
{
public main()
{
InitializeComponent();
}
//绑定并显示相关信息
DataSet ds = new DataSet();
DataTable dt = new DataTable();
private void 学生信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
ds = sqlhelper.ExecuteDataSet("select * from tb_student");
dt = ds.Tables[0];
dataGridView1.DataSource = dt;
}
private void 课程信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
ds = sqlhelper.ExecuteDataSet("select * from tb_course");
dt = ds.Tables[0];
dataGridView1.DataSource = dt;
}
private void 选课信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
ds = sqlhelper.ExecuteDataSet("select * from tb_student_course");
dt = ds.Tables[0];
dataGridView1.DataSource = dt;
dataGridView1.Columns["sc_id"].DisplayIndex = 0;
}
private void 管理员信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
ds = sqlhelper.ExecuteDataSet("select * from tb_admin");
dt = ds.Tables[0];
dataGridView1.DataSource = dt;
}
//添加按钮事件
private void btn_insert_Click(object sender, EventArgs e)
{
if (dataGridView1.Columns[0].HeaderText=="student_num")
{
string sql = @"insert tb_student (student_num,student_name,student_password,student_sex
,student_age,student_dept)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" +
dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value
+ "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[4].Value
+ "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[5].Value + "')";
sqlhelper.ExecuteNoQuery(sql);
}
else if (dataGridView1.Columns[0].HeaderText=="course_num")
{
string sql = @"insert tb_course (course_num,course_name,course_credit,course_semester)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "')";
sqlhelper.ExecuteNoQuery(sql);
}
else if (dataGridView1.Columns[0].HeaderText=="sc_id")
{
try
{
string sql = @"insert tb_student_course (sc_id,student_num,course_num,grade)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "')";
sqlhelper.ExecuteNoQuery(sql);
}
catch (Exception)
{
MessageBox.Show("学号或姓名不存在，请重新添加。");
}
}
else if (dataGridView1.Columns[0].HeaderText == "admin_id")
{
string sql = @"insert tb_admin (admin_id,admin_name,admin_password,remark)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "')";
sqlhelper.ExecuteNoQuery(sql);
}
MessageBox.Show("添加成功");
}
//更新按钮事件
private void btn_update_Click(object sender, EventArgs e)
{
if (dataGridView1.Columns[0].HeaderText == "student_num")
{
update("tb_student","student_num");
}
else if (dataGridView1.Columns[0].HeaderText == "course_num")
{
update("tb_course","course_num");
}
else if (dataGridView1.Columns[0].HeaderText == "admin_id")
{
update("tb_admin","admin_id");
}
else if (dataGridView1.Columns[0].HeaderText == "sc_id")
{
try
{
update("tb_studnet_course","sc_id");
}
catch (Exception)
{
MessageBox.Show("学号或课程号不存在，请重新输入");
throw;
}
}
}
//更新方法
private void update(string table,string head_id)
{
for (int i = 0; i < dataGridView1.RowCount; i++)
{
int id = Convert.ToInt32(dataGridView1.Rows[i].Cells[0].Value);
for (int j = 1; j < dataGridView1.ColumnCount; j++)
{
if (dataGridView1.Columns[j].Visible == true)
{
string columnName = dataGridView1.Columns[j].Name.ToString();
string sql = "update " + table + " set " + columnName + "='" + dataGridView1.Rows[i].Cells[j].Value + "' where " + head_id + "='" + id + "'";
sqlhelper.ExecuteNoQuery(sql);
}
}
}
MessageBox.Show("更新成功");
}
//删除事件
private void btn_delete_Click(object sender, EventArgs e)
{
if (dataGridView1.Columns[0].HeaderText == "student_num")
{
string sql = "delete from tb_student where student_num='" + dataGridView1.SelectedCells[0].Value + "'";
sqlhelper.ExecuteNoQuery(sql);
}
else if (dataGridView1.Columns[0].HeaderText == "course_num")
{
string sql = "delete from tb_course where course_num='" + dataGridView1.SelectedCells[0].Value + "'";
sqlhelper.ExecuteNoQuery(sql);
}
else if (dataGridView1.Columns[0].HeaderText == "sc_id")
{
string sql = "delete from tb_student_course where sc_id='" + dataGridView1.SelectedCells[0].Value + "'";
sqlhelper.ExecuteNoQuery(sql);
}
else if (dataGridView1.Columns[0].HeaderText == "admin_id")
{
string sql = "delete from tb_admin where admin_id='" + dataGridView1.SelectedCells[0].Value + "'";
sqlhelper.ExecuteNoQuery(sql);
}
MessageBox.Show("删除成功");
}
//界面载入显示身份和登录时间
private void main_Load(object sender, EventArgs e)
{
lbl_username.Text = "Welcome," + login.GlobelValue + "";
lbl_logintime.Text ="登录时间:" +DateTime.Now.ToString()+"";
}
}
}3.学生界面
界面预览：
个人信息显示：在这个界面，学生能看到自己的信息，并进行修改操作。
代码：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace sql_homework_end
{
public partial class studnet : Form
{
public studnet()
{
InitializeComponent();
}
DataSet ds = new DataSet();
DataTable dt = new DataTable();
private void studnet_Load(object sender, EventArgs e)
{
lbl_username.Text = "Welcome," + login.GlobelValue + "";
lbl_logintime.Text = "登录时间:" + DateTime.Now.ToString() + "";
}
//相关信息显示
private void 个人信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
ds = sqlhelper.ExecuteDataSet("select * from tb_student where student_name='" + login.GlobelValue + "'");
dt = ds.Tables[0];
dataGridView1.DataSource = dt;
btn_update.Visible = true;
btn_insert.Visible = false;
btn_delete.Visible = false;
}
private void 课程信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
ds = sqlhelper.ExecuteDataSet("select * from tb_course");
dt = ds.Tables[0];
dataGridView1.DataSource = dt;
btn_update.Visible = false;
btn_insert.Visible = false;
btn_delete.Visible = false;
}
private void 选课管理ToolStripMenuItem_Click(object sender, EventArgs e)
{
ds = sqlhelper.ExecuteDataSet(@"select sc.sc_id,s.student_num,c.course_num,c.course_name,grade
from tb_student_course sc join tb_student s on sc.student_num=s.student_num
join tb_course c on sc.course_num=c.course_num
where s.student_name = '" + login.GlobelValue + "'");
dt = ds.Tables[0];
dataGridView1.DataSource = dt;
dataGridView1.Columns["sc_id"].DisplayIndex = 0;
btn_update.Visible = false;
btn_insert.Visible = true;
btn_delete.Visible = true;
}
//学生选课功能
private void btn_insert_Click_1(object sender, EventArgs e)
{
if (Convert.ToInt32(dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[4].Value) > 0)
{
MessageBox.Show("不准填写成绩");
}
else
{
string sql = @"insert tb_student_course (sc_id,student_num,course_num)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value + "')";
sqlhelper.ExecuteNoQuery(sql);
MessageBox.Show("选课成功");
}
}
//删除选的课程
private void btn_delete_Click(object sender, EventArgs e)
{
string sql = "delete from tb_student_course where sc_id='" + dataGridView1.SelectedCells[0].Value + "'";
sqlhelper.ExecuteNoQuery(sql);
MessageBox.Show("删除成功，请重新选课。");
}
//更新个人信息
private void btn_update_Click(object sender, EventArgs e)
{
update("tb_student", "student_num");
}
private void update(string table, string head_id)
{
for (int i = 0; i < dataGridView1.RowCount; i++)
{
int id = Convert.ToInt32(dataGridView1.Rows[i].Cells[0].Value);
for (int j = 1; j < dataGridView1.ColumnCount; j++)
{
if (dataGridView1.Columns[j].Visible == true)
{
string columnName = dataGridView1.Columns[j].Name.ToString();
string sql = "update " + table + " set " + columnName + "='" + dataGridView1.Rows[i].Cells[j].Value + "' where " + head_id + "='" + id + "'";
sqlhelper.ExecuteNoQuery(sql);
}
}
}
MessageBox.Show("更新成功");
}
}
}