Orasis Software Blog

Orasis Mapping Studio eliminates NHibernate performance problems

adnan — Tue, 05 May 2009 07:13:00 -1100

Writing SQL queries is something NHibernate eliminates for the developers when writing data access code. Complex data models and Hibernate (HQL) queries can often lead to slow performing generated SQL resulting in overall database access performance problems. Also, if the volume of data returned is large, the reflection operations which load data into the objects can add performance lag during data loading compared to instantiating objects using the new statement and assigning data to properties natively in C#.

Orasis Mapping Studio uses SQL to build your data access and can map the queries directly to the same Domain objects that NHibernate maps to. To eliminate the performance problems, we can take the NHibernate queries, capture the SQL, and optimize them to a pure ADO.NET method returning a list of NHibernate Domain objects. All this can be done through the Orasis Mapping Studio.

Capturing the SQL

You can capture the generated SQL in the NHibernate generated log when using the

<property name="show_sql">true</property>

configuration element in the NHibernate config file, either through the console or log file when running the application.

Optimizing the Query

Once you have captured the SQL, launch the Orasis Mapping Studio and open or create a mapping project connecting to your database. Then launch the Query Designer through the Tools, Query Designer menu.

Paste the query into the query designer and let it parse it. You will be able to see your query visually now and execute it with the parameters it may have to see its execution time. You can then optimize the query by minimizing sub queries and writing efficient joins.

Map to the existing NHibernate Domain Object

Once you are satisfied with the query, click the Create mapping button. This will walk you through mapping your query to the object that you want it mapped to. In the third page of the wizard, where the Type Selector appears, select the Import type tab and select the Assembly in which your Domain objects are and then select the Domain object to map to. For a more in depth walk through of creating mappings read here: http://www.orasissoftware.com/blog/post/2009/03/28/Smart-Code-Generation-for-Data-Access-Using-Orasis-Mapping-Studio.aspx

Once the mapping is complete and the project is built you will now have a generated method built with pure ADO.NET to execute the query and return you the Domain objects in a list, with the parameters necessary to execute the query. The method will be generated with very efficient ADO.NET code to execute your query and load your objects.

Your Data Access Code will now perform much faster, the SQL will be well tested and the NHibernate domain object that you have built your application will be reused. It will be all manageable through the Orasis Mapping Studio project file for future modifications to your query, object and mappings.

Comparison of Linq to Sql and Orasis Generated Data Access Code

adnan — Sun, 26 Apr 2009 09:19:18 -1100

Lets look at a brief code by code comparison of how to perform different data access operations using the Linq to Sql framework compared to using the Orasis Generated data access layer from a mapping project.

Download the NorthwindDataAccess project file and open it with the Orasis Mapping Studio. Edit the database connection string by right clicking on the Database node and changing the connection properties to point to your Northwind database.

Northwind Mapping Project File

The Linq to SQL Context is setup by Connecting to the Northwind database and adding the Product table to the dbml designer in Visual Studio. We will name our context NorthwindDataContext.

Let us see some examples of data access operations for the Products table from Linq to Sql and from the Data Access Code generated by the Orasis Mapping Studio.

Selecting a Product from the Northwind Database:

Linq to SQL: (C#)

NorthwindDataContext nwContext = new NorthwindDataContext(); 
var products = from p in db.Products where p.ProductId = 1
               select p;

Orasis Generated Data Access Layer:

NorthwindDataAccess nwDal = new NorthwindDataAccess(); 
IList<Product> productsList = nwDal.GetProductbyId(1);

The SQL query in the GetProductbyID mapping is :

Select * From Products
	Where Products.ProductID = @productId

The query was written and mapped to the Product object using the Mapping Studio’s mapping wizard.

The result is strongly typed, and the parameters are clearly defined. Also, the caller of the Data Access Library does not need to see the query, he just calls the method which gets him the Objects.

Updating a Product:

The code below shows both approaches of how to retrieve a product by its ID and update its Name and Unit Price.

Linq to SQL: (C#);

NorthwindDataContext nwContext = new NorthwindDataContext ();
Product product = nwContext.Products.Single(p => p.ProductId == 1);

product.ProductName = "New Name";
product.UnitPrice = 23;

nwContext.SubmitChanges();

Orasis Generated Data Access Layer:

NorthwindDataAccess nwDal = new NorthwindDataAccess(); 
Product product = nwDal.GetProductbyId(1)[0]; 

product.Name = "New Name"; 
product.UnitPrice = 23; 

int rowsAffected = nwDal.UpdateProducts(new List<Product>{ product });

The query inside the UpdateProducts method is:

Update dbo.Products set ProductName = @ProductName,
UnitPrice = @UnitPrice
where ProductID = @ProductId

Inserting a Product

Linq to SQL: (C#)

NorthwindDataContext nwContext = new NorthwindDataContext (); 

Product product1 = new Product();
product1.ProductName = "New Product1";
product1.UnitPrice = 35;
product.Category = 1;

nwContext.Products.Add(product1);
nwContext.SubmitChanges();

Orasis Generated Data Access Layer:

NorthwindDataAccess nwDal = new NorthwindDataAccess(); 

Product product = new Product(); 
product.ProductName = "New Product";
product.UnitPrice = 23; 
product.Category = 1;

int rowsAffected = nwDal.InsertProducts(new List<Product>{ product });

The insert Query inside the mapping is :

Insert into dbo.Products(ProductName,UnitPrice,CategoryID) values (@ProductName,@UnitPrice,@CategoryID)

Deleting a Product:

In the code below we will delete a product with the name “New Product”.

Linq to SQL: (C#)

NorthwindDataContext nwContext = new NorthwindDataContext ();

Product productToDelete = nwContext.Products.Single(p => p.ProductName == "New Product");
nwContext.Products.DeleteOnSubmit(productToDelete );

nwContext.SubmitChanges();

Calling the Orasis Generated Data Access Layer:

NorthwindDataAccess nwDal = new NorthwindDataAccess(); 

Product product = new Product(); 
product.ProductName = "New Product"; 

int rowsAffected = nwDal.DeleteProductByName(new List<Product>{ product });

The Query inside the DeleteProductByName Mapping is :

Delete from dbo.Products where Products.ProductName = @ProductName

Calling a Stored Procedure

The code below demonstrates calling a stored procedure that we created in the Database to return products by Category called ‘GetProductsByCategory’.

Linq to SQL: (C#)

NorthwindDataContext nwContext = new NorthwindDataContext ();

var products = nwContext.GetProductsByCategory(1);

foreach (Product product in products)
{
	System.Console.WriteLine("Product Name:" + product.ProductName);
}

Orasis Generated Data Access Layer with a Mapping to the same Stored Procedure:

NorthwindDataAccess nwDal = new NorthwindDataAccess(); 

IList<Product> products = mdDal.GetProductsByCategory(1);

foreach (Product product in products) 
{ 
	System.Console.WriteLine("Product Name:" + product.ProductName); 
}

This concludes our side by side code comparison of accessing our Northwind database through Linq to SQL, compared to Mapping the Database through the Orasis Mapping Studio’s mappings. The end results are same for accessing the database through Objects.

SQL Based O/R Mapping?

adnan — Sat, 11 Apr 2009 05:04:47 -1100

After reviewing all the major ORM technologies out there and participating in a project that was a classic ORM nightmare, I have come to the following conclusion: ORM is a good acronym but not a good concept. But, I must give credit where credit is due; ORMs have moved us forward in thinking of Objects and making our applications more Object Oriented. Gone are the days when binding a grid to a DataTable or some other data structure directly from the data provider was considered a viable solution.

Here is the bad part about ORMs, and we must learn from the negatives to come up with a positive solution. They couple or glue your Objects to your Database Schema in some form, directly or indirectly. How about if we mapped SQL Results to Objects instead of mapping relational schemas to Objects? One couples your Relational Schema to your Objects while the other maps to SQL which is the natural language of any Relational Database. In this way the abstraction is at a higher level and not at the database's schema level.

The reason why ORMs are problematic is that they introduce their own query languages that query your Objects instead of tables. They say they hide the "complexity" of SQL, but in reality it's the only way you can get objects out of an ORM. It's a simple fact that Objects are not designed to be queried. They are not inherently relational structures, they are hierarchical. Database Tables, views, etc are all relational tuples designed for storage and queries. So which one would you query against?

Aside from the query, ORMs let you map data directly into objects without having to write mapping code. That is a great convenience, I admit. In the process they take care of type conversions, null checks, nullable types, data lengths, etc. all which are quite painful to write for developers. Now remember, that you do take a performance hit because ORMs use reflection to do this work for you which is slower than native object instantiation and population, but for most cases it is negligible.

So the lesson learnt is that if SQL queries were brought back into the picture and somehow the mapping of results, and parameters from the Query to the Objects was automated and managed and the Objects were allowed to be decoupled from the Database that would be a better solution than an ORM.

The only problem is that you would need a tool to manage all this work and possibly generate the code for mapping each query to an Object.

So why not try a SQL to Object mapping solution? It seems, at least to me, to be a more viable solution than using an ORM solution. I'm sure many of you may have already tried this. Let me know your degree of success if you did.

You can also try the Orasis Mapping Studio which was built from the ground up to do SQL based O/R Mapping at http://www.orasissoftware.com/download.aspx

ADO.Net is back

neophytos — Wed, 08 Apr 2009 17:14:21 -1100

ADO.Net revived. Why now visual crafting of your data access tier seems the right solution. Start mapping not coding!

Craft your database access API visually.

Visual creation of your database access tier seems more attractive than ever today. Simply drag, drop, click to auto generate your database access tier. Test your actual data access code without writing either the data access code or the tests. Orasis Mapping Studio 2009 makes it all possible.

Utilize the power of ADO.Net but do not write it.

Writing ADO.Net code can be a tedious job but given the right tool to auto generate it makes it a comeback. The hassle of writing ADO.Net and creating queries by hand is nullified with one comprehensive solution.

Use an ORM but not with the drawbacks.

Yes an ORM solution has a lot of drawbacks. It is time to face reality. Orasis allows you to ORM but in a more easy way. Visually map your queries to your object graphs. Simple as this.

Object Query languages; LINQ-SQL, HQL Are they easy to use?

ORM implementations of different flavors that introduce object query languages and auto generation of not so optimized SQL underneath the hood can cause development time delays and substantial performance issues. This is a proven fact and developers are starting to realize that maybe they should be looking somewhere else. Data access plays a vital role in business applications; and performance is one of the key factors to consider in any data-intensive application. There are many factors that can affect data-access performance negatively, such as network load, load on the database server, un-optimized SQL, and so on. Apart from these, there are other factors to consider with respect to the various data-access operations that most applications perform, such as opening and closing a connection, fetching a result set, blob access, and metadata retrieval, utilization of reflection/introspection, need for distributed object caching, etc. First of all, I have never seen a real enterprise system that involves fair amount of database access going into production without optimization. During regression testing, different operations show their true colors especially under load. That is when engineering teams jump in and try to optimize SQL and data access code. If the code was written by utilizing some object query language, the question remains on how you optimize performance since the SQL is generated dynamically by the ORM engine.

ORMs always perform faster?

Some developers support that ORMs perform faster even though:

ADO.Net is the most direct access to the database.

ORMs usually need an Object cache to offer compatible performance.

Ignore the fact that reflection/introspection versus direct access of model object properties is very expensive.

The illusion of avoiding SQL.

So why is this happening? It seems that the fact that developers think they do not have to deal with SQL makes ORMs so attractive that all of the overheads associated with them simply vanish. The truth is that object query language constructs are not easy to understand and use either, so developers end up writing the query but in a different language. Looking at a fairly involved object query code snippet one realizes that the object API is not for the average developer. This is where ROI kicks in and on a large scale system the time simply adds up to a lot of money and frustration. A related article that explains the ROI can be accessed at http://forums.orasissoftware.com/comments.php?DiscussionID=7&page=1#Item_0

Classes to table Mappings. Is this what we want?

Even though most ORM implementations force a one to one mapping of database tables to Classes, it seems that not many people object this practice. So let me understand this; we are using an object query language to abstract us from the database but our model object match the database tables 100%?

Since when my business object model should resemble my physical storage? Isn’t that a disadvantage? Should I waste time talking why is a bad practice? I guess not since it is a fact.

How to Choose a Technology for your Data Access tier

Adnan — Tue, 07 Apr 2009 07:40:00 -1100

Should you choose an ORM implementation or simply allow a tool to craft your data services to your actual and immediate needs? Do you need both options in your projects? Can an ORM implementation satisfy the performance requirements, the ease of use and ease of changes? Can you really avoid SQL at no cost? Does an object query language make our lives easier or harder? Does any ORM implementation address the Object-Relational Impedance Mismatch?

Choosing a technology for your database access layer seems to be a challenging decision for many projects today. There are many advocates of different ORM implementations and object query languages of some sort. Some claim one object query language is simpler than the other; some ORM implementations support all the major object query languages and some developers write their own abstraction layer to the object query language of the specific ORM implementation that they have chosen.

To really benchmark an ORM implementation is extremely challenging since different schemas carry their own complexities and application requirements. In terms of tuning the queries, the choices appear to be quite limited with ORM implementations and require “expert knowledge” to do it right if it can be done at all.
Multiple times, the compromise of multiple fetches to generate in memory domain structures from multiple domain objects deprecates performance but at least it gets the data. In reality there is no one seemingly easy way to judge performance.

We can compare an ORM’s performance against the equivalent SQL and may turn out that this test can be misleading. Some important performance optimizations (for example, the transaction-level cache) can actually reduce performance for the kind of very trivial benchmarks that people typically write. This is more a problem of the triviality of the benchmarks than anything else. Problems that affect ORM performance in nontrivial use cases may not even exist. The biggest problems really come from what we call the "problem of graph navigation", where the pattern of data access used by an object-oriented application is a fundamentally inefficient way to access relational data. A lot of times the data required are not a one to one mapping of domain objects to tables; multiple columns from different tables can make up a model object. In that case an ORM implementation is relatively useless.

How about if we have an integrated development studio that allows us to build our own data services layer, that would allow us to edit/modify/benchmark all of our database access layer in one place with no ORM engine code underneath but only clean vendor-independent code with a choice of different drivers? If a hand crafted auto code generated data services solution is available then this is something that can be considered?

Sometimes the problem is sort of cultural. Some developers come to using ORMs because they are uncomfortable with SQL and relational databases. ORM architects state that this is the wrong reason to use an ORM. Being comfortable with relational databases and SQL are requirements for using an ORM. One of the major reasons that databases succeeded is the pure power of SQL. How about if we could harvest the power of SQL, ADO.Net , the OOP concept and bring them together in a powerful visual mapping studio that allows automatic generation of data services code based on the visual mappings between database columns and objects? Is that a compromise or an improvement? Some debate that objects to query syntax is very complicated and they prefer to deal directly with SQL. It is also a fact that database administrators can participate in the optimization of SQL if they had easy access to it. The concept of a Mapping is a one compilation unit that is composed of two sub units; one being the SQL statement and the other the model object that translates into a method in the data service class. If both of these units are edited by their own visual editors then tuning should be a breeze. Mappings are part of a project and the project compiles to a data access library. In this case the data tier grows based on the application requirements and the data services themselves can be distributed in different projects. The application services have a choice to link to the data tier libraries that they need. Simply reference the libraries and access your data. If we have a choice to custom create and fully control the data services in terms of maintenance and growth, modularity and abstraction, benchmarking and round trip engineering, in an IDE that finally focuses only on all of these requirements of the data tier, should we consider it? Even if we choose an ORM that is not custom, could we use this in addition to that?

Orasis software introduces the custom IDE data services crafter, the Orasis Mapping Studio 2009 and it provides the choice to utilize the better of the two worlds. Relational databases and class hierarchies should not relate as the ORM implementation specifies but they should relate on what the business requirements are. True mapping of objects to SQL should address object graphs at any depth level. If an object graph has contained members of abstract classes and interfaces, the mapping process should allow implementation classes of these instances to be replaced. Even contained members that are instantiable classes should be allowed to be replaced by derived ones. Data architects and developers can now orchestrate the development of the data tier layer and guarantee performance. The benchmarking features of the IDE on the data services code and the SQL perform the ultimate test. The ability to drill into any mapping through a visual editor to modify/enhance it reveals a powerful feature that needs to be taken into serious consideration. No system goes into production before it is tuned.

Can a traditional ORM technology that generates model objects that map one to one to a database schema satisfy all the needs for data access in a system? Or even a more enhanced approach of an ORM like Entity framework that still shares the majority of the features of a traditional ORM can satisfy all of the needs of a true isolated database tier that targets performance in the enterprise?
The flexibility to map easily database fields to object properties in object graphs, auto generate custom data services layer code with no introspection code and validating it's performance without having to write tedious testing code for it, in now an option.

Develop your data access layer using safe parameterized queries

Adnan — Sat, 04 Apr 2009 06:02:07 -1100

One of the biggest concerns I face as a developer when writing SQL queries in my application is how to make them safe so that they are not prone to SQL injection attacks and at the same time they execute efficiently. Most developers go the route of putting all their queries as stored procedures in the database to handle the security and performance concern. Although this is a viable solution, it splits your application code in multiple places making it difficult to read, maintain and debug.

Also I want to avoid writing the tedious ADO.NET code to create SQL Commands, add the parameters, set the right data types, check for nulls set nulls, etc.

We can achieve all this with a high level of security, flexibility and maintainability by writing parameterized SQL queries and letting the Orasis Mapping Studio handle the mapping of parameters and generating data access code that executes the parameterized query and maps the results to our objects.

Parameterized Query

A parameterized query has SQL parameters embedded in the query itself, and the values of the parameters in the queries are set using the ADO.NET API to set parameters on the Command object. The parameterized query has a fixed execution plan therefore the database is able to cache the execution plan and it runs faster the next time the same query is executed. They are the safer, better, faster alternative to dynamic queries, that developers build by concatenating strings to the query to give it the dynamic effect.

Example query:

select * from users where UserName = @userName

Optional parameters

Parameterized queries can also be written in such a way that allows certain parameters to be optional so that the database can ignore them if there is a null value provided for them. Here is an example of a parameterized query that has an optional parameter which will get ignored if there is a null value assigned to it.

select * from users where username = @username and (usertype = @usertype or @usertype is null)

The (usertype = @usertype or @usertype is null) clause will resolve to ‘true’ if the @usertype parameter is set to null and therefore the query will in effect ignore the parameter.

This query when mapped through the Orasis Mapping Studio will automatically have the parameters set to a null value if the user passes in null to the .NET method that gets generated.

Database Differences

Different database use different delimiters for handling parameters such as ‘@’ for Microsoft SQL Server and Access, ‘:’ for Oracle, and ‘@’ for MySql. The Orasis Mapping Studio is able to identify the right parameter for your database provider and parse the queries accordingly so that the code is generated accordingly and the values are set correctly.

Example

Let’s look at a sample query with parameters and map it to an object and see how the Orasis Mapping Studio generates code for the query. We will then run the query with different values and see the results we get.

Once the method and assembly are generated we can call them in our application using the following test method and see the results in our application.

public void TestParameterizedQueryMethod()
{

	//Instantiate the generated DAL Class        
	SqlServerProject.DataAccess.AdventureDBAccess dataAccess = new SqlServerProject.DataAccess.AdventureDBAccess();
	        
	//Execute the method with parameter value 1        
	IList<Employee> resultsList = dataAccess.EmployeeQueryMapping(1);
	System.Console.WriteLine("Results with value {0}: {1}",1, resultsList.Count);

	//Execute the same method with a null value for the parameter        
	resultsList = dataAccess.EmployeeQueryMapping(null);
	System.Console.WriteLine("Results with value {0}: {1}","null", resultsList.Count);

}

This concludes showing the how easy it is to use Parameterized queries and map them with the Orasis Mapping Studio. The same can be done with different databases by using the correct parameter delimiter.

How to update a table from an object using the Orasis Mapping Studio

adnan — Sat, 28 Mar 2009 16:32:00 -1100

This video demonstrates how to create a Persist mapping to update a table using the Orasis Mapping Studio’s persist mapping and data access code generation mechanism.

Once the code is generated from the Studio and the assembly is successfully built, it can be invoked using the following test method:

 	
/// <summary>       
/// Call the Orasis Generated Update Method to Update the Product table        
/// </summary>        
public void UpdateProduct()        
{            
	IList<ProductUpdater> productUpdateList = new List<ProductUpdater>();            
	OrasisSoftware.Sample.DataAccess.DataAccessService dal = new OrasisSoftware.Sample.DataAccess.DataAccessService();            
	ProductUpdater product1 = new ProductUpdater();            
	product1.productid = 1;            
	product1.QuantityPerUnit = "12 - 14 mg";            
	product1.UnitPrice = 20;            
	product1.UnitsInStock = 1;            	
	product1.UnitsOnOrder = 10;            	
	productUpdateList.Add(product1);            

	int rowsAffected = dal.UpdateProductById(productUpdateList);        
}

This concludes creating a Persist mapping and generating code to perform an update on a table. The same can be done for Inserting and Deleting by creating a Persist mapping and choosing the right query type.

Technorati Tags: mapping,ado .net,sql,query,persist,object,orasis,mapping studio,insert,update,delete,c#,.net

Smart Code Generation for Data Access Using Orasis Mapping Studio

Adnan — Sat, 28 Mar 2009 03:53:00 -1100

Learn how generate data access code the smart way using mappings and smart code generation.

Download Link: http://www.orasissoftware.com/download.aspx

Introduction

Populating objects with data from a database is something almost every application needs to do. Let's say we have a screen in which we want to populate a grid with a given SQL Query using custom object binding of a given object type. What we want to do is to be able to generate a smart Data Access Layer that will do all the work for us and return us the objects populated with data from our query and nothing more.

To achieve this in a matter of minutes we will use the Orasis Mapping Studio and its Smart Mapping and Code Generation technology. The code that will be generated will only focus on populating the Model objects and returning them in a list, taking in any parameters that are needed. We will show you how the code is generated and then tested and built into an assembly all within the Orasis Mapping Studio that can be simply exported to Microsoft Visual Studio with a click of a button.

Example Scenario:

Let us take an industry standard query that requires complex joins, sub queries, unions and a few input parameters. This query retrieves a bill of materials for a given product for a given date:

With BOM_cte(ProductAssemblyID, ComponentID, ComponentDesc, PerAssemblyQty,
    StandardCost, ListPrice, BOMLevel, RecursionLevel) As (Select
      b.ProductAssemblyID, b.ComponentID, p.Name, b.PerAssemblyQty,
      p.StandardCost, p.ListPrice, b.BOMLevel, 0
    From Production.BillOfMaterials b Inner Join
      Production.Product p On b.ComponentID = p.ProductID
    Where b.ProductAssemblyID = @StartProductID And (@CheckDate >= b.StartDate
      And @CheckDate <= IsNull(b.EndDate, @CheckDate))
    Union All
    Select b.ProductAssemblyID, b.ComponentID, p.Name, b.PerAssemblyQty,
      p.StandardCost, p.ListPrice, b.BOMLevel, RecursionLevel + 1
    From BOM_cte cte Inner Join
      Production.BillOfMaterials b On b.ProductAssemblyID = cte.ComponentID
      Inner Join
      Production.Product p On b.ComponentID = p.ProductID
    Where (@CheckDate >= b.StartDate And @CheckDate <= IsNull(b.EndDate,
      @CheckDate)))
Select b.ProductAssemblyID, b.ComponentID, b.ComponentDesc,
  Sum(b.PerAssemblyQty) As TotalQuantity, b.StandardCost, b.ListPrice,
  b.BOMLevel, b.RecursionLevel
From BOM_cte b
Group By b.ProductAssemblyID, b.ComponentID, b.ComponentDesc, b.StandardCost,
  b.ListPrice, b.BOMLevel, b.RecursionLevel
Order By b.BOMLevel, b.ProductAssemblyID, b.ComponentID

The we want to populate the results into a model object called BillOfMaterialModel which has the following properties:

The code that needs to get generated to achieve this mapping will perform the work of connecting to the database, executing the query with any of the parameters passed. Then retrieving the results and mapping them into the model object's properties. While doing that it will make sure all the implicit and explicit conversions are done accurately to match the data types. Then it will return the final list of objects in an IList interface. All this will be achieved as a generated partial class with virtual methods that can be overridden with no third party dependencies to any library or any configuration and reflection operations that cost us in performance.

Let's first look at the end result we want and look at the code that will do the mapping:

using AdventureWorks.Objects;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace AdventureWorks.DataAccess
{
    
    
    /// <summary>
    /// 
    /// </summary>
    public partial class ProductsDALClass
    {
        
        private String _connectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security" +
            "=True";
        
        public virtual String ConnectionString
        {
            get
            {
                return this._connectionString;
            }
            set
            {
                this._connectionString = value;
            }
        }
        
        /// <summary>
        /// This method returns the Bill Of Materials for a given ProductId and a check date.
        /// </summary>
        public IList<BillOfMaterialsModel> GetBillOfMaterials(System.Nullable<int> StartProductID, System.Nullable<System.DateTime> CheckDate)
        {
            System.Data.IDbCommand command;
            IDataReader dataReader = null;
            List<BillOfMaterialsModel> returnList = new List<BillOfMaterialsModel>();
            IDbConnection connection = new System.Data.SqlClient.SqlConnection(this.ConnectionString);
            try
            {
                connection.Open();
                command = connection.CreateCommand();
		command.CommandText = @"With BOM_cte(ProductAssemblyID, ComponentID, ComponentDesc, PerAssemblyQty,
		    StandardCost, ListPrice, BOMLevel, RecursionLevel) As (Select
		      b.ProductAssemblyID, b.ComponentID, p.Name, b.PerAssemblyQty,
		      p.StandardCost, p.ListPrice, b.BOMLevel, 0
		    From Production.BillOfMaterials b Inner Join
		      Production.Product p On b.ComponentID = p.ProductID
		    Where b.ProductAssemblyID = @StartProductID And @CheckDate >= b.StartDate
		      And @CheckDate <= ISNULL(b.EndDate, @CheckDate)
		    Union All
		    Select b.ProductAssemblyID, b.ComponentID, p.Name, b.PerAssemblyQty,
		      p.StandardCost, p.ListPrice, b.BOMLevel, RecursionLevel + 1
		    From BOM_cte cte Inner Join
		      Production.BillOfMaterials b On b.ProductAssemblyID = cte.ComponentID
	            Inner Join
		      Production.Product p On b.ComponentID = p.ProductID
		    Where @CheckDate >= b.StartDate And @CheckDate <= ISNULL(b.EndDate,
		      @CheckDate))
	            Select b.ProductAssemblyID, b.ComponentID, b.ComponentDesc,
			  Sum(b.PerAssemblyQty) As TotalQuantity, b.StandardCost, b.ListPrice,
			  b.BOMLevel, b.RecursionLevel
		    From BOM_cte b
		    Group By b.ProductAssemblyID, b.ComponentID, b.ComponentDesc, b.StandardCost,
			  b.ListPrice, b.BOMLevel, b.RecursionLevel
		    Order By b.BOMLevel, b.ProductAssemblyID, b.ComponentID";

                System.Console.WriteLine("Executing Query: {0}", command.CommandText);
                IDbDataParameter param_StartProductID = command.CreateParameter();
                param_StartProductID.ParameterName = "StartProductID";
                if ((StartProductID == null))
                {
                    param_StartProductID.Value = DBNull.Value;
                }
                else
                {
                    param_StartProductID.Value = StartProductID;
                }
                command.Parameters.Add(param_StartProductID);
                IDbDataParameter param_CheckDate = command.CreateParameter();
                param_CheckDate.ParameterName = "CheckDate";
                if ((CheckDate == null))
                {
                    param_CheckDate.Value = DBNull.Value;
                }
                else
                {
                    param_CheckDate.Value = CheckDate;
                }
                command.Parameters.Add(param_CheckDate);
                dataReader = command.ExecuteReader();
                for (
                ; dataReader.Read(); 
                )
                {

                    BillOfMaterialsModel modelObj = new BillOfMaterialsModel();
                    if ((dataReader["ProductAssemblyID"].Equals(DBNull.Value) == false))
                    {
                        modelObj.ProductAssemblyID = ((int)(dataReader["ProductAssemblyID"]));
                    }
                    if ((dataReader["ComponentID"].Equals(DBNull.Value) == false))
                    {
                        modelObj.ComponentID = ((int)(dataReader["ComponentID"]));
                    }
                    if ((dataReader["ComponentDesc"].Equals(DBNull.Value) == false))
                    {
                        modelObj.ComponentDesc = ((string)(dataReader["ComponentDesc"]));
                    }
                    else
                    {
                        modelObj.ComponentDesc = null;
                    }
                    if ((dataReader["TotalQuantity"].Equals(DBNull.Value) == false))
                    {
                        modelObj.TotalQuantity = ((decimal)(dataReader["TotalQuantity"]));
                    }
                    if ((dataReader["StandardCost"].Equals(DBNull.Value) == false))
                    {
                        modelObj.StandardCost = ((decimal)(dataReader["StandardCost"]));
                    }
                    if ((dataReader["ListPrice"].Equals(DBNull.Value) == false))
                    {
                        modelObj.ListPrice = ((decimal)(dataReader["ListPrice"]));
                    }
                    if ((dataReader["BOMLevel"].Equals(DBNull.Value) == false))
                    {
                        modelObj.BOMLevel = ((short)(dataReader["BOMLevel"]));
                    }
                    if ((dataReader["RecursionLevel"].Equals(DBNull.Value) == false))
                    {
                        modelObj.RecursionLevel = ((int)(dataReader["RecursionLevel"]));
                    }
                    returnList.Add(modelObj);
                }
            }
            finally
            {
                connection.Close();
                ((System.IDisposable)(connection)).Dispose();
            }
            return returnList;
        }
    }
}

After this code is generated we will be able to simply call the method in our generated DAL library to get us the results in an object and show them in our grid.

Step by Step Walkthrough

We will begin by launching the Orasis Mapping Studio and connecting to our database.

Step 1: Start a Mapping

Then we will begin creating a mapping through the "Add Mapping" wizard. The first step is providing a name to identify our mapping and what type of mapping it is:

Step 2: Enter your SQL Query

Then we will enter our query or build it in the query designer, whichever suits the developer.

The query will be taken in and checked for any errors. Then we can also see the query in design mode to get an idea of the tables and relationships being used in the query:

Step 3: Select your Object

Then we will move to the next step in our mapping wizard where we select the object that needs to get populated from our object's Assembly.

Figure 8. Mapping Step 3: Selecting the Class to map the query result

Step 4: Map the Fields

Next we will go to the mapping screen which allows us to map the query's return columns into the Object's properties field by field.

Step 5: Set Code Options

In the final step we will specify the name of the method and the class that the code for this mapping will get generated into.

Once we are finished creating the mapping we can see the mapping get added to our project and we can see the generated code and classes all in the project explorer:

Step 6: Build your Output

That's it. We are done with mapping our query to our object. Now all we need to do is hit the Build button and the Orasis Mapping Studio takes all the mapping information and generates all the class files and code to get the data we need in the object specified. It also compiles the code into a .NET assembly that we can use in our UI project directly and call the methods. The output folder for the generated code looks like the following. It gets created next to the location of the project file for the Orasis Mapping Studio.

/Output/ProductDALClass.cs
/Bin/MySmartDal.dll

Unit Test

Before we actually call the method in our Application or UI, we want to make sure the code we generated executes correctly, bringing us the results we expect in the timing we need. To achieve that we can launch the Tester and execute the method we want to test. It will prompt us for the necessary parameters and show us the results with row counts and timings as following:

Using the Data Access Library in your Project

Now, we can simply open our Application project and call the generated MySmartDal.dll in our code and get the results shown in a grid with the following code behind a windows form:

using System;
using System.Windows.Forms;
using MySmartDAL.BillOfMaterials;

namespace ProductViewerSample
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void btnGetBillOfMaterials_Click(object sender, EventArgs e)
        {
            ProductsDALClass dalClass = new ProductsDALClass();
            int productId = int.Parse(this.textBox1.Text);

	    this.dataGridViewBOM.DataSource = dalClass.GetBillOfMaterials(productId, this.dateTimePicker1.Value);
        }
    }
}

Running your application

Running the application code and the data access generated code brings us the following result screen:

Exporting to Visual Studio

We can also export all the output code to a Visual Studio Project file and launch it in Visual Studio 2005 or 2008.

Clicking on the Launch Project in Visual Studio will open our sample project in Visual Studio with all the generated code files, projects and project references set up for you.

Conclusion

This concludes building a quick, efficient, object oriented, well abstracted data access layer that satisfies our business requirements in an automated, managed, and testable way in only a few minutes. The Mapping project can then be expanded to create mappings for different business requirements the application may have, each using a query, object and field mappings. All managed in one data access management and code generation studio.

You can download the Orasis Mapping Studio from http://www.orasissoftware.com. It has a full feature 30 day trial version with full support to get you started. Start building data access code the smart way!

Technorati Tags: orasis,.net mapping,code generation,sql,data access,dal,object,mapping studio,mapping