数据库设计实验报告.doc
一 实验目的：本次实验主要是使用ADO.NET，使用Command与dataReader与DataSet连续数据库，练习简单的统计查询方法，绑定DataGridView到数据源的方法，绑定其他控件到数据源的方法。二 数据库设计：实验设计一个成语查询系统。实验要求：实现用户登录，并对数据库的成语进行浏览、查询（精确和模糊）、添加、删除、修改等基本功能。1. 用户表（user1）结构及记录如图1.1。 2 在Access里创建数据表hyt2008。所用到的表hyt2008及其结构、记录如图1.2所示。三 软件界面：管理员登录功能由LogForm.cs窗体实现。管理员可以输入用户名和密码，单击"登录"按钮实现登录功能，单击"取消"按钮关闭窗口，如图1.3所示。2、主窗体介绍登录成功后进入主窗体界面，下面对主窗体进行设计。(1)设计界面：在空白窗体中添加菜单MenuStrip、标签、TextBox、DataGridView等控件，对控件的属性进行修改，如表11所示。 表11控件属性及属性值控件名称属性属性值MenuStripItems浏览（查看所有、保存结果、推出）、查询（精确查询、模糊查询）、添加、删除、刷新、推出Lable1Text 选择方式：GroopBox1Text操作界面Lable2Text设置值：Lable3Text拼音：Lable4Text成语：Lable5Text备注：Lable6Text显示界面Button1Text精确查询Button2Text模糊查询Button3Text添加Button4Text到处Word文档Button5Text修改Button6Text删除comboBox1Items拼音检索、汉字检索然后进行整体布局调整，窗体效果如图1.4所示。浏览查询菜单设计如图1.5所示。 四 功能代码：程序1: private void button1_Click(object sender, EventArgs e) String str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=oyy.mdb" OleDbConnection conn = new OleDbConnection(str); String sqlstr = "select count(*) from user1 where username='" + textBox1.Text + "'and password='" + textBox2.Text + "'" DataSet ds = new DataSet(); OleDbDataAdapter sqldata = new OleDbDataAdapter(sqlstr, conn); conn.Open(); int n = (int)sqldata.SelectCommand.ExecuteScalar(); conn.Close(); if (n = 0) MessageBox.Show("用户名和密码不正确"); else if (n = 1) Form f2 = new Form2(); f2.Show(); this.Hide(); private void button2_Click(object sender, EventArgs e) this.Close(); 程序2： private void 精确查询ToolStripMenuItem_Click(object sender, EventArgs e) if (comboBox1.Text = "") MessageBox.Show("请先选择查询方式！", "提示"); else String str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=oyy.mdb" OleDbConnection conn = new OleDbConnection(str); String sqlstr = " select * from hyt2008 where " + comboBox1.Text + "='" + textBox1.Text + "'" OleDbCommand cmd = new OleDbCommand(sqlstr, conn); OleDbDataAdapter da = new OleDbDataAdapter(sqlstr, conn); DataSet ds = new DataSet(); try conn.Open(); da.Fill(ds); catch (Exception) MessageBox.Show("查找失败"); finally conn.Close(); dataGridView1.DataSource = ds.Tables0.DefaultView; private void 模糊查询BToolStripMenuItem_Click(object sender, EventArgs e) if (comboBox1.Text = "") MessageBox.Show("请先选择查询方式！", "提示"); else String str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=oyy.mdb" OleDbConnection conn = new OleDbConnection(str); String sqlstr = "select * from hyt2008 where " + comboBox1.Text + " like '" + textBox1.Text + "%'" OleDbCommand cmd = new OleDbCommand(sqlstr, conn); OleDbDataAdapter da = new OleDbDataAdapter(sqlstr, conn); DataSet ds = new DataSet(); try conn.Open(); da.Fill(ds); catch (Exception) MessageBox.Show("查找失败"); finally conn.Close(); dataGridView1.DataSource = ds.Tables0.DefaultView; private void 添加ToolStripMenuItem_Click(object sender, EventArgs e) String str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=oyy.mdb" OleDbConnection con = new OleDbConnection(); con.ConnectionString = str; string cmdText = "insert into hyt2008 (拼音检索，汉字检索，备注)values('" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')" OleDbCommand cmd = new OleDbCommand(cmdText, con); try con.Open(); cmd.ExecuteNonQuery(); qingkong(); catch (Exception) MessageBox.Show("对不起！添加数据失败！", "提示"); finally con.Close(); shuaxin(); private void 修改ToolStripMenuItem_Click(object sender, EventArgs e) String str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=oyy.mdb" OleDbConnection conn = new OleDbConnection(str); if (comboBox1.Text = "") MessageBox.Show("请先输入修改的类型"); else String sqlstr = "UPDATE hyt2008 SET 备注 = '" + textBox4.Text + " 'where " + comboBox1.Text + " = '" + textBox1.Text + " '" OleDbCommand cmd = new OleDbCommand(sqlstr, conn); try conn.Open(); cmd.ExecuteNonQuery(); qingkong(); catch (Exception) MessageBox.Show("修改失败", "提示"); finally conn.Close(); shuaxin(); private void 删除ToolStripMenuItem_Click(object sender, EventArgs e) String str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=oyy.mdb" OleDbConnection conn = new OleDbConnection(str); if (comboBox1.Text = "") MessageBox.Show("请先输入删除的类型"); else String sqlstr = " DELETE FROM hyt2008 where " + comboBox1.Text + " = '" + textBox1.Text + " ' " OleDbCommand cmd = new OleDbCommand(sqlstr, conn); try conn.Open(); cmd.ExecuteNonQuery(); qingkong(); catch (Exception) MessageBox.Show("删除失败", "提示"); finally conn.Close(); shuaxin(); void qingkong() textBox4.Text = "" textBox2.Text = "" textBox3.Text = "" void shuaxin() String str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=oyy.mdb" OleDbConnection conn = new OleDbConnection(str); String sqlstr = " SELECT * FROM hyt2008" OleDbDataAdapter da = new OleDbDataAdapter(sqlstr, conn); DataSet ds = new DataSet(); try conn.Open(); da.Fill(ds); catch (Exception) MessageBox.Show("查找失败"); finally conn.Close(); dataGridView1.DataSource = ds.Tables0.DefaultView; private void 退出PToolStripMenuItem_Click(object sender, EventArgs e) Application.Exit(); private void 刷新ToolStripMenuItem_Click(object sender, EventArgs e) shuaxin(); private void button1_Click(object sender, EventArgs e) if (comboBox1.Text = "") MessageBox.Show("请先选择查询方式", "提示"); else String str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=oyy.mdb" OleDbConnection sqlConnection1 = new OleDbConnection(); sqlConnection1.ConnectionString = str; string sql = "select * from hyt2008 where " + comboBox1.Text + "='" + textBox1.Text + "'" OleDbCommand sqlCommand1 = new OleDbCommand(); sqlCommand1.CommandText = sql; sqlCommand1.Connection = sqlConnection1; OleDbDataAdapter da = new OleDbDataAdapter(sql, sqlConnection1); DataSet ds = new DataSet(); try sqlConnection1.Open(); da.Fill(ds); catch (Exception) MessageBox.Show("对不起！精确查找失败！", "提示"); finally sqlConnection1.Close(); dataGridView1.DataSource = ds.Tables0.DefaultView; private void button2_Click(object sender, EventArgs e) if (comboBox1.Text = "") MessageBox.Show("请先选择查询方式", "提示"); else String str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=XLW.mdb" OleDbConnection sqlConnection1 = new OleDbConnection(str); string sql = "select * from hyt2008 where " + comboBox1.Text + " like '%" + textBox1.Text + "%'" OleDbDataAdapter da = new OleDbDataAdapter(sql, sqlConnection1); DataSet ds = new DataSet(); try sqlConnection1.Open(); da.Fill(ds); catch (Exception) MessageBox.Show("对不起！模糊查找失败！", "提示"); finally sqlConnection1.Close(); dataGridView1.DataSource = ds.Tables0.DefaultView; 五实验结果1登录失败（1）精确查询（2）模糊查询五 实验小结：通过此次实验，.本人学会了怎么使用ADO.NET，使用Command与dataReader与DataSet连续数据库，练习简单的统计查询方法，同时学习了绑定DataGridView到数据源的方法，绑定其他控件到数据源的方法。我还学会了怎么用Access 建立数据库以及怎么将数据连接到程序中。本次实验较为复杂，代码很长，而且由于上课并没有掌握好相关知识，导致出现了很多问题，一直没有成功，但还是锻炼了自己的能力，也对课本知识更熟悉了。
