Reverse-formats SQL Server error message: SQL Server formats a template error message inserting table/column/index/etc. names. This code extracts those inserted names. This allows you to detect what table/column/index/etc. is causing trouble. Use this e.g. to detect attempts to insert non-unique values governed by a unique index without first ha…

SqlExceptionHandler.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;

namespace Company.Product
{
    using ModelTableAdapters;

    /// <summary>
    /// Extract useful information from SQL messages
    /// and converts it to a machine-readable form.
    /// </summary>
    public sealed class SqlExceptionHandler
    {
        public SqlExceptionHandler()
        {
            _replacementPattern.Add(547, "$3");
            _replacementPattern.Add(2601, "$2");
        }

        public SqlExceptionHandler(QueriesFactory queriesFactory) : this()
        {
            _queriesFactory = queriesFactory;
        }

        public string Parse(SqlException exception)
        {
            using (IQueries queries = _queriesFactory())
            {
                string messageTemplate = (string)queries.GetMessageTemplate(exception.Number);

                Regex specialsRegex = new Regex(SPECIALS_PATTERN);
                string escapedTemplate = specialsRegex.Replace(messageTemplate, @"\$0");

                Regex templateRegex = new Regex(PARAMETERS_PATTERN);
                string messageEx = templateRegex.Replace(escapedTemplate, "(.*)");

                Regex messageRegex = new Regex(messageEx);
                return messageRegex.Replace(exception.Message, _replacementPattern[exception.Number]);
            }
        }

        private const string SPECIALS_PATTERN = @"\.|\$|\^|\{|\[|\(|\||\)|\*|\+|\?|\\";
        private const string PARAMETERS_PATTERN = @"%[-+0# ]?\d*(\\\.)?(\d*|\\\*)?[hl]?[diosuxX]";
        private readonly static Dictionary<int, string> _replacementPattern = new Dictionary<int, string>();

        private QueriesFactory _queriesFactory = delegate { return new QueriesTableAdapter(); };
    }

    /// <summary>
    /// SQL message numbers.
    /// </summary>
    public enum SqlMessage
    {
        ConstraintConflict = 547,
        DuplicateKeyRow = 2601,
    }
}