[VB.NET] โค้ดตัวอย่างของการนำชุดข้อมูลหลายชุดทั้ง Table หรือ Query ใส่ไว้ใน DataSet ตัวเดียว
http://www.g2gsoft.com/webboard/images/VBNet/dataset.pngโค้ดตัวอย่างซึ่งแอดมินคิดว่าน่าจะเพิ่มความเข้าใจได้ดีขึ้นระหว่างคำว่า DataSet กับ DataTable เพราะว่าเราสามารถนำชุดข้อมูลหลายชุดทั้ง Table หรือ Query ใส่ไว้ใน DataSet เพียงตัวเดียวได้ ส่วน DataTable จะใส่ได้เพียงทีละ 1 Table หรือ 1 Query เท่านั้น ...
มาดูโค้ดฉบับเต็มกันเถอะ ...
Public Class frmMultipleDataSet
Private Conn As New OleDbConnection
'// Data Path
Private strPathData As String = MyPath(Application.StartupPath) & "Data\"
Dim MyDataSet As New DataSet()
' / ------------------------------------------------------------------------------------
' / S T A R T ... H E R E
' / ------------------------------------------------------------------------------------
Private Sub frmMultipleDataSet_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Call ConnectDataBase()'// Connect Northwind.accdb DataBase.
Call InitialDataGridView()'// Initialized DataGridView one time for DataSet.
'//
Dim Tables(4) As String '// (Indexs 0 - 4 of 5 Tables)
Tables(0) = "customers" : Tables(1) = "employees" : Tables(2) = "products" : Tables(3) = "suppliers" : Tables(4) = "orders"
MyDataSet = GetDataSet(Tables)
'// Tables in DataSet.
With cmbDataSet
.Items.Add("Customers")
.Items.Add("Employees")
.Items.Add("Products")
.Items.Add("Suppliers")
.Items.Add("Orders")
.Items.Add("Sample Query")
End With
cmbDataSet.SelectedIndex = 0
'// DataTable.
With cmbDataTable
.Items.Add("Customers")
.Items.Add("Employees")
.Items.Add("Products")
.Items.Add("Suppliers")
.Items.Add("Orders")
.Items.Add("Sample Query")
End With
cmbDataTable.SelectedIndex = 0
End Sub
' / ------------------------------------------------------------------------------------
'// Multiple Tables/Query in DataSet.
' / ------------------------------------------------------------------------------------
Private Function GetDataSet(ByRef Tables() As String) As System.Data.DataSet
If Conn.State = ConnectionState.Closed Then Conn.Open()
Dim Cmd As New OleDbCommand()
Cmd.Connection = Conn
Cmd.CommandType = System.Data.CommandType.Text
Dim DA As OleDbDataAdapter = New OleDbDataAdapter(Cmd)
Dim DS As DataSet = New DataSet()
'// Fill data from many tables or queries into DataSet object.
For intCount As Byte = 0 To Tables.GetUpperBound(0)
Cmd.CommandText = "SELECT * FROM " & Tables(intCount)
DA.Fill(DS, Tables(intCount))
Next
'// Add new sample query. (6 Tables in 1 DataSet)
Dim sql As String = _
" SELECT Orders.OrderID, Customers.CustomerID, Customers.CompanyName, Orders.OrderDate, " & _
" Products.ProductName, .Quantity, Products.UnitPrice " & _
" FROM Products INNER JOIN (Customers INNER JOIN (Orders INNER JOIN ON " & _
" Orders.OrderID = .OrderID) ON Customers.CustomerID = Orders.CustomerID) ON " & _
" Products.ProductID = .ProductID"
Cmd.CommandText = sql
DA.Fill(DS, "SampleQuery")'// Also give the new data table a name.
'//
Conn.Close()
Return DS
End Function
' / ------------------------------------------------------------------------------------
' / Select Table/Query in DataSet.
' / ------------------------------------------------------------------------------------
Private Sub cmbDataSet_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbDataSet.SelectedIndexChanged
Select Case cmbDataSet.SelectedIndex
Case 0
dgvData.DataSource = MyDataSet.Tables("customers").DefaultView
Case 1
dgvData.DataSource = MyDataSet.Tables("employees").DefaultView
Case 2
dgvData.DataSource = MyDataSet.Tables("products").DefaultView
Case 3
dgvData.DataSource = MyDataSet.Tables("suppliers").DefaultView
Case 4
dgvData.DataSource = MyDataSet.Tables("orders").DefaultView
Case 5
dgvData.DataSource = MyDataSet.Tables("SampleQuery").DefaultView
End Select
End Sub
' / ------------------------------------------------------------------------------------
' / Sample for DataSet to DataTable.
' / ------------------------------------------------------------------------------------
Private Sub btnDataSetToDataTable_Click(sender As System.Object, e As System.EventArgs) Handles btnDataSetToDataTable.Click
'// DataTable can contain only 1 table.
Dim DT As New DataTable
Select Case cmbDataTable.SelectedIndex
Case 0
DT = MyDataSet.Tables(0)
dgvData.DataSource = DT
Case 1
DT = MyDataSet.Tables(1)
dgvData.DataSource = DT
Case 2
DT = MyDataSet.Tables(2)
dgvData.DataSource = DT
Case 3
DT = MyDataSet.Tables(3)
dgvData.DataSource = DT
Case 4
DT = MyDataSet.Tables(4)
dgvData.DataSource = DT
'// Query.
Case 5
'DT = MyDataSet.Tables(5)
'// Or use the name of Table.
DT = MyDataSet.Tables("SampleQuery")
'//
If dgvData.Rows.Count > 0 Then dgvData.DataSource = Nothing
'// If there is a column named Total, delete it from the DataTable.
If DT.Columns.Contains("Total") Then DT.Columns.Remove("Total")
'// Add a new column to calculate the sum of the Quantity X UnitPrice.
DT.Columns.Add("Total", GetType(Double))
DT.Columns("Total").Expression = " * "
'// An example of how to customize the display of DataGridView with DataTable.
dgvData.DataSource = DT
With dgvData
.Columns(0).HeaderText = "Order ID"
.Columns(1).HeaderText = "Customer ID"
.Columns(2).HeaderText = "Company Name"
.Columns(3).HeaderText = "Order Date"
.Columns(4).HeaderText = "Product Name"
.Columns(5).HeaderText = "Quantity"
With .Columns(6)
.HeaderText = "Unit Price"
.DefaultCellStyle.Format = "0.00"
.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight
.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
End With
'// Columns(7) and Columns("Total") is the same.
.Columns(7).HeaderText = "Total"
.Columns("Total").DefaultCellStyle.Format = "N2"
.Columns(7).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight
.Columns("Total").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
End With
End Select
End Sub
' / ------------------------------------------------------------------------------------
Sub InitialDataGridView()
With dgvData
.RowHeadersVisible = False
.AllowUserToAddRows = False
.AllowUserToDeleteRows = False
.AllowUserToResizeRows = False
.MultiSelect = False
.SelectionMode = DataGridViewSelectionMode.FullRowSelect
.ReadOnly = True
' Autosize Column Mode.
.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
.RowTemplate.DefaultCellStyle.Font = New Font("Tahoma", 11, FontStyle.Regular)
.RowTemplate.Height = 32
'// Set ColumnHeadersHeightSizeMode before adjusting row heights.
.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
.ColumnHeadersHeight = 28
'// Set EnableHeadersVisualStyles = False to accept background color changes.
.EnableHeadersVisualStyles = False
'// Even-Odd Color
.AlternatingRowsDefaultCellStyle.BackColor = Color.LightYellow ' .AliceBlue
'// Example of adjusting Header Style.
With .ColumnHeadersDefaultCellStyle
.BackColor = Color.Orange
.ForeColor = Color.Black
.Font = New Font("Tahoma", 11, FontStyle.Bold)
End With
End With
End Sub
Private Sub btnExit_Click(sender As System.Object, e As System.EventArgs) Handles btnExit.Click
Me.Close()
End Sub
Private Sub frmMultipleDataSet_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
Conn.Close()
Me.Dispose()
GC.SuppressFinalize(Me)
Application.Exit()
End Sub
#Region "FUNCTION"
' / --------------------------------------------------------------------------------
' / Get my project path
' / AppPath = C:\My Project\bin\debug
' / Replace "\bin\debug" with "\"
' / Return : C:\My Project\
Function MyPath(AppPath As String) As String
'/ Return Value
MyPath = AppPath.ToLower.Replace("\bin\debug", "\").Replace("\bin\release", "\").Replace("\bin\x86\debug", "\")
'// If not found folder then put the \ (BackSlash) at the end.
If Microsoft.VisualBasic.Right(MyPath, 1) <> Chr(92) Then MyPath = MyPath & Chr(92)
End Function
#End Region
#Region "CONNECTDATABASE"
Public Function ConnectDataBase() As Boolean
Dim strConn As String = _
"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " & strPathData & "Northwind.accdb"
Try
Conn = New OleDb.OleDbConnection(strConn)
'// Create Connection
Conn.ConnectionString = strConn
Conn.Open()
'// Return
Return True
Catch ex As Exception
MessageBox.Show(ex.Message)
Conn = Nothing
Return False
'End
End Try
End Function
#End Region
End Class
ดาวน์โหลดโค้ดต้นฉบับ VB.NET (2010) ได้ที่นี่ ...
ขอบคุณครับ อาจารย์
หน้า:
[1]