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.
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 (
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
SelectEntity selectData = new SelectEntity();
selectData.Table.Name = "Products";
this.dataGridView1.DataSource = dataManager.ReadDataTable(selectData);
DatabaseManager object manages the connection.
So we can list all the entities in this framework:
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
DatabaseLayer from the application