10913郭亚丽图书管理系统报告.docx

1、10913郭亚丽图书管理系统报告实验报告数据库开发技术实验报告题 目：图书管理系统开发姓名郭亚丽日期2012-4-27实验环境：Microsoft Visual Studio 2005 Microsoft SQL Server 2005实验内容与完成情况：第一个类：using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace library public class

2、 BookInfo private string titleID; private string titleName; private string author; private string isbn; private string type; private double price; private int pages; private string publisherID; private Publisher publisher; private string strConn; private SqlConnection cn; public string TitleID get r

3、eturn titleID; set titleID = value; public string Name get return titleName; set titleName = value; public string Author get return author; set author = value; public string Isbn get return isbn; set isbn = value; public string Type get return type; set type = value; public double Price get return p

4、rice; set price = value; public int Pages get return pages; set pages = value; public string PublisherID get return publisherID; set publisherID = value; public Publisher Publisher get if (publisher = null) publisher = new Publisher(); publisher.FindByPublisherID(this.publisherID); return publisher;

5、 public BookInfo() strConn = ConfigurationManager.ConnectionStringslibrary.Properties.Settings.图书馆管理系统ConnectionString.ConnectionString; cn = new SqlConnection(strConn); public BookInfo(string titleID, string name, string isbn, string author, string publisherID, string type, double price, int pages)

6、 this.titleID = titleID; this.titleName =name ; this.isbn = isbn; this.author = author; this.publisherID = publisherID; this.type = type; this.price = price ; this.pages = pages; public DataTable FindBook(string column, string condition) try SqlCommand selCmd = new SqlCommand(); selCmd.Connection =

7、cn; selCmd.CommandType = CommandType.StoredProcedure; selCmd.CommandText = p_bookStock; SqlParameter p1 = selCmd.Parameters.Add(column, SqlDbType.NVarChar, 20); SqlParameter p2=selCmd .Parameters .Add (value,SqlDbType .NVarChar ,50); p1 .Value =column ; p2 .Value =condition ; SqlDataAdapter da =new

8、SqlDataAdapter (selCmd ); DataTable bookList=new DataTable (); da .Fill (bookList ); return bookList ; catch (SqlException ex) return null ; public int FindByTitleID(string titleID) /try / cn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = SELECT *FROM BookInfo WHER

9、E titleID = + titleID + ; SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read() this.titleID = titleID; this.titleName = dr1.ToString(); this.isbn = dr2.ToString(); this.author = dr3.ToString(); this.publisherID = dr4.ToString(); this.type = dr5.ToString(); this.price = Convert.ToDouble(dr6.ToString

10、(); this.pages = Convert.ToInt32(dr7.ToString(); dr.Close(); cn.Close(); return 1; else dr.Close(); cn.Close(); return 0; / /catch (SqlException ex) / / cn.Close(); / return 0; / public bool Insert() try cn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = INSERT INTO

11、 bookInfo VALUES (tITLEid,bookName,isbn,author ,publisherID,type,price,pages); cmd.Parameters.Add(titleID, SqlDbType.NVarChar, 10); cmd.Parameters.Add(bookName, SqlDbType.NVarChar, 50); cmd.Parameters.Add(isbn , SqlDbType.Char, 13); cmd.Parameters.Add(author , SqlDbType.NVarChar, 20); cmd.Parameters

12、.Add(publisherID , SqlDbType.Char, 4); cmd.Parameters.Add(type , SqlDbType.NVarChar, 10); cmd.Parameters.Add(price, SqlDbType.Decimal, 10); cmd.Parameters.Add(pages , SqlDbType.Int); cmd.Parameters0.Value = titleID; cmd.Parameters1.Value = titleName; cmd.Parameters2.Value = isbn; cmd.Parameters3.Val

13、ue = author; cmd.Parameters4.Value = publisherID; cmd.Parameters5.Value = type; cmd.Parameters6.Value = price; cmd.Parameters7.Value = pages; int result = cmd.ExecuteNonQuery(); cn.Close(); return (result = 1); catch (SqlException ex) cn.Close(); return false; public bool Remove() try BookItem books

14、 = new BookItem(); books.RemoveByTitleID(this.titleID); cn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = DELETE FROM bookInfo WHERE titleID= + this.titleID + ; int result = cmd.ExecuteNonQuery(); cn.Close(); return (result =1); catch (SqlException ex) cn.Close();

15、return false; public bool Updata(string titleID, string name, string isbn, string author, string publisherID, string type, double price, int pages) try cn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd .CommandText =UPDATE bookInfo SET titleID=titleID,titleName=bookName, isbn=is

16、bn,author=author,publisherID=publisherID,type=type,price=price,pages=pages WHERE titleID= +this.titleID +; cmd.Parameters.Add(titleID, SqlDbType.NVarChar, 10); cmd.Parameters.Add(bookName, SqlDbType.NVarChar, 50); cmd.Parameters.Add(isbn , SqlDbType.Char, 13); cmd.Parameters.Add(author , SqlDbType.N

17、VarChar, 20); cmd.Parameters.Add(publisherID , SqlDbType.Char, 4); cmd.Parameters.Add(type , SqlDbType.NVarChar, 10); cmd.Parameters.Add(price, SqlDbType.Decimal, 10); cmd.Parameters.Add(pages , SqlDbType.Int); cmd.Parameters0.Value = titleID; cmd.Parameters1.Value = titleName; cmd.Parameters2.Value

18、 = isbn; cmd.Parameters3.Value = author; cmd.Parameters4.Value = publisherID; cmd.Parameters5.Value = type; cmd.Parameters6.Value = price; cmd.Parameters7.Value = pages; int result = cmd.ExecuteNonQuery(); cn.Close(); return (result = 1); catch (SqlException ex) cn.Close(); return false; 第二个类：using

19、System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace library class BookItem private string bookID; /图书条码 private string state; /图书状态 private string titleID; /图书对应的书籍编号 private BookInfo info; /图书信息 private string

20、strConn; /数据库连接串 private SqlConnection cn; /数据库连接对象 public string BookID get return bookID; set bookID = value; public string State get return state; set state = value; public string TitleID get return titleID; set titleID = value; public BookInfo Info get if (info = null) info = new BookInfo(); inf

21、o.FindByTitleID(this.titleID); return info; public BookItem() strConn = ConfigurationManager.ConnectionStringslibrary.Properties.Settings.图书馆管理系统ConnectionString.ConnectionString; cn = new SqlConnection(strConn); /构造函数 public BookItem(string bookID, string titleID) this.bookID = bookID; this.state =

22、 馆内; this.titleID = titleID; strConn = ConfigurationManager.ConnectionStringslibrary.Properties.Settings.图书馆管理系统ConnectionString.ConnectionString; cn = new SqlConnection(strConn); public int FindByBookID(string bookID) try cn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.Comman

23、dText = SELECT * FROM bookItem WHERE bookID= + bookID + ; SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read() this.bookID = bookID; this.state = dr1.ToString(); this.titleID = dr2.ToString(); dr.Close(); cn.Close(); return 1; else dr.Close(); cn.Close(); return 0; catch (SqlException ex) cn.Close(

24、); return 0; public DataTable FindByTitleID(string titleID) string strSql = SELECT bookID, state FROM bookItem WHERE titleID= + titleID + ; try SqlDataAdapter da = new SqlDataAdapter(strSql ,strConn ); DataTable itemList = new DataTable (); da.Fill (itemList); return (itemList); catch (SqlException

25、ex) return null; /借出，修改数据库状态字段为“借出” public int Lend() try cn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = UPDATA bookItem SET state= 借出 WHERE bookID = + bookID + ; int result = cmd.ExecuteNonQuery(); return result; cn.Close(); catch (SqlException ex) return 0; /归

26、还，修改数据库状态字段为“馆内” public bool Back() try cn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = UPDATE bookItem SET state=馆内 WHERE bookID= + bookID + ; int result = cmd.ExecuteNonQuery(); return(result=1); catch (SqlException ex) cn.Close(); return false ; /当前图书添加到数据库 pu

27、blic bool Insert() try cn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = INSERT INTO bookItem VALUES( + bookID + , 馆内, + titleID + ); int result = cmd.ExecuteNonQuery(); cn.Close(); return(result=1); catch (SqlException ex) cn.Close(); return false; /从数据库删除该图书 publ

28、ic bool Remove() try cn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = DELETE FROM bookItem WHERE bookID= + bookID + ; int result = cmd.ExecuteNonQuery(); return(result=1); catch (SqlException ex) cn.Close(); return false; /按照图书编号titleID删除所有馆藏图书 public int RemoveByTitleID(string titleID) try cn.Open(); SqlCommand cmd = n