“Time” for ADO .NET

A few days ago I described the new date and time data types coming in SQL Server 2008. To get the complete picture, we need to see what it looks like to use those new types from .NET Code.

The necessary changes to ADO .NET come in a few places, namely extensions to the SqlDbType enum and data access functions, additional type mappings, and a completely new .NET Framework type.

Let's start with the new type – System.DateTimeOffset. This new type is equivalent to the similarly named new type in SQL Server 2008. It stores A full DateTime value as well as a timespan that represents the offset from Coordinated Universal Time (UTC). You can construct an instance of DateTimeOffset manually by providing DateTime and TimeSpan values, and you can also use static functions like DateTimeOffset.Now() to create an instance using the current system time and the current system time zone offset.

Here is an example of creating a DateTimeOffset manually and passing it to SQL Server 2008:

using (SqlConnection cn = new SqlConnection("server=.;database=test;Integrated Security=sspi"))
using (SqlCommand cmd = new SqlCommand("Insert into Events (EventName, EventStartTime) values (@Name, @Start)", cn))
{

cmd.Parameters.AddWithValue("@Name", "Thanksgiving Dinner (East Coast USA)");
cmd.Parameters.AddWithValue("@Start", new DateTimeOffset(2007, 11, 22, 16, 0, 0, new TimeSpan(-5, 0, 0)));

cn.Open();

cmd.ExecuteNonQuery();

}

You will notice that ADO .NET implicitly maps System.DateTimeOffset to a datetimeoffset value in SQL Server.

The real power of having time zone-aware types becomes more apparent when you need to do queries for users in the context of a time zone that is different from the stored data. In this example we will retrieve events, and then use functionality in the DateTimeOffset type to transform the data to the preferred time zone of the user:

Console.Write("Enter TimeZone: ");
int offset = int.Parse(Console.ReadLine());

using (SqlConnection cn = new SqlConnection("server=.;database=test;Integrated Security=sspi"))
using (SqlCommand cmd = new SqlCommand("Select * from Events", cn))
{

cn.Open();

SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{

string name = dr.GetString(dr.GetOrdinal("EventName"));
DateTimeOffset original = dr.GetDateTimeOffset(dr.GetOrdinal("EventStartTime"));

DateTimeOffset local = original.ToOffset(new TimeSpan(offset, 0, 0));

Console.WriteLine("{0}:\t{1}\t[{2}]", name, local, original);

}

}

Results:

Enter TimeZone: -8
Thanksgiving Dinner (East Coast USA): 11/22/2007 1:00:00 PM -08:00 [11/22/2007 4:00:00 PM -05:00]

You will notice in that last example that when we retrieved the original DateTimeOffset value from the SqlDataReader, we used a new method on the reader that maps to the new .NET Framework Type: GetDateTimeOffset(). In Addition, the SqlDbType Enum has been extended to allow you to explicitly set the SQL Server types of parameters you pass. New options include Date, Time, DateTime2, and DateTimeOffset (as well as the "Structured" option for passing Table-Valued Parameters).

Now is probably a good time to talk about which .NET Framework types map to these new data types in SQL Server. Here is the scoop.

Finally, let's show a scenario where the value of having separate date and time types comes to life, In this example, we will select rows from a table matching exactly on the date of items (regardless of the time), or on the time of an item (regardless of date).

First, here is the data in a table called AgendaItems:

AgendaItemID AgendaItemName AgendaItemDate AgendaItemTime
------------ -------------------------------------------------- ----------------------- ----------------
1 Welcome 2007-11-14 09:00:00
2 Business Review 2007-11-14 10:00:00
3 Lunch 2007-11-14 12:00:00
4 Kickoff 2007-11-15 09:30:00
5 Pipeline Review 2007-11-15 10:30:00
6 Working Lunch 2007-11-15 12:00:00

Now the queries:

using (SqlConnection cn = new SqlConnection("server=.;database=test;Integrated Security=sspi"))
using (SqlCommand cmd = new SqlCommand("Select * from AgendaItems where AgendaItemDate = @DATE", cn))
{

DateTime startDay = DateTime.Today;

cmd.Parameters.Add("@DATE", SqlDbType.Date).Value = startDay;

cn.Open();

SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

Console.WriteLine("\nEvents on {0}", startDay.ToShortDateString());

while (dr.Read())
{

string name = dr.GetString(dr.GetOrdinal("AgendaItemName"));
DateTime date = dr.GetDateTime(dr.GetOrdinal("AgendaItemDate"));
TimeSpan time = dr.GetTimeSpan(dr.GetOrdinal("AgendaItemTime"));

Console.WriteLine("{0}: {1}", name, date+time);

}

}

using (SqlConnection cn = new SqlConnection("server=.;database=test;Integrated Security=sspi"))
using (SqlCommand cmd = new SqlCommand("Select * from AgendaItems where AgendaItemTime = @TIME", cn))
{

TimeSpan startTime = new TimeSpan(12,0,0);

cmd.Parameters.Add("@TIME", SqlDbType.Time).Value = startTime;

cn.Open();

SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

Console.WriteLine("\nEvents at {0}", startTime);

while (dr.Read())
{

string name = dr.GetString(dr.GetOrdinal("AgendaItemName"));
DateTime date = dr.GetDateTime(dr.GetOrdinal("AgendaItemDate"));
TimeSpan time = dr.GetTimeSpan(dr.GetOrdinal("AgendaItemTime"));

Console.WriteLine("{0}: {1}", name, date + time);

}

}

And now the payoff:

Events on 11/14/2007
Welcome: 11/14/2007 9:00:00 AM
Business Review: 11/14/2007 10:00:00 AM
Lunch: 11/14/2007 12:00:00 PM

Events at 12:00:00
Lunch: 11/14/2007 12:00:00 PM
Working Lunch: 11/15/2007 12:00:00 PM

So that's the story. I hope you are as excited about the new features coming in SQL Server 2008 as I am. Be on the lookout for the next CTP that is coming very soon. When it releases, we will continue this exploration with the long list of new features that will become available with that release.