1. Introduction / Purpose
The purpose of this project is to expose common date functionality in an easy to use way to customized business applications running under the .NET 2.0 and SQL Server 2005 platform (or .NET 3.0 for the WCF version). In phase one of this series, a subsystem was implemented to load SQL Server database tables containing all the pre-compiled logic needed to be able to make calls against the database to perform common logical date functions. The methods below could certainly be created in a system that includes nothing more than a small table of holidays, without date difference data - in that case the application code can count days on a case-by-case basis. But due to the work already done in phase one, implementation of most of the functions here is quite simple, as most of the logic the methods are based on is already pre-compiled into the database data.
Setup notes:
- In order to execute the downloaded code, you must download, set up, and run the project from phase 1: Building a Business Day and Holiday Database Subsystem
- There are two solutions included in the zip download. The second solution requires the .NET 3.0 Framework as well as the Windows SDK for .NET 3.0 and Visual Studio Orcas beta1 or greater. More details may be found in the readme.txt files in the download (one for each solution).
2. Date Methods
Here is the list of methods that are exposed by the API:
BusinessDaysUntil (date1, date2) IsBusinessDate(date1) IsBusinessHolidayDate(date1) IsFederalHolidayDate(date1) IsHolidayDate(date1) IsWeekendDate(date1) NextBusinessDate (date1) NextNonBusinessDate(date1) NextWeekendDate(date1) PreviousBusinessDate(date1) PreviousNonBusinessDate(date1) PreviousWeekendDate(date1)
More details can be found in the source code interface and method headers.
3. Accessing the API - Thoughts on Design
What would be the best or simplest way to access this API? The answer really depends on what and what kind of applications you are building. Exposing and consuming these methods through a WCF service as I do in the second download makes great sense in an SOA context. The WCF service works well here since the only persistent state the application has to be concerned about with these date methods lies in the database. But in many client-server situations, simply adding the DateAgingData.DLL library to each application assembly that needs it might be a simpler and preferable option. You can clearly see the WCF solution contains a lot more lines of code as well as configuration, and that is not a good thing if it can be avoided.
4. DateAgingApp1 Solution - Simple .NET Assembly Access
Layer 1: ADO.NET data access against DateAgingDemo database
I considered creating stored procedures for each of the methods, as I did for the data loading piece in part 1 (for good reasons in that case) - but I feel that for these kinds of simple SQL queries, executing the SQL directly from the C# data library is a better and more easily testable approach. I definitely feel if these methods are going to be exposed and accessed by a WCF service, a stored procedure layer offers no real benefits that I can see.
The only somewhat complicated method as far as logic here is BusinessDaysUntil(). I needed to include some extra logic because date intervals greater than the MAXDAYSDIFF value stored in the database Registry table are not actually stored in the database (XML header comments removed from the snip below):
public short BusinessDaysUntil(DateTime dt1, DateTime dt2)
{
TimeSpan diff1 = dt2 - dt1;
if (diff1.Days == 0)
return 0;
bool positive = (diff1.Days >= 0);
short ret;
short diffmodify = 0;
short diffchange = 0;
bool done = false;
if (!positive)
{
DateTime dtTemp;
dtTemp = dt2;
dt2 = dt1;
dt1 = dtTemp;
}
DateTime dt1Adjusted = dt1;
do
{
string sql = "SELECT DateDifference FROM AllDatesDiff WHERE Date1 = @dt1"
+ " AND Date2 = @dt2";
ret = Convert.ToInt16(this.SqlCommandExecDateParamReturnScalar(sql,
"@dt1", dt1Adjusted, "@dt2", dt2));
if (ret == 0)
{
this.GetNextDate(dt1Adjusted, ref dt1Adjusted, ref diffchange);
diffmodify = Convert.ToInt16(diffmodify + diffchange);
}
else
{
diffmodify = Convert.ToInt16(diffmodify + ret);
done = true;
}
} while (!done);
if (positive)
return diffmodify;
else
return Convert.ToInt16(-diffmodify);
}
The SqlCommandExecDateParamReturnScalar method above is a data access wrapper method I created along with some other similar methods that kept many of the other method calls short and sweet. For the purpose of this project and to reduce dependencies, I made these private methods within the data access class. Also, to keep the above method more readable and maintainable, I broke out the piece that gets the next date when the return value is 0 from the first query into a second private method (again, XML header comments removed from the snip below):
private void GetNextDate(DateTime dt1, ref DateTime NextDate, ref short datedifference)
{
string sql = "SELECT MAX(date2), datedifference FROM alldatesdiff WHERE date1 = @dt1"
+ " AND datedifference = "
+ " (SELECT MAX(datedifference) FROM alldatesdiff"
+ " WHERE date1 = @dt1) GROUP BY datedifference";
SqlConnection connection = new SqlConnection(m_ConnectionString);
connection.Open();
SqlCommand cmm = new SqlCommand(sql, connection);
using (connection)
{
cmm.Parameters.AddWithValue("@dt1", dt1.ToString("d"));
SqlDataReader dr = cmm.ExecuteReader();
while (dr.Read())
{
NextDate = Convert.ToDateTime(dr.GetValue(0));
datedifference = Convert.ToInt16(dr.GetValue(1));
}
dr.Close();
}
return;
}
Layer 2: The DLL library
The DLL library is referenced and called directly by a console application that executes each of the methods. Here is the output:
5. DateAgingApp2 Solution- Access via Windows Communication Foundation Host (WCF)
Layer1: See DateAgingApp1
The source code for the data access layer for both applications is identical.
Layer 2 - WCF Service
I created a more or less simple as possible service hosted by a console application. As of this writing, I am very new to WCF and this is the first service I have created using the technology - this part of the code demonstrates the concept more than robust, knowledgeable implementation. I have already learned there are lots and lots of possible dials to turn; as a result WCF is not nearly as easy as creating or consuming "old-fashioned" ASMX.
Below is the interface for the service, with XML headers removed:
[ServiceContract(Namespace = "http://localhost:8000/DateAgingDataService")]
public interface IDateAgingDataService
{
[OperationContract]
short BusinessDaysUntil(DateTime dt1, DateTime dt2);
[OperationContract]
bool IsBusinessDate(DateTime dt);
[OperationContract]
bool IsBusinessHolidayDate(DateTime dt);
[OperationContract]
bool IsFederalHolidayDate(DateTime dt);
[OperationContract]
bool IsHolidayDate(DateTime dt);
[OperationContract]
bool IsWeekendDate(DateTime dt);
[OperationContract]
DateTime NextBusinessDate(DateTime dt);
[OperationContract]
DateTime NextNonBusinessDate(DateTime dt);
[OperationContract]
DateTime NextWeekendDate(DateTime dt);
[OperationContract]
DateTime PreviousBusinessDate(DateTime dt);
[OperationContract]
DateTime PreviousNonBusinessDate(DateTime dt);
[OperationContract]
DateTime PreviousWeekendDate(DateTime dt);
}
And here is a snippet from the implementation class:
public class DateAgingDataService : IDateAgingDataService
{
public bool IsBusinessDate(DateTime dt)
{
DateAgingDataAccessLib objDate = DataConnect();
return objDate.IsBusinessDate(dt);
}
public bool IsHolidayDate(DateTime dt)
{
DateAgingDataAccessLib objDate = DataConnect();
return objDate.IsHolidayDate(dt);
}
}
The difficult part of using WCF I think is not coding interfaces and methods, but in setting up appropriate configuration for your project.
You can see that I am utilizing the .NET DateTime data type. I'm not sure this is really the best approach given the problems DateTime has with timezone changes, and the fact that the time portion of DateTime is not relevant here. Other options could include a string in a recognizable date format (mm/dd/yyyy) or a custom Date object (WCF does support custom complex .NET types).
In any case, if you want to host your service in a live project, you will need to do some reading and research to determine the best way to do it. You can host WCF services in IIS or in a Windows service; both are better production deployment options than the method I use here (a console app requires that a user be logged in to the machine to start the service. Also the console-based service can easily be closed with an accidental key stroke or mouse click).
At this point, I won't go into any more detail on WCF, except to point out that there are many good resources and tutorials available on the subject here on CodeProject and elsewhere, as well as a number of new books (check the ratings at Amazon.com).
Layer 3 - Client Application
This is a similar console application to the one in DateAgingApp1 - the only difference here being the DateAgingApp2 client is set up to reference and utilize the WCF service as opposed to linking directly to DateAgingData.dll. The method signatures of the called methods and the output are the same in both DateAgingApp1 and DateAgingApp2 clients.
Note: If you want to make any changes to the code or app.config in the WCF service, you have to execute the file svcutil_out.bat (and if you change namespaces, modify it as well) to update the client source with an updated app.config and updated generated proxy (generatedClient.cs). To sum: If you can't get this little beast to run, or want to change the configuration or namespaces, consider this a good opportunity to learn the technology via the effective "hitting your head against the wall a few times" technique! I did include step by step details in the readme.txt file and I hope that helps.
6. Conclusion
I hope you are finding these articles and code helpful in giving you ideas for dealing with date functionality in your apps.
Coming soon! Phase 3 of the project will demonstrate utilization of the pre-compiled data for high-performance reporting purposes against large sets of data - the main reason for going to the trouble of creating the data in the first place in phase 1.
7. History
- 2007-07-19: Posted on CodeProject
- 2007-07-27: Changes made to DateAgingDataAccessLib.cs - instance level connection variable removed and
using clause added to methods. ServiceHost.cs - modified boolean methods for conciseness.
