[VB.NET] การ Import JSON เข้าสู่ตารางกริดและ Export ข้อมูลไป MS Access
http://www.g2gsoft.com/webboard/images/VBNet/json2access.pnghttp://www.g2gsoft.com/webboard/images/VBNet/jsonnewtoncom.png
Add References NewtonSoft.Json และ COM ที่จัดการไฟล์ฐานข้อมูล ...
เป็นโค้ดที่ต่อเนื่องจากครั้งที่แล้ว หลังจากที่โหลดข้อมูลรหัสไปรษณีย์มาจาก JSON นำมาใส่ลงใน DataGridView ต่อจากนั้นก็จะทำการ Export เข้าสู่ไฟล์ MS Access โดยการสร้างชื่อไฟล์ข้อมูล ตามด้วยการสร้างตารางและฟิลด์ข้อมูลตามลำดับ ด้วยการใช้โค้ด VB.NET ในแบบไดนามิค แต่เนื่องจากว่าจำนวนรายการข้อมูลมีอยู่เจ็ดพันกว่ารายการ ซึ่งในระดับ File Base จะต้องใช้การส่งออกไปแบบทีละรายการ ซึ่งแตกต่างไปจาก File Server ที่สามารถใช้การคัดลอกไปแบบชุดได้ด้วย BulkCopy ในกรณีนี้จะเกิดอาการหน้าจอค้าง เราจึงต้องใช้ BackGround Worker เข้ามาช่วย เพื่อโยนงานให้ไปทำงานอยู่เบื้องหลังแทน ...
มาดูโค้ดฉบับเต็มกันเถอะ ... ฟอร์มหลัก frmJsonPostCode2Access.vb ...
' / ------------------------------------------------------------------------------------------------
'// Special Thank ... Original JSON data.
'// https://gist.github.com/mennwebs/8ff8e27a01fd06ca2ac965a1c7317552
' / ------------------------------------------------------------------------------------------------
' / ------------------------------------------------------------------------------------------------
'// Don't Forget Add References ... COM
'// Microsoft Ext. 6.0 for DLL and Security.
' / ------------------------------------------------------------------------------------------------
Imports Newtonsoft.Json
Imports System.IO
Imports System.Data.OleDb
Public Class frmJsonPostCode2Access
Private JsonSource As New BindingSource()
Private Sub btnExport_Click(sender As System.Object, e As System.EventArgs) Handles btnExport.Click
If dgvData.RowCount = 0 Then Return
'// Create instance form and call to frmProcess for show ProgressBar working with BackgroundWorker.
Dim frmProcess As New frmProcess
frmProcess.AcceptFormMain(Me)
frmProcess.ShowDialog()
End Sub
Private Sub frmJsonPostCode_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
'// JSON data as a string
Dim json = File.ReadAllText(MyPath(Application.StartupPath) & "src\th-address.json")
'// Deserialize JSON into a List(Of LocationData)
Dim dataList As List(Of LocationData) = JsonConvert.DeserializeObject(Of List(Of LocationData))(json)
Dim DT As New DataTable()
'// Add Columns to DataTable.
With DT.Columns
.Add("zipCode", GetType(String))
.Add("subDistrictId", GetType(String))
.Add("subDistrictName", GetType(String))
.Add("districtId", GetType(String))
.Add("districtName", GetType(String))
.Add("provinceId", GetType(String))
.Add("provinceName", GetType(String))
End With
'// Loop for add row data to DataTable.
Try
For Each LocationData In dataList
For Each subDistrict In LocationData.subDistrictList
Dim row As DataRow = DT.NewRow()
row("zipCode") = LocationData.zipCode
row("subDistrictId") = subDistrict.subDistrictId
row("subDistrictName") = subDistrict.subDistrictName
'//
Dim matchingDistrict As District = Nothing '// Assuming District is the type of the elements in districtList.
Dim currentSubDistrict = subDistrict '// Declare a local variable.
'// Lambda Expression.
matchingDistrict = LocationData.districtList.FirstOrDefault(Function(d) d.districtId = currentSubDistrict.districtId)
If matchingDistrict IsNot Nothing Then
row("districtId") = matchingDistrict.districtId
row("districtName") = matchingDistrict.districtName
End If
'//
Dim matchingProvince As Province = Nothing
matchingProvince = LocationData.provinceList.FirstOrDefault(Function(p) p.provinceId = currentSubDistrict.provinceId)
If matchingProvince IsNot Nothing Then
row("provinceId") = matchingProvince.provinceId
row("provinceName") = matchingProvince.provinceName
End If
DT.Rows.Add(row)
Next
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'// ReOrder DataTable.
DT.DefaultView.Sort = "provinceName ASC, zipCode ASC, subDistrictName ASC, districtName ASC"
DT = DT.DefaultView.ToTable
'// Set DataTable as the DataSource for BindingSource.
JsonSource.DataSource = DT
'// Set BindingSource as the DataSource for DataGridView.
dgvData.DataSource = JsonSource
'// Remove some columns.
With dgvData.Columns
.Remove("subDistrictId")
.Remove("districtId")
.Remove("provinceId")
End With
'//
With dgvData
.Columns("zipCode").HeaderText = "รหัสไปรษณีย์"
.Columns("subDistrictName").HeaderText = "ตำบล/แขวง"
.Columns("districtName").HeaderText = "อำเภอ/เขต"
.Columns("provinceName").HeaderText = "จังหวัด"
End With
Call SetupGridView()
Me.lblRecordCount.Text = "Total: " & Format(dgvData.RowCount, "#,##") & " Records."
End Sub
' / --------------------------------------------------------------------------------
'// Filter data.
' / --------------------------------------------------------------------------------
Private Sub txtFilterJson_TextChanged(sender As Object, e As System.EventArgs) Handles txtFilterJson.TextChanged
If rdoFilterAll.Checked Then
'// Filter for All.
JsonSource.Filter = _
" provinceName LIKE " & "'%" & txtFilterJson.Text & "%'" & _
" OR districtName LIKE " & "'%" & txtFilterJson.Text & "%'" & _
" OR subDistrictName LIKE " & "'%" & txtFilterJson.Text & "%'" & _
" OR zipCode LIKE " & "'%" & txtFilterJson.Text & "%'"
'// Filter ProvinceName.
ElseIf rdoProvinceName.Checked Then
JsonSource.Filter = "provinceName LIKE " & "'%" & txtFilterJson.Text & "%'"
'// Filter DistrictName.
ElseIf rdoDistrictName.Checked Then
JsonSource.Filter = "districtName LIKE " & "'%" & txtFilterJson.Text & "%'"
'// Filter SubDistrictName.
ElseIf rdoSubDistrictName.Checked Then
JsonSource.Filter = "subdistrictName LIKE " & "'%" & txtFilterJson.Text & "%'"
'// Filter ZipCode.
ElseIf rdoZipCode.Checked Then
JsonSource.Filter = "zipCode LIKE " & "'%" & txtFilterJson.Text & "%'"
End If
'//
Me.lblRecordCount.Text = "Total: " & Format(dgvData.RowCount, "#,##") & " Records."
End Sub
Private Sub rdoFilterAll_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles rdoFilterAll.CheckedChanged
txtFilterJson.Focus()
End Sub
Private Sub rdoProvinceName_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles rdoProvinceName.CheckedChanged
txtFilterJson.Focus()
End Sub
Private Sub rdoDistrictName_CheckedChanged(sender As Object, e As System.EventArgs) Handles rdoDistrictName.CheckedChanged
txtFilterJson.Focus()
End Sub
Private Sub rdoSubDistrictName_CheckedChanged(sender As Object, e As System.EventArgs) Handles rdoSubDistrictName.CheckedChanged
txtFilterJson.Focus()
End Sub
Private Sub rdoZipCode_CheckedChanged(sender As Object, e As System.EventArgs) Handles rdoZipCode.CheckedChanged
txtFilterJson.Focus()
End Sub
Private Sub btnExit_Click(sender As System.Object, e As System.EventArgs) Handles btnExit.Click
Me.Close()
End Sub
Private Sub frmJsonPostCode2Access_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
Me.Dispose()
GC.SuppressFinalize(Me)
Application.Exit()
End Sub
#Region "DATAGRIDVIEW"
'// Initialized DataGridView.
Private Sub SetupGridView()
With dgvData
.RowHeadersVisible = True
.AllowUserToAddRows = False
.AllowUserToDeleteRows = False
.AllowUserToResizeRows = False
.MultiSelect = False
.SelectionMode = DataGridViewSelectionMode.FullRowSelect
.ReadOnly = True
'// Data rows
.Font = New Font("Tahoma", 10)
.RowTemplate.MinimumHeight = 27
.RowTemplate.Height = 27
'// Autosize Column
.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
'// Header
With .ColumnHeadersDefaultCellStyle
.BackColor = Color.RoyalBlue
.ForeColor = Color.White
.Font = New Font(dgvData.Font, FontStyle.Bold)
End With
.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing
.ColumnHeadersHeight = 36
'/ Accept changes to the header's background color.
.EnableHeadersVisualStyles = False
'// Even-Odd Color of Rows.
.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
'// Even-Odd Color of Columns.
For iCol As Integer = 0 To dgvData.Columns.Count - 1
'// If any integer Mod by 2 and gets the answer is 0 so even number, 1 is an odd number.
If iCol Mod 2 = 1 Then
dgvData.Columns(iCol).HeaderCell.Style.BackColor = Color.BlueViolet
Else
dgvData.Columns(iCol).HeaderCell.Style.BackColor = Color.SeaGreen
End If
Next
End With
End Sub
#End Region
End Class
'// Define data model classes.
Public Class SubDistrict
Public Property subDistrictId As String
Public Property districtId As String
Public Property provinceId As String
Public Property subDistrictName As String
End Class
Public Class District
Public Property districtId As String
Public Property districtName As String
Public Property provinceId As String
End Class
Public Class Province
Public Property provinceId As String
Public Property provinceName As String
End Class
Public Class LocationData
Public Property zipCode As String
Public Property subDistrictList As List(Of SubDistrict)
Public Property districtList As List(Of District)
Public Property provinceList As List(Of Province)
End Class
โค้ดในส่วนของการโปรเซสเพื่อดึงข้อมูลเข้าสู่ MS Access ... frmProcess.vb ...
Imports System.ComponentModel
Imports System.Data.OleDb
Imports System.IO
Public Class frmProcess
'// Instance Form for reference to frmJsonPostCode2Access.
Private frmJson As frmJsonPostCode2Access
'// Create BackgroundWorker.
Private WithEvents BgWorker As New BackgroundWorker()
Private Conn As New OleDbConnection
'//
Private DataBasePath As String
Public Sub AcceptFormMain(frmMain As frmJsonPostCode2Access)
frmJson = frmMain
End Sub
' / ------------------------------------------------------------------------------------------------
Private Sub frmProcess_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Call CreateAccessFile()
'// Initialized
With BgWorker
.WorkerReportsProgress = True
.WorkerSupportsCancellation = True
End With
'//
Control.CheckForIllegalCrossThreadCalls = False
'// Handle events
AddHandler BgWorker.DoWork, AddressOf BgWorker_DoWork
AddHandler BgWorker.ProgressChanged, AddressOf BgWorker_ProgressChanged
AddHandler BgWorker.RunWorkerCompleted, AddressOf BgWorker_RunWorkerCompleted
'// Trigger the background worker when a button is clicked or when your task starts.
BgWorker.RunWorkerAsync()
End Sub
' / ------------------------------------------------------------------------------------------------
Sub CreateAccessFile()
'// Declare variable for Save File Dialog in Run Time.
Dim dlgSaveFile As SaveFileDialog = New SaveFileDialog()
'// Setting up Save File Dialog.
With dlgSaveFile
.InitialDirectory = MyPath(Application.StartupPath) & "data\"
.Title = "Create MS Access File"
.Filter = "MS Access Files (*.accdb)|*.accdb"
.FilterIndex = 1
.RestoreDirectory = True
End With
Try
'/ If you select the OK button after Browse ...
If dlgSaveFile.ShowDialog() = DialogResult.OK Then
Dim strCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dlgSaveFile.FileName & ";"
Call CreateDatabase(dlgSaveFile.FileName)
'// Create a connection to the new database
Conn = New OleDbConnection(strCon)
Conn.Open()
'// Create a table and columns in the new database.
Call CreateTableColumn()
Conn.Close()
Conn = Nothing
'//
DataBasePath = dlgSaveFile.FileName
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
' / ------------------------------------------------------------------------------------------------
Private Sub CreateDatabase(DatabasePath As String)
'// If the file exists, delete it.
If File.Exists(DatabasePath) Then File.Delete(DatabasePath)
'// Add COM Microsoft Ext. 6.0 for DLL and Security.
'// Create the new Access database.
Dim Cat As New ADOX.Catalog()
Cat.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DatabasePath & ";Jet OLEDB:Engine Type=5")
Cat = Nothing
End Sub
' / ------------------------------------------------------------------------------------------------
Private Sub CreateTableColumn()
'// Create a table and Columns.
Dim CreateTableSQL As String = _
"CREATE TABLE PostCodeThailand (" &
"PostCodeID SMALLINT PRIMARY KEY, " &
"SubDistrictName TEXT(100), " &
"DistrictName TEXT(100), " &
"ProvinceName TEXT(100), " &
"PostCode TEXT(5))"
Dim Cmd As New OleDbCommand(CreateTableSQL, Conn)
Cmd.ExecuteNonQuery()
End Sub
' / ------------------------------------------------------------------------------------------------
' / Export to MS Access.
' / ------------------------------------------------------------------------------------------------
Private Sub BgWorker_DoWork(sender As Object, e As DoWorkEventArgs)
Dim Cmd As OleDbCommand
Dim Statement As String
Dim strConn As String = _
" Provider=Microsoft.ACE.OLEDB.12.0;" & _
" Data Source = " & DataBasePath & ";" & _
" Persist Security Info=False;"
Try
'Opening the connection
Conn = New OleDb.OleDbConnection(strConn)
Conn.Open()
' / เก็บค่า Primary Key (PostCodeID)
Dim iRow As Integer = 0
Dim MaxValue As Int16 = frmJson.dgvData.RowCount
' / Collection of rows in the DataGridView control
For Each row As DataGridViewRow In frmJson.dgvData.Rows
'// Store query to a variable(sql).
Statement = _
"INSERT INTO PostCodeThailand (PostCodeID, PostCode, SubDistrictName, DistrictName, ProvinceName ) VALUES (" _
& "'" & iRow + 1 & "','" _
& CStr(row.Cells(0).FormattedValue) & "','" _
& CStr(row.Cells(1).FormattedValue) & "','" _
& CStr(row.Cells(2).FormattedValue) & "','" _
& CStr(row.Cells(3).FormattedValue) & "')"
'// Set your SQL COMMANDS
Cmd = New OleDbCommand(Statement, Conn)
' / Execute the Data
Cmd.ExecuteNonQuery()
' / Next row
iRow = iRow + 1
'// ProgressBar Percentage.
Dim progressPercentage As Integer = CInt((iRow / MaxValue) * 100)
BgWorker.ReportProgress(progressPercentage)
lblProgress.Text = ProgressBar1.Value & "%"
lblCount.Text = iRow & "/" & MaxValue & " Records."
Next
Cmd = Nothing
Conn.Close()
Conn = Nothing
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub BgWorker_ProgressChanged(sender As Object, e As ProgressChangedEventArgs)
'// Update the progress bar based on the reported progress.
ProgressBar1.Value = e.ProgressPercentage
End Sub
Private Sub BgWorker_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs)
'// Handle any post-processing after the background worker completes.
MessageBox.Show("Done Complete.", "Report Status", MessageBoxButtons.OK, MessageBoxIcon.Information)
Me.Dispose()
Me.Close()
End Sub
End Class
โมดูลฟังค์ชั่น ... modFunction.vb ...
Module modFunction
' / --------------------------------------------------------------------------------
' / Get my project path
' / AppPath = C:\My Project\bin\debug
' / Replace "\bin\debug" with "\"
' / Return : C:\My Project\
Function MyPath(ByVal 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 Module
ดาวน์โหลดโค้ดต้นฉบับ VB.NET (2010) ได้ที่นี่ ...
หน้า:
[1]