nLog and TransactionScope

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

nLog and TransactionScope

mrboring
Hi All

I have an ASP.NET MVC 3 (using Entity Framework 4.2) application that uses transactions as follows:

using (var transaction = new TransactionScope())
{
        // Database action 1

        // Database action 2

        context.SaveChanges();

        Logger.Info("Record X updated");

        transaction.Complete();
}

I get no errors, but no data is written to the database. However, the log file target (there just for testing purposes) works fine. Here is my nLog (I'm using v2) config:

<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                throwExceptions="true"
                internalLogToConsole="true"
                internalLogToConsoleError="true" >

  <targets>
          <target name="logfile" xsi:type="File" fileName="${basedir}app_data\file.txt"  />
         
         <target xsi:type="Database" name="database">
                <commandText>INSERT INTO [LogEntries] (TimeStamp, Message, Level, Logger) VALUES(GETDATE(), @msg, @level, @logger)</commandText>
                <parameter name="@msg" layout="${message}" />
                <parameter name="@level" layout="${level}" />
                <parameter name="@logger" layout="${logger}" />
                <dbProvider>System.Data.SqlServerCe.4.0</dbProvider>
                <connectionString>Data Source=${basedir}app_data\Logger.sdf</connectionString>
         </target>
  </targets>

  <rules>
          <logger name="*" minlevel="Trace" writeTo="logfile" />
          <logger name="*" minlevel="Trace" writeTo="database" />
  </rules>
</nlog>

If I move the Logger line outside of the transactionscope it works fine. So I think it's something to do with this. I'm using SQL Server 2008 r2 for my main database and SQL Compact 4 for my logging database. Also, I've tried adding useTranscations="true".

Any ideas what I'm doing wrong?

Thanks

Alan
Reply | Threaded
Open this post in threaded view
|

Re: nLog and TransactionScope

SoftInvent
Hi Alan

Was just reading up on the features of SQL Server Compact 4 and noticed that it doesn't support distributed transactions which is basically what you are doing when you write to two different databases in the same transaction.
A solution could be to move your activity log table to your main database, if possible or else I think you'll have to rely on non transacted logging.

Cheers
Henrik
Reply | Threaded
Open this post in threaded view
|

Re: nLog and TransactionScope

SoftInvent
In reply to this post by mrboring
BTW, ErikEj has made an excellenct comparison chart of the different "small" SQL Server editions. Find it at: http://erikej.blogspot.com/2011/01/comparison-of-sql-server-compact-4-and.html
Reply | Threaded
Open this post in threaded view
|

Re: nLog and TransactionScope

Alan
In reply to this post by SoftInvent
Hi Henrik

Thanks for you response. I've actually done as you suggested and moved the logging into the main database and it's now working fine.

Alan
Reply | Threaded
Open this post in threaded view
|

Re: nLog and TransactionScope

Alan
In reply to this post by SoftInvent
Hi Henrik

That's for the link. I found it very informative.

Alan