ADO .NET 簡介

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

ADO .NET Architecture DataSource Connection DataAdapter DataReader Command DataSet Client Data Provider

連接資料庫 引用ADO .NET命名空間 System.Data System.Data.OleDb 引用OLE DB資料來源的物件。若使用的資料庫為Access、Excel、SQL Server、文字檔….等，須引用此命名空間，其物件包含OleDbConnection、OleDbCommand、OleDbDataReader…等 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> System.Data.SqlClient 引用SQL Server資料來源的物件。若使用的資料庫為SQL Server 7.0以上的版本，須引用此命名空間，其物件包含SqlConnection、SqlCommand、SqlDataReader…等 <%@ Import Namespace="System.Data.SqlClient" %> System.IO 存取XML資料的物件

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

使用DataReader物件讀取資料 DataReader物件的特性 由資料庫開頭依序往下逐一讀取、自動下移 不能修改資料庫中的資料 命名空間 引用System.Data.OleDb命名空間，則必須使用OleDbDataReader 引用System.Data.Sql命名空間，則必須使用SqlDataReader Command物件 可用來執行SQL語法 使用Command物件的ExecuteNonQuery方法，可以新增、刪除、修改資料庫中的資料 使用Command物件的ExecuteReader方法將查詢結果的DataReader物件傳回

使用DataReader物件 宣告DataReader、Command物件 設定Command物件所要執行的SQL命令 Dim cmd As OleDbCommand Dim reader As OleDbDataReader 設定Command物件所要執行的SQL命令 cmd = New OleDbCommand (“SQL命令”, _ OleDbConnection物件) 使用Command物件的ExecuteReader方法執行SQL命令。此時即會將查詢結果的DataReader物件傳回 reader = Cmd.ExecuteReader()

DataReader常用的屬性與方法 Read: 判斷記錄指標是否指到EOF(檔案結尾符號)，若是則傳回False，若不是則傳回True並將目前記錄指標所指到的記錄由資料庫中讀出來，再將記錄指標移到下一筆記錄 FieldCount: 取得目前DataReader中總共有多少個欄位 GetName(i): 取得第i欄的欄位名稱 Item(i): 取得第i欄的資料內容 Item(“欄位名稱”): 取得目前DataReader所指定的欄位資料 GetDataTypeName(i): 取得第i個欄位的資料型別 GetOrdinal(“欄位名稱”): 取得某個欄位的欄位編號 GetValue(i): 取得第i個欄位的資料內容 IsNull(i): 判斷第i個欄位是否有資料

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

使用DataSet物件來讀取資料 DataSet物件 像是一個記憶體中的資料庫 儲存在IIS上(執行aspx程式的主機記憶體中) 採用離線存取資料庫的方式 Data set 可以XML文件方式檢視 DataSet實際是用DataReader讀取資料 速度比DataReader慢

Six Classes DataSet memory-resident database contains DataTable and DataRelation DataTable memory-resident database table DataRelation to define relationship between DataTables DataView to filter and sort the contents of a DataTable SqlDataAdapter to build a DataTable from a SQL Server database table OleDbDataAdapter to build a DataTable from other types of databases

DataSet Properties and Methods DefaultViewManger Gets a view of the data HasErrors Gets a value indicating if there are any errors Relations Gets the relations collection Tables Gets the tables collection AcceptChanges Accepts all the changes made since loaded or since last time AcceptChanges was called Clear Clears the dataset of any data GetChanges Returns a copy of the dataset containing all the changes made since loaded or since AcceptChanges was called GetXML Gets the XML representation of the data in the dataset GetXMLSchema Gets the XSD schema for the XML representation Merge Merges the data in the dataset with another dataset ReadXML Reads an XML schema and data into the dataset ReadXMLSchema Reads an XML schema into the dataset RejectChanges Rolls back to the state since lase AcceptChanges WriteXML Writes out the XML schema and data from the WriteXMLSchema Writes the structure of the dataset as an XML schema

DataTable Properties and Methods ChildRelations Gets the collection of child relations Columns Gets the columns collection Constraints Gets the constraints collection DataSet Gets the dataset this table belongs to DefaultView Gets a view of the table for filtering ParentRelations Gets the parent relations collection PrimaryKey Gets or sets an array of columns as primary key for this table Rows Gets the rows collection AcceptChanges Commits all the changes since last AcceptChanges Clear Clears the table of all data GetChanges Gets a copy of the DataTable with all the changes since last AcceptChanges NewRow Creates a new DataRow with the same schema as the table RejectChanges Rolls back changes since last AcceptChanges Select Gets an array of DataRow objects

DataRow Properties and Methods Item Gets or sets the data stored in a specific column ItemArray Gets or sets all the values for the row using an array Table Gets the table this row is owned by AcceptChanges Accepts all the changes since the last time AcceptChanges was called GetChildRows Gets the child rows for this row GetParent Row Gets the parent row of this row RejectChanges Rejects all the changes since the

DataAdapter Properties and Methods AcceptChangesDuringFill Indicates whether or not to call AcceptChanges on a DataRow after adding it ot a DataTable Fill Fills a DataTable by adding or updating rows in the DataSet FillSchema Adds DataTable object to the specified DataSet Update Updates all the modified rows in the specified table of the DataSet

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

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

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