构建业务日和节假日数据库子系统

• 下载源代码 - 79.4 KB

1. 引言/目的

许多业务应用程序和报表在处理过程中需要灵活且高性能的特殊老化和日期功能，这些功能需要考虑周末日期和各种类型的假期。许多现有系统和报表，特别是那些在 Access 数据库中或之上构建的系统，如果使用迭代游标处理，则这些任务的性能很差或很慢。可能存在数以万计的体面规模的业务应用程序仍然采用这种方法，通过旧的 "MoveFirst" 和 "MoveNext" 方法迭代加载到旧式 ADO 或 DAO 记录集中的大量数据。我知道，因为我自十多年前刚入行 IT 时就一直从事 VB 和 Access 的编程工作，我自己也经常这样做。幸运的是，如果我必须编写处理数十万行数据的报表，我将给用户带来极大的痛苦，除非我学会更好的方法，并坚持应用程序基于 Oracle 或 SQL Server 等真正的数据库。本项目这个阶段为这种系统奠定了基础。您需要等到第三阶段才能看到它的威力是如何发挥的，但一旦您查看 AllDatesDiff 表中生成的详细数据，您可能会有所了解。

2. 数据库概述

使用的方法是数据驱动的，并将逻辑内置到预加载的数据文件中。数据库包含以下表：

• HolidayNames - 一个基础的节假日名称表。该表在数据库创建时填充，每个与业务相关的节假日有一行。
• DateFlagsReference - 此表是对不同类型可能假期的灵活整数枚举的引用，这些枚举以位字段的形式存储在数据库列 DateFlags 中。任何特定的假期都可以属于一个或多个类别（假期类型）。对于这个演示系统，我包含了商业假期和银行假期；其他类型可能包括半班假期或加拿大或其他国家假期——这取决于特定业务需求。替代设计是在 HolidayNames 表及其派生的两个表中为每种假期创建一个布尔列——这是纯关系型方法，也许更清晰简单，但代价是灵活性降低。说实话，如果我有时间重做这个小型设计，我可能会考虑这种更简单的方法。另一种选择是使用用户定义类型来定义此枚举。
• HolidayDates - 此表包含每个假期每年基础数据——每年每个假期有一行。实际日期是从 www.holidaywebservice.com 运行的公共 Web Service 派生的。在加载过程中，ASP.NET 维护应用程序从 Web Service 调用。在我上次工作时，每年十二月下旬总有人需要输入大量的节假日表，并手动修改脚本或输入下一年的日期。使用这样的服务解决了这个问题。
• HolidayDates - 一个暂存表，加载基础的节假日日期数据，通过存储过程 DateLoad 设置事务性数据加载。
• AllDates - 此表一旦加载，包含一年中的每一天。这种设计允许与表数据轻松关联，而无需使用返回 NULL 值的 OUTER join。NULL 值是令人头疼的实体，最好避免使用，除非需要实现业务逻辑来区分“未知”和“空”。
• AllDatesDiff - 这是老化表，包含每天的系列行——每行包含一个开始日期、一个结束日期以及它们之间的天数差（以工作日计）。此表的存在以及我们可以从中派生的视图是本系统实用性的基础。
• Registry - 由键值结构中的各种条目组成的配置表。
• DateLoadLogHistory - 一个非常简单的成功加载日志。

3. 数据库加载过程

除了上述基础表外，此数据库还包含可执行以填充表的 Transact-SQL 存储过程。这些过程是从用 C# 编码的 .NET 数据类执行的，前面是一个 ASP.NET 维护小程序（WinForms 小程序也是一个选择，在某些情况下可能更好）。通常，此过程可能每年十二月左右运行一次，以加载下一年的数据。必须建立一个业务流程来记录何时运行这些过程，并将访问权限严格限制给适当的管理员或 IT 人员。此演示中未实现安全性，但在任何生产系统中都必须包含。

下面是执行完整数据加载的主要存储过程。一些值得注意的是 SQL Server 2005 中新提供的 Try...Catch 的使用。如果您仍在使用 SQL Server 2000，请移除 Try...Catch 并以旧的非结构化方式处理错误。我认为 Transact-SQL 代码中没有使用其他 SQL Server 2005 功能，因此只需稍作调整即可运行。

-- =============================================
-- Description: Runs administrative process to load all the date tables for
-- a calendar year.
-- =============================================

ALTER PROCEDURE [dbo].[DateLoad]
    @LoadYear VARCHAR(4), -- yyyy will work
    @Success CHAR(1) OUT -- returns Y if data loaded successfully, 
                         -- otherwise N

AS
BEGIN
    DECLARE @Found CHAR(1)
    DECLARE @NumRows INT
    DECLARE @StartDt SMALLDATETIME
    DECLARE @EndDt SMALLDATETIME
    DECLARE @StartDtAdj SMALLDATETIME --adjusted to include diff days
    DECLARE @EndDtAdj SMALLDATETIME --adjusted to include diff days
    DECLARE @CalendarDaysMaxDiff SMALLINT
    DECLARE @msg VARCHAR(200)

    BEGIN

        SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --makes totally 
        -- sure no other process can modify this data while this txn runs.

        BEGIN TRY

            SET @Success = 'N'
            EXEC dbo.PrintInfoMessage 'Initializing data load.'

            IF LEN(@LoadYear) < 4
            BEGIN
                SET @msg = 
                  'Load year parameter not valid - must be ' +
                  '4 digit year. Value passed: ' + 
                  @LoadYear
                RAISERROR(@msg, 16, 1)
            END

            --check to be sure year was not already loaded
            EXEC DateLoadLogHistoryCheck @LoadYear, @Found = @Found OUT
            IF @Found = 'Y'
            BEGIN
                SET @msg = 'Dates for the year ' + @LoadYear + 
                           ' have already been loaded.'
                RAISERROR(@msg, 16, 1)
            END

            --check to be sure that we have data in the stage
            EXEC dbo.GetHolidayDateStageRowCount @NumRows = @NumRows OUT
            IF @NumRows = 0
            BEGIN
                SET @msg = 'Holiday stage table is not loaded.'
                RAISERROR(@msg, 16, 1)
            END

            -- first, get config setting for days diff
            SET @CalendarDaysMaxDiff = 
                CONVERT(SMALLINT, dbo.RegistryGetItem('maxdaysdiff'))
            IF @CalendarDaysMaxDiff = 0
            BEGIN
                SET @msg = 
                 'value for [maxdaysdiff] in registry table invalid or not found.'
                RAISERROR(@msg, 16, 1)
            END

            --set start and end date values
            SET @StartDt = '01/01/' + @LoadYear
            SET @EndDt = DATEADD(day, 364, @StartDt)

            --adjust start date if earlier dates not 
            --previously loaded, or also if later dates loaded
            EXEC GetUseStartDate @StartDt, @CalendarDaysMaxDiff, 
                 @StartDtAdj = @StartDtAdj OUT

            --adjust end date by the date diff
            SET @EndDtAdj = DATEADD(dd, @CalendarDaysMaxDiff, @EndDt)

            -- ok, load data now
            -- NOTE: table 2 - the AllDates table  
            --       - needs dates before and after the year boundary
            -- to allow complete fill in of table 3 (the AllDatesDiff table)
            EXEC dbo.PrintInfoMessage 'Data load initialization complete.'
        END TRY
        BEGIN CATCH
            EXECUTE ProcessError --TODO log this to an error table
            RETURN
        END CATCH

        -- table 1 - load primary table from stage - 1 row per holiday for the year
        BEGIN TRAN
        BEGIN TRY
            EXEC dbo.PrintInfoMessage 'Entered transaction.'
            EXEC dbo.PrintInfoMessage 'Starting HolidayDates table load.'
            EXEC LoadHolidayDates
            EXEC dbo.PrintInfoMessage 'HolidayDates table load completed.'

        -- table 2 - load the table with complete list
        --           of days for year including non-holidays.
            EXEC dbo.PrintInfoMessage 'Starting AllDates table load.'
            EXEC AllDatesLoad @StartDtAdj, @EndDtAdj
            EXEC dbo.PrintInfoMessage 'AllDates table load completed.'

        -- table 3 - load table with all the diff data
            EXEC dbo.PrintInfoMessage 'Starting AllDatesDiff table load.'
            EXEC AllDatesDiffLoad @StartDt, @EndDt, @CalendarDaysMaxDiff
            EXEC dbo.PrintInfoMessage 'AllDates table load completed.'

            EXEC dbo.PrintInfoMessage 'All tables loaded.'

            EXEC dbo.HolidayDatesStageDeleteAll
            EXEC dbo.PrintInfoMessage 'Holiday stage table data deleted.'

            EXEC dbo.DateLoadLogHistoryInsert @LoadYear
            EXEC dbo.PrintInfoMessage 'Load history log updated.'

            SET @Success = 'Y'

        END TRY

        BEGIN CATCH
            ROLLBACK
            EXECUTE ProcessError
            RETURN
        END CATCH

        --commit the transaction.
        BEGIN TRY
            COMMIT
            EXEC dbo.PrintInfoMessage 
                 'Transaction successfully committed. Data load complete.'
        END TRY
        BEGIN CATCH
            EXECUTE ProcessError
            RETURN
        END CATCH

        RETURN

    END

END

我确保将所有从 SQL Server 外部调用的存储过程都包装在 TRY...CATCH 块中，以确保向客户端报告一致的错误。

请注意上面过程执行时调用 PrintInfoMessage。我创建了它来向客户端应用程序发送 InfoMessage 消息。存储过程是 PRINT 方法的一个简单包装器。

-- =============================================
-- Description: Execute a PRINT statement for purposes
--            of client capture by MessageInfo
-- NOTE: Use this sp to specifically intend to send an InfoMessage to a client
--        (eg an ASP.NET or Winforms ADO.NET client).
--        Inline PRINT statements are not good for this purpose as they are
--        likely to be considered debugging statements 
--        to be cleaned up (removed) by developers.
-- =============================================

ALTER PROCEDURE [dbo].[PrintInfoMessage]
    @msg VARCHAR(200)
AS

BEGIN
    DECLARE @CurrDate CHAR(23) --ODBC canonical with ms
    SET @CurrDate = CONVERT(CHAR, GETDATE(), 121)
    SET @msg = @CurrDate + ' : ' + @Msg

    --note: caller may trim first 26 chars of the message
    --      string to get the original message only

    PRINT @msg
    RETURN

END

仔细考虑一下，我不确定我是否喜欢让客户端解析日期中的消息字符串的方法，但它确实有效。

4. 客户端 ASP.NET 数据加载/维护小程序

这里没有太多特别值得注意的地方，我尽量在 Transact-SQL 层面完成了尽可能多的工作。但是您必须使用我创建的 ASP.NET Web 应用，或某种客户端 .NET 应用程序来执行数据加载。需要某种客户端应用程序，因为该过程基本上包括三个阶段，而这三个阶段都无法在 Transact-SQL 中完成。

1. 通过使用 HolidayWebService.com 上公开的美国节假日日期 Web Service 获取下一年的节假日日期（需要 .NET Framework）。
2. 使用包含节假日日期在内的基础节假日数据填充暂存表。
3. 调用存储过程 DateLoad 来执行数据加载。

这是包含用于从节假日 Web Service 获取日期的类的代码。要链接到服务，我添加了以下 Web 引用： http://www.holidaywebservice.com/Holidays/servicesAvailable_USHolidayDates.aspx。 .NET 会完成其余的工作，生成必要的管道代码，然后我就可以像使用其他对象一样对服务进行编程了。

using System;
using System.Data;
using System.Configuration;
using System.Diagnostics;
using System.Reflection;
using HolidayWbSrvConsumer.HolidayWS;
//request URL: http://www.holidaywebservice.com/Holidays/US/Dates/USHolidayDates.asmx

namespace Pwryn.HolidayServiceCli
{
    /// <summary>
    /// Author: Paul W. Reynolds (www.codeproject.com author: LittleDev06)
    /// Consumer class for public web service method
    /// located at holidaywebservice.com
    /// </summary>
    public class HolidayWebServiceConsumer
    {
        //member object
        private USHolidayDates m_HolService;

        public HolidayWebServiceConsumer()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        /// <summary>
        /// Execute the web service methods from
        //  http://www.holidaywebservice.com to get actual dates for
        /// the holidays, and add them to a new column in our datatable object.
        /// The USHolidayDates service with an individual call for each
        /// named holiday seems to fiit our purposes best
        /// </summary>
        /// <param name="NextYear"></param>
        /// <param name="HolidaysToAddNextYear">This is a count.
        ///     If not zero, add in additional holidays for subsequent year. 
        ///     May be needed for to be sure we can do date differencing
        ///     for some time into the future</param>
        /// <param name="holtable">(ref) Incoming datatable object
        ///     preloaded with dateless holiday data (names, flags)</param>
        public void AddHolidayDates(ref DataTable holtable)
        {

            //set up the external holiday web service object
            m_HolService = new USHolidayDates();
            Object[] args = new Object[] { 0 };

            //find the method names in the data table
            //and execute web service method to get the dates
            foreach (DataRow row in holtable.Rows)
            {
                args[0] = row["DateYear"];
                row["HolidayDate"] = 
                  InvokeHolidayMethod(row["MethodName"].ToString(), args);
            }

            m_HolService.Dispose();
        }

       ///// <param name="args"></param>
        /// <summary>
        /// Get the holiday date for a given row by making the web method invokation
        /// </summary>
        /// <param name="holtable">Datatable we are operating on</param>
        /// <param name="row">row to update</param>
        /// <param name="args">list of arguments to pass to the Web method</param>
        private string InvokeHolidayMethod(string MethodName, Object[] args)
        {
            DateTime dtm;

            BindingFlags bndflags = BindingFlags.DeclaredOnly |
                BindingFlags.Public | BindingFlags.NonPublic |
                BindingFlags.Instance | BindingFlags.InvokeMethod;

            //UNDONE trap for TargetInvocationException if server
            //is down or no Web connectivity, or other general exception
            dtm = (DateTime)m_HolService.GetType().InvokeMember(MethodName, bndflags, 
                            null, (object)m_HolService, args);

            return dtm.ToShortDateString();

        }
    }
}

顾名思义，此服务仅限于美国假期。我将调用的方法名（根据 USHolidayDates 接口，每个假期一个）存储在 SQL Server 表 HolidayNames 中。然后，我使用反射和 InvokeMember 方法（VB6 中的 CallByName 用起来稍微容易些）迭代调用每个方法。

这是执行 DateLoad 的代码。

/// <summary>
/// Call the Transact SQL sp to load all holiday, date, and aging data needed
/// into the appropriate tables
/// </summary>
/// <param name="LoadYear">The year of date data to load</param>
public void DateLoad(string LoadYear)
{
    try
    {
        m_InfoMessageItemQueue = new Queue<InfoMessageItem>();

        SqlCommand cmm = new SqlCommand();

        this.SqlCommandInitProps(cmm, MethodBase.GetCurrentMethod().Name);
        this.SqlCommandSetConnection(cmm);
        SqlParameter prmLoadYear = cmm.Parameters.Add("@LoadYear", SqlDbType.VarChar, 4);
        prmLoadYear.Value = LoadYear;
        SqlParameter prmSuccess = cmm.Parameters.Add("@Success", SqlDbType.Char, 1);
        prmSuccess.Direction = ParameterDirection.Output;

        int ret = cmm.ExecuteNonQuery();
    }
    finally
    {
        if (m_Connection != null && m_Connection.State != ConnectionState.Closed)
        {
            m_Connection.Close();
        }
    }

}

泛型 Queue 变量用于接收从存储过程发送的 InfoMessage 消息（见上文）。消息由以下事件处理程序捕获：

/// <summary>
/// This event handler captures process messages
/// sent while the stored procedures are executing.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void SqlMessageEventHandler(object sender, SqlInfoMessageEventArgs e)
{
    foreach (SqlError err in e.Errors)
    {

        //only want messages specifically sent
        //to us via PrintInfoMessage stored procedure.
        //Other system messages are ignored;
        //errors or exceptions are not handled here
        //but via standard .NET exception handling.
        InfoMessageItem imi;
        if (err.Procedure == "PrintInfoMessage")
        {
            //please see sproc [dbo].[PrintInfoMessage]
            //for how the message is constructed.
            imi = new InfoMessageItem();
            imi.MessageDateTime = err.Message.Substring(0, 23);
            imi.Message = err.Message.Substring(26, err.Message.Length - 26);
            m_InfoMessageItemQueue.Enqueue(imi);
            Debug.WriteLine(imi.Message);
        }
    }
}

我曾尝试异步实现对 DateLoad 的调用，但未能成功使其与 ASP 一起工作（我相信通过 AJAX 风格的编码可以做到）。如上所述，我确实捕获了在运行过程中发送给客户端的消息（参见存储过程 PrintInfoMessage）。但鉴于加载是同步运行的，我可能也只是将该信息保存到数据库日志表中，然后在提交后读取表内容。

注意：节假日 Web Service 访问部分和数据库访问部分存在于各自的类 DLL 模块中（DateAgingAdminData.dll 和 HolidayWbSrvConsumer.dll），所以如果您想将我那个笨拙且略显丑陋的 ASP.NET 应用迁移到一个更简洁的 WinForms 前端并链接到这两个 DLL，那么抛弃我的 ASP 并编写您自己的客户端不会太难。我确定在 WinApp 中异步执行 DateLoad 以向用户提供正在发生的事情的运行状态，会容易得多；即使是我也能轻易做到（William Vaughn 的最新《Hitchhiker's Guide》一书是一个极好的参考）。

5. 总结

那么，到目前为止我们有什么？我们有一个数据库表，里面充满了大量的日期和日期差异数据。

为了使其有用，我们必须在其之上应用视图，公开这些视图并访问它们。在这样一个生产模块中，除了事先确保系统设计符合业务需求外，我还会确保实现数据审计并记录运行的数据流程。我还会尽可能限制对数据库的访问，特别是对基础表的添加和更新访问。甚至选择功能通常也可以限制为用户友好的视图（您将在第 2 阶段和第 3 阶段看到更多这些视图）。

6. 未来计划

在第 2 阶段，我计划实现一个 ASP.NET Web Service，该服务针对此数据库执行常见的业务日期函数，如 NextBusinessDay、IsBusinessDay、IsHoliday 等。它将使用加载数据中存在的预编译逻辑。对于第 3 阶段（这是真正有用的部分），我将添加一些视图，通过在 SQL 中使用 CASE 语句来包含派生列——然后创建查询视图并与大量样本数据行集合联接的报表。我还不确定是使用 Reporting Services 还是 Crystal Reports 作为我的样本报表。希望报表会比我这里粗糙的 ASP.NET 前端看起来更好看。

历史

• 2007-05-28：发布在 CodeProject 上。
• 2007-06-02：解决方案文件指向“E”驱动器作为项目驱动器。修改了解决方案文件以指向“C”驱动器，因此所有项目在压缩到“C”驱动器时都应正确加载。还更改了表的[主键]为非聚集索引，以减小数据库大小，因为它们应该不需要。
• 2007-07-17:
• ASP.NET - 修改了维护屏幕，允许用户选择要加载的年份。
• 数据库 - 修改了存储过程 AllDatesDiffLoad，将*所有*日期加载到 DateDiff 表中作为开始日期——而不仅仅是工作日，并且不加载开始日期与结束日期相同的日期。