DBTool – Part 2: Harlinn.Oracle

• Download Harlinn.DBTool-2013-10-01-01.zip - 2 MB

Introduction

• The first article about DBTool can be found here: DBTool for Oracle - Part 1[^]

DBTool allows you to browse the contents of an Oracle database, and it generates c# code that you can use in your own code to access the database. In this, the second article about DBTool, we're going to take a look at the Harlinn.Oracle.dll assembly that I'm developing for use with DBTool.

One of the more annoying things about using Oracle's Oracle.DataAccess.dll assembly is that you normally have to reference it explicitly to access the features not exposed through the common base classes. Another thing is that so far the Oracle.DataAccess.dll is either explicitly 32-bit or explicitly 64-bit, and that means that you have to decide at compile time whether you want to create a 32-bit or a 64-bit application.

Harlinn.Oracle.dll is an assembly that uses reflection to access many of the features that would normally require an explicit assembly reference. The penalty for using reflection is minimized by caching information about properties, methods, constructors and events - I'm actually a bit surprised at how well it performs.

Created user HarlinnOracle
Created sequence TEST_SEQ 
Created table TEST_TABLE 
ODPConnection: Inserted and retrieved 50000 records in 13,4124415 seconds
OracleConnection: Inserted and retrieved 50000 records in 21,7103289 seconds
ODPConnection: Inserted and retrieved 50000 records in 13,2898706 seconds
OracleConnection: Inserted and retrieved 50000 records in 12,9866745 seconds
ODPConnection: Inserted and retrieved 50000 records in 19,1152156 seconds
OracleConnection: Inserted and retrieved 50000 records in 20,1206863 seconds
ODPConnection: Inserted and retrieved 50000 records in 13,2173342 seconds
OracleConnection: Inserted and retrieved 50000 records in 24,8340471 seconds
ODPConnection: Inserted and retrieved 50000 records in 13,2524953 seconds
OracleConnection: Inserted and retrieved 50000 records in 12,7241699 seconds
ODPConnection: Inserted and retrieved 50000 records in 13,2143947 seconds
OracleConnection: Inserted and retrieved 50000 records in 14,1340197 seconds
ODPConnection: Inserted and retrieved 50000 records in 37,0409351 seconds
OracleConnection: Inserted and retrieved 50000 records in 13,1895755 seconds
ODPConnection: Inserted and retrieved 50000 records in 13,6237786 seconds
OracleConnection: Inserted and retrieved 50000 records in 34,1442815 seconds
ODPConnection: Inserted and retrieved 50000 records in 26,0132111 seconds
OracleConnection: Inserted and retrieved 50000 records in 13,1367761 seconds
ODPConnection: Inserted and retrieved 50000 records in 19,246613 seconds
OracleConnection: Inserted and retrieved 50000 records in 17,0021234 seconds
ODPConnection execution time: 181,4262897 seconds
OracleConnection execution time: 183,9826829 seconds
Dropped user HarlinnOracle

The execution times are nearly identical.

The core of the code that generates the above results looks like this:

for (int i = 0; i < count; i++)
{
    using (ODPCommand cmd = con.CreateCommand())
    {
                    
        string sql = "INSERT INTO " + Constants.User + 
                     ".TEST_TABLE(ID,TEXT) VALUES(:id,:text)";
        cmd.CommandText = sql;
        cmd.Parameters.Add(":id", i + 1);
        cmd.Parameters.Add(":text", "ROW "+ i + 1);
        cmd.ExecuteNonQuery();
                        
    }
}

using (ODPCommand cmd = con.CreateCommand())
{
    string sql = "SELECT ID,TEXT FROM " + Constants.User + ".TEST_TABLE";
    cmd.CommandText = sql;
    using (ODPDataReader reader = cmd.ExecuteReader())
    {
        long sum = 0;
        while (reader.Read())
        {
            sum += reader.GetOracleDecimal(0).ToInt32();
        }
    }
}

The code for the test of the OracleConnection, OracleCommand and the OracleDataReader is identical except that it uses Oracles ODP.Net classes directly. While the timings vary quite wildly, I think it's fair to say that this indicates that using my reflection based classes doesn't seem to have much impact on the performance.

The test executes reflection based code quite often. GetOracleDecimal returns an ODPDecimal, which is entirely based on reflection, and the cmd.Parameters.Add method is also implemented using reflection.

DBTool now loads Oracle.DataAccess.dll using the "Oracle.DataAccess.Client" provider name. This means that DBTool will now use the Oracle.DataAccess.dll registered in the machine.config file, and that the same executable can be used for both 32-bit and 64-bit execution.

The library is now complete enough to handle the needs of DBTool, and the primary classes are:

• ODPConnection
• ODPCommand
• ODPParameterCollection
• ODPParameter
• ODPDataReader
• ODPTransaction

There are also a number of classes for working with Oracle specific column types:

• ODPBFile
• ODPBinary
• ODPBlob
• ODPClob
• ODPDate
• ODPDecimal
• ODPIntervalDS
• ODPIntervalYM
• ODPRef
• ODPString
• ODPTimeStamp
• ODPTimeStampLTZ
• ODPTimeStampTZ
• ODPXmlStream
• ODPXmlType

Why

The Oracle RDBMS is perhaps the database system most often used to store data for industrial management systems. Over the years I’ve found that explicitly referencing the Oracle.DataAccess.dll causes a bit of pain for system administrators since they usually would like to be able to upgrade the Oracle client installation without having to rebuild the applications that use the Oracle.DataAccess.dll assembly. This is an effort to address that problem.

The Harlinn.Oracle.dll is currently a work in progress, and I would appreciate a bit of feedback on the usefulness of the features I’m implementing.

An short introduction to reflection

By reflection we refer to the functionality offered by the .Net runtime that allows us to inspect and manipulate code entities without knowing their identification or formal structure ahead of time.

In .Net reflection permits us to analyse objects and types and collect information about their definition and behaviour. This information can be used to create new objects dynamically and invoke methods dynamically.

Most of the reflection related functionality in .Net is provided by the classes in the System.Reflection namespace. We also have the System.Type class which provide significant functionality related to reflection.

The most commonly used classes are:

• System.Type:

  Everything in .Net has a Type which is the primary mechanism for accessing metadata about objects, structs and basic data types. Type allows us to retrieve information about the constructors, methods, fields, properties, and events of a class, as well as the module and the assembly containing the implementation.

• System.Reflection.Assembly:

  A .Net application is a collection of assemblies, usually *.exe or *.dll files. Much of the functionality of .Net is provided through assemblies that ships as part of the .Net runtime. An assembly contains, among other things, the byte code that implements the classes and structs used by our applications. The Assembly class represents a single assembly, and it provides functionality that allows us to access the types implemented in the assembly.

• System.Reflection.ConstructorInfo:

  We can retrieve information about the constructors for a type using the Type.GetConstructor method. ConstructorInfo also allows us to create instances of a Type through the Invoke method.

• System.Reflection.EventInfo:

  We can retrieve information about an event for a type using the Type.GetEvent method. EventInfo also allows us to add and remove event handlers for an instance of the type.

• System.Reflection.FieldInfo:

  We can retrieve information about a field of a type using the Type.GetField method. FieldInfo also allows us to set and retrieve the value of a field for an instance of the type.

• System.Reflection.MethodInfo:

  We can retrieve information about the methods implemented by a type using the Type.GetMethod method. MethodInfo also allows us to call the methods through the Invoke method.

• System.Reflection.PropertyInfo:

  We can retrieve information about a property of a type using the Type.GetProperty method. PropertyInfo also allows us to set and retrieve the value of a property for an instance of the type.

The following articles from MSDN magazine provide insights into some of the possibilities and pitfalls related to reflection:

• Use Reflection to Discover and Assess the Most Common Types in the .NET Framework[^]
• Dodge Common Performance Pitfalls to Craft Speedy Applications[^]
• Reflections on Reflection[^]

Usage

If you know how to use the Oracle.DataAccess.dll assembly then you’ll find that the library is quite simple to use:

public static ColumnReader CreateReader(ODPConnection oracleConnection, 
                                        string owner, string tableName, string columnName)
{
    try
    {
        string fullSelect = string.Format(FULL_SELECT, DEFAULT_QUALIFIED_DBNAME);
        ODPCommand oracleCommand = oracleConnection.CreateCommand();
        using (oracleCommand)
        {
            oracleCommand.BindByName = true;
            string queryFilter = " WHERE OWNER = :owner AND "+
                                 " TABLE_NAME = :tableName AND COLUMN_NAME = :columnName";
            string selectStatement = fullSelect + queryFilter;
            oracleCommand.CommandText = selectStatement;


            var ownerParameter = 
                oracleCommand.Parameters.Add(new ODPParameter(":owner", ODPDbType.Varchar2));
            ownerParameter.Value = owner;

            var tableNameParameter = 
                oracleCommand.Parameters.Add(new ODPParameter(":tableName", ODPDbType.Varchar2));
            tableNameParameter.Value = tableName;

            var columnNameParameter = 
                oracleCommand.Parameters.Add(new ODPParameter(":columnName", ODPDbType.Varchar2));
            columnNameParameter.Value = columnName;

            var result = 
                oracleCommand.RawExecuteReader(CommandBehavior.SingleResult | 
                                               CommandBehavior.SingleRow);
            return new ColumnReader(result);
        }
    }
    catch (Exception exc)
    {
        LogException(exc, MethodBase.GetCurrentMethod());
        throw;
    }
}

ODPConnection

ODPConnection implements the System.Data.IDbConnection interface, and exposes the following functionality through reflection:

ODPCommand

ODPCommand implements the System.Data.IDbCommand interface, and exposes the following functionality through reflection:

Properties:

• BindByName: specifies the binding method for the parameters collection. Set to true if the parameters are bound by name, or false if the parameters are bound by position.

ODPParameterCollection

ODPParameterCollection implements the System.Data.IDataParameterCollection interface, and exposes the following functionality through reflection. Methods:

• ODPParameter Add(ODPParameter param): adds the supplied ODEParameter object to the parameters collection.
• ODPParameter Add(string name, object value): Creates a new ODEParameter and adds it to the parameters collection.
• ODPParameter Add(string name, ODPDbType dbType): Creates a new ODEParameter and adds it to the parameters collection.
• ODPParameter Add(string name, ODPDbType dbType, ParameterDirection direction): Creates a new ODEParameter and adds it to the parameters collection.
• ODPParameter Add(string name, ODPDbType dbType, int size, object val, ParameterDirection direction): Creates a new ODEParameter and adds it to the parameters collection.
• ODPParameter Add(string name, ODPDbType dbType, int size): Creates a new ODEParameter and adds it to the parameters collection.
• ODPParameter Add(string name, ODPDbType dbType, int size, string sourceColumn): Creates a new ODEParameter and adds it to the parameters collection.

ODPParameter

ODPParameter implements the System.Data.IDbDataParameter interface, and exposes the following functionality through reflection:

Constructors:

• ODPParameter(string name, ODPDbType dbType)

Properties:

• ArrayBindSize: Specifies the input or output size of a parameter before or after an Array Bind or PL/SQL Associative Array Bind execution.
• ArrayBindStatus: Specifies the input or output status of a parameter before or after an Array Bind or PL/SQL Associative Array Bind execution
• CollectionType: Specifies whether or not the OracleParameter represents a collection, and if so, specifies the collection type.
• Offset: Specifies the offset to the Value property or offset to the elements in the Value property.
• ODPDbType: Specifies the datatype.
• Status: Specifies the status of the execution related to the data in the Value property

ODPDataReader

ODPDataReader implements the System.Data.IDataReader interface, and exposes the following functionality through reflection:

Methods:

• byte[] GetBytes(int i): Uses GetOracleBinary to retrieve the data.
• long GetInt64(int i): Uses GetOracleDecimal to retrieve the data.
• TimeSpan GetTimeSpan(int i): This method returns a TimeSpan value for the specified INTERVAL DAY TO SECOND column.
• ODPBinary GetOracleBinary(int i): This method returns an ODPBinary object for the specified column.
• ODPDecimal GetOracleDecimal(int i):This method returns an ODPDecimal object for the specified NUMBER column.
• ODPBFile GetOracleBFile(int i): This method returns an ODPBFile object for the specified BFILE column.
• ODPBlob GetOracleBlob(int index): This method returns an ODPBlob object for the specified BLOB column.
• ODPClob GetOracleClob(int index): This method returns an ODPClob object for the specified CLOB column
• ODPDate GetOracleDate(int index):This method returns an ODPDate object for the specified DATE column.
• ODPIntervalDS GetOracleIntervalDS(int index):This method returns an ODPIntervalDS object for the specified INTERVAL DAY TO SECOND column.
• ODPIntervalYM GetOracleIntervalYM(int index):This method returns an ODPIntervalYM object for the specified INTERVAL YEAR TO MONTH column.
• ODPRef GetOracleRef(int index):This method returns an ODPRef object for the specified REF column.
• ODPString GetOracleString(int index):This method returns an ODPString object for the specified column.
• ODPTimeStamp GetOracleTimeStamp(int index):This method returns an ODPTimeStamp object for the specified TimeStamp column.
• ODPTimeStampLTZ GetOracleTimeStampLTZ(int index):This method returns an ODPTimeStampLTZ object for the specified TimeStamp WITH LOCAL TIME ZONE column.
• ODPTimeStampTZ GetOracleTimeStampTZ(int index):This method returns an ODPTimeStampTZ object for the specified TimeStamp WITH TIME ZONE column.
• object GetOracleValue(int index):This method returns the specified column value as an ODPxxx type.
• int GetOracleValues(object[] values):This method gets all the column values as ODPxxx types.
• ODPXmlType GetOracleXmlType(int index):This method returns an ODPXmlType object for the specified XMLType column.
• XmlReader GetXmlReader(int index):This method returns the contents of an XMLType column as an instance of an .NET XmlReader object.

ODPTransaction

ODPTransaction implements the System.Data.IDbTransaction interface, and exposes the following functionality through reflection:

Methods

• void Rollback(string savepointName):This method rolls back a database transaction to a savepoint within the current transaction.
• void Save(string savepointName): This method creates a savepoint within the current transaction. savepointName is case-insensitive and a savepoint is a mechanism that allows portions of a transaction to be rolled back, instead of the entire transaction.

Internals

The ODPAssemblyHelper class is responsible for caching type information and locating the Oracle.DataAccess.dll assembly.

The method GetAssemblyFromProviderFactory tries to access the Oracle.DataAccess.dll assembly using the provider name:

Assembly GetAssemblyFromProviderFactory()
{
    try
    {
        DbProviderFactory factory = 
               DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
        if (factory != null)
        {
            return factory.GetType().Assembly;
        }
    }
    catch (Exception exc)
    {
        LogException(exc, System.Reflection.MethodBase.GetCurrentMethod());
    }
    return null;
}

if GetAssemblyFromProviderFactory fails the library will attempt to load the assembly using the deprecated Assembly.LoadWithPartialName method.

Assembly Assembly
{
    get
    {
        if (assembly == null)
        {
            assembly = GetAssemblyFromProviderFactory();
            if (assembly == null)
            {
                assembly = Assembly.LoadWithPartialName("Oracle.DataAccess");
            }
        }
        return assembly;
    }
}

To avoid repeated calls to Type.GetProperty, Type.GetMethod, Type.GetEvent and Type.GetConstructor the library uses a set of helper classes that retrieves and caches the PropertyInfo, MethodInfo, EventInfo, and ConstructorInfo objects required to interact with the classes implemented in the Oracle.DataAccess.dll assembly.

ODPConnectionHelper()
{
    try
    {
        actionName = Type.GetProperty("ActionName");
        clientId = Type.GetProperty("ClientId");
        clientInfo = Type.GetProperty("ClientInfo");
        databaseDomainName = Type.GetProperty("DatabaseDomainName");
        databaseName = Type.GetProperty("DatabaseName");
        hostName = Type.GetProperty("HostName");
        instanceName = Type.GetProperty("InstanceName");
        moduleName = Type.GetProperty("ModuleName");
        serviceName = Type.GetProperty("ServiceName");
        statementCacheSize = Type.GetProperty("StatementCacheSize");

        failover = Type.GetEvent("Failover");

        clearAllPools = Type.GetMethod("ClearAllPools", BindingFlags.Static);
        clearPool = Type.GetMethod("ClearPool", BindingFlags.Static);
        flushCache = Type.GetMethod("FlushCache");
        openWithNewPassword = Type.GetMethod("OpenWithNewPassword", new Type[] { typeof(string) });
    }
    catch (Exception exc)
    {
        LogException(exc, System.Reflection.MethodBase.GetCurrentMethod());
        throw;
    }
}

The helper classes are singleton objects accessed through a static Instance property:

public static ODPConnectionHelper Instance
{
    get
    {
        try
        {
            if (instance == null)
            {
                lock (synchObject)
                {
                    if (instance == null)
                    {
                        instance = new ODPConnectionHelper();
                    }
                }
            }
            return instance;
        }
        catch (Exception exc)
        {
            LogException(exc, System.Reflection.MethodBase.GetCurrentMethod());
            throw;
        }
    }
}

History

• 30. of July 2013 - Initial posting.
• 1st. of August 2013:
  • Added support for savepoints to ODPTransaction.
  • Added support for ODPBinary, ODPBFile and ODPDecimal to ODPDataReader.
  • Added initial support for ODPBlob and ODPClob to ODPDataReader.
  • A few bug fixes.
• 6. of August 2013:
  • ODPTimeStamp: Implemented constructors and properties.
  • ODPTimeStampLTZ: Implemented constructors and properties.
  • ODPTimeStampTZ: Implemented constructors and properties.
• 1. of October 2013: A few fixes and minor enhancements.