Feeds:
Posts
Comments

Posts Tagged ‘mapping’

Dear community,

It’s me once more with a small but handy hint that can make your coding and maintaining a bit easier.

When mapping columns to integral types, it certainly is possible that a field’s value is NULL, right? Now, let’s assume a hypothetical table ORDER that contains a field QUANTITY. Assume also that we have an existing mapping that looks like the one in snippet 1:

   1: <property name="Quantity" column="QUANTITY" type="System.Int32"/>

Snippet 1: Excerpt of a Order table HBM file

Assume also the class we’re mapping to, Order:

   1: public class Order

   2: {

   3:     Int32 Quantity;

   4: ...

Snippet 2: Excerpt of the Order class

Now, what happens to this property once we’re actually mapping values from the DB to it?

If the value is NULL, then the property will assume its default value, which in this case i 0.

So what’s wrong with that?

Simply put, this is correct, but not very effective. Imagine a scenario where we have both 0 and NULL values stored in the QUANTITY column. Once the values are mapped, they could not be distinguished from each other anymore, since they would both map to 0 in our property Order.Quantity.

Hence, we need to come up with a more elegant solution:

We simply use Nullable Types

All you have to do is to change the property Quantity into a Nullable Type, as shown in Snippet 3:

   1: public class Order

   2: {

   3:     Int32? Quantity;

   4: ...

Snippet 3: Excerpt of the changed Order class. Note that the Int32 has been turned into a Int32? Nullable Type.

What else do we have to change? Nothing. NHibernate automatically determines that the property is a nullable type and assigns Null to the property in case of a NULL value arriving from DB.

For the above mentioned scenario, now a 0 would be mapped to a 0 (in the class), and an NULL value from DB would be mapped to an actual Null value (in the class).

That’s it! Enjoy mapping with NHibernate!

Till next time & best regards,

Martin

Advertisements

Read Full Post »

Howdy ladies and gents out there,

Here we go with another NHibernate common problem solved for you. Recently the question arose whether it is possible or not to execute a query with NHibernate that checks whether a property lies in a given range of values or not. Not much of a problem, we would say, since HQL can do it just directly using its SQL-like syntax. For example, if we recall our example DevJour1 from part 1 of the NHibernate noob series, we could define a query in the Book.hbm.xml file:

   1: <query name="GetAllBooksWithinRange">

   2:   <![CDATA[

   3:     select b from Book b where b.Title in ('It', 'Salem's Lot',                                                  'Langoliers');

   4:   ]]>

   5: </query>

Snippet 1: Excerpt from the Book.hbm.xml file: HQL query returning all books whose title lies within a certain range of values.

Problem solved? Not quite. Another requirement to this query was to provide custom sorting w/ a custom field and a custom sort direction. Sounds familiar, doesn’t it? Remember our first NHibernate troubles post: Custom Sorting. Simply put, using HQL, this is not possible. So we had to use Criteria queries. But how do we implement the range value check for a given property using Criteria queries? It’s simple, really:

   1: public static List<Book> GetAllBooksWithinRange()

   2: {

   3:     String[] titles = { "It", "Salem's Lot", "Langoliers" };

   4:     ISession session = Program.OpenSession();

   5:     List<Book> books = session.CreateCriteria(typeof(Book)).

   6:         Add(Restrictions.In("Title", titles)).

   7:         List<Book>().ToList<Book>();

   8:     return books;

   9: }

Snippet 2: Excerpt from the Book class.

BookGetAllBooksWithinRange()explained (line by line):

3: Definition of our range of values

4: Opening a session, recall from Program class of the Custom Sorting article.

5: Creating criteria for type Book .

6: Crucial part: Adding Restrictions.In (NHibernate.Criterion), passing the property that needs to be checked (Title) and the list of range values.

7-8: Calling the List() method(s) and returning the retrieved list

Important: This works also, when comparing a property of a property with a range of values. Example: Assuming, Book contains a class Author , which in turn contains a property Surname. Then we could formulate such a comparison statement like follows:

.Add(Restrictions.In(“Author.Surname”, names)).


Under the assumption that names is an array containing strings.

That’s it! I’m sure there are more problems to be solved!

See you later!

Best regards,

Martin

Read Full Post »

Howdy folks,

And welcome back to part 2 of our newbie nhibernate tutorial series. Here is a quick summary of what we did the last time, in part 1:

  • Create a basic database (w/ 1 table)
  • Create a new console application
  • Add the nhibernate binary files as references to our application

2.1 Creating the Entity Class

So far, so good. This time we will proceed with mapping our first table into a .NET class:

  1. Open the DevJour1 project we created the last time
  2. Add a new class to our project (right-click the project -> Add -> Class).
  3. Name it Book
  4. Add the properties as in the snippet below:

clip_image002

Figure 1: This class will reflect our table Book as of SQL Server DevJour database we created in part 1 of the tutorial series. Each field of the table Book will be mapped accordingly to the class’ property with the same name.

2.2 Creating the Mapping File

The mapping file is an XML document which tells nhibernate which DB field belongs to which property in the class, which type it has and many more things. Let’s dig right in:

1. Create and XML document (right-click on project -> Add new item -> XML file)

2. Name the XML file Book.hbm.xml and click on “Add” (Figure 2)

3. Important: Under Book.hbm.xml file’s properties, under Build Action, choose Embedded Resource. It will not work if you do not choose this type! (Figure 3)

4. Write the code for the mapping file according to Figure 4

clip_image004

Figure 2: Adding the mapping file. The .hbm. middle part of the file name stands for hibernate mapping.

clip_image006

Figure 3: Set the Build Action to Embedded Resource!

clip_image008

Figure 4: The mapping file Book.hbm.xml

2.3 Explanation of the Mapping File (Figure 4, line by line):

2: Specifying that this is a mapping file

3: Specifying the class and the table we are mapping from. Please note that it is required to enter the fully qualified class name followed by a comma, and then add the assembly name. The assembly name can be found by right-clicking on the project -> Properties ->Assembly name. This is important, too! Don’t worry about the lazy=true attribute, we’ll take care of that later.

4-6: Indicates that the PK id of the rows in the table belongs to the property Id of class Book. The generator tag just tells nhibernate that we are going to use our DBs unique id generator by default for creating new rows. This will be used once we create an object and save it to DB.

7-8: Property mappings. Where name attribute indicates the property’s name we’re mapping towards, and column attribute indicates the DB column we’re mapping from.

This is it already. Now we’ll head on to the final part of this issue: Crea

2.4 Creating the Configuration File

The purpose of creating this file is that we need to establish a connection to our database, hence we must provide driver, DB name, login credentials etc. for nhibernate.

Right-click on the project, go to Add new item -> Application Configuration File. Leave the name as it is (App.config, see Figure 5)

The newly created file looks like in Figure 6.

clip_image010

Figure 5: Creating the application configuration file.

clip_image012

Figure 6: The empty App.config

Now, let’s complete the App.config’s code:

   1: <?xml version="1.0"?>

   2:

   3: <configuration>

   4:

   5:     <configSections>

   6:         <section name="hibernate-configuration"

   7:                  type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate"/>

   8:     </configSections>

   9:

  10:     <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">

  11:         <session-factory>

  12:

  13:             <property name="connection.provider">

  14:                 NHibernate.Connection.DriverConnectionProvider

  15:             </property>

  16:

  17:             <property name="connection.driver_class">

  18:                 NHibernate.Driver.SqlClientDriver

  19:             </property>

  20:

  21:             <property name="connection.connection_string">

  22:                 Data Source=.\SQLEXPRESS;

  23:                                 AttachDbFilename=C:\PathToYourDbFile\devjour.mdf;

  24:                                 Integrated Security=True;

  25:                                 Connect Timeout=30;

  26:                                 User Instance=True

  27:             </property>

  28:

  29:             <property name="dialect">

  30:                 NHibernate.Dialect.MsSql2005Dialect

  31:             </property>

  32:

  33:             <property name="proxyfactory.factory_class">

  34:                 NHibernate.ByteCode.Castle.ProxyFactoryFactory,

  35:                 NHibernate.ByteCode.Castle

  36:             </property>

  37:

  38:         </session-factory>

  39:

  40:     </hibernate-configuration>

  41:     <startup>

  42:         <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>

  43:     </startup>

  44: </configuration>

  45:

Snippet 1: The configuration file.

As you can see, the App.config sections specify mostly the database connection modality, i.e. which driver to use, which connection provider, as well as the connection string.

Important: You do not have to write the connection string manually, but can simply copy it from the database explorer. There you simply right-click your database file, choose Properties, and copy the connection string from the corresponding field. In the example, I only split it up by semicolons in order to make it more readable; however: You should leave it as a one-liner.

Now, let’s write the Program class:

   1: using System;

   2:

   3: using System.Collections.Generic;

   4:

   5: using System.Linq;

   6:

   7: using System.Text;

   8:

   9: using NHibernate;

  10:

  11: using NHibernate.Cfg;

  12:

  13: using System.Reflection;

  14:

  15: namespace DevJour1

  16: {

  17:     class Program

  18:     {

  19:         static ISessionFactory s;

  20:         static void Main(string[] args)

  21:         {

  22:             Book b = new Book();

  23:             b.Title = "Pulse";

  24:             b.Author = "King";

  25:             ISession session = OpenSession();

  26:

  27:             try

  28:             {

  29:                 session.Save(b);

  30:                 Console.WriteLine(b.Title + "was successfully saved to DB.");

  31:             }

  32:             catch (Exception e)

  33:             {

  34:                 Console.WriteLine(e.Message);

  35:             }

  36:         }

  37:

  38:         static ISession OpenSession()

  39:         {

  40:             if (s == null)

  41:             {

  42:                 Configuration configuration = new Configuration();

  43:                 configuration.AddAssembly(Assembly.GetCallingAssembly());

  44:                 s= configuration.BuildSessionFactory();

  45:             }

  46:             return s.OpenSession();

  47:         }

  48:     }

  49: }

  50:

Snippet 2: Usage of our mapped entity. In this case, it is used for making an object of class Book persistent to the database.

2.5 Explanation of the Program class (snippet 2, line by line)

Premise: In order to execute queries, insertions (as in Snippet 1, the session’s Save method), updates or deletes, we need an object of which implements the  ISession interface. ISession is part of the nhibernate API, and the OpenSession() method simply returns an instance of such, w.r.t. the current assembly that we are working from.

21-24: Construct a new object of type Book and set its properties manually

25: Retrieve the ISession by calling OpenSession()

27-36: Calling the Save method and passing Book b as parameter. We perform some basic exception handling to see whether we did everything correctly. If no exception is thrown, we are fine.

That was already the essence of this code. We just created a .NET object and saved it to our database. In fact, we did so only about 50 lines of code (and another 45 for the mapping file).

Quite simple, wasn’t it?

Next time, in part 3, we’ll have a look on how to query the objects from DB and at the different ways we can execute queries.

Stay tuned for more nhibernate parts!

Best regards,

Martin

Read Full Post »

Howdy,

This is my first post here, and I’d like to go right into medias res. The motivation is clear: I am a developer dealing with MS VS & nhibernate in order to map DB schemas. It is not a long time, since I am working on the topic, so I encountered a few troubles that can be shared, I guess. I carried the tests out using a sample schema on an Oracle 10g XE instance that I set up for this purpose.

Recently, I came across the problem that I needed to sort data upon retrieval. Nothing special there, one could say. But what I needed was sorting by:

a.) a custom field, provided by the user
b.) a custom order direction (asc/desc), also user provided

Unless I wanted to use the custom sort direction, HQL did the trick for me. I simply could create a query like:

Sample hql query using custom sort field

Just had to pass the customField parameter before calling the AllHouses query. However, when I try to pass also the sort direction, like in the following figure, any calls to the query fail.

Sample hql query using custom sort field and custom sort direction

At this point, any call to the query fails. The Oracle XE is obviously not able to assign the ASC/DESC sorting direction through a parameter. Hence, I came up w/ the following solution: Criteria queries! This way you can add custom order fields AND custom order directions.

In short words, it is simply done by using the AddOrder method of an ISession object. It takes as a parameter the field (custom provided by you), and has got two methods (Asc() and Desc()) for creating an Order object. The rest should be self-explanatory.

Here is how it works:

Criteria query using custom field and sort order

Simple, isn’t it? One more advantage is, that this way you are a bit more independent from DB-vendor-specific sorting possibilities. nhibernate will take care of creating the correct SQL syntax, no matter what the underlying DB is (as long as it’s supported, of course).

The nhibernate version I used for these tests is 1.2.

Stay tuned to the next time!

Martin

Read Full Post »