OracleDatabase

Connection strings

Net Framework version of the component reads connection strings from app.config file. Until version 1.0.3 Core version does too, but from 1.0.4 onwards Core version reads connection strings from appsettings.json file's ConnectionString section. In both cases the first found connection string is selected by default. This can be overwritten by static parameter OracleDatabase.ConnectionStringName or by a parameter while creating the OracleDatabase object. When no connection string can be found found from settings, then OracleDatabase.UseRawConnectionString is set to true and connection string is left empty.

Object creation


        new OracleDatabase()

        new OracleDatabase(string connectionString)

        new OracleDatabase(byte connectionTryCount)

        new OracleDatabase(string connectionString, byte connectionTryCount)

When OracleDatabase.UseRawConnectionString is set to true, then the constructor treats connectionStringName (or when left empty, then OracleDatabase.ConnectionStringName) as actual connection string data instead of a reference to a key in the connection string collection.

While trying to rapidly create lots of database components (e.g. load testing a REST) using XE versions of Oracle database, then opening the connection might fail with ORA-12516 error. Then the component retries connection opening, by default 100 times. Retry count can be changed with OracleDatabase.ConnectionTryCount static parameter or while creating the object.

Querying


        using (OracleDatabase database = new OracleDatabase())

        
            OracleCommand command = new OracleCommand("select * from DUAL");

            OracleCommand command = database.NewSPCommand("MY_PROCEDURE");

            

            command.Parameters.Add("I_INTEGER", 1);

            command.Parameters.Add("O_INTEGER", null, ParameterDirection.Output);

            command.Parameters.Add("O_CURSOR", OracleDbType.RefCursor, ParameterDirection.Output);

            

            DataSet data = database.QuerySet(command);

            DataTable data = database.QueryTable(command);

            DataRow data = database.QueryRow(command);

            

            int rowCount = database.NonQuery("update MY_TABLE set A = 1");

QuerySet returns all the DataTables of data the query produces. If there are other output parameters than RefCursor, they will be returned as the first DataTable of the set and it will be named _OUTPUT. Each RefCursor will be returned as separate DataTable in the DataSet and are named by their parameter name.

There are two short-hand methods: QueryTable will return the first table of a QuerySet and QueryRow will return the first row of the first table.

If OracleDatabase.StripPrefixes is true (it is by default), then DataSet's column names for output parameters will be stripped from prefixes defined by OutputPrefix (by default O_) and InputOutputPrefix (by default IO_) properties. E.g. output parameter named O_FIELD or IO_FIELD will become a column named FIELD (with default prefixes). This is also applied to DataTable names for RefCursor parameters.

If OracleDatabase.ForceLocalDate is true (it is by default), then all input date values are converted into local time. This is done by using DateTime object's ToLocalTime method or when value's DateTimeKind is Undefined, then the value is assumed to be local time.

If there is an error, returned data object will be null and LastStatusCode property will be set to 500 except with Oracle error ORA-01403 (no data). In this case LastStatusCode will be set to 204. In both cases LastException property will contain the actual thrown error, and if it was a database error, then Oracle's error code is stored into LastErrorCode. Also the method defined by OracleDatabase.ErrorLogger property will be called.

If there isn't an error, then LastStatusCode property will be set to 200, and LastException and LastErrorCode properties are set to null.

NonQuery method will return what Oracle's data access returns, unless there is an error. With an error, the return value will be OracleDatabase.NonQueryFail.

If OracleDatabase.BindParametersByName is true (it is by default), then procedure parameters are bound by name instead of by their order.

Transactions


        using (OracleDatabase database = new OracleDatabase())
        
            database.StartTransaction();

            database.Commit();

            database.Rollback();

Active transaction is automatically added into queries. Only one active transaction is supported.

Data helpers

OracleDatabase.BulkSet


        OracleDatabase.BulkSet(OracleCommand command, DataTable data, string[] columns = null)

        OracleDatabase.BulkSet(OracleCommand command, List<T> data, string[] columns = null)

Inserts provided data from a DataTable or a list of classes into the command's parameters for bulk insert. Columns array can be used to limit which data is inserted.

From the list of classes readable public fields and properties are converted into column names using reversed CamelCase, where an underscore is added before every uppercase letter, and finally the whole name is converted to uppercase: e.g. ColumnName → COLUMN_NAME. Parameter names will be added with OracleDatabase.InputPrefix (by default I_). E.g. column named FIELD will become a parameter named I_FIELD (with default input prefix).

OracleDatabase.BulkFill


        OracleDatabase.BulkFill(OracleCommand command, string parameterName, object parameterValue)

        OracleDatabase.BulkFill(OracleCommand command, string parameterName, OracleDbType parameterType, object parameterValue)

        OracleDatabase.BulkFill(OracleCommand command, string parameterName, OracleDbType parameterType, int parameterSize, object parameterValue)

Duplicates a single parameter value for bulk insert to match row count of a BulkSet.

NOTE: BulkFill works only after BulkSet or if command's ArrayBindCount property has been set manually.

OracleDatabase.ToClass


        T = OracleDatabase.ToClass<T>(DataRow dataRow)

Converts a DataRow into a class, where column names are mapped into class property names applying CamelCase to column names (e.g. COLUMN_NAME → ColumnName).

OracleDatabase.ToList


        List<T> = OracleDatabase.ToList<T>(DataTable dataTable)

Converts a DataTable into a list of classes, where column names are mapped into class property names applying CamelCase to column names (e.g. COLUMN_NAME → ColumnName).


        List<T> = OracleDatabase.ToList<T>(DataTable dataTable, int index)

        List<T> = OracleDatabase.ToList<T>(DataTable dataTable, string column)

        List<KeyValuePair<K, V>> = OracleDatabase.ToList<K, V>(DataTable dataTable, int keyIndex, int valueIndex)

        List<KeyValuePair<K, V>> = OracleDatabase.ToList<K, V>(DataTable dataTable, string keyColumn, string valueColumn)

Converts a DataTable into a list specified data type.

OracleDatabase.ToDictionary


        Dictionary<K, V> = OracleDatabase.ToDictionary<K, V>(DataTable dataTable, int keyIndex)

        Dictionary<K, V> = OracleDatabase.ToDictionary<K, V>(DataTable dataTable, string keyColumn)

        Dictionary<K, V> = OracleDatabase.ToDictionary<K, V>(DataTable dataTable, int keyIndex, int valueIndex)

        Dictionary<K, V> = OracleDatabase.ToDictionary<K, V>(DataTable dataTable, string keyColumn, string valueColumn)

Converts a DataTable into a dictionary where keys come from a single column and values are either a value from another column or rows converted into the specified class.