Author：水如烟
以上所为在数据库做好了主体表及增加数据的存储过程。这次重新回到程序，目的是将数据导进数据库里面，因为在后面的数据库附属表的设计要用到这些数据，要边调试边设计。
现在的方案组织如下：
效果图：
增加的代码：
Constant.vb
Namespace Database
Friend Class Constant
Private Sub New()
End Sub
Public Const CONNECTIONSTRING As String = "Database=RegionalCodeWorks;Server=.\SQLEXPRESS;Integrated Security=SSPI;"
Public Const PROCEDURE_ADDRECORD As String = "Program.增添记录"
End Class
End Namespace
UpdateDatabase.vb
Namespace Database
Public Class UpdateDatabase
Public Event UpdateCompleted(ByVal sender As Object, ByVal msg As String)
Private gCommand As SqlClient.SqlCommand
Sub New()
gCommand = GetCommand()
End Sub
Public Sub OpenConnection()
With gCommand.Connection
If .State <> ConnectionState.Open Then .Open()
End With
End Sub
Public Sub CloseConnection()
With gCommand.Connection
If .State = ConnectionState.Open Then .Close()
End With
End Sub
Public Sub Import(ByVal table As DataTable, ByVal lastdate As String)
Dim mCode As String
Dim mName As String
For Each row As DataRow In table.Rows
mCode = row.Item("Code").ToString
mName = row.Item("Name").ToString
ImportRecord(mCode, mName, lastdate)
Next
End Sub
Private Sub ImportRecord(ByVal code As String, ByVal name As String, ByVal lastdate As String)
With gCommand
.Parameters.Item("@区划码").Value = code
.Parameters.Item("@名称").Value = name
.Parameters.Item("@起始日期").Value = lastdate
.ExecuteNonQuery()
End With
RaiseEvent UpdateCompleted(Nothing, String.Format("已处理 区划码:{0} 名称:{1}", code, name))
End Sub
Private Function GetCommand() As SqlClient.SqlCommand
Dim mCommand As New SqlClient.SqlCommand
With mCommand
.Connection = New SqlClient.SqlConnection(Constant.CONNECTIONSTRING)
.CommandText = Constant.PROCEDURE_ADDRECORD
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@区划码", SqlDbType.NChar, 6)
.Parameters.Add("@名称", SqlDbType.NVarChar, 100)
.Parameters.Add("@起始日期", SqlDbType.NChar, 8)
End With
Return mCommand
End Function
Public Sub Dispose()
Me.CloseConnection()
Me.gCommand.Connection.Dispose()
Me.gCommand.Dispose()
Me.gCommand = Nothing
End Sub
End Class
End Namespace
测试窗体现在的代码：
Public Class MainForm
Private gNetInformation As New RegionalCodeLibrary.NET.NetInformation
Private gQueryTable As RegionalCodeLibrary.NET.ExcelQueryTable
''
Private WithEvents gUpdateDatabase As New RegionalCodeLibrary.Database.UpdateDatabase
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If Not CheckNetworkIsAvailable() Then Exit Sub
ShowMessage("正在下载数据信息...")
gNetInformation.DownloadInformationsFromNet()
With Me.ComboBox1
.DataSource = gNetInformation.UpdateInformationsTable
.DisplayMember = "LastDate"
End With
ShowMessage("")
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
If String.IsNullOrEmpty(Me.ComboBox1.Text) Then Exit Sub
If Not CheckNetworkIsAvailable() Then Exit Sub
If gQueryTable Is Nothing Then
ShowMessage("正在启动Excel...")
gQueryTable = New RegionalCodeLibrary.NET.ExcelQueryTable
End If
Dim mAddress As String = CType(Me.ComboBox1.SelectedItem, DataRowView).Row.Item("Address").ToString
ShowMessage(String.Format("正在下载{0}数据...", Me.ComboBox1.Text))
Me.DataGridView1.DataSource = gQueryTable.Query(mAddress)
ShowMessage(String.Format("{0}共有数据{1}项", Me.ComboBox1.Text, Me.DataGridView1.RowCount))
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
ClearEnvironment()
End Sub
Private Function CheckNetworkIsAvailable() As Boolean
Dim mResult As Boolean = False
mResult = My.Computer.Network.IsAvailable
If Not mResult Then
ShowMessage("本地连接无效")
Else
mResult = My.Computer.Network.Ping(RegionalCodeLibrary.NET.NetConst.GOV_DEFAULT)
If Not mResult Then
ShowMessage(String.Format("本机没有连接Internet或发布网址{0}无效", RegionalCodeLibrary.NET.NetConst.GOV_ADDRESS))
End If
End If
Return mResult
End Function
Private Sub ShowMessage(ByVal msg As String)
If msg = "" Then msg = "待命"
Me.Label1.Text = String.Format("消息:{0}", msg)
Me.Label1.Refresh()
End Sub
Private Sub MainForm_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
ClearEnvironment()
End Sub
Private Sub ClearEnvironment()
If gQueryTable Is Nothing Then Exit Sub
gQueryTable.Close()
gQueryTable = Nothing
''
gUpdateDatabase.Dispose()
End Sub
''
Private Sub gUpdateDatabase_UpdateCompleted(ByVal sender As Object, ByVal msg As String) Handles gUpdateDatabase.UpdateCompleted
ShowMessage(msg)
'加以下代码是为了截图的
Windows.Forms.Application.DoEvents()
End Sub
''
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Me.DataGridView1.DataSource = Nothing
End Sub
''
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
If Me.DataGridView1.DataSource Is Nothing Then Exit Sub
Dim mDataTable As DataTable = CType(Me.DataGridView1.DataSource, DataTable)
Dim mLastdate As String = Me.ComboBox1.Text
ShowMessage(String.Format("开始更新{0}数据到数据库...", mLastdate))
With gUpdateDatabase
.OpenConnection()
.Import(mDataTable, mLastdate)
.CloseConnection()
End With
ShowMessage("")
End Sub
''
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
Dim mDataTable As DataTable
Dim mLastdate As String
Me.Button1.PerformClick()
gUpdateDatabase.OpenConnection()
For i As Integer = Me.ComboBox1.Items.Count - 1 To 0 Step -1
Me.ComboBox1.SelectedIndex = i
Me.Refresh()
Me.Button2.PerformClick()
Me.Refresh()
mDataTable = CType(Me.DataGridView1.DataSource, DataTable)
mLastdate = Me.ComboBox1.Text
ShowMessage(String.Format("开始更新{0}数据到数据库...", mLastdate))
gUpdateDatabase.Import(mDataTable, mLastdate)
ShowMessage("")
Me.Refresh()
Threading.Thread.Sleep(1000)
Next
gUpdateDatabase.CloseConnection()
End Sub
End Class
偷懒的话可以按“更新全部数据”把网上的数据全部导入到数据库里面。
全部导入数据库后，
表[Base].区划码有数据3732项，表[Edit].区划码情况有数据3763项，比主体表多31项。
最后回到数据库的Management界面，查询一下有哪些区划码做了更改：
USE [RegionalCodeWorks]
GO
SELECT a.区划码ID
,a.区划码
,b.名称
,b.截止日期
,b.起始日期
FROM
(
SELECT b.区划码ID
,b.区划码
FROM [Edit].区划码情况 a
INNER JOIN [Base].区划码 b
ON a.区划码ID = b.区划码ID
GROUP BY b.区划码ID, b.区划码
HAVING COUNT(b.区划码ID) > 1
) a
INNER JOIN [Edit].区划码情况 b
ON a.区划码ID = b.区划码ID
ORDER BY a.区划码ID
结果有记录62项，比较上面的31，刚好是两倍，也就是说有31个区划码更改了一次，也仅仅是一次，没有哪个区划码在这段时间更改了两次。
图示，合并了两截图。
这篇目的是导入数据，看看主体表设计的效果。
下一篇，重点是设计附属表，查询中我们需要的目标信息，依靠它来实现。
方案下载：代码