Thursday, August 30, 2007

Programmatically Accessing Excel Data via .NET on 64-bit servers

So it turns out that using ADO.NET to access MS Excel worksheet data in an ASP.NET application running on the 64-bit version of Windows Server 2003 does not quite work as expected. This also goes for any 64-bit .NET code. In short, the Microsoft.Jet.OLEDB.4.0 provider is a 32-bit driver that does not work when directly used by 64-bit code. The following post from a Microsoft employee describes the issue:

Connect to Excel. Using x64 (64-bit) platform. Compiled as x64

"Since there are no 64-bit OleDb drivers for anything other than SQL Server, therefore, we cannot write 64-bit apps which interface with databases directly.

What you need to do is split your application into a 32 bit part and a 64 bit part, use COM interop to cross the 64/32 bit boundary. For instance, drop the code (just a simple class library compiled as 32 bit) that retrieves the Excel data into a COM+ (System.EnterpriseServices) as a "server type" application, and call those server methods from your 64 bit Windows service. This is exactly why System.EnterpriseServices are made for."

To restate the above in a list of to do items:
  • Refactor the Excel access code into a class by itself in a class library by itself
  • Make sure that this new class inherits from System.EnterpriseServices.ServicedComponent so that it can be hosted within COM+ (Component Services)
  • Make sure the new ServicedComponent's class library is compiled in 32-bit mode
  • Alter the calling code (ASP.NET web page, control, web part, etc.) to call the new ServicedComponent for the Excel data retrieval routines
  • Install the new ServicedComponent into Component Services

Using Log4Net in 4 Simple Steps

First off, yes, this is a complete rip-off from this blog post, but with value added in that this represents my personal preference when using log4net. This is a simple four step process of adding logging to an application. I prefer log4net over the MS logging application block, primarily as log4net has zero dependencies and NHibernate already references it. IMHO, if you're doing ANY kind of logging, reference log4net into your project and use it instead of Trace.WriteLine() or Debug.WriteLine().

Step 1. Add the following in the AssemblyInfo.cs

[assembly: log4net.Config.XmlConfiguratorAttribute(
ConfigFile = "log4net.xml", Watch = true)]
Step 2. Create the log4net.xml file and add the following to the new file

With respect to the location of this file and SharePoint, this file can exist in the root of the wss root, next to the relevant web.config file for the given site. In general, this should sit next to the app.config or web.config file for the given application.

<log4net>
<appender name="GeneralLog" type="log4net.Appender.RollingFileAppender">
<file value="${TEMP}\\Logs\\AppName_${COMPUTERNAME} " />
<appendToFile value="true" />
<rollingStyle value="Composite" />
<staticLogFileName value="false" />
<datePattern value=".yyyyMMdd.'log'" />
<maxSizeRollBackups value="10" />
<maximumFileSize value="5MB" />
<lockingModel type="log4net.Appender.FileAppender+MinimalLock" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%d{HH:mm:ss.fff} [%t] %-5p %c - %m%n" />
</layout>
</appender>
<appender name="DebugAppender" type="log4net.Appender.DebugAppender">
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%d [%t] %-5level %logger - %message%newline" />
</layout>
</appender>
<root>
<level value="ALL" />
<appender-ref ref="GeneralLog" />
<appender-ref ref="DebugAppender" />
</root>
<!-- Print only messages of level ERROR or above in the package NHibernate -->
<logger name="NHibernate" additivity="true">
<level value="ERROR" />
</logger>
</log4net>

Step 3. Add the following at the top of your class
private static readonly log4net.ILog log = log4net.LogManager.GetLogger(
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

Step 4. Add the following line in the code where you want logging to be done

log.Info("Program Started " + DateTime.Now.ToString ());
References

Apache log4net Home Page - http://logging.apache.org/log4net/