Background: ELMAH (Error Logging Modules and Handlers) is a great tool for any web developer that doesn’t want to go through the pain of adding in error logging to their web applications. A great tutorial on it was written up by Simone Busoli.
One of the features that ships with ELMAH is a database script for setting up the SQL Server table and stored procedures. And, unfortunately you can’t alter the names of the stored procedures out of the box, because the names are hardcoded in the ELMAH dll. This causes a bit of a problem with our environment and SQL Server 2008, because the script is designed for SQL Server 2000. All of the stored procedures are created under the owner [dbo].
While you can setup the schema of ‘dbo’ in SQL Server 2008, it’s not the most flexible way to go. Also, using the dbo schema would enforce the rule that you must have only one ELMAH error table in the database. Which would require making a separate database for each website.
This constraint didn’t work for our environment, so I went through the source code and found that it was a pretty straight forward process to add in Schema & Stored Procedure Prefixes while keeping the dll backwards compatible. Since ELMAH uses Inversion of Control to make it more reusable, you only need to update one file, SqlErrorLog.cs.
First you add in new properties for the Schema and SprocPrefix. And, then initialize them in the constructor:
/// <summary>
/// The schema name to use in place of 'dbo' on 2005/2008 Sql Server instances.
/// </summary>
internal virtual string Schema { get; set; }
/// <summary>
/// The stored procedure prefix. When used this will allow multiple applications
/// to use the same database (but, different sprocs/tables) to store exception
/// logs.
/// </summary>
internal virtual string SprocPrefix { get; set; }
public SqlErrorLog(IDictionary config)
{
// ... left out for clarity
// grab the schema if provided, else use 'dbo' as default
Schema = config["schema"] == null ? "dbo" : config["schema"].ToString().Trim();
// grab the stored procedure prefix if one is provided. the default value is an empty string.
SprocPrefix = config["sprocPrefix"] == null ? string.Empty : config["sprocPrefix"].ToString().Trim();
}
After that, all that’s left is updating the private Commands classes method signatures to require the schema and sprocPrefix variables. Like so:
public static SqlCommand LogError(
Guid id,
string appName,
string hostName,
string typeName,
string source,
string message,
string user,
int statusCode,
DateTime time,
string xml,
string schema,
string sprocPrefix)
{
var commandText = string.Format( "[{0}].[{1}ELMAH_LogError]", schema, sprocPrefix );
var command = new SqlCommand(commandText);
// ... left out for clarity
}
And, finally update the code which calls those methods.
Then you can use the ‘schema’ and ‘sprocPrefix’ attributes in the configuration file.
<errorLog type="Elmah.SqlErrorLog, Elmah" connectionStringName="XYZ" schema="Ent" sprocPrefix="WebApp1_" />
Thanks Atif Aziz for making it so easy.
