數(shù)據(jù)庫(kù)課程設(shè)計(jì)示例

1、圖書管理系統(tǒng)1. 數(shù)據(jù)庫(kù)和表結(jié)構(gòu)系統(tǒng)數(shù)據(jù)庫(kù)為XSBOOK，該數(shù)據(jù)庫(kù)由學(xué)生（XS）、圖書（BOOK）、借閱（JY）表構(gòu)成，各表的主要結(jié)構(gòu)如表1.1表1.3所示。表1.1 XS表結(jié)構(gòu)字段名類型與寬度是否主碼是否允許空值說明借書證號(hào)char(8)×姓名char(8)××專業(yè)char(12)××性別bit××0：男，1：女出生時(shí)間date××借書量int××默認(rèn)值為0照片Image×表1.2 BOOK表結(jié)構(gòu)字段名類型與寬度是否主碼是否允許空值說明ISBNchar(16)×書

2、名char(26)××作者char(8)××出版社char(20)××價(jià)格float××復(fù)本量int××當(dāng)對(duì)書進(jìn)行插入、刪除時(shí)，復(fù)本量應(yīng)等于庫(kù)存量庫(kù)存量int××表1.3 JY表結(jié)構(gòu)字段名類型與寬度是否主碼是否允許空值說明借書證號(hào)char(8)×× 當(dāng)借一本書時(shí)，BOOK的庫(kù)存量應(yīng)減1，同時(shí)，借書人的借書量應(yīng)加1；當(dāng)還一本書時(shí)，BOOK的庫(kù)存量應(yīng)加1，同時(shí)，借書人的借書量應(yīng)減1，同時(shí)在JYLS表中插入一條記錄。ISBNchar(16)××

3、;索書號(hào)char(10)×借書時(shí)間date××2.根據(jù)功能設(shè)計(jì)的存儲(chǔ)過程與觸發(fā)器-對(duì)XS表操作的存儲(chǔ)過程2.1對(duì)XS表進(jìn)行操作的存儲(chǔ)過程（1）在XS表插入一條記錄use xsbooksif exists(select name from sysobjectswhere name='XS_insert' and type='p')drop proc XS_insertgocreate proc XS_insertjszh char(8),xm char(8),zym char(12),xb bit,cssj datetime,jsl

4、intASinsert into XS(借書證號(hào),姓名,專業(yè),性別,出生時(shí)間,借書量)values(jszh ,xm ,zym ,xb ,cssj ,jsl)GO-測(cè)試exec xs_insert '10000008','ddd','asdf','0','1984-8-6',3（2）修改XS表的記錄use xsbooksif exists(select name from sysobjectswhere name='xs_update' and type='p')drop proc

5、xs_updategocreate proc xs_updatejszh char(8),xm char(8),zym char(12),xb bit,cssj datetime,jsl intASupdate xs set 姓名=xm,專業(yè)=zym,性別=xb,出生時(shí)間=cssj,借書量=jslwhere 借書證號(hào)=jszhgo-測(cè)試exec xs_update '10000008','zs','gis',1,'1999-1-2',3（3）在XS表中刪除一條記錄，只有沒有借書才能刪除該記錄use xsbooksif exists

6、(select name from sysobjectswhere name='XS_delete' and type='p')drop proc XS_deletegocreate proc XS_deletejszh char(8),flag int outputAsif exists(select * from xs where 借書證號(hào)=jszh and 借書量=0)begindelete from xswhere 借書證號(hào)=jszhset flag=0endelsebeginprint '刪除'+jszh+'不成功！'s

7、et flag=1endgo-測(cè)試declare flag intexec XS_delete '10000008',flag outputselect flag2.2對(duì)BOOK進(jìn)行操作（3）在BOOK表中插入記錄use xsbooksif exists(select name from sysobjectswhere name='book_insert' and type='p')drop proc book_insertgocreate proc book_insert isbn char(16),sm char(26),zz char(8)

8、,cbx char(20),jg float,fbl int ,kcl intASinsert into book(ISBN,書名,作者,出版社,價(jià)格,復(fù)本量,庫(kù)存量)values(isbn,sm,zz,cbx,jg,fbl ,kcl)goexec book_insert '464765','6357545665','sdf','sasdfa',50,9,8（2）修改BOOK表的記錄use xsbooksif exists(select name from sysobjectswhere name='book_update

9、' and type='p')drop proc book_updategocreate proc book_update isbn char(16),sm char(26),zz char(8),cbx char(20),jg float,fbl int ,kcl intASupdate book set ISBN=isbn,書名=sm,作者=zz,出版社=cbx,價(jià)格=jg,復(fù)本量=fbl,庫(kù)存量=kclWHERE ISBN=ISBNgoexec book_update '464765','333333333','sdf

10、9;,'sasdfa',50,9,8（3）刪除BOOK表的記錄，書要全部還回才能刪除該書use xsbooksif exists(select name from sysobjectswhere name='book_delete' and type='p')drop proc book_deletegocreate proc book_delete ISBN char(16),flag int outputASif exists(select * from bookwhere isbn=isbn and 復(fù)本量=庫(kù)存量)begindelete

11、from book where isbn=isbnset flag=0endelseset flag=1godeclare flag intexec book_delete '464765',flag outputselect flag 2.3存儲(chǔ)過程實(shí)現(xiàn)P364借閱功能CREATE PROCEDURE Book_Borrow in_ReaderID char(8), in_ISBN char(18), in_BookID char(10), out_str char(30) OUTPUTASBEGINIF NOT EXISTS(SELECT * FROM XS WHERE 借

12、書證號(hào)=in_ReaderID)BEGINSET out_str= '該讀者不存在'RETURN 0ENDIF NOT EXISTS(SELECT * FROM BOOK WHERE ISBN=in_ISBN)BEGINSET out_str= '該圖書不存在'RETURN 0ENDIF (SELECT 借書量 FROM XS WHERE 借書證號(hào)=in_ReaderID)=5BEGINSET out_str= '讀者借書量不能大于'RETURN 0ENDIF (SELECT 庫(kù)存量 FROM BOOK WHERE ISBN=in_ISBN)=0

13、BEGINSET out_str= '圖書庫(kù)存量為'RETURN 0ENDIF in_ISBN IN (SELECT ISBN FROM JY WHERE 借書證號(hào)=in_ReaderID)BEGINSET out_str= '讀者已經(jīng)借過該書'RETURN 0ENDIF EXISTS(SELECT * FROM JY WHERE 索書號(hào)=in_BookID)BEGINSET out_str= '索書號(hào)已存在'RETURN 0ENDBEGIN TRAN/*開始一個(gè)事務(wù)*/INSERT INTO JY VALUES(in_ReaderID, in_

14、ISBN, in_BookID, GETDATE()IF ERROR>0/*如果前面一條SQL語(yǔ)句出錯(cuò)則回滾事務(wù)并返回*/BEGINROLLBACK TRANSET out_str= '執(zhí)行過程中遇到錯(cuò)誤'RETURN 0ENDUPDATE XS SET 借書量=借書量+1 WHERE 借書證號(hào)=in_ReaderIDIF ERROR>0/*如果前面一條SQL語(yǔ)句出錯(cuò)則回滾事務(wù)并返回*/BEGINROLLBACK TRANSET out_str= '執(zhí)行過程中遇到錯(cuò)誤'RETURN 0ENDUPDATE BOOK SET 庫(kù)存量=庫(kù)存量-1 WHER

15、E ISBN=in_ISBNIF ERROR=0/*如果所有語(yǔ)句都不出錯(cuò)則結(jié)束事務(wù)并返回*/BEGINCOMMIT TRANSET out_str= '借書成功'RETURN 1ENDELSE /*如果執(zhí)行出錯(cuò)則回滾所有操作并返回*/BEGINROLLBACK TRANSET out_str= '執(zhí)行過程中遇到錯(cuò)誤'RETURN 0ENDEND-測(cè)試declare out_str char(30)declare con intselect 借書證號(hào),xs.借書量 from xs where 借書證號(hào)='10000001'select ISBN,b

16、ook.庫(kù)存量 from book where ISBN='7-302-03035-9'exec con=Book_Borrow '10000001','7-302-03035-9','5100000005',out_str outputselect out_str as 執(zhí)行情況select con as 返回情況select 借書證號(hào),xs.借書量 from xs where 借書證號(hào)='10000001'select ISBN,book.庫(kù)存量 from book where ISBN='7-302-

17、03035-9'2.4利用觸發(fā)器實(shí)現(xiàn)還書功能create trigger JY_delete on jy after delete as beginupdate xs set xs.借書量=借書量-1 where 借書證號(hào)=(select 借書證號(hào) from deleted)update book set book.庫(kù)存量=庫(kù)存量+1 where ISBN=(select ISBN from deleted)end-測(cè)試select 借書證號(hào),xs.借書量 from xs where 借書證號(hào)='10000001'select ISBN,book.庫(kù)存量 from bo

18、ok where ISBN='7-302-03035-9'delete from jy where jy.索書號(hào)='5100000005'select 借書證號(hào),xs.借書量 from xs where 借書證號(hào)='10000001'select ISBN,book.庫(kù)存量 from book where ISBN='7-302-03035-9'3.圖書管理系統(tǒng)C實(shí)現(xiàn)3.1公共類為提高代碼效率與增強(qiáng)程序的移植性，建立公共類，主要包括連接數(shù)據(jù)庫(kù)與獲取查詢后生成的數(shù)據(jù)集。代碼如下：namespace ClassDbAccess /連接

19、數(shù)據(jù)庫(kù)public class Dbconnection public SqlConnection Dblink() string connectionString = "Server=XP-201209181056; database=XSBookS;Integrated Security=True" SqlConnection myConnection = new SqlConnection(connectionString); /SqlCommand command = new SqlCommand(connectionString); return myConnec

20、tion; / 執(zhí)行select語(yǔ)句，返回?cái)?shù)據(jù)集 public DataSet ExeSelect(string sql) try DataSet ds = new DataSet(); SqlConnection myConnection = Dblink(); myConnection.Open(); SqlCommand myCommand = new SqlCommand(sql, myConnection); myCommand.CommandText = sql; SqlDataAdapter adapter = new SqlDataAdapter(); adapter.Sele

21、ctCommand = myCommand; int i = adapter.Fill(ds); if (i = 0) return null; else return ds; catch return null; 3.2主界面3.3讀者管理 private void frmReader_Load(object sender, EventArgs e) /利用公共鏈接訪問數(shù)據(jù)庫(kù) Dbconnection db = new Dbconnection(); SqlConnection conn = db.Dblink(); conn.Open(); SqlCommand cmd = new Sql

22、Command("select * from xs", conn); /與dataGridView1綁定，如此綁定即使遇到移植或更改數(shù)據(jù)庫(kù)、機(jī)器名此處也不受影響 string sql = "select * from xs" SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = new SqlCommand(sql, conn); DataSet ds = new DataSet(); da.Fill(ds); this.dataGridView1.DataSource = ds.Tab

23、les0.DefaultView; System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(); DateTime dt; /只顯示第一條記錄信息 reader.Read(); this.textBoxJszh.Text = (String)reader0; this.textBoxName.Text = (String)reader1; boBoxSpec.Text = (String)reader2; if (bool)reader3 = false) this.radioButtonMan.Checked = true

24、; else this.radioButtonWoman.Checked = true; dt = (DateTime)(reader4); this.textBoxBirth.Text = dt.ToLongDateString().ToString(); this.textBoxJsl.Text = reader5.ToString(); /將讀取到的照片放入圖片框中 byte imagebytes = null; /是否獲取到照片應(yīng)當(dāng)使用如下判斷語(yǔ)句 if (reader6 != DBNull.Value) imagebytes = (byte)reader6; MemoryStream

25、 ms = new MemoryStream(imagebytes); Bitmap bmpt = new Bitmap(ms); pictureBox1.Image = bmpt; conn.Close(); /通過輸入借書證號(hào)或姓名查詢讀者信息 private void button_Query_Click(object sender, EventArgs e) /利用公共鏈接訪問數(shù)據(jù)庫(kù) Dbconnection db = new Dbconnection(); SqlConnection conn = db.Dblink(); conn.Open(); string strJszh, s

26、trName, sql; strJszh = this.textBoxJszh.Text.Trim(); strName = this.textBoxName.Text.Trim(); sql = "select * from xs where 借¨¨書º¨¦證¡è號(hào)?='" + strJszh + "' or 姓?名?='" + strName + "'" SqlCommand cmd = new SqlCommand(sql, c

27、onn); /通過調(diào)用自定義公共函數(shù)獲取數(shù)據(jù)集 DataSet ds = db.ExeSelect(sql); this.dataGridView1.DataSource = ds.Tables0.DefaultView; conn.Close(); /以下是通過SQL中的inserte子句來(lái)插入讀者信息 private void button_Add_Click(object sender, EventArgs e) /利用公共鏈接訪問數(shù)據(jù)庫(kù) Dbconnection db = new Dbconnection(); SqlConnection conn = db.Dblink(); con

28、n.Open(); /獲取所有框的值 string strJszh, strName, strSpec, strBirth; int iJsl; int sex = 1; DateTime dt; strJszh = this.textBoxJszh.Text; strName = this.textBoxName.Text; strSpec = boBoxSpec.Text; if (this.radioButtonMan.Checked = true) sex = 0; strBirth = this.textBoxBirth.Text; dt = Convert.ToDateTime(s

29、trBirth); iJsl = Convert.ToInt32(this.textBoxJsl.Text); string sql; sql = "insert into xs values('" + strJszh + "','" + strName + "','" + strSpec + "'," + sex + ",'" + dt + "'," + 0 + ",null)" SqlCo

30、mmand cmd = new SqlCommand(sql, conn); int flag = cmd.ExecuteNonQuery(); /該方法執(zhí)行成功后會(huì)返回一個(gè)非0整數(shù) if (flag > 0) MessageBox.Show("添加讀者成功！"); else conn.Close(); return; /更新DataGridView sql = "select * from xs" /通過DataAdapter方法填充數(shù)據(jù)集 SqlDataAdapter da = new SqlDataAdapter(); da.SelectCo

31、mmand = new SqlCommand(sql, conn); DataSet ds = new DataSet(); da.Fill(ds); this.dataGridView1.DataSource = ds.Tables0.DefaultView; conn.Close(); private void button_Update_Click(object sender, EventArgs e) /利用公共鏈接訪問數(shù)據(jù)庫(kù) Dbconnection db = new Dbconnection(); SqlConnection conn = db.Dblink(); conn.Ope

32、n(); /獲取所有框的值 string strJszh, strName, strSpec, strBirth; int iJsl; int sex = 1; DateTime dt; strJszh = this.textBoxJszh.Text.Trim(); strName = this.textBoxName.Text.Trim(); strSpec = boBoxSpec.Text.Trim(); if (this.radioButtonMan.Checked = true) sex = 0; strBirth = this.textBoxBirth.Text.Trim(); dt

33、 = Convert.ToDateTime(strBirth); iJsl = Convert.ToInt32(this.textBoxJsl.Text.Trim(); /利用SQL中已有的存儲(chǔ)過程實(shí)現(xiàn)更新 SqlCommand cmd = new SqlCommand("xs_update", conn); cmd.CommandType = CommandType.StoredProcedure; /創(chuàng)建參數(shù) SqlParameter paraJszh = new SqlParameter("jszh", SqlDbType.Char, 8); pa

34、raJszh.Direction = ParameterDirection.Input; paraJszh.Value = strJszh; cmd.Parameters.Add(paraJszh); SqlParameter paraName = new SqlParameter("xm", SqlDbType.Char, 8); paraName.Direction = ParameterDirection.Input; paraName.Value = strName; cmd.Parameters.Add(paraName); SqlParameter paraSp

35、ec = new SqlParameter("zym", SqlDbType.Char, 12); paraSpec.Direction = ParameterDirection.Input; paraSpec.Value = strSpec; cmd.Parameters.Add(paraSpec); SqlParameter paraSex = new SqlParameter("xb", SqlDbType.Bit); paraSex.Direction = ParameterDirection.Input; if (sex = 1) paraSe

36、x.Value = true; else paraSex.Value = false; cmd.Parameters.Add(paraSex); SqlParameter paraBirth = new SqlParameter("cssj", SqlDbType.DateTime); paraBirth.Direction = ParameterDirection.Input; paraBirth.Value = dt; cmd.Parameters.Add(paraBirth); SqlParameter paraJsl = new SqlParameter("

37、;jsl", SqlDbType.Int); paraJsl.Direction = ParameterDirection.Input; paraJsl.Value = iJsl; cmd.Parameters.Add(paraJsl); try cmd.ExecuteNonQuery(); MessageBox.Show("更新學(xué)生表成功！"); string sql = "select * from xs" SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = new Sq

38、lCommand(sql, conn); DataSet ds = new DataSet(); da.Fill(ds); this.dataGridView1.DataSource = ds.Tables0.DefaultView; catch MessageBox.Show("更新學(xué)生表失?。?quot;); private void dataGridView1_SelectionChanged(object sender, EventArgs e) /獲取dataGridView1當(dāng)前行的信息并將其值反應(yīng)到各文本框中 DataGridViewRow dgvRow = new D

39、ataGridViewRow(); dgvRow = this.dataGridView1.CurrentRow; this.textBoxJszh.Text = dgvRow.Cells0.Value.ToString(); this.textBoxName.Text = dgvRow.Cells1.Value.ToString(); boBoxSpec.Text = dgvRow.Cells2.Value.ToString(); /獲取性別并顯示處理如下 bool sex = (bool)dgvRow.Cells3.Value; if (sex) this.radioButtonWoman

40、.Checked = true; else this.radioButtonMan.Checked = true; this.textBoxBirth.Text = dgvRow.Cells4.Value.ToString(); this.textBoxJsl.Text = dgvRow.Cells5.Value.ToString(); /將讀取到的照片放入圖片框中 byte imagebytes = null; /判斷當(dāng)前記錄是否有照片 if (dgvRow.Cells6.Value != DBNull.Value ) imagebytes = (byte)dgvRow.Cells6.Val

41、ue; MemoryStream ms = new MemoryStream(imagebytes); Bitmap bmpt = new Bitmap(ms); pictureBox1.Image = bmpt; /加載照片至圖片框中 private void button5_Click(object sender, EventArgs e) openFileDialog1.FileName = "" openFileDialog1.Filter = "Image File(*.jpg)|*.jpg|All File(*.*)|*.*" openFil

42、eDialog1.ShowDialog(); if (openFileDialog1.FileName != "") this.pictureBox1.Image = Image.FromFile(openFileDialog1.FileName); /將照片添加到當(dāng)前記錄并保存至數(shù)據(jù)庫(kù)中 private void button_SavePicture_Click(object sender, EventArgs e) string fullpath = "" Image img = null; if (openFileDialog1.FileName

43、!= "") fullpath = openFileDialog1.FileName; img = Image.FromFile(fullpath); else MessageBox.Show("沒有圖片！"); return; /需要使用System.IO命名空間 FileStream fs = new FileStream(fullpath, FileMode.Open, FileAccess.Read); byte imagebytes = new bytefs.Length; fs.Read(imagebytes, 0, (int)fs.Leng

44、th); fs.Close(); fs = null; /利用公共鏈接訪問數(shù)據(jù)庫(kù) Dbconnection db = new Dbconnection(); SqlConnection conn = db.Dblink(); conn.Open(); string sql, strJszh; strJszh = this.textBoxJszh.Text.Trim(); /注意下面的 img變量在雙引號(hào)里面，否則出錯(cuò) sql = "update xs set 照片= img where 借書證?='" + strJszh + "'" Sq

45、lCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add("img", SqlDbType.Image); cmd.Parameters0.Value = imagebytes; int flag = cmd.ExecuteNonQuery(); conn.Close(); imagebytes = null; if (flag > 0) MessageBox.Show("添加照片成功！"); /更新數(shù)據(jù)庫(kù)后將界面也及時(shí)更新 sql = "select * from xs&q

46、uot; DataSet ds = db.ExeSelect(sql); this.dataGridView1.DataSource = ds.Tables0.DefaultView; /以下使用dataset刪除符合條件的記錄 private void button_Delete_Click(object sender, EventArgs e) /利用公共鏈接訪問數(shù)據(jù)庫(kù) Dbconnection db = new Dbconnection(); SqlConnection conn = db.Dblink(); conn.Open(); SqlCommand cmd = new SqlCommand("select * from xs&quo