This article presents some of my private work as a software developer. This is mainly about the so called ‘back-end‘ which entails the enabling of interaction between data models in the memory of a computer and the database. Initially these projects are for personal use like for application which support software development and database design, but also for example for forum software. When these projects have matured and are finished I can offer these projects as products so that other developers can benefit from working with these universal database components in a universal, uniform manner. This is however not the main goal; these are still private projects.
UDO.NET is a software layer which facilitates direct database access in such way that every database brand that supports SQL can be used without having to make changes in the application level which accesses the database through this layer. Adding support for a database brand through this layer in a documented, defined manner is enough to make compatible with this database any application using this framework. This way, software can be rolled out at companies regardless of a database brand which they may be using already without a change to a line of code. Below you will find a number of images depicting the development environment.
In the background UDO.NET works together with the ADO.NET driver components of the respective database brand. Within the application one doesn’t have to use or access these and only needs to use the UDO.NET components. This means that no further references to assemblies of the database driver of a given database brand have to be added. UDO.NET will load the appropriate assemblies dynamically. The SQL commands will still use the dialect of the respective database brand. When one connects to Microsoft SQL Server, the SQL commands have to be of the dialect for this database. When one connects to Oracle, one has to use the SQL dialect for Oracle. Thus, all database specific functionalities are still available like normal.
Then how is it possible, despite of this, that one would not have to adapt application code to the dialect of the relevant database vendor? That is because of the second primary part of the UDO.NET framework: the SQL Object Model. This is an object model which models the SQL language. UDO.NET translates this model into the appropriate SQL dialect that belongs to the connected database. Because this concerns objects, one can use Visual Studio ‘Intellisense’ with the composition of SQL commands using these objects. The image with the UDO.NET section already shows this. In this image the code file responsible for the specific translation to Microsoft Server 2016: SqlServer2016Dialect.cs at the bottom inside the ‘Solution Explorer’ to the right. The image below shows a test SQL command which has been clicked together with the SQL object model and shows how it will look like after translation. Note: This is a test model and does not represent a functionally sensible SQL statement. It is a feature demonstration.
The SQL object model provides a huge advantage when SQL statements have to be constructed dynamically. No longer one has to perform complex string operations to formulate commands dynamically, and for each different database at that. All one has to do now is to click together an object model dynamically to get the desired SQL command which will be correctly translated for each specific database brand supported. This dynamic construction is exactly what the following project does:
The ‘Entities’ software layer is a library of components which make it possible to work with various kinds of data and fetch these from as well as save back to the database. One can work with data through a classic object oriented fashion. Think of data like customer relations, their orders, product and article list, financial data and so much more. These kinds of data in jargon are called ‘entities’ or ‘business objects’. When one works with this kind of data, as a developer one wouldn’t really want to be concerning with details of the actual database layer such as formulating the correct SQL commands.
One wants to work with this data like any other software object and after modifying, adding or deleting of these object, commit these modifications to the database in one go. That is what this software layer does. The following image for example shows a ‘Contact’ object. It is a rather slim representation of generic customer data. No trace of SQL is to be found. All that is specified are the properties of the object like the ‘Name’ field, together with its field size in characters.
Also the object itself is marked to instruct that it corresponds to a database table, with the same name as the object (class). Furthermore it shows that the primary key for each contact object is generated by the database and that the system must use temporary keys until they are committed to the database. This helps reducing the waste of ID values when new objects are cancelled. Because this component layer makes use of UDO.NET and the SQL Object Model, this layer will automatically be compatible with any database supported by the UDO.NET layer.