November 2008 - Posts

Visual Studio Keyboard Shortcut Tip: Ctrl+-
24 November 08 10:17 AM | Scott Mitchell

I learned this Visual Studio keyboard shortcut from a client a couple of months ago. I use it several times a day now.

Typing the Ctrl key and - (the minus key) returns you to the previous cursor location, be it in the same file or in a different, open file.

Visual Studio maintains your position in a file as a stack. So as you do a Find and jump to a particular location in a file, or right-click on a method or property name and choose “Go To Definition” Visual Studio keeps track of your position prior to these jumps. You can then 'pop' the position off the stack and return to it by hitting Ctrl+-.

This keyboard shortcut is quite useful when you're bug hunting or trying to understand how some bit of code is working and are drilling down into methods and search results. When you are ready to return to the previous location, which could be in another file or elsewhere in the currently opened file, just hit Ctrl+- to get back! And if you need to get back to the spot before that, hit Ctrl+- again.

Filed under:
New Version of Error Logging Modules and Handlers (ELMAH) Available
20 November 08 11:34 AM | Scott Mitchell

ELMAH is a free, open-source library created by Atif Aziz for logging errors that occur in an ASP.NET application. I've written about ELMAH many times before; its one of the first things I setup when creating a new ASP.NET application. A new version - ELMAH 1.0 BETA 3 - was recently released.

With just a few minutes of setup and configuration you can have ELMAH automatically log unhandled exceptions to a number of different data stores - SQL Server, Oracle, a Microsoft Access database, an XML file, to an e-mail address, and so on. You can also write code to proactively record an error via the ELMAH library. Granted, ASP.NET provides some built-in support for logging errors via its health monitoring system, but ELMAH is a simpler version of the health monitoring system that focuses specifically on error logging and is easier to configure. It also works in ASP.NET 1.x applications, whereas health monitoring is only available in ASP.NET 2.0 and up. What's more, ELMAH provides a built-in web page and RSS support for viewing error information.

So, what's new in ELMAH 1.0 BETA 3? The project page and issue tracker has the full set of details, but in a nutshell here are the enhancements that most interest me:

  • Three new error logging sources: Oracle, Microsoft Access, and VistaDB.
  • Log errors in AJAX applications.
  • Addition of an ErrorLogDataSourceAdapter, which you can use with the ObjectDataSource to declaratively work with the error log details from an ASP.NET page.

It is imperative that every web application in production log errors and report those errors to the development team. ELMAH offers this important functionality and can be setup, configured, and customized within a few minutes.

Filed under:
Running the Same Query Against Multiple Databases
17 November 08 08:00 AM | Scott Mitchell

One of my clients has a data-driven Software as a Service (SaaS) web application that is used by a dozen different customers. In particular, this client of mine has a web application that's used by various clinics and hospitals to track, schedule, and bill health care-related activities. As discussed in the Multi-Tenant Data Architecture guide from Microsoft, there are three approaches for modeling data that comes from many distinct customers:

  • Separate Databases - each customer has a separate database on the database server.
  • Shared Database, Separate Schema - a single databsae is used, but each customer has a unique set of database tables.
  • Shared Database, Shared Schema - a single set of database tables are used, with a CustomerId column or some other technique used to identify what rows belong to what customers.

Each approach has its pros and cons, and is good reading if you plan on building a SaaS web app.

For security and privacy reasons (important concerns for health care providers), we used the Separate Databases approach - each customer's data is hosted as a separate database on the server. I would not recommend this approach for the vast majority of multi-tenant applications. The most pronounced drawback of this model centers around the pain points associated with managing and administration of these databases. For example, when rolling out a new version or a bug fix, the database changes must be applied to all customer databases. If there is a business logic-type bug discovered, where there may be logically incorrect data in the database that needs to be addressed, you now have to look through all databases to see where the problem occurs and apply the fix across all databases.

One tool that assists with such debugging challenges is the undocumented Microsoft SQL Server stored procedure sp_MsForEachDb. In short, this stored procedure allows you to run a command on every database on the server. You can use it like so:

EXEC sp_MsForEachDb @command1='command to execute on all databases'

Within the @command1 input you can use a question mark (?) to denote that the name of the database the command is currently executing on be injected.

sp_MsForEachDb is most commonly used for administrative tasks such as updating statistics on all databases, but can also be used for debugging or bug hunting in a multi-tenant SaaS architecture that uses separate databases. Here's how: consider that you unearth a bug in the application code that allows for some illogical value in the database. Perhaps there's some business rule that dictates that a patient cannot have their insurance information entered if the patient's primary address is not provided, yet the application did not correctly enforce this rule and as a result there may be some patients with insurance information on file but without a primary address. Your task is to find out what patients in each database fit this description. You could run the following query on every single database:

SELECT p.PatientId, p.PatientName
FROM Patients p
INNER JOIN PatientInsuranceInformation pi ON p.PatientId = pi.PatientId
WHERE p.Address1 IS NULL AND pi.Active = 1

The issue is that this quickly becomes a pain if you have many different databases. Here's where sp_MsForEachDb comes in - you can use it to run the above on each database with the following statement:

EXEC sp_MsForEachDb @command1 = 'SELECT ''?'' as DatabaseName, p.PatientId, p.PatientName
FROM ?.dbo.Patients p
INNER JOIN ?.dbo.PatientInsuranceInformation pi ON p.PatientId = pi.PatientId
WHERE p.Address1 IS NULL AND pi.Active = 1'

This will output a three column resultset that displays those patients that have insurance information but no primary address along with what database the patient information resides in. The ''?'' part in the SELECT list displays the database name. The question mark character is also used in the FROM and INNER JOIN clauses to indicate the database from which to query against (i.e., you can refer to a table as database.owner.tableName).

The only downside of sp_MsForEachDb is that it runs on all databases, meaning it will run on databases like master, which don't have the application tables. As a result, you'll get SQL errors for those databases which clutter the output a bit. But for the databases specific to the application you will quickly see the results of your query and know, in an instant, what patients in what databases have this particular problem.

Filed under:
The Eight Commandments of Source Code Control
13 November 08 08:28 AM | Scott Mitchell

Feel free to print out a copy of these commandments and tape them to your coworker's monitor.

  1. You shall check in early and check in often. You anger your coworkers when you check out a file and insist on keeping it checked out until some future point in time that is measured using variables that exist solely in your brain.
  2. You shall never check in code that breaks the build. If you code does not compile, it does not belong in the source control repository.
  3. You shall not go home for the day with files checked out, nor shall you depart for the weekend or for a vacation, with files checked out.
  4. You shall leave a descriptive comment when checking in your code. You need not include your name or the date in the comment as that information is already tracked.
  5. You shall use the 'Undo Checkout' option if you check out a file and do not make any changes. It displeases your coworkers when you check in code that has not changed at all from the original.
  6. You shall not use comments to 'save' defunct code. Fear not, for the code you delete still exists in the source control code history and can be retrieved if needed.
  7. You shall use source control for more than archiving just code. The source code control repository makes an excellent storage for technical docs, SQL scripts, and other documents and files related to the project.
  8. You shall religiously backup your source code control database on a regular basis and store a copy in an off-site location.

Filed under: ,
Tracking User Activity
11 November 08 08:24 AM | Scott Mitchell

Many websites that support user accounts, such as the ASP.NET Forums, include information as to how what users are currently online and what they are doing. For example, the Who Is Online page on the ASP.NET Forums shows the users currently online, what forum they're viewing, and how many minutes it's been since they were last active. Unfotunately the Membership system does not offer much in the way of tracking user activity. All it has out-of-the-box is the ability to log the date and time the user was last 'active' and to report the total number of users online - that is, the count of users whose last active date and time falls within a certain time window (15 minutes, by default).

With a little bit of elbow grease it's possible to track user activity. I use the word activity here loosely. An activity might mean visiting a particular URL, much like how the ASP.NET Forums Who Is Online page shows what forum the user is visiting. It could also be more broad. For example, if a user just updated their account information the activity could be logged as, “Updating account information.” The activity information of interest can be logged in a database table and, once there, can be used in reports, be it a Who Is Online page or a report detailing the activity of a particular user.

To learn how to log user activity and display the results in the aforementioned reports, check out my latest article on Tracking User Activity.

Filed under:
More Posts


My Books

  • Teach Yourself ASP.NET 4 in 24 Hours
  • Teach Yourself ASP.NET 3.5 in 24 Hours
  • Teach Yourself ASP.NET 2.0 in 24 Hours
  • ASP.NET Data Web Controls Kick Start
  • ASP.NET: Tips, Tutorials, and Code
  • Designing Active Server Pages
  • Teach Yourself Active Server Pages 3.0 in 21 Days

I am a Microsoft MVP for ASP.NET.

I am an ASPInsider.