how to log exceptions into sql server using enterprise library

Topics: Exception Handling Application Block, Logging Application Block
Feb 19, 2009 at 12:27 PM

how to log exceptions into sql server using enterprise library

please guide me with example

Feb 19, 2009 at 12:48 PM

Hi,

Try following this steps.

Assuming that you already have the config file...

1. Add a Exception Handling Application Block.
2. Add a Exception Policy, name it.
3. In your exception policy, add the desired Exception Type.
4. In the added exception type, add a Logging Handler.
           After the the Logging Handler has been added, the Logging Block is also added.
5. In the Logging Block, In the TraceListeners node, Add a new DatabaseTraceListener.
6. Configure the Data Access Application Block connection string that will be used by the database trace listener.
7. In the category node of your Logging Block, you can add a new category and then add a new Trace Listener Reference referencing your Database Trace Listener or you can just use the default category.
8. In your Logging Handler properties, specify the FormattedType as TextExceptionFormatter.
9. In your Logging Handler properties, set the LogCategory to the category where your database trace listener resides.


After you configured the Exception handling with logging, you are now good to go.

here is a sample code snippet.

try
{
    //do something
}
catch(Exception ex)
{
    bool rethrow = ExceptionPolicy.HandleException(ex, "MyExceptionPolicy")
    if(rethrow)
        throw;
}


Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
Feb 20, 2009 at 2:55 AM
can you guide me with some real example

i would really appreciate ,if you make it with some code program as example
Feb 20, 2009 at 3:09 AM
You can mail us so we can send you a sample solution.


Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
Feb 20, 2009 at 3:47 AM
You forgot to mention the setup of the Logging database.

Before you can log to the database, you'll need to setup the Logging database.  You can run CreateLoggingDb.cmd at the command prompt.  This executes a databse script file called "LoggingDatabase.sql".

These files are created when you install the source files.  The default location is:

C:\EntLib4Src\Blocks\Logging\Src\DatabaseTraceListener\Scripts

HTH,
An
Feb 20, 2009 at 5:23 AM
Yes, forgot to include that, thanks An.  The key is the setup of your configuration file.  The code posted by Valiant is sufficient enough to handle the logging to the database once you've properly configure your logging and exceptionhandling block.  If you still find difficulty configuring your app.config, let us know.



Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
Feb 20, 2009 at 7:34 AM
hi

To be honest i dont know enterprise library completely.I used only data access.I am new to exception handling and logging.all i need is when ever the exception occurs i want to log it in database.Friend of mine told me  following are the fields.
  @EventID int,
 @Priority int,
 @Severity nvarchar(32),
 @Title nvarchar(256),
 @Timestamp datetime,
 @MachineName nvarchar(32),
 @AppDomainName nvarchar(512),
 @ProcessID nvarchar(256),
 @ProcessName nvarchar(512),
 @ThreadName nvarchar(512),
 @Win32ThreadId nvarchar(128),
 @Message nvarchar(1500),
 @FormattedMessage ntext

so please guide me .i got struck with this....
Please
Feb 20, 2009 at 8:01 AM
Ok, have you run the script already?  After running it, look at the Logging database that was created. Those information will get logged to the Log table in the Message and FormattedMessage columns. 

Next you have to do is add the exceptionhandling and logging configuration sections in your config file.  Do you know how to use entlib's configuration editor tool?  I'm assuming you created your DAAB config section using it.  If not, let me know.


Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Feb 20, 2009 at 8:57 AM
Iam not sure about this

"Next you have to do is add the exceptionhandling and logging configuration sections in your config file.  Do you know how to use entlib's configuration editor tool?  I'm assuming you created your DAAB config section using it.  If not, let me know."

all i have is
<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
    <section name="cachingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Caching.Configuration.CacheManagerSettings, Microsoft.Practices.EnterpriseLibrary.Caching, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
  </configSections>
  <dataConfiguration defaultDatabase="Connection String" />
  <connectionStrings>
    <add name="Connection String" connectionString="Data Source=TestServer;Initial Catalog=TestDB;Persist Security Info=True;User ID=sa;Password=password"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
  <cachingConfiguration defaultCacheManager="Cache Manager">
    <cacheManagers>
      <add expirationPollFrequencyInSeconds="60" maximumElementsInCacheBeforeScavenging="1000"
        numberToRemoveWhenScavenging="10" backingStoreName="Null Storage"
        name="Cache Manager" />
    </cacheManagers>
    <backingStores>
      <add  type="Microsoft.Practices.EnterpriseLibrary.Caching.BackingStoreImplementations.NullBackingStore, Microsoft.Practices.EnterpriseLibrary.Caching, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
        name="Null Storage" />
    </backingStores>
  </cachingConfiguration>
</configuration>

Thank you so much for the follow up
Feb 20, 2009 at 9:39 AM

Hi,

I've followed the steps mentioned by Valiant. And as a result, my app.config file looks like as follows


<configuration>
 <configSections>
  <section name="loggingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.LoggingSettings, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
  <section name="exceptionHandling" type="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Configuration.ExceptionHandlingSettings, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
  <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
 </configSections>
 <loggingConfiguration name="Logging Application Block" tracingEnabled="true"
   defaultCategory="General" logWarningsWhenNoCategoriesMatch="true">
  <listeners>
   <add databaseInstanceName="Connection String" writeLogStoredProcName="WriteLog"
     addCategoryStoredProcName="AddCategory" formatter="" listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Database.Configuration.FormattedDatabaseTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
     traceOutputOptions="None" type="Microsoft.Practices.EnterpriseLibrary.Logging.Database.FormattedDatabaseTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
     name="Database Trace Listener" />
   <add source="Enterprise Library Logging" formatter="Text Formatter"
     log="Application" machineName="" listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.FormattedEventLogTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
     traceOutputOptions="None" type="Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners.FormattedEventLogTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
     name="Formatted EventLog TraceListener" />
  </listeners>
  <formatters>
   <add template="Timestamp: {timestamp}&#xD;&#xA;Message: {message}&#xD;&#xA;Category: {category}&#xD;&#xA;Priority: {priority}&#xD;&#xA;EventId: {eventid}&#xD;&#xA;Severity: {severity}&#xD;&#xA;Title:{title}&#xD;&#xA;Machine: {machine}&#xD;&#xA;Application Domain: {appDomain}&#xD;&#xA;Process Id: {processId}&#xD;&#xA;Process Name: {processName}&#xD;&#xA;Win32 Thread Id: {win32ThreadId}&#xD;&#xA;Thread Name: {threadName}&#xD;&#xA;Extended Properties: {dictionary({key} - {value}&#xD;&#xA;)}"
     type="Microsoft.Practices.EnterpriseLibrary.Logging.Formatters.TextFormatter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
     name="Text Formatter" />
  </formatters>
  <categorySources>
   <add switchValue="All" name="General">
    <listeners>
     <add name="Formatted EventLog TraceListener" />
    </listeners>
   </add>
  </categorySources>
  <specialSources>
   <allEvents switchValue="All" name="All Events" />
   <notProcessed switchValue="All" name="Unprocessed Category" />
   <errors switchValue="All" name="Logging Errors &amp; Warnings">
    <listeners>
     <add name="Formatted EventLog TraceListener" />
    </listeners>
   </errors>
  </specialSources>
 </loggingConfiguration>
 <exceptionHandling>
  <exceptionPolicies>
   <add name="Exception Policy">
    <exceptionTypes>
     <add type="System.Exception, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
       postHandlingAction="ThrowNewException" name="Exception">
      <exceptionHandlers>
       <add logCategory="General" eventId="100" severity="Error" title="Enterprise Library Exception Handling"
         formatterType="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.TextExceptionFormatter, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
         priority="0" type="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging.LoggingExceptionHandler, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
         name="Logging Handler" />
      </exceptionHandlers>
     </add>
    </exceptionTypes>
   </add>
  </exceptionPolicies>
 </exceptionHandling>
 <dataConfiguration defaultDatabase="Connection String" />
 <connectionStrings>
  <add name="Connection String" connectionString="Database=Logging;Server=saurabh\SQLEXPRESS;Integrated Security=SSPI"
    providerName="System.Data.SqlClient" />
 </connectionStrings>
</configuration>

However, any exception is not getting logged in database. I have also created Logging Database.


Below is the sample script I'm using :

 private void btnLogException_Click(object sender, EventArgs e)
        {
            try
            {
                throw new Exception ("Exception in button click event...");
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, "Exception Policy");
                if (rethrow)
                {
                    throw;
                }
            }
        }

 

Feb 20, 2009 at 10:31 AM
I tried your config, it seems that your missing the step #7, Right click on the Category node. In your case, there is the "General" node under the Categories Sources, New Trace Listener Reference, then set that reference to the Database Trace Listener.


Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
Feb 20, 2009 at 10:49 AM
Hi,

I have now added a new category and new Database Trace Listener and change ReferencedTraceListener property to Database Trace Listener as per Step #7. Now Categorysources section of my config file looks like as below :

<categorySources>
   <add switchValue="All" name="DB Trace Category">
    <listeners>
     <add name="Database Trace Listener" />
    </listeners>
   </add>
   <add switchValue="All" name="General">
    <listeners>
     <add name="Formatted EventLog TraceListener" />
    </listeners>
   </add>
  </categorySources>

However, when I'm trying to run the application, application prompt with the following error at this line 
bool rethrow = ExceptionPolicy.HandleException(ex, "Exception Policy");

Exception : "The value can not be null or string or empty. Parameter name: name"

Thanks,
Saurabh
Feb 20, 2009 at 11:32 AM
Make sure you've referenced the following assemblies:

Microsoft.Practices.EnterpriseLibrary.Logging.dll
Microsoft.Practices.EnterpriseLibrary.Logging.Database.dll
Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.dll
Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging.dll

Also, what exception are you throwing? The exception you're getting is probably the exception that was thrown.  Check the database to see if there was a log entry.


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Feb 21, 2009 at 5:24 AM
Hi,

I've already taken the reference to all mentioned assemblies.  I've check the database and it contains no entry for any exceptions.


Thanks
Saurabh
Feb 21, 2009 at 6:23 AM
What is the original exception being thrown?  Could you set the PostHandlingAction of your exception policy to "None" instead of "ThrowNewException" and see what happens.


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Feb 22, 2009 at 6:53 AM

Sarah is right.  The exception you are getting is the exception that is thrown by your app.

Your logging handler is assigning your exception messages to the "General" category.  

<exceptionHandling>
  <exceptionPolicies>
   ...
      <exceptionHandlers>
       <add logCategory="General" eventId="100" severity="Error" title="Enterprise Library Exception Handling"


Your Logging Block configuration is using the Eventlog Trace listener for messages with the "General" category.

  <categorySources>
   <add switchValue="All" name="General">
    <listeners>
     <add name="Formatted EventLog TraceListener" />
    </listeners>
   </add>
  </categorySources>


This is why you are not seeing a database record. 

You'll need to add the database trace listener to the "General" category like so,
  <categorySources>
   <add switchValue="All" name="General">
    <listeners>
     <add name="Formatted EventLog TraceListener" />
     <add name="Database Trace Listener" />
    </listeners>
   </add>
  </categorySources>



Also be sure the process account have access to the Logging database.

HTH,
An


Feb 24, 2009 at 1:52 PM
Hi,

Right, my application flow went to catch block because of exception that I had thrown in try block.  "
            try
            {
                throw new Exception ("This is a sample exception");
            }


While debugging application, when I'm looking into catch block, it shows me ex.message as "This is a sample exception". However when the flow comes to below line 
bool rethrow = ExceptionPolicy.HandleException(ex, "Exception Policy");

it throw another error saying "The type initializer for 'Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.ExceptionPolicy' threw an exception." And to add that even after adding Database Listenter, no logging to database is happening.

Thanks
Saurabh

Feb 24, 2009 at 1:59 PM
We should resolve the error you're getting first.  Could you add more details to it like the inner exception.  Also add the Microsoft.Practices.EnterpriseLibrary.Common assembly, sorry, I forgot to mention this.


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Feb 24, 2009 at 3:47 PM
Hi,

I've already taken the reference of Common assembly. Inner Exception have null value. Do you want me to paste new app.config file ?

Regards
Saurabh
Feb 25, 2009 at 1:23 AM
Edited Feb 25, 2009 at 3:57 AM
saurabh1982,
Yes, but maybe the complete error message will do.  

On second thought, sending your sample solution would save us a lot of effort.  Regarding the TypeInitializerException, do you have other version of entlib installed? Make sure you're referencing the correct assemblies.

surendiran,
Were you able to get your app working now?



Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Feb 25, 2009 at 4:57 AM
Edited Feb 25, 2009 at 7:21 AM
Hi Sarah,

I will send you sample soultion shortly at email id entlib.support@avanade.com .

Just to inform that, I'm using enterprise library version 3.1.

Regards
Saurabh
Feb 25, 2009 at 6:06 AM
You don't have other versions of entlib installed? I'll wait for your sample app.


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com