Friday, July 19, 2013

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();
}

2 comments:

Rick Cloud said...

I'm in need of your code here, but I don't see how you connect the try/catch with the static routine you provided? I'm trying to do a 'Pull' to perfrom a snapshot sync on my 4.0 CE file and I keep getting an SqlCeException that merely returns my SQL Server instance connection string. I know the connection string is correct, because when I create a setting string in VS2012, the connection test works.

ErikEJ said...

In the first catch block call ShowErrors with the caught exception to return a string with full info