Tuesday, June 30, 2009

How to Perform a Database-independent Databind with UDLA Framework

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.

Search_Form.png

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:


See full detail: http://www.codeproject.com/KB/database/UDBASample.aspx

No comments: