Oracle@DotNet

ODP.NET 11g beta is here!

2007-07-04T10:13:00.000+03:00

Well, ODP.NET 11g is here, and with it a long list of features to support the .Net to Oracle experience. But it's not just ODP.NET anymore, the ODAC (Oracle Data Access Components) package now contains a set of products for the .Net developer, all in single installation.

ODAC consists of 3 components:

Oracle Data Provider for .NET (AKA ODP.NET)

This is Oracle's ADO.NET implementation (Oracle.DataAccess.dll) that supports the latest database features. Some of the new features are:

Instant Client Support - XCopy installation with small client footprint
User-Defined Types - Map Oracle objects and collections to .NET custom types and support REFs to object types
Connection Pool Performance Counters - Option to monitor the connection pool using the Windows Performance Monitor.

Oracle Developer Tools for Visual Studio .NET (AKA ODT.NET)

ODT.NET is now fully integrated with the server explorer, that means it is now consists of a query builder and supports paging, but there is more than that:

Oracle Database script project - ODT.NET now supports source controlling for the Oracle schema objects. That means that now we can keep versioning on Packages, Stored Procedures, Tables etc.
Explain plan support - now you don't have to use a command line to view the execution plan. You can see it built in to the Visual Studio.

Oracle Providers for ASP.NET

Ever looked for OracleMembershipProvider, OracleRoleProvider, OracleProfileProvider?

ASP.NET contains new components that can help us implement common use cases, like Login to a system, Roles management, Sitemap, Personalization etc. All of these components now can be now persisted to Oracle using Oracle Providers for ASP.NET.

The full list of features can be found here: http://www.oracle.com/technology/tech/windows/odpnet/newbetafeatures.html

you can download it at the Oracle Technology Network site, for free of course...

NHibernator in SourceForge

2007-05-02T10:46:00.000+03:00

Hya all NHibernator users,

(What is NHibernator anyway?)

NHibernator is now available in SourceForge
http://sourceforge.net/projects/nhibernator

You can get anonymous access to the cvs, and see the code
http://nhibernator.cvs.sourceforge.net/nhibernator/

NHibernator - The simple but yet powerful NHibernate helper

2007-04-15T13:57:00.000+03:00

Overview
Working with NHibernate requires the use of a framework to configure NHibernate and to manage the session. The NHibernate wiki contains documentation about the recommended way to do it (see http://www.hibernate.org/363.html) especially in an ASP.NET environment.

Frameworks like Spring.Net and Castle provide a full-featured solution for integrating NHibernate in an application, but for some applications it's considered as overkill.
That's why most applications use some kind of homemade NHibernate Helper, based on code examples from the Internet. Some of them can be found at the bottom of http://www.hibernate.org/363.html.

So I was searching for a simple NHibernate Helper that is simple to integrate, but still provides the feature I need, like working with multiple databases, support for ASP.NET and Winform environment, and more...

NHibernator
I created the NHibernator as a solution for a simple NHibernate Helper, I will upload it to SourceForge as an open source so people can contribute. Meanwhile you can download it here.

Main features:
1. Easy configuration at the bootstrap, with a specific configuration file.
2. Support for multiple databases.
3. OpenSessionInViewModule for ASP.NET applications.
4. Thread bounded session management for Winform application.
5. NHibernatorTransaction for easy transaction management, with automatic Rollback and easy snippet for easy coding.

NHibernator supports 3 session management mechanisms.
1. OpenSessionInViewModule: Just define a module in the web.config and the session will be opened when needed and released at the end of each request.
2. Session per Transaction: In a non-web applications session will be opened at the beginning the transaction and closed at the end of it.
3. Manual session management: Session will be opened and closed manually with NHibernator.OpenSession() and NHibernator.CloseSession() respectively.

How to use?
1. Basic Configuration: Just place "hibernate.cfg.xml" file in the executable directory, as an alternative you can use the "NHibernatorConfigFileLocation" key in the appSettings section of the web.config. For example:

<appSettings>
  <add key="NHibernatorConfigFileLocation" value="~Config\hibernate.cfg.xml"/>
</appSettings>

This can either be full path or relative path using ~. For multiple databases use semicolon, for example:

<appSettings>
  <add key="NHibernatorConfigFileLocation" value="~Config\hibernate1.cfg.xml;~Config\hibernate2.cfg.xml"/>
</appSettings>

2. ASP.NET: For a web application you will probably want to use the OpenSessionInViewModule for handling the session at the http-request level.
This can be done by adding this to your web.config file.

<system.web>
  <httpModules>
    <add name ="NHibernator" type="NHibernatorFramework.OpenSessionInViewModule, NHibernator"/>
  </httpModules>
</system.web>

3. Using NHibernator

Add a reference to the NHibernator assembly.
Add a using to NHibernatorFramework.
Get the session with NHibernator.GetSession(). When using a Data Access Layer in your application, it's recommended to use a BaseDal like this:

public class BaseDAO
{
  protected ISession session = null;

  public BaseDAO()
  {
        session = NHibernator.GetSession();
  }
}

When using multiple databases, you will have to specify the name of the sessionFactory to use, like this:
NHibernator.GetSession("SomeDB")
respectively with the name defined in the xml

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory name="SomeDB">
    <property name="hibernate.connection.connection_string">...</property>

  </session-factory>

4. NHibernatorTransaction: Using NHibernatorTransaction will provide Auto Rollback on exceptions, and Automatic session-per-transaction management for a non-web application.
For example:

using (NHibernatorTransaction transaction = new NHibernatorTransaction())
{
    // some dal code here
    transaction.Commit();
}

This can also be done using the "NHibernator Transaction" Snippet, like this:

That's it, Please comment if you have remarks or suggestions.

Oracling your log4net messages Log4NetOracleAppender

2007-02-26T01:00:00.000+02:00

Log4Net has gained enormous popularity across .Net developers over the last years. And there is no wonder, at last there is a logging utility that is powerful and yet easy to use.
For those of you who don''t know log4net, check it out at http://logging.apache.org/log4net/.
One of the coolest things about log4net, is the Appender class that allows developers to output their logs to different logging targets.

log4net is supplied with more than a couple appenders (see examples here http://logging.apache.org/log4net/release/config-examples.html)
with them we can output our logs in to text files, event viewer, xml, console, smtp, database and many more. But there is still one question remains, what appender should I use?

Chossing an Appender
Projects are usually struggling with the question, of what appender is most suitable for their application. The first obvious choice is usually the RollingFileAppender, which is simple, reliable and easy to configure. But what about DatabaseAppender (or in it's formal name 'AdoNetAppender')?

Writing logs to database has a lot of advantages:

Deals well with large quantities of data. can store millions and millions of rows, and has a built in mechanism for archiving.
Shared across multiple applications, and more importantly across multiple applications servers.
And maybe the most important thing: messages written to database, are formatted to fields, and therefore queryable. Message stored in the database can be easily sorted, filtered, grouped and so on.

But what about the disadvantages?
The most common issues developers usually raise against storing messages in the database are:

What if I have a problem in my system that's related with the database itself? That is indeed a good issue, but databases are much more stable these days, and in a well maintained environment, this should be a rare issue. Besides, isn't this problem also relevant to files? Writing to a file also isn't fault-safe, file can be locked by other process, the disk can be full. So in any case, a backup log is a good practice.
The 2nd one is usually, what about the performance? Well this is cool part.
Log4net and the AdoNetAppender in particular, are designed to make the performance optimal, to the point that it's a non-issue in most systems.

Some performance tricks

Maybe the most importance thing is that log messages are asynchronous, meaning - your application never waits till message is actually written to database, so logging doesn't really affect the application response time.Connection always opened. Because the logger is a singleton in the system, there is no sense in opening and closing connections for each execution. So there is no overhead for opening connections.
AdoNetAppender also uses statement caching against the database. This feature minimizes the execution process very significantly.
Messages aren't flushed to the database for every message, thanks to log4net's buffering Appenders, messages persistence is deferred until the buffer is full so database round trips are minimized.

So, can it get any better than this?
The answer is yes, it can!

Log4NetOracleAppender

So I was looking at the source code of the AdoNetAppender, to see how is the database execution is implemented. And there it was:

a For loop 
    With a cmd.ExecuteNonQuery() inside

Why not use bind array ?!
In that moment I was shocked, all that hard work for nothing, to go that far and ruin it all at the end?
ok, maybe I'm exaggerating a little bit , AdoNetAppender is a generic database appender and not Oracle appender, so obviously it would not use that feature. But actually, bind array perform up to a 100 times better than multiple executions (depends on network and the number of rows).

OracleAppender

So I took AdoNetAppender, and twiked it a little. Basically making it to work with ODP.NET.
The major change is in the SendBuffer method, which is the method responsible for adding the records to the database, when the buffer is full. And it goes something like this...

ArrayList paramArr = null;
foreach (OracleAppenderParameter param in m_parameters)
{
    paramArr = new ArrayList();
    OracleParameter oracleParam = (OracleParameter)m_dbCommand.Parameters[param.ParameterName];
    foreach (LoggingEvent e in events)
    {
        object value = param.Layout.Format(e);
        if (value.ToString()=="(null)")
        {
            value = System.DBNull.Value;
        }
        paramArr.Add(value);
        
    }
    oracleParam.Value = paramArr.ToArray();
}
m_dbCommand.ArrayBindCount = events.Length;
m_dbCommand.ExecuteNonQuery();

You can download the complete code here: Log4netOracleAppender.zip.
It's a good practice that you compile it into a dedicated assembly. If you do, make sure you use the full qualified name for the type, in the configuration (described later on)

Configuration

The configuration isn't really different from the built-in AdoNetAppender, but to save you the hassle of reading some documentation, here is the short version:

Add the OracleAppender section

<appender name="OracleAppender" type="log4net.Appender.OracleAppender" >
  <connectionString value="data source=xe;User ID=tomer;Password=tomer" />
  <bufferSize value="20"/>
  <commandText value="INSERT INTO Log (LogDate,Thread,logLevel,Logger,Message) VALUES (to_timestamp(:log_date, 'dd-MM-yyyy HH24:mi:ss.ff6'), :thread, :log_level, :logger, :message)" />
  <parameter>
    <parameterName value=":log_date" />
    <dbType value="String" />
    <layout type="log4net.Layout.PatternLayout" value="%date{dd'-'MM'-'yyyy HH':'mm':'ss'.'ffffff}" />
  </parameter>
  <parameter>
    <parameterName value=":thread" />
    <dbType value="String" />
    <size value="255" />
    <layout type="log4net.Layout.PatternLayout" value="%thread" />
  </parameter>
  <parameter>
    <parameterName value=":log_level" />
    <dbType value="String" />
    <size value="50" />
    <layout type="log4net.Layout.PatternLayout" value="%level" />
  </parameter>
  <parameter>
    <parameterName value=":logger" />
    <dbType value="String" />
    <size value="255" />
    <layout type="log4net.Layout.PatternLayout" value="%logger" />
  </parameter>
  <parameter>
    <parameterName value=":message" />
    <dbType value="String" />
    <size value="4000" />
    <layout type="log4net.Layout.PatternLayout" value="%message" />
  </parameter>
</appender>

Make sure you change the connection string parameter to the desired database. You can also change the commandText to suit your database table and columns.

The BufferSize parameter is where you can set how many rows will be buffered until all rows flushed to the database with one Bind Array statement.

Now just make sure that you create the logs table itself, for example:

CREATE TABLE "LOG" 
("LOGDATE" TIMESTAMP(6),
"THREAD" VARCHAR2(255 BYTE),
"LOGLEVEL" VARCHAR2(20 BYTE),
"LOGGER" VARCHAR2(255 BYTE),
"MESSAGE" VARCHAR2(4000 BYTE))  

As long as ODP.NET is under the hood - NHibernate

2007-01-16T16:14:00.000+02:00

A lot of developers are embracing ODP.NET as their Oracle ADO.NET provider (this as opposed to the built in Microsoft OracleClient).
ODP.NET is a high performance provider that takes advantage of Oracle database functionality. More than that, ODP.NET is fully documented, and updates regularly.

When working with NHinbernate, we can still make sure that it's using ODP.NET under the hood. This will allow us to utilize ODP.NET features like the Connection Pool, Tracing etc. Plus, since this product is at Oracle's hands, it will ensure us compatibility and optimization with the Oracle database.

In the NHibernate config file, use this driver_class:

<property name="connection.driver_class">
NHibernate.Driver.OracleDataClientDriver
</property>

Can it be easier than that?

Welcome Oracle & .Net developers

2007-01-05T22:53:00.000+02:00

For quite some time I've been planning on creating a Blog to share my thoughts and knowledge with the people. So today I’m posting my first post (I hope the first of many…).

As you can see in the “About Me” section, I’m a .Net consultant working in Oracle (I’m sure this fact puzzles some of you :-) . As you probably know, there are more than a few .Net project that chose to use Oracle as their database. That’s the reason Oracle is committed to provide the best connectivity and functionality between these two platforms, and that is why I’m here.

In this Blog you will find posts on best practices with Oracle .Net related products like “Oracle Data Provider for .Net” (ODP.NET), "Oracle Developer Tools for Visual Studio .Net" (ODT.NET), "Oracle Database Extensions for .Net". I will also submit information and code, on database connectivity from other products like the ASP.NET 2.0 Providers, log4net.
In addition, I will try to demonstrate open source solutions that help developers build a good enterprise application, like NHibernate, Spring.Net, etc.

So… that’s it for now. I will appreciate any feedbacks on stuff related to .Net and Oracle that you would like to see in this Blog.

Stay tuned.