How to Perform a Database-independent Databind with UDLA Framework

• Download demo project - 558.09 KB

Introduction

In this article, we will explain how to perform data access using the UDLA framework. In practice, we will show how to fetch data from a database and how to perform simple queries to insert, update or delete rows.

Background

When we build a software application, first of all we usually design the database. That is the bottom layer of the application. After that, we write all the other levels, until we reach the user interface level. Most of the development is based on database choice. It shouldn't be, but in lot of cases it is. Most programmers fix a database type (i.e. Microsoft Access, SQL Server, Postgres, Oracle, MySql, etc.) and they develop all the applications based on this choice. This means that we cannot change database type after, and that we cannot reuse most of the code in new projects (if we will have different database types). It's true that there are a lot of O\R Mapper and code generators that write code for us and they make it easy to build and rebuild the database access layer. In this context, the UDLA framework tries to use a different approach. With UDLA, you don't mind what is the database you are using, because it offers a uniformed database access criteria. The acronym UDLA stands for Uniformed Database Layer Access and remarks this feature. The UDLA framework works at low level and manages connections, data fetch, command execution. All the database elements are mapped by objects called “entities”. For example, we have SelectEntity for a select statement, or UpdateEntity for an update command... All those objects are self-executable: the select entity executes itself and returns the result. In this way, we don't need to write more SQL queries. SQL queries may vary from one database to another, and they cannot be reused. However, those entities do not depend on the database and we don't need the specific SQL used by the database.

Using the Code

In this section, there is a brief description of how to use the UDLA framework. We will list all the most important features of the UDLA framework. First of all, we take care of a data fetch. We use a SQL statement to retrieve all the products from the product table of NorthWind database that have a price greater than $5. Using ADO, we should write a SQL statement, filling it with the right values of parameters and use some object (DataReader or DataAdapter) to fetch the data. In fact, the syntax of SQL statement may vary from one database to another, for example the delimiter for field names or string can be different. Using this library, we don't need to explicit any SQL statement. Every query is represented by an instance of a class, called “Entity”. For example a select query is mapped in a SelectEntity object. We add all the fields we need (if not specified, all are selected), we specify all the constraints to perform data cutting and data filtering, and also some ordering clauses. When this object is fully filled, we pass it to a DatabaseManager object to be executed. DatabaseManager is database-independent: when we configure it, we can specify what kind of database will be used and the connection string. So all we need to execute this select is call a static method of DatabaseManager:

//Fetch Data and Fill Table
SelectEntity selectData = new SelectEntity();
selectData.Table.Name = "Products";
selectData.WhereClauses.AddFilter("UnitPrice", 
	this.numericUpDown1.Value, SqlComparers.EqualMoreThan);
this.dataGridView1.DataSource = dataManager.ReadDataTable(selectData);

The DatabaseManager object manages the connection.

So we can list all the entities in this framework:

• SelectEntity
• InsertEntity
• DeleteEntity
• UpdateEntity

The use of these other objects is analogous to the selectEntity sample. In the bottom rows of the code above, we explained all about the usage of commands and queries, but nothing about the DatabaseManager object. This is because the use of that class is very simple. DatabaseManager is a class that performs operation on the database. The only thing we had to do is initialize it on the application startup. We need to give it only few objects, which contain the database and connection information. An important notice is that this initialization is the only part of the application that may change from a database to another. For each kind of known database, we have an object called XXXConnectionInfoEntity, where XXX stands for the database name. For example if you use a Microsoft Access database, you will create a new MSAccessConnectionInfoEntity. All those classes inherit the interface IconnectionInfoEntity and implement the methods that uniforms the database access. The DatabaseManager contains an object that keeps track about connection, another that allows data fetch and another one is used for command execution. For each database type, there is one of these that inherits a shared interface. In other words, the ConnectionInfoEntity gives information about the selected database. So you can easily switch from one database to another. You can also share the same query with a different database.

Points of Interest

With UDLA, you can:

• Execute data fetch and any kind of command
• Use the same approach for all the projects
• Reuse the same code for different databases
• Disassociate DatabaseLayer from the application