行政区划数据数据库的设计(五)

王朝other·作者佚名  2006-09-21
窄屏简体版  字體: |||超大  

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个区划码更改了一次,也仅仅是一次,没有哪个区划码在这段时间更改了两次。

图示,合并了两截图。

这篇目的是导入数据,看看主体表设计的效果。

下一篇,重点是设计附属表,查询中我们需要的目标信息,依靠它来实现。

方案下载:代码

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航