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 toolsmith is an essential part of Harlan Mills surgical team[^]
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:
private void startToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
serverComponent.Start();
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
}
}
The service stops when you call the Stop() method:
private void stopToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
serverComponent.Stop();
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
}
}
Only users that are member of the DBToolExampleUsers group can connect to the WCF service.
Setting up a client to communicate with the WCF service requires the following steps:
public MainForm()
{
InitializeComponent();
Create a DataClient object, which implements IDataContextProvider:
dataClient = new DataClient();
Create an EntityContext object using the DataClient object
entityContext = new EntityContext(dataClient);
Assign the form to the SynchronizationControl property of the EntityContext object.
entityContext.SynchronizationControl = this;
Make the new EntityContext object the default EntityContext for EntitySource objects:
DefaultEntitySourceEntityContext.Context = entityContext;
}
protected override void OnShown(EventArgs e)
{
base.OnShown(e);
Utils.Logging.EventAppender.Instance.MessageLoggedEvent += Instance_MessageLoggedEvent;
try
{
Connect to the WCF service:
dataClient.Connect();
At this point the client is connected to the server.
RefreshNodes();
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
}
}
Most of this article is about the code generated by DBTool, so there is a lot of code listings - the stuff that DBTool saves you from writing.
Since everything gets done through the IDataContextProvider interface the EntityContext can bypass WCF, or you can set up a DataService that operates through a DataClient.
DBA Features
Admittedly, DBTool is not yet a replacement for a professional DBA tool, but it has many features that's usually of some interest to DBAs' too.
DBTool queries the SYS.ALL_OBJECTS view for the basic information about the objects owned by a particular user:
SELECT
OWNER,
OBJECT_NAME,
SUBOBJECT_NAME,
OBJECT_ID,
DATA_OBJECT_ID,
OBJECT_TYPE,
CREATED,
LAST_DDL_TIME,
TIMESTAMP,
STATUS,
TEMPORARY,
GENERATED,
SECONDARY,
NAMESPACE,
EDITION_NAME
FROM
SYS.ALL_OBJECTS
where OWNER = :owner
The query is perfomed using the ObjectReader class which expose the fields as readonly properties, permitting easy, and very readable access to the data, while iterating over the resultset.
Extracting the definition for the following database object types:
- Cluster
- Function
- Index
- Library
- Operator
- Package
- Procedure
- Sequence
- Synonym
- Table
- Trigger
- Type
- View
is done using a simple select
select
SYS.DBMS_METADATA.GET_DDL(:typeName,:objectName,:owner)
FROM DUAL
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:
DBTool queries the ALL_SEQUENCES view
SELECT
SEQUENCE_OWNER,
SEQUENCE_NAME,
MIN_VALUE,
MAX_VALUE,
INCREMENT_BY,
CYCLE_FLAG,
ORDER_FLAG,
CACHE_SIZE,
LAST_NUMBER
FROM ALL_SEQUENCES
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.
The information is retrieved from the ALL_TABLES view:
SELECT
OWNER, TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, IOT_NAME, STATUS, PCT_FREE, PCT_USED,
INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE,
FREELISTS, FREELIST_GROUPS, LOGGING, BACKED_UP, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE,
CHAIN_CNT, AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS, DEGREE, INSTANCES,
CACHE, TABLE_LOCK, SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED, IOT_TYPE, TEMPORARY, SECONDARY,
NESTED, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, ROW_MOVEMENT, GLOBAL_STATS, USER_STATS,
DURATION, SKIP_CORRUPT, MONITORING, CLUSTER_OWNER, DEPENDENCIES, COMPRESSION, COMPRESS_FOR,
DROPPED, READ_ONLY, SEGMENT_CREATED, RESULT_CACHE
FROM ALL_TABLES
and the information is extracted from the result set using the TableReader class. Information about views is available through the ALL_VIEWS view:
SELECT OWNER,VIEW_NAME,TEXT_LENGTH,TEXT,TYPE_TEXT_LENGTH,TYPE_TEXT,OID_TEXT_LENGTH,OID_TEXT,
VIEW_TYPE_OWNER,VIEW_TYPE,SUPERVIEW_NAME,EDITIONING_VIEW,READ_ONLY
FROM SYS.ALL_VIEWS
and DBTool uses the ViewReader class to retrieve this information.
Information about the columns returned by a view or contained in a table are retrieved by querying the SYS.ALL_TAB_COLUMNS view:
SELECT
OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_TYPE_MOD,DATA_TYPE_OWNER,DATA_LENGTH,
DATA_PRECISION,DATA_SCALE,NULLABLE,COLUMN_ID,DEFAULT_LENGTH,DATA_DEFAULT,NUM_DISTINCT,
LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS,NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,
CHARACTER_SET_NAME,CHAR_COL_DECL_LENGTH,GLOBAL_STATS,USER_STATS,AVG_COL_LEN,CHAR_LENGTH,
CHAR_USED,V80_FMT_IMAGE,DATA_UPGRADED,HISTOGRAM
FROM SYS.ALL_TAB_COLUMNS
and the information is extracted using the ColumnReader class.
The properties for a constraint on a table:
DBTool can be used to quickly get the details about a table or a view:
or a function and its result and arguments:
Details about an argument is of interest both to DBAs and developers:
Setting up a simple project
Before we discuss the output of DBTool we need to create a simple project that we will use to generate that output. Here are the two tables we are going to work with:
create table SCOTT.DEPT
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);
alter table SCOTT.DEPT
add constraint PK_DEPT primary key (DEPTNO);
create table SCOTT.EMP
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
alter table SCOTT.EMP
add constraint PK_EMP primary key (EMPNO);
alter table SCOTT.EMP
add constraint FK_DEPTNO foreign key (DEPTNO)
references SCOTT.DEPT (DEPTNO);
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[^].
[DataObject]
public partial class DeptElementAccessor : Accessor
{
The generated code uses Log4Net to log exceptions:
private static readonly log4net.ILog sfLog =
log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
private static void LogException(Exception exc, MethodBase method)
{
Logger.LogException(sfLog, exc, method);
}
public const string SCHEMA = "SCOTT";
public const string TABLE = "DEPT";
public const string DB_QUALIFIED_NAME = "SCOTT.DEPT";
The CreateDataTable() method creates an empty DataTable[^]. There are so much code floating around that works with DataTable, so this method is often handy.
public static DataTable CreateDataTable()
{
try
{
DataTable result = new DataTable();
DataColumn deptnoDataColumn = new DataColumn( "DEPTNO", typeof(short) );
deptnoDataColumn.AllowDBNull = false;
result.Columns.Add(deptnoDataColumn);
DataColumn dnameDataColumn = new DataColumn( "DNAME", typeof(string) );
dnameDataColumn.AllowDBNull = true;
result.Columns.Add(dnameDataColumn);
DataColumn locDataColumn = new DataColumn( "LOC", typeof(string) );
locDataColumn.AllowDBNull = true;
result.Columns.Add(locDataColumn);
DataColumn[] keys = new DataColumn[1];
keys[0] = deptnoDataColumn;
result.PrimaryKey = keys;
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
This overload of CreateDataTable takes a List<DeptElementData> and creates a DataTable containing the information:
public static DataTable CreateDataTable( List<DeptElementData> elements )
{
try
{
DataTable result = CreateDataTable();
foreach(DeptElementData element in elements)
{
object deptno = element.Deptno;
object dname;
if( element.Dname != null )
{
dname = element.Dname;
}
else
{
dname = null;
}
object loc;
if( element.Loc != null )
{
loc = element.Loc;
}
else
{
loc = null;
}
result.Rows.Add(deptno,dname,loc );
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
WriteToServer takes a DataTable and writes the data to the database using an OracleBulkCopy, which is pretty efficient when you have a large number of records.
public static void WriteToServer( OracleConnection oracleConnection,
string qualifiedDBName, DataTable dataTable )
{
try
{
using ( OracleBulkCopy bulkCopy = new OracleBulkCopy( oracleConnection ) )
{
bulkCopy.DestinationTableName = qualifiedDBName;
bulkCopy.WriteToServer( dataTable );
}
}
catch ( Exception exc )
{
LogException( exc, MethodBase.GetCurrentMethod( ) );
throw;
}
}
GetAll is the default DataObjectMethod used to retrieve all the records from the database:
[DataObjectMethod(DataObjectMethodType.Select,true)]
public static List<DeptElementData> GetAll( )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
List<DeptElementData> result = new List<DeptElementData>( );
It uses the DeptElementReader, which is the reader implementation for the DEPT table:
DeptElementReader elementReader = new DeptElementReader( qualifiedDBName );
using( elementReader )
{
while( elementReader.Read( ) )
{
DeptElementData element = elementReader.Dept;
result.Add(element);
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
[DataObjectMethod(DataObjectMethodType.Select,false)]
public static List<DeptElementData> GetAll( OracleConnection oracleConnection )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
List<DeptElementData> result = new List<DeptElementData>( );
DeptElementReader elementReader =
new DeptElementReader( oracleConnection, qualifiedDBName );
using( elementReader )
{
while( elementReader.Read( ) )
{
DeptElementData element = elementReader.Dept;
result.Add(element);
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
GetKeyedCollection retrieves a KeyedDeptCollection, which is a KeyedCollection<TKey, TItem>[^] implemented with a TKey matching the primary key of of the DEPT table and TItem of DeptElementData.
public static KeyedDeptCollection GetKeyedCollection( )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
KeyedDeptCollection result = new KeyedDeptCollection( );
DeptElementReader elementReader = new DeptElementReader( qualifiedDBName );
using( elementReader )
{
while( elementReader.Read( ) )
{
DeptElementData element = elementReader.Dept;
result.Add(element);
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
GetByDeptno retrieves a single DeptElementData object:
[DataObjectMethod(DataObjectMethodType.Select,false)]
public static DeptElementData GetByDeptno( short deptno )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
DeptElementData result = null;
DeptElementReader elementReader =
DeptElementReader.CreateReaderByDeptno( qualifiedDBName,deptno );
using( elementReader )
{
if( elementReader.Read( ) )
{
DeptElementData element = elementReader.Dept;
result = element;
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
[DataObjectMethod(DataObjectMethodType.Select,false)]
public static DeptElementData GetByDeptno(
OracleConnection oracleConnection, short deptno )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
DeptElementData result = null;
DeptElementReader elementReader =
DeptElementReader.CreateReaderByDeptno( oracleConnection ,
qualifiedDBName,deptno );
using( elementReader )
{
if( elementReader.Read( ) )
{
DeptElementData element = elementReader.Dept;
result = element;
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static int Insert( DeptElementData element )
{
try
{
DeptElementData result = null;
int recordsInserted = Insert( GetConnection(), element, out result );
return recordsInserted;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static int Insert( DeptElementData element, out DeptElementData result )
{
try
{
int recordsInserted = Insert( GetConnection(), element, out result );
return recordsInserted;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
This overload of Insert performs the actual operation:
public static int Insert( OracleConnection oracleConnection,
DeptElementData element, out DeptElementData result )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
int recordsInserted = 0;
result = null;
OracleCommand oracleCommand = oracleConnection.CreateCommand();
using (oracleCommand)
{
oracleCommand.BindByName = true;
string insertSQLStatement = "INSERT INTO {0}(DEPTNO,DNAME,LOC) " +
" VALUES(:deptno_, :dname_, :loc_)";
string finalInsertSQLStatement =
string.Format(insertSQLStatement,qualifiedDBName);
oracleCommand.CommandText = finalInsertSQLStatement;
It's worth noticing that all the data is passed to the database driver using parameters, which helps to prevent SQL injection.
OracleParameter deptnoParameter =
oracleCommand.Parameters.Add(new OracleParameter( ":deptno_",
OracleDbType.Int16 ));
deptnoParameter.Value = element.Deptno;
OracleParameter dnameParameter =
oracleCommand.Parameters.Add(new OracleParameter( ":dname_",
OracleDbType.Varchar2 ));
This is how the generated code handles nullable columns:
if( element.Dname != null )
{
dnameParameter.Value = element.Dname;
}
else
{
dnameParameter.IsNullable = true;
dnameParameter.Value = null;
}
OracleParameter locParameter =
oracleCommand.Parameters.Add(new OracleParameter( ":loc_",
OracleDbType.Varchar2 ));
if( element.Loc != null )
{
locParameter.Value = element.Loc;
}
else
{
locParameter.IsNullable = true;
locParameter.Value = null;
}
At this point all the parameters have been assigned, so we hand over execution to the Oracle database:
recordsInserted = oracleCommand.ExecuteNonQuery();
if( recordsInserted != 0)
{
result = element;
}
}
return recordsInserted;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
If we had assigned a SEQUENCE to SCOTT.DEPT in the project that would have caused a slight change to the generated code for the insert operation, beginning with the SQL statement:
string insertSQLStatement = "INSERT INTO {0}(DEPTNO,DNAME,LOC) " +
" VALUES(DEPTNO_SEQ.NEXTVAL, :dname_, :loc_)" +
" RETURNING DEPTNO INTO :deptno_";
For this to work as expected we would change the direction of the parameter for :deptno_:
deptnoParameter.Direction = ParameterDirection.Output;
Which would have allowed us to retrieve the generated value like this:
recordsInserted = oracleCommand.ExecuteNonQuery();
if( recordsInserted != 0)
{
result = element;
result.Deptno = deptnoParameter.Value;
}
public static void Insert( List<DeptElementData> elements )
{
try
{
Insert( GetConnection(), elements);
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static void Insert( OracleConnection oracleConnection,
List<DeptElementData> elements )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
DataTable dataTable = CreateDataTable( elements );
using (dataTable)
{
WriteToServer(oracleConnection,qualifiedDBName,dataTable);
}
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static int Update( DeptElementData element )
{
try
{
DeptElementData result = null;
int recordsUpdated = Update(GetConnection(), element, out result);
return recordsUpdated;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static int Update( DeptElementData element, out DeptElementData result )
{
try
{
int recordsUpdated = Update(GetConnection(), element, out result);
return recordsUpdated;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
The Update method works pretty much along the same lines as the Insert method:
public static int Update( OracleConnection oracleConnection, DeptElementData element,
out DeptElementData result )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
int recordsUpdated = 0;
result = null;
OracleCommand oracleCommand = oracleConnection.CreateCommand();
using (oracleCommand)
{
oracleCommand.BindByName = true;
string updateSQLStatement = "UPDATE {0} SET " +
"DNAME = :dname_ , " +
"LOC = :loc_ " +
" WHERE " +
"(DEPTNO = :deptno_) ";
string finalUpdateSQLStatement = string.Format(updateSQLStatement,qualifiedDBName);
oracleCommand.CommandText = finalUpdateSQLStatement;
OracleParameter deptnoParameter =
oracleCommand.Parameters.Add(new OracleParameter( ":deptno_",
OracleDbType.Int16 ));
deptnoParameter.Value = element.Deptno;
OracleParameter dnameParameter =
oracleCommand.Parameters.Add(new OracleParameter( ":dname_",
OracleDbType.Varchar2 ));
if( element.Dname != null )
{
dnameParameter.Value = element.Dname;
}
else
{
dnameParameter.IsNullable = true;
dnameParameter.Value = null;
}
OracleParameter locParameter =
oracleCommand.Parameters.Add(new OracleParameter( ":loc_",
OracleDbType.Varchar2 ));
if( element.Loc != null )
{
locParameter.Value = element.Loc;
}
else
{
locParameter.IsNullable = true;
locParameter.Value = null;
}
recordsUpdated = oracleCommand.ExecuteNonQuery();
if( recordsUpdated != 0)
{
result = element;
}
else
{
DeptElementReader reader =
DeptElementReader.CreateReaderByDeptno(oracleConnection,
qualifiedDBName,element.Deptno);
using (reader)
{
result = element;
}
}
}
return recordsUpdated;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
The Update operation gets slightly altered when Concurrency is enabled for a table, which is done by checking the Concurrency property of the field used for optimistic locking:
Starting with the SQL statement:
string updateSQLStatement = "UPDATE {0} SET " +
"PROCESS = :process_ , " +
"NAME = :name_ , " +
"OPTIMISTIC_LOCK = OPTIMISTIC_LOCK + 1, " +
"DESCRIPTION = :description_ , " +
"COMMENTS = :comments_ " +
" WHERE " +
"(ID = :id_) AND " +
"(OPTIMISTIC_LOCK = :optimisticLock_) " +
" RETURNING OPTIMISTIC_LOCK INTO :optimisticLock_";
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.
recordsUpdated = oracleCommand.ExecuteNonQuery();
if( recordsUpdated != 0)
{
result = element;
result.OptimisticLock = ((OracleDecimal)optimisticLockParameter.Value).ToInt64();
result.ElementState = ElementState.Stored;
}
else
{
ModelElementReader reader =
ModelElementReader.CreateReaderById(oracleConnection, qualifiedDBName,element.Id);
using (reader)
{
result = element;
if (reader.Read())
{
result.ElementState = ElementState.ConcurrencyConflict;
result.ConcurrencyConflictElement = reader.Model;
}
else
{
result.ElementState = ElementState.Deleted;
}
}
}
When optimistic locking is enabled the implementation of the Delete operation is changed in a similar manner. Now, back to our walk-through of the code generated for the DEPT table:
public static int Delete( short deptno )
{
try
{
int result = Delete( GetConnection(), deptno );
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
Finally we have the Delete method:
private const string DELETE_STATEMENT = "DELETE FROM {0} WHERE DEPTNO = :deptno";
public static int Delete( OracleConnection oracleConnection, short deptno )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
int result = 0;
OracleCommand oracleCommand = oracleConnection.CreateCommand();
using (oracleCommand)
{
string deleteStatement = string.Format(DELETE_STATEMENT, qualifiedDBName);
oracleCommand.CommandText = deleteStatement;
OracleParameter deptnoParameter =
oracleCommand.Parameters.Add(new OracleParameter( ":deptno",
OracleDbType.Int16 ));
deptnoParameter.Value = deptno;
result = oracleCommand.ExecuteNonQuery();
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
}
}
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.
DeptElementReader is the reader for the DEPT table:
namespace Harlinn.Oracle.DBTool.DB
{
public partial class DeptElementReader : Reader
{
Again log4Net is used to log any exception that would occur during execution.
private static readonly log4net.ILog sfLog =
log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
private static void LogException(Exception exc, MethodBase method)
{
Harlinn.Oracle.DBTool.Common.Logger.LogException(sfLog, exc, method);
}
public const string DEFAULT_QUALIFIED_DBNAME = "SCOTT.DEPT";
public const string FULL_SELECT = "SELECT DEPTNO,DNAME,LOC FROM {0}";
public const string KEY_FIELDS = "DEPTNO";
public const int DEPTNO = 0;
public const int DNAME = 1;
public const int LOC = 2;
The various constructors invoke CreateReader factory functions to create the OracleDataReader object for the DeptElementReader.
public DeptElementReader ( )
: base( CreateReader( DEFAULT_QUALIFIED_DBNAME ) )
{
}
public DeptElementReader ( string qualifiedDBName )
: base( CreateReader( qualifiedDBName ) )
{
}
public DeptElementReader ( OracleConnection oracleConnection )
: base( CreateReader( oracleConnection ) )
{
}
public DeptElementReader ( OracleConnection oracleConnection, string qualifiedDBName )
: base( CreateReader( oracleConnection, qualifiedDBName ) )
{
}
public DeptElementReader ( OracleDataReader reader )
: base( reader )
{
}
Factory functions used to create OracleDataReader objects:
private static OracleDataReader CreateReader( string qualifiedDBName )
{
try
{
OracleConnection oracleConnection = GetConnection();
OracleDataReader result = CreateReader(oracleConnection,qualifiedDBName);
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
private static OracleDataReader CreateReader( OracleConnection oracleConnection )
{
try
{
OracleDataReader result =
CreateReader(oracleConnection,DEFAULT_QUALIFIED_DBNAME);
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
private static OracleDataReader CreateReader( OracleConnection oracleConnection,
string qualifiedDBName )
{
try
{
string sql = string.Format(FULL_SELECT, qualifiedDBName) +
" ORDER BY " + KEY_FIELDS;
OracleCommand oracleCommand = oracleConnection.CreateCommand();
using (oracleCommand)
{
oracleCommand.CommandText = sql;
OracleDataReader result =
oracleCommand.ExecuteReader(CommandBehavior.SingleResult);
return result;
}
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
Factory functions used to create DeptElementReader objects:
public static DeptElementReader CreateReaderByDeptno( short deptno)
{
DeptElementReader result =
CreateReaderByDeptno( GetConnection(), DEFAULT_QUALIFIED_DBNAME,deptno);
return result;
}
public static DeptElementReader CreateReaderByDeptno( OracleConnection oracleConnection ,
short deptno)
{
DeptElementReader result =
CreateReaderByDeptno( oracleConnection, DEFAULT_QUALIFIED_DBNAME,deptno);
return result;
}
public static DeptElementReader CreateReaderByDeptno( string qualifiedDBName, short deptno)
{
DeptElementReader result =
CreateReaderByDeptno( GetConnection(), qualifiedDBName,deptno);
return result;
}
public static DeptElementReader CreateReaderByDeptno( OracleConnection oracleConnection ,
string qualifiedDBName, short deptno)
{
try
{
string fullSelect = string.Format(FULL_SELECT, qualifiedDBName);
OracleCommand oracleCommand = oracleConnection.CreateCommand();
using (oracleCommand)
{
oracleCommand.BindByName = true;
string queryFilter = " WHERE DEPTNO = :deptno";
string selectStatement = fullSelect + queryFilter;
oracleCommand.CommandText = selectStatement;
OracleParameter deptnoParameter =
oracleCommand.Parameters.Add(new OracleParameter( ":deptno",
OracleDbType.Int16 ));
deptnoParameter.Value = deptno;
OracleDataReader result =
oracleCommand.ExecuteReader(CommandBehavior.SingleResult);
return new DeptElementReader( result );
}
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
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.
public short Deptno
{
get
{
try
{
short result = GetInt16(DEPTNO);
return result;
}
catch(Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
}
Both DNAME and LOC are nullable columns of the DEPT table, so the generated code checks for that, and only attempts to read values that have previously been set to a non null value.
public string Dname
{
get
{
try
{
if(IsDBNull(DNAME) == false)
{
string result = GetString(DNAME);
return result;
}
return null;
}
catch(Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
}
public string Loc
{
get
{
try
{
if(IsDBNull(LOC) == false)
{
string result = GetString(LOC);
return result;
}
return null;
}
catch(Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
}
And at last we have the Dept property which reads all the information from a row of the DEPT table and turns it into a DeptElementData object.
public DeptElementData Dept
{
get
{
try
{
DeptElementData result = new DeptElementData(Deptno,Dname,Loc );
return result;
}
catch(Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
}
}
}
Common classes and interfaces
DeptElementData is a class that we can use to transfer information contained in a single row of the DEPT table. It can be serialized using the DataContractSerializer[^], the BinaryFormatter[^] and the SoapFormatter[^].
namespace Harlinn.Oracle.DBTool.Types
{
[DataContract(Namespace = Constants.Namespace)]
[Serializable]
public class DeptElementData : ElementBase
{
private short deptno;
private string dname;
private string loc;
public DeptElementData( )
{
}
public DeptElementData( short deptno, string dname, string loc )
{
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
public override ElementType ElementType
{
get
{
return ElementType.Dept;
}
}
AssignTo copies data from another instance of the DeptElementData class.
public override void AssignTo(ElementBase destination)
{
DeptElementData destinationElement = (DeptElementData)destination;
destinationElement.deptno = this.deptno;
destinationElement.dname = this.dname;
destinationElement.loc = this.loc;
}
CompareTo compares an instance of the DeptElementData with another instance of the class.
public override int CompareTo(ElementBase other)
{
DeptElementData otherElement = (DeptElementData)other;
int result = CompareHelper.Compare( otherElement.deptno , this.deptno);
if( result != 0)
{
return result;
}
result = CompareHelper.Compare( otherElement.dname , this.dname);
if( result != 0)
{
return result;
}
result = CompareHelper.Compare( otherElement.loc , this.loc);
return result;
}
The rest is the expected properties, one for each of the columns of the DEPT table.
[DataMember(EmitDefaultValue=false)]
public short Deptno
{
get
{
return deptno;
}
set
{
this.deptno = value;
}
}
[DataMember(EmitDefaultValue=false)]
public string Dname
{
get
{
return dname;
}
set
{
this.dname = value;
}
}
[DataMember(EmitDefaultValue=false)]
public string Loc
{
get
{
return loc;
}
set
{
this.loc = value;
}
}
IDataProvider
IDataProvider declares the operations that can be performed on the two tables:
namespace Harlinn.Oracle.DBTool.Types
{
public interface IDataProvider
{
List<EmpElementData> GetAllEmps();
EmpElementData GetEmpByEmpno( short empno );
List<EmpElementData> GetEmpByDeptno( short? deptno );
EmpElementData InsertEmp( Guid clientId, EmpElementData element );
void InsertEmpList( Guid clientId, List<EmpElementData> elements );
EmpElementData UpdateEmp( Guid clientId, EmpElementData element );
int DeleteEmp( Guid clientId, short empno );
List<DeptElementData> GetAllDepts();
DeptElementData GetDeptByDeptno( short deptno );
DeptElementData InsertDept( Guid clientId, DeptElementData element );
void InsertDeptList( Guid clientId, List<DeptElementData> elements );
DeptElementData UpdateDept( Guid clientId, DeptElementData element );
int DeleteDept( Guid clientId, short deptno );
}
}
IDataObserver
IDataObserver declares an interface that allows an implementing class to get notified when something is changed in the system.
namespace Harlinn.Oracle.DBTool.Types
{
public interface IDataObserver
{
void OnEmpInserted(object sender, OnEmpInsertedEventArgs eventArgs );
void OnEmpChanged(object sender, OnEmpChangedEventArgs eventArgs );
void OnEmpDeleted(object sender, OnEmpDeletedEventArgs eventArgs );
void OnDeptInserted(object sender, OnDeptInsertedEventArgs eventArgs );
void OnDeptChanged(object sender, OnDeptChangedEventArgs eventArgs );
void OnDeptDeleted(object sender, OnDeptDeletedEventArgs eventArgs );
}
}
IDataContextProvider
IDataContextProvider extends the IDataProvider interface, adding events that allows other classes to get notified when something is changed in the system.
namespace Harlinn.Oracle.DBTool.Types
{
public interface IDataContextProvider : IDataProvider
{
event OnEmpInsertedDelegate OnEmpInsertedEvent;
event OnEmpChangedDelegate OnEmpChangedEvent;
event OnEmpDeletedDelegate OnEmpDeletedEvent;
event OnDeptInsertedDelegate OnDeptInsertedEvent;
event OnDeptChangedDelegate OnDeptChangedEvent;
event OnDeptDeletedDelegate OnDeptDeletedEvent;
}
}
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.
namespace Harlinn.Oracle.DBTool.Implementation
{
public partial class ServiceImplementation : IDataContextProvider
{
private static readonly log4net.ILog sfLog =
log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
private static void LogException(Exception exc, MethodBase method)
{
Logger.LogException(sfLog, exc, method);
}
static void Entering( MethodBase method )
{
Logger.Entering(sfLog, method);
}
static void Leaving( MethodBase method )
{
Logger.Leaving(sfLog, method);
}
private static readonly object synchObject = new object();
private static ServiceImplementation implementation;
public static ServiceImplementation Implementation
{
get
{
if (implementation == null)
{
lock (synchObject)
{
if (implementation == null)
{
implementation = new ServiceImplementation();
}
}
}
return implementation;
}
}
}
}
DataObserver
DataObserver implements the IDataObserver interface and works with an instance of a class implementing the IDataContextProvider interface.
DataService
DataService is the implementation of the WCF service
namespace Harlinn.Oracle.DBTool.Implementation
{
The DataService uses the DataServiceDataContextProvider get hold of the object implementing the IDataContextProvider interface. By default it will return the ServiceImplementation singleton.
public partial class DataServiceDataContextProvider
{
private static IDataContextProvider dataContextProvider;
public static IDataContextProvider DataContextProvider
{
get
{
if( dataContextProvider == null )
{
dataContextProvider = ServiceImplementation.Implementation;
}
return dataContextProvider;
}
set
{
dataContextProvider = value;
}
}
}
DataService is a session oriented WCF service. which is derived from DataObserver.
[ServiceContract(SessionMode = SessionMode.Required, Namespace = Constants.Namespace)]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public partial class DataService : DataObserver, IDisposable
{
private static readonly log4net.ILog sfLog =
log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
private static void LogException(Exception exc, MethodBase method)
{
Logger.LogException(sfLog, exc, method);
}
public DataService()
{
clientId = Guid.NewGuid();
IDataContextProvider dataContextProvider =
DataServiceDataContextProvider.DataContextProvider;
Attach(dataContextProvider);
}
protected IDisposable CreateOperationContext(MethodBase method)
{
Harlinn.Oracle.DBTool.Common.OperationContext result =
new Harlinn.Oracle.DBTool.Common.OperationContext(method);
return result;
}
A WCF client must initiate a session by calling Connect() before calling other methods implemented by the WCF service.
[OperationContract(IsOneWay = false, IsInitiating = true, IsTerminating = false)]
[PrincipalPermission(SecurityAction.Demand, Role = DATASERVICE_USERS_GROUP)]
[OperationBehavior(Impersonation = ImpersonationOption.Required)]
public Guid Connect()
{
}
A WCF client calls Disconnect() to terminate the session.
[OperationContract(IsOneWay = true, IsInitiating = false, IsTerminating = true)]
[PrincipalPermission(SecurityAction.Demand, Role = DATASERVICE_USERS_GROUP)]
[OperationBehavior(Impersonation = ImpersonationOption.Required)]
public void Disconnect()
{
}
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.
[OperationContract(IsOneWay = false, IsInitiating = false, IsTerminating = false)]
[PrincipalPermission(SecurityAction.Demand, Role = DATASERVICE_USERS_GROUP)]
[OperationBehavior(Impersonation = ImpersonationOption.Required)]
public List<EmpElementData> GetAllEmps()
{
List<EmpElementData> result = null;
MethodBase currentMethod = MethodBase.GetCurrentMethod();
Entering(currentMethod);
try
{
try
{
IDisposable operationContext = CreateOperationContext(currentMethod);
using(operationContext)
{
result = DataContextProvider.GetAllEmps();
}
}
catch (Exception exc)
{
LogException(exc, currentMethod );
ConvertExceptionToFaultAndThrow( exc, currentMethod );
}
}
finally
{
Leaving(currentMethod);
}
return result;
}
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:
Since I was unable to find a nuget package for the WPF Property Grid[^], I've included the project in the download.
You will also need Oracle Developer Tools for Visual Studio[^], and an Oracle client or full database installation[^]
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.
Martin Fowler, Rebecca Parsons Domain Specific Languages[^]
A nice introduction to Domain Specific Languages.
Juha-Pekka Tolvanen - Keynote at Code Generation 2014: The business case of modeling and generators[^]
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
History
- 15. of March, 2013 - Initial posting
- 16. of March, 2013 - A bit of eye candy
- 17. of March, 2013 - Many new features and a few bugfixes
- 19. of March, 2013 - Added example projects
- Harlinn.Oracle.DBTool.Example - Contains nearly everything
- Harlinn.Oracle.DBTool.Example.Service - Contains the DataService class
- Harlinn.Oracle.DBTool.Example.Client - Contains the DataClient class
- Harlinn.Oracle.DBTool.Example.Service.Win - hosts the generated WCF service
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.
