Reader Level:

Auditing and Logging Exceptions

By Sonu Chauhan on Feb 01, 2006
This article shows how to handle a SQL Server error condition by using the SQL Server .NET Framework data provider.

The default ASP.NET process identity for Web applications can write new records to the event log, but it does not have sufficient permissions to create new event sources.

To address this issue, you have two choices. You can create an installer class, which is invoked at installation time when administrator privileges are available, or you can configure the permissions on the EventLog registry key to allow the ASP.NET process identity (or impersonated identity) to create event sources at run time. The former approach is recommended.

Example: Logging Exceptions

The following code fragment shows how to handle a SQL Server error condition by using the SQL Server .NET Framework data provider:

using System.Data;
// Method exposed by a Data Access Layer (DAL) Component
string GetProductName( int ProductID )
      SqlConnection conn = new SqlConnection(
      "server=(local);Integrated Security=SSPI;database=products")
      // Enclose all data access code within a try block
                  SqlCommand cmd = new SqlCommand("LookupProductName", conn );
                  cmd.CommandType = CommandType.StoredProcedure;
                  cmd.Parameters.Add("@ProductID", ProductID );
                  SqlParameter paramPN =
                  cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 40 );
                  paramPN.Direction = ParameterDirection.Output;
                  // The finally code is executed before the method returns
                  return paramPN.Value.ToString();
            catch (SqlException sqlex)
                  // Handle data access exception condition
                  // Log specific exception details
                  // Wrap the current exception in a more relevant
                  // outer exception and re-throw the new exception
                  throw new Exception("Failed to retrieve product details for product ID: "
                  +ProductID.ToString(), sqlex );
            conn.Close(); // Ensures connection is closed

// Helper routine that logs SqlException details to the

// Application event log

private void LogException( SqlException sqlex )


          EventLog el = new EventLog();

          el.Source = "CustomAppLog";

          string strMessage;

          strMessage = "Exception Number : " + sqlex.Number +

                    "(" + sqlex.Message + ") has occurred";

          el.WriteEntry( strMessage );

          foreach (SqlError sqle in sqlex.Errors)


                    strMessage = "Message: " + sqle.Message +

                              " Number: " + sqle.Number +

                                        " Procedure: " + sqle.Procedure +

                              " Server: " + sqle.Server +

                                        " Source: " + sqle.Source +

                                        " State: " + sqle.State +

                                        " Severity: " + sqle.Class +

                                        " LineNumber: " + sqle.LineNumber;

                    el.WriteEntry( strMessage );



Sonu Chauhan
Sonu Chauhan

Sonu holds B. Sc. (Mathmatics, Physics & Statistics) and MCA (Master's of Computer Applications) degrees. Currently he is working with Merrill Lynch. India and has extensive experience in web technologies using .NET and ... Read more