ADO.Net的Programmer Guide

創稿人	蔡爾榮	創稿日期	2002/11/15
最後修改人	蔡爾榮	最後修改日期	2003/02/03

版本：1.06.00.0007

本文以SQL Server .Net managed data provider為準，若使用OleDB連接Database，可以在Class name用OleDb取代Sql。

本文不會詳述ADO.Net的使用方式，僅提供一brief introduction，詳細說明請自行參閱ADO.Net的OnLine Help。

ADO.Net-System.Data

DataSet

類似SQL Server的Database，DataSet中可以含DataTable，DataRelation。

DataTable

類似SQL Server的Table，DataTable上可以有DataView，DataTable與DataTable間可以有DataRelation。

DataTable可以有PrimaryKey。

當DataTable.NewRow時，DataTable中Columns的Default就會跑至新增DataRow中了。

當DataTable.Rows.Add(row)，ADO.Net會檢查該Row是否違反Constraint。

There are several events that are raised by the DataTable object when a change is occurring in a record: 

The ColumnChanging and ColumnChanged events are raised during and after each change to an individual column. The ColumnChanging event is useful when you want to validate changes in specific columns. Information about the proposed change is passed as an argument with the event. 
The RowChanging and RowChanged events are raised during and after any change in a row. The RowChanging event is more general, in that it simply indicates that a change is occurring somewhere in the row; you do not know which column has changed.  
By default, each change to a column therefore raises four events: first the ColumnChanging and ColumnChanged events for the specific column being changed, and then the RowChanging and RowChanged event. If multiple changes are being made to the row, the events will be raised for each change.

Note The data row's BeginEdit method turns off the RowChanging and RowChanged events after each individual column change. In that case, the event is not raised until the EndEdit method has been called, when the RowChanging and RowChanged events are raised just once. 
The event you choose depends on how granular you want the validation to be. If it is important that you catch an error immediately when a column is changed, build validation using the ColumnChanging event. Otherwise, use the RowChanging event, which might result in catching several errors at once. Additionally, if your data is structured in such a way that the value of one column is validated based on the contents of another column then you should perform your validation during the RowChanging event.

DataView

類似SQL Server的View，DataView依附於DataTable，本身不存資料，資料存在DataTable中。

DataRow

DataTable中的一筆資料。

有RowState決定目前Row是新增刪除或修改。

具有double data buffer的功能。

DataRowCollection類似ADO.Recordset

The DataRowVersion informs you what version of a DataRow exists. Versions change under the following circumstances:

• After calling the DataRow object's BeginEdit method, if you change the value, the Current and Proposed values become available.
• After calling the DataRow object's CancelEdit method, the Proposed value is deleted.
• After calling the DataRow object's EndEdit method, the Proposed value becomes the Current value.
• After calling the DataRow object's AcceptChanges method, the Original value becomes identical to the Current value.
• After calling the DataTable object's AcceptChanges method, the Original value becomes identical to the Current value.
• After calling the DataRow object's RejectChanges, the Proposed value is discarded, and the version becomes Current.

DataRowVersion

當DataRow.HasVersion(DataRowVersion.Proposed) == true時，DataRow["DataCol"]會與DataRow["DataCol",DataRowVersion.Proposed]相同。

當DataRow.HasVersion(DataRowVersion.Proposed) == false時，DataRow["DataCol"]會與DataRow["DataCol",DataRowVersion.Current]相同。

DataRowView

DataView中的一筆資料。

DataColumn

記載DataTable中的Column Schema。

有AutoIncrement Column類似SQL Server Identity的功能。

有計算欄位的功能。

當DataColumn被Assign到某Table後，該DataColumn的MaxLength便無法修改。

DataRelation

類似SQL Server的Foreign key constrain，描述著DataTable與DataTable之間的關係。

SQLClient-System.Data.SqlClient

SqlDataAdapter

ADO.Net與Database溝通的Class。可將SQL Server上的Table讀進DataTable，亦可將DataTable的異動寫回Database。

SqlConnection

類似ADO.Connection。

SqlParameter

類似ADO.Parameter。

SqlCommand

類似ADO.Command

在使用SqlCommand.ExecuteScalar(含IDbCommand.ExecuteScalar)時須注意，當SqlCommand.CommandText的語法錯誤時，會throw Exception，但是若僅僅是型別有錯，是不會throw Exception的，但是該SqlConnection會自動被Rollback，且ExecuteScalar return null。

例如：CaseMap.MapID在資料庫的型別為int

sqlConnection1.Open();
IDbCommand SQLCommand = new SqlCommand("select count(*) from CaseMap where MapID='rwerwe'",sqlConnection1);
SQLCommand.ExecuteScalar(); // 不會throw Exception
sqlConnection1.Close();

sqlConnection1.Open();
IDbCommand SQLCommand = new SqlCommand("select count(*) from CaseMap where1 MapID='1'",sqlConnection1); 
SQLCommand.ExecuteScalar();　// 會throw Exception
sqlConnection1.Close();

TQuarkLib.Net的TQuark.Lib.Data.DBHelp.ExecuteScalarMustHaveValue提供一Partial Solution。當IDbCommand.ExecuteScalar return null時，會throw exception，但是依然無法恢復Transaction的狀態，因為該Connection已經被sp_reset_connection了。

SqlDataReader

可自SQL Server一筆一筆資料讀取的class。

SqlDataAdapter.Update與Transaction的關係

當呼叫SqlDataAdapter.Update時，若發生Exception，因為Update是Process by Row by row的，理論上已經做完的Row其RowState應該已經AcceptChange。但是若後來SQL Rollback會造成State不符。

Read and Write BLOB Data by Using ADO.NET

待研究

TQuarkLib.Net的支援

DataHelp

提供一些有關DataSet，DataTable，DataView，DataRow間處理的utility。

DBHelp

提供一些有關DataSet，DataTable，DataView，DataRow與IDbCommand的處理utility。

SqlHelp

提供一些有關DataSet，DataTable，DataView，DataRow與SqlClient的處理utility。

DataSetTransactionControl

類似NestedTransactionControl，但是NestedTransactionControl是支援Connection，而DataSetTransactionControl是支援DataSet。

DataSetTransactionMonitor

類似DBTransactionMonitor，但是DBTransactionMonitor是支援Connection，而DataSetTransactionMonitor是支援DataSet。