» Publishers, Monetize your RSS feeds with FeedShow: More infos (Show/Hide Ads)?
NHibernateConfigurationBuilder; } } }
The Installer()); };
Side note
The same mechanics can also be used to manage the lifetime of Entity framework’s objectcontext! Even if you are in a wcf context, just change the lifestyle of your session accordingly (ehum wcf facility) ;)
Object-Relational Mapping. Is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a “virtual object database” that can be used from within the programming language.
Entity Framework. ADO.NET Entity Framework (EF) is an open source[1] object-relational mapping (OR) framework for the NET Framework.
NHibernate. the popular Java O/R mapper Hibernate to .NET.
CSLA. Component-based Scalable Logical Architecture is a software framework created by Rockford Lhotka that provides a standard way to create robust object oriented programs using business objects. Business objects are objects that abstract business entities in an object oriented program. Some examples of business entities include sales orders, employees, or invoices.
Dapper. Is an object-relational mapping (ORM) solution for the Microsoft .NET platform: it provides a framework for mapping an object-oriented domain model to a traditional relational database. Its purpose is to relieve the developer from a significant portion of relational data persistence-related programming tasks. Dapper is free as open source software that is distributed under the Apache License 2.0.
This post assume that you already download and extract NHibernate to get some needed dlls. Assume that you have and SQL Server DB with Table Pet included 4 fields: Id, Name, Species and Birthday. One more thing is download NHibernate.Mapping.Attribute.dll
1. Open VS and create a ClassLibrary project. This project will be incharged to talk to NHibernate
2. Add reference to NHibernate.dll and NHibernate.Mapping.Attribute: using NHibernate,Mapping,Attribute assembly
[NHibernate.Mapping.Attributes.Class(Table="Pet")]
public class DTOPet
{
[Id(Name="Id")]
[Generator(Class = "guid")]
public virtual Guid Id { get; set; }
[Property]
public virtual string Name { get; set; }
[Property]
public virtual string Species { get; set; }
[Property]
public virtual DateTime Birthday { get; set; }
}
5. Add a class for handle session called NHibernateHelper.cs. This class need to use NHibernate.Mapping.Attribute assembly
public sealed class NHibernateHelper
{
private static ISessionFactory SessionFactory;
public static void OpenSession()
{
Configuration config = new Configuration();
config.Configure();
config.AddInputStream(HbmSerializer.Default.Serialize();
}
}();
}
}
}
7..
This post assume that you already download and extract NHibernate to get some needed dlls. And assume that you have and SQL Server DB with Table Pet included 4 fields: Id, Name, Species and Birthday
1. Open VS and create a ClassLibrary project. This project will be incharged to talk to NHibernate
2. Add reference to NHibernate:
public class DTOPet
{
public virtual Guid Id { get; set; }
public virtual string Name { get; set; }
public virtual string Species { get; set; }
public virtual DateTime Birthday { get; set; }
}
5. Create Pet.hbm.xml mapping file for this class
<?xml version=”1.0″ encoding=”utf-8″ ?>
<hibernate-mapping xmlns=”urn:nhibernate-mapping-2.2″ assembly=”ProjectName” namespace=”Namespace contain DTOPet”>
<class name=”DTOPet” table=”Pet”>
<id name=”Id”>
<generator/>
</id>
<property name=”Name”> <!–Name of object field–>
<column name=”Name”/> <!–Name of data field–>
</property>
<property name=”Species”>
<column name=”Species”/>
</property>
<property name =”Birthday”>
<column name=”Birthday”/>
</property>
</class>
</hibernate-mapping>
6. Add a class for handle session called NHibernateHelper.cs.
public sealed class NHibernateHelper
{
private static ISessionFactory SessionFactory;
public static void OpenSession()
{
Configuration config = new Configuration();
config.Configure();
config.AddAssembly();
}
}();
}
}
}
8..
Some amazing books which I am keeping with me these days for reference and going through portions whenever I get the opportunity.
Amazon List::
- NuGet Package Manager (unless you already have NHibernate)
- Visual Studio 2010
- MS SQL 2005 or 2008 :)
So, lets begin …
Step 1:- Create a 2-Thier Project
1. New Web Application and name solution to “MyFirstNhibernate”
2. Add a new Class Library project to the solution and name it “DataLayer” which will be used to connect to the database through Nhibernate
3. Rename the Web Layer to “PresentationLayer” and add reference to the “DataLayer” Project
4. In the “DataLayer” Class Library add the following folders:
– Design (will contain class diagram of all classes in the Domain Folder)
– Domain (will contain classes to represent tables and their properties in the database)
– Mappings (will contain xml files to map to tables in database through Nhibernate)
– Repositories (method implementation for Select, Insert, Update and Delete in tables of the database)
Step 2:- Install Nhibernate
Nhibernate has to be installed for both Thiers. Make sure you install NuGet to be able to complete this step:
Visual Studio Tools >> Library Package Manager >> Manage NuGet Packages for Solution >> type “Nhibernate” >> Add Nhibernate
Step 3:- Define Business Objects (Domain Folder)
In the folder “Domain”, add a new class and name it “Book.cs” and copy the following properties:
public class Book
{
public virtual Guid Id { get; set; }
public virtual string Title { get; set; }
public virtual string Author { get; set; }
}
Step 4:- Define the Mapping (Mappings Folder)
Add XML file and make sure to name it with the right convension naming for nhibernate to auto recognize it is a mapping file: use ending .hbm.xml
Lets name it “Book.hbm.xml” for this example to match the Book class in the Domain.
IMP: Click on the newely created xml file, on properties, select “Embedded Resource” for Build Action
Step 5:- Setting XML Schema and properties
Now we need to set the XML schema for the xml we just created. Lets start by importing the nhibernate xml schema as follow:
1. In Windows Explorer: Open the folder of your project
2. Click packages >> click on the Nhibernate.3.3.2 folder
3. Copy the nhibernate-mapping.xsd file
4. Go to the main directory of your Class Library “DataLayer” and paste it.
So, now we need to define that schema in our mapping XML file, and that should look like:
<?xml version=”1.0″ encoding=”utf-8″ ?>
<hibernate-mapping xmlns=”urn:nhibernate-mapping” assembly=”MyFirstNhibernate” namespace=”DataLayer.Domain”>
<!–Your Mapping Code Here –>
</hibernate-mapping>
Once the schema has been defined, we need to create entity mapping by listing all columns and their properties In the <!–Your Mapping Code Here –> above:
<class name=”Book”>
<id name=”Id”>
<generator class=”guid” />
</id>
<property name=”Title” not-null=”true” />
<property name=”Author” not-null=”true” />
</class>
Step 6:- Configure Nhibernate to SQL Server 2008
Create a new XML file in the main directory of your project and name it hibernate.cfg.xml
On the propertied of hibernate.cfg.xml change the setting “Copy to Output directory” to “Copy Always”
In the hibernate.cfg.xml copy the following Connection:
<hibernate-configuration xmlns=”urn:nhibernate-configuration-2.2″>
<session-factory>
<property name=”connection.provider”>NHibernate.Connection.DriverConnectionProvider</property>
<property name=”connection.driver_class”>NHibernate.Driver.SqlClientDriver</property>
<property name=”connection.connection_string”>Data Source=XXX.XXX.XXX.XXX;Initial Catalog=DBname;MultipleActiveResultSets=True;Persist Security Info=True;User ID=username;Password=********</property>
<property name=”dialect”>NHibernate.Dialect.MsSql2008Dialect</property>
<property name=”show_sql”>true</property>
</session-factory>
</hibernate-configuration>
P.S do not forget to change the connection string with the right values for the IP, Catalog, UserId and password
Copy this hibernate.cfg.xml file from the DataLayer into the Presentation Layer where we will test connection to the database.
Step 7:- Create ISessionFactory (NhibernateHelper)
Create a new class in the main directory of the Class Library and name it “NhibernateHelper.cs” which will create a session factory when the client makes his first request a new session (ONLY ONCE)
Add the following references to the NhibernateHelper class:
using NHibernate;
using NHibernate.Cfg;
using DataLayer.Domain;
Make sure the class is made public and copy the following ISessionFactory methods:
private static ISessionFactory _sessionFactory;
private static ISessionFactory SessionFactoryBook
{
get
{
if (_sessionFactory == null)
{
var configuration = new Configuration();
configuration.Configure();
configuration.AddAssembly(typeof(Book).Assembly);
_sessionFactory = configuration.BuildSessionFactory();
}
return _sessionFactory;
}
}
public static ISession OpenSessionProduct()
{
return SessionFactoryBook.OpenSession();
}
Step 8:- Create Table from Schema and Test Connection
Now lets check if our code is working and to do so we will create the Book table from the schema created.
In the “DataLayer” create a new class and name it “SchemaGenerator.cs”, add the following references:
using NHibernate.Cfg;
using NHibernate.Tool.hbm2ddl;
using DataLayer.Domain;
And paste the following method (make sure to make the class public):
public void generate_book_schema()
{
var cfg = new Configuration();
cfg.Configure();
cfg.AddAssembly(typeof(Book).Assembly);
new SchemaExport(cfg).Execute(false, true, false);
}
Now, lets go to the “PresentationLayer” and on the Default page add a button “btn_Create_Book_Table”.
On the on_click event call the method we just created in the SchemaGenerator.cs which should look something like:
new DataLayer.SchemaGenerator().generate_book_schema();
Run this page and click the button.
Finally, check your tables in the database and you should see the table “Book” listed within the tables :)
Step 9:- Create Repositories (Domain Folder)
Repository interface is part of the domain, while implementation will be held in a sub folder of the domain to keep the domain persistant ignorant (PI).
In the Domain Folder, Add>> New Item>> Interface >> name it “IBookRepository”
Set the interface to public and add the following code for the Select, Insert, Update and Delete (a.k.a CRUD create, read, update, delete):
public interface IBookRepository
{
void Add(Book book);
void Update(Book book);
void Delete(Book book);
Book GetById(Guid Id);
}
Step 10:- Define Method from Repositories (Repositories Folder)
Add a new class and name it “BookRepository” where implementation of each method will take place.
Unless we inheritance from IBookRepository which we created above, it will give us an error so do not forget to inherit the interface.
So lets implement the first method, Add:
public void Add(Book book)
{
using (ISession session = NhibernateHelper.OpenSessionBook())
using (ITransaction transaction = session.BeginTransaction())
{
try
{
session.Save(book);
//Save Changes in Database
transaction.Commit();
}
catch (Exception ex)
{
//If error occurs, all changes will be reverted
transaction.Rollback();
}
}
}
So thats our first method which creates an ISession for transactions which allow us to rollback if an exception occurs. This is mostly useful when update multuple tables.
Now implement the Update and Delete which are pretty much the same expect you will need to change the session.Save(book) accordingly to session.Update(book) and session.Delete(Book).
Last method is the Get Row by a specific Id which in this is using a GUID / uniqueidentifier:
public Book GetById(Guid Id)
{
using (ISession session = NhibernateHelper.OpenSessionBook())
return session.Get<Book>(Id);
}
Unless you implement all 4 methods, a similar error to the following will keep showing up:
‘DataLayer.Repositories.BookRepository’ does not implement interface member ‘DataLayer.Domain.IBookRepository.Update(DataLayer.Domain.Book)’
Step 11:- Testing Add, Update, Delete Method
1. Testing the ADD Method:
In our Web Project, the “PresentationLayer” in the Default page or any other page you prefer add the following table:
<fieldset>
<legend> Book Details: </legend>
<table>
<tr>
<td>Title:</td>
<td><asp:TextBox ID=”txt_title” runat=”server”></asp:TextBox></td>
</tr>
<tr>
<td>Author:</td>
<td><asp:TextBox ID=”txt_author” runat=”server”></asp:TextBox></td>
</tr>
<tr>
<td colspan=”2″><asp:Button ID=”btn_add_book” runat=”server” Text=”Add New Book”
onclick=”btn_add_book_Click” /></td>
</tr>
</table>
</fieldset>
In your code behind add the on_click event and the required references to DataLayer.Domain and DataLayer.Repositories:
protected void btn_add_book_Click(object sender, EventArgs e)
{
var book = new Book { Title = (txt_title.Text), Author = (txt_author.Text) };
IBookRepository repository = new BookRepository();
repository.Add(book);
}
Now, run the page in your browser and click the button “Add New Book”
Open your database >> Right-Click table Book >> Show Table Data:
and there is our first row!!!!
2. Testing the Update Method:
Lets first copy the Guid (Id) of our first book and paste it somewhere easy to find so that we can edit that row.
In the same table we created above, add the following row:
<tr>
<td colspan=”2″><asp:Button ID=”btn_update_book” runat=”server” Text=”Update Book”
onclick=”btn_update_book_Click” /></td>
</tr>
and in the code behind lets add its on_click event:
protected void btn_update_book_Click(object sender, EventArgs e)
{
var book = new Book { Id = new Guid(txt_id.Text), Title = (txt_title.Text), Author = (txt_author.Text) };
IBookRepository repository = new BookRepository();
repository.Update(book);
}
Now, in your browser copy and paste the guid in the ID textbox, and the new title and author… click “Update Book”
Lets confirm the update, refresh the table content and you should see your changes there:
3. Testing the Delete Method
Lets add a new button “Delete Book” to the current html table:
<tr>
<td colspan=”2″><asp:Button ID=”btn_delete_book” runat=”server” Text=”Delete Book”
onclick=”btn_delete_book_Click” /></td>
</tr>
Also as we did before, add the on_click event in the code behind:
protected void btn_delete_book_Click(object sender, EventArgs e)
{
var book = new Book { Id = new Guid(txt_id.Text) };
IBookRepository repository = new BookRepository();
repository.Delete(book);
}
Run in your browser >> copy and paste the same guid so that we delete that same row:
The ID is enough for it to work, click “Delete Book” button and refresh your table one last time:
4. Testing SELECT ( Get Book By ID )
Our last method to test is the Select row by ID, so lets start by adding a new button to the html table:
<tr>
<td colspan=”2″><asp:Button ID=”btn_get_book_by_id” runat=”server” Text=”Get Book By ID”
onclick=”btn_get_book_by_id_Click” /></td>
</tr>
Add the code behind:
protected void btn_get_book_by_id_Click(object sender, EventArgs e)
{
Guid Id = new Guid(“4823c5b6-7b70-459b-9a8b-86beb624a623“); //Replace GUID with your Id
IBookRepository repository = new BookRepository();
var book = repository.GetById(Id);
txt_title.Text = book.Title;
txt_author.Text = book.Author;
}
Now we need to add a new row in the Book table, so run the Page, fill the Title and Author and click “Add New Book”
Refresh the table and copy the Id (guid)
Paste the GUID in the code behind where we have Guid Id = new Guid(“”);
Run the Page again and click “Get Book By ID”
As in the screenshot above, the Title and the Author of the given ID will be displayed.
And that is done .. hope you did not encounter any issues which have not been covered in this tutorial. If so do not hasitate to ask or if you have the solution to it, helpful comments are appreciated.
Goodluck and enjoy :)
Download <a href=”“> Project Demo </a>
I’ve recently moved our Membership data to NHibernate on SQL Azure, and wanted a secure way to store sensitive “safely”.
My starting point for this was the excellent article at
The only problem is that the default implementation does not salt the encrypted data – so identical plain text values are identical when encrypted and this can make cracking the data easier.
A salt is a random value that is used to seed the encryption algorithm to avoid this. The salt should be randomly generated (ie, not derived from the data itself) and can be stored in an insecure way as knowing it doesn’t help you particularly when trying to crack the encryption.
I spent ages going through different routes to achieve this:
- Adding a salt column to the entity schema – FAIL – because at the point of decryption it’s not possible to read from other columns.
- Writing an interceptor and “transparently” encrypting and decrypting data when loaded and saved – FAIL – didn’t seem to be working in a whole load of ways – probably my bad
- Adding a whole bunch of fields to the entity model that aren’t persisted and allow programatic access to decrypted strings in conjunction with a salt column – FAIL – just plain messy
In the end the answer was staring me in the face and popped into my head when I woke up the next day – proving working at something till 1am isn’t always the best option!
The solution wasn’t actually in the encrypted field code () but actually in the encryptor code.
I modified the code so that on encryption a random salt string is generated and used to encrypt, but is then prepended to the resulting string. The encryptor result is now of the form <salt>:<encrypted data>.
On decryption all that’s needed is to split the string and reverse the process using the correct salt.
The only negative is that it’s not possible to query across the dataset using the encrypted fields as the salt is field specific so can’t be pre-calculated and used in the SQL command. It’s necessary to retrieve the whole dataset, which is transparently decrypted and then query accross this in the application.
We only use encryption for fairly small tables – user data generally so this is a hit we’re prepared to take to protect client data.
Learning How to be a Better Developer
I see myself as a decent-ish software developer. I am great at the things I do know but I have been a developer long enough to build a system without taking advantage of the full set of language features available to me. I have neglected to learn some of the more advanced language features in .NET, some of the valuable patterns and practices that have been discovered as well as a lot of the tools that are available to build better software.
This will be my last post for the week and I have a long enough list of objectives to keep me busy for a while.
As I mentioned in my initial post, I am using this blog as a self evaluation tool. If you are reading this and learning from it, GREAT! If you are bored, skip to the next post. This blog is almost like a pocket notepad for me so I know that some of these posts are going to be a bit tedious.
Objectives:
Admin
I have 31 pages on hand written notes from learning in the past two weeks. I need to organise them and type them up so that I can refer back to them easily. I will use Evernote for this.
Broad Learning
- I need to finish up the test first design courses on Pluralsight and complete the assessments on them.
- I have bookmarked a few web pages that I want to read through to gain a more thorough understanding of IoC. These are:
-
-
-
-
-
-(X(1)S(mmzabk45b40p3e45b4p00245))/Default.aspx?Page=MainPage&NS=Windsor&AspxAutoDetectCookieSupport=1
-
-
- Learn more about service locator and contextualised lookup
- Learn the prototype pattern
- Read more on what agile and lean methods could be practical for me as a one man development house
Technical Learning
- Read summaries of StructureMap, Spring.NET, Unity and Ninject. While I feel like I’ve already decided on Castle Windsor as a DI container, I don’t know anything about any other ones so it would be best to have a quick peruse through them.
- Gather more insight into using Entity Framework, ActiveRecord and NHibernate and maybe to a small tutorial on each.
- Read about the Logging Facility in Castle Windsor as Simone Busoli makes good mention of it in this article.
- Learn how to properly implement IInitializable/ISupportable and IDisposable interfaces
- Start learning how to use Regular Expressions
As you can see my plate is full.
Progress
I have made some progress though. Here is what I’ve learned so far:
- I now understand the concept of test first development and how to implement it is simple scenarios
- Defining units of code
- Red – Green – Refactor Cycle
- Using NUnit
- Differentiating between unit tests and integration tests
- Testing the sad path
- Refactoring with great help from ReSharper
- I’ve started using ReSharper to increase my productivity
- I’ve brushed over behavioural driven design which is really just a more refined way of looking at test driven design
- I’ve schooled myself on the following patterns and methods:
- Builder Design Pattern
- Template Method Design Pattern
- Strategy Method
- Factory Method
- Adapter Pattern
- Decorator Pattern
- I now understand Dependency Injection
- I’ve learned how to use two C# keywords: yield and lock
- I’ve learned how to use the Castle Windsor container.
- I’ve learned about the out of box lifestyles and the lifecycle of a component inside the container
- I’ve learned how to inherit from Castle Windsor container to create a custom container
- I’ve learned how to use configuration files to inject dependencies into the container and change behaviour of the component and change the injected dependencies using pre-processor directives
- I’ve learned how to build a custom type converter for a component
- I’ve learned how to use two semantic facilities: Factory Support and Startable
So not too shabby on my part.
Catch you mofo’s on the flip side.
BEAN
Dependency Injection & Castle Windsor
As I mentioned in my last post, one of my goals was to school myself on inversion of control (IoC) so that I could start practicing it in my daily work. I also mentioned that IoC is an incredibly broad and vague practice that can be implemented using a number of methods such as:
- Using a service locator
- Using contextualised lookup
- Using dependency injection
Well I started finding this topic hard to wrap my head around mainly because I couldn’t put it into practice based on the definitions provided by Guru’s like Martin Fowler. You see guy’s like this, are very careful about putting their blinkers on and concentration too much on the practical context as they need to cover all bases. So after a few questions I posted on StackOverflow I’ve actually made some real progress. Firstly, I have been told that modern languages such as C# and Java are great for using dependency injection while service locator and contextualised lookup were recognised as practices to cater IoC for older or less fully OO languages. That was when I decided to focus on learning about how to practice dependency injection.
This led me on a journey to find the best container framework for dependency injection The big ones are:
- Castle Windsor
- StructureMap
- Spring.NET
- Unity
- Ninject
- PicoContainer
And there are quite a lot more. I went for a beer with Susan on Wednesday and he says that they use Castle Windsor because it seemed to be the most feature rich and mature container for their needs at the time. I’ve been told that Spring.NET is the most ‘enterprisy’ one (whatever that means) but that it requires more work to set it up. I am one man who needs to climb a mountain of refactoring so the long path just doesn’t seem like the right path. Having said that, taking shortcuts would be, by far, a worse decision. I learned this the hard way by using LINQ2SQL as the ORM for Behemoth.
PicoContainer and Ninject have been described as light containers that perform well. With my very limited knowledge and gut, I decided to look at Castle Windsor because Martin Fowler describes it as ‘well documented’, there are a lot of project teams that seem to be using it and it just feels like a mature product with the right amount of well considered features.
So far I had decided two things:
- Learn and focus on dependency injection to apply IoC practices in my code
- Learn how to make good use of Castle Windsor
This led me onto David Siew’s article on getting started with Castle Windsor which in turn led me to this magnificent piece of work by Simone Busoli. Thanks guys, I have worked through my first Castle Windsor tutorial and feel ready to start leveraging the full power of what seems to be an incredible container.
I would not try and regurgitate what I’ve learned here because why plagiarise? So far, everything that I know about Castle Windsor is courtesy of Simone Busoli. If you want to get started using this container, work through this 4 part tutorial. It is a bit dated and Castle Windsor has changed a bit since it was written but I had a bit of fun figuring out what has changed and how to accommodate to the current version.
What About Actually Working For a Change?
Well, I have to say, for the past two weeks I have been neglecting a lot of work in favour of reading, learning and participating in StackOverflow.
I need to find a balance between work and learning so that the money doesn’t totally dry up in the meantime.
I’ve asked a few questions about what ORM I should use to refactor Behemoth posing the two options of Entity Framework and NHibernate.
Susan reckons NHibernate is great because it provides more granular control over the generated code while Microsoft as tried to include too much in Entity Framework. I’m still blind to this. All I know is LINQ2SQL and that LINQ2SQL is the wrong option for medium-large database systems. The reason I say this is because it is too rich. It does so much and executes so much code in normal, everyday operations that you have to tiptoe around it when your requirements start becoming a bit more complex. I guess the main reason for this is that it implements INotifyPropertyChangeda as well as INotifyPropertyChanging and when you get an inexperienced dev that inserts a lot of processing into a get or set operator, it starts getting a little crazy.
Look I am not dissing LINQ2SQ. It is a fantastic ORM that has tided us over for 6 years and I would hands down recommend it for small-medium projects. It translates lambda syntax into SQL LIKE A BOSS! In fact, it outperforms most DBA’s that I know! But the time has come to recognise that Microsoft is trying hard to shift developer focus away from LINQ2SQL in favour of Entity Framework.
So I haven’t decided yet. Here are the pros and cons:
Entity Framework
Pros:
- Developed by Microsoft for Microsoft Language Users
- Already in Version 4 (doesn’t look like it’s going away anytime soon)
- Well integrated with Visual Studio
- Lot’s of learning material and well documented
Cons:
- I don’t know of too many big project teams using it
- I’ve been told that, similar to LINQ2SQL, it is incredibly feature rich at the expense of performance
- The Jedis on Stack Overflow seem to favour NHibernate
NHibernate
Pros:
- Mature product
- Used by many large project teams who speak volumes about it
- Well integrated with Castle Windsor
- The Jedis on Stack Overflow sing it’s praises
Cons:
- Not as well integrated into VS
- Worried that Microsoft will take what they’ve done and produce something better soon
- I naturally resist products that aren’t from Microsoft as I have been brainwashed over the past twenty years to believe that the Microsoft way is the most intuitive way.
Any Other ORM’s
Ok so you know that I am considering 2 big shot ORMs to use in Behemoth. Would you suggest any others that are better than either of these? If so, let me know and tell me why. I’m hungry to learn.
Side Note
On a tangent, I just want to say that my fiancé made the most awesome salami, cheese and mustard sandwiches for my lunch today. Thank you my love, they were the BOMB.COM!
Anyway, while I salivate over the deliciousness of those sandwiches I just ate, you go drink your soup or whatever.
Adiosh!
BEAN
There are many ways to connect to an Oracle database from a .NET application. You can use ADO .NET directly, or use an ORM solution, such as NHibernate or the likes. In my workplace, previously we are using NHibernate. But for some reason, we stop using it and revert back to the old ADO .NET way.
However, after some experiments, I’ve found my own way to work with Oracle in a .NET environment. I’m using a library called BLToolkit, along with several other tools. After reading their documentation, I’m convinced enough to use it, and so far I’m very satisfied with the result.
Our development approach is database-first, and this article will only cover that. So if you are using code-first approach, this article would not be relevant to you.
The first thing we need is a data model class that maps to our Oracle database. Since we are already spending our precious time designing the database, we want the class to be auto-generated from the database. BLToolkit provides this kind of functionality using a T4 template. Unfortunately, they don’t support generating data model class directly from an Oracle schema. Since I’m not capable enough to write a T4 template to achieve this, I need a workaround.
My workaround here, is by using SQL Server and a utility called SSMA for Oracle. The idea here is by importing our Oracle database to an SQL Server database, and generate a data model from there. For this purpose, the SQL Server Express Edition is sufficient, so you don’t have to worry about licensing issues.
Importing the database
- Open SSMA for Oracle and create a new project.
- Click Connect to Oracle, and provide the information to connect to the Oracle server.
Depends on what user you use to connect to Oracle, you might be shown this warning. In my case, I can safely ignore it and click Continue.
- Click Connect to SQL Server, and provide the information to connect to the SQL Server.
If you are connecting to SQL Server Express, like me, you will get this warning. Just ignore it and click Continue.
- Optionally, you can change the type mapping between Oracle and SQL Server. I usually change the mapping of number to decimal instead of the default float. The reason for this is because I usually use number as a primary key in Oracle. If it is mapped as float in SQL Server, then it would lost its primary key status because SSMA doesn’t seems to allow float to be used a primary key.
- If you made a change to the Oracle schema after connecting to Oracle, you can refresh them by right-clicking the schema name and select Refresh from Database.
- Tick the checkbox next to the schema(s) you want to import, and click Convert Schema.
Optionally, you could also change the target schema in SQL Server.
While the process is running, you can inspect it through the Output window.
- On the SQL Server Metadata Explorer, you’ll see the target schema produced by the conversion process. Right click on it, and select Sync with Database. This process will actually update our SQL Server with the new definition.
- Done. Now you should be able to see your shiny new database (or an up-to-date version of the existing ones) in SQL Server.
Handling the problems
You may encounter some issues while doing this conversion process (you can see them in the Error List Window). Once, I got an error setting a float column as a primary key. Changes the type mapping of number from float to decimal (as described in step 4 above) solve this.
However, some issues might not be so important to us. Keep in mind, the main reason we are doing this process are merely to enable us to auto-generate our data model class. So as long as the SQL Server tables has the same column name, column type, primary key, and foreign key specification as their counterpart in Oracle, then it should be good enough for us.
On the next article, I will cover on how to generate the data model class using the T4 template provided by BLToolkit.
NHibernate’s inverse concept is one of most discussed mappings features (if not the most!) defined by it. In practice, you’ll be using the inverse when you need to set the owner of a “relationship”. Before taking a dive into it and how it affects the way NH generates SQL and manages relationships between entities, here are some facts about inverse:
- It’s a Boolean attribute used only in collection and join mappings;
- By default, it’s set to false;
- It only makes sense when you’re configuring bidirectional relationships between entities;
- You should only set inverse to true on one of the sides of the relationship;
- Not setting it on any of the sides will generate superfluous SQL instructions.
Ok, before trying to understand what this means, lets start with a RDE (ie, a really dumb example): Blogs and posts will do it for now!
public Int32 BlogId { get; set; }
public Int32 Version { get; set; }
public String Description { get; set; }
private IList<Post> _posts = new List<Post>();
public IEnumerable<Post> Posts {
get { return new ReadOnlyCollection<Post>(_posts); }
set { _posts = new List<Post>(value); }
}
public void AddPost(Post post) {
post.Blog = this;
_posts.Add(post);
}
public void RemoveAll() {
foreach (var post in _posts) {
post.Blog = null;
}
_posts.Clear();
}
}
public class Post {
public Int32 PostId { get; set; }
public String Description { get; set; }
public Blog Blog { get; set; }
}
As you can see, there’s nothing too fancy going on here. Each blog has a collection of posts (with each post referencing back its parent blog) and you’ll typically use the AddPost method to add a new Post. After building the classes, we can concentrate in building the mappings. And nothing like Fluent NHibernate to give us a hand. Since I’m not really a great fan of automapping, here’s the code I’ve written to get us started:
public PostMapping() {
Table("Post");
Not.LazyLoad();
Id(p => p.PostId)
.GeneratedBy.Identity()
.Default(0);
Map(p => p.Description);
References(p => p.Blog, "BlogId")
.Not.LazyLoad();
}
}
public class BlogMapping : ClassMap<Blog> {");
}
}
This is more than enough for you to reproduce the tables (if you’re interested in running the examples). I’m also not showing the code I’ve written to create NH’s session factory from these mappings (Fluent’s wiki shows you how to get started, so I’m not repeating it here). Having said that, lets start with the code required to create a new Blog:
var sessionFactory = SessionFactory.CreateSessionFactory();
using (var session = sessionFactory.OpenSession()) {
using (var tran = session.BeginTransaction()) {
var blog = new Blog() {Description = "Testing blog"};
var post = new Post() {Description = "Post 1"};
blog.AddPost(post);
session.SaveOrUpdate(blog);
tran.Commit();
}
}
}
If we were writing the SQL by hand, we could probably agree that 2 SQL instructions would be more than enough for getting the entities stored in the database: we’d start by saving Blog, getting its ID and then we’d insert all its associated posts into the Post’s table (oh, and since Post is also an entity, with probably get its ID too since I’m using autogenerated IDs – not the best of choices for real world projects, but more than enough for our current discussion). The following snippet shows the SQL generated by NHibernate to save the previous objects to the database: = 4 [Type: Int32 (0)]
select @@IDENTITY
UPDATE Post SET BlogId = @p0 WHERE PostId = @p1;@p0 = 4 [Type: Int32 (0)], @p1 = 14 [Type: Int32 (0)]
The first SQL instructions seem to be going as expected, but then there’s an extra update instruction right at the end which, at first sight, makes no sense: we’re updating the Post table’s BlogId field to the ID of the inserted post and this isn’t really needed.
If we change the mappings by setting the inverse flag on the Blog’s Posts mapping, then the results are completely different:")
.Inverse();
}
}
And here’s NH’s generated SQL for the previous insertion: = 5 [Type: Int32 (0)]
select @@IDENTITY
Oh yes, now we’re in business! But what’s going on here? What does Inverse do?
IMO, the main problem in getting the inverse attribute is it’s “negative” meaning. If we set inverse to true, we’re saying that this entity is not responsible for maintaining the relationship. On the other hand, setting inverse to false does mean that that entity is the one responsible for managing the relationship. So, when we added the Inverse call to the Blog side, we’re saying that the Blog entity isn’t responsible for managing its relationship with Post (at the database level). In this case, managing the relationship should be done on the Post’s side. This is a little bit counterintuitive, but if we take a step back and recall how things work at the database level, it might make sense.
In a database, each relationship is represented by a foreign key on the many side (going back to our RDE, the Post table will have a foreign key – BlogId – which references the primary key of the Blog table). Taking this into consideration does help understand the invert attribute: we can insert entries in the Blog table without caring about its associated Posts, but we can’t really add a Post without setting its BlogId foreign key. And that’s why the inverse attribute we’ve set in the Blog side of the mappings did eliminate the superfluous UPDATE call: with it, Blog is no longer responsible for making sure that the foreign key of each post is correctly set up (that will be taken care of by Post).
It goes without saying that in order for the persistence of the Blog aggregate to work, Post must have all the required info so that it doesn’t violate the foreign key rule when it persists itself to the database. And in fact, my initial code already does all the necessary setup. Notice that adding a Post to a Blog will always set its Blog field to its parent (the owning Blog) and that that field is defined as a reference to the Blog table on the mapping file.
Before ending, one extra note: did you notice that Cascade.AllDeleteOrphan() call in the mappings? Well, that’s really important when you want to remove a Post that has previously been persisted from the database. Here’s some code that will help you understand what I’m saying:
var sessionFactory = SessionFactory.CreateSessionFactory();
using (var session = sessionFactory.OpenSession()) {
using (var tran = session.BeginTransaction()) {
var blog = session.Get<Blog>(2);
blog.RemoveAll();
var newPost = new Post {Description = "Post 2"};
blog.AddPost(newPost);
tran.Commit();
}
}
}
After loading a previously saved Blog, I’m removing all its posts and adding a new one. Here’s the log for the generated SQL when you don’t delete orphan’s entities: = 5 [Type: Int32 (0)], @p1 = ‘Testing blog’ [Type: String (0)], @p2 = 2 [Type: Int32 (0)], @p3 = 4 [Type: Int32 (0)]
UPDATE Post SET Description = @p0, BlogId = @p1 WHERE PostId = @p2;@p0 = ‘Post 2′ [Type: String (0)], @p1 = NULL [Type: Int32 (0)], @p2 = 12 [Type: Int32 (0)]
As you can see, the last SQL instruction is trying to set Post table’s foreign key to null and that will not end well. Setting the delete orphan entities attribute does ensure that NH will generate a DELETE instead of an UPDATE. Here’s the SQL generated when you add the delete orphan entities mapping: = 7 [Type: Int32 (0)], @p1 = ‘Testing blog’ [Type: String (0)], @p2 = 2 [Type: Int32 (0)], @p3 = 6 [Type: Int32 (0)]
DELETE FROM Post WHERE PostId = @p0;@p0 = 18 [Type: Int32 (0)]
There’s still more to say about inverse (for instance, we haven’t discussed many-to-many relationships), but I believe that this is more than enough for getting you started.
That’s it for now. Stay tuned for more!
What’s the main difference between:
Restrictions.Dysjunction()
and
Restrictions.Conjunction() ?
Answer:
Dysjunction pertains to OR while Conjunction pertains to AND when translated in SQL language.
When_1<<.
Our Client is looking for a C#.Net developer with experience of WCF / Winforms and VB. The customer is an International leader in the Telecommunications sector.
Technical requirements
Good .NET knowledge (3.5 / 4) – C# & VB.NET
- WPF & winforms
- Experience with ORM (NHibernate)
- Good knowledge of SQL server (T-SQL)
- Mutlithreading (TPL)
- Algorithm complexity
- WCF-knowledge is a plus.
Soft skills
- Able to work alone or in a small team, take a project end to end (from technical analysis to implementation and support).
- Result driven
- Ability to mingle in discussion (challenge requirement, functional aspect and architecture when relevant)
- Telecom knowledge (voice business) is a plus.