数据库课程设计报告.doc

数据库课程设计报告专业名称： 计算机0701 班级学号： 3070602024学生姓名： 吴天俣指导教师：王新胜设计时间： 2009年 6月25日2009 年 7月3日E-R图界面设计登录界面登录采用用户名和密码认证，输入用户名和密码，与数据库中用户表中的数据相比较，根据应有的权限和选择的权限进入不同的界面。登录时首先比较用户名，若没有，则提示无此用户。再比较密码，若不同则提示密码错误。最后比较权限，若超出权限，则给出提示，若都符合则进入所选择的界面。源代码：using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using System.Data.SqlClient;namespace mySQL public partial class Form1 : Form SqlConnection dataConnection;/数据库连接类 SqlCommand dataCommand;/数据库命令类 SqlDataReader dataReader;/数据库读出类 String uID;/存放用户名的String类 String uPassD;/存放密码的String类 /构造函数 public Form1() InitializeComponent(); /关闭窗口 private void button2_Click(object sender, EventArgs e) dataConnection.Close(); this.Dispose(); /载入窗口时连接数据库，进行初始化 private void Form1_Load(object sender, EventArgs e) /连接数据库 try dataConnection = new SqlConnection(); dataConnection.ConnectionString = Integrated Security=true;Initial Catalog=mySQL; + Data Source=wty-PC; /dataConnection.Open(); /捕获异常 catch (SqlException ee) MessageBox.Show(ee.Message); /数据库类之间的相互绑定 finally dataCommand = new SqlCommand(); dataCommand.Connection = dataConnection; /进行身份验证，并登录 private void button1_Click(object sender, EventArgs e) uID =textBox1.Text; uPassD =textBox2.Text; dataConnection.Close(); dataConnection.Open(); if (uID = ) MessageBox.Show(请输入用户名); else if(uID!=) dataCommand.CommandText = (SELECT * FROM 用户 WHERE 用户名= + uID + ); dataReader = dataCommand.ExecuteReader(); if (!dataReader.Read() MessageBox.Show(没有此用户！); textBox1.Text = ; textBox2.Text = null; dataConnection.Close(); else if (uPassD != dataReader.GetString(1) MessageBox.Show(密码错误，请重新输入！); textBox2.Text = ; dataConnection.Close(); else if (radioButton2.Checked) if (dataReader.GetString(2) = 读者) MessageBox.Show(你没有管理员权限！); dataReader.Close(); dataConnection.Close(); else if (dataReader.GetString(2) = 管理员) dataReader.Close(); dataConnection.Close(); Form3 frm3 = new Form3(dataConnection, dataCommand, dataReader, uID); this.Hide(); frm3.ShowDialog(); else if (radioButton1.Checked) dataReader.Close(); dataConnection.Close(); Form2 frm2 = new Form2(dataConnection, dataCommand, dataReader, uID); this.Hide(); frm2.ShowDialog(); /按下ENTER键时，响应验证 private void textBox2_KeyDown(object sender, KeyEventArgs e) if (int)e.KeyCode = 13) this.button1_Click(sender, e); 读者界面读者信息，显示你当前的一些信息，包括可借数量和预约数量等，并包括修改密码功能。查询，可以进行图书的查询，包括模糊查询和精确查询。通过radiobuttun来判断。模糊查询不支持ISBN的查询。选中查询结果的行头，自动填充到借阅/预约界面借阅/预约，这里的textbox都设置为readonly，主要是为了防止出现图书馆中没有的书进行借阅，这样可以节省查询的时间，并且通过查询界面可以很方便的填入这两项信息。借阅和预约分别是计入相应的表内。并可以查询归还界面，同样，textbox设为readonly，通过查询借书或预约情况，选中行头来填入信息。归还或取消预约好删除相应元组。注销界面注销是返回登录界面重新登陆退出是退出程序。源代码：using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using System.Data.SqlClient;namespace mySQL public partial class Form2 : Form SqlConnection dataConnection;/数据库连接类 SqlCommand dataCommand;/数据库命令类 /SqlDataReader dataReader;/数据库读出类 String uID;/存放用户名的String类 Int32 ynum=0; Int32 lnum=0;/在馆数量 Int32 maxnum = 0; Int32 dnum = 0; Int32 ymaxnum = 0; Int32 ydnum = 0; bool bflag = true; bool yflag = false; /String bISBN;/存放ISBN编号 /String bName;/存放书名 /Int32 bNum;/图书数量 /Int32 lNum;/在馆数量 /构造函数 public Form2() InitializeComponent(); /传递参数的构造函数 public Form2(SqlConnection conn, SqlCommand comm, SqlDataReader read, String ID) dataConnection = new SqlConnection (); dataConnection = conn; dataCommand = new SqlCommand(); dataCommand = comm; /dataReader = read; uID = ID; InitializeComponent(); /注销，返回登录界面 private void button1_Click(object sender, EventArgs e) Form1 frm1 = new Form1(); this.Hide(); frm1.ShowDialog(); /退出系统 private void button2_Click(object sender, EventArgs e) this.Dispose(); /清空所有textBox中的内容 private void button4_Click(object sender, EventArgs e) this.textBox1.Text = ; this.textBox2.Text = ; this.dataGridView1.DataSource = null; /查询图书 private void button3_Click(object sender, EventArgs e) dataConnection.Open(); String bookname=; String bookisbn=; String bmark = ; if(textBox1.Text!=) bookisbn = (ISBN编号= + textBox1.Text + ); if (textBox2.Text != ) & (textBox1.Text!=) bookname = (AND 书名= + textBox2.Text + ); else if(textBox2.Text !=) bookname = (书名= + textBox2.Text + ); bmark = bookname + bookisbn; if (radioButton2.Checked) /添加精确查询的代码 if(bmark!=) dataCommand.CommandText = SELECT * FROM book WHERE + bmark; else if(bmark=) MessageBox.Show(请输入查询数据！); else if (radioButton1.Checked) if (textBox1.Text != ) MessageBox.Show(不支持ISBN的模糊查询！); else /添加模糊查询的代码 if (textBox2.Text != ) bookname = (书名like % + textBox2.Text + %); dataCommand.CommandText = SELECT * FROM book WHERE + bookname; else/查询所有图书 dataCommand.CommandText = SELECT * FROM book; else MessageBox.Show(请选择查询方式！); SqlDataReader dataReader = dataCommand.ExecuteReader(); BindingSource bs = new BindingSource(); bs.DataSource = dataReader; this.dataGridView1.DataSource = bs; if (dataReader.Read() lnum = dataReader.GetInt32(3); ynum = dataReader.GetInt32(6); dataConnection.Close(); /借书 private void button5_Click(object sender, EventArgs e) dataConnection.Open(); String bookname = ; String bookisbn = ; dataCommand.CommandText = SELECT * FROM borrow WHERE 读者号= + uID + ; SqlDataReader tempReader = dataCommand.ExecuteReader(); if (textBox3.Text != ) & (textBox4.Text != ) bookisbn = textBox3.Text; bookname = textBox4.Text; bool flag = true; if (tempReader.Read() if (bookisbn = tempReader.GetString(1) MessageBox.Show(你已经借了此书了！); flag = false; tempReader.Close(); else if(flag =true) tempReader.Close(); if (lnum=0) MessageBox.Show(在馆数量为零，不可借阅); else if(maxnum=dnum) MessageBox.Show(你的借书数量已满，无法再借！); else dataCommand.CommandText = SELECT * FROM border WHERE 读者号= + uID + ; tempReader = dataCommand.ExecuteReader(); if (tempReader.Read() if (bookisbn = tempReader.GetString(1) MessageBox.Show(你已经预约此书了，该操作将会删除你的预约记录！); tempReader.Close(); dataCommand.CommandText = DELETE FROM border WHERE ISBN编号= + bookisbn + ; dataCommand.ExecuteNonQuery(); tempReader.Close(); dataCommand.CommandText = INSERT INTO borrow(读者号,ISBN编号) VALUES ( + uID + , + bookisbn + ); dataCommand.ExecuteNonQuery(); dataCommand.CommandText = UPDATE book SET 在馆数量=在馆数量-1 WHERE ISBN编号= + bookisbn + ; dataCommand.ExecuteNonQuery(); dataCommand.CommandText = UPDATE book SET 可预约数量=可预约数量+1 WHERE ISBN编号= + bookisbn + ; dataCommand.ExecuteNonQuery(); dataCommand.CommandText = UPDATE reader SET 已借数量=已借数量+1 WHERE 读者号= + uID + ; dataCommand.ExecuteNonQuery(); MessageBox.Show(借阅成功!); dnum+; textBox3.Text = ; textBox4.Text = ; /tempreader_1.Close(); tempReader.Close(); dataCommand.CommandText = SELECT * FROM BORROW WHERE 读者号=+uID+; SqlDataReader dataReader = dataCommand.ExecuteReader(); BindingSource bs = new BindingSource(); bs.DataSource = dataReader; this.dataGridView2.DataSource = bs; dataConnection.Close(); /将所选图书信息传递至借书模块 private void dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e) textBox3.Text = dataGridView1.RowsdataGridView1.CurrentRow.Index.CellsISBN编号.Value.ToString(); textBox4.Text = dataGridView1.RowsdataGridView1.CurrentRow.Index.Cells书名.Value.ToString(); lnum = (int)dataGridView1.RowsdataGridView1.CurrentRow.Index.Cells在馆数量.Value; ynum = (int)dataGridView1.RowsdataGridView1.CurrentRow.Index.Cells可预约数量.Value; /查询借书情况 private void button7_Click(object sender, EventArgs e) dataConnection.Open(); dataCommand.CommandText = SELECT * FROM BORROW WHERE 读者号= + uID + ; SqlDataReader dataReader = dataCommand.ExecuteReader(); BindingSource bs = new BindingSource(); bs.DataSource = dataReader; this.dataGridView2.DataSource = bs; dataConnection.Close(); /查询借书情况 private void button8_Click(object sender, EventArgs e) dataConnection.Open(); dataCommand.CommandText = SELECT * FROM BORROW WHERE 读者号= + uID + ; SqlDataReader dataReader = dataCommand.ExecuteReader(); BindingSource bs = new BindingSource(); bs.DataSource = dataReader; this.dataGridView3.DataSource = bs; dataConnection.Close(); bflag = true; yflag = false; /传递还书信息 private void dataGridView3_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e) if(bflag=true&yflag=false) textBox5.Text = dataGridView3.RowsdataGridView3.CurrentRow.Index.CellsISBN编号.Value.ToString(); else if(yflag=true&bflag=false) textBox8.Text = dataGridView3.RowsdataGridView3.CurrentRow.Index.CellsISBN编号.Value.ToString(); /还书 private void button9_Click(object sender, EventArgs e) dataConnection.Open(); String bookisbn; bookisbn = textBox5.Text; if (bookisbn != ) dataCommand.CommandText = DELETE FROM borrow WHERE ISBN编号= + bookisbn + ; dataCommand.ExecuteNonQuery(); dataCommand.CommandText = UPDATE book SET 在馆数量=在馆数量+1 WHERE ISBN编号= + bookisbn + ; dataCommand.ExecuteNonQuery(); dataCommand.CommandText = UPDATE book SET 可预约数量=可预约数量-1 WHERE ISBN编号= + bookisbn + ; dataCommand.ExecuteNonQuery(); dataCommand.CommandText = UPDATE reader SET 已借数量=已借数量-1 WHERE 读者号= + uID + ; dataCommand.ExecuteNonQuery(); textBox5.Text = ; MessageBox.Show(归还成功!); dnum-; dataConnection.Close(); /预约 private void button6_Click(object sender, EventArgs e) dataConnection.Open(); String bookname = ; String bookisbn = ; dataCommand.CommandText = SELECT * FROM BORROW WHERE 读者号= + uID + ; SqlDataReader tempReader = dataCommand.ExecuteReader(); if (textBox3.Text != ) & (textBox4.Text != ) bookisbn = textBox3.Text; bookname = textBox4.Text; bool flag = true; if (tempReader.Read() if (bookisbn = tempReader.GetString(1) MessageBox.Show(你已经借了此书了！); flag = false; tempReader.Close(); if (flag = true) tempReader.Close(); dataCommand.CommandText = SELECT * FROM border WHERE 读者号= + uID + ; tempReader = dataCommand.ExecuteReader(); if (tempReader.Read() if (bookisbn = tempReader.GetString(1) MessageBox.Show(你已经预约了此书了); flag = false; else if (flag = true) tempReader.Close(); if (lnum != 0) MessageBox.Show(该书可借，无需预约); else if (ynum = 0) MessageBox.Show(预约数量为零，不可预约); else if(ymaxnum=ydnum) MessageBox.Show(你的预约数量已达上限，不可预约); else dataCommand.CommandText = INSERT INTO border(读者号,ISBN编号) VALUES ( + uID + , + bookisbn + ); dataCommand.ExecuteNonQuery(); dataCommand.CommandText = UPDATE book SET 可预约数量=可预约数量-1 WHERE ISBN编号= + bookisbn + ; dataCommand.ExecuteNonQuery(); dataCommand.CommandText = UPDATE reader SET 已预约数量=已预约数量+1 WHERE 读者号= + uID + ; dataCommand.ExecuteNonQuery(); MessageBox.Show(