Stitching Up Time Zones
Looping Through the Zones
The next step loops through the Registry key and dumps the zone key values. Notice that I first extracted the list of time zones into a string array. Each zone is referenced by name using the OpenSubKey method against the root key in the registry. A list of the time zones is saved to a string array populated with the GetSubKeyNames method.
As shown in Figure 3, the code saves the individual Registry key values to a strongly typed DataTable. This was built using Visual Studio's Data Source wizard that was pointed at the empty TimeZone table in the database. The benefit here is that you don't have to build any of the CRUD to save the data and you can reference the columns with human-readable names.
Click here for a larger image.
Figure 3: Saving key values to DataTable columns.
Extracting the Binary TZI Value
As you would imagine, accessing a binary key value in the Registry is a bit more complex. The trick is to map the binary value to a Structure that permits the code to reference each element of the structure with a named variable. This code is shown in Figure 4.
Figure 4: Extracting the TZI binary value.
The last step in the code (as shown in Figure 4) uses the TableAdapter Update method to save the DataTable to the database. Notice that the code generates the GMTOffset column by extracting a piece of the Description key value.
Building the Report
Okay, now that I have a table that has the GMTOffset and a matching Description, I can use Reporting Services to build a report that shows the correlated values in a chart by including query that uses a JOIN on the TimeZone and the RegisteredUsers tables. Figure 5 shows how the report looks so far. Just exactly how this report is built is fodder for another article.
Figure 5: The Users by Time Zone Report
Notice that the GMT -5 time zone (Eastern Standard Time in the US) is marked as "Bogota, Lima...". That's correct. Unfortunately, I don't really know if these users are from North or South America. That's a task I'll need to take up after the grandkids fly home. Right now, they seem to be disassembling something with a chainsaw in the next room.
About the Author
William (Bill) Vaughn is an industry-recognized author, mentor, and subject-matter expert on Visual Studio, SQL Server, Reporting Services, and data access interfaces. He's worked in the computer industry for over thirty-five years—working with mainframe, minicomputer, and personal computer systems as a developer, manager, architect, trainer, marketer, support specialist, writer, and publisher. In 2000, after 14 years at Microsoft, Bill stepped away to work on his books, mentoring, and independent training seminars. He's written seven editions of the Hitchhiker's Guide to Visual Basic and SQL Server and three editions of ADO.NET and ADO Examples and Best Practices for Visual Basic (and C#) Programmers. He and Peter Blackburn also wrote the critically acclaimed Hitchhiker's Guide to SQL Server 2000 Reporting Services.
Bill is a top-rated speaker and frequents conferences all over the world including TechEd, Visual Studio/SQL Connections, DevTeach, and many others. He's also written a wealth of articles for magazines such as MSDN, SQL Server Magazine, Visual Basic Programmer's Journal, .NET Magazine, and many others as well as a regular editorial for Processor magazine. Bill spends considerable time answering questions on the public newsgroups and speaking at INETA user group meetings all over the country and at other speaking venues all over the world. He's available for consulting, mentoring, or custom training. See www.betav.com or www.betav.com/blog/billva for his current schedule and course catalog.
Page 2 of 2