DataSet Tips

DataSet 是一個儲存在記憶體內的資料庫，它擁有很多類似資料庫的操作功能，例如：

1. 兩表合併(Merge Table)
Table1.Merge(Table2, False)
有時候，這個方法會因為資料結構造成失敗，此時可改用DataRelation，將兩個Table Join。
Dim mDataRelation As New DataRelation("TempJoin", Table2Columns, Table1Columns, False)
ds.Relations.Add(mDataRelation)
' jj 是Table2填入Table1的起始欄位
For Each mRow As DataRow In Table1.Rows
For ii = jj To Table2.Columns.Count - 1
mRow(jj+ii) = mRow.GetParentRow("TempJoin")(ii)
Next
Next

2. 排序(Sort)
Table1.DefaultView.Sort(Field1)
For i = 0 To Table1.DefaultView.Count - 1
aa=Table1.DefaultView(i).Item("Field2")
next

3. 篩選(Filter)
Dim rows As DataRow() = dtTopicid.Select("Field1='aaa'")

4. 衍生欄位(Derived Field)
Dim col As DataColumn
col = dt.Columns.Add("ExpCol", GetType(Boolean), expr)



另外，還有一些小技巧，與大家分享：
1. 欄位值大小寫有區別(Case Sensitive)
Table1.CaseSensitive = True

2. 設定額外屬性(Custom Property)
column.ExtendedProperties.Add("TimeStamp", DateTime.Now)

3. 取唯一值(Unique Values)
Dim dtDistinct As DataTable = Table1.DefaultView.ToTable("Table1", True, "Field1")

Move Column position in Data Table

Q: How to Insert Data Column in a specified position, instead of appending to last column?
A: DataTable.Columns.Add does not support it. We need to adjust the position after adding the column. The sample code is listed as below:

 
    Dim mColumn As New DataColumn("Column1")
    Table1.Columns.Add(mColumn)
    mColumn.SetOrdinal(0)