SQL Server Compact Code Snippet of the Week #18 : handling SqlCeExceptions

Often I see developers complain that the error messages from SQL Server Compact does not provide any information about the error (in SqlCeEcxeption.Message). This is due to to way that the SqlCeException object is shaped, as it contains a SqlCeErrors collection and other properties you must inspect in order to get “full disclosure” on the error information. Getting this complete information of course helps a lot in troubleshooting the root causes for the errors. (The exact same pattern applies to SqlException, which also has a collection of SqlCeErrors)

Remember in your error handling to catch any specialized exceptions (like SqlCeException) before the more general Exception object:

            try
            {
                //Data access code here
            }
            catch (SqlCeException scx)
            { }
            catch (Exception ex)
            { }

For getting full error information, I use the code below:

        public static string ShowErrors(System.Data.SqlServerCe.SqlCeException e)
        {
            System.Data.SqlServerCe.SqlCeErrorCollection errorCollection = e.Errors;

            StringBuilder bld = new StringBuilder();
            Exception inner = e.InnerException;

            if (!string.IsNullOrEmpty(e.HelpLink))
            {
                bld.Append("\nCommand text: ");
                bld.Append(e.HelpLink);
            }

            if (null != inner)
            {
                bld.Append("\nInner Exception: " + inner.ToString());
            }
            // Enumerate the errors to a message box.
            foreach (System.Data.SqlServerCe.SqlCeError err in errorCollection)
            {
                bld.Append("\n Error Code: " + err.HResult.ToString("X", System.Globalization.CultureInfo.InvariantCulture));
                bld.Append("\n Message   : " + err.Message);
                bld.Append("\n Minor Err.: " + err.NativeError);
                bld.Append("\n Source    : " + err.Source);

                // Enumerate each numeric parameter for the error.
                foreach (int numPar in err.NumericErrorParameters)
                {
                    if (0 != numPar) bld.Append("\n Num. Par. : " + numPar);
                }

                // Enumerate each string parameter for the error.
                foreach (string errPar in err.ErrorParameters)
                {
                    if (!string.IsNullOrEmpty(errPar)) bld.Append("\n Err. Par. : " + errPar);
                }
            }
            return bld.ToString();
        }