数据库课程设计2——学分管理系统

数据库课程设计

课程名：学分管理系统

姓名：XXX

专业班级：XXXX

年级：XXXX

指导老师：XXXX

设计时间：XXXXX

一．课设题目：学分管理系统

1.使用的开发工具：visual studio2010系统, sql server2005 ,windows xp sp3操作系统. 2.开发语言：C#面向对象语言 二．系统开发步骤：

1.系统功能设计（软件结构设计）

步骤:(1)系统总体功能模块图设计

系统总控模块 编辑数据 查询数据 统计数据 系统维护 退出 添加，删除 学生 信息 添加 课程信息 编辑学生分数 按课程号查 按班级号查 查不及格名单 查应留级名单 按姓名查(按姓名) 按班级统计不及格人数及比例 按班级统计各分数段人数及比例 各科的平均分数 密码的设置与修改 数据库数据转储 代码库的维护 帮助 (2)系统详细功能设计

将总体功能模块图中的每个模块进行逻辑过程的设计。

a.系统封面设计：画出封面的显示图

b.系统总控模块设计(菜单):画出菜单的显示图 2.数据库结构的设计 a.数据库表格：

Student(Sno,Sclass,Ssex,Sdept,Sname,Spsw), Course(Cno,Cname), SC(Sno, Cno,Grade), denglu(us_id,us_name,us_psw), b. ER图：

姓名 性别 班级 专业 学号 学生 登录密码 选课 课程编号 课程 课程名称 登陆名 管理员 编号 登录密 码

3.系统的功能实现界面

1>登录界面：（用户角色：学生和管理员） 学生登录界面：

管理员登录界面：

2>学生模块：（查询个人的课程信息，个人信息，

查询各科的成绩，修改登录密码，退出系统）

3>管理员模块：（编辑数据，查询数据，统计数据，

密码的设置与修改）

修改用户密码

编辑学生分数

编辑分数界面

各科平均分数及比例

各学生的平均分数

4.系统的代码（前台和后台）

1>学生界面的母版页：

2>管理员界面的母版页：

3>登录界面的关键代码：

public partial class _Default : System.Web.UI.Page {

protected void Page_Load(object sender, EventArgs e) {

}

protected void Button1_Click(object sender, EventArgs e)//登录按钮触发的事件 {

string type = userType.SelectedValue.ToString(); string user = userName.Text; string password = FormsAuthentication.HashPasswordForStoringInConfigFile(pwd.Text, \"MD5\"); string sel = \"\"; switch (type) {

case \"1\": sel = \"select Sno from student where Sname='\" + user + \"' and Spswd='\" + password + \"'\";

break;

case \"2\": sel = \"select us_id from denglu where us_name='\" + user + \"' and us_psw='\" + password + \"'\"; break; }

object body = dbConnection.MyExecuteScalar(sel); if (body == null) {

Page.ClientScript.RegisterStartupScript(this.GetType(), \"\\"\"); } else {

Session[\"userID\"] = body.ToString(); switch (type) {

case \"1\": Response.Redirect(\"student/student_index.aspx\"); break; case \"2\":

Response.Redirect(\"admin/admin_index.aspx\"); break; } } }

protected void Button2_Click(object sender, EventArgs e)//重置按钮触发的事件 {

userName.Text = \"\"; pwd.Text = \"\"; } }

4>学生修改个人密码的后台代码：

protected void tijiao_Click(object sender, EventArgs e)//提交按钮触发的事件 {

string pwd = FormsAuthentication.HashPasswordForStoringInConfigFile(this.txtoldpwd.Text, \"md5\");

/////修改密码

if (txtnewpwd2.Text.Trim() == txtnewpwd.Text.Trim()) {

string newpwd = FormsAuthentication.HashPasswordForStoringInConfigFile(txtnewpwd.Text, \"md5\");

string sele = \"select count(Sno) from Student where Sno=\" +

Convert.ToInt32(Session[\"userID\"].ToString()) + \" and Spswd='\" + pwd + \"'\";

int num = Convert.ToInt32(dbConnection.MyExecuteScalar(sele).ToString()); if (num > 0) {

string chang = \"update Student set Spswd='\" + newpwd + \"' where Sno=\" + Convert.ToInt32(Session[\"userID\"].ToString()); if (dbConnection.insert(chang))

Response.Write(\"\");

else

Response.Write(\"\"); } else {

Response.Write(\"\"); } } else {

Response.Write(\"\"); } }

protected void btcancel_Click(object sender, EventArgs e)//取消修改 {

txtoldpwd.Text = \"\"; txtnewpwd.Text = \"\"; txtnewpwd2.Text = \"\"; } }

5>学生查看个人信息的后台代码：

public partial class student_student_info : System.Web.UI.Page {

if (!IsPostBack) {

student stu = new student();

DataTable dt = new DataTable();

dt = CreateAdapter.getAdapter(\"select Sno,Sclass,Sdept,Ssex,Sname from Student where Sno=\" + Convert.ToInt32(Session[\"userID\"].ToString())); stu.setSclass(dt.Rows[0][1].ToString()); stu.setSdept(dt.Rows[0][2].ToString()); stu.setSname(dt.Rows[0][4].ToString()); stu.setSno(dt.Rows[0][0].ToString()); stu.setSsex(dt.Rows[0][3].ToString());

Session.Add(\"stu\ Sno.Text=stu.getSno(); Sclass.Text=stu.getSclass(); Ssex.Text=stu.getSsex(); Sname.Text=stu.getSname(); dept.Text=stu.getSdept(); } } }

6>学生查看课程的后台代码：

protected void Page_Load(object sender, EventArgs e) {

string sql = \"select Cname,Course.Cno from Student,Course,SC where Student.Sno=SC.Sno and Course.Cno=SC.Cno and Student.Sno=\" + Convert.ToInt32(Session[\"userID\"].ToString()); DataTable dt = new DataTable(); dt = CreateAdapter.getAdapter(sql); grid_course.DataSource = dt; grid_course.DataBind(); }

7>学生查看分数的后台代码：

protected void Page_Load(object sender, EventArgs e) {

string sql = \"select Cname,Grade from Student,Course,SC where Student.Sno=SC.Sno and Course.Cno=SC.Cno and Student.Sno=\"+Convert.ToInt32(Session[\"userID\"].ToString()); DataTable dt = new DataTable();

dt = CreateAdapter.getAdapter(sql); grid_Sname.DataSource = dt; grid_Sname.DataBind(); }

8>管理员添加学生课程：

protected void submit_Click(object sender, EventArgs e)//提交按钮触发的事件 {

string sql = \"insert into Course(Cno,Cname) \" + \"values(@Cno,@Cname)\";

List parmeters = new List(); parmeters.Add(new SqlParameter(\"@Cno\ parmeters.Add(new SqlParameter(\"@Cname\ //dbConnection.MyExecuteScalar(sql);

if (Boolean.Parse(dbConnection.insert(sql, parmeters).ToString())) {

Response.Write(\"\"); } else

Response.Write(\"\"); }

protected void Button1_Click(object sender, EventArgs e)//给新课添加学生按钮触发的事件 {

string sql = \"select Sno from Student\";

DataTable dt = CreateAdapter.getAdapter(sql); for (int i = 0; i < dt.Rows.Count; i++) {

string sql1 = \"insert into SC(Sno,Cno,Grade) values(@Sno,@Cno,'')\"; List parmeters1 = new List(); parmeters1.Add(new SqlParameter(\"@Cno\ parmeters1.Add(new SqlParameter(\"@Sno\

if (Boolean.Parse(dbConnection.insert(sql1, parmeters1).ToString())) {

Response.Write(\"\");

} else

Response.Write(\"\");

} } }

9>管理员添加学生：

protected void add_Click(object sender, EventArgs e)//添加学生 {

string sql = \"insert into Student(Sno,Sname,Ssex,Sdept,Sclass,Spswd) \" + \"values(@Sno,@Sname,@Ssex,@Sdept,@Sclass,@Spswd)\"; //string sql1=\"insert into SC()\"

List parmeters = new List(); parmeters.Add(new SqlParameter(\"@Sno\ parmeters.Add(new SqlParameter(\"@Sname\ parmeters.Add(new SqlParameter(\"@Ssex\

parmeters.Add(new SqlParameter(\"@Sdept\ parmeters.Add(new SqlParameter(\"@Sclass\ parmeters.Add(new SqlParameter(\"@Spswd\FormsAuthentication.HashPasswordForStoringInConfigFile(\"123\

if (Boolean.Parse(dbConnection.insert(sql, parmeters).ToString())) {

Response.Write(\"\"); } else

Response.Write(\"\"); }

protected void again_Click(object sender, EventArgs e)//重置按钮触发的事件 {

Snumber.Text = \"\"; Sname.Text=\"\"; sex.Text=\"\";

S_class.Text = \"\"; }

protected void add_course_Click(object sender, EventArgs e)//为学生添加课程 {

string sql = \"select Cno from Course\";

DataTable dt = CreateAdapter.getAdapter(sql); for (int i = 0; i < dt.Rows.Count; i++) {

string sql1 = \"insert into SC(Sno,Cno,Grade) values(@Sno,@Cno,'')\";

List parmeters1 = new List(); parmeters1.Add(new SqlParameter(\"@Cno\ parmeters1.Add(new SqlParameter(\"@Sno\

if (Boolean.Parse(dbConnection.insert(sql1, parmeters1).ToString())) {

Response.Write(\"\");

} else

Response.Write(\"\"); } }

10>管理员修改个人密码：

protected void bt_tijiao_Click(object sender, EventArgs e)//提交按钮触发的事件 {

string pwd = FormsAuthentication.HashPasswordForStoringInConfigFile(this.txtoldpwd.Text, \"md5\");

if (txtnewpwd2.Text.Trim() == txtnewpwd.Text.Trim()) {

string newpwd = FormsAuthentication.HashPasswordForStoringInConfigFile(txtnewpwd.Text, \"md5\");

string sele = \"select count(us_id) from denglu where us_id=\" +

Convert.ToInt32(Session[\"userID\"].ToString()) + \" and us_psw='\" + pwd + \"'\";

int num = Convert.ToInt32(dbConnection.MyExecuteScalar(sele).ToString()); if (num > 0) {

string chang = \"update denglu set us_psw='\" + newpwd + \"' where us_id=\" + Convert.ToInt32(Session[\"userID\"].ToString()); if (dbConnection.insert(chang))

Response.Write(\"\");

else

Response.Write(\"\"); } else

{

Response.Write(\"\"); } } else {

Response.Write(\"\"); } }

protected void cancel_Click(object sender, EventArgs e) {

txtoldpwd.Text = \"\"; txtnewpwd.Text = \"\"; txtnewpwd2.Text = \"\"; }

11>管理员编辑学生分数：

AutoGenerateColumns=\"False\" CellPadding=\"4\" DataKeyNames=\"Sno,Cno\" PageSize=\"20\"

DataSourceID=\"SqlDataSource1\" ForeColor=\"#333333\" GridLines=\"None\" Width=\"811px\">

温馨提示：当前没有任何记录哦。

ConnectionString=\"<%$ ConnectionStrings:db_xuefenConnectionString %>\"

DeleteCommand=\"DELETE FROM [SC] WHERE [Sno] = @Sno AND [Cno] = @Cno\"

InsertCommand=\"INSERT INTO [SC] ([Sno], [Cno], [Grade]) VALUES (@Sno, @Cno, @Grade)\" SelectCommand=\"SELECT * FROM [SC]\"

UpdateCommand=\"UPDATE [SC] SET [Grade] = @Grade WHERE [Sno] = @Sno AND [Cno] = @Cno\">

12>按照课程编号查：

using System;

using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI;

using System.Web.UI.WebControls; using System.Data; using System.IO;

public partial class teacher_check_by_Cno : System.Web.UI.Page {

string Cno = \"\";

protected void Page_Load(object sender, EventArgs e) {

if (Session[\"userID\"] == null) {

Page.ClientScript.RegisterStartupScript(this.GetType(), ');location.href='../Default.aspx'\");

\"\\"\"); } }

protected void Button1_Click(object sender, EventArgs e) {

string sql = \"select Student.Sno,Sname,Sdept,Sclass,Ssex,Course.Cno,Cname \"+ \"from Student,Course,SC where Student.Sno=SC.Sno \"+

\" and Course.Cno=SC.Cno and Sdept like '\"+dept.Text.ToString()+\"'\"+\"and Sclass like '\"+class1.Text.ToString()+\"'\";

DataTable dt = new DataTable(); dt = CreateAdapter.getAdapter(sql); grid_class.DataSource = dt; grid_class.DataBind(); } }

14>按照学生姓名查：

using System;

using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI;

using System.Web.UI.WebControls; using System.Data; using System.IO;

public partial class teacher_check_by_Sname : System.Web.UI.Page {

protected void Page_Load(object sender, EventArgs e) {

if (Session[\"userID\"] == null)

{

Page.ClientScript.RegisterStartupScript(this.GetType(), \"\\"\"); } }

protected void Button1_Click(object sender, EventArgs e) { string + \"'\";

DataTable dt = new DataTable(); dt = CreateAdapter.getAdapter(sql); grid_Sname.DataSource = dt; grid_Sname.DataBind(); } }

sql

=

\"select

Student.Sno,Sname,Sdept,Sclass,Ssex,Course.Cno,Cname,Grade

from

Student,Course,SC where Student.Sno=SC.Sno and Course.Cno=SC.Cno and Sname like '\" + name.Text.ToString()

15>查各科平均分数：

using System;

using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI;

using System.Web.UI.WebControls; using System.IO; using System.Data;

public partial class teacher_course_average : System.Web.UI.Page {

protected void Page_Load(object sender, EventArgs e) {

if (Session[\"userID\"] == null) {

Page.ClientScript.RegisterStartupScript(this.GetType(), \"\\"\"); }

string sql = \"select Cno,avg(Grade) as AVGrade from SC group by Cno\"; DataTable dt = new DataTable(); dt = CreateAdapter.getAdapter(sql); grid_course.DataSource = dt; grid_course.DataBind(); }

}

16>修改用户密码：

using System;

using System.Collections.Generic; using System.Web; using System.Web.UI;

using System.Web.UI.WebControls; using System.Data; using System.Web.Security; using System.IO;

using System.Data.SqlClient;

public partial class createpsw : System.Web.UI.Page {

protected void Page_Load(object sender, EventArgs e) { }

protected void Button1_Click(object sender, EventArgs e) { string stu_newpwd FormsAuthentication.HashPasswordForStoringInConfigFile(TextBox1.Text.ToString(), \"md5\"); string

tea_newpwd

FormsAuthentication.HashPasswordForStoringInConfigFile(TextBox1.Text.ToString(), \"md5\"); string update = \"update Student set Spswd='\" + stu_newpwd + \"';\" + \"update denglu set us_psw='\" + tea_newpwd + \"'\"; if (dbConnection.insert(update))

Response.Write(\"\"); } }

17>删除学生：

using System;

using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI;

using System.Web.UI.WebControls; using System.Data.SqlClient; using System.Data;

= =

public partial class admin_deletestudent : System.Web.UI.Page {

DataTable dt = new DataTable();

protected void Page_Load(object sender, EventArgs e) {

if (Session[\"userID\"] == null) {

Page.ClientScript.RegisterStartupScript(this.GetType(), \"\\"\"); }

string sql = \"select Sno,Sclass,Sdept,Ssex,Sname from Student\";

dt = CreateAdapter.getAdapter(sql); grid_student.DataSource = dt; grid_student.DataBind(); }

protected void studnetediting(object sender, GridViewEditEventArgs e) {

string no = dt.Rows[0][0].ToString();

string sql1 = \"delete from SC where Sno='\" + no + \"'\"; string sql2 = \"delete from Student where Sno='\" + no + \"'\"; dbConnection.MyExecuteScalar(sql1); dbConnection.MyExecuteScalar(sql2); Response.Redirect(\"deletestudent.aspx\"); } }

18>应留级的学生：

using System;

using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI;

using System.Web.UI.WebControls; using System.Data; using System.IO;

public partial class teacher_down_grade : System.Web.UI.Page {

protected void Page_Load(object sender, EventArgs e) {

if (Session[\"userID\"] == null) {

Page.ClientScript.RegisterStartupScript(this.GetType(), \"\\"\"); }

string sql = \"select Sno,Sname,sdept,Sclass,Ssex from student where Sno in (select Sno from SC where Grade<60 group by Sno having count(Cno)>=3)\"; DataTable dt = new DataTable(); dt = CreateAdapter.getAdapter(sql); down_grade.DataSource = dt; down_grade.DataBind(); } }

19>各班不及格的人数及比例：

using System;

using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI;

using System.Web.UI.WebControls; using System.Data;

public partial class teacher_nopass_class : System.Web.UI.Page {

protected void Page_Load(object sender, EventArgs e) {

if (Session[\"userID\"] == null) {

Page.ClientScript.RegisterStartupScript(this.GetType(), \"\\"\"); } }

protected void Button1_Click(object sender, EventArgs e) {

string sql = \"select Cno,count(Sno) as number,(select distinct count(Sno) from SC \" +

\"where SC.Sno in(select Sno from student where Sdept='\" + dept.Text.ToString() + \"'\" + \"and Sclass='\" + class1.Text.ToString() + \"') \" +

\"group by Cno) as totel,cast(count(Sno)*1.0/(select distinct count(Sno) from SC \" + \"where SC.Sno in(select Sno from student where Sdept='\" + dept.Text.ToString() + \"' and Sclass='\" + class1.Text.ToString() + \"') \" +

\"group by Cno) as varchar) +'%' as scale \" +

\"from SC where Grade<60 and SC.Sno in \" +

\"(select Sno from student where Sdept='\" + dept.Text.ToString() + \"'\" + \"and Sclass='\" + class1.Text.ToString() + \"') group by Cno\";

DataTable dt = new DataTable(); dt = CreateAdapter.getAdapter(sql); grid_class.DataSource = dt; grid_class.DataBind(); } }

20>不及格的学生：

using System;

using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI;

using System.Web.UI.WebControls; using System.Data; using System.IO;

public partial class teacher_nopass_students : System.Web.UI.Page {

protected void Page_Load(object sender, EventArgs e) {

if (Session[\"userID\"] == null) {

Page.ClientScript.RegisterStartupScript(this.GetType(), \"\\"\"); }

string

sql

=

\"select

Student.Sno,Sname,Sdept,Sclass,Ssex,Course.Cno,Cname,Grade

from

Student,Course,SC where Student.Sno=SC.Sno and Course.Cno=SC.Cno and Grade <60\"; DataTable dt = new DataTable(); dt = CreateAdapter.getAdapter(sql); grid_Sname.DataSource = dt; grid_Sname.DataBind(); } }

21>各分数段的人数及比例：

using System;

using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI;

using System.Web.UI.WebControls; using System.Data;

public partial class teacher_numbers_of_everyGrade : System.Web.UI.Page {

protected void Page_Load(object sender, EventArgs e) {

if (Session[\"userID\"] == null) {

Page.ClientScript.RegisterStartupScript(this.GetType(), \"\\"\"); } }

protected void Button1_Click(object sender, EventArgs e) {

//string sql = \"select Cno,count(Sno) as number from SC where Grade between \" + grade1.Text + \"and \" + grade2.Text +

// \" and SC.Sno in(select Sno from student where Sdept='\"+dept.Text.ToString()+\"'\"+\" and Sclass='\"+class1.Text.ToString()+\"')group by Cno\";

string sql1 = \"select Cno,count(Sno) as number,(select distinct count(Sno) from SC \"+

\"where SC.Sno in(select Sno from student where Sdept='\" + dept.Text.ToString() + \"'\" + \"and Sclass='\" + class1.Text.ToString() + \"') \" +

\"group by Cno) as totel,cast(count(Sno)*1.0/(select distinct count(Sno) from SC \"+ \"where SC.Sno in(select Sno from student where Sdept='\" + dept.Text.ToString() + \"' and Sclass='\" + class1.Text.ToString() + \"') \" +

\"group by Cno) as varchar) +'%' as scale \" +

\"from SC where Grade between 50 and 70 and SC.Sno in \"+

\"(select Sno from student where Sdept='\" + dept.Text.ToString() + \"'\"+\"and Sclass='\" + class1.Text.ToString() + \"') group by Cno\"; DataTable dt = new DataTable(); dt = CreateAdapter.getAdapter(sql1); grid_grade.DataSource = dt; grid_grade.DataBind(); } }

22>学生的平均成绩：

using System;

using System.Collections.Generic;

using System.Linq; using System.Web; using System.Web.UI;

using System.Web.UI.WebControls; using System.Data;

public partial class teacher_student_avag : System.Web.UI.Page {

protected void Page_Load(object sender, EventArgs e) { if (Session[\"userID\"] == null) {

Page.ClientScript.RegisterStartupScript(this.GetType(), \"\\"\"); }

string sql = \"select Sno,avg(Grade) as avag from SC group by Sno\"; DataTable dt = new DataTable(); dt = CreateAdapter.getAdapter(sql); grid_avag.DataSource = dt; grid_avag.DataBind(); } }

23>连接数据库的类的编写：

using System;

using System.Collections.Generic; using System.Web; using System.Data;

using System.Data.SqlClient;

///

public class CreateAdapter {

public CreateAdapter() { //

//TODO: 在此处添加构造函数逻辑 // }

public static DataTable getAdapter(string sql)

{

SqlDataAdapter da = null;

SqlConnection ocon = dbConnection.getcon(); da = new SqlDataAdapter(sql, ocon); DataTable tb = new DataTable(); da.Fill(tb); return tb; }

public static SqlDataReader getReader(string sql) {

SqlDataReader reader = null;

SqlConnection ocon = dbConnection.getcon(); SqlCommand cmd = new SqlCommand(sql, ocon); try {

ocon.Open();

reader = cmd.ExecuteReader(); }

catch (SqlException ex) { } finally {

ocon.Close();

}

return reader; } }

using System;

using System.Collections.Generic; using System.Web; using System.Data;

using System.Data.SqlClient;

///

public class dbConnection { //

//TODO: 在此处添加构造函数逻辑 //

///