数据库系统原理实验报告
实验名称 学生成绩管理系统
院 系 计算机与信息工程学院
班 级 2013级计算机科学与技术二班
姓 名 郅伟远
学 号 1308114088
一、实验目的：
1、熟悉并掌握数据库系统原理这门课所应用的软件SQL Server2008，以及使用该软件的相关要求与具体用法。
2、在掌握SQL Server2008应用的基础上，建立相关学生成绩管理信息的数据库，并在此基础上与Visual Studio.NET 2008建立连接，进行相关的操作。
3、在Visual Studio.NET 2008上进行学生成绩管理信息系统的查询、更改以及成绩录入的相关界面的设计，使流程能够顺利进行。
二、总体设计（设计原理、设计方案及流程等）
设计原理：
1、以SQL Server2008软件的功能与作用为基础。
2、以Visual Studio.NET 2008软件的功能为平台。
设计方案及流程：
以Visual Studio.NET 2008为前台开发工具进行操作，并以SQL Server2008软件为后台，使创建成功的一个学生成绩管理系统具有如下基本功能：
1、学生信息查询，可根据学号，姓名及专业分别进行学生信息查询。
2、学生信息修改，可在数据库中插入，删除，修改学生记录
3、学生成绩录入，可录入每个学生某门课程成绩。
在此基础的操作中上能够添加个人的信息，如添加个人的学号，姓名，专业和成绩等，并能够执行查询个人相关信息的操作。
三、实验步骤（包括主要步骤、代码分析等）
主要步骤及相应的代码：
1、在SQL Server 2008条件下以ID=sa,password=123456的身份进入数据库引擎，并将PXSCJ附加到数据库。打开Visual Studio.NET 2008，将已有的数据库PXSCJ与VS2008建立连接。
2、在Visual Studio.NET 2008环境下，建立Windows窗体并命名为Form1,并添加相应的程序代码，同时需要添加相关联的子窗体分别双击窗体，双击3个botton按钮，添加相应的程序代码：
private void button1_Click(object sender, EventArgs e)
{
//建立学生信息查询窗体
SearchForm searchfrm=new SearchForm();
searchfrm.ShowDialog();
}
private void button2_Click(object sender, EventArgs e)
{
//建立学生信息修改窗体
ModifyForm modifyfrm= new ModifyForm();
modifyfrm.ShowDialog();
}
private void button3_Click(object sender, EventArgs e)
{
//建立学生信息录入窗体
ScoreForm scorefrm = new ScoreForm();
scorefrm.ShowDialog();
}
3、在窗体SearchForm上添加相应的控件，修改相应控件的添加控件,合理设计此界面控件的基础上，双击各个相应的控件，添加相应的程序代码：
private string sql = "";
private void SearchForm_Load(object sender, EventArgs e)
{
string connStr = @"Data Source=C56;Initial Catalog=G:\数据库系统\PXSCJ\PXSCJ.MDF;User ID=sa; Password=123456";
string _sql = "select XH as'学号',XM as'姓名',ZY as'专业',XB as'性别'," + "CSSJ as'出生日期',ZXF as'总学分',BZ as'备注'from XSB";
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter sda = new SqlDataAdapter(_sql, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
StuDGV.DataSource = ds.Tables[0].DefaultView;
}
private void MakeSqlStr()
{
sql="";
if(stuXH.Text.Trim()!=string.Empty)
{
sql="and XH like'%"+stuXH.Text.Trim()+"%'";
}
if(stuXM.Text.Trim()!=string.Empty)
{
sql+="and XM like'%"+stuXM.Text.Trim()+"%'";
}
if(stuZY.Text!="所有专业")
{
sql += "and ZY='" + stuZY.Text + "'";
}
}
private void button1_Click(object sender, EventArgs e)
{
MakeSqlStr();
string connStr = @"Data Source=C56;Initial Catalog=G:\数据库系统\PXSCJ\PXSCJ.MDF;User ID=sa; Password=123456";
string _sql="select XH as'学号',XM as'姓名',ZY as'专业',XB as'性别',"+"CSSJ as'出生日期',ZXF as'总学分',BZ as'备注'from XSB where 1=1"+sql;
SqlConnection conn=new SqlConnection(connStr);
SqlDataAdapter sda=new SqlDataAdapter(_sql,conn);
DataSet ds=new DataSet();
sda.Fill(ds);
StuDGV.DataSource=ds.Tables[0].DefaultView;
}
4、在窗体ModifyForm上添加相应的控件：在工具箱中合理的将控件摆放在窗体上，双击相应的控件，并添加相应的代码：
private void ModifyForm_Load(object sender, EventArgs e)
{
string connStr = @"Data Source=c56; Initial Catalog=G:\数据库系统\PXSCJ\PXSCJ.MDF; Persist Security Info=True; User ID=sa; Password=123456";
string _sql="select XH as '学号',XM as '姓名',ZY as '专业',XB as '性别',"+"CSSJ as '出生年月',ZXF as '总学分',BZ as '备注' from XSB";
SqlConnection conn=new SqlConnection(connStr);
SqlDataAdapter sda=new SqlDataAdapter(_sql,conn);
DataSet ds=new DataSet();
sda.Fill(ds);
StuDGV.DataSource=ds.Tables[0].DefaultView;
}
private void stuDelete_Click(object sender, EventArgs e)
{
string connStr = @"Data Source=c56; Initial Catalog=G:\数据库系统\PXSCJ\PXSCJ.MDF; Persist Security Info=True; User ID=sa; Password=123456";
DialogResult ret=MessageBox.Show("确定要删除记录吗？","删除",MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (ret==DialogResult.Cancel) return;
string _sql="delete from XSB where XH='"+stuXH2.Text+"'";
SqlConnection conn=new SqlConnection(connStr);
SqlCommand cmd=new SqlCommand(_sql,conn);
try
{
conn.Open();
int rows = cmd.ExecuteNonQuery();
ModifyForm_Load(null, null);
if (rows == 1)
{
MessageBox.Show("删除成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
finally
{
conn.Close();
}
}
private void stuUpdate_Click(object sender, EventArgs e)
{
string connStr = @"Data Source=c56; Initial Catalog=G:\数据库系统\PXSCJ\PXSCJ.MDF; Persist Security Info=True; User ID=sa; Password=123456";
string _sql="select count(*) from XSB where XH='"+stuXH2.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 XSB set XM='"+stuXM2.Text+" ',ZY='"+stuZY2.Text+" ',ZXF="+int.Parse(stuZXF2.Text)+",BZ='"+stuBZ.Text+"'where XH='"+stuXH2.Text+"'";
}
else
{
string sex=male.Checked ?"男":"女";
_sql="insert into XSB (XH,XM,ZY,ZXF) values('"+stuXH2.Text+" ','"+stuXM2.Text+" ','"+stuZY2.Text+" ',"+int.Parse(stuZXF2.Text)+")";
}
cmd=new SqlCommand(_sql,conn);
cmd.ExecuteNonQuery();
ModifyForm_Load(null,null);
}
finally
{
conn.Close();
}
}
private void stuCancel_Click(object sender, EventArgs e)
{
this .Close ();
}
private void StuDGV_RowHeaderMouseClick_1(object sender, DataGridViewCellMouseEventArgs e)
{
DataGridViewRow dgvRow = StuDGV.Rows[e.RowIndex];
DataGridViewCellCollection dgvCC = dgvRow.Cells;
stuXH2.Text = dgvCC[0].Value.ToString();
stuXM2.Text = dgvCC[1].Value.ToString();
stuZY2.Text = dgvCC[2].Value.ToString();
if (dgvCC[3].Value.ToString() == "男")
male.Checked = true;
else
female.Checked = true;
stuCSSJ2.Text = Convert.ToDateTime(dgvCC[4].Value).ToShortTimeString();
stuZXF2.Text = dgvCC[5].Value.ToString();
stuBZ.Text = dgvCC[6].Value.ToString();
}
5、在窗体ScoreForm上添加相应的控件：添加1个Lable控件，并将Text改为“学生成绩录入”； 拖拽6个Lable控件，将其属性Text分别设置为“姓名，成绩，课程名”;合理的将控件摆放在窗体上，双击相应的控件，并添加相应的代码：
private void ScoreForm_Load(object sender, EventArgs e)
{
string connStr = @"Data Source=C56;Initial Catalog=G:\数据库系统\PXSCJ\PXSCJ.MDF;User ID=sa; Password=123456";
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())
{
stuZY3.Items.Add(dr[0]);
}
dr.Close();
_sql = "select KCM from KCB";
cmd = new SqlCommand(_sql, conn);
dr = cmd.ExecuteReader();//读取课程名
while (dr.Read())
{
stuKCM3.Items.Add(dr[0]);
}
dr.Close();
}
finally
{
conn.Close();
}
}
private void update_btn_Click(object sender, EventArgs e)
{
string connStr = @"Data Source=C56;Initial Catalog=G:\数据库系统\PXSCJ\PXSCJ.MDF;User ID=sa; Password=123456";
string _sql = "select count(*)from CJB where CJB.XH='" + stuXH3.Text + "'and CJB.KCH=(select KCH from KCB where KCM='" + stuKCM3.Text.Trim () +"')";
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='" + stuCJ3.Text + "' where CJB.XH='" + stuXH3.Text + "' and CJB.KCH=(select KCH from KCB where KCM='" + stuKCM3.Text + "')";
}
else
{
string _sql2 = "select KCH from KCB where KCM='" + stuKCM3.Text + "'";
SqlCommand cmd2 = new SqlCommand(_sql2, conn);
_sql = "insert into CJB values('" + stuXH3.Text.Trim() + "'," + cmd2.ExecuteScalar() + "," + int.Parse(stuCJ3.Text.Trim()) + ")";
}
cmd = new SqlCommand(_sql, conn);
cmd.ExecuteNonQuery();
_sql = "select XSB.XH as '学号',XSB.XM as '姓名',KCB.KCM as '课程名',CJB.CJ as '成绩',KCB.XF as '学分',KCB.XS as '学时',KCB.KKXQ as '开课学期' from XSB,KCB,CJB where XSB.XH=CJB.XH and KCB.KCH=CJB.KCH and KCB.KCM='" + stuKCM3.Text + "'" + "and XSB.XH='" + stuXH3.Text + "'";
SqlDataAdapter sda = new SqlDataAdapter(_sql, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
scoreDGV.DataSource = ds.Tables[0].DefaultView;
}
finally
{
conn.Close();
}
}
private void delete_btn_Click(object sender, EventArgs e)
{
string connStr = @"Data Source=C56;Initial Catalog=G:\数据库系统\PXSCJ\PXSCJ.MDF;User ID=sa; Password=123456";
DialogResult ret = MessageBox.Show("确定要删除记录吗?", "删除", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (ret == DialogResult.Cancel)
{
return;
}
string _sql = "delete from CJB where XH=' " + stuXH3.Text.Trim() + "'and KCH=(select KCH from KCB where KCM='" + stuKCM3.Text.Trim()+ "')";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(_sql, conn);
try
{
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows == 1)
{
MessageBox.Show("删除成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
finally
{
conn.Close();
}
}
private void cancel_btn_Click(object sender, EventArgs e)
{
this.Close();
}
private void stuZY3_SelectedIndexChanged(object sender, EventArgs e)
{
string connStr = @"Data Source=C56;Initial Catalog=G:\数据库系统\PXSCJ\PXSCJ.MDF;User ID=sa;Password=123456";
string _sql = "select XH from XSB where ZY='" + stuZY3.Text + "'";
//清空现有的学号
stuXH3.Items.Clear();
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(_sql, conn);
try
{
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
//读取相应的学号
while (dr.Read())
{
stuXH3.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.KKXQ as'开课学期'from XSB,KCB,CJB where XSB.XH=CJB.XH and KCB.KCH =CJB.KCH and XSB.ZY='" + stuZY3.Text + "'";
SqlDataAdapter sda = new SqlDataAdapter(_sql, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
scoreDGV.DataSource = ds.Tables[0].DefaultView;
}
finally
{
conn.Close();
}
}
private void stuXH3_SelectedIndexChanged(object sender, EventArgs e)
{
string connStr = @"Data Source=C56;Initial Catalog=G:\数据库系统\PXSCJ\PXSCJ.MDF;User ID=sa;Password=123456";
string _sql = "select XSB.XH as'学号',XSB.XM as'姓名',KCB.KCM as'课程名',CJB.CJ as'成绩',KCB.XF as'学分',KCB.XS as'学时',KCB.KKXQ as'开课学期'from XSB,KCB,CJB where XSB.XH=CJB.XH and KCB.KCH=CJB.KCH and XSB.XH='" +stuXH3.Text + "'";
if (stuKCM3.Text.Trim() != string.Empty)
{
_sql += "and KCB.KCM='" + stuKCM3.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 stuKCM3_SelectedIndexChanged(object sender, EventArgs e)
{
string connStr = @"Data Source=C56;Initial Catalog=G:\数据库系统\PXSCJ\PXSCJ.MDF;User ID=sa;Password=123456";
string _sql = "select XSB.XH as'学号',XSB.XM as'姓名',KCB.KCM as'课程名',CJB.CJ as'成绩'," + "KCB.XF as'学分',KCB.XS as'学时',KCB.KKXQ as'开课学期'from XSB,KCB,CJB where XSB.XH=CJB.XH and KCB.KCH=CJB.KCH and KCB.KCM='" + stuKCM3.Text + "'";
if (stuXH3.Text.Trim() != string.Empty)
{
_sql += "and XSB.XH='" + stuXH3.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 scoreDGV_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
//获得选中的记录行
DataGridViewRow dgvRow = scoreDGV.Rows[e.RowIndex];
//获得航单元格集合
DataGridViewCellCollection dgvCC = dgvRow.Cells;
stuXM3.Text = dgvCC[1].Value.ToString();
stuKCM3.SelectedItem = dgvCC[2].Value;
stuCJ3.Text = dgvCC[3].Value.ToString();
stuZXF3.Text = dgvCC[4].Value.ToString();
}
四、结果分析与总结
实验结果总界面：
一、单击“学生信息查询”的学生信息查询结果如下：
二、单击“学生信息修改”的学生信息修改结果如下：
1、执行学生信息更新（对已有的学生信息进行修改）的结果：
三、单击“学生成绩录入”学生成绩录入结果如下：
