Summary
So far I’ve written a lot of posts about Fluent NHibernate using MS SQL Server. I wanted to demonstrate how to connect to Oracle and what the differences were between SQL and Oracle. Getting the connection to work is probably the most difficult part. After that, the mappings are a bit different, but not complicated.
Installing Oracle
Oracle has a free version of it’s database for development purposes. This is an excellent strategy for a company to get it’s product into smaller companies. One of the strongest influences on which database engine is used at a company is based on what the developers are familiar with. Once a database server has been established, it will be there for a long time (it’s very difficult and expensive to change database servers once a system goes live). As a developer, I like to keep my options open, so I’ve experimented with many databases in my career. I’ve also used Oracle and SQL Server on major projects, and I converted a major project from Oracle to SQL Server.
Back to the free version of Oracle that I was talking about… If you go to Oracle’s website and create an account, you can download the windows version of Oracle XE (Express Edition). This is an easy to use version of Oracle that you can install on your PC and experiment with. I’m currently working with 11g Express:
Just download the x64 (I’m assuming you are on a 64 bit machine) and install it. Once Oracle is installed you’ll have to create your own workspace and create some tables. Go to the “Get Started” application. It should startup a browser and show an interface like this:
Next, you’ll need to click on “Application Express”. This will allow you to setup a new workspace. You’ll need to use your “SYSTEM” id and password to get to the express application (which you have to set when you install the application). You should now see this screen:
This is where you’ll setup your workspace. You can choose a name for your “Database Username” which is not used in the connection string for this sample. Your Application Express Username and password is important. You’ll need these for the NHibernate connection string. Once you have created a workspace you will have access to a lot of tools to administer your database.
The easiest way to create your tables is to go to the SQL workshop section under Object Browser. There is a “Create” button that can be used to create new tables. Click that button, and select “Table”:
Now you can create one table at a time. For this demo, you’ll need three tables: store, product and producttype. Start with producttype and use this:
Make sure you set the primary key on each of these tables to populate from new sequence. This is the same as setting an identity on a primary key in MS SQL:
Then add product:
Then add store:
If you’re feeling brave, you can add in the relational integrity constraints (foreign keys) while you create your tables. Otherwise, you can dig around for the raw queries to create such constraints.
The Oracle Database Connection
You’ll need the same 3 dlls used in any of my previous NHibernate samples: FluentNHibernate.dll, Iesi.Collections.dll and NHibernate.dll. You can get these using the NuGet package manager (or just download my sample and grab them from the 3rdPartyDLLs directory.
I created a session factory to connect to my default Oracle.Instance. Here’s the code:
public class OracleSessionFactory
{
private static ISessionFactory _sessionFactory;
private static ISessionFactory SessionFactory
{
get
{
if (_sessionFactory == null)
{
_sessionFactory = Fluently.Configure()
.Database(OracleClientConfiguration.Oracle10
.ConnectionString("DATA SOURCE=XE;USER ID=username;PASSWORD=pass;")
.Driver<NHibernate.Driver.OracleClientDriver>())
.Mappings(m => m.FluentMappings.Add<ProductTypeMap>())
.Mappings(m => m.FluentMappings.Add<ProductMap>())
.Mappings(m => m.FluentMappings.Add<StoreMap>())
.ExposeConfiguration(config =>
{
SchemaExport schemaExport = new SchemaExport(config);
})
.BuildSessionFactory();
}
return _sessionFactory;
}
}
public static ISession OpenSession()
{
return SessionFactory.OpenSession();
}
}
You’ll need to modify your mappings to something like this:
public class ProductType
{
public virtual int Id { get; set; }
public virtual string Description { get; set; }
}
public class ProductTypeMap : ClassMap<ProductType>
{
public ProductTypeMap()
{
Table("ProductType");
Id(u => u.Id).GeneratedBy.Sequence("PRODUCTTYPE_SEQ").Not.Nullable();
Map(u => u.Description).Length(50).Nullable();
}
}
The “Sequence” attribute is necessary to declare that the “Id” field is a sequence field. This is similar to the SQL Server identity declaration.
If you were to build this program in a console application, set your user name and password correct and run the program, you’ll get an error like this:
This is an annoying side affect that can be solved by setting the bitness of the project itself. Right-click on your project and select “Properties”. Choose “Build” and change the “Platform Target” from “Any” to “x64” (if you’re using the 64 bit version of Oracle). Now you can successfully run your application.
From this point on, everything about Fluent NHibernate is the same. I have included a select, insert, delete and update query in my sample application so you can see all the CRUD operations against an Oracle server.
Testing the Connection Outside Visual Studio
If you’re having difficulty getting your Oracle connection to work you can use the “Data Link Properties” window to test your connection parameters. Create a text file on your desktop and name it “conn.udl”, then double-click on that file and you’ll see this window:
Select “Oracle Provider for OLE DB” and click the “Next >>” button. Now fill in your connection information in this window. Oracle uses “XE” as the default for the Data Source:
Once you click the “Test Connection” button you should get confirmation of a good connection. Otherwise, you’ll get an error. If you can get your settings to work in this window, then you’re one step closer to making your Oracle session work.
Sample Application
You can download this sample application and run it on your PC (after you install Oracle XE). Be sure and set your user name and password in the SessionFactory.cs file.