My Toolbox column in the November 2009 issue of MSDN Magazine is available online and includes the following reviews:

• BI Documenter - point BI Documenter to the schema of your database and select which database objects to include. Then, with the click of a button, BI Documenter will create a Compiled Html Help File (.chm) or HTML pages that documents the selected database objects. You can also add rich database diagrams created using BI Documenter's built-in diagramming tool, as well as include your own image and Microsoft Word files.
• Blogs of Note: Beth Massi - Microsoftie Beth Massi has a great blog with tips, tricks, and tutorials on Office development, WPF, ADO.NET Data Services, and more. What makes Beth's blog unique is her passion for Visual Basic - all of her code samples are in VB and she often posts about upcoming language features and enhancements.
• CuttingEdge.Conditions - CuttingEdge.Conditions is an open source library that enables developers to define pre- and post-conditions using a fluent interface, which is an API design style that aims at maximizing readability through the use of descriptive names and method chaining. For example, you can define a pre-condition on the input parameter id like so:

public void DoSomething(int id)
{
    // Check all preconditions:
    Condition.Requires(id, "id")
            .IsInRange(1, 999)         // ArgumentOutOfRangeException on failure
            .IsNotEqualTo(128);        // throws ArgumentException on failure

    ...
}

This issue reviewed ASP.NET MVC Framework Unleashed, by Stephen Walther. An exerpt from my review follows:

Getting started with ASP.NET MVC involves a bit of a learning curve, even for experienced ASP.NET developers, because of the numerous differences between the two frameworks. For example, when creating an ASP.NET MVC application in Visual Studio, you are prompted to create a unit test project. With ASP.NET MVC, you design your Web pages using HTML along with a few helper classes—there are no Web controls to drag and drop onto the page. And unlike Web Forms, there are no baked-in postback or Web control event models. In short, there are a lot of new techniques to learn when moving from Web Forms to ASP.NET MVC. If you are an intermediate to experienced ASP.NET developer who wants to learn ASP.NET MVC, check out Stephen Walther’s latest book, “ASP.NET MVC Framework Unleashed” (Sams, 2009). Walther does an excellent job introducing new concepts and showing how they’re used—without overwhelming the reader with an avalanche of information.

Enjoy! - http://msdn.microsoft.com/en-us/magazine/ee335714.aspx

Imagine that you've got an ASP.NET page that is generating an email message that needs to include links back to the website. Perhaps you're writing the next greatest online message board application and when someone replies to a thread you want to send out emails to the other thread participants indicating that a new message has been posted along with a link to view the just-posted message. You know that the URL to view a particular thread is, say, ~/Threads/View.aspx?ThreadId=threadId. But how do you turn that relative URL into an absolute URL like http://www.yourserver.com/Threads/View.aspx?ThreadId=threadId?

The simplest answer is to hard code the domain name into the URL when generating the email's content, but that approach is naive and wouldn't work if you were building an application that others could buy and install on their servers. It also would cause a headache if you change your domain name. Finally, it's a headache because oftentimes an ASP.NET application has different URLs depending on the environment. From the development environment the URL might be http://localhost:somePortNumber/MyMessageboard/Threads/View.aspx?ThreadId=threadId, whereas on the staging environment it might be http://stagingserver/MB/Threads/View.aspx?ThreadId=threadId. Ideally, the email generated from a particular environment would contain the appropriate URL back to said environment.

The good news is that generating a fully qualified URL in ASP.NET is quite simple. The following code will pick up the correct HTTP or HTTPS setting, the correct server name - be it localhost, localhost:portNumber, serverInIntranet, an IP address, or a domain name (www.example.com) - and the applicaiton path, if any. Here's the code in both C# and VB:

// C#
string relativeUrl = "~/Threads/View.aspx?ThreadId=" + threadId;
string fullyQualifiedUrl = Request.Url.GetLeftPart(UriPartial.Authority) + Page.ResolveUrl(relativeUrl);

' VB
Dim relativeUrl As String = "~/Threads/View.aspx?ThreadId=" + threadId
Dim fullyQualifiedUrl As String = Request.Url.GetLeftPart(UriPartial.Authority) & Page.ResolveUrl(relativeUrl)

That's it! Pretty easy.

The Request.Url property returns the fully qualified URL of the currently requesting page. The GetLeftPart(UriPartial.Authority) method returns just the protocol and domain name, which is then concatenated with the resolved relative URL to give us the fully qualified URL for the relative URL.

For more on Request.Url, along with a look at the many other path-related properties available of the HttpRequest class, see Rick Strahl's blog entry, Making Sense of ASP.NET Paths.

I recently ran into a situation where I needed to delete all records from a table except for the 1,000 most recently added records. Specifically, I was working on a site that used Error Logging Modules And Handlers (ELMAH), a free, open source error logging library for ASP.NET applications. ELMAH doesn't automatically prune its error log. If you are using the database as a log source and if your web hosting provider has a database quota in effect, it is possible to have ELMAH's error log grow so large that the quota is surpassed and the database is taken offline. To help mitigate this problem, it is a good idea to take steps to keep ELAMH's error log size in check:

As with any sort of logging service is is important that ELMAH's log be periodically pruned. If you let ELMAH's log grow unchecked it can reduce performance when querying the log and suck up disk space, which is especially important in hosted environments where there are typically hard disk quote limits for each user on the database server. The good news is that there are a number of techniques you can employ to help ensure that your ELMAH error log stays a reasonable size.

• Use error filtering.
• Setup a weekly job in SQL Server to delete entries older than, say, three months.
• Update the ELMAH_LogError stored procedure to delete old log entries.

Keep in mind that trimming the error log brings with it a tradeoff: you are removing error log entries that might be important for analysis later in time. If this is the case, if you think you might need to review that error log from more than 90 days in the past, then before deleting records from the error log you should archive them somewhere.

While these approaches are certainly good measures to implement, they don't handle the case where there's a flood of errors in a short amount of time. If ELMAH's error log is being inundated with errors from the past 24 hours, a script that deletes error log entries older than three months is going to have no effect.

An alternative approach is to delete all entries in ELMAH other than the most recently added N error log entries. This ensures that the error log never exceeds a maximum number of records (N), while keeping the N most recent records. The following query will do the trick:

DELETE FROM ELMAH_Error
WHERE ErrorId NOT IN
        (SELECT TOP 1000 ErrorId
         FROM ELAMH_Error
         ORDER BY TimeUtc DESC)

The ErrorId column is the primary key in the ELMAH_Error table, and TimeUtc stores the UTC date/time the error was recorded. The subquery gets the first 1,000 ErrorId values ordered by the time the error was logged, from the most recently logged to the oldest. The query deletes all error records whose ErrorId values are not in that set of the 1,000 most recently added errors.

If you are using SQL Server 2005 or beyond, you can use the ROW_NUMBER() keyword in a query like so:

DELETE FROM ELMAH_Error
WHERE ErrorId IN (
      SELECT ErrorId
      FROM
        (
           SELECT ErrorId, ROW_NUMBER() OVER(ORDER BY TimeUtc DESC) AS RowIndex
           FROM ELMAH_Error
        ) AS ErrorsWithRowNumbers
      WHERE RowIndex > 1000
)

The innermost query uses the ROW_NUMBER() keyword to assign a row number to each record from ELMAH_Error, numbering the most recently logged error 1, the next one 2, and so forth, such that the oldest error will have the largest row number assigned. This innermost query is used as a derived table. The query that uses it gets the ErrorId values whose row number is greater than 1,000, which returns those ErrorId values for errors that are “ranked” 1,001 or higher. In other words, it returns those ErrorId values for errors that are not one of the 1,000 most recently added errors. Finally, this set of ErrorIds are deleted from the table.

I ended up using the second query (the one with ROW_NUMBER()), placing it in the ELMAH_LogError stored procedure (along with a DELETE statement that removes any error log entries older than 3 weeks). If you use either one of these queries, consider adding an index on the TimeUtc column sorted in descending order.

Happy Programming!

One of the key advantages of web applications is that they can be deployed as a hosted service and accessed over the Internet rather than needing to be locally installed at a customer's site. Over the years I've helped numerous clients build web applications of this sort. When building such an application there are bound to be questions on the best way to model and store data from the different customers that access the hosted application. Do you co-mingle customer data in a single database with a single schema? Do you use a single database, but separate customer data into different schemas? Or do you create a separate database with separate tables for each customer?

These three data architectures are examined and discussed in detail in the article Multi-Tenant Data Architecture. The authors introduce three different multi-tenant data architectures, which they name:

• Separate Databases - in this architecture each customer's data is stored in a separate database. The databases may all be on the same database server or they could be partitioned across multiple database servers. This approach provides for maximum isolation of customer data. If we were building a hosted application using the Northwind database and had three customers then with this approach we would have three databases - Northwind01, Northwind02, and Northwind03 - and each database would have the same tables, views, stored procedures, and so on. Customer 1's data would all be located in the Northwind01 database, while Customer 2's data would be over in the Northwind02 database.
  
• Shared Databases, Separate Schemas - SQL Server 2005 introduced the concept of schemas, which offer a way to group a set of tables. With this approach you can have a single database with one schema for each customer. Returning to the Northwind example, with this approach there would be a single database, but there would be three schemas - Customer01Schema, Customer02Schema, and Customer03 schema. Each schema would have the same set of tables, views, stored procedures, and so forth. Customer1's product information would be found in the Customer01Schema.Products table, whereas Customer2's product information would be stored in Customer02Schema.Products.
  
• Shared Database, Shared Schema - here we have only a single database and a single schema. There would be only one Products table. To differentiate one customer's products from another we'd need to add a NorthwindCustomers table that would have a record for each customer and then add a NorthwindCustomerID foreign key to the Products table (and to the other pertinent tables).

The Multi-Tenant Data Architecture examines these three approaches in much more detail with screen shots, T-SQL snippets, and more in-depth examples, and is definitely worth reading.

I'd like to talk a little bit about my “from the trenches” experience with multi-tenant data architectures. First, a little background.

Medical Software
In 2003 I started work with a client we'll call Acme Medical, which was building a hosted, ASP.NET medical software application. This application had been in existence for a couple of years by this point, but as a Microsoft Access application that was installed and run locally from a couple of hospitals and clinics. I joined the project with the aim of moving the application from Microsoft Access to ASP.NET and SQL Server. We chose to use a Separate Databases architecture. I continue to work on this project today. Originally, there were two customers, each with a few thousand records. The data model contained maybe 20-30 tables. Today there are more than 15 customers, each with hundreds of thousands if not millions of records. There are nearly 350 database tables, hundreds of ASP.NET pages, and several automated backend processes.

Print Management
Another client, Acme Printers, was a prominent player in the print shop marketplace, selling in-house applications to large-scale print shops. I helped this company build an online, hosted version for order placement and fulfillment. We used a Shared Database, Shared Schema data model. In a nutshell, there is a Customers table with a CustomerID primary key value uniquely identifying each customer. Every other database table has a CustomerID field that identifies what data belongs to what customer. When a user signs on, we look up what customer the user is associated with and then store this CustomerID in session. This CustomerID variable is then used in other pages on the site to pull back the data pertinent to that customer.

I have never used the Shared Database, Separate Schema approach. I recently spoke with Michael Campbell about this topic, and he shared the following advice: “I'd recommend against using shared DBs and separate schemas. In my experience that almost NEVER works out as advertised, adds all sorts of difficulty in terms of disaster recovery, management, and isolation, and really doesn't offer any worthwhile benefits. It also becomes absolutely insane in terms of managing permissions/security as well.”

The factors that should influence whether you go with a Separate Database or Shared Database, Shared Schema architecture are not technical, but rather are regulatory-, security-, or business-related. If you find yourself deciding to use one architecture over another because of some technological reason you're probably asking the wrong questions and evaluating the wrong criteria.

Here's how I tackle this problem. I start by assuming that I'm going to use the Shared Database, Shared Schema architecture, as it's usually the best fit for the types of projects I work on. It easier to setup and implement and test and debug and scale up (to a point) than the Separate Database architecture. For the majority of web applications, the Shared Database, Shared Schema architecture is the best approach when weighing just the technological- and development-related factors.

Sadly, in the real-world there are many factors that outweigh technological ones. While the Separate Databases architecture has more friction associated with it than the Shared Database, Shared Schema, it does have certain advantages in terms of security, privacy, isolation, and so on. When evaluating which architecture to use, I like to ask my client the following questions:

• How important is the security and privacy of the data?
  • For the medical software, security and privacy is paramount. Customers (the hospitals using the software) are entering social security numbers, lab test results, diagnoses from specialists and psychologists, and so on. The data is rife with very personal and sensitive patient information, not to mention information about the health care practitioners, including social security numbers, license information, and disciplinary actions.
• Are there any regulatory reasons for choosing one architecture over the other?
  • Depending on the line of business, there may be governmental or professional regulations that require a clean separation of data. For medical providers in the US there is a regulation known as HIPAA, which defines guidelines for patient privacy. It's been several years since I've explored HIPAA, but from my recollection HIPAA does not require that a Separate Database architecture be used, but using one ensures that patient data in one hospital is isolated from the doctors and staff at other hospitals.
• Do your customers want/need access to the database?
  • In some industries, customers using a hosted application demand access to the data. For the medical software, there have been a handful of clients who, before signing up, stipulated that they have access to a weekly backup of their data, which they could store on their computer system. With the Separate Databases approach it has been easy to service these requests - schedule a weekly backup for that customer's database and add an ASP.NET page that allows that customer's administrative users the ability to see past backups and download those backup files to their computer.
• How many customers do you expect to be using the system?
  • There is a maintenance cost associated with each new database. If you expect hundreds or thousands of customers then a Separate Databases architecture is probably going to be prohibitive from a maintenance standpoint.

Note that the above questions don't touch on technological or development issues. Rather, they are focusing on security, privacy, regulations, and the end user's needs or expectations.

While the Separate Databases architecture provides a higher degree of security and privacy through data isolation, there are a couple of challenges worth noting.

• Rolling out changes to the data model is more difficult. Imagine that you've added a new feature, which entailed adding two database tables, a new view, and six new stored procedures. In a Shared Database, Shared Schema architecture, rolling out that change is a matter of adding those database objects to the production database. With a Separate Databases architecture, you need to make sure to roll out those changes to all of the databases. With a systematic, tested, scripted process this is not much of a challenge, but if you don't have such a system defined - if this work is done manually, for instance - you're asking for trouble as you're undoubtedly going to have a scenario where the database changes get rolled out to some databases, but not all. This can be especially challenging if you have decided to customize the data model on a per-customer basis. If database X has certain tables or fields that are not found in database Y then this makes rolling out changes in an automated fashion more challenging. For this reason I would discourage making table-level customizations per customer if at all possible.
  
• Viewing data aggregated across the databases is difficult. I've touched upon this topic in an earlier blog post, Running the Same Query Against Multiple Databases. When you find a bug on one database and need to see whether it affects data in other databases there are not many tools at your disposal. One poor man's tool is sp_msForEachDb, but it's less than ideal.
  
• Adding a new customer requires creating a new database. Signing up a new customer is great news for the company's bottom line, but what changes does it entail in the data model? For a Shared Database, Shared Schema architecture, adding a new customer is as simple as adding a new record to the Customers table. For a Separate Databases architecture, adding a new customer means creating a new database and adding the appropriate database objects and any initial data in these tables. Using the SQL Server model database you can simplify this process, but in order to do so you need to make sure that any changes to the data model - new stored procedures, tables, views, UDFs, etc. - need to also be added to the model database. Similarly, any changes to the initial state - new default records in a lookup table, say - need to also be added to the appropriate tables in model.

Given the pros and cons of these two multi-tenant architectures, you can guess what architecture a company is using based on their price point and their targeted industry. If there is a high cost to sign up to the site then chances are the company has only a “few” clients (maybe just a few, maybe dozens, but probably not hundreds or thousands), and if the application is for medical, financial, or legal purposes then they probably use a Separate Databases architecture. For web applications with a lower price point and geared to industries where data privacy and security is less important and less encumbered by regulations, chances are a Shared Database, Shared Schema approach is being used. As you might expect, the medical software has around 15 customers, each paying thousands of dollars per month to use the service, whereas the print shop software has a couple hundred clients who pay a one time setup fee with the option to buy an annual support contract, which amortizes to hundreds of dollars per month per cusotmer.

Whether you choose the Shared Database, Shared Schema or Separate Databases architecture depends largely on non-technological factors. As I noted earlier, I typically choose the Shared Database, Shared Schema architecture by default, only switching to a Separate Databases architecture if there are particular security, privacy, regulatory, or other business needs that necessitate it.

The HTTP/1.1 protocol include support for range-specific requests, which allow a client to optionally request a particular range of bytes rather than request the entire file. This functionality is most commonly used by download manager programs, which allow users to pause and resume downloads. In a nutshell, a download manager will start by asking for the entire contents of a file (the default behavior). If the user pauses the download or if the download manager is shut down, the last downloaded byte is remembered. Later, the download manager can resume the download by sending a request to the server for the file starting from the last downloaded byte.

The following diagram illustrates the range-specific request workflow just described when downloading a large file named DancingHampsters.zip. The diagram shows what happens when the download is paused (or interrupted) after having downloaded the first 500,000 bytes, and how using range-specific requests the client can resume the download starting from a specified location in the file (rather than having to re-download the file in its entirety).

While IIS natively handles range-specific requests, ASP.NET does not. If you are serving binary content from an ASP.NET HTTP Handler and if you want (or need) to support range-specific requests then you'll need to add such functionality yourself. I bumped into this requirement when working on a project that serves videos to Apple's handheld devices - the iPhone and iPod Touch. The iPhone and iPod Touch request video files using range-specific requests. Therefore, if you are serving these videos straight from the file system via IIS then everything will work as expected, but if you serve the video from an HTTP Handler in order to implement authorization rules or if the files are dynamically generated then you'll need to write code in your HTTP Handler that will handle the range-specific requests sent by the Apple devices.

In my research I stumbled upon an article by Alexander Schaaf titled Tracking and Resuming Large File Downloads in ASP.NET, which presented Visual Basic code showing how to implement range-specific requests in an ASP.NET 1.x application. I took this code, refactored it, ported it to C#, and utilized a number of language enhancements added since the .NET 1.x days. This code, along with a discussion on how range-specific requests work and a look at how to use the code, is now available on DotNetSlackers.

Read more at: Range-Specific Requests in ASP.NET.

Happy Programming!