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!
673f0b30-4f19-4e7c-943b-2668d6947c4f|8|4.0