Nik's Technology Blog

Travels through programming, networks, and computers

Enumerate Available Database Providers in ASP.NET

Using the DbProviderFactories class in ADO.NET you can retrieve a list of available database factories using the GetFactoryClasses method. This can be useful if you host your ASP.NET site on a shared server and don't have access to the web server and machine.config file.

The output of the GetFactoryClasses method is a DataTable of available factory classes, these are the factory classes that the .NET runtime will have access to.

Here's the kind of output you'll get...

Odbc Data Provider .Net Framework Data Provider for Odbc System.Data.Odbc System.Data.Odbc.OdbcFactory, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089
OleDb Data Provider .Net Framework Data Provider for OleDb System.Data.OleDb System.Data.OleDb.OleDbFactory, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089
OracleClient Data Provider .Net Framework Data Provider for Oracle System.Data.OracleClient System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089
SqlClient Data Provider .Net Framework Data Provider for SqlServer System.Data.SqlClient System.Data.SqlClient.SqlClientFactory, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089
Here's the code you'll need in your code behind file.

DataTable providersList = null;
providersList = System.Data.Common.DbProviderFactories.GetFactoryClasses();
GridView providerGridView = new GridView();
providerGridView.DataSource = providersList;

Migrating from ASP to ASP.NET 2.0

I've pretty much finished migrating my personal website from classic ASP to ASP.NET 2.0. In the end I decided to keep certain pages using ASP (Active Server Pages) technology (more on that in a moment), the majority of the pages however have been migrated.

Minimise Page Rank Loss

While I wanted to bring my site up-to-date I also didn't want to lose too much Google Page Rank in the process, and make people's bookmarks and RSS blog subscriptions stop functioning. The reason the pages have to change URLs is that ASP.NET pages use the extension .aspx, compared to ASP's .asp 3-digit extension. So my portfolio.asp page for example has become portfolio.aspx.

Analysing what can be Migrated

My blog area uses Google's Blogger as a CMS, so this area hasn't had to change, although prior to using Blogger I had previously built my own blog engine and this has remained as is.

The most popular part of my site is my Cisco CCNA section. Apart from the new menu page, the other pages have half-decent Page Rank and a few pages also have DMOZ entries, so those have had to remain ASP too.

Using 301 Permanent Redirects

All the other pages however have been migrated. When you now visit those old pages (from SERPS or old links) you'll get HTTP 301 redirected to the new ASP.NET pages. Because I'm on a shared server with no access to IIS (Internet Information Server), I essentially had to hard-code ASP 301 redirects on all the ASP pages that have moved, redirecting users to the new versions.

Update Robots.txt

The next step in the process was to include those old ASP pages in my robots.txt file and log-in to the Google Webmaster console to expedite the removal of those old pages from the Google index using the URL Removal tool. If you haven't already accessed Webmaster tools I highly recommend you log-in and verify your site.

Spidering as a Final Check

Next, I made sure all navigation menus and links to the old pages under my control were pointing to the new versions. This meant updating my Blogger template and republishing, updating my old ASP navigation include files and crawling my site using XENU link sleuth to check for any I had missed.


Moving my content over to ASP.NET has been fairly straight forward due to the small number of pages, my Tools and Portfolio pages display data stored in XML files, so it was just a case of using XmlDataSource controls to pull the information onto the pages. My homepage picks up the latest entries in my Blogger Atom feed using XSLT, and my contact form uses basic ASP.NET form and validation controls.

Increased Functionality

While migrating my content I thought I'd use the caching feature built-in to ASP.NET to allow me to display my latest ma.gnolia bookmarks on my site, so I ended-up creating a Bookmarks page, which fetches my ma.gnolia lite RSS bookmarks XML file, either from or my cache. The cache doesn't hold my data for as long as I stipulate, but I'm assuming this is because I'm on a shared server and the cache is dropping it to free resources.

Insert a Blogger Atom Feed into an ASP.NET Web Page

I've been busy recently migrated my homepage (and several others) from classic ASP to ASP.NET. My homepage displays the latest 5 posts with a summary and a link to the full blog post.
I eventually found a tutorial using XSLT explaining how to achieve this after discovering that XmlDataSource XPATH doesn't support namespaces!
I've tinkered with the XSLT that Arnaud Weil posted in his blog to achieve the following objectives:

  1. Limit the amount of posts returned by the transformation.
  2. Show a summary of the post.
  3. Show a summary that tries hard not to cut words in half when generating a snippet.
  4. Produce XHTML valid code.

Here's the source of my XSLT...

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"

<!--<xsl:output method="html"/>-->
<xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>
<xsl:template match="/atom:feed">
<div id="FeedSnippets">
<xsl:apply-templates select="atom:entry" />

<xsl:template match="atom:entry" name="feed">
<xsl:if test="position()&lt;6">
<h4><xsl:value-of select="atom:title"/></h4>
<xsl:when test="string-length(substring-before(atom:summary,'. ')) &gt; 0">
<xsl:value-of select="substring-before(atom:summary,'. ')" />...<br />
<xsl:when test="string-length(substring-before(atom:summary,'.')) &gt; 0">
<xsl:value-of select="substring-before(atom:summary,'.')" />...<br />
<xsl:value-of select="substring(atom:summary,0,200)" />...<br />
<strong>Read full post: </strong><a href="{atom:link[@rel='alternate']/@href}"><xsl:value-of select="atom:title"/></a></p>
<hr />

An Overview of the ASP.NET Cache Object

I've been looking at the ASP.NET cache object, which makes the older Application object effectively redundant. The analogy is that it is like a leaky bucket. A bucket in which you can store data which is "expensive" to retrieve from its source every time. Since databases and files are considered slow in comparison with in-memory data then it makes sense to keep a copy of certain data you use frequently in the cache, which is stored in memory.
The bucket is leaky because you can only put so much data in the cache before it fills up and has to drop some. It uses a simple method called LRU - Least Recently Used to decide what data it disposes of by default. I say by default, because you can specify, if you so wish and make data persist for a period of time or make it dependent on other data on file, database, or cached data.

The cache is shared however by the whole application and is therefore accessible by the whole application, so you have to be careful what kind on data you store in here. You probably don't want to store user specific data here!
You also need to check that the data you want to use is in the cache before you go and use it. Sounds obvious, but if you don't code this right, by assigning the cached object to a variable then checking the variable you could discover bugs which you can't replicate in a test environment.

Data Layer with Business Logic

Deciding when to move business logic to the data layer in a multi-tiered application is a tricky one and depends a lot on the RDMS you are using.
I've been looking at an ASP.NET application that creates 7 rows in a database for each "musical performance" when a button is clicked on a web form to rebuild a database.
To begin with a SQL 2005 stored procedure written in VB.NET (But could easily have been C# with the same results since all .NET languages are compiled to IL). was called from a loop in the business layer 7 times to create the 7 rows. The application was run and it took about 90 seconds to rebuild a test database in this manner.

This logic was then moved into 1 stored procedure still written in VB.NET; The result was that instead of making 7 calls to the database each time, we make 1 call to the database and move the business logic, executing the loop there, and hence at the data layer. The database rebuild took about 30 seconds this time.

Stored Procedures in Native T-SQL

To try and make the execution faster we decided to write the stored procedure in T-SQL, which is still the native language of SQL Server. This shaved another 15 seconds of the execution time! Just proving what sort of overhead writing your stored procedures in anything other than T-SQL introduces.

This exercise provides a glimpse into how the decisions you make when designing an application radically affect the responsiveness of the system.

Business Logic at the Data Layer

If you were pulling lots of data to the business layer performing some processing then discarding most of the data at that point, then it may improve performance if this operation was moved down to the data layer. It really depends on the data and the application.
One of the problems with storing too much application logic in the data layer is version control. It can become a mine field managing lots of complex stored procedures between test and live environments and keeping them in-sync with the application.

ASP.NET Web Accessibility and Visual Studio 2005

I've been using Visual Studio 2005 on a recent project and was surprised that even though it is supposed to feature lots of web accessiblity tools and options, they don't seem to be turned on by default. I'll bring you an example to back this up.
I created a new ASP.NET page, essentially it was a simple form, which when submitted sent an email; similar to a contact form if you like. The form was built within an HTML table, with ASP:Label controls to hold the textbox definitions.
On viewing the page in a browser and examining the source code, I noticed that the ASP:Label controls are converted to HTML <span> tags, which is a little bizarre. After a little research I found that if you use the AssociatedControlID property of the ASP:Label to link to the related textbox the HTML source code produced now uses an HTML <label> tag.
I also figured out that using the ToolTip property of the ASP:Label control renders as the title property of the HTML label tag.

So the following ASP.NET source code:

<asp:Label ID="LblDayMovedOut" runat="server" ToolTip="Day Moved Out" AssociatedControlID="DayMovedOut">
<asp:DropDownList ID="DayMovedOut" runat="server"></asp:DropDownList>
<asp:Label ID="LblMonthMovedOut" runat="server" ToolTip="Month Moved Out" AssociatedControlID="MonthMovedOut">
<asp:DropDownList ID="MonthMovedOut" runat="server"></asp:DropDownList>
<asp:Label ID="LblYearMovedOut" runat="server" ToolTip="Year Moved Out" AssociatedControlID="YearMovedOut">
<asp:DropDownList ID="YearMovedOut" runat="server"></asp:DropDownList>

Would render the following bloated, but accessible HTML:

<label for="ctl00_ContentPLaceHolder_DayMovedOut" id="ctl00_ContentPLaceHolder_LblDayMovedOut" title="Day Moved Out">
<select name="ctl00$ContentPLaceHolder$DayMovedOut" id="ctl00_ContentPLaceHolder_DayMovedOut">
<label for="ctl00_ContentPLaceHolder_MonthMovedOut" id="ctl00_ContentPLaceHolder_LblMonthMovedOut" title="Month Moved Out">
<select name="ctl00$ContentPLaceHolder$MonthMovedOut" id="ctl00_ContentPLaceHolder_MonthMovedOut">
<label for="ctl00_ContentPLaceHolder_YearMovedOut" id="ctl00_ContentPLaceHolder_LblYearMovedOut" title="Year Moved Out">
<select name="ctl00$ContentPLaceHolder$YearMovedOut" id="ctl00_ContentPLaceHolder_YearMovedOut">