Updating AdventureWorksDW2008R2 for Today

Back in May I did a post called “Updating AdventureWorksDW2012 for Today”. In that post I included a script that would take the AdventureWorksDW2012 sample database, available on CodePlex, and convert it to use current dates.

As part of my job at PragmaticWorks I teach classes on SQL Server Analysis Services. My upcoming class is using SQL Server 2008R2, and as such I wanted to update the 2008R2 version of AdventureWorksDW. I did find some subtle differences between the 2008R2 and 2012 versions of the Adventure Works Data Warehouse that I wanted to post an updated version of the script to use with 2008R2.

If you don’t have the AdventureWorksDW2008R2 data warehouse database, you can obtain it from http://msftdbprodsamples.codeplex.com/releases/view/59211 or go to CodePlex and search for SQL Server 2008 Sample Databases. Note there are several versions of Adventure Works here. The file for the data warehouse is either “AdventureWorks 2008R2 DW Script”, which is a T-SQL script that creates and populates the 2008R2 data warehouse, or “AdventureWorksDW2008R2 Data File” which is a database file you’ll need to reattach.

The structure of the script is basically the same as the one in the first post, so I’ll let you refer back to it for the explanations. Without further ado, here is the script for AdventureWorksDW2008R2, which will create a new AdventureWorksDW2013R2 database.

 

/*-----------------------------------------------------------------------------------------------*/
/* Updating AdventureWorks2008R2 for Today */
/* */
/* Robert C. Cain, http://arcanecode.com @ArcaneCode */
/* */
/* Script Copyright (c) 2013 by Robert C. Cain */
/* AdventureWorks database Copyright (c) Microsoft. */
/* */
/* This script will make a backup of the AdventureWorks2012DW database, then copy and restore it */
/* as AdventureWorksDW2013. It will then update it for current dates. 2008 now becomes 2013, */
/* 2007 is now 2012, and so forth. This script is dependent on the AdventureWorks2008R2DW sample */
/* database already being installed. It won't change AdventureWorksDW2008R2 in anyway. */
/* */
/* Be warned, if AdventureWorksDW2013R2 exists, it will be deleted as part of this process. */
/* */
/*-----------------------------------------------------------------------------------------------*/

PRINT 'Updating AdventureWorksDW2008R2 for Today - Starting'
GO

/*-----------------------------------------------------------------------------------------------*/
/* Step 1 - Make a copy of AdventureWorksDW2008R2 and restore as AdventureWorksDW2013 */
/*-----------------------------------------------------------------------------------------------*/
SET NOCOUNT ON

USE [master]

-- Step 1.1. Make a backup of AdventureWorksDW2008R2 ----------------------------------------------
PRINT 'Backing up AdventureWorksDW2008R2'
GO

BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\AdventureWorksDW2008R2.bak'
WITH NOFORMAT,
INIT,
NAME = N'AdventureWorksDW2008R2-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO


-- Step 1.2. Delete the database AdventureWorksDW2013 if it exists ------------------------------
PRINT 'Deleting AdventureWorksDW2013R2, if it exists'
GO

IF (EXISTS (SELECT 1
FROM master.dbo.sysdatabases
WHERE name = 'AdventureWorksDW2013R2' )
)
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'AdventureWorksDW2013R2'
GO

IF (EXISTS (SELECT 1
FROM master.dbo.sysdatabases
WHERE name = 'AdventureWorksDW2013R2' )
)
DROP DATABASE [AdventureWorksDW2013R2]
GO

-- Step 1.3. Restore the database to a new copy -------------------------------------------------
PRINT 'Restoring AdventureWorksDW2008R2 to AdventureWorksDW2013R2'
GO

RESTORE DATABASE [AdventureWorksDW2013R2]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\AdventureWorksDW2008R2.bak'
WITH FILE = 1,
MOVE N'AdventureWorksDW2008R2_Data'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\AdventureWorksDW2013R2_Data.mdf',
MOVE N'AdventureWorksDW2008R2_Log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\AdventureWorksDW2013R2_log.ldf',
NOUNLOAD, STATS = 5

GO

PRINT 'Done Creating AdventureWorksDW2013R2'
GO



/*-----------------------------------------------------------------------------------------------*/
/* Step 2. Create a helper function to convert dates to a YYYYMMDD format Date Id. */
/*-----------------------------------------------------------------------------------------------*/

USE [AdventureWorksDW2013R2]
GO

IF EXISTS (SELECT [name] FROM [sys].[all_objects] WHERE [name] = 'DateToDateId')
DROP FUNCTION [dbo].[DateToDateId];
GO

CREATE FUNCTION [dbo].[DateToDateId]
(
@Date DATETIME
)
RETURNS INT
AS
BEGIN

DECLARE @DateId AS INT
DECLARE @TodayId AS INT

SET @TodayId = YEAR(GETDATE()) * 10000
+ MONTH(GETDATE()) * 100
+ DAY(GETDATE())

-- If the date is missing, or a placeholder for a missing date, set to the Id for missing dates
-- Else convert the date to an integer
IF @Date IS NULL OR @Date = '1900-01-01' OR @Date = -1
SET @DateId = -1
ELSE
BEGIN
SET @DateId = YEAR(@Date) * 10000
+ MONTH(@Date) * 100
+ DAY(@Date)
END

-- If there's any data prior to 2000 it was incorrectly entered, mark it as missing
IF @DateId BETWEEN 0 AND 19991231
SET @DateId = -1

-- Commented out for this project as future dates are OK
-- If the date is in the future, don't allow it, change to missing
-- IF @DateId > @TodayId
-- SET @DateId = -1

RETURN @DateId

END

GO




/*-----------------------------------------------------------------------------------------------*/
/* Step 3. Add new dates to the dbo.DimDate table. */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Adding new dates to dbo.DimDate'
GO

SET NOCOUNT ON

-- Later we will be writing an INSERT INTO... SELECT FROM to insert the new record. I want to
-- join the day and month name memory variable tables, but need to have something to join to.
-- Since everything is calculated, we'll just create this little bogus table to have something
-- to select from.
DECLARE @BogusTable TABLE
( PK TINYINT)

INSERT INTO @BogusTable SELECT 1;


-- Create a table variable to hold the days of the week with their various language versions
DECLARE @DayNameTable TABLE
( [DayNumberOFWeek] TINYINT
, [EnglishDayNameOfWeek] NVARCHAR(10)
, [SpanishDayNameOfWeek] NVARCHAR(10)
, [FrenchDayNameOfWeek] NVARCHAR(10)
)

INSERT INTO @DayNameTable
SELECT DISTINCT
[DayNumberOFWeek]
, [EnglishDayNameOfWeek]
, [SpanishDayNameOfWeek]
, [FrenchDayNameOfWeek]
FROM dbo.DimDate

-- Create a month table to hold the months and their language versions.
DECLARE @MonthNameTable TABLE
( [MonthNumberOfYear] TINYINT
, [EnglishMonthName] NVARCHAR(10)
, [SpanishMonthName] NVARCHAR(10)
, [FrenchMonthName] NVARCHAR(10)
)

INSERT INTO @MonthNameTable
SELECT DISTINCT
[MonthNumberOfYear]
, [EnglishMonthName]
, [SpanishMonthName]
, [FrenchMonthName]
FROM dbo.DimDate


-- Some dates for 2010 are already there so we have to work around it in two passes
-- FiscalDate will be set six months into the future from the CurrentDate
DECLARE @FiscalDate AS DATE

-- Pass 1
DECLARE @FromDate AS DATE = '2010-01-01'
DECLARE @ThruDate AS DATE = '2010-10-31'
DECLARE @CurrentDate AS DATE

-- CurrentDate will be incremented each time through the loop below.
SET @CurrentDate = @FromDate

-- Now we simply loop over every date between the From and Thru, inserting the
-- calculated values into DimDate.
WHILE @CurrentDate <= @ThruDate
BEGIN

SET @FiscalDate = DATEADD(m, 6, @CurrentDate)

INSERT INTO dbo.DimDate
SELECT [dbo].[DateToDateId](@CurrentDate)
, @CurrentDate
, DATEPART(dw, @CurrentDate) AS DayNumberOFWeek
, d.EnglishDayNameOfWeek
, d.SpanishDayNameOfWeek
, d.FrenchDayNameOfWeek
, DAY(@CurrentDate) AS DayNumberOfMonth
, DATEPART(dy, @CurrentDate) AS DayNumberOfYear
, DATEPART(wk, @CurrentDate) AS WeekNumberOfYear
, m.EnglishMonthName
, m.SpanishMonthName
, m.FrenchMonthName
, MONTH(@CurrentDate) AS MonthNumberOfYear
, DATEPART(q, @CurrentDate) AS CalendarQuarter
, YEAR(@CurrentDate) AS CalendarYear
, CASE WHEN MONTH(@CurrentDate) < 7
THEN 1
ELSE 2
END AS CalendarSemester
, DATEPART(q, @FiscalDate) AS FiscalQuarter
, YEAR(@FiscalDate) AS FiscalYear
, CASE WHEN MONTH(@FiscalDate) < 7
THEN 1
ELSE 2
END AS FiscalSemester
FROM @BogusTable
JOIN @DayNameTable d
ON DATEPART(dw, @CurrentDate) = d.[DayNumberOFWeek]
JOIN @MonthNameTable m
ON MONTH(@CurrentDate) = m.MonthNumberOfYear

SET @CurrentDate = DATEADD(d, 1, @CurrentDate)
END

-- Pass 2
-- This is the start and end date ranges to use to populate the
-- dbo.DimDate dimension. Change if it's 2014 and you run across this script.
SET @FromDate = '2010-12-01'
SET @ThruDate = '2013-12-31'

-- CurrentDate will be incremented each time through the loop below.
SET @CurrentDate = @FromDate

-- Now we simply loop over every date between the From and Thru, inserting the
-- calculated values into DimDate.
WHILE @CurrentDate <= @ThruDate
BEGIN

SET @FiscalDate = DATEADD(m, 6, @CurrentDate)

INSERT INTO dbo.DimDate
SELECT [dbo].[DateToDateId](@CurrentDate)
, @CurrentDate
, DATEPART(dw, @CurrentDate) AS DayNumberOFWeek
, d.EnglishDayNameOfWeek
, d.SpanishDayNameOfWeek
, d.FrenchDayNameOfWeek
, DAY(@CurrentDate) AS DayNumberOfMonth
, DATEPART(dy, @CurrentDate) AS DayNumberOfYear
, DATEPART(wk, @CurrentDate) AS WeekNumberOfYear
, m.EnglishMonthName
, m.SpanishMonthName
, m.FrenchMonthName
, MONTH(@CurrentDate) AS MonthNumberOfYear
, DATEPART(q, @CurrentDate) AS CalendarQuarter
, YEAR(@CurrentDate) AS CalendarYear
, CASE WHEN MONTH(@CurrentDate) < 7
THEN 1
ELSE 2
END AS CalendarSemester
, DATEPART(q, @FiscalDate) AS FiscalQuarter
, YEAR(@FiscalDate) AS FiscalYear
, CASE WHEN MONTH(@FiscalDate) < 7
THEN 1
ELSE 2
END AS FiscalSemester
FROM @BogusTable
JOIN @DayNameTable d
ON DATEPART(dw, @CurrentDate) = d.[DayNumberOFWeek]
JOIN @MonthNameTable m
ON MONTH(@CurrentDate) = m.MonthNumberOfYear

SET @CurrentDate = DATEADD(d, 1, @CurrentDate)
END
GO

-- If you want to verify you can uncomment this line.
-- SELECT * FROM dbo.DimDate WHERE DateKey > 20110000

PRINT 'Done adding new dates to dbo.DimDate'
GO


/*-----------------------------------------------------------------------------------------------*/
/* Step 4. Update the Fact Tables with the new dates. */
/*-----------------------------------------------------------------------------------------------*/


PRINT 'Update Fact Tables'
GO

SET NOCOUNT ON

-- To move forward five years, we simply add 50,000 to the date key

-- 4.1 FactFinance ------------------------------------------------------------------------------
PRINT ' FactFinance'
GO

UPDATE [dbo].[FactFinance]
SET [DateKey] = [DateKey] + 50000;


-- 4.2 FactInternetSales ------------------------------------------------------------------------
PRINT ' FactInternetSales'
GO

-- There are a few rows where the due date is on leap year. Update these to back off a day
-- so the date add works OK
UPDATE [dbo].[FactInternetSales]
SET [OrderDateKey] = 20080228
WHERE [OrderDateKey] = 20080229

UPDATE [dbo].[FactInternetSales]
SET [DueDateKey] = 20080228
WHERE [DueDateKey] = 20080229

UPDATE [dbo].[FactInternetSales]
SET [ShipDateKey] = 20080228
WHERE [ShipDateKey] = 20080229

-- Now update the rest of the days.
UPDATE [dbo].[FactInternetSales]
SET [OrderDateKey] = [OrderDateKey] + 50000
, [DueDateKey] = [DueDateKey] + 50000
, [ShipDateKey] = [ShipDateKey] + 50000


-- 4.3 FactResellerSales ------------------------------------------------------------------------
PRINT ' FactResellerSales'
GO

-- As with Internet Sales, there are rows where the due date is on leap year.
-- Update these to back off a day so the date add works OK
UPDATE [dbo].[FactResellerSales]
SET [OrderDateKey] = 20080228
WHERE [OrderDateKey] = 20080229

UPDATE [dbo].[FactResellerSales]
SET [DueDateKey] = 20080228
WHERE [DueDateKey] = 20080229

UPDATE [dbo].[FactResellerSales]
SET [ShipDateKey] = 20080228
WHERE [ShipDateKey] = 20080229

-- Now update the table
UPDATE [dbo].[FactResellerSales]
SET [OrderDateKey] = [OrderDateKey] + 50000
, [DueDateKey] = [DueDateKey] + 50000
, [ShipDateKey] = [ShipDateKey] + 50000

-- 4.4 FactSalesQuota ---------------------------------------------------------------------------
PRINT ' FactSalesQuota'
GO

UPDATE [dbo].[FactSalesQuota]
SET [DateKey] = [DateKey] + 50000

-- 4.5 FactSurveyResponse -----------------------------------------------------------------------
PRINT ' FactSurveyResponse'
GO

UPDATE [dbo].[FactSurveyResponse]
SET [DateKey] = [DateKey] + 50000

-- 4.6 FactCallCenter ---------------------------------------------------------------------------
PRINT ' FactCallCenter'
GO

-- All the rows in call center have a 2010 date, just add 3 years to make these 2013
UPDATE [dbo].[FactCallCenter]
SET [DateKey] = [DateKey] + 30000


-- 4.7 FactCurrencyRate -------------------------------------------------------------------------
PRINT ' FactCurrencyRate'
GO

-- Because the DateKey is part of the PK, we have to drop the key before we can update it
ALTER TABLE [dbo].[FactCurrencyRate] DROP CONSTRAINT [PK_FactCurrencyRate_CurrencyKey_DateKey]
GO

-- Shift the 2008 Leap Year days to 2012 Leap Year
UPDATE [dbo].[FactCurrencyRate]
SET [DateKey] = 20120229
WHERE [DateKey] = 20080229

-- Update everything except the leap year we fixed already
UPDATE [dbo].[FactCurrencyRate]
SET [DateKey] = [DateKey] + 50000
WHERE [DateKey] <> 20120229

-- Add the PK back
ALTER TABLE [dbo].[FactCurrencyRate]
ADD CONSTRAINT [PK_FactCurrencyRate_CurrencyKey_DateKey] PRIMARY KEY CLUSTERED
( [CurrencyKey] ASC,
[DateKey] ASC
)
WITH ( PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF
, ONLINE = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
GO


PRINT 'Done updating the Fact tables'
GO



/*-----------------------------------------------------------------------------------------------*/
/* Step 5. Cleanup, remove the helper function we added earlier. */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Removing Helper Function'
GO

IF EXISTS (SELECT 1 FROM [sys].[all_objects] WHERE [name] = 'DateToDateId')
DROP FUNCTION [dbo].[DateToDateId];
GO

/*-----------------------------------------------------------------------------------------------*/
/* All done! */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Updating AdventureWorksDW2008R2 for Today - Completed'
GO

PowerShell, Atlanta Techstravaganza, and reading the PowerScripting.net RSS Feed with PowerShell

Today I had the pleasure of co-presenting “What’s new in Windows 8 PowerShell” (aka PowerShell v3) with my friend, Microsoft Regional Developer Evangelist Glen Gordon (blog | twitter).

You can find the slide deck for our presentation here.

We had a lot of code demos in the presentation, and some due to the short time period we didn’t get to. I’ll be posting the demos over the next few weeks, doing it slowly so as not to overwhelm anyone.

Below is the code to pull the RSS feed for the PowerScripting podcast. The code is pretty self explanatory, see the comments for more info.

#region Reading RSS Feeds

  #----------------------------------------------------------------------------
  # Reading RSS Feeds
  #----------------------------------------------------------------------------
  
    # Reading RSS Feeds has become even easier in v3
    $url = "http://feeds.feedburner.com/Powerscripting?format=xml"
  
    # v2 Syntax
    $webClient = New-Object net.webclient
    [xml]$rss = $webClient.DownloadString($url)
    $rss.rss.channel.item |
       Select-Object Title, pubDate, duration  |
       Format-List 
  
    # v3 Syntax
    Invoke-RestMethod $url |
       Select-Object Title, pubDate, duration  |
       Format-List 

    # To know the available object names, open the RSS
    # feed and look at the XML tags inside. The ones
    # shown in this example are pretty commong to most feeds.
  
#endregion Reading RSS Feeds

Presenting SQL Server 2005 2008 Full Text Searching at TechMixer University

On August 19th, 2008 I am presenting “Getting Started with SQL Server 2005/2008″ at the TechMixer University conference that is taking place between in Birmingham Alabama. This post will have all the links relevant to my talk.

Detailed descriptive material can be found on my blog, ArcaneCode.com, with links to specific posts below.

First off, the slides and sample code can be located at the Code Gallery site I setup specifically for Full Text Searching with SQL Server:

http://code.msdn.microsoft.com/SqlServerFTS

Look on the downloads page to see various projects around SQL Server Full Text Searching. I’ve created one “release” for each of the projects around FTS. Be sure to look on the right side at the various releases in order to see the various projects.

Next, you can get started with the basics by reading these entries on my blog:

Lesson 0 – Getting the Bits to do Full Text Searching in SQL Server 2005
Lesson 1 – The Catalog
Lesson 2 – The Indexes
Lesson 3 – Using SQL
Lesson 4 – Valid Data Types
Lesson 5 – Advanced Searching

After that you’ll be ready for some advanced topics.

Can you hear me now? Checking to see if FTS is installed.
Exploring SQL Servers FullTextCatalogProperty Function
Using the ObjectPropertyEx Function
Using FORMSOF in SQL Server Full Text Searching
Creating Custom Thesaurus Entries in SQL Server 2005 and 2008 Full Text Search
Creating and Customizing Noise Words in SQL Server 2005 Full Text Search
Creating and Customizing Noise Words / StopWords in SQL Server 2008 Full Text Search
Advanced Queries for Using SQL Server 2008 Full Text Search StopWords / StopLists

SSIS Issue From Monday

Just a quick update today, due to being swamped with meetings and travel. We managed to resolve the SSIS issue from Monday by applying SQL Server 2005 Service Pack 2.  So if you encounter a “Scripts failed to load” error, verify which service pack your SQL Server is on.

WOW!

With taking vacation the first week of the month, and having three different user group meetings last week (one of which I presented at) I’ve gotten a little behind in my podcast listening. After work yesterday I was cutting my grass and using the time to finally get up to date on my listening.

I was putting away the mower when last week’s Run As Radio came on my player. You can imagine my shock when all of a sudden I hear Greg and Richard say my name! I’m surprised I didn’t fall over right there in the pile of grass clippings.

Back when they were at Tech Ed I had done a small blog post on the show, which Greg found and you can read at http://arcanecode.wordpress.com/2007/06/15/arcane-fun-fridays-run-as-radio/ . Greg also sent me a nice e-mail.

I took the time to respond, and then to be honest got busy and forgot about it. So it was quite a rush to hear my message read on the air. To address a few quick points about it…

Yes, I do admit I was simplifying a bit when I talked about server admins/DBA and their domains, but I still feel that it’s the developer who has to glue all of those services together. Don’t overlook those guys though, if you work with them they can be a developer’s best friend. I know at our work place I’ve worked quite closely with the DBA group, they are extremely helpful when it comes to things like database design and optimizing queiries.

I also want to reiterate my point that all developers should have at least a basic understanding of the way other areas of IT work. That’s why I was so thrilled when Pwop came out with Run As Radio, it is a great resource to fill in that gap in my education. Now if we could just get them to do a show on SQL Server!

If you want to listen to the show yourself, it’s show number 14:

http://www.runasradio.com/default.aspx?showNum=14

Finally I’d like to announce a new feature of the blog, Arcane Lessons. Because of the technical nature of my writing, a lot of topics get broken up over multiple posts, over several days. It can make it a bit difficult to make sure you’ve gotten everything.

To solve that I’ve added a new page I’m calling Arcane Lessons. It lists the topic, then all the posts I’ve done for that topic. Take a look by clicking the “Arcane Lessons” link at the top of the page, or add http://arcanecode.wordpress.com/arcane-lessons/ to your favorites.

Currently I have three up there now, “Getting Started with SQL Server 2005 Full Text Searching”, “Windows Services in C#”, and “Event Logging”. Be sure to check back, I have many more I’ll be adding over the next few weeks such as the “SQL Server Compact Edition” series, or the “Installing Ubuntu under VirtualPC” series.

Arcane Fun… Saturdays?

Sorry for missing my usual Friday post, I was having ISP issues (which are still unresolved, but I’ve done a workaround for now).

This weekend I’ll be participating in something very geeky, it’s called Field Day. Each year on the fourth full weekend in June amateur radio operators (you may have heard them called “Hams”) get together to practice their emergency response preparedness, fellowship and have a good time.

The idea behind Field Day is for the hams in a community to gather at a single location, setup radios, equipment, run off of emergency power, and generally practice what we would do in case of an emergency. At the same time my local clubs are gathered, other clubs will be gathering in their communities as well. We’ll then get on the air and communicate with each other, exchanging brief messages similar to what we would do in the event of a real emergency.

This preparedness has already paid off, several times. In the days after 9/11 amateur radio was the chief form of communication. More recently, the hurricanes that devastated Louisiana, Mississippi, and parts of Alabama provided a wide scale communications effort. For months it was amateur radio that provided the communications links between emergency responders as well as relief agencies like the Red Cross and United Way.

In this day and age you might be thinking “is amateur radio still around? I thought cell phones and the internet got rid of it?” Not so. Most amateur radio equipment can be setup with a minimum of requirements. A decent 12 volt battery, the radio, and some wire in a tree and the radio operator is in business. The internet doesn’t work so well without power, and the cell phones don’t seem to work to well after a hurricane knocks the cell towers onto the ground.

Community education is the other component to Field Day. Often we gather in public places like parks so that we can be seen by folks driving or walking by. This year my clubs, the Shelby County Amateur Radio Club and the Birmingham Amateur Radio Club are joining forces and will be at Oak Mountain State Park near the fishing lake. I’m sure in your community hams will be gathering too.

If you happen to be out and about and see a bunch of guys bent over radios, wander up and say hello. They’ll be glad to show you around, maybe even let you get on the air. There’s nothing quite like the thrill of picking up a microphone and realizing the guy you are talking to is on the other side of the planet, then realizing the only thing making it happen is the little box in front of you and a piece of wire strung up in a tree! Who needs the internet anyway?

Dictionaries in C#: The SortedList

There are times when you need to sort your collection easily. Fortunately, there is a special dictionary called the SortedList to handle these needs. The SortedList is part of the System.Collections.Specialized library (don’t forget your using reference!).

There is one thing to understand that is a bit counter intuitive. The SortedList sorts off of the Key, and not the Value. While this may not seem natural, it can work to your advantage in times when users want to have peculiar sort orders. You can store the odd sort in the key, then display the values to the users.

In my simple example below, I’ve created a SortedList of the artists I might listen to during a day of programming. To make the sort case insensitive, I entered all the keys in lower case, then the value is the artist name in normal type.

      SortedList myMusic = new SortedList();

 

      myMusic.Add(“zztop”, “ZZTop”);

      myMusic.Add(“midnight synidicate”, “Midnight Syndicate”);

      myMusic.Add(“kate bush”, “Kate Bush”);

      myMusic.Add(“bond”, “Bond”);

      myMusic.Add(“clint black”, “Clint Black”);

      myMusic.Add(“queen latifah”, “Queen Latifah”);

 

      foreach (DictionaryEntry favoriteArtist in myMusic)

      {

        Console.WriteLine(favoriteArtist.Value);

      }

 

Produces this output in the command window:

Bond
Clint Black
Kate Bush
Midnight Syndicate
Queen Latifah
ZZTop

When you need to maintain a frequently changing list of values that need to be sorted, using the SortedList dictionary can be a real time saver.

Dictionaries in C#: The ListDictionary

Hashtables (see my posts from the last two days ago) are a great, general use type of dictionary, but they have the one drawback of requiring some extra overhead to implement. For very small collections, say 10 items or less, the overhead can have an impact on performance. To solve this, Microsoft has created an alternate form of hashtable called a ListDictionary.

Behind the scenes, ListDictionaries are implemented as simple arrays. For small collections then their performance is very fast. They have the same interface as a hashtable, so it’s very easy to swap from one to another, aside of course from the issue of having to recompile your project.

Before starting, in addition to the using System.Collections reference, you will also need to include a using System.Collections.Specialized reference to the top of your code.

Next, all you have to do is create a variable of type ListDictionary. After that, using it is identical to using a HashTable.

      ListDictionary genders = new ListDictionary();

 

      genders.Add(“M”, “Male”);

      genders.Add(“F”, “Female”);

 

Frequently when dealing with collections the size of the collection is quite small. Lists of values for drop down boxes, which hold data for things like employee type, gender, ethnicity, and perhaps ranges of ages are all times when you will have a short list. For those times, when you are certain the list is small, use a ListDictionary to increase the performance of your application.

Follow

Get every new post delivered to your Inbox.

Join 93 other followers