In one of our custom DNN modules we are grabbing the list of Countries and Regions for each Country for the DotNetNuke Host List. This is normally a good thing since we then have a standardized list of Countries/Regions, and it also allows the DNN Site Administrator to add new Countries and/or Regions to the list and have them show up automatically in our module.
The problem is that the DNN ListController API seems to be very slow when dealing with larger sets of Countries and Regions. A customer of ours had customized their list to include many more countries, and many more regions for each country. All of a sudden our module slowed to a crawl!
I ran the RedGate ANTS profiler on the code and found the “GetListEntryInfoCollection()” method on the “ListController” DNN class was taking almost 4 seconds to execute!!
I ended up writing my own helper method to retrieve the same list in under 0.5 seconds. This may not be a best practice, since my function is calling straight SQL through ADO.Net, but it’s definitely faster to execute!
The relevant code snippets are below.
——————————————————————-
internal static List GetCountryListAdoNet()
{
string sql = string.Format(@"
SELECT
country.Value as CountryCode
,country.[Text] as CountryName
,region.Value as RegionCode
,region.[Text] as RegionName
FROM {0}Lists country
LEFT JOIN {0}Lists region ON country.EntryID = region.ParentID
WHERE country.ListName = 'Country'
--ORDER BY country.[Text], region.[Text]
", GetDbObjectQualifier() ?? "");
Dictionary countries = new Dictionary();
using (IDataReader reader = DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(sql))
{
while (reader.Read())
{
string countryCode = reader.GetString(0);
string countryName = reader.GetString(1);
CountryInfo country;
if(!countries.TryGetValue(countryCode, out country))
{
country = new CountryInfo() { CountryCode = countryCode, Name = countryName };
countries[countryCode] = country;
}
if (!reader.IsDBNull(2) && !reader.IsDBNull(3))
{
country.Regions.Add(reader.GetString(3), new RegionInfo() { RegionCode = reader.GetString(2), Name = reader.GetString(3) });
//country.Regions.Add(new RegionInfo() { RegionCode = reader.GetString(2), Name = reader.GetString(3) });
}
}
}
var list = countries.Values.ToList();
list.Sort((left, right) => left.Name.CompareTo(right.Name));
return list;
}
internal class CountryInfo
{
public string CountryCode { get; set; }
public string Name { get; set; }
public SortedList Regions { get; set; }
public CountryInfo()
{
Regions = new SortedList();
}
}
internal class RegionInfo
{
public string RegionCode { get; set; }
public string Name { get; set; }
}
