Author：水如烟
总目录：行政区划数据方案设计
上一篇，行政区划程序的设计(七)
通过SUB PovideSetFurthestDataDate(ByVal datadate As String)函数来定义可查询的最早的起始日期，细想还是觉得不妥。从保障区划码数据的权威性考虑，原有的版本日期是必需保留的。为此，我取消了这个函数。另外在数据库中定义了一个存储过程“[Program].[数据版本集]”，由RegionalCodeLibary提供这项信息，这样，如果用户查询时如日期早于最早的数据版本日期，提醒用户是否使用最早的版本日期做为参考。
[Program].[数据版本集]
USE
[
RegionalCodeWorks
]
GO
/* ***** 对象: StoredProcedure [Program].[数据版本集] 脚本日期: 09/29/2006 21:48:41 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: LzmTW
-- Create date: 20060929
-- Description: 全部数据版本
-- =============================================
CREATE PROCEDURE [ Program ] . [ 数据版本集 ]
AS
BEGIN
SET NOCOUNT ON ;
SELECT DISTINCT 起始日期 AS 版本日期
FROM [ Edit ] .区划码情况
ORDER BY 起始日期 ASC
END
GO
/* ***** 对象: StoredProcedure [Program].[数据版本集] 脚本日期: 09/29/2006 21:48:41 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: LzmTW
-- Create date: 20060929
-- Description: 全部数据版本
-- =============================================
CREATE PROCEDURE [ Program ] . [ 数据版本集 ]
AS
BEGIN
SET NOCOUNT ON ;
SELECT DISTINCT 起始日期 AS 版本日期
FROM [ Edit ] .区划码情况
ORDER BY 起始日期 ASC
END
另外，还修改了存储过程“[Program].[行政区情况]”，在信息视图的最后增加了取该信息的数据版本日期。
[Program].[行政区情况]
set
ANSI_NULLS
ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: LzmTW
-- Create date: 20060921
-- Description: 取行政区的全名
-- =============================================
ALTER PROCEDURE [ Program ] . [ 行政区情况 ]
@当前时间 [ Base ] . [ RegionalDate ] = N ' Current '
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @类型情况 TABLE
(
区划码ID smallint
,类型ID smallint
)
INSERT INTO @类型情况
EXEC [ Action ] . [ 区划码类型情况 ] @当前时间
SELECT TOP 100 PERCENT
d.区划码ID
,d.区划码
,d.名称
, [ Action ] .全名( @当前时间 , d.一级, d.二级, d.三级) AS 全名
,a.类型
,b.级别
,d.一级
,d.二级
,d.三级
,d.截止日期
,d.起始日期
,a.类型ID
,b.级别ID
FROM Base.行政区类型 AS a
INNER JOIN Base.行政区级别 AS b
ON a.级别ID = b.级别ID
INNER JOIN @类型情况 AS c
ON a.类型ID = c.类型ID
INNER JOIN [ Action ] .行政区情况( @当前时间 ) AS d
ON d.区划码ID = c.区划码ID
ORDER BY d.一级, d.二级, d.三级
-- 新增视图的版本日期，20060929
SELECT MAX (起始日期) AS 版本日期
FROM [ FUNCTION ] .区划码情况( @当前时间 )
END
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: LzmTW
-- Create date: 20060921
-- Description: 取行政区的全名
-- =============================================
ALTER PROCEDURE [ Program ] . [ 行政区情况 ]
@当前时间 [ Base ] . [ RegionalDate ] = N ' Current '
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @类型情况 TABLE
(
区划码ID smallint
,类型ID smallint
)
INSERT INTO @类型情况
EXEC [ Action ] . [ 区划码类型情况 ] @当前时间
SELECT TOP 100 PERCENT
d.区划码ID
,d.区划码
,d.名称
, [ Action ] .全名( @当前时间 , d.一级, d.二级, d.三级) AS 全名
,a.类型
,b.级别
,d.一级
,d.二级
,d.三级
,d.截止日期
,d.起始日期
,a.类型ID
,b.级别ID
FROM Base.行政区类型 AS a
INNER JOIN Base.行政区级别 AS b
ON a.级别ID = b.级别ID
INNER JOIN @类型情况 AS c
ON a.类型ID = c.类型ID
INNER JOIN [ Action ] .行政区情况( @当前时间 ) AS d
ON d.区划码ID = c.区划码ID
ORDER BY d.一级, d.二级, d.三级
-- 新增视图的版本日期，20060929
SELECT MAX (起始日期) AS 版本日期
FROM [ FUNCTION ] .区划码情况( @当前时间 )
END
现在对RegionalCodeLibary项目进行编码（还没有测试）：
注意项目组织，特别是文档组织方法。我觉得这样一目了然。这也是“按应用范围和服务对象两者的结合来组织”的体现。
Constant.vb
Friend
Class
Constant
Private Sub New ()
End Sub
Friend Const PROCEDURE_ALLINFORMATIONS As String = " [Program].[行政区情况] "
Friend Const PROCEDURE_DATAVERSIONTABLE As String = " [Program].[数据版本集] "
Friend Const PROCEDURE_ADDRECORD As String = " [Program].[增添记录] "
Friend Const MSG_UPDATEDATABASE_DATASET_IS_EMPTY As String = " 数据集无数据 "
Friend Const MSG_UPDATEDATABASE_TABLE_IS_INVALID As String = " 数据集中表的格式无效。要求数据集中的表:列名Code表示区划码,Name表示区划码名称,表名为版本日期八位字符串 "
End Class
Private Sub New ()
End Sub
Friend Const PROCEDURE_ALLINFORMATIONS As String = " [Program].[行政区情况] "
Friend Const PROCEDURE_DATAVERSIONTABLE As String = " [Program].[数据版本集] "
Friend Const PROCEDURE_ADDRECORD As String = " [Program].[增添记录] "
Friend Const MSG_UPDATEDATABASE_DATASET_IS_EMPTY As String = " 数据集无数据 "
Friend Const MSG_UPDATEDATABASE_TABLE_IS_INVALID As String = " 数据集中表的格式无效。要求数据集中的表:列名Code表示区划码,Name表示区划码名称,表名为版本日期八位字符串 "
End Class
DatabaseServices.vb
Public
Class
DatabaseServices
Public Event ServiceMessage( ByVal sender As Object , ByVal message As String )
Private gConnectionString As String = ""
Sub New ( ByVal connectionstring As String )
gConnectionString = connectionstring
End Sub
Private Function GetCommand( ByVal sql As String ) As SqlClient.SqlCommand
Return New SqlClient.SqlCommand(sql, Me .GetConnection)
End Function
Private Function GetConnection() As SqlClient.SqlConnection
Return New SqlClient.SqlConnection( Me .gConnectionString)
End Function
End Class
Public Event ServiceMessage( ByVal sender As Object , ByVal message As String )
Private gConnectionString As String = ""
Sub New ( ByVal connectionstring As String )
gConnectionString = connectionstring
End Sub
Private Function GetCommand( ByVal sql As String ) As SqlClient.SqlCommand
Return New SqlClient.SqlCommand(sql, Me .GetConnection)
End Function
Private Function GetConnection() As SqlClient.SqlConnection
Return New SqlClient.SqlConnection( Me .gConnectionString)
End Function
End Class
DatabaseServices.DataVersionTable.vb
Partial
Class
DatabaseServices
' '' <summary>
' '' 数据库当前数据版本集
' '' </summary>
' '' <returns></returns>
' '' <remarks></remarks>
Public Function DataVersionTable() As DataTable
Dim mResult As New DataTable( " DataVersion " )
Dim mCommand As SqlClient.SqlCommand = Me .GetCommand(Constant.PROCEDURE_DATAVERSIONTABLE)
mCommand.CommandType = CommandType.StoredProcedure
Dim mDataAdapter As New SqlClient.SqlDataAdapter(mCommand)
mDataAdapter.Fill(mResult)
mCommand.Connection.Dispose()
mCommand.Dispose()
mDataAdapter.Dispose()
Return mResult
End Function
End Class
' '' <summary>
' '' 数据库当前数据版本集
' '' </summary>
' '' <returns></returns>
' '' <remarks></remarks>
Public Function DataVersionTable() As DataTable
Dim mResult As New DataTable( " DataVersion " )
Dim mCommand As SqlClient.SqlCommand = Me .GetCommand(Constant.PROCEDURE_DATAVERSIONTABLE)
mCommand.CommandType = CommandType.StoredProcedure
Dim mDataAdapter As New SqlClient.SqlDataAdapter(mCommand)
mDataAdapter.Fill(mResult)
mCommand.Connection.Dispose()
mCommand.Dispose()
mDataAdapter.Dispose()
Return mResult
End Function
End Class
DatabaseServices.AllInformationsTable.vb
Partial
Class
DatabaseServices
' '' <summary>
' '' 区划码信息
' '' </summary>
' '' <param name="regionalcodeDate">查询日期</param>
' '' <remarks>如无数据,表名为Empty,否则为数据版本日期</remarks>
Public Function AllInformationsTable( ByVal regionalcodeDate As String ) As DataTable
Dim mResult As DataTable
Dim mDataSet As New DataSet
Dim mCommand As SqlClient.SqlCommand = Me .GetCommand(Constant.PROCEDURE_ALLINFORMATIONS)
With mCommand
.CommandType = CommandType.StoredProcedure
.Parameters.Add( " @当前时间 " , SqlDbType.NVarChar, 8 ).Value = regionalcodeDate
End With
Dim mDataAdapter As New SqlClient.SqlDataAdapter(mCommand)
mDataAdapter.Fill(mDataSet)
mCommand.Connection.Dispose()
mCommand.Dispose()
mDataAdapter.Dispose()
' 'mDataSet有两个表,第一个区划码信息,第二个仅含一条记录:版本信息
mResult = mDataSet.Tables( 0 )
If mDataSet.Tables( 1 ).Rows.Count = 1 Then
mResult.TableName = mDataSet.Tables( 1 ).Rows( 0 ).Item( 0 ).ToString
Else
mResult.TableName = " Empty "
End If
Return mResult
End Function
End Class
' '' <summary>
' '' 区划码信息
' '' </summary>
' '' <param name="regionalcodeDate">查询日期</param>
' '' <remarks>如无数据,表名为Empty,否则为数据版本日期</remarks>
Public Function AllInformationsTable( ByVal regionalcodeDate As String ) As DataTable
Dim mResult As DataTable
Dim mDataSet As New DataSet
Dim mCommand As SqlClient.SqlCommand = Me .GetCommand(Constant.PROCEDURE_ALLINFORMATIONS)
With mCommand
.CommandType = CommandType.StoredProcedure
.Parameters.Add( " @当前时间 " , SqlDbType.NVarChar, 8 ).Value = regionalcodeDate
End With
Dim mDataAdapter As New SqlClient.SqlDataAdapter(mCommand)
mDataAdapter.Fill(mDataSet)
mCommand.Connection.Dispose()
mCommand.Dispose()
mDataAdapter.Dispose()
' 'mDataSet有两个表,第一个区划码信息,第二个仅含一条记录:版本信息
mResult = mDataSet.Tables( 0 )
If mDataSet.Tables( 1 ).Rows.Count = 1 Then
mResult.TableName = mDataSet.Tables( 1 ).Rows( 0 ).Item( 0 ).ToString
Else
mResult.TableName = " Empty "
End If
Return mResult
End Function
End Class
DatabaseServices.UpdateDatabase.vb
Partial
Class
DatabaseServices
Public Event UpdateDatabaseCurrentPercent( ByVal sender As Object , ByVal currentPercent As Integer )
' 以下参数用在显示更新进度
Private gUpdateDatabase_RowsCount As Integer
Private gUpdateDatabase_CurrentCount As Integer
Private gUpdateDatabase_Command As SqlClient.SqlCommand
' '' <summary>
' '' 更新版本数据
' '' </summary>
' '' <param name="ds">数据集</param>
' '' <remarks>要求数据集中的表:列名Code表示区划码,Name表示区划码名称,表名为版本日期八位字符串</remarks>
Public Overridable Sub UpdateDatabase( ByVal ds As DataSet)
' 初始有关参数
UpdateDatabase_Initialize()
' 检测数据集有效性
If Not DataSetIsValid(ds) Then Exit Sub
' 更新
UpdateDatabaseBegin()
UpdateDatabaseByDataSet(ds)
UpdateDatabaseFinish()
End Sub
Private Sub UpdateDatabaseBegin()
gUpdateDatabase_Command = Me .GetCommand(Constant.PROCEDURE_ADDRECORD)
With gUpdateDatabase_Command
.CommandType = CommandType.StoredProcedure
.Parameters.Add( " @区划码 " , SqlDbType.NChar, 6 )
.Parameters.Add( " @名称 " , SqlDbType.NVarChar, 100 )
.Parameters.Add( " @起始日期 " , SqlDbType.NChar, 8 )
.Connection.Open()
End With
End Sub
Private Sub UpdateDatabaseFinish()
With gUpdateDatabase_Command
.Connection.Close()
.Connection.Dispose()
.Dispose()
End With
End Sub
Private Sub UpdateDatabaseByDataSet( ByVal ds As DataSet)
For Each table As DataTable In ds.Tables
UpdateDatabaseByDataTable(table)
Next
End Sub
Private Sub UpdateDatabaseByDataTable( ByVal table As DataTable)
For Each row As DataRow In table.Rows
UpdateDatabaseByDataRow(row, table.TableName)
Next
End Sub
Private Sub UpdateDatabaseByDataRow( ByVal row As DataRow, ByVal dataVersionDate As String )
With row
ImportRecord(.Item( " Code " ).ToString, .Item( " Name " ).ToString, dataVersionDate)
End With
End Sub
Private Sub ImportRecord( ByVal code As String , ByVal name As String , ByVal dataVersionDate As String )
With gUpdateDatabase_Command
.Parameters.Item( " @区划码 " ).Value = code
.Parameters.Item( " @名称 " ).Value = name
.Parameters.Item( " @起始日期 " ).Value = dataVersionDate
.ExecuteNonQuery()
End With
Me .gUpdateDatabase_CurrentCount += 1
Dim mCurrentPercent As Integer = CType ( Me .gUpdateDatabase_CurrentCount / Me .gUpdateDatabase_RowsCount * 100 , Integer )
RaiseEvent UpdateDatabaseCurrentPercent( Nothing , mCurrentPercent)
End Sub
' 初始化
Private Sub UpdateDatabase_Initialize()
'进度 计数器复位
Me .gUpdateDatabase_RowsCount = 0
Me .gUpdateDatabase_CurrentCount = 0
End Sub
' 检测数据集有效性
Private Function DataSetIsValid( ByVal ds As DataSet) As Boolean
Dim mResult As Boolean = False
If ds.Tables.Count = 0 Then
RaiseEvent ServiceMessage( Nothing , Constant.MSG_UPDATEDATABASE_DATASET_IS_EMPTY)
GoTo CHECK_FINISH
End If
For Each table As DataTable In ds.Tables
With table
Try
' 表名是否八位字符串日期
Dim tmp As DateTime = LzmTW.uSystem.uString.GetDate(.TableName)
Catch ex As Exception
RaiseEvent ServiceMessage( Nothing , Constant.MSG_UPDATEDATABASE_TABLE_IS_INVALID)
GoTo CHECK_FINISH
End Try
If Not .Columns.Contains( " Code " ) OrElse Not .Columns.Contains( " Name " ) Then
RaiseEvent ServiceMessage( Nothing , Constant.MSG_UPDATEDATABASE_TABLE_IS_INVALID)
GoTo CHECK_FINISH
End If
End With
' 累加记录数
Me .gUpdateDatabase_RowsCount += table.Rows.Count
Next
mResult = True
CHECK_FINISH: _
Return mResult
End Function
End Class
Public Event UpdateDatabaseCurrentPercent( ByVal sender As Object , ByVal currentPercent As Integer )
' 以下参数用在显示更新进度
Private gUpdateDatabase_RowsCount As Integer
Private gUpdateDatabase_CurrentCount As Integer
Private gUpdateDatabase_Command As SqlClient.SqlCommand
' '' <summary>
' '' 更新版本数据
' '' </summary>
' '' <param name="ds">数据集</param>
' '' <remarks>要求数据集中的表:列名Code表示区划码,Name表示区划码名称,表名为版本日期八位字符串</remarks>
Public Overridable Sub UpdateDatabase( ByVal ds As DataSet)
' 初始有关参数
UpdateDatabase_Initialize()
' 检测数据集有效性
If Not DataSetIsValid(ds) Then Exit Sub
' 更新
UpdateDatabaseBegin()
UpdateDatabaseByDataSet(ds)
UpdateDatabaseFinish()
End Sub
Private Sub UpdateDatabaseBegin()
gUpdateDatabase_Command = Me .GetCommand(Constant.PROCEDURE_ADDRECORD)
With gUpdateDatabase_Command
.CommandType = CommandType.StoredProcedure
.Parameters.Add( " @区划码 " , SqlDbType.NChar, 6 )
.Parameters.Add( " @名称 " , SqlDbType.NVarChar, 100 )
.Parameters.Add( " @起始日期 " , SqlDbType.NChar, 8 )
.Connection.Open()
End With
End Sub
Private Sub UpdateDatabaseFinish()
With gUpdateDatabase_Command
.Connection.Close()
.Connection.Dispose()
.Dispose()
End With
End Sub
Private Sub UpdateDatabaseByDataSet( ByVal ds As DataSet)
For Each table As DataTable In ds.Tables
UpdateDatabaseByDataTable(table)
Next
End Sub
Private Sub UpdateDatabaseByDataTable( ByVal table As DataTable)
For Each row As DataRow In table.Rows
UpdateDatabaseByDataRow(row, table.TableName)
Next
End Sub
Private Sub UpdateDatabaseByDataRow( ByVal row As DataRow, ByVal dataVersionDate As String )
With row
ImportRecord(.Item( " Code " ).ToString, .Item( " Name " ).ToString, dataVersionDate)
End With
End Sub
Private Sub ImportRecord( ByVal code As String , ByVal name As String , ByVal dataVersionDate As String )
With gUpdateDatabase_Command
.Parameters.Item( " @区划码 " ).Value = code
.Parameters.Item( " @名称 " ).Value = name
.Parameters.Item( " @起始日期 " ).Value = dataVersionDate
.ExecuteNonQuery()
End With
Me .gUpdateDatabase_CurrentCount += 1
Dim mCurrentPercent As Integer = CType ( Me .gUpdateDatabase_CurrentCount / Me .gUpdateDatabase_RowsCount * 100 , Integer )
RaiseEvent UpdateDatabaseCurrentPercent( Nothing , mCurrentPercent)
End Sub
' 初始化
Private Sub UpdateDatabase_Initialize()
'进度 计数器复位
Me .gUpdateDatabase_RowsCount = 0
Me .gUpdateDatabase_CurrentCount = 0
End Sub
' 检测数据集有效性
Private Function DataSetIsValid( ByVal ds As DataSet) As Boolean
Dim mResult As Boolean = False
If ds.Tables.Count = 0 Then
RaiseEvent ServiceMessage( Nothing , Constant.MSG_UPDATEDATABASE_DATASET_IS_EMPTY)
GoTo CHECK_FINISH
End If
For Each table As DataTable In ds.Tables
With table
Try
' 表名是否八位字符串日期
Dim tmp As DateTime = LzmTW.uSystem.uString.GetDate(.TableName)
Catch ex As Exception
RaiseEvent ServiceMessage( Nothing , Constant.MSG_UPDATEDATABASE_TABLE_IS_INVALID)
GoTo CHECK_FINISH
End Try
If Not .Columns.Contains( " Code " ) OrElse Not .Columns.Contains( " Name " ) Then
RaiseEvent ServiceMessage( Nothing , Constant.MSG_UPDATEDATABASE_TABLE_IS_INVALID)
GoTo CHECK_FINISH
End If
End With
' 累加记录数
Me .gUpdateDatabase_RowsCount += table.Rows.Count
Next
mResult = True
CHECK_FINISH: _
Return mResult
End Function
End Class
方案暂缓上传。
下一篇，行政区划程序的设计(九)，RegionalCodeCenter项目的编写。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
