Introduction

Building specialized code generation tools is something development teams can do to enhance their productivity and reliability. It can be a tool that processes a domain specific language designed to effectively express information about a particular problem domain, or it can be a tool that generates a perfect hash function for a set of keywords, or a tool that pre-calculates a set of mathematical results for a number of input ranges – spitting out a large number of structures that maps the input to the result to avoid time consuming calculations at runtime.

the surgeon must be sole judge of the adequacy of the service available to him. He needs a toolsmith, responsible for ensuring this adequacy of the basic service and for constructing, maintaining, and upgrading special tools—mostly interactive computer services—needed by his team. Each team will need its own toolsmith, regardless of the excellence and reliability of any centrally provided service, for his job is to see to the tools needed or wanted by his surgeon, without regard to any other team's needs.

Tools that process metadata from a database server, are among the tools most often required by a development team – because we often wish to create more than just the mapping layer between a table, or set of tables, created by an existing commercial or open source ORM.

The primary purpose of DBTool is to generate code for a multi layered data access framework tailored to the requirements for an industrial management system. It does not create an IMS, but it can be used to significantly reduce the effort required to do so.

DBTool does most of what you would expect from an ORM tool, and allows us to specify that certain tables are to be processed quite differently from the ‘normal’ tables.

A table that links two other tables forming a many-to-many relationship can be marked as such.

The many side of a one-to-many relation can be marked as a timeseries, indicating that there is a current record, and records containing historical data. A table that is marked as a timeseries must have a unique key consisting of a reference to the one side of the one-to-many relation and a TIMESTAMP. DBTool will generate code that allows us to retrieve a record by passing a time that falls into the interval from the TIMESTAMP until the TIMESTAMP of the next record – allowing us to easily retrieve the current record at the time.

A table that links two other tables forming a many-to-many relationship can be marked as both a timeseries and as a many-to-many relation, indicating that we have a current relation, and records maintaining the history of relations between records.

A table can be marked as a tag table. A tag table must sit on the many side of a one-to-many relation with a table named ITEMS. The purpose of this construction is to attach tags, or rather properties, to an ITEM.

A table can be marked as a value table. A value table is a template for a table that will hold the values for one record in the tag table. The system will dynamically create on table for each row in the tag table. DBTool uses this information to create a separate tag type for each kind of table marked as a value table, adding the ability to attach tags of various types to an item in the ITEMS table. These dynamically generated tables will usually hold a very large number of records containing measurement data.

DBTool creates code that enables integration with message queuing systems without specifying a particular vendor. Message queuing systems, such as WebSphere MQ[^], are commonly regarded as the most reliable communication mechanism between components making up a distributed processing system.

Accessing the Metadata

DBTool allows you to browse the contents of an Oracle database, but it's certainly not a replacement for PL/SQL Developer[^] and other similar packages. You can also execute queries against the database.

DBTool is in itself an example of how to use the generated code, since it is implemented using Reader classes generated from various views in the SYS schema.

When DBTool is used to inspect column information, it displays two sets of information about the column. The first set is the information that IDataReader.GetSchemaTable[^] retrieves from the ADO.Net driver, while the rest is the column information retrieved directly from the Oracle database.

The information retrieved from GetSchemaTable is pretty useful when you are working with the Oracle.DataAccess assembly since the conversions performed by the driver aren't always what you expect.

ADO.NET column information

Oracle 'native' column information

The generated code

The code generated by DBTool can briefly be categorized into:

Classes common to both client and server code.

Interfaces such as IDataProvider, IDataObserver, and IDataContextProvider that specifies the operations that can be performed against the database.

Classes that are used to transfer data between WCF clients and servers.

Classes that can be used to query and update data using a message queuing system.

Server side

Low level Accessor and Reader classes

DataObserver that enables monitoring of changes made to the data, useful when you want to implement change notification using a message queuing system.

OperationsHandlerBase is a descendant of the DataObserver class that creates OperationNotification objects for all insert, update and delete operations and hands them over to the virtual HandleNotification method. By deriving from the OperationsHandlerBase class and overriding the HandleNotification method you will be able to implement asynchronous change notification using a message queuing technology.

The ProcessOperationRequest of the OperationsHandlerBase takes an OperationRequest as its parameter, and returns an OperationReply. The code generator creates OperationRequest/OperationReply classes for all the operations declared by the IDataProvider interface. OperationsHandlerBase converts the OperationRequest into a call to the IDataContextProvider and converts the result to the returned OperationReply. This can be used to perform all the CRUD operations using a message queuing technology.

ServiceImplementation implementing the IDataContextProvider interface against the Oracle Data Access provider using the low level classes.

DataService is the WCF service implementation which performs all operations through the IDataContextProvider interface.

Client side

DataClient implementing the IDataContextProvider interface which performs all operations through the WCF client generated by svcutil[^].

DataClient implements the HandleNotification method taking an OperationNotification object as its parameter. The code generator creates OperationNotification descendants for insert, update and delete operations. The DataClient class converts the notifications to the respective events declared by the IDataContextProvider interface.

EntityContext and entity classes for each of the tables added to the project. Entities exists in terms of their context, and the context guarantees that there is at most one object representing a row in the database.

EntitySource which is a component that provides rappid application development using the design tools provided by Visual Studio.

The figure below provide a rough outline of one possible way to use the generated code:

The black arrows designate two way communication between layers, while the red arrows shows the flow of change notifications.

Example project

The download includes an example of a WCF service hosted in a Windows Forms application built using code generated by DBTool. DBTool creates a component that can easily be hosted in Windows Forms or Windows Service applications. The service starts when you call the Start() method:

where SYS.DBMS_METADATA is a PL/SQL package supplied by Oracle that makes this very easy.

Oracle does not have IDENTITY columns, instead we have a mechanism called a SEQUENCE that generates unique values on request. How is shown later as part of the walk-through related to insert operations. The properties of a SEQUENCE:

to retrieve the properties of a sequence using the SequenceReader class.

The properties for a table provides a wealth of information about the table. This will tell you how the server is configured to grow the table, whether the server will attempt to keep it cached in memory, or if logging or monitoring enabled, and many other interesting tidbits about how Oracle manages the table.

Both tables have a primary key, and there is a one-to-many relationship between them.

The first thing to do is to add the two tables to the current project:

Once that is done we have a simple project containing the two tables:

Now let us take a look at the properties for the project:

For now we’re going to accept the defaults for the project, but it’s nice to know that they can be altered.

The low level Accessor and Reader classes

The Accessor and Reader classes are the classes that implement the basic CRUD operations against the database.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Reflection;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using Harlinn.Oracle.DBTool.Common;
using Harlinn.Oracle.DBTool.Types;
namespace Harlinn.Oracle.DBTool.DB
{

The first interesting thing to note about the generated accessor class is that this class is tagged with the DataObject[^] attribute, so it can be used with an ASP.NET ObjectDataSource[^].

With optimistic locking enabled DBTool generates code that sets the ElementState property to either ElementState.ConcurrencyConflict or ElementState.Deleted. When ElementState is set to ElementState.ConcurrencyConflict the conflicting data is assigned to the ConcurrencyConflictElement property of the result.

That was the accessor class for the DEPT table, and I included the whole thing because I feel it tells a lot about the value of code generators in general. This is a very small project, and for a normal project mapping the parameters manually is a process that is both time consuming and error prone.

Reader is an implementation of the IDataReader[^] interface that delegates all the operations to an OracleDataReader object while using Log4Net to log any exception that occurs during data retrieval.

The following properties allows us to access the fields of the current row in a very readable manner. Since Deptno is not a nullable column there is no reason to test whether the column is null or not.

Server side classes

This is the stuff that would normally go into a server, but they can be used to implement a thick client that connects directly with Oracle.

ServiceImplementation

ServiceImplementation implements the IDataContextProvider interface using the respective accessor and reader classes to implement CRUD functionality on the EMP and DEPT tables.

ServiceImplementation is intended to be used as a singleton, where the static Implementation property returns the common instance. When it's used this way it can serve as the central hub of the system.

GetAllEmps is a typical implementation of a callable method exposed by the WCF service. operationContext ensures that we have a valid connection with Oracle for the duration of the call. The method requires that the caller is a member of the specified Role - which defaults to "Administrators" and impersonates the caller.

Concluding remarks

This covers the basics related to the server side of things, and if you've read so far you're probably a pretty amazing person. It's hard to write anything exiting about CRUD, even if the stuff is pretty useful. Well, the next article will cover the client side of the generated code.

Build Instructions

Before you can build this project you need to install a few nuget packages:

It's also quite likely that you will need to update the reference to the Oracle.DataAccess assembly.

You will also need to update the OracleConnection connection string in the App.config file to provide the correct User Id, Password, and other settings matching your setup.

Further reading

John Hutchinson, Jon Whittle, Mark Rouncefield at School of Computing and Communications Lancaster University and Steinar Kristoffersen at Østfold University College and Møreforskning Molde AS: Empirical Assessment of MDE in Industry[^]

This paper presents the results from a twelve-month empirical study with the long-term goal of providing guidelines for Model-driven Engineering based on industry evidence.

Tolvanen makes an interesting point: success with modelling is most likely when companies develope their own bespoke modelling approaches, languages and tools rather than simply applying off-the-shelf solutions

The projects are based on a database schema that can be created by executing the SQL commands in CreateDatabase.sql which is located in the SQL folder.

Fixed a number of bugs related to Oracles automagic conversion of types.

21. of March, 2013 - Performance improvements and initial support for most of the missing Oracle object types.

If there are no elements of a particular type, the tree will not display a node for that type. Opening the SYS user takes a 'wee' bit of time because of the number of Java classes.

Added the ability to extract definitions for the following types:

Cluster

Function

Index

Library

Operator

Package

Procedure

Sequence

Synonym

Table

Trigger

Type

View

28. of March, 2013 - Added description about how DBTool retrieves some of the metadata from the database.

29. of March, 2013 - Added the ability to view relations between objects. This means that DBTool will display the tables and views a view references.

4. of April, 2013 - Added code to enable or disable context menu items depending on the currently selected element in the treeviews.

1. of May, 2013 - Major refactoring of the code. The original program is still included, but I'm refactoring the code so that I will be able to add support for more than one database server, while at the same time create a set of reusable libraries for working with the meta data exposed by the database servers.

16th. of July 2013 - Now using AvalonDock.2.0.2000, AvalonEdit.4.3.1.9430 and log4net.2.0.0.

25th. of July 2013 - Added new classes to Harlinn.Oracle, a library that aims to provide access to most of the features provided by the Oracle.DataAccess assembly while still loading the Oracle.DataAccess assembly dynamically. In the future DBTool will not reference the Oracle.DataAccess assembly, it will be loaded using the providerName from the connection string.

In the last few weeks I have been working on something similar. The prospect of recreating all data access layers in C++ by hand was not very encouraging. Even if I will not have a nice WPF UI, this article is still an inspiration.

I tried you tool an get an ORA-00904 "EDITION_NAME" invalid identifier when i try to expand the node of my schema user. The database is a 10g but i only have an Oracle 9i client installed on my machine. Do i need a higher Oracle client?

i had to remove NAMESPACE from the select too. Additionally i had to remove RESULT_CACHE, SEGMENT_CREATED, READ_ONLY, COMPRESS_FOR, CELL_FLASH_CACHE, FLASH_CACHE from the SQL in TableReader.

Then i failed because i don't have dba rights on the database and did not have access to SYS.DBA_VIEWS (i think this will also exist for older releases). In my company no developer has SYSDBA rights. We only have access to the schema user.

This application looks very helpful, unfortunately I am not using VS2012 yet. I forced the project to load by editing the solution and project files and retargeting for .NET 4.0. I had to remove a reference to System.Windows (4.5 also?). Then changed the build to 32 bit. Everything compiles and runs. Just nothing is populating for the Oracle data except the users on the Database tab (so it is getting a connection to Oracle). I may be trying to do something that just isn't possible.

Has anyone had any success with this in VS 2010, .NET 4.0 or 32-bit platform target?

This project originally started out as Windows forms application, using 32-bit .Net 3.5.

The Windows Forms application makes extensive use of commercial components, which is somewhat frowned upon here on CP, so I decided to move it to WPF.

The error messages seems to indicate that the program has trouble binding to Model. In the code I originally posted I used a CollectionViewSource[^] to create and bind to the Model object, while the program now creates the initial Model object in the constructor of the MainWindow. I made this change because I ran into a number of errors related to the bindings, when reloading a previously saved project.

Thanks for the amazingly quick reply. I do get receive fewer errors with the latest code, but I am still not seeing the data (still Model binding trouble I suspect). I have to put it down for the time being, but will dig into the code later this week and see if I can make it work.