C# Utility method to populate list controls with all countries as given in ISO 3166-1

Most projects I work on need a list of countries at some point so I put together a snippet of SQL that I could reuse to create and populate a countries table in the database with all countries as given in ISO 3166-1. After recently writing a utility class to populate list controls with world currencies according to ISO 4217 it got me wondering if I could also do the same for countries using only the .Net Framework. And so I came up with the following utility class to do the job.

        /// <summary>

        /// Populates the list control with countries as given by ISO 4217.

        /// </summary>

        /// <param name="ctrl">The list control to populate.</param>

        public static void FillWithISOCountries(ListControl ctrl)

        {

            foreach (CultureInfo cultureInfo in CultureInfo.GetCultures(CultureTypes.SpecificCultures))

            {

                RegionInfo regionInfo = new RegionInfo(cultureInfo.LCID);

                if (ctrl.Items.FindByValue(regionInfo.TwoLetterISORegionName) == null)

                {

                    ctrl.Items.Add(new ListItem(regionInfo.EnglishName, regionInfo.TwoLetterISORegionName));

                }

            }

            RegionInfo currentRegionInfo = new RegionInfo(CultureInfo.CurrentCulture.LCID);

            //- Default the selection to the current cultures country

            if (ctrl.Items.FindByValue(currentRegionInfo.TwoLetterISORegionName) != null)

            {

                ctrl.Items.FindByValue(currentRegionInfo.TwoLetterISORegionName).Selected = true;

            }

        }

More Info

Read More

Dynamic WHERE Clause with SQL Server Stored Procedure

Working with SQL Server over the years has made me realize just how much learning I have to do before I can classify myself a database guru.  Along the way, I have learned a lot of cool things from various sources, but still have plenty more areas to study.  Most of the things that I've learned about SQL Server, as well as many other things, have come from a need for a solution to a particular situation.  What I will be discussing today came from one of those situations.

The problem I had to solve involved querying the database with several different parameter options.  The same data was to be retrieved with each query, but it was the filtering of the data that changed.  My initial reaction was to write a stored procedure for each of the different parameter permutations.  The problems with that approach are numerous, so let's just say it was too much of a waste of time.  I started looking for alternatives and found this blog post discussing the basics of just what I needed to do.  Since I'm using this blog as a way for me to archive some of the cool things I've learned, I'll put my own spin on the topic here.

The basics are this: make a single stored procedure that will take in all the possible parameters, giving each one a default value of NULL in case it is not needed, and return the necessary data filtered accordingly.  Sound simple?  Actually, it really is.  Here is an example of a stored proc that does just that:

CREATE PROCEDURE GetCrap

    @CategoryID int = NULL,

    @MinPrice decimal(4,2) = NULL,

    @MaxPrice decimal(4,2) = NULL

AS

BEGIN

    SELECT *

    FROM TableWithCrap T

    WHERE (@CategoryID IS NULL OR T.CategoryID = @CategoryID) AND

          (@MinPrice IS NULL OR T.Price >= @MinPrice) AND

          (@MaxPrice IS NULL OR T.Price <= @MaxPrice)

END 

See how simple it is?  Simply give the optional parameters a default NULL value (@CategoryID int = NULL).  Then, and here's the cool part, make each part of the WHERE clause a conditional that will either evaluate to true if the parameter is null or evaluate according to the requirement.

@CategoryID IS NULL

will return true if the parameter CategoryID has not been given, thus eliminating it from the filtering process.

@CategoryID IS NULL OR T.CategoryID = @CategoryID

basically says, "if I have a value, use me."

That's it!  AND those bad boys together and you've got a respectable dynamic query built right into a nice stored procedure!  Isn't that cool?

More Info

Read More