Tuesday, May 31, 2016

Code Snippet: Set default database schema in Nhibernate SessionFactory

Be The First To Comment
1:  //Setup oracle database connection and connection pool  
2:    public class OracleConnectionManager  
3:    {  
4:      private static string _userName;  
5:      private static string _password;  
6:    
7:      public OracleConnectionManager(string userName, string password)  
8:      {  
9:        _userName = userName;  
10:        _password = password;  
11:      }  
12:    
13:      private static ISessionFactory _sessionFactory;  
14:      private static ISessionFactory SessionFactory { get  
15:        {  
16:          if(_sessionFactory == null)  
17:          {  
18:            var connectionString = DatabaseConnections.GetOracleConnectionsString(_userName,_password);  
19:              
20:            //Nhibernate Oracle database configuration  
21:            var configuration = new Configuration();  
22:            configuration.DataBaseIntegration(db =>  
23:            {  
24:              db.ConnectionProvider<NHibernate.Connection.DriverConnectionProvider>();  
25:              db.Dialect<NHibernate.Dialect.Oracle10gDialect>();  
26:              db.Driver<NHibernate.Driver.OracleDataClientDriver>();  
27:              db.ConnectionString = connectionString;  
28:             });  
29:    
30:            // Setup default schema  
31:            configuration.SetProperty(Environment.DefaultSchema, "DB_SCHEMA_NAME");  

Wednesday, January 6, 2016

Nhibernate criteria API - select from multiple tables

Be The First To Comment
If you ever worked with relational databases then higher chances are you already worked with sub queries. Some time while working with Nhibernate you get yourself into some situation where you need sub query to do some lifting for you. In NHibernate criteria API you can do the sub query usingDetachedCriteria and Subqueries classes. As the name suggests DetachedCriteria helps you define your sub query criteria while Subqueries class help you create sub query criterion object using detached criteria object.

In order to demonstrate sub query implementation lets consider an example. Suppose we have three entities Order, OrderItem and Product where one order can more have more then one order items while one order item may be linked to a product. If we need to do a query where we need to list all those order items which are linked to products and have price greater then $100. Also the products must be the ones marked as special in system. The SQL for such query may be something like below

SELECT * FROM OrderItem AS OI 
WHERE OI.Price > 100 AND OI.ProductId IN ( SELECT P.ProductId FROM Product WHERE P.IsSpecial = 1 )
 

© 2011 GIS and Remote Sensing Tools, Tips and more .. ToS | Privacy Policy | Sitemap

About Me