離線環境下的編輯作業-建立DataTable 物件

DataSet物件是SystemData命名空間中的一個物件，主要用途為提供應用程式一個於記憶體之中的資料儲存機制，其概念類似於資料庫之中的資料表。

<是否一定要將DataTable放入DataSet裡????>

• DataSet物件裡可加入DataTable物件，並利用DataRelation物件將兩個DataTable物件關聯在一起
• 亦可單獨使用DataTable，不涉及DataSet物件。

DataTable
-Rows

-DataRow1
-DataRow2

DataTable
-Columns

-DataColumn1
-DataColumn2

一、基本語法
1、建立DataTable物件

'宣告一個新的DataTable執行個體(於表單的全域範圍裡)
Private CustomersTable As New DataTable("Customers")

2、於DataSet加入DataTable

Private ds_Northwind As New DataSet("ds_Northwind")
Dim table_Customers As New DataTable("Customers")
ds_Northwind.Tables.Add(table_Customers)

3、於DataTable物件建立運算式資料行

• 運算式(Expression)資料行可用來為DataTable加入額外的資料行，做為儲存現有資料行的計算結果用。

Private Sub ExpressionButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExpressionButton.Click
'建立一個新的DataColumn並設定其名稱以及資料型態
Dim ds_Northwind1 As New ds_Northwind

Dim TotalPriceColumn As New DataColumn("TotalPrice", GetType(System.Double))
'在該欄位的Expression屬性設定指定的運算式
TotalPriceColumn.Expression = ("UnitPrice * Quantity")
ds_Northwind1.Order_Details.Columns.Add(TotalPriceColumn)

Dim da_Order_Details As New SqlDataAdapter("select * FROM dbo.[Order Details]", cn_SQL)
da_Order_Details.Fill(ds_Northwind1.Order_Details)
'將此欄位加入至DataTable之中

TableGrid.DataSource = ds_Northwind1.Order_Details

End Sub

4、於DataTable物件建立自動遞增的資料行

• 常見於訂單資料中必須是唯一(Unique)的但實際內容值為不重要的「訂單編號」(OrderID)。

'此欄位為自動遞增行
CustomersTable.Columns("CustomerID").AutoIncrement = True
'為自動遞增行指定起始值
CustomersTable.Columns("CustomerID").AutoIncrementSeed = 1
'指定每次遞增的數值
CustomersTable.Columns("CustomerID").AutoIncrementStep = 5

5、於DataTable物件加入條件約束

'建立ForeignKeyConstraint
Dim ds_Northwind As New NorthwindDataSet
Dim ForeignKey As New ForeignKeyConstraint(ds_Northwind.Customers.Columns("CustmoerID"), ds_Northwind.Orders.Columns("CustmoerID"))
ds_Northwind.Orders.Constraints.Add(ForeignKey)

6、如何建立唯一的條件約束

Dim ds_Northwind As New NorthwindDataSet
Dim Unique As New UniqueConstraint(ds_Northwind.Customers.Columns("CustomerID"))
ds_Northwind.Customers.Constraints.Add(Unique)

7、建立DataTable物件練習

• 畫面

• 程式原始碼
  

Imports System.data
Imports System.Data.SqlClient


Public Class frm_Ex4
'宣告一個新的DataTable執行個體(於表單的全域範圍裡)
Private CustomersTable As New DataTable("Customers")

Private Sub CreateTableButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateTableButton.Click
'設定DataGridView以顯示資料表
TableGrid.DataSource = CustomersTable

'建立DataColumns物件並加入資料表的Columns集合之中，為資料表定義資料結構
CustomersTable.Columns.Add("CustomerID", Type.GetType("System.String"))
CustomersTable.Columns.Add("CustomerName", Type.GetType("System.String"))
CustomersTable.Columns.Add("ContactName", Type.GetType("System.String"))
CustomersTable.Columns.Add("ContactTitle", Type.GetType("System.String"))
CustomersTable.Columns.Add("Address", Type.GetType("System.String"))
CustomersTable.Columns.Add("City", Type.GetType("System.String"))
CustomersTable.Columns.Add("Region", Type.GetType("System.String"))
CustomersTable.Columns.Add("PostalCode", Type.GetType("System.String"))
CustomersTable.Columns.Add("Country", Type.GetType("System.String"))
CustomersTable.Columns.Add("Phone", Type.GetType("System.String"))
CustomersTable.Columns.Add("Fax", Type.GetType("System.String"))

'將CustomerID資料行設定為主索引鍵
Dim KeyColumns(1) As DataColumn
KeyColumns(0) = CustomersTable.Columns("CustomerID")
CustomersTable.PrimaryKey = KeyColumns


'將CustomerID以及CompanyName資料行設定為不允許空值
CustomersTable.Columns("CustomerID").AllowDBNull = False
''於DataTable物件建立自動遞增的資料行
'CustomersTable.Columns("CustomerID").AutoIncrement = True
'CustomersTable.Columns("CustomerID").AutoIncrementSeed = 1
'CustomersTable.Columns("CustomerID").AutoIncrementStep = 5

CustomersTable.Columns("CustomerName").AllowDBNull = False


End Sub

Private Sub AddRowButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddRowButton.Click
'建立一筆新的資料列(DataRow)
Dim CusRow As DataRow = CustomersTable.NewRow
With CusRow
.Item("CustomerID") = "ALFKI"
.Item("CustomerName") = "Alfreds Futterkiste"
.Item("ContactName") = "Maria Anders"
.Item("ContactTitle") = "Sales Representative"
.Item("Address") = "Obere Str. 57"
.Item("City") = "Berlin"
.Item("Region") = "Nothing"
.Item("PostalCode") = "12209"
.Item("Country") = "Germany"
.Item("Phone") = "030-0074321"
.Item("Fax") = "030-0076546"
End With
'將新增的資料列加入至資料表之中
CustomersTable.Rows.Add(CusRow)

End Sub
Private WithEvents cn_SQL As New SqlConnection("Data Source=PC9611107;Initial Catalog=Northwind;Integrated Security = True")
Private Sub ExpressionButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExpressionButton.Click
'建立一個新的DataColumn並設定其名稱以及資料型態
Dim ds_Northwind1 As New ds_Northwind

Dim TotalPriceColumn As New DataColumn("TotalPrice", GetType(System.Double))
'在該欄位的Expression屬性設定指定的運算式
TotalPriceColumn.Expression = ("UnitPrice * Quantity")
ds_Northwind1.Order_Details.Columns.Add(TotalPriceColumn)

Dim da_Order_Details As New SqlDataAdapter("select * FROM dbo.[Order Details]", cn_SQL)
da_Order_Details.Fill(ds_Northwind1.Order_Details)
'將此欄位加入至DataTable之中

TableGrid.DataSource = ds_Northwind1.Order_Details
'Dim ds_Northwind As New NorthwindDataSet
'Dim Unique As New UniqueConstraint(ds_Northwind.Customers.Columns("CustomerID"))
'ds_Northwind.Customers.Constraints.Add(Unique)

End Sub
End Class