C#與SQL數(shù)據(jù)庫(kù)學(xué)生成績(jī)管理系統(tǒng)完整代碼

1、數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用項(xiàng)目設(shè)計(jì)報(bào)告學(xué)生成績(jī)管理系統(tǒng) 姓名：Celia Yan 2015-01-07一設(shè)計(jì)目的及意義在如今的高校日常管理中，學(xué)生成績(jī)管理系統(tǒng)是其中非常重要的一環(huán)，特別是當(dāng)前學(xué)校規(guī)模不斷擴(kuò)大，學(xué)生人數(shù)日益增加，課程門(mén)類(lèi)多，校區(qū)分散等實(shí)際情況，學(xué)生成績(jī)統(tǒng)計(jì)功能越來(lái)越繁重，稍有疏忽就會(huì)出現(xiàn)差錯(cuò)。因此，學(xué)生成績(jī)管理系統(tǒng)更具有非常大的實(shí)際應(yīng)用意義。在互聯(lián)網(wǎng)快速崛起的今天，改革傳統(tǒng)的手工錄入方式，公正，準(zhǔn)確，及時(shí)反映學(xué)生的信息和成績(jī)的情況，以適應(yīng)信息時(shí)代的要求，是學(xué)生成績(jī)管理系統(tǒng)的一個(gè)新的理念。通過(guò)成績(jī)管理可以大大提高學(xué)校的工作效率。學(xué)生成績(jī)管理系統(tǒng)應(yīng)該完成以下兩個(gè)方面的內(nèi)容：學(xué)生檔案資料的管理、

2、學(xué)生成績(jī)的管理。通過(guò)學(xué)生成績(jī)管理系統(tǒng)可以做到信息的規(guī)范管理，科學(xué)統(tǒng)計(jì)和快速查詢(xún)、修改、增加、刪除等，減少管理方面的工作量。二主要功能該系統(tǒng)主要用于學(xué)校學(xué)生信息管理，總體任務(wù)是實(shí)現(xiàn)學(xué)生信息關(guān)系的系統(tǒng)化、規(guī)范化和自動(dòng)化，其主要任務(wù)是用計(jì)算機(jī)對(duì)學(xué)生信息進(jìn)行日常管理，如查詢(xún)、修改、增加、刪除，另外還考慮到用戶(hù)登錄的權(quán)限，針對(duì)學(xué)生信息和權(quán)限登錄的學(xué)生成績(jī)管理系統(tǒng)。本系統(tǒng)主要包括注冊(cè)管理、教師管理、學(xué)生信息查詢(xún)、添加、修改、刪除等部分。其主要功能有：（1） 學(xué)生信息的添加，包括輸入學(xué)生基本信息和成績(jī)。（2） 學(xué)生信息的查詢(xún)，包括查詢(xún)學(xué)生的基本信息和成績(jī)。（3） 學(xué)生信息的修改，包括修改學(xué)生基本信息和成績(jī)。

3、（4） 學(xué)生信息的刪除，包括刪除學(xué)生基本信息和成績(jī)。（5） 登錄用戶(hù)密碼修改，用戶(hù)登錄到系統(tǒng)可進(jìn)行相應(yīng)的用戶(hù)密碼修改。（6） 管理員用戶(hù)對(duì)用戶(hù)名的管理，包括添加新用戶(hù)、刪除用戶(hù)。 學(xué)生成績(jī)管理系統(tǒng)是典型的信息管理系統(tǒng)，其開(kāi)發(fā)主要包括后臺(tái)數(shù)據(jù)庫(kù)的建立和維護(hù)以及前端應(yīng)用程序開(kāi)發(fā)兩個(gè)方面。對(duì)于前者要求建立起數(shù)據(jù)一致性和完整性強(qiáng)、數(shù)據(jù)安全性好的數(shù)據(jù)庫(kù)。對(duì)于后者則要求應(yīng)用程序功能完備，易使用。該管理系統(tǒng)我使用的是Microsoft Visual Studio 2012 及 Microsoft SQL Server 2008。系統(tǒng)功能流程圖圖 2.1 系統(tǒng)功能流程三數(shù)據(jù)庫(kù)設(shè)計(jì)3.1本系統(tǒng)的數(shù)據(jù)庫(kù)采用的是S

4、QL Server2008 。該數(shù)據(jù)庫(kù)包括學(xué)生成績(jī)表、用戶(hù)登錄表用于紀(jì)錄學(xué)生的基本信息數(shù)據(jù)庫(kù)表結(jié)構(gòu)如下：成績(jī)表物理結(jié)構(gòu)圖3.1圖3.2用戶(hù)登錄表物理結(jié)構(gòu)：圖3.3圖3.43.2觸發(fā)器刪除DLB中的記錄時(shí)同時(shí)刪除CJB中姓名相同的記錄USE studentscoreGO/* Object: Trigger dbo.dlb_delete Script Date: 01/07/2015 12:42:17 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER trigger dbo.dlb_deleteon dbo.dlb after deletea

5、sbegindeclare leib varchar(50) select leib='學(xué)生'from deleteddelete from cjb where 姓名 in (select 用戶(hù)名 from deleted)End3.3數(shù)據(jù)庫(kù)連接 string constr = "Password=617804;Persist Security Info=True;User ID=ywj;Initial Catalog=studentscore;Data Source=YANJING" SqlConnection conn = new SqlConnecti

6、on(constr); string sqlstr = "select * from cjb" SqlCommand cmd = new SqlCommand(sqlstr, conn); SqlDataReader reader = cmd.ExecuteReader(); DataSet ds = new DataSet(); while (reader.Read() string id = reader"學(xué)號(hào)".ToString(); string name = reader"姓名".ToString(); MessageBox

7、.Show(string.Format("id=0,name=1,學(xué)號(hào)，姓名"); 四系統(tǒng)實(shí)現(xiàn) 4.1.登錄界面圖4.1該界面是學(xué)生成績(jī)管理系統(tǒng)的登錄界面，可以選擇作為學(xué)生，管理員或者教師身份登錄。每一個(gè)進(jìn)入系統(tǒng)的人都會(huì)看到當(dāng)前的時(shí)間，在登錄時(shí)只有用戶(hù)名，密碼，與身份相符合時(shí)才能進(jìn)入系統(tǒng)，否則登錄無(wú)法成功。using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using

8、 System.Text;using System.Threading.Tasks;using System.Windows.Forms;using System.Data.SqlClient;namespace WindowsFormsApplication1 public partial class Form1 : Form static public string sn, sub; public Form1() InitializeComponent(); Form2 fr2 = new Form2(); Form3 fr3 = new Form3(); Form4 fr4 = new

9、Form4(); private void Form1_Load(object sender, EventArgs e) timer1.Start(); private void label1_Click(object sender, EventArgs e) private void button1_Click(object sender, EventArgs e) string constr = "Password=617804;Persist Security Info=True;User ID=ywj;Initial Catalog=studentscore;Data Sou

10、rce=YANJING" SqlConnection conn = new SqlConnection(constr); conn.Open(); if (textname.Text = "" | textpassword.Text = "") MessageBox.Show("信息不全，請(qǐng)不要遺漏信息！"); if (rbtnmanager.Checked) string cstr = "select * from dlb where 類(lèi)別='管理員' and 用戶(hù)名='" +

11、textname.Text.Trim() + "'and 密碼='" + textpassword.Text.Trim() + "'" SqlCommand comm = new SqlCommand(cstr, conn); SqlDataReader dr = comm.ExecuteReader(); if (dr.Read() sn = textname.Text.Trim(); fr4.Show(); this.Visible = false; ; else MessageBox.Show("密碼或用戶(hù)名出錯(cuò)，

12、請(qǐng)重新輸入！"); textname.Text = "" textpassword.Text = "" if (rbtnteacher.Checked) string cstr = "select * from dlb where 類(lèi)別='教師' and 用戶(hù)名='" + textname.Text.Trim() + "'and 密碼='" + textpassword.Text.Trim() + "'" SqlCommand comm

13、= new SqlCommand(cstr, conn); SqlDataReader dr = comm.ExecuteReader(); if (dr.Read() sn = textname.Text.Trim(); sub = dr.GetValue(3).ToString(); fr3.Show(); this.Visible = false; else MessageBox.Show("密碼或用戶(hù)名出錯(cuò)，請(qǐng)重新輸入！"); textname.Text = "" textpassword.Text = "" if (rbtn

14、student.Checked) string cstr = "select * from dlb where 類(lèi)別='學(xué)生' and 用戶(hù)名='" + textname.Text.Trim() + "'and 密碼='" + textpassword.Text.Trim() + "'" SqlCommand comm = new SqlCommand(cstr, conn); SqlDataReader dr = comm.ExecuteReader(); if (dr.Read()

15、sn = textname.Text.Trim(); fr2.Show(); this.Visible = false; else MessageBox.Show("密碼或用戶(hù)名出錯(cuò)，請(qǐng)重新輸入！"); textname.Text = "" textpassword.Text = "" conn.Close(); conn.Dispose(); private void button2_Click(object sender, EventArgs e) Application.Exit(); private void timer1_T

16、ick(object sender, EventArgs e) label4.Text = "當(dāng)前時(shí)間：" + DateTime.Now.ToLongDateString() + "" + DateTime.Now.ToLongTimeString(); 4.2學(xué)生成績(jī)管理圖4.2設(shè)計(jì)過(guò)程： 此部分主要針對(duì)學(xué)生信息的管理，可以查詢(xún)學(xué)生的所有成績(jī)信息，實(shí)現(xiàn)成績(jī)的添加、刪除、修改、計(jì)算平均分等功能。還可以修改當(dāng)前用戶(hù)下的密碼。 private void Form2_Load(object sender, EventArgs e) label1.Te

17、xt = Form1.sn + "同學(xué)，歡迎你進(jìn)入成績(jī)管理系統(tǒng)！" timer1.Start(); groupBox1.Visible = false; groupBox2.Visible = false; private void button1_Click(object sender, EventArgs e) private void button3_Click(object sender, EventArgs e) / string constr = "Password=null;Persist Security Info=True;User ID=BIN

18、IANDOUKOUadministrator;Initial Catalog=studentscore;Data Source=BINIANDOUKOUSQLEXPRESS" /SqlConnection conn = new SqlConnection(constr); /conn.Open(); /SqlDataAdapter da = new SqlDataAdapter("Select * from cjb where 姓名="+textBox1 .Text , conn); /DataSet ds = new DataSet(); /da.Fill(ds

19、, "usertable"); /dataGridView1.DataSource = ; private void toolStripButton1_Click(object sender, EventArgs e) dataGridView1.Visible = true; groupBox1.Visible = false; groupBox2.Visible = false; string constr = "Password=617804;Persist Security Info=True;User ID=ywj;Initial Catalog=stu

20、dentscore;Data Source=YANJING" SqlConnection conn = new SqlConnection(constr); conn.Open(); SqlDataAdapter da = new SqlDataAdapter("select * from cjb where 姓名='" + Form1.sn.Trim() + "'", conn);/-詳細(xì)信息 DataSet ds = new DataSet(); da.Fill(ds, "usertable"); dat

21、aGridView1.DataSource = ds.Tables"usertable".DefaultView; conn.Close(); conn.Dispose(); private void toolStripButton2_Click(object sender, EventArgs e) private void toolStripButton4_Click(object sender, EventArgs e) groupBox1.Visible = true; private void button1_Click_1(object sender, Even

22、tArgs e) string constr = "Password=617804;Persist Security Info=True;User ID=ywj;Initial Catalog=studentscore;Data Source=YANJING" SqlConnection conn = new SqlConnection(constr); conn.Open(); if (textnpd.Text != textpassword.Text) MessageBox.Show("密碼輸入有誤，請(qǐng)重新輸入"); if (textnpd.Text

23、 = "" && textpassword.Text = "") MessageBox.Show("密碼不允許為空！"); if (textnpd.Text=textpassword.Text && textnpd.Text !="") SqlCommand com = new SqlCommand("update dlb set 密碼='" + textpassword.Text.Trim() + "'where 用戶(hù)名='&q

24、uot; + Form1.sn.Trim() + "'", conn); if (com.ExecuteNonQuery() = 1) MessageBox.Show("密碼更改成功"); groupBox1.Visible = false; conn.Close(); conn.Dispose(); private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) private void toolStripButton5_Click(

25、object sender, EventArgs e) this.Close(); Form1 l = new Form1(); l.Visible = true; private void toolStripButton6_Click(object sender, EventArgs e) / string constr = "Password=null;Persist Security Info=True;User ID=BINIANDOUKOUadministrator;Initial Catalog=studentscore;Data Source=BINIANDOUKOUS

26、QLEXPRESS" /SqlConnection conn = new SqlConnection(constr); /conn.Open(); /SqlDataAdapter da = new SqlDataAdapter("Select * from cjb where 姓名="+textBox1 .Text , conn); /DataSet ds = new DataSet(); /da.Fill(ds, "usertable"); /string s; /for(int i=2;i <=6;i+) / / if (int.Pa

27、rse(ds.Tables"usertable".Rows0i.ToString() <60) / s=int.Parse(ds.Tables"usertable".Rows0i.ToString(); / private void groupBox2_Enter(object sender, EventArgs e) dataGridView1.Visible = false; groupBox1.Visible = false; private void toolStripButton3_Click(object sender, EventAr

28、gs e) groupBox2 .Visible=true; string constr = "Password=617804;Persist Security Info=True;User ID=ywj;Initial Catalog=studentscore;Data Source=YANJING" SqlConnection conn = new SqlConnection(constr); conn.Open(); SqlDataAdapter da = new SqlDataAdapter("Select * from cjb where 姓名='

29、;"+Form1 .sn .Trim ()+"'" , conn); DataSet ds = new DataSet(); da.Fill(ds, "usertable"); int max=0,min=1001; double ave = 0.0; for(int i=2;i <=6;i+) if(int.Parse(ds.Tables"usertable".Rows0i.ToString()>max) max=int.Parse(ds.Tables"usertable".Rows

30、0i.ToString(); if (int.Parse(ds.Tables"usertable".Rows0i.ToString()<min ) min=int.Parse(ds.Tables"usertable".Rows0i.ToString(); txthscore.Text=max.ToString(); txtlscore.Text=min.ToString(); txtall.Text=ds.Tables"usertable".Rows0"總分".ToString(); ave=int.Pars

31、e(ds.Tables"usertable".Rows0"總分".ToString()/(double)5; txtave.Text=ave.ToString(); conn.Close();conn.Dispose(); private void timer1_Tick(object sender, EventArgs e) label2.Text="當(dāng)前時(shí)間："+DateTime.Now.ToLongDateString()+DateTime.Now.ToLongTimeString(); 4.3教師管理界面 圖4.3設(shè)計(jì)過(guò)程：&

32、#160;此部分主要針對(duì)教師信息管理，可以查詢(xún)學(xué)生的所有成績(jī)信息，實(shí)現(xiàn)成績(jī)的添加、刪除、修改、成績(jī)升序排序等功能。還可以修改當(dāng)前用戶(hù)下的密碼。private void button1_Click(object sender, EventArgs e) /查找 string constr = "Password=617804;Persist Security Info=True;User ID=ywj;Initial Catalog=studentscore;Data Source=YANJING" SqlConnection conn= new SqlConnection(

33、constr); conn.Open(); if (comboBox1.Text="學(xué)號(hào)") SqlCommand cm=new SqlCommand("select 學(xué)號(hào),"+Form1 .sub + "from cjb where 學(xué)號(hào)='"+ textBox1.Text+"'",conn ); if (cm.ExecuteScalar ()=null)MessageBox.Show("用戶(hù)名不存在"); else SqlDataAdapter da =new SqlData

34、Adapter("select 學(xué)號(hào),"+Form1.sub+" from cjb where 學(xué)號(hào)='"+textBox1.Text+"'",conn); DataSet ds =new DataSet(); da.Fill (ds ,"chaxun"); dataGridView1.DataSource=ds.Tables"chaxun".DefaultView; if (comboBox1.Text="姓名") SqlCommand cm=new Sql

35、Command("select 姓名," + Form1.sub + "from cjb where 姓名='" + textBox1.Text+"'",conn ); if (cm.ExecuteScalar()=null)MessageBox.Show("該用戶(hù)不存在"); else SqlDataAdapter da = new SqlDataAdapter("select 姓名,"+Form1.sub + " from cjb where 姓名='&qu

36、ot; + textBox1.Text + "'",conn ); DataSet ds = new DataSet(); da.Fill (ds,"table"); dataGridView1.DataSource=ds.Tables"table".DefaultView; conn.Close();conn.Dispose(); private void textBox1_TextChanged(object sender, EventArgs e) private void toolStripButton2_Click(

37、object sender, EventArgs e) /查找 groupBox3 .Visible =false ; groupBox1.Enabled = true; groupBox2.Enabled = false; private void toolStripButton1_Click(object sender, EventArgs e) /所有成績(jī) groupBox3.Visible = true; string constr = "Password=617804;Persist Security Info=True;User ID=ywj;Initial Catalo

38、g=studentscore;Data Source=YANJING" SqlConnection conn = new SqlConnection(constr); conn.Open(); SqlDataAdapter da = new SqlDataAdapter("select 學(xué)號(hào),姓名," + Form1.sub + " from cjb ",conn); DataSet ds = new DataSet(); da.Fill(ds, "table"); dataGridView1 .DataSource =ds

39、.Tables "table".DefaultView; conn.Close ();conn .Dispose(); private void button2_Click(object sender, EventArgs e) /修改成績(jī) string constr = "Password=617804;Persist Security Info=True;User ID=ywj;Initial Catalog=studentscore;Data Source=YANJING" SqlConnection conn = new SqlConnectio

40、n(constr); conn.Open(); SqlCommand com = new SqlCommand("update cjb set " + Form1.sub + "='" + textBox3.Text + "'where 姓名 ='" + textBox4.Text + "'", conn); if (com.ExecuteNonQuery() = 1) MessageBox.Show("修改成功"); else MessageBox.Show(&

41、quot;信息有誤，請(qǐng)重新輸入"); conn.Close(); conn.Dispose(); private void toolStripButton3_Click(object sender, EventArgs e) /修改學(xué)生成績(jī) groupBox3 .Visible =false ; groupBox2.Enabled = true; groupBox1.Enabled = false; groupBox3.Visible = false; private void textBox3_TextChanged(object sender, EventArgs e) priv

42、ate void groupBox2_Enter(object sender, EventArgs e) private void button3_Click(object sender, EventArgs e) /確認(rèn)修改密碼 string constr = "Password=617804;Persist Security Info=True;User ID=ywj;Initial Catalog=studentscore;Data Source=YANJING" SqlConnection conn = new SqlConnection(constr); conn

43、.Open(); if (txtnpd.Text != txtnpassword.Text) MessageBox.Show("兩次輸入密碼不一致，請(qǐng)重新輸入"); if (txtnpd.Text = "" && txtnpassword.Text = "") MessageBox.Show("密碼不允許為空，請(qǐng)輸入密碼"); if (txtnpd.Text = txtnpassword.Text && txtnpd.Text != "") SqlCommand

44、com = new SqlCommand("update dlb set 密碼='" + txtnpassword.Text.Trim() + "'where 用戶(hù)名='" + Form1.sn.Trim() + "'", conn); if (com.ExecuteNonQuery() = 1) MessageBox.Show("密碼更改成功"); groupBox1.Visible = true; conn.Close(); conn.Dispose(); private voi

45、d toolStripButton5_Click(object sender, EventArgs e) groupBox4.Visible = true; groupBox3.Visible = true; dataGridView1.Visible = false; private void toolStripButton4_Click(object sender, EventArgs e) /升序排序 groupBox3.Visible = true; string constr = "Password=617804;Persist Security Info=True;Use

46、r ID=ywj;Initial Catalog=studentscore;Data Source=YANJING" SqlConnection conn = new SqlConnection(constr); conn.Open(); SqlDataAdapter da = new SqlDataAdapter("select 學(xué)號(hào),姓名," + Form1.sub + " from cjb order by " + Form1.sub + " desc ", conn); DataSet ds = new DataSe

47、t(); da.Fill(ds, "table"); dataGridView1.DataSource = ds.Tables"table".DefaultView; conn.Close(); conn.Dispose(); private void toolStripButton6_Click(object sender, EventArgs e) this.Close(); Form1 l = new Form1(); l.Visible = true; private void timer1_Tick(object sender, EventAr

48、gs e) label8.Text = "當(dāng)前時(shí)間:" + DateTime.Now.ToLongDateString() + DateTime.Now.ToLongTimeString(); 4.4管理員管理界面 圖4.4設(shè)計(jì)過(guò)程： 此部分主要針對(duì)管理員的，可以添加和刪除所有用戶(hù)的賬號(hào)和密碼。 private void Form4_Load(object sender, EventArgs e) timer1.Start(); label1.Text = Form1.sn + "管理員，歡迎進(jìn)入成績(jī)管理系統(tǒng)！" groupBox2.Enabled = false; private void label1_Click(object sender, EventArgs e) private void radio