C#数据库管理系统范例.doc

C#综合实验报告 班级：0882052班 学号：14号 姓名：郭斌 指导老师：方芳 南昌航空大学科技学院一、实验目的1 通过完成简单用户需求分析以及数据库的SQL操作具体实现等全过程，把前面的各个实验更好地综合起来。2 进一步理解和掌握教材中的相关内容。3 掌握分析和设计一个大型数据库系统的基本思路与方法。二、 实验要求 1独立完成该系统的数据库设计。2实现数据库的设计，并在Access上调试通过。3写出查询、更新SQL语句和执行结果。4在前台实现对后台数据库的添加、删除、查询和修改操作。三、 实验内容 综合前面各章内容设计并调试一学生成绩管理系统，C#作为前台开发工具，Access完成后台数据库的管理。创建学生成绩管理系统所需的表，实现对学生成绩、课程、学生基本信息进行录入、修改、删除、查询的功能；其中查询应完成模糊查询和精确查询。具体如下：主函数程序: static class Program / / 应用程序的主入口点。 / STAThread static void Main() Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); Application.Run(new Form4(); 1 数据库三张表2 登录界面与代码代码：private void button1_Click(object sender, EventArgs e) try if (textBox1.Text = ) MessageBox.Show(请输入帐号, 信息提示, MessageBoxButtons.OK); else if (textBox2.Text = ) MessageBox.Show(请输入密码,信息提示,MessageBoxButtons.OK); else if (textBox1.Text = 088205214 & textBox2.Text = 088205214) Form5 main = new Form5(); main.Show(); this.Hide(); else MessageBox.Show(用户名或者密码错误, 信息提示, MessageBoxButtons.OK); catch (Exception ex) MessageBox.Show(ex.ToString(); private void Form4_Load(object sender, EventArgs e) textBox2.PasswordChar = *; private void button2_Click(object sender, EventArgs e) this.Close(); 3 进入系统界面与其代码代码： private void button1_Click(object sender, EventArgs e) Form1 main = new Form1(); main.Show(); this.Hide(); private void button2_Click(object sender, EventArgs e) Form2 main = new Form2(); main.Show(); this.Hide(); private void button3_Click(object sender, EventArgs e) Form3 main = new Form3(); main.Show(); this.Hide(); private void button4_Click(object sender, EventArgs e) Form4 main = new Form4(); main.Show(); this.Hide(); 4 学生系统的操作与代码1） 模糊查找和精确查找代码： private void button1_Click(object sender, EventArgs e) string mystr, mysql; OleDbConnection myconn = new OleDbConnection(); OleDbCommand mycmd = new OleDbCommand(); mystr = Provider = Microsoft.Jet.OLEDB.4.0; + Data Source = G:数据库db1.mdb; myconn.ConnectionString = mystr; if (radioButton1.Checked) myconn.Open(); mysql = SELECT * FROM Students WHERE StudentNo LIKE 21% ; mycmd.CommandText = mysql; mycmd.Connection = myconn; mycmd.Parameters.Add(no, OleDbType.VarChar, 10).Value = textBox1.Text + %; OleDbDataReader myreader = mycmd.ExecuteReader(); listBox1.Items.Add(学号t 姓名t性别t 生日t 籍贯t 地址); listBox1.Items.Add(-); while (myreader.Read() listBox1.Items.Add(String.Format(0t1t2t3t4t5, myreader0.ToString(), myreader1.ToString(), myreader2.ToString(), myreader3.ToString(), myreader4.ToString(), myreader5.ToString(); myconn.Close(); myreader.Close(); else if (radioButton2.Checked) myconn.Open(); mysql = SELECT * FROM Students WHERE StudentNo=no ; mycmd.CommandText = mysql; mycmd.Connection = myconn; mycmd.Parameters.Add(no, OleDbType.VarChar, 10).Value = textBox1.Text ; OleDbDataReader myreader = mycmd.ExecuteReader(); listBox1.Items.Add(学号t 姓名t性别t 生日t 籍贯t 地址); listBox1.Items.Add(-); while (myreader.Read() listBox1.Items.Add(String.Format(0t1t2t3t4t5, myreader0.ToString(), myreader1.ToString(), myreader2.ToString(), myreader3.ToString(), myreader4.ToString(), myreader5.ToString(); myconn.Close(); myreader.Close(); else MessageBox.Show(你没有选择查找方式, 信息提示, MessageBoxButtons.OK); 2） 修改代码： private void button3_Click(object sender, EventArgs e) string mystr, mysql; OleDbConnection myconn = new OleDbConnection(); OleDbCommand mycmd = new OleDbCommand(); mystr = Provider = Microsoft.Jet.OLEDB.4.0; + Data Source =G:数据库db1.mdb; myconn.ConnectionString = mystr; try myconn.Open(); mysql = update Students set StudentName= + textBox7.Text + , Sex= + textBox3.Text + where StudentNo= + textBox4.Text + ; label9.Text = 数据修改成功; mycmd.CommandText = mysql; mycmd.Connection = myconn; mycmd.ExecuteNonQuery(); myconn.Close(); catch (Exception Err) MessageBox.Show(Err.Message, 信息提示, MessageBoxButtons.OK, MessageBoxIcon.Error); myconn.Close(); 3） 添加代码： private void button2_Click(object sender, EventArgs e) string mystr, mysql; OleDbConnection myconn = new OleDbConnection(); OleDbCommand mycmd = new OleDbCommand(); mystr = Provider = Microsoft.Jet.OLEDB.4.0; + Data Source =G:数据库db1.mdb; myconn.ConnectionString = mystr; try myconn.Open(); mysql = INSERT INTO Students (StudentNo,StudentName,Sex,Birthday,Native,Address) VALUES (Num,Name,Sex,Birth,Native,Address) ; mycmd.Parameters.Add(Num, OleDbType.VarChar, 50).Value = textBox4.Text; mycmd.Parameters.Add(Name, OleDbType.VarChar, 50).Value = textBox7.Text; mycmd.Parameters.Add(Sex, OleDbType.VarChar, 50).Value = textBox3.Text; mycmd.Parameters.Add(Birth, OleDbType.VarChar, 50).Value = textBox2.Text; mycmd.Parameters.Add(Native, OleDbType.VarChar, 50).Value = textBox5.Text; mycmd.Parameters.Add(Address, OleDbType.VarChar, 50).Value = textBox6.Text; label10.Text = 数据添加成功; mycmd.CommandText = mysql; mycmd.Connection = myconn; mycmd.ExecuteNonQuery(); myconn.Close(); /Variant catch (Exception Err) MessageBox.Show(Err.Message, 信息提示, MessageBoxButtons.OK, MessageBoxIcon.Error); myconn.Close(); 4） 删除代码： private void button4_Click(object sender, EventArgs e) string mystr, mysql; OleDbConnection myconn = new OleDbConnection(); OleDbCommand mycmd = new OleDbCommand(); mystr = Provider = Microsoft.Jet.OLEDB.4.0; + Data Source = G:数据库db1.mdb; myconn.ConnectionString = mystr; DialogResult Re = MessageBox.Show(删除不可复原，继续删除？, 信息提示, MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question); if (Re = DialogResult.Yes) try myconn.Open(); mysql = DELETE FROM Students WHERE StudentNo=no ; mycmd.Parameters.Add(no, OleDbType.VarChar, 10).Value = textBox4.Text; label11.Text = 数据删除成功; mycmd.CommandText = mysql; mycmd.Connection = myconn; mycmd.ExecuteNonQuery(); myconn.Close(); catch (Exception Err) MessageBox.Show(Err.Message, 信息提示, MessageBoxButtons.OK, MessageBoxIcon.Error); myconn.Close(); 5 选课表系统操作与代码1) 模糊查询和精确查询代码：private void button1_Click(object sender, EventArgs e) string mystr, mysql; OleDbConnection myconn = new OleDbConnection(); OleDbCommand mycmd = new OleDbCommand(); mystr = Provider = Microsoft.Jet.OLEDB.4.0; + Data Source = G:数据库db1.mdb; myconn.ConnectionString = mystr; if (radioButton1.Checked) myconn.Open(); mysql = SELECT * FROM Courses WHERE CourseNo LIKE 21% ; mycmd.CommandText = mysql; mycmd.Connection = myconn; mycmd.Parameters.Add(no, OleDbType.VarChar, 10).Value = textBox1.Text + %; OleDbDataReader myreader = mycmd.ExecuteReader(); listBox1.Items.Add(课程号t 课程名t 课程类型t 课程数t 课时t 学分); listBox1.Items.Add(-); while (myreader.Read() listBox1.Items.Add(String.Format(0 t1 t2 t3 t4 t5, myreader0.ToString(), myreader1.ToString(), myreader2.ToString(), myreader3.ToString(), myreader4.ToString(), myreader5.ToString(); myconn.Close(); myreader.Close(); else if (radioButton2.Checked) myconn.Open(); mysql = SELECT * FROM Courses WHERE CourseNo=no ; mycmd.CommandText = mysql; mycmd.Connection = myconn; mycmd.Parameters.Add(no, OleDbType.VarChar, 10).Value = textBox1.Text; OleDbDataReader myreader = mycmd.ExecuteReader(); listBox1.Items.Add(课程号t 课程名t 课程类型t 课程数t 课时t 学分); listBox1.Items.Add(-); while (myreader.Read() listBox1.Items.Add(String.Format(0 t1 t2 t3 t4 t5, myreader0.ToString(), myreader1.ToString(), myreader2.ToString(), myreader3.ToString(), myreader4.ToString(), myreader5.ToString(); myconn.Close(); myreader.Close(); else MessageBox.Show(你没有选择查找方式, 信息提示, MessageBoxButtons.OK); 2) 修改代码： private void button2_Click(object sender, EventArgs e) string mystr, mysql; OleDbConnection myconn = new OleDbConnection(); OleDbCommand mycmd = new OleDbCommand(); mystr = Provider = Microsoft.Jet.OLEDB.4.0; + Data Source =G:数据库db1.mdb; myconn.ConnectionString = mystr; try myconn.Open(); mysql = update Courses set CourseHours= + textBox6.Text + ,Credit= + textBox7.Text + where CourseNo= + textBox2.Text + ; label8.Text = 数据修改成功; mycmd.CommandText = mysql; mycmd.Connection = myconn; mycmd.ExecuteNonQuery(); myconn.Close(); catch (Exception Err) MessageBox.Show(Err.Message, 信息提示, MessageBoxButtons.OK, MessageBoxIcon.Error); myconn.Close(); 3) 添加代码： private void button3_Click(object sender, EventArgs e) string mystr, mysql; OleDbConnection myconn = new OleDbConnection(); OleDbCommand mycmd = new OleDbCommand(); mystr = Provider = Microsoft.Jet.OLEDB.4.0; + Data Source =G:数据库db1.mdb; myconn.ConnectionString = mystr; try myconn.Open(); mysql = INSERT INTO Courses (CourseNo,CourseName,CourseType,OpenTerm,CourseHours,Credit) VALUES (No,Name,Type,Term,Hours,Credit) ; mycmd.Parameters.Add(No, OleDbType.Variant, 4).Value = Convert.ToInt32(textBox2.Text); mycmd.Parameters.Add(Name, OleDbType.VarChar, 50).Value = textBox3.Text; mycmd.Parameters.Add(Type, OleDbType.VarChar, 50).Value = textBox4.Text; mycmd.Parameters.Add(Term, OleDbType.VarChar, 50).Value = textBox5.Text; mycmd.Parameters.Add(Hours, OleDbType.VarChar, 50).Value = textBox6.Text; mycmd.Parameters.Add(Credit, OleDbType.VarChar, 50).Value = textBox7.Text; label9.Text = 数据添加成功; mycmd.CommandText = mysql; mycmd.Connection = myconn; mycmd.ExecuteNonQuery(); myconn.Close(); /Variant catch (Exception Err) MessageBox.Show(Err.Message, 信息提示, MessageBoxButtons.OK, MessageBoxIcon.Error); myconn.Close(); 4) 删除代码: private void button4_Click(object sender, EventArgs e) string mystr, mysql; OleDbConnection myconn = new OleDbConnection(); OleDbCommand mycmd = new OleDbCommand(); mystr = Provider = Microsoft.Jet.OLEDB.4.0; + Data Source = G:数据库db1.mdb; myconn.ConnectionString = mystr; DialogResult Re = MessageBox.Show(删除不可复原，继续删除？, 信息提示, MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question); if (Re = DialogResult.Yes) try myconn.Open(); mysql = DELETE FROM Courses WHERE CourseNo=no ; mycmd.Parameters.Add(no, OleDbType.VarChar, 10).Value = textBox2.Text; label10.Text = 数据删除成功; mycmd.CommandText = mysql; mycmd.Connection = myconn; mycmd.ExecuteNonQuery(); myconn.Close(); catch (Exception Err) MessageBox.Show(Err.Message, 信息提示, MessageBoxButtons.OK, MessageBoxIcon.Error); myconn.Close(); 6 成绩表系统操作与代码1) 模糊查询和精确查询代码： private void button1_Click(object sender, EventArgs e) string mystr, mysql; OleDbConnection myconn = new OleDbConnection(); OleDbCommand mycmd = new OleDbCommand(); mystr = Provider = Microsoft.Jet.OLEDB.4.0; + Data Source = G:数据库db1.mdb; myconn.ConnectionString = mystr; if (radioButton1.Checked) myconn.Open(); mysql = SELECT * FROM Score WHERE StudentNo LIKE 21% ; mycmd.CommandText = mysql; mycmd.Connection = myconn; mycmd.Parameters.Add(no, OleDbType.VarChar, 10).Value = textBox1.Text + %; OleDbDataReader myreader = mycmd.ExecuteReader(); listBox1.Items.Add(学号t 课程代号 t 学分t ); listBox1.Items.Add(-); while (myreader.Read() listBox1.Items.Add(String.Format(0t1 t2, myreader0.ToString(), myreader1.ToString(), myreader2.ToString(); myconn.Close(); myreader.Close(); else if (radioButton2.Checked) myconn.Open(); mysql = SELECT * FROM Score WHERE StudentNo=no ; mycmd.CommandText = mysql; mycmd.Connection = myconn; mycmd.Parameters.Add(no, OleDbType.VarChar, 10).Value = textBox1.Text; OleDbDataReader myreader = mycmd.ExecuteReader(); listBox1.Items.Add(学号t 课程代号 t 学分t); listBox1.Items.Add(-); while (myreader.Read() listBox1.Items.Add(String.Format(0t1 t2, myreader0.ToString(), myreader1.ToString(), myreader2.ToString(); myconn.Close(); myreader.Close(); else MessageBox.Show(你没有选择查找方式, 信息提示, MessageBoxButtons.OK); 2) 修改代码：private void button2_Click(object sender, EventArgs e) string