學(xué)生成績管理系統(tǒng)實(shí)驗(yàn)報(bào)告-2

數(shù)據(jù)庫系統(tǒng)原理實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)名稱學(xué)生成績管理系統(tǒng)院系計(jì)算機(jī)與信息工程學(xué)院班級2013級計(jì)算機(jī)科學(xué)與技術(shù)二班姓名郅偉遠(yuǎn)學(xué)號1308114088實(shí)驗(yàn)課程名稱數(shù)據(jù)庫系統(tǒng)原理實(shí)驗(yàn)項(xiàng)目名稱學(xué)生成績管理系統(tǒng)實(shí)驗(yàn)項(xiàng)目性質(zhì)綜合性主講教師孟慧麗開課年級2013開課專業(yè)計(jì)算機(jī)實(shí)驗(yàn)地點(diǎn)過街樓開課日期開課時間學(xué)號姓名1308114088郅偉遠(yuǎn)一、實(shí)驗(yàn)?zāi)康模菏煜げ⒄莆諗?shù)據(jù)庫系統(tǒng)原理這門課所應(yīng)用的軟件SQLServer2008，以及使用該軟件的相關(guān)要求與具體用法。在掌握SQLServer2008應(yīng)用的基礎(chǔ)上，建立相關(guān)學(xué)生成績管理信息的數(shù)據(jù)庫，并在此基礎(chǔ)上與VisualStudio.NET2008建立連接，進(jìn)行相關(guān)的操作。在VisualStudio.NET2008上進(jìn)行學(xué)生成績管理信息系統(tǒng)的查詢、更改以及成績錄入的相關(guān)界面的設(shè)計(jì)，使流程能夠順利進(jìn)行。二、總體設(shè)計(jì)（設(shè)計(jì)原理、設(shè)計(jì)方案及流程等）設(shè)計(jì)原理：1、以SQLServer2008軟件的功能與作用為基礎(chǔ)。2、以VisualStudio.NET2008軟件的功能為平臺。設(shè)計(jì)方案及流程：以VisualStudio.NET2008為前臺開發(fā)工具進(jìn)行操作，并以SQLServer2008軟件為后臺，使創(chuàng)建成功的一個學(xué)生成績管理系統(tǒng)具有如下基本功能：1、學(xué)生信息查詢，可根據(jù)學(xué)號，姓名及專業(yè)分別進(jìn)行學(xué)生信息查詢。2、學(xué)生信息修改，可在數(shù)據(jù)庫中插入，刪除，修改學(xué)生記錄3、學(xué)生成績錄入，可錄入每個學(xué)生某門課程成績。在此基礎(chǔ)的操作中上能夠添加個人的信息，如添加個人的學(xué)號，姓名，專業(yè)和成績等，并能夠執(zhí)行查詢個人相關(guān)信息的操作。三、實(shí)驗(yàn)步驟（包括主要步驟、代碼分析等）主要步驟及相應(yīng)的代碼：在SQLServer2008條件下以ID=sa,password=123456的身份進(jìn)入數(shù)據(jù)庫引擎，并將PXSCJ附加到數(shù)據(jù)庫。打開VisualStudio.NET2008，將已有的數(shù)據(jù)庫PXSCJ與VS2008建立連接。在VisualStudio.NET2008環(huán)境下，建立Windows窗體并命名為Form1,并添加相應(yīng)的程序代碼，同時需要添加相關(guān)聯(lián)的子窗體分別雙擊窗體，雙擊3個botton按鈕，添加相應(yīng)的程序代碼：privatevoidbutton1_Click(objectsender,EventArgse){//建立學(xué)生信息查詢窗體SearchFormsearchfrm=newSearchForm();searchfrm.ShowDialog();}privatevoidbutton2_Click(objectsender,EventArgse){//建立學(xué)生信息修改窗體ModifyFormmodifyfrm=newModifyForm();modifyfrm.ShowDialog();}privatevoidbutton3_Click(objectsender,EventArgse){//建立學(xué)生信息錄入窗體ScoreFormscorefrm=newScoreForm();scorefrm.ShowDialog();}在窗體SearchForm上添加相應(yīng)的控件，修改相應(yīng)控件的添加控件,合理設(shè)計(jì)此界面控件的基礎(chǔ)上，雙擊各個相應(yīng)的控件，添加相應(yīng)的程序代碼：privatestringsql="";privatevoidSearchForm_Load(objectsender,EventArgse){stringconnStr=@"DataSource=C56;InitialCatalog=G:\數(shù)據(jù)庫系統(tǒng)\PXSCJ\PXSCJ.MDF;UserID=sa;Password=123456";string_sql="selectXHas'學(xué)號',XMas'姓名',ZYas'專業(yè)',XBas'性別',"+"CSSJas'出生日期',ZXFas'總學(xué)分',BZas'備注'fromXSB";SqlConnectionconn=newSqlConnection(connStr);SqlDataAdaptersda=newSqlDataAdapter(_sql,conn);DataSetds=newDataSet();sda.Fill(ds);StuDGV.DataSource=ds.Tables[0].DefaultView;}privatevoidMakeSqlStr(){sql="";if(stuXH.Text.Trim()!=string.Empty){sql="andXHlike'%"+stuXH.Text.Trim()+"%'";}if(stuXM.Text.Trim()!=string.Empty){sql+="andXMlike'%"+stuXM.Text.Trim()+"%'";}if(stuZY.Text!="所有專業(yè)"){sql+="andZY='"+stuZY.Text+"'";}}privatevoidbutton1_Click(objectsender,EventArgse){MakeSqlStr();stringconnStr=@"DataSource=C56;InitialCatalog=G:\數(shù)據(jù)庫系統(tǒng)\PXSCJ\PXSCJ.MDF;UserID=sa;Password=123456";string_sql="selectXHas'學(xué)號',XMas'姓名',ZYas'專業(yè)',XBas'性別',"+"CSSJas'出生日期',ZXFas'總學(xué)分',BZas'備注'fromXSBwhere1=1"+sql;SqlConnectionconn=newSqlConnection(connStr);SqlDataAdaptersda=newSqlDataAdapter(_sql,conn);DataSetds=newDataSet();sda.Fill(ds);StuDGV.DataSource=ds.Tables[0].DefaultView;}在窗體ModifyForm上添加相應(yīng)的控件：在工具箱中合理的將控件擺放在窗體上，雙擊相應(yīng)的控件，并添加相應(yīng)的代碼：privatevoidModifyForm_Load(objectsender,EventArgse){stringconnStr=@"DataSource=c56;InitialCatalog=G:\數(shù)據(jù)庫系統(tǒng)\PXSCJ\PXSCJ.MDF;PersistSecurityInfo=True;UserID=sa;Password=123456";string_sql="selectXHas'學(xué)號',XMas'姓名',ZYas'專業(yè)',XBas'性別',"+"CSSJas'出生年月',ZXFas'總學(xué)分',BZas'備注'fromXSB";SqlConnectionconn=newSqlConnection(connStr);SqlDataAdaptersda=newSqlDataAdapter(_sql,conn);DataSetds=newDataSet();sda.Fill(ds);StuDGV.DataSource=ds.Tables[0].DefaultView;}privatevoidstuDelete_Click(objectsender,EventArgse){stringconnStr=@"DataSource=c56;InitialCatalog=G:\數(shù)據(jù)庫系統(tǒng)\PXSCJ\PXSCJ.MDF;PersistSecurityInfo=True;UserID=sa;Password=123456";DialogResultret=MessageBox.Show("確定要刪除記錄嗎？","刪除",MessageBoxButtons.OKCancel,MessageBoxIcon.Question);if(ret==DialogResult.Cancel)return;string_sql="deletefromXSBwhereXH='"+stuXH2.Text+"'";SqlConnectionconn=newSqlConnection(connStr);SqlCommandcmd=newSqlCommand(_sql,conn);try{conn.Open();introws=cmd.ExecuteNonQuery();ModifyForm_Load(null,null);if(rows==1){MessageBox.Show("刪除成功！","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);}}finally{conn.Close();}}privatevoidstuUpdate_Click(objectsender,EventArgse){stringconnStr=@"DataSource=c56;InitialCatalog=G:\數(shù)據(jù)庫系統(tǒng)\PXSCJ\PXSCJ.MDF;PersistSecurityInfo=True;UserID=sa;Password=123456";string_sql="selectcount(*)fromXSBwhereXH='"+stuXH2.Text+"'";SqlConnectionconn=newSqlConnection(connStr);SqlCommandcmd=newSqlCommand(_sql,conn);try{conn.Open();intcnt=(int)cmd.ExecuteScalar();if(cnt==1){_sql="updateXSBsetXM='"+stuXM2.Text+"',ZY='"+stuZY2.Text+"',ZXF="+int.Parse(stuZXF2.Text)+",BZ='"+stuBZ.Text+"'whereXH='"+stuXH2.Text+"'";}else{stringsex=male.Checked?"男":"女";_sql="insertintoXSB(XH,XM,ZY,ZXF)values('"+stuXH2.Text+"','"+stuXM2.Text+"','"+stuZY2.Text+"',"+int.Parse(stuZXF2.Text)+")";}cmd=newSqlCommand(_sql,conn);cmd.ExecuteNonQuery();ModifyForm_Load(null,null);}finally{conn.Close();}}privatevoidstuCancel_Click(objectsender,EventArgse){this.Close();}privatevoidStuDGV_RowHeaderMouseClick_1(objectsender,DataGridViewCellMouseEventArgse){DataGridViewRowdgvRow=StuDGV.Rows[e.RowIndex];DataGridViewCellCollectiondgvCC=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;elsefemale.Checked=true;stuCSSJ2.Text=Convert.ToDateTime(dgvCC[4].Value).ToShortTimeString();stuZXF2.Text=dgvCC[5].Value.ToString();stuBZ.Text=dgvCC[6].Value.ToString();}在窗體ScoreForm上添加相應(yīng)的控件：添加1個Lable控件，并將Text改為“學(xué)生成績錄入”；拖拽6個Lable控件，將其屬性Text分別設(shè)置為“姓名，成績，課程名”;合理的將控件擺放在窗體上，雙擊相應(yīng)的控件，并添加相應(yīng)的代碼：privatevoidScoreForm_Load(objectsender,EventArgse){stringconnStr=@"DataSource=C56;InitialCatalog=G:\數(shù)據(jù)庫系統(tǒng)\PXSCJ\PXSCJ.MDF;UserID=sa;Password=123456";string_sql="selectdistinctZYfromXSB";SqlConnectionconn=newSqlConnection(connStr);SqlCommandcmd=newSqlCommand(_sql,conn);try{conn.Open();SqlDataReaderdr=cmd.ExecuteReader();//讀取專業(yè)名while(dr.Read()){stuZY3.Items.Add(dr[0]);}dr.Close();_sql="selectKCMfromKCB";cmd=newSqlCommand(_sql,conn);dr=cmd.ExecuteReader();//讀取課程名while(dr.Read()){stuKCM3.Items.Add(dr[0]);}dr.Close();}finally{conn.Close();}}privatevoidupdate_btn_Click(objectsender,EventArgse){stringconnStr=@"DataSource=C56;InitialCatalog=G:\數(shù)據(jù)庫系統(tǒng)\PXSCJ\PXSCJ.MDF;UserID=sa;Password=123456";string_sql="selectcount(*)fromCJBwhereCJB.XH='"+stuXH3.Text+"'andCJB.KCH=(selectKCHfromKCBwhereKCM='"+stuKCM3.Text.Trim()+"')";SqlConnectionconn=newSqlConnection(connStr);SqlCommandcmd=newSqlCommand(_sql,conn);try{conn.Open();intcnt=(int)cmd.ExecuteScalar();if(cnt==1){_sql="updateCJBsetCJB.CJ='"+stuCJ3.Text+"'whereCJB.XH='"+stuXH3.Text+"'andCJB.KCH=(selectKCHfromKCBwhereKCM='"+stuKCM3.Text+"')";}else{string_sql2="selectKCHfromKCBwhereKCM='"+stuKCM3.Text+"'";SqlCommandcmd2=newSqlCommand(_sql2,conn);_sql="insertintoCJBvalues('"+stuXH3.Text.Trim()+"',"+cmd2.ExecuteScalar()+","+int.Parse(stuCJ3.Text.Trim())+")";}cmd=newSqlCommand(_sql,conn);cmd.ExecuteNonQuery();_sql="selectXSB.XHas'學(xué)號',XSB.XMas'姓名',KCB.KCMas'課程名',CJB.CJas'成績',KCB.XFas'學(xué)分',KCB.XSas'學(xué)時',KCB.KKXQas'開課學(xué)期'fromXSB,KCB,CJBwhereXSB.XH=CJB.XHandKCB.KCH=CJB.KCHandKCB.KCM='"+stuKCM3.Text+"'"+"andXSB.XH='"+stuXH3.Text+"'";SqlDataAdaptersda=newSqlDataAdapter(_sql,conn);DataSetds=newDataSet();sda.Fill(ds);scoreDGV.DataSource=ds.Tables[0].DefaultView;}finally{conn.Close();}}privatevoiddelete_btn_Click(objectsender,EventArgse){stringconnStr=@"DataSource=C56;InitialCatalog=G:\數(shù)據(jù)庫系統(tǒng)\PXSCJ\PXSCJ.MDF;UserID=sa;Password=123456";DialogResultret=MessageBox.Show("確定要刪除記錄嗎?","刪除",MessageBoxButtons.OKCancel,MessageBoxIcon.Question);if(ret==DialogResult.Cancel){return;}string_sql="deletefromCJBwhereXH='"+stuXH3.Text.Trim()+"'andKCH=(selectKCHfromKCBwhereKCM='"+stuKCM3.Text.Trim()+"')";SqlConnectionconn=newSqlConnection(connStr);SqlCommandcmd=newSqlCommand(_sql,conn);try{conn.Open();introws=cmd.ExecuteNonQuery();if(rows==1){MessageBox.Show("刪除成功!","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);}}finally{conn.Close();}}privatevoidcancel_btn_Click(objectsender,EventArgse){this.Close();}privatevoidstuZY3_SelectedIndexChanged(objectsender,EventArgse){stringconnStr=@"DataSource=C56;InitialCatalog=G:\數(shù)據(jù)庫系統(tǒng)\PXSCJ\PXSCJ.MDF;UserID=sa;Password=123456";string_sql="selectXHfromXSBwhereZY='"+stuZY3.Text+"'";//清空現(xiàn)有的學(xué)號stuXH3.Items.Clear();SqlConnectionconn=newSqlConnection(connStr);SqlCommandcmd=newSqlCommand(_sql,conn);try{conn.Open();SqlDataReaderdr=cmd.ExecuteReader();//讀取相應(yīng)的學(xué)號while(dr.Read()){stuXH3.Items.Add(dr[0]);}dr.Close();_sql="selectXSB.XHas'學(xué)號',XSB.XMas'姓名',KCB.KCMas'課程名',CJB.CJas'成績',"+"KCB.XFas'學(xué)分',KCB.XSas'學(xué)時',KCB.KKXQas'開課學(xué)期'fromXSB,KCB,CJBwhereXSB.XH=CJB.XHandKCB.KCH=CJB.KCHandXSB.ZY='"+stuZY3.Text+"'";SqlDataAdaptersda=newSqlDataAdapter(_sql,conn);DataSetds=newDataSet();sda.Fill(ds);scoreDGV.DataSource=ds.Tables[0].DefaultView;}finally{conn.Close();}}privatevoidstuXH3_SelectedIndexChanged(objectsender,EventArgse){stringconnStr=@"DataSource=C56;InitialCatalog=G:\數(shù)據(jù)庫系統(tǒng)\PXSCJ\PXSCJ.MDF;UserID=sa;Password=123456";string_sql="selectXSB.XHas'學(xué)號',XSB.XMas'姓名',KCB.KCMas'課程名',CJB.CJas'成績',KCB.XFas'學(xué)分',KCB.XSas'學(xué)時',KCB.KKXQas'開課學(xué)期'fromXSB,KCB,CJBwhereXSB.XH=CJB.XHandKCB.KCH=CJB.KCHandXSB.XH='"+stuXH3.Text+"'";if(stuKCM3.Text.Trim()!=string.Empty){_sql+="andKCB.KCM='"+stuKCM3.Text+"";}SqlConnectionconn=newSqlConnection(connStr);SqlDataAdaptersda=newSqlDataAdapter(_sql,conn);DataSetds=newDataSet();sda.Fill(ds);scoreDGV.DataSource=ds.Tables[0].DefaultView;