开发语言选择C#,数据库选择SQLSERVER2008
“学生成绩管理系统”开发要求如下:
- 数据库中包含学生、教师、课程、选课共计四张表
- 系统用户为任课教师
- 系统功能包括:
- 登录与注销
- 查看所承担课程的学生选课情况
- 添加、删除选课记录
- 成绩录入
- 成绩修改
界面截图如下:
登录界面:
登录后的主界面:
选课管理
成绩管理
工程目录截图:
配置文件代码如下:
App.config
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/>
</startup>
<connectionStrings>
<clear/>
<add name="MConn" connectionString="Data Source=.; Database=DB; Integrated Security=false;User ID=sa;Password=sa;" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
工程详细代码如下:
SQLHelper
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace MySchool
{
class SQLHelper
{
static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MConn"].ConnectionString;
SqlConnection conn = new SqlConnection(connectionString);
/// <summary>
/// 执行增删改
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public bool ExcuteNonQuery(string sql)
{
int success = 0;
conn.Open();
using (var command = new SqlCommand(sql, conn))
{
success = command.ExecuteNonQuery();
}
conn.Close();
return success > 0;
}
/// <summary>
/// 查询数据并返回table
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable ExcuteQuery(string sql)
{
conn.Open();
DataTable dataTable = new DataTable();
using (var command = new SqlCommand(sql, conn))
{
using (var reader = command.ExecuteReader())
{
dataTable.Load(reader);
}
}
conn.Close();
return dataTable;
}
}
}
登录界面代码:
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 MySchool
{
public partial class LoginForm : Form
{
SQLHelper help = new SQLHelper();
public LoginForm()
{
InitializeComponent();
}
/// <summary>
/// 取消按钮事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_cancel_Click(object sender, EventArgs e)
{
Application.Exit();
}
/// <summary>
/// 登录按钮事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_login_Click(object sender, EventArgs e)
{
string tch_no = txt_userName.Text.Trim();
string tch_name = txt_pwd.Text.Trim();
if (tch_no.Length > 0 && tch_name.Length > 0)
{
string sql = string.Format("select RTRIM(T#) as TNo from dbo.t_teacher where Tname='{0}' and T#='{1}'", tch_name, tch_no);
DataTable dt = help.ExcuteQuery(sql);
if(dt.Rows.Count==1)
{
MessageBox.Show("登录成功!", "系统提示");
string tchNo = Convert.ToString(dt.Rows[0]["TNo"]);
MainForm mf = new MainForm(tchNo);
mf.Show();
mf.Text = string.Format("当前登录教师[{0}]",tch_name);
this.Hide();
}
else
{
MessageBox.Show("登录失败,请检查教师号和教师名!", "系统提示");
}
}
else
{
MessageBox.Show("请输入教师号和教师名!", "系统提示");
}
}
}
}
主界面代码:
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 MySchool
{
public partial class MainForm : Form
{
string TNo = "";//教师编号
public MainForm(string tno)
{
InitializeComponent();
this.TNo = tno;
}
private void 成绩管理ToolStripMenuItem_Click(object sender, EventArgs e)
{
Course course = new Course(TNo);
course.ShowDialog();
}
private void MainForm_FormClosed(object sender, FormClosedEventArgs e)
{
Application.Exit();
}
private void 注销ToolStripMenuItem_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show(string.Format("确定要退出系统吗?"), "确认对话框", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
Application.Exit();
}
}
private void 选课管理ToolStripMenuItem_Click(object sender, EventArgs e)
{
StuCourse course = new StuCourse(TNo); course.ShowDialog();
}
}
}
选课代码:
using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
namespace MySchool
{
public partial class StuCourse : Form
{
SQLHelper SQLHelper;
string Tch_No = "";
public StuCourse(string tch_no)
{
InitializeComponent();
this.Tch_No = tch_no;
SQLHelper = new SQLHelper();
}
private void StuCourse_Load(object sender, EventArgs e)
{
load_course();
load_stu_course();
load_stu_info();
}
/// <summary>
/// 加载当前登录教师对应的课程信息
/// </summary>
private void load_course()
{
string sql = string.Format(@"select C# as Cno,RTRIM(C#)+'|'+Cname as Cname from t_course where T#='{0}'", Tch_No);
DataTable dt = SQLHelper.ExcuteQuery(sql);
cbox_course.DataSource = dt;
cbox_course.ValueMember = "Cno";
cbox_course.DisplayMember = "Cname";
}
/// <summary>
/// 加载已选课学生信息
/// </summary>
private void load_stu_course()
{
string sql = string.Format(@"select tsc.S# as Sno,s.Sname,tsc.C# as Cno ,c.Cname from t_student_course tsc
inner join t_course c on tsc.C#=c.C#
inner join t_student s on s.S#=tsc.S#
where c.T#='{0}' and tsc.C#='{1}'", Tch_No, cbox_course.SelectedValue.ToString());
DataTable dataTable = SQLHelper.ExcuteQuery(sql);
dgv_stu_course.AutoGenerateColumns = false;
dgv_stu_course.DataSource = dataTable;
}
/// <summary>
/// 加载未选当前课程的学生信息
/// </summary>
private void load_stu_info()
{
string sql = string.Format(@"select RTRIM(S#)+'|'+Sname as Sname from t_student where S# not in(select S# from t_student_course where C# ='{0}')", cbox_course.SelectedValue.ToString());
DataTable dataTable = SQLHelper.ExcuteQuery(sql);
List<ListDataItem> list = new List<ListDataItem>();
foreach (DataRow row in dataTable.Rows)
{
ListDataItem item = new ListDataItem();
item.Text = row["Sname"].ToString();
item.Checked = false;
list.Add(item);
}
clisbox_stu.Items.Clear();
foreach (var item in list)
{
clisbox_stu.Items.Add(item.Text, item.Checked);
}
}
private void cbox_course_SelectedIndexChanged(object sender, EventArgs e)
{
if (cbox_course.SelectedIndex != -1)
{
load_stu_course();
load_stu_info();
}
}
private void btn_save_Click(object sender, EventArgs e)
{
List<string> listDataItems = new List<string>();
List<int> inclouds = new List<int>();
for (int i = 0; i < clisbox_stu.Items.Count; i++)
{
if (clisbox_stu.GetItemChecked(i))
{
inclouds.Add(i);
}
}
foreach (int index in inclouds)
{
listDataItems.Add(clisbox_stu.Items[index].ToString().Split('|')[0]);
}
if (listDataItems.Count > 0)
{
if (add_stu_cusore(listDataItems))
{
MessageBox.Show("添加选课成功", "操作提示");
load_stu_course();
load_stu_info();
}
else
{
MessageBox.Show("添加选课失败", "操作提示");
}
}
else
{
MessageBox.Show("请选择学生", "系统提示");
}
}
/// <summary>
/// 添加选课信息
/// </summary>
/// <param name="stuNo"></param>
/// <returns></returns>
private bool add_stu_cusore(List<string> stuNo)
{
bool success=false;
for (int i = 0;i < stuNo.Count; i++)
{
string addSql = string.Format(@"insert into t_student_course(S#,C#)
values('{0}','{1}')", stuNo[i].ToString(), cbox_course.SelectedValue.ToString());
success = SQLHelper.ExcuteNonQuery(addSql);
}
return success;
}
private void dgv_stu_course_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
// 如果点击的是删除按钮
if (dgv_stu_course.Columns[e.ColumnIndex] is DataGridViewButtonColumn && e.RowIndex >= 0)
{
DialogResult result = MessageBox.Show("确定要删除当前行吗?", "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK) {
string stuNo = dgv_stu_course.Rows[e.RowIndex].Cells[0].Value.ToString();
string cNo = dgv_stu_course.Rows[e.RowIndex].Cells[2].Value.ToString();
if (del_stu_course(stuNo, cNo))
{
MessageBox.Show("删除成功!","系统提示");
// 删除行
dgv_stu_course.Rows.RemoveAt(e.RowIndex);
load_stu_course();
load_stu_info();
}
else
{
MessageBox.Show("删除失败!","系统提示");
}
}
}
}
/// <summary>
/// 删除选课信息
/// </summary>
/// <param name="stuNo"></param>
/// <param name="cNo"></param>
/// <returns></returns>
private bool del_stu_course(string stuNo,string cNo)
{
string delSql = string.Format("delete from dbo.t_student_course where S#='{0}' and C#='{1}'",stuNo,cNo);
return SQLHelper.ExcuteNonQuery(delSql);
}
}
}
成绩代码:
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 MySchool
{
public partial class Course : Form
{
SQLHelper help;
string Tch_No = "";
public Course(string tch_No)
{
InitializeComponent();
this.Tch_No = tch_No;
help = new SQLHelper();
}
/// <summary>
/// 学生成绩窗体加载方法
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Course_Load(object sender, EventArgs e)
{
load_course();
load_stu_course_score();
}
/// <summary>
/// 加载课程列表
/// </summary>
private void load_course()
{
string sql = string.Format("select C#,RTRIM(C#)+'|'+Cname as Cname from t_course where T#='{0}'",Tch_No);
DataTable dt = help.ExcuteQuery(sql);
cbx_curse.DataSource=dt;
cbx_curse.DisplayMember="Cname";
cbx_curse.ValueMember="C#";
}
private void load_stu_course_score()
{
string sql = string.Format(@"select T2.S# SNo,T2.Sname,T3.C# CNo,T3.Cname,T1.Score from t_student_course T1
inner join t_student T2 on T1.S#=T2.S#
inner join t_course T3 on T1.C#=T3.C#
where T3.T#='{0}' and t3.C#='{1}'",Tch_No,cbx_curse.SelectedValue.ToString());
DataTable dt = help.ExcuteQuery(sql);
dgv_stu_course_score.AutoGenerateColumns = false;
dgv_stu_course_score.DataSource = dt;
}
private void cbx_curse_SelectedIndexChanged(object sender, EventArgs e)
{
if (cbx_curse.SelectedIndex != -1)
{
load_stu_course_score();
}
}
private void dgv_stu_course_score_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
// 如果点击的是删除按钮
if (dgv_stu_course_score.Columns[e.ColumnIndex] is DataGridViewButtonColumn && e.RowIndex >= 0)
{
string stuNo = dgv_stu_course_score.Rows[e.RowIndex].Cells["SNo"].Value.ToString();
string cNo = dgv_stu_course_score.Rows[e.RowIndex].Cells["CNo"].Value.ToString();
try
{
int score = int.Parse(dgv_stu_course_score.Rows[e.RowIndex].Cells["Score"].Value.ToString());
if (update_Score(stuNo,cNo,score))
{
MessageBox.Show("成绩保存成功!", "系统提示");
dgv_stu_course_score.CellClick += Dgv_stu_course_score_CellClick;
dgv_stu_course_score.Rows[e.RowIndex].Cells["Score"].Value=score.ToString();
}
}
catch (Exception ex)
{
MessageBox.Show("成绩必须是整数", "系统提示");
}
}
}
/// <summary>
/// 保存成功后更新行的颜色,表示已进行过成绩设定
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Dgv_stu_course_score_CellClick(object sender, DataGridViewCellEventArgs e)
{
// 确保点击的不是表头
if (e.RowIndex >= 0)
{
// 设置当前行的颜色
dgv_stu_course_score.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.LightBlue;
}
}
/// <summary>
/// 更新成绩数据
/// </summary>
/// <param name="stuNo">学号</param>
/// <param name="Cno">课程编号</param>
/// <param name="score">成绩</param>
/// <returns></returns>
private bool update_Score(string stuNo,string Cno,int score)
{
string updateSql = string.Format("update t_student_course set Score={2} where S#='{0}' and C#='{1}'",stuNo,Cno,score);
return help.ExcuteNonQuery(updateSql);
}
}
}
数据库表创建语句:
T_student
CREATE TABLE [dbo].[t_student](
[S#] [char](10) NOT NULL,
[Sname] [nvarchar](10) NOT NULL,
[Sex] [nchar](1) NULL,
[Age] [int] NULL,
[Major] [nvarchar](20) NULL,
CONSTRAINT [PK_t_student] PRIMARY KEY CLUSTERED
(
[S#] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_student', @level2type=N'COLUMN',@level2name=N'S#'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_student', @level2type=N'COLUMN',@level2name=N'Sname'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_student', @level2type=N'COLUMN',@level2name=N'Sex'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年龄' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_student', @level2type=N'COLUMN',@level2name=N'Age'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'专业' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_student', @level2type=N'COLUMN',@level2name=N'Major'
GO
T_teacher
CREATE TABLE [dbo].[t_teacher](
[T#] [char](10) NOT NULL,
[Tname] [nvarchar](10) NOT NULL,
[Age] [int] NULL,
[Title] [nvarchar](20) NULL,
CONSTRAINT [PK_t_teacher] PRIMARY KEY CLUSTERED
(
[T#] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_teacher', @level2type=N'COLUMN',@level2name=N'T#'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_teacher', @level2type=N'COLUMN',@level2name=N'Tname'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年龄' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_teacher', @level2type=N'COLUMN',@level2name=N'Age'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'职称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_teacher', @level2type=N'COLUMN',@level2name=N'Title'
GO
T_course
CREATE TABLE [dbo].[t_course](
[C#] [char](10) NOT NULL,
[Cname] [nvarchar](20) NOT NULL,
[T#] [char](10) NULL,
CONSTRAINT [PK_t_course] PRIMARY KEY CLUSTERED
(
[C#] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'课程编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_course', @level2type=N'COLUMN',@level2name=N'C#'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'课程名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_course', @level2type=N'COLUMN',@level2name=N'Cname'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_course', @level2type=N'COLUMN',@level2name=N'T#'
GO
ALTER TABLE [dbo].[t_course] WITH CHECK ADD CONSTRAINT [FK_t_course_t_teacher] FOREIGN KEY([T#])
REFERENCES [dbo].[t_teacher] ([T#])
GO
ALTER TABLE [dbo].[t_course] CHECK CONSTRAINT [FK_t_course_t_teacher]
GO
T_student_course
CREATE TABLE [dbo].[t_student_course](
[S#] [char](10) NOT NULL,
[C#] [char](10) NOT NULL,
[Score] [int] NULL,
CONSTRAINT [PK_t_student_course] PRIMARY KEY CLUSTERED
(
[S#] ASC,
[C#] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_student_course', @level2type=N'COLUMN',@level2name=N'S#'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'课程号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_student_course', @level2type=N'COLUMN',@level2name=N'C#'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'成绩' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_student_course', @level2type=N'COLUMN',@level2name=N'Score'
GO
ALTER TABLE [dbo].[t_student_course] WITH CHECK ADD CONSTRAINT [FK_t_student_course_t_course] FOREIGN KEY([C#])
REFERENCES [dbo].[t_course] ([C#])
GO
ALTER TABLE [dbo].[t_student_course] CHECK CONSTRAINT [FK_t_student_course_t_course]
GO
ALTER TABLE [dbo].[t_student_course] WITH CHECK ADD CONSTRAINT [FK_t_student_course_t_student] FOREIGN KEY([S#])
REFERENCES [dbo].[t_student] ([S#])
GO
ALTER TABLE [dbo].[t_student_course] CHECK CONSTRAINT [FK_t_student_course_t_student]
GO