[VB.NET] การ Import JSON เข้าสู่ตารางกริดและ Export ข้อมูลไป MS Access

thongkorn

Add References NewtonSoft.Json และ COM ที่จัดการไฟล์ฐานข้อมูล ...

เป็นโค้ดที่ต่อเนื่องจากครั้งที่แล้ว หลังจากที่โหลดข้อมูลรหัสไปรษณีย์มาจาก JSON นำมาใส่ลงใน DataGridView ต่อจากนั้นก็จะทำการ Export เข้าสู่ไฟล์ MS Access โดยการสร้างชื่อไฟล์ข้อมูล ตามด้วยการสร้างตารางและฟิลด์ข้อมูลตามลำดับ ด้วยการใช้โค้ด VB.NET ในแบบไดนามิค แต่เนื่องจากว่าจำนวนรายการข้อมูลมีอยู่เจ็ดพันกว่ารายการ ซึ่งในระดับ File Base จะต้องใช้การส่งออกไปแบบทีละรายการ ซึ่งแตกต่างไปจาก File Server ที่สามารถใช้การคัดลอกไปแบบชุดได้ด้วย BulkCopy ในกรณีนี้จะเกิดอาการหน้าจอค้าง เราจึงต้องใช้ BackGround Worker เข้ามาช่วย เพื่อโยนงานให้ไปทำงานอยู่เบื้องหลังแทน ...

มาดูโค้ดฉบับเต็มกันเถอะ ... ฟอร์มหลัก frmJsonPostCode2Access.vb ...

1. ' / ------------------------------------------------------------------------------------------------
   
2. '// Special Thank ... Original JSON data.
   
3. '// https://gist.github.com/mennwebs/8ff8e27a01fd06ca2ac965a1c7317552
   
4. ' / ------------------------------------------------------------------------------------------------
   
5. 
   
6. ' / ------------------------------------------------------------------------------------------------
   
7. '// Don't Forget Add References ... COM
   
8. '// Microsoft Ext. 6.0 for DLL and Security.
   
9. ' / ------------------------------------------------------------------------------------------------
   
10. 
    
11. Imports Newtonsoft.Json
    
12. Imports System.IO
    
13. Imports System.Data.OleDb
    
14. 
    
15. Public Class frmJsonPostCode2Access
    
16.     Private JsonSource As New BindingSource()
    
17. 
    
18.     Private Sub btnExport_Click(sender As System.Object, e As System.EventArgs) Handles btnExport.Click
    
19.         If dgvData.RowCount = 0 Then Return
    
20.         '// Create instance form and call to frmProcess for show ProgressBar working with BackgroundWorker.
    
21.         Dim frmProcess As New frmProcess
    
22.         frmProcess.AcceptFormMain(Me)
    
23.         frmProcess.ShowDialog()
    
24.     End Sub
    
25. 
    
26.     Private Sub frmJsonPostCode_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    
27.         '// JSON data as a string
    
28.         Dim json = File.ReadAllText(MyPath(Application.StartupPath) & "src\th-address.json")
    
29.         '// Deserialize JSON into a List(Of LocationData)
    
30.         Dim dataList As List(Of LocationData) = JsonConvert.DeserializeObject(Of List(Of LocationData))(json)
    
31.         Dim DT As New DataTable()
    
32.         '// Add Columns to DataTable.
    
33.         With DT.Columns
    
34.             .Add("zipCode", GetType(String))
    
35.             .Add("subDistrictId", GetType(String))
    
36.             .Add("subDistrictName", GetType(String))
    
37.             .Add("districtId", GetType(String))
    
38.             .Add("districtName", GetType(String))
    
39.             .Add("provinceId", GetType(String))
    
40.             .Add("provinceName", GetType(String))
    
41.         End With
    
42.         '// Loop for add row data to DataTable.
    
43.         Try
    
44.             For Each LocationData In dataList
    
45.                 For Each subDistrict In LocationData.subDistrictList
    
46.                     Dim row As DataRow = DT.NewRow()
    
47.                     row("zipCode") = LocationData.zipCode
    
48.                     row("subDistrictId") = subDistrict.subDistrictId
    
49.                     row("subDistrictName") = subDistrict.subDistrictName
    
50.                     '//
    
51.                     Dim matchingDistrict As District = Nothing '// Assuming District is the type of the elements in districtList.
    
52.                     Dim currentSubDistrict = subDistrict '// Declare a local variable.
    
53.                     '// Lambda Expression.
    
54.                     matchingDistrict = LocationData.districtList.FirstOrDefault(Function(d) d.districtId = currentSubDistrict.districtId)
    
55.                     If matchingDistrict IsNot Nothing Then
    
56.                         row("districtId") = matchingDistrict.districtId
    
57.                         row("districtName") = matchingDistrict.districtName
    
58.                     End If
    
59.                     '//
    
60.                     Dim matchingProvince As Province = Nothing
    
61.                     matchingProvince = LocationData.provinceList.FirstOrDefault(Function(p) p.provinceId = currentSubDistrict.provinceId)
    
62.                     If matchingProvince IsNot Nothing Then
    
63.                         row("provinceId") = matchingProvince.provinceId
    
64.                         row("provinceName") = matchingProvince.provinceName
    
65.                     End If
    
66.                     DT.Rows.Add(row)
    
67.                 Next
    
68.             Next
    
69.         Catch ex As Exception
    
70.             MessageBox.Show(ex.Message)
    
71.         End Try
    
72.         '// ReOrder DataTable.
    
73.         DT.DefaultView.Sort = "provinceName ASC, zipCode ASC, subDistrictName ASC, districtName ASC"
    
74.         DT = DT.DefaultView.ToTable
    
75.         '// Set DataTable as the DataSource for BindingSource.
    
76.         JsonSource.DataSource = DT
    
77.         '// Set BindingSource as the DataSource for DataGridView.
    
78.         dgvData.DataSource = JsonSource
    
79.         '// Remove some columns.
    
80.         With dgvData.Columns
    
81.             .Remove("subDistrictId")
    
82.             .Remove("districtId")
    
83.             .Remove("provinceId")
    
84.         End With
    
85.         '//
    
86.         With dgvData
    
87.             .Columns("zipCode").HeaderText = "รหัสไปรษณีย์"
    
88.             .Columns("subDistrictName").HeaderText = "ตำบล/แขวง"
    
89.             .Columns("districtName").HeaderText = "อำเภอ/เขต"
    
90.             .Columns("provinceName").HeaderText = "จังหวัด"
    
91.         End With
    
92.         Call SetupGridView()
    
93.         Me.lblRecordCount.Text = "Total: " & Format(dgvData.RowCount, "#,##") & " Records."
    
94.     End Sub
    
95. 
    
96.     ' / --------------------------------------------------------------------------------
    
97.     '// Filter data.
    
98.     ' / --------------------------------------------------------------------------------
    
99.     Private Sub txtFilterJson_TextChanged(sender As Object, e As System.EventArgs) Handles txtFilterJson.TextChanged
    
100.         If rdoFilterAll.Checked Then
     
101.             '// Filter for All.
     
102.             JsonSource.Filter = _
     
103.                 " provinceName LIKE " & "'%" & txtFilterJson.Text & "%'" & _
     
104.                 " OR districtName LIKE " & "'%" & txtFilterJson.Text & "%'" & _
     
105.                 " OR subDistrictName LIKE " & "'%" & txtFilterJson.Text & "%'" & _
     
106.                 " OR zipCode LIKE " & "'%" & txtFilterJson.Text & "%'"
     
107.             '// Filter ProvinceName.
     
108.         ElseIf rdoProvinceName.Checked Then
     
109.             JsonSource.Filter = "provinceName LIKE " & "'%" & txtFilterJson.Text & "%'"
     
110.             '// Filter DistrictName.
     
111.         ElseIf rdoDistrictName.Checked Then
     
112.             JsonSource.Filter = "districtName LIKE " & "'%" & txtFilterJson.Text & "%'"
     
113.             '// Filter SubDistrictName.
     
114.         ElseIf rdoSubDistrictName.Checked Then
     
115.             JsonSource.Filter = "subdistrictName LIKE " & "'%" & txtFilterJson.Text & "%'"
     
116.             '// Filter ZipCode.
     
117.         ElseIf rdoZipCode.Checked Then
     
118.             JsonSource.Filter = "zipCode LIKE " & "'%" & txtFilterJson.Text & "%'"
     
119.         End If
     
120.         '//
     
121.         Me.lblRecordCount.Text = "Total: " & Format(dgvData.RowCount, "#,##") & " Records."
     
122.     End Sub
     
123. 
     
124.     Private Sub rdoFilterAll_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles rdoFilterAll.CheckedChanged
     
125.         txtFilterJson.Focus()
     
126.     End Sub
     
127. 
     
128.     Private Sub rdoProvinceName_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles rdoProvinceName.CheckedChanged
     
129.         txtFilterJson.Focus()
     
130.     End Sub
     
131. 
     
132.     Private Sub rdoDistrictName_CheckedChanged(sender As Object, e As System.EventArgs) Handles rdoDistrictName.CheckedChanged
     
133.         txtFilterJson.Focus()
     
134.     End Sub
     
135. 
     
136.     Private Sub rdoSubDistrictName_CheckedChanged(sender As Object, e As System.EventArgs) Handles rdoSubDistrictName.CheckedChanged
     
137.         txtFilterJson.Focus()
     
138.     End Sub
     
139. 
     
140.     Private Sub rdoZipCode_CheckedChanged(sender As Object, e As System.EventArgs) Handles rdoZipCode.CheckedChanged
     
141.         txtFilterJson.Focus()
     
142.     End Sub
     
143. 
     
144.     Private Sub btnExit_Click(sender As System.Object, e As System.EventArgs) Handles btnExit.Click
     
145.         Me.Close()
     
146.     End Sub
     
147. 
     
148.     Private Sub frmJsonPostCode2Access_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
     
149.         Me.Dispose()
     
150.         GC.SuppressFinalize(Me)
     
151.         Application.Exit()
     
152.     End Sub
     
153. 
     
154. #Region "DATAGRIDVIEW"
     
155.     '// Initialized DataGridView.
     
156.     Private Sub SetupGridView()
     
157.         With dgvData
     
158.             .RowHeadersVisible = True
     
159.             .AllowUserToAddRows = False
     
160.             .AllowUserToDeleteRows = False
     
161.             .AllowUserToResizeRows = False
     
162.             .MultiSelect = False
     
163.             .SelectionMode = DataGridViewSelectionMode.FullRowSelect
     
164.             .ReadOnly = True
     
165.             '// Data rows
     
166.             .Font = New Font("Tahoma", 10)
     
167.             .RowTemplate.MinimumHeight = 27
     
168.             .RowTemplate.Height = 27
     
169.             '// Autosize Column
     
170.             .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
     
171.             '// Header
     
172.             With .ColumnHeadersDefaultCellStyle
     
173.                 .BackColor = Color.RoyalBlue
     
174.                 .ForeColor = Color.White
     
175.                 .Font = New Font(dgvData.Font, FontStyle.Bold)
     
176.             End With
     
177.             .ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing
     
178.             .ColumnHeadersHeight = 36
     
179.             '/ Accept changes to the header's background color.
     
180.             .EnableHeadersVisualStyles = False
     
181.             '// Even-Odd Color of Rows.
     
182.             .AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
     
183.             '// Even-Odd Color of Columns.
     
184.             For iCol As Integer = 0 To dgvData.Columns.Count - 1
     
185.                 '// If any integer Mod by 2 and gets the answer is 0 so even number, 1 is an odd number.
     
186.                 If iCol Mod 2 = 1 Then
     
187.                     dgvData.Columns(iCol).HeaderCell.Style.BackColor = Color.BlueViolet
     
188.                 Else
     
189.                     dgvData.Columns(iCol).HeaderCell.Style.BackColor = Color.SeaGreen
     
190.                 End If
     
191.             Next
     
192.         End With
     
193.     End Sub
     
194. #End Region
     
195. 
     
196. End Class
     
197. 
     
198. '// Define data model classes.
     
199. Public Class SubDistrict
     
200.     Public Property subDistrictId As String
     
201.     Public Property districtId As String
     
202.     Public Property provinceId As String
     
203.     Public Property subDistrictName As String
     
204. End Class
     
205. 
     
206. Public Class District
     
207.     Public Property districtId As String
     
208.     Public Property districtName As String
     
209.     Public Property provinceId As String
     
210. End Class
     
211. 
     
212. Public Class Province
     
213.     Public Property provinceId As String
     
214.     Public Property provinceName As String
     
215. End Class
     
216. 
     
217. Public Class LocationData
     
218.     Public Property zipCode As String
     
219.     Public Property subDistrictList As List(Of SubDistrict)
     
220.     Public Property districtList As List(Of District)
     
221.     Public Property provinceList As List(Of Province)
     
222. End Class

คัดลอกไปที่คลิปบอร์ด

โค้ดในส่วนของการโปรเซสเพื่อดึงข้อมูลเข้าสู่ MS Access ... frmProcess.vb ...

1. Imports System.ComponentModel
   
2. Imports System.Data.OleDb
   
3. Imports System.IO
   
4. 
   
5. Public Class frmProcess
   
6.     '// Instance Form for reference to frmJsonPostCode2Access.
   
7.     Private frmJson As frmJsonPostCode2Access
   
8.     '// Create BackgroundWorker.
   
9.     Private WithEvents BgWorker As New BackgroundWorker()
   
10.     Private Conn As New OleDbConnection
    
11.     '//
    
12.     Private DataBasePath As String
    
13. 
    
14.     Public Sub AcceptFormMain(frmMain As frmJsonPostCode2Access)
    
15.         frmJson = frmMain
    
16.     End Sub
    
17. 
    
18.     ' / ------------------------------------------------------------------------------------------------
    
19.     Private Sub frmProcess_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    
20.         Call CreateAccessFile()
    
21.         '// Initialized
    
22.         With BgWorker
    
23.             .WorkerReportsProgress = True
    
24.             .WorkerSupportsCancellation = True
    
25.         End With
    
26.         '//
    
27.         Control.CheckForIllegalCrossThreadCalls = False
    
28.         '// Handle events
    
29.         AddHandler BgWorker.DoWork, AddressOf BgWorker_DoWork
    
30.         AddHandler BgWorker.ProgressChanged, AddressOf BgWorker_ProgressChanged
    
31.         AddHandler BgWorker.RunWorkerCompleted, AddressOf BgWorker_RunWorkerCompleted
    
32.         '// Trigger the background worker when a button is clicked or when your task starts.
    
33.         BgWorker.RunWorkerAsync()
    
34.     End Sub
    
35. 
    
36.     ' / ------------------------------------------------------------------------------------------------
    
37.     Sub CreateAccessFile()
    
38.         '// Declare variable for Save File Dialog in Run Time.
    
39.         Dim dlgSaveFile As SaveFileDialog = New SaveFileDialog()
    
40.         '// Setting up Save File Dialog.
    
41.         With dlgSaveFile
    
42.             .InitialDirectory = MyPath(Application.StartupPath) & "data"
    
43.             .Title = "Create MS Access File"
    
44.             .Filter = "MS Access Files (*.accdb)|*.accdb"
    
45.             .FilterIndex = 1
    
46.             .RestoreDirectory = True
    
47.         End With
    
48.         Try
    
49.             '/ If you select the OK button after Browse ...
    
50.             If dlgSaveFile.ShowDialog() = DialogResult.OK Then
    
51.                 Dim strCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dlgSaveFile.FileName & ";"
    
52.                 Call CreateDatabase(dlgSaveFile.FileName)
    
53.                 '// Create a connection to the new database
    
54.                 Conn = New OleDbConnection(strCon)
    
55.                 Conn.Open()
    
56.                 '// Create a table and columns in the new database.
    
57.                 Call CreateTableColumn()
    
58.                 Conn.Close()
    
59.                 Conn = Nothing
    
60.                 '//
    
61.                 DataBasePath = dlgSaveFile.FileName
    
62.             End If
    
63.         Catch ex As Exception
    
64.             MessageBox.Show(ex.Message)
    
65.         End Try
    
66. 
    
67.     End Sub
    
68. 
    
69.     ' / ------------------------------------------------------------------------------------------------
    
70.     Private Sub CreateDatabase(DatabasePath As String)
    
71.         '// If the file exists, delete it.
    
72.         If File.Exists(DatabasePath) Then File.Delete(DatabasePath)
    
73.         '// Add COM Microsoft Ext. 6.0 for DLL and Security.
    
74.         '// Create the new Access database.
    
75.         Dim Cat As New ADOX.Catalog()
    
76.         Cat.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DatabasePath & ";Jet OLEDB:Engine Type=5")
    
77.         Cat = Nothing
    
78.     End Sub
    
79. 
    
80.     ' / ------------------------------------------------------------------------------------------------
    
81.     Private Sub CreateTableColumn()
    
82.         '// Create a table and Columns.
    
83.         Dim CreateTableSQL As String = _
    
84.             "CREATE TABLE PostCodeThailand (" &
    
85.             "PostCodeID SMALLINT PRIMARY KEY, " &
    
86.             "SubDistrictName TEXT(100), " &
    
87.             "DistrictName TEXT(100), " &
    
88.             "ProvinceName TEXT(100), " &
    
89.             "PostCode TEXT(5))"
    
90.         Dim Cmd As New OleDbCommand(CreateTableSQL, Conn)
    
91.         Cmd.ExecuteNonQuery()
    
92.     End Sub
    
93. 
    
94.     ' / ------------------------------------------------------------------------------------------------
    
95.     ' / Export to MS Access.
    
96.     ' / ------------------------------------------------------------------------------------------------
    
97.     Private Sub BgWorker_DoWork(sender As Object, e As DoWorkEventArgs)
    
98.         Dim Cmd As OleDbCommand
    
99.         Dim Statement As String
    
100.         Dim strConn As String = _
     
101.             " Provider=Microsoft.ACE.OLEDB.12.0;" & _
     
102.             " Data Source = " & DataBasePath & ";" & _
     
103.             " Persist Security Info=False;"
     
104.         Try
     
105.             'Opening the connection
     
106.             Conn = New OleDb.OleDbConnection(strConn)
     
107.             Conn.Open()
     
108.             ' / เก็บค่า Primary Key (PostCodeID)
     
109.             Dim iRow As Integer = 0
     
110.             Dim MaxValue As Int16 = frmJson.dgvData.RowCount
     
111.             ' / Collection of rows in the DataGridView control
     
112.             For Each row As DataGridViewRow In frmJson.dgvData.Rows
     
113.                 '// Store query to a variable(sql).
     
114.                 Statement = _
     
115.                     "INSERT INTO PostCodeThailand (PostCodeID, PostCode, SubDistrictName, DistrictName, ProvinceName ) VALUES (" _
     
116.                 & "'" & iRow + 1 & "','" _
     
117.                 & CStr(row.Cells(0).FormattedValue) & "','" _
     
118.                 & CStr(row.Cells(1).FormattedValue) & "','" _
     
119.                 & CStr(row.Cells(2).FormattedValue) & "','" _
     
120.                 & CStr(row.Cells(3).FormattedValue) & "')"
     
121.                 '// Set your SQL COMMANDS
     
122.                 Cmd = New OleDbCommand(Statement, Conn)
     
123.                 ' / Execute the Data
     
124.                 Cmd.ExecuteNonQuery()
     
125.                 ' / Next row
     
126.                 iRow = iRow + 1
     
127.                 '// ProgressBar Percentage.
     
128.                 Dim progressPercentage As Integer = CInt((iRow / MaxValue) * 100)
     
129.                 BgWorker.ReportProgress(progressPercentage)
     
130.                 lblProgress.Text = ProgressBar1.Value & "%"
     
131.                 lblCount.Text = iRow & "/" & MaxValue & " Records."
     
132.             Next
     
133.             Cmd = Nothing
     
134.             Conn.Close()
     
135.             Conn = Nothing
     
136.         Catch ex As Exception
     
137.             MessageBox.Show(ex.ToString)
     
138.         End Try
     
139.     End Sub
     
140. 
     
141.     Private Sub BgWorker_ProgressChanged(sender As Object, e As ProgressChangedEventArgs)
     
142.         '// Update the progress bar based on the reported progress.
     
143.         ProgressBar1.Value = e.ProgressPercentage
     
144.     End Sub
     
145. 
     
146.     Private Sub BgWorker_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs)
     
147.         '// Handle any post-processing after the background worker completes.
     
148.         MessageBox.Show("Done Complete.", "Report Status", MessageBoxButtons.OK, MessageBoxIcon.Information)
     
149.         Me.Dispose()
     
150.         Me.Close()
     
151.     End Sub
     
152. 
     
153. End Class

คัดลอกไปที่คลิปบอร์ด

โมดูลฟังค์ชั่น ... modFunction.vb ...

1. Module modFunction
   
2. 
   
3.     ' / --------------------------------------------------------------------------------
   
4.     ' / Get my project path
   
5.     ' / AppPath = C:\My Project\bin\debug
   
6.     ' / Replace "\bin\debug" with ""
   
7.     ' / Return : C:\My Project\
   
8.     Function MyPath(ByVal AppPath As String) As String
   
9.         '/ Return Value
   
10.         MyPath = AppPath.ToLower.Replace("\bin\debug", "").Replace("\bin\release", "").Replace("\bin\x86\debug", "")
    
11.         '// If not found folder then put the \ (BackSlash) at the end.
    
12.         If Microsoft.VisualBasic.Right(MyPath, 1) <> Chr(92) Then MyPath = MyPath & Chr(92)
    
13.     End Function
    
14. End Module

คัดลอกไปที่คลิปบอร์ด

ดาวน์โหลดโค้ดต้นฉบับ VB.NET (2010) ได้ที่นี่ ...