ADO NET 1 ADO NET Objects n ADO

ADO. NET 簡介 1

ADO. NET Objects n ADO. NET is a data abstraction layer that smoothes over difference between data providers and includes objects and functions for easy access to data n A standard set of. NET objects n n n Connection: For connection to and managing transactions against a database. Command: For issuing SQL commands against a database. Data. Reader: For reading a forward-only stream of data records from a SQL Server data source. Data. Set: For storing, remoting and programming against flat data, XML data and relational data. Data. Adapter: For pushing data into a Data. Set, and reconciling data against a database. 2

ADO. NET Architecture Client Data. Set Data. Adapter Data. Reader Command Connection Data Provider Data. Source 3

連接資料庫 n 引用ADO. NET命名空間 n System. Data n n 引用ADO. NET基礎物件 System. Data. Ole. Db n 引用OLE DB資料來源的物件。若使用的資料庫為Access、 Excel、SQL Server、文字檔…. 等，須引用此命名空間，其物件 包含Ole. Db. Connection、Ole. Db. Command、 Ole. Db. Data. Reader…等 <%@ Import Namespace="System. Data" %> <%@ Import Namespace="System. Data. Ole. Db" %> n System. Data. Sql. Client n 引用SQL Server資料來源的物件。若使用的資料庫為SQL Server 7. 0以上的版本，須引用此命名空間，其物件包含 Sql. Connection、Sql. Command、Sql. Data. Reader…等 <%@ Import Namespace="System. Data" %> <%@ Import Namespace="System. Data. Sql. Client" %> n System. IO n 存取XML資料的物件 4

連接資料庫 n Connection 物件(引用System. Data. Ole. Db命名空間) n 使用Ole. Db. Connection物件來開啟、關閉資料庫。先宣告 Ole. Db. Connection物件 Dim conn As Ole. Db. Connection n n 使用Server. Map. Path方法取得資料庫檔案所在的真實路徑 Dim DBPath As String = Server. Map. Path("資料庫名稱") 宣告一個變數用來存放資料庫的連結字串並指定資料庫的真 實路徑 Dim conn. Str As String conn. Str = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=" & _ DBPath n 建立Ole. Db. Connection物件並指定其資料庫的連結字串 conn = New Ole. Db. Connection( conn. Str ) n 使用Open方法開啟資料庫 conn. Open() n '開啟資料庫 完成資料庫存取後再使用Close方法關閉資料庫 conn. Close() '關閉資料庫 5

使用Data. Reader物件 n 宣告Data. Reader、Command物件 Dim cmd As Ole. Db. Command Dim reader As Ole. Db. Data. Reader n 設定Command物件所要執行的SQL命令 cmd = New Ole. Db. Command (“SQL命令”, _ Ole. Db. Connection物件) n 使用Command物件的Execute. Reader方法執 行SQL命令。此時即會將查詢結果的 Data. Reader物件傳回 reader = Cmd. Execute. Reader() 7

顯示查詢結果的所有記錄 n 取得Data. Reader物件中記錄的欄位名稱 For i = 0 To Data. Reader物件. Field. Count - 1 Response. Write( Data. Reader物件. Get. Name(i) ) Next n 取得Data. Reader物件的欄位資料 Do While Data. Reader物件. Read() For i = 0 To Data. Reader物件. Field. Count - 1 Response. Write(Data. Reader物件. Item(i)) Next Loop n Eg [aspx][exec] 9

Six Classes n Data. Set memory-resident database n contains Data. Table and Data. Relation Data. Table n memory-resident database table Data. Relation n to define relationship between Data. Tables Data. View n to filter and sort the contents of a Data. Table Sql. Data. Adapter n to build a Data. Table from a SQL Server database table Ole. Db. Data. Adapter n to build a Data. Table from other types of databases n n n 11

Data. Set Properties and Methods n n n Default. View. Manger Has. Errors Relations Tables Accept. Changes n n Clear Get. Changes n Get. XML n n n Get. XMLSchema Merge Read. XMLSchema Reject. Changes Write. XML n Write. XMLSchema Gets a view of the data Gets a value indicating if there any errors Gets the relations collection Gets the tables collection Accepts all the changes made since loaded or since last time Accept. Changes was called Clears the dataset of any data Returns a copy of the dataset containing all the changes made since loaded or since Accept. Changes was called Gets the XML representation of the data in the dataset Gets the XSD schema for the XML representation Merges the data in the dataset with another dataset Reads an XML schema and data into the dataset Reads an XML schema into the dataset Rolls back to the state since lase Accept. Changes Writes out the XML schema and data from the dataset 12 Writes the structure of the dataset as an XML schema

Data. Table Properties and Methods n n n n Child. Relations Columns Constraints Data. Set Default. View Parent. Relations Primary. Key n n Rows Accept. Changes Clear Get. Changes n New. Row n n Reject. Changes Select Gets the collection of child relations Gets the columns collection Gets the constraints collection Gets the dataset this table belongs to Gets a view of the table for filtering Gets the parent relations collection Gets or sets an array of columns as primary key for this table Gets the rows collection Commits all the changes since last Accept. Changes Clears the table of all data Gets a copy of the Data. Table with all the changes since last Accept. Changes Creates a new Data. Row with the same schema as the table Rolls back changes since last Accept. Changes Gets an array of Data. Row objects 13

Data. Row Properties and Methods n Item Gets or sets the data stored in a specific column n Item. Array Gets or sets all the values for the row using an array n Table Gets the table this row is owned by n Accept. Changes Accepts all the changes since the last time Accept. Changes was called n Get. Child. Rows Gets the child rows for this row n Get. Parent Row Gets the parent row of this row n Reject. Changes Rejects all the changes since the last time Accept. Changes was called 14

Data. Adapter Properties and Methods n Accept. Changes. During. Fill n Indicates whether or not to call Accept. Changes on a Data. Row after adding it ot a Data. Table n Fills a Data. Table by adding or updating rows in the Data. Set n Fill. Schema n Adds Data. Table object to the specified Data. Set n Updates all the modified rows in the specified table of the Data. Set 15

使用Data. Adapter物件 n 建立Data. Adapter、Data. Set物件 n n Dim Data. Set物件 As Data. Set = new Data. Set() Dim Data. Adapter物件 As Ole. Db. Data. Adapter 利用Data. Adapter物件並指定所要執行的SQL命令 ‘如下程式產生成績單的Data. Table物件 Selectcmd = "Select * from SCORE" ‘查詢SCORE資料表 使用Data. Adapter物件的Fill方法將查詢資料的結果放到Data. Set物 件中 Data. Adapter物件 = New Ole. Db. Data. Adapter(Select. Cmd, conn) Data. Adapter物件. Fill(Data. Set物件, "成績表") Eg [aspx][exec] 共執行三次SQL命令，並使用Data. Adapter物件的Fill方法將查詢 的結果存放到Data. Set物件，並產生三個Data. Table物件，其名稱 為成績單、股票行情表、產品資料表 16

使用Data. Table物件來取得資料 n Data. Table物件. Columns. Count n 取得Data. Table的欄位數目。 n Data. Table物件. Columns(j). Column. Name n 取得Data. Table的第j個的欄位名稱，註標起始值為 0。 n Data. Table物件. Rows. Count n 取得Data. Table的資料總筆數。 n Data. Table物件. Rows(i). Item(“欄位名稱”) n 取得Data. Table的第i列某一個欄位的資料內容。 n Data. Table物件. Rows(i). Item(j) n 取得Data. Table的第i列第j欄的資料內容，註標起始值為 0 17

使用Data. Table顯示查詢結果 n 取得Data. Table物件的欄位名稱 For i = 0 To Data. Table物件. Columns. Count - 1 Response. Write( _ Data. Table物件. Columns(i). Column. Name) Next n 使用巢狀迴圈來逐一取得Data. Table物件第i列第j欄的 資料 For i = 0 To Data. Table物件. Rows. Count - 1 For j = 0 To Data. Table物件. Columns. Count - 1 Response. Write(Data. Table物件. Rows(i). Item(j)) Next n Eg [aspx][exec] 18

19