Review

In Part 1 I created a basic implementation for a new ELMAH logging provider that will provide indirect logging to SQL Server over a web service interface. Currently, the basic skeleton code is called, but it doesn’t do anything useful at the moment. Eventually, I need to fill in those methods with useful code, but before I do that, I’m going to take a look at the Web Service portion of the project.

It really helps to have the source code for this exercise, and you can download it from the here, if you don’t already have it.

Wrapping Data Access

I started by creating a new ASP.Net Web Service project in a new solution. I want to wrap the data access functions and serialize the results as simply as possible for the moment.

The following data access code is largely lifted from the original SqlErrorLog implementation.

namespace MyCompany.ElmahExt.Service
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
 
    internal static class SqlCommands
    {
        public static SqlCommand LogError(
            Guid id, string appName, string hostName, string typeName, string source,
            string message, string user, int statusCode, DateTime time, string xml)
        {
            SqlCommand cmd = new SqlCommand("ELMAH_LogError");
            cmd.CommandType = CommandType.StoredProcedure;
 
            SqlParameterCollection parms = cmd.Parameters;
 
            parms.Add("@ErrorId", SqlDbType.UniqueIdentifier).Value = id;
            parms.Add("@Application", SqlDbType.NVarChar, 60).Value = appName;
            parms.Add("@Host", SqlDbType.NVarChar, 30).Value = hostName;
            parms.Add("@Type", SqlDbType.NVarChar, 100).Value = typeName;
            parms.Add("@Source", SqlDbType.NVarChar, 60).Value = source;
            parms.Add("@Message", SqlDbType.NVarChar, 500).Value = message;
            parms.Add("@User", SqlDbType.NVarChar, 50).Value = user;
            parms.Add("@AllXml", SqlDbType.NText).Value = xml;
            parms.Add("@StatusCode", SqlDbType.Int).Value = statusCode;
            parms.Add("@TimeUtc", SqlDbType.DateTime).Value = time;
 
            return cmd;
        }
 
        public static SqlCommand GetErrorXml(string appName, Guid id)
        {
            SqlCommand cmd = new SqlCommand("ELMAH_GetErrorXml");
            cmd.CommandType = CommandType.StoredProcedure;
 
            SqlParameterCollection parms = cmd.Parameters;
 
            parms.Add("@Application", SqlDbType.NVarChar, 60).Value = appName;
            parms.Add("@ErrorId", SqlDbType.UniqueIdentifier).Value = id;
 
            return cmd;
        }
 
        public static SqlCommand GetErrorsXml(string appName, int pageIndex, int pageSize)
        {
            SqlCommand cmd = new SqlCommand("ELMAH_GetErrorsXml");
            cmd.CommandType = CommandType.StoredProcedure;
 
            SqlParameterCollection parms = cmd.Parameters;
 
            parms.Add("@Application", SqlDbType.NVarChar, 60).Value = appName;
            parms.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            parms.Add("@PageSize", SqlDbType.Int).Value = pageSize;
            parms.Add("@TotalCount", SqlDbType.Int).Direction = ParameterDirection.Output;
 
            return cmd;
        }
    }
}

The web service itself will expose the three basic methods for interacting with the database:

LogError

For recording a new error.

[WebMethod]
public string LogError(
    string appName, string hostName, string typeName, string source,
    string message, string user, int statusCode, DateTime time, string xml)
{
    Guid id = Guid.NewGuid();
 
    using (SqlConnection cnn = new SqlConnection(Logging.ConnectionString))
    using (SqlCommand cmd = SqlCommands.LogError(id, appName, hostName, typeName, source, message, user, statusCode, time, xml))
    {
        cmd.Connection = cnn;
        cnn.Open();
        cmd.ExecuteNonQuery();
  
        XDocument doc = new XDocument(
            new XElement("LogErrorResult",
                new XAttribute("id", id.ToString())
                )
            );
 
        return doc.ToString();
    }
}

GetErrors

For getting an error list.

[WebMethod]
public string GetErrors(string appName, int pageIndex, int pageSize)
{
    using (SqlConnection cnn = new SqlConnection(Logging.ConnectionString))
    using (SqlCommand cmd = SqlCommands.GetErrorsXml(appName, pageIndex, pageSize))
    {
        cmd.Connection = cnn;
        cnn.Open();
 
        XmlReader reader = cmd.ExecuteXmlReader();
 
        try
        {
            int total = (int)cmd.Parameters["@TotalCount"].Value;
 
            List<string> errors = new List<string>();
 
            while (reader.IsStartElement("error"))
                errors.Add(reader.ReadOuterXml());
 
            XDocument doc = new XDocument(
                new XElement("GetErrorsResult",
                    new XAttribute("total", total),
                    from string error in errors
                    select new XElement("Error", error)
                    )
                );
 
            return doc.ToString();
        }
        finally
        {
            reader.Close();
        }
    }
}

GetError

For getting a specific error.

[WebMethod]
public string GetError(string appName, string id)
{
    Guid errorGuid = new Guid(id);
 
    string errorXml;
 
    using (SqlConnection cnn = new SqlConnection(Logging.ConnectionString))
    using (SqlCommand cmd = SqlCommands.GetErrorXml(appName, errorGuid))
    {
        cmd.Connection = cnn;
        cnn.Open();
        errorXml = (string)cmd.ExecuteScalar();
    }
 
    XElement errorEl = (errorXml == null) ? null : new XElement("Error", errorXml);
 
    XDocument doc = new XDocument(
        new XElement("GetErrorResult", errorEl)
        );
 
    return doc.ToString();
}

To serialize the result of each method, I’m creating a simple Xml document and passing the whole document as a single string.

A quick check that these web methods can be run independently of ELMAH, and that they all work, and I’m done for now.

In Part 3, I’ll hook it all together.