学生管理系统(共24页)

1、河南师范大学计算机与信息工程学院数据库系统原理(yunl)实验报告实验(shyn)名称 学生(xu sheng)成绩管理系统 院 系 计算机与信息工程学院 班 级 13级计科2班 姓 名 王亚龙 学 号 1308114090 计算机与信息工程学院综合性、设计性实验报告专业(zhuny)：计算机科学与技术(jsh) 年级(ninj)/班级:13级计科2班 20142015学年第二学期课程名称数据库系统原理指导教师孟慧丽学号姓名1308114090 王亚龙实验地点过街楼实验时间项目名称学生成绩管理系统实验类型综合性一、实验目的：自选程序设计语言为前台开发工具，SQL Server为后台数据库开发一

2、个学生成绩管理系统，后台连接SQL server 数据库PXSCJ，具有如下基本功能：学生信息查询，可根据学号，姓名及专业分别进行学生信息查询。学生信息修改，可在数据库中插入，删除，修改学生记录学生成绩录入，可录入每个学生某门课程成绩。（4）将自己的个人信息，作为一条学生记录插入学生表中，写实验报告时，截图时显示的数据中要包含自己的个人信息。二、实验仪器或设备：带有SQL server2008和Visual Studio 2008的计算机一台三、总体设计（设计原理、设计方案及流程等）（1）设计原理：运用C#语言设计数据库管理系统的前台界面，运用SQL Server 语言做出学生数据库。将C#语

3、言和SQL Server结合，做出能在界面上实现实验目的的数据库管理系统。（2）设计方案：1)ER图设计2）学生(xu sheng)表结构设计 = 1 * GB3 学生(xu sheng)信息表（包括学号、姓名、出生日期、专业(zhuny)、总学分、备注） = 2 * GB3 学生成绩表（包括学号、课程号、成绩） = 3 * GB3 学生(xu sheng)课程表（包括(boku)课程号、学分、开课学期(xuq)、学时等）3）模块设计 四、实验(shyn)步骤（包括主要步骤、代码分析等）1、可视化操作(cozu)创建与SQL Server 2008的连接(linji) 在Visual Stud

4、io 2008中，打开服务器资源管理器面板，在“数据连接”选项上单击鼠标右键，选择“添加连接”选项，弹出“添加连接”对话框,如下图所示做链接：2、导航界面的代码如下所示：private void button1_Click(object sender, EventArgs e) SearchForm searchfm = new SearchForm(); searchfm.ShowDialog(); private void button2_Click(object sender, EventArgs e) ModifyForm modifyfrm = new ModifyForm(); m

5、odifyfrm.ShowDialog(); private void button3_Click(object sender, EventArgs e) ScoreForm scorefrm = new ScoreForm(); scorefrm.ShowDialog();3、创建信息(xnx)查询窗体的过程(guchng)：窗体上放入DataGridView，命名为StuDGV。再放入两个(lin )文本框，一个下拉列表框和一个“查询”命令按钮。窗体代码如下： private string sql=; public SearchForm() InitializeComponent(); p

6、rivate void SearchForm_Load(object sender, EventArgs e) string connStr = Data Source=PC201506041939;Initial Catalog=PXSCJ;Integrated Security=True; string _sql=select XH as学号,XM as姓名,ZY as专业,XB as性别,+CSSJ as出生日期,ZXF as 总学分,BZ as 备注from XSB; SqlConnection conn =new SqlConnection(connStr); SqlDataAdap

7、ter sda= new SqlDataAdapter(_sql,conn); DataSet ds=new DataSet(); sda.Fill(ds); StuDGV.DataSource=ds.Tables0.DefaultView; stuZY.SelectedIndex=0; private void MakeSqlStr() sql= ; if (stuXH.Text.Trim()!=string.Empty) sql = and XH like %+stuXH.Text.Trim()+%; if (stuXM.Text.Trim()!=string.Empty) sql +=

8、and XM like %+stuXM.Text.Trim()+%; if (stuZY.Text!=所有(suyu)专业) sql += and ZY=+stuZY.Text +; private void button1_Click(object sender, EventArgs e) MakeSqlStr();string connStr=DataSource=PC201506041939;InitialCatalog=PXSCJ;Integrated Security=True;string _sql = select XH as学号,XM as姓名,ZY as专业,XB as性别(

9、xngbi),+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.Tables0.DefaultView;4、学生(xu sheng)信息修改界面(jimin)创建过程：在窗体ModifyForm上放入DataGri

10、dView并命名(mng mng)为StuDGV，然后放入需要的其他控件，创建结果如下图：代码如下： private void StuDGV_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e) DataGridViewRow dgvRow = StuDGV.Rowse.RowIndex; DataGridViewCellCollection dgvCC = dgvRow.Cells; stuXH.Text = dgvCC0.Value.ToString(); stuXM.Text = dgvCC1.Value

11、.ToString(); stuZY.Text = dgvCC2.Value.ToString(); if (dgvCC3.Value.ToString() = 男) male.Checked = true; else female.Checked = true; stuCS.Text = Convert.ToDateTime(dgvCC4.Value).ToShortDateString(); stuZXF.Text = dgvCC5.Value.ToString(); stuBZ.Text = dgvCC6.Value.ToString(); private void ModifyForm

12、_Load(object sender, EventArgs e) string connStr = Data Source=PC201506041939;Initial Catalog=PXSCJ;Integrated Security=True; string _sql = select XH as学号,XM as姓名,ZY as专业,XB as性别(xngbi),CSSJ as出生日期,ZXF as 总学分,BZ as备注 from XSB; SqlConnection conn = new SqlConnection(connStr); SqlDataAdapter sda = new

13、 SqlDataAdapter(_sql, conn); DataSet ds = new DataSet(); sda.Fill(ds); StuDGV.DataSource = ds.Tables0.DefaultView; private void button2_Click(object sender, EventArgs e) string connStr = Data Source=PC201506041939;Initial Catalog=PXSCJ;Integrated Security=True; DialogResult ret=MessageBox.Show(确定要删除

14、(shnch)记录吗？,删除,MessageBoxButtons.OKCancel,MessageBoxIcon.Question); if (ret = DialogResult.Cancel) return; string _sql=Delete from XSB where XH=+stuXH.Text+;SqlConnection conn =new SqlConnection(connStr);SqlCommand cmd= new SqlCommand(_sql,conn); try conn .Open (); int rows=cmd.ExecuteNonQuery (); M

15、odifyForm_Load(null,null ); if (rows =1) MessageBox .Show (删除(shnch)成功!,提示,MessageBoxButtons .OK ,MessageBoxIcon .Information ); finally conn .Close (); private void button3_Click(object sender, EventArgs e) this.Close(); private void button1_Click(object sender, EventArgs e) string connStr = Data S

16、ource=PC201506041939;Initial Catalog=PXSCJ;Integrated Security=True; string _sql = select count(*)from XSB where XH= + stuXH.Text + ; SqlConnection conn = new SqlConnection(connStr); SqlCommand cmd = new SqlCommand(_sql, conn); try conn.Open(); int cnt = (int)cmd.ExecuteScalar(); if (cnt = 1) _sql =

17、 update XSB set XM = + stuXM.Text + ,ZY= + stuZY.Text + ,ZXF= + int.Parse(stuZXF.Text) + where XH= + stuXH.Text + ; else string sex = male.Checked ? 男 : 女; _sql = insert into XSB (xh,xm,zy,zxf) values ( + stuXH.Text + , + stuXM.Text + , + stuZY.Text + , + int.Parse(stuZXF.Text) + ); cmd = new SqlCom

18、mand(_sql, conn); cmd.ExecuteNonQuery(); ModifyForm_Load(null, null); finally conn.Close(); 5、成绩(chngj)录入窗体创建(chungjin)过程(guchng)参见信息修改窗体创建过程，创建结果如下图（1）在窗体的Load事件中编写专业、课程名列下的添加代码，如下：private void ScoreForm_Load(object sender, EventArgs e) string connStr = Data Source=PC201506041939;Initial Catalog=PX

19、SCJ;Integrated Security=True; 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(dr0); dr.Close(); _sql = select KCM from KCB;

20、cmd = new SqlCommand(_sql, conn); dr = cmd.ExecuteReader(); while (dr.Read () stuKCM.Items.Add(dr0); dr.Close(); finally conn.Close(); （2）选择的专业改变时，实现学号列表(li bio)的联动，并显示此专业所有学生的成绩，代码如下：private void stuZY_SelectedIndexChanged(object sender, EventArgs e) string connStr = Data Source=PC201506041939;Init

21、ial Catalog=PXSCJ;Integrated Security=True; 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(

22、dr0); stuXH.SelectedIndex=0; dr.Close (); string _sql = select XSB.XH as学号,XSB.XM as姓名(xngmng),KCB.KCM as课程名,CJB.CJ as成绩, + KCB.XF as学分,KCB.XS as学时,KCB.KKXQ as开课学期 + from XSB,KCB,CJB + where CJB.XH= + stuXH.Text + and CJB.XH=XSB.XH and CJB.KCH=KCB.KCH;SqlDataAdapter sda=new SqlDataAdapter (_sql ,con

23、n); DataSet ds=new DataSet (); sda .Fill (ds ); scoreDGV .DataSource =ds .Tables 0.DefaultView ; finally conn .Close (); （3）当所选学(xun xu)号改变(gibin)时,显示该学生的成绩信息,代码如下: private void stuXH_SelectedIndexChanged(object sender, EventArgs e) string connStr = Data Source=PC201506041939;Initial Catalog=PXSCJ;I

24、ntegrated Security=True; string _sql = select XSB.XH as学号,XSB.XM as姓名,KCB.KCM as课程(kchng)名,CJB.CJ as成绩, + KCB.XF as学分,KCB.XS as学时,KCB.KKXQ 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= + s

25、tuKCM.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 connstr = Data So

26、urce=PC201506041939;Initial Catalog=PXSCJ;Integrated Security=True; string _sql = select XSB.XH as学号,XSB.XM as姓名,KCB.KCM as课程名,CJB.CJ as 成绩(chngj), + KCB.XF as学分,KCB.XS as学时,KCB.KKXQ as开课日期 + from XSB,KCB,CJB + where KCB.KCM= + stuKCM.Text + and CJB.XH= XSB.XH and CJB.KCH= KCB.KCH; if (stuXH.Text.Tr

27、im() != 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.Tables0.DefaultView;（4）当课程(kchng)名改变时，显示此学生(xu sheng)当前课程信息，代码如下：private

28、 void stuKCM_SelectedIndexChanged(object sender, EventArgs e) string connstr = Data Source=PC201506041939;Initial Catalog=PXSCJ;Integrated Security=True; string _sql = select XSB.XH as学号,XSB.XM as姓名,KCB.KCM as课程(kchng)名,CJB.CJ as 成绩, + KCB.XF as学分,KCB.XS as学时,KCB.KKXQ as开课日期 + from XSB,KCB,CJB + whe

29、re 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.Da

30、taSource = ds.Tables0.DefaultView; （5）当单击DataGridView时，将某条数据记录读出，代码如下：private void scoreDGV_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e) DataGridViewRow dgvRow=scoreDGV.Rowse.RowIndex; DataGridViewCellCollection dgvCC=dgvRow.Cells; stuXH.Text=dgvCC0.Value.ToString(); stuKCM.S

31、electedItem=dgvCC2.Value; stuCJ.Text=dgvCC3.Value.ToString(); stuXF.Text=dgvCC4.Value.ToString(); （6）完成(wn chng)三个按钮操作，其中更新按钮可实现添加、更改(gnggi)，其代码如下：private void button1_Click(object sender, EventArgs e) string connStr = Data Source=PC201506041939;Initial Catalog=PXSCJ;Integrated Security=True; string

32、 _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

33、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.Execute

34、Scalar() + , + int.Parse(stuCJ.Text.Trim() + ); cmd =new SqlCommand (_sql ,conn ); cmd.ExecuteNonQuery (); finally conn .Close (); （7）删除按钮实现删除功能(gngnng)，其代码如下：private void button2_Click(object sender, EventArgs e) string connStr = Data Source=PC201506041939;Initial Catalog=PXSCJ;Integrated Security=True; DialogResult ret =MessageBox.Show(确定(qudng)要删除记录吗？,删除, MessageBoxButtons.OKCancel, MessageBoxIcon.Question); if (ret=DialogResult.Cancel ) return ; string _sql = delete from CJB where XH= + stuXH.Text + and KCH=(select KCH from K