物件读取库课件.ppt

1、第 17 章 使用 DataReader 物件讀取資料庫 n無論使用哪種.NET Framework 資料提供者存取資料庫，其步驟均相同，只是使用的物件不同：1.建立資料連接2.執行 SQL 命令並傳回結果3.讀取執行結果 17-2建立資料連接 n無論哪種資料來源，建立資料連接的語法均相同，您可以使用下列兩種語法為 OLE DB 相容資料庫建立資料連接：nDim objConn As New OleDbConnection()nDim objConn As New OleDbConnection(connectionString)nConnection 物件的屬性 nConnectionStr

2、ing=nConnectionTimeout nDataSource nDriver nPacketSize nProvider nServerVersion nState nConnection 物件的方法nBeginTransaction()nClose()nCreateCommand()nOpen()nConnection 物件的事件nStateChange nCh17Connection_01_Access.aspx01:02:03:04:05:Sub Page_Load(ByVal sender As Object,ByVal e As System.EventArgs)06:Dim

3、 objConn As New OleDbConnection()07:objConn.ConnectionString=Provider=Microsoft.Jet.OLEDB.4.0;&Data Source=&_ Server.MapPath(/App_Data/Friend.mdb)08:objConn.Open()09:objConn.Close()10:End Sub11:nweb.config nweb.config n若要在程式碼中取得特定的連接設定，可以使用 ConfigurationManager 類別的靜態方法 ConnectionStrings()，其語法如下：Dim

4、setting As ConnectionStringSettings=ConfigurationManager.ConnectionStrings(name)n例如下面的敘述可以從 web.config 組態檔取得識別名稱為 OLEDB_Friend 的連接設定：Dim setting As ConnectionStringSettings=ConfigurationManager.ConnectionStrings(OLEDB_Friend)nCh17Connection_02_Access.aspx Sub Page_Load(ByVal sender As Object,ByVal e

5、 As System.EventArgs)取得 web.config 組態檔的資料連接設定 Dim setting As ConnectionStringSettings=_ConfigurationManager.ConnectionStrings(OLEDB_ Friend)建立 Connection 物件 Dim objConn As New OleDbConnection()objConn.ConnectionString=setting.ConnectionString 開啟資料連接 objConn.Open()關閉資料連接 objConn.Close()End Sub17-3執行

6、SQL 命令 n無論哪種資料來源，建立 Command 物件的語法均相同，您可以使用下列四種語法為 OLE DB 相容資料庫建立 Command 物件：Dim objCmd As New OleDbCommand()Dim objCmd As New OleDbCommand(sql)Dim objCmd As New OleDbCommand(sql,connection)Dim objCmd As New OleDbCommand(sql,connection,transaction)nCommand 物件的屬性nCommandText=nCommandTimeout=n nCommand

7、Type=StoredProcedure|TableDirect|Text nConnection=nParameters nTransaction=nCommand 物件的方法nCancel()nClone()nExecuteNonQuery()nExecuteReader()nExecuteScalar()nCh17Command_Access.aspx Sub Page_Load(ByVal sender As Object,ByVal e As System.EventArgs)取得 web.config 組態檔的資料連接設定 Dim setting As ConnectionStri

8、ngSettings=_ ConfigurationManager.ConnectionStrings(OLEDB_Friend)建立資料連接 Dim objConn As New OleDbConnection()objConn.ConnectionString=setting.ConnectionString 開啟資料連接 objConn.Open()使用 Command 物件執行 SQL 命令 Dim strSQL As String=Delete From 擇友俱樂部 Where 姓名=幻影小雨貓 Dim objCmd As New OleDbCommand(strSQL,objCon

9、n)objCmd.ExecuteNonQuery()關閉資料連接 objConn.Close()End Sub17-4使用 DataReader 物件讀取資料 n我們可以透過 Command 物件的 ExecuteReader()方法建立 DataReader 物件，如下所示：Dim objReader As OleDbDataReader=objCmd.ExecuteReader()Dim objReader As SqlDataReader=objCmd.ExecuteReader()Dim objReader As OdbcDataReader=objCmd.ExecuteReader(

10、)Dim objReader As OracleDataReader=objCmd.ExecuteReader()nDataReader 物件的屬性nFieldCount nHasRows nIsClosed nItem(name|ordinal)nDataReader 物件的方法nClose()nGetBoolean(ordinal)nGetDataTypeName(ordinal)nGetFieldType(ordinal)nGetName(ordinal)nGetOrdinal(name)nGetValue(ordinal)nGetValues(values)n IsDBNull(ord

11、inal)nRead()nCh17DataReader_01_Access.aspx01:02:03:04:05:Sub Page_Load(ByVal sender As Object,ByVal e As System.EventArgs)06:取得 web.config 組態檔的資料連接設定07:Dim setting As ConnectionStringSettings=_ ConfigurationManager.ConnectionStrings(OLEDB_Friend)08:09:建立資料連接10:Dim objConn As New OleDbConnection(sett

12、ing.ConnectionString)11:12:開啟資料連接13:objConn.Open()14:15:建立 DataReader 物件16:Dim objCmd As New OleDbCommand(Select*From 擇友俱樂部,objConn)17:Dim objReader As OleDbDataReader=objCmd.ExecuteReader()18:讀取並顯示資料19:Dim I As Integer20:Response.Write()21:For I=0 To objReader.FieldCount 122:Response.Write(&objRead

13、er.GetName(I)&)23:Next24:Response.Write()25:While objReader.Read()26:Response.Write()27:For I=0 To objReader.FieldCount-128:Response.Write(&objReader.Item(I).ToString()&)29:Next30:Response.Write()31:End While32:Response.Write()33:34:關閉 DataReader 物件與資料連接35:objReader.Close()36:objConn.Close()37:End S

14、ub38:17-5使用 Connection 物件的事件 nConnection 物件有一個名稱為 StateChange 的事件，除了 Web 伺服器控制項之外，使用任何事件都必須依照如下形式進行宣告：AddHandler EventName,New EventHandler(AddressOf ProcedureName)nCh17StateChange_Access.aspx01:02:03:04:05:Sub Page_Load(ByVal sender As Object,ByVal e As System.EventArgs)06:取得 web.config 組態檔的資料連接設定0

15、7:Dim setting As ConnectionStringSettings=_ ConfigurationManager.ConnectionStrings(OLEDB_Friend)08:Dim objConn As New OleDbConnection(setting.ConnectionString)09:AddHandler objConn.StateChange,_ New StateChangeEventHandler(AddressOf Connection_StateChange)10:objConn.Open()11:objConn.Close()12:End Sub13:14:Sub Connection_StateChange(sender As Object,e As StateChangeEventArgs)15:Response.Write(目前連接狀態：&e.CurrentState.ToString()&)16:Response.Write(原來連接狀態：&e.OriginalState.ToString()&)17:End Sub18: