目录
功能要求:
用户选择专业,系统列出专业所有学生的学号;选择课程名,在表格中显示此课程的成绩、学分。通过单击表格中的某一行,把某学生该门课的成绩反映到表单控件中,并可以修改表单中的信息。用户还可以添加新课程,录入新课程的成绩,并把当前录入的成绩添加到数据库中。选中某条记录的学号和课程名,单击“删除”按钮,可以删除该学生原课程的成绩。
界面设计:
学生成绩录入界面所需控件及属性所用和学生成绩修改类似。
其中三个组合框控件的DropDownStyle属性均设置为DropDownList。
学生成绩录入界面如下图:
功能实现:
1.专业、课程名列表选项加载
为方便用户使用,程序启动时需要加载专业和课程名的列表。在窗体的Load事件中编写代码,代码如下:
public partial class ScoreForm : Form
{
private string connStr = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=XSCJDB;Integrated Security=True";
public ScoreForm()
{
InitializeComponent();
}
private void ScoreForm_Load(object sender, EventArgs e)
{
string _sql = "SELECT distinct ZY FROM XSB";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(_sql, conn);
try
{
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
//读取专业名
while (dr.Read())
{
stuZY.Items.Add(dr[0]);
}
stuZY.SelectedIndex = 0;
dr.Close();
_sql = "SELECT KCM FROM KCB";
cmd = new SqlCommand(_sql, conn);
dr = cmd.ExecuteReader();
//读取课程名
while (dr.Read())
{
stuKCM.Items.Add(dr[0]);
}
dr.Close();
}
finally
{
conn.Close();
}
}
}
运行截图:
这样在程序初始运行时就已经加载了现有的专业和课程的列表供用户选择。如下图。
2.专业和学号列表联动
选择的专业改变时,实现学号列表的联动,并显示此专业所有学生的成绩,代码如下:
private void stuZY_SelectedIndexChanged(object sender, EventArgs e)
{
string _sql = "SELECT XH FROM XSB WHERE ZY='" + stuZY.Text + "'";
//清空现有的学号
stuXH.Items.Clear();
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(_sql, conn);
try
{
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
//读取相应的学号
while (dr.Read())
{
stuXH.Items.Add(dr[0]);
}
dr.Close();
_sql = "SELECT XSB.XH AS '学号',XSB.XM AS '姓名',KCB.KCM AS '课程名',CJB.CJ AS '成绩'," +
"KCB.XF AS '学分',KCB.XS AS '学时',KCB.XQ AS'开课学期' FROM XSB,KCB,CJB " +
"WHERE XSB.XH=CJB.XH AND KCB.KCH=CJB.KCH AND XSB.ZY='" + stuZY.Text + "'";
SqlDataAdapter sda = new SqlDataAdapter(_sql, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
scoreDGV.DataSource = ds.Tables[0].DefaultView;
}
finally
{
conn.Close();
}
}
如选择“计算机”专业,“学号”下拉列表中自动载入计算机专业所有学生的学号,网格中同步列出这些同学的成绩。
3.显示某学生某一门课的成绩
当所选学号改变时,显示此学生当前课程的成绩信息,代码如下:
private void stuXH_SelectedIndexChanged(object sender, EventArgs e)
{
string _sql = "SELECT XSB.XH AS'学号',XSB.XM as '姓名',KCB.KCM AS '课程名'," +
"CJB.CJ AS '成绩',KCB.XF AS '学分',KCB.XS AS '学时',KCB.XQ AS '开课学期'" +
"FROM XSB,KCB,CJB WHERE CJB.XH='" + stuXH.Text + "'AND CJB.XH=XSB.XH AND CJB.KCH=KCB.KCH";
if (stuKCM.Text.Trim() != string.Empty)
{
_sql += "AND KCB.KCM='" + stuKCM.Text + "'";
}
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter sda = new SqlDataAdapter(_sql, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
scoreDGV.DataSource = ds.Tables[0].DefaultView;
}
同理,在学号选择不变的情况下,改变所选课程名,对应显示此学生这门课的成绩信息,代码如下:
private void stuKCM_SelectedIndexChanged(object sender, EventArgs e)
{
string _sql = "SELECT XSB.XH AS '学号',XSB.XM AS '姓名',KCB.KCM AS '课程名',CJB.CJ AS '成绩'," +
"KCB.XF AS '学分',KCB.XS AS '学时',KCB.XQ AS '开课学期' FROM XSB,KCB,CJB WHERE KCB.KCM='" +
stuKCM.Text + "'AND CJB.XH=XSB.XH AND CJB.KCH=KCB.KCH";
if (stuXH.Text.Trim() != string.Empty)
{
_sql += " AND XSB.XH='" + stuXH.Text + "'";
}
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter sda = new SqlDataAdapter(_sql, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
scoreDGV.DataSource = ds.Tables[0].DefaultView;
}
4.学生成绩显示到表单
当单击DataGridView控件中的某条记录时,将数据读出,代码如下:
private void scoreDGV_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
//获得选中的记录行
DataGridViewRow dgvRow = scoreDGV.Rows[e.RowIndex];
//获得行单元格集合
DataGridViewCellCollection dgvCC = dgvRow.Cells;
//获得单元格数据
stuXM.Text = dgvCC[1].Value.ToString();
stuCJ.Text = dgvCC[3].Value.ToString();
stuXF.Text = dgvCC[4].Value.ToString();
stuKCM.SelectedItem = dgvCC[2].Value;
}
读出的数据显示到表单,以便修改。
5.录入学生成绩
“更新”按钮用以完成录入(包括修改)操作,代码如下:
private void update_btn_Click(object sender, EventArgs e)
{
string _sql = "SELECT COUNT(*) FROM CJB WHERE CJB.XH='" + stuXH.Text +
"'AND CJB.KCH=(SELECT KCH FROM KCB WHERE KCM ='" + stuKCM.Text + "')";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(_sql, conn);
//检查是否有此学生记录,有就修改,无则添加
try
{
conn.Open();
int cnt = (int)cmd.ExecuteScalar();
//修改记录
if (cnt == 1)
{
_sql = "UPDATE CJB SET CJB.CJ='" + stuCJ.Text + "'WHERE CJB.XH='" + stuXH.Text +
"'AND CJB.KCH=(SELECT KCH FROM KCB WHERE KCM='" + stuKCM.Text + "')";
}
//添加新纪录
else
{
string _sql2 = "SELECT KCH FROM KCB WHERE KCM='" + stuKCM.Text + "'";
SqlCommand cmd2 = new SqlCommand(_sql2, conn);
_sql = "INSERT INTO CJB VALUES('" + stuXH.Text.Trim() + "','" + cmd2.ExecuteScalar() +
"," + int.Parse(stuCJ.Text.Trim()) + ")";
}
cmd = new SqlCommand(_sql, conn);
cmd.ExecuteNonQuery();
stuXH_SelectedIndexChanged(null, null);
MessageBox.Show("更新成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
conn.Close();
}
}
也可以单击“删除”按钮删除原先录入的成绩,代码如下:
private void delete_btn_Click(object sender, EventArgs e)
{
DialogResult ret = MessageBox.Show("确定要删除记录吗?", "删除", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (ret == DialogResult.Cancel)
{
return;
}
string _sql = "DELETE FROM CJB WHERE XH='" + stuXH.Text + "'AND KCH=(SELECT KCH FROM KCB WHERE KCM='" + stuKCM.Text + "')";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(_sql, conn);
try
{
conn.Open();
int rows = cmd.ExecuteNonQuery();
stuXH_SelectedIndexChanged(null, null);
if (rows == 1)
{
MessageBox.Show("删除成功!", "提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
}
finally
{
conn.Close();
}
}
运行截图:
点击确定后:
最后完成“取消”按钮的事件过程,代码如下:
private void cancel_btn_Click(object sender, EventArgs e)
{
this.Close();
}
点击取消后,学生信息录入窗体关闭。
OVER!🎉🎶👌✨😃😍🤞💕💋🌹😊😁😉💖😘👏😜😆🎁