數(shù)據(jù)庫(kù)技術(shù)基礎(chǔ)：第八章 ADO.NET與數(shù)據(jù)庫(kù)訪問(wèn)技術(shù)

1、數(shù)據(jù)庫(kù)技術(shù)基礎(chǔ)SQL Server & VB.NET 數(shù)據(jù)庫(kù)技術(shù)基礎(chǔ)本書目錄1第八章 ADO.NET與數(shù)據(jù)庫(kù)訪問(wèn)技術(shù) 學(xué)習(xí)目標(biāo)：ADO.NET體系結(jié)構(gòu)VB.NET與SQL Server數(shù)據(jù)庫(kù)的連接訪問(wèn)數(shù)據(jù)庫(kù)常用方法ADO.NET體系結(jié)構(gòu)ADO.NET數(shù)據(jù)庫(kù)訪問(wèn)過(guò)程ConnectionDataBaseDataAdapterSelectCommandFillUpdateDataSetInsertCommandUpdateCommandDeleteCommand.NET Framework 數(shù)據(jù)提供程序 .NET Framework 數(shù)據(jù)提供程序是ADO.NET 結(jié)構(gòu)的另一個(gè)核心元素，其組件的設(shè)計(jì)目

2、的相當(dāng)明確：實(shí)現(xiàn)對(duì)底層數(shù)據(jù)源的訪問(wèn)。這些組件包括：Connection、Command、DataAdapter和DataReader對(duì)象。Connection 對(duì)象提供與數(shù)據(jù)源的連接。Command 對(duì)象使您能夠訪問(wèn)用于返回?cái)?shù)據(jù)、修改數(shù)據(jù)、運(yùn)行存儲(chǔ)過(guò)程以及發(fā)送或檢索參數(shù)信息的數(shù)據(jù)庫(kù)命令。DataReader 從數(shù)據(jù)源中提供高性能的數(shù)據(jù)流。最后DataAdapter 提供連接 DataSet 對(duì)象和數(shù)據(jù)源的橋梁。DataAdapter 使用 Command 對(duì)象在數(shù)據(jù)源中執(zhí)行 SQL 命令，以便將數(shù)據(jù)加載到 DataSet 中，并使對(duì) DataSet 中數(shù)據(jù)的更改與數(shù)據(jù)源保持一致。ADO .NE

3、T的四類數(shù)據(jù)提供程序Microsoft SQL .NET 數(shù)據(jù)提供程序 該提供程序使 .NET 應(yīng)用程序可以直接訪問(wèn) Microsoft SQL Server 數(shù)據(jù)庫(kù)。System.Data.SqlClientMicrosoft OLE DB .NET 數(shù)據(jù)提供程序 該提供程序使 .NET 應(yīng)用程序可以使用它們的本機(jī) OLE DB 提供程序訪問(wèn)數(shù)據(jù)庫(kù)。 System.Data.OleDbMicrosoft ODBC .NET 數(shù)據(jù)提供程序 該提供程序使 .NET 應(yīng)用程序可以通過(guò)使用它們的 ODBC 驅(qū)動(dòng)程序訪問(wèn)數(shù)據(jù)庫(kù)。System.Data.OdbcMicrosoft Oracle .NET

4、 數(shù)據(jù)提供程序 該提供程序使 .NET 應(yīng)用程序可以訪問(wèn) Oracle 數(shù)據(jù)庫(kù)。System.Data. OracleClient.NET數(shù)據(jù)提供程序?qū)ο竽Ｐ?.NET Data Provider(數(shù)據(jù)提供程序)ConnectionDataAdapterDeleteCommandUpdateCommanInsertCommandSelectCommandCommandDataReaderADO.NET DataSet ADO.NET DataSet 是 ADO.NET 的斷開式結(jié)構(gòu)的核心組件。DataSet 的設(shè)計(jì)目的很明確：為了實(shí)現(xiàn)獨(dú)立于任何數(shù)據(jù)源的數(shù)據(jù)訪問(wèn)。因此，它可以用于多種不同的數(shù)據(jù)源

5、，用于 XML 數(shù)據(jù)，或用于管理應(yīng)用程序本地的數(shù)據(jù)。DataSet 是數(shù)據(jù)的內(nèi)存駐留表示形式，無(wú)論數(shù)據(jù)源是什么，它都會(huì)提供一致的關(guān)系編程模型。DataSet 是包含一個(gè)或多個(gè) DataTable 對(duì)象的集合，這些對(duì)象由數(shù)據(jù)行和數(shù)據(jù)列以及主鍵、外鍵、約束和有關(guān) DataTable 對(duì)象中數(shù)據(jù)的關(guān)系信息組成。Connection對(duì)象 OLEDB.NET數(shù)據(jù)提供程序Connection對(duì)象連接參數(shù)屬性說(shuō)明Provider指定OLEDB ProviderData Source指定數(shù)據(jù)庫(kù)服務(wù)器名稱或位置Initial Catalog指定數(shù)據(jù)庫(kù)名User ID用戶帳戶Password用戶密碼Intigra

6、ted SecuitySSPI或TrueSqlConnection對(duì)象 SQL Server登錄模式: Initial Catalog= mydb;DataSource=myServer;User ID=sa;password=;SQL.NET數(shù)據(jù)提供程序Connection對(duì)象連接參數(shù)屬性說(shuō)明Data Source指定數(shù)據(jù)庫(kù)服務(wù)器名稱或位置Initial Catalog或DataBase指定數(shù)據(jù)庫(kù)名User ID用戶帳戶Password用戶密碼SqlConnection對(duì)象 Windows登錄模式:Initial Catalog= mydb;DataSource=myServer;Integ

7、rated Security=True;SQL.NET數(shù)據(jù)提供程序Connection對(duì)象連接參數(shù)屬性說(shuō)明Data Source指定數(shù)據(jù)庫(kù)服務(wù)器名稱或位置Initial Catalog 或DataBase指定數(shù)據(jù)庫(kù)名Intigrated SecuitySSPI或TrueDataAdapter對(duì)象 DataAdapter 是連接 DataSet 對(duì)象和數(shù)據(jù)源的橋梁。DataAdapter 使用 Command 對(duì)象在數(shù)據(jù)源中執(zhí)行 SQL 命令，以便將數(shù)據(jù)加載到 DataSet 中，并使對(duì) DataSet 中數(shù)據(jù)的更改與數(shù)據(jù)源保持一致。如何驗(yàn)證？創(chuàng)建Connection、DataAdapter、D

8、ataset對(duì)象方法一： 從數(shù)據(jù)源直接拖放數(shù)據(jù)表，在此過(guò)程中由系統(tǒng)自動(dòng)生成方法二： 通過(guò)代碼創(chuàng)建基于斷開式訪問(wèn)方法設(shè)計(jì)步驟：(1)創(chuàng)建SqlConnection對(duì)象(2)創(chuàng)建SqlDataAdapter對(duì)象(3)創(chuàng)建DataSet對(duì)象(4)為控件指定數(shù)據(jù)源用代碼創(chuàng)建SqlConnection對(duì)象 Dim myconn As New SqlConnection (Initial Catalog=university; Data Source=(local); Integrated Security=True;) Dim myconn As New SqlConnection (Initial C

9、atalog= mydb; DataSource=myServer; User ID=sa; Password=; ) 用代碼創(chuàng)建DataAdapter、Dataset對(duì)象 創(chuàng)建數(shù)據(jù)適配器 Dim mysql As String = “select * from student Dim myadapter As New SqlDataAdapter(mysql, myconn) 創(chuàng)建數(shù)據(jù)集 Dim mydataset As New DataSet myadapter.Fill(mydataset, “student) 只能取“student”這個(gè)表名嗎？斷開式訪問(wèn)模型實(shí)例Private Sub

10、 Form1_Load() Handles MyBase.Load Dim myconn As New SqlConnection(“initial catalog=university;data source=.; integrated security=true;) Dim myadapter As New SqlDataAdapter(select * from student, myconn) Dim mydataset As New DataSet myadapter.Fill(mydataset) Me.DataGridView1.DataSource = mydataset.Ta

11、bles(0)End Sub上述最后兩行代碼可以改寫為：myadapter.Fill(mydataset,”student”)DataGridView1.DataSource = mydataset.Tables(“student”)DataSet訪問(wèn)結(jié)構(gòu) 庫(kù). 表 . 行 . 列Dataset.Tables.Rows.Itemmydataset.tables(“表名”).rows(i).item(j)mydataset.tables(“student”).rows(1).item(3)后臺(tái)數(shù)據(jù)庫(kù)設(shè)計(jì)CREATE TABLE CasePavilion( CaseNo CHAR(3) not n

12、ull Primary Key, CaseTitle VARCHAR(30), Feature VARCHAR(500), Highlight VARCHAR(500) )圖片如何存儲(chǔ)？前臺(tái)功能代碼設(shè)計(jì)Imports System.Data.SqlClientPublic Class Form1 Dim myconn As New SqlConnection(initial catalog=UBPA; data source=.SQLExpress;integrated security=SSPI;) Dim myadapter As New SqlDataAdapter(select * f

13、rom casepavilion, myconn) Dim mydataset As New Data.DataSetPrivate Sub Form1_Load() myadapter.Fill(mydataset, casecity) Me.ComboBox1.DataSource = mydataset.Tables(casecity) Me.ComboBox1.DisplayMember = “CaseTitle” Me.AxWindowsMediaPlayer1.URL = Application.StartupPath & casecity.mp3 Me.AxWindowsMedi

14、aPlayer1.Ctlcontrols.play()End Sub前臺(tái)功能代碼設(shè)計(jì)Private Sub ComboBox1_SelectedIndexChanged() Label1.Text = mydataset.Tables(casecity). Rows(Cint(ComboBox1.SelectedIndex).Item(1) TextBox1.Text =mydataset.Tables(casecity). Rows(CInt(Me.ComboBox1.SelectedIndex).Item(3) TextBox2.Text =mydataset.Tables(casecit

15、y). Rows(CInt(Me.ComboBox1.SelectedIndex).Item(2) PictureBox1.Image = Image.FromFile(Application.StartupPath & images & mydataset.Tables(casecity).Rows(CInt(Me.ComboBox1.SelectedIndex). Item(0) & .jpg)End Sub數(shù)據(jù)綁定 數(shù)據(jù)綁定是綁定技術(shù)中使用最頻繁，也是最為重要的技術(shù)，也可以說(shuō)是各種.Net開發(fā)語(yǔ)言開發(fā)數(shù)據(jù)庫(kù)應(yīng)用程序最需要掌握的基本知識(shí)之一。數(shù)據(jù)綁定之所以很重要，是因?yàn)閿?shù)據(jù)綁定技術(shù)使得界面

16、上的所有控件訪問(wèn)數(shù)據(jù)源數(shù)據(jù)時(shí)可以步調(diào)一致。圖8.3.1 例8.6程序運(yùn)行結(jié)果(1) 建立SQLServerUBPACases表與t控件之間的數(shù)據(jù)綁定關(guān)系 Public mybind As BindingManagerBase SqlDataAdapter2.Fill(DataSet11.UBPACases) mybind = Me.BindingContext(DataSet11, UBPACases) (如果是.NET2005以上版本，直接用拖放的綁定數(shù)據(jù)源)(2) 用綁定對(duì)象的position屬性實(shí)現(xiàn)UBPACases表的數(shù)據(jù)瀏覽 mybind.Position = 0 /到頭 mybin

17、d.Position = mybind.Position 1 /往前翻 mybind.Position = mybind.Position + 1 /往后翻 mybind.Position = mybind.Count 1 /到尾(3) 用綁定對(duì)象的下列方法實(shí)現(xiàn)UBPACases表的更新操作 mybind.AddNew() /插入一條記錄 mybind.RemoveAt(mybind.Position) /刪除當(dāng)前記錄 mybind.EndCurrentEdit() /結(jié)束當(dāng)前更新狀態(tài) mybind.CancelCurrentEdit() /取消當(dāng)前編輯修改 mybind.EndCurrent

18、Edit() SqlDataAdapter1.Update(DataSet11.UBPACases)數(shù)據(jù)瀏覽操作StudentBindingSource.MoveFirst()StudentBindingSource.MovePrevious()StudentBindingSource.MoveNext()StudentBindingSource.MoveLast()更新操作StudentBindingSource.AddNew()StudentBindingSource.RemoveCurrent()StudentBindingSource.CancelEdit()Me.Validate()

19、StudentBindingSource.EndEdit()StudentTableAdapter.Update(Me.UniversityDataSet.student)2005以上版本 mybind=BindingContext(StudentBindingSource) (如果是.NET2005以上版本，直接用拖放的綁定數(shù)據(jù)源)通過(guò)代碼實(shí)現(xiàn)數(shù)據(jù)綁定 數(shù)據(jù)綁定的常規(guī)方法是：首先根據(jù)應(yīng)用需求，設(shè)計(jì)界面；然后在應(yīng)用程序開始處定義一個(gè)綁定數(shù)據(jù)源對(duì)象，并指明其數(shù)據(jù)源為數(shù)據(jù)集中的對(duì)應(yīng)表；接著將用于顯示數(shù)據(jù)的控件分別綁定到對(duì)應(yīng)的數(shù)據(jù)源，這樣所有數(shù)據(jù)控件的顯示都可以用這個(gè)綁定數(shù)據(jù)源對(duì)象來(lái)編程設(shè)計(jì)，從而完

20、成數(shù)據(jù)導(dǎo)航功能。數(shù)據(jù)綁定設(shè)計(jì)步驟1. 綁定對(duì)象的定義 Public mybind As BindingSource2. 設(shè)定綁定對(duì)象的數(shù)據(jù)源 mybind.DataSource=數(shù)據(jù)集名稱 mybind.DataMember=數(shù)據(jù)表名稱3. 將控件和對(duì)應(yīng)數(shù)據(jù)源綁定 控件.DataBindings.Add(New Binding(Text, mybind, 屬性名, True)控件名稱控件屬性綁定數(shù)據(jù)表屬性名數(shù)據(jù)綁定實(shí)例例8.7 用代碼實(shí)現(xiàn)城市最佳實(shí)踐區(qū)建設(shè)案例的數(shù)據(jù)訪問(wèn)。Imports System.Data.SqlClientPublic Class Form4 Dim myconn As

21、New SqlConnection(Data Source=.;Initial Catalog=UBPA;Integrated Security=True;) Dim myadapter As New SqlDataAdapter(SELECT * FROM UBPACases, myconn) Dim mydataset As New DataSet Dim mybind As New BindingSource /定義綁定數(shù)據(jù)源對(duì)象 Private Sub Form4_Load() /生成數(shù)據(jù)集 myadapter.Fill(mydataset, UBPACases) /為綁定數(shù)據(jù)源對(duì)象設(shè)

22、置數(shù)據(jù)源 mybind.DataSource = mydataset mybind.DataMember = UBPACases /為控件設(shè)置要綁定的數(shù)據(jù)源中對(duì)應(yīng)表的屬性 TextBox1.DataBindings.Add(New Binding(Text, mybind, 場(chǎng)館名稱, True) TextBox2.DataBindings.Add(New Binding(Text, mybind, 案例名稱, True) TextBox3.DataBindings.Add(New Binding(Text, mybind, 城市, True) TextBox4.DataBindings.Ad

23、d(New Binding(Text, mybind, 造型亮點(diǎn)與案例主題, True) TextBox5.DataBindings.Add(New Binding(Text, mybind, 案例位置, True) End Sub數(shù)據(jù)-控件綁定實(shí)現(xiàn)mybind.MoveNext() mybind.MoveLast() mybind.AddNew() mybind.RemoveCurrent() mybind.MoveFirst()mybind.MovePrevious() 保存更新的編程實(shí)現(xiàn)Private Sub Button7_Click() mybind.EndEdit() /用SqlC

24、ommandBuilder對(duì)象生成數(shù)據(jù)適配器myadapter對(duì)應(yīng)的三條更新命令， Dim cmd As New SqlCommandBuilder(myadapter) /然后通過(guò)調(diào)用myadapter的Update方法將前臺(tái)窗體的數(shù)據(jù)更新回填到物理數(shù)據(jù)庫(kù)。 myadapter.Update(mydataset.Tables(UBPACases) End SubCommand對(duì)象 建立連接后，可以通過(guò)Command對(duì)象執(zhí)行SQL命令，從數(shù)據(jù)源（比如SQL Server）中返回結(jié)果。在創(chuàng)建Command對(duì)象時(shí)，需要指定一個(gè)要在數(shù)據(jù)源上執(zhí)行的SQL語(yǔ)句和Connection對(duì)象。Command對(duì)

25、象的SQL語(yǔ)句可以通過(guò)CommandText屬性進(jìn)行查詢和修改。 Command對(duì)象的執(zhí)行方法 執(zhí)行方式執(zhí)行結(jié)果ExecuteReader 返回結(jié)果為一個(gè)數(shù)據(jù)流，即生成一個(gè)SqlDataReader對(duì)象，其中存放著 Command命令執(zhí)行的一個(gè)當(dāng)前的結(jié)果行。ExecuteScalar 執(zhí)行查詢，并返回查詢的結(jié)果集中第一行的第一列，即只返回一個(gè)單值。ExecuteNonQuery 在不使用 DataSet的情況下直接更改數(shù)據(jù)庫(kù)中的數(shù)據(jù)，用于不返回值的命令執(zhí)行。怎樣合理使用好 Command對(duì)象？1、通過(guò)顯式連接，進(jìn)行COMMAND的執(zhí)行讀操作，對(duì)應(yīng)數(shù)據(jù)查詢功能，執(zhí)行方式為：ExecuteRead

26、er（）ExecuteScalar （）寫操作，對(duì)應(yīng)數(shù)據(jù)更新功能，執(zhí)行方式為：ExecuteNonQuery（）2、通過(guò)隱式連接，進(jìn)行COMMAND的執(zhí)行DataAdapter的Fill和Update方法在連接關(guān)閉的情況下自動(dòng)打開為相關(guān)命令屬性指定的連接。 應(yīng)用實(shí)例例：求SC中所有選課學(xué)生的平均成績(jī)。SqlCommand、ExecuteScalarPrivate Sub Form1_Load() Handles MyBase.Load Dim sql As String = select avg(score) from scDim myconn As New SqlConnection(Ini

27、tial Catalog=學(xué)生選課;Data_ Source=(local);Integrated Security=SSPI;) Dim mycmd As New SqlCommand(sql, myconn) Dim avg As Integer = 0 myconn.Open() avg = mycmd.ExecuteScalar() myconn.Close() MsgBox(平均成績(jī)= & avg)End SubSQL_insert命令的執(zhí)行Private Sub Button1_Click() Handles Button1.Click Dim myconn = New SqlCl

28、ient.SqlConnection(integrated security=SSPI;data source=.;initial catalog=tjdb) Dim mysql As String = insert into sc values( & Me.TextBox1.Text & , & Me.TextBox2.Text & , & Val(Me.TextBox3.Text) & ) Dim mycmd As New SqlClient.SqlCommand(mysql, myconn) myconn.open() Try mycmd.ExecuteNonQuery() Catch

29、ex As Exception MsgBox(ex.Message) End Try myconn.close()End SubSQL_delete命令的執(zhí)行Private Sub Button2_Click() Handles Button2.Click Dim myconn = New SqlClient.SqlConnection (integrated security=SSPI; data source=.; initial catalog=tjdb) Dim mysql As String = delete from sc where snum= & Me.TextBox4.Tex

30、t & and cnum= & Me.TextBox5.Text & Dim mycmd As New SqlClient.SqlCommand(mysql, myconn) myconn.open() mycmd.ExecuteNonQuery() myconn.close()End SubADO.NET數(shù)據(jù)庫(kù)訪問(wèn)技術(shù) 數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用41SqlCommand與存儲(chǔ)過(guò)程的調(diào)用無(wú)參數(shù)存儲(chǔ)過(guò)程的執(zhí)行Private Sub Form1_Load() Handles MyBase.LoadDim myconn As New SqlConnection(Initial Catalog=學(xué)生選課;Dat

31、a Source=.;Integrated Security=SSPI;) Dim mycmd As New SqlCommand(“sc_avg, myconn) mycomm.CommandType = CommandType.StoredProcedure Dim avg As Integer = 0 myconn.Open() avg = mycmd.ExecuteScalar() myconn.Close() MsgBox(平均成績(jī)= & avg)End SubCreate proc sc_avgAsSelect avg(score) from sc有輸入?yún)?shù)存儲(chǔ)過(guò)程的執(zhí)行 Priv

32、ate Sub Button6_Click() Handles Button6.Click Dim avg As Integer Dim myconn = New SqlClient.SqlConnection(integrated security=SSPI;data source=.;initial catalog=tjdb) Dim mycmd As New SqlClient.SqlCommand(get_snum_avg, myconn) mycmd.CommandType = CommandType.StoredProcedure Dim _snum As New SqlClien

33、t.SqlParameter(_snum, SqlDbType.NChar, 10) mycmd.Parameters.Add(_snum) _snum.Value = TextBox4.Text myconn.open() avg = mycmd.ExecuteScalar() MsgBox(avg) myconn.close() End Subcreate proc get_snum_avg_snum char(10)as select avg(score) from sc where snum=_snum有輸入、輸出參數(shù)的存儲(chǔ)過(guò)程的執(zhí)行 Private Sub Button7_Click

34、() Handles Button7.Click Dim myconn = New SqlClient.SqlConnection(integrated security=SSPI; data source=.;initial catalog=tjdb) Dim mycmd As New SqlClient.SqlCommand(get_cnum_max, myconn) mycmd.CommandType = CommandType.StoredProcedure Dim _cnum = New SqlClient.SqlParameter(_cnum, SqlDbType.NChar, 1

35、0) mycmd.Parameters.Add(_cnum) mycmd.Parameters(_cnum).Value = TextBox5.Text Dim _max As New SqlClient.SqlParameter(_max, SqlDbType.Decimal, 4) mycmd.Parameters.Add(_max) _max.Direction = ParameterDirection.Output myconn.open() mycmd.ExecuteScalar() TextBox6.Text = mycmd.Parameters(“_max”).Value 取值方

36、法一 TextBox6.Text = _max.Value 取值方法二 myconn.close() End Subcreate proc get_cnum_max_cnum char(10),_max decimal outputas select _max=max(score) from sc where cnum=_cnum思考題 給定學(xué)號(hào)，查找指定學(xué)生的平均成績(jī)。分析：Step 1 在SQL Server上創(chuàng)建存儲(chǔ)過(guò)程sc_proc，帶有一個(gè)輸入?yún)?shù)_snum 和一個(gè)輸出參數(shù)_avg 。Step 2 在VB.NET上設(shè)計(jì)帶有一個(gè)文本輸入框或其他輸入控件，用于在運(yùn)行時(shí)獲得界面用戶輸入 ，

37、以及一個(gè)輸出控件用于顯示存儲(chǔ)過(guò)程調(diào)用的執(zhí)行結(jié)果。存儲(chǔ)過(guò)程create proc sc_proc_snum char(10),_avg int outputas select _avg=avg(score) from sc where snum=_snum group by snum Private Sub Button3_Click( ) Handles Button3.Click 帶有輸出參數(shù)的存儲(chǔ)過(guò)程調(diào)用 Dim myconn As New SqlClient.SqlConnection(Initial Catalog=tjdb;Data Source=.;Integrated Security=SSPI;) ) Dim mycmd As New SqlCommand mycmd.Connection = myconn mycmd.Comma