BlueGroup.ClsDBSupport.Oracle

BlueGroup.ClsDBSupport.Oracle is an open-source library used to access and operate on Oracle DataBases. It is a wrapper for the BlueGroup.ClsDBSupport.Base package, specific for Oracle DataBases.

How To Use

Just add the package to your project via NuGet Package Manager.
The DataBase Connection String must be provided as a parameter of the constructor. If you use an Alias for the DataSource, you must pass the tnsnames.ora path as a parameter of the specific constructor, otherwise the client won't be able to resolve the alias.

Methods

Query

This method is used to retrieve multiple records from any DB Table. Only SELECT statements should be used as queries. Results of the query are returned as a DataSet inside a specific structure.

Query

This method is used to retrieve multiple records from any DB Table. Only SELECT statements should be used as queries. Results of the query are returned as a List inside a specific structure. Note that, in this case, the DataBase columns must have the same name of the object's properties, or the object's properties must be marked with the attribute DbColumn("COLUMN_NAME") or DbNullableColumn("COLUMN_NAME") for nullable columns.

QueryJson

This method is used to retrieve multiple records from any DB Table. Only SELECT statements should be used as queries. Results of the query are returned as a List<Dictionary<string, object>> inside a specific structure, together with the correspondent Json string. Each fetched DataBase row is represented by a Dictionary<string, object> object, where the dictionary key is the DB column name. The Json string is the Json serialization of the List<Dictionary<string, object>> object.

ExecCommand

This method is used to perform all the operations on DB objects and records (INSERT, DELETE, UPDATE, ALTER TABLE, etc...).

ScalarQuery

This method is used to retrieve a single value from a DB Table (the value of a single column of a Table for a specific record); the value is returned as a string inside a specific structure.

ScalarQuery

This method is used to retrieve a single record from a DB Table; the value is returned as an object of type T inside a specific structure. Note that, in this case, the DataBase columns must have the same name of the object's properties, or the object's properties must be marked with the attribute DbColumn("COLUMN_NAME") or DbNullableColumn("COLUMN_NAME") for nullable columns.

ExecCommandList

This method is used to perform several commands in a single transaction. If all commands have successful response, the transaction is committed, while if one (or more) fail, the transaction is rolled back. Commands must be passed to the method as a List<string> and they must be enlisted in the desired order of execution.

ExecStoredProcedure

This method is used to execute a stored procedure. If the stored procedure returns data, the results of the query are returned as a DataSet inside the return structure; if the stored procedure does not return data, the number of rows involved in the non-query is returned inside the return structure.

Return Values

All methods return a specific return structure, which inherits from a generic ReturnStruct class. The properties of the generic ReturnStruct class (which are inherited by ALL specific return structures) are:

• RetVal: it can assume either SUCCESS or ERROR values, according to the result of the query/command;
• Message: if the result is ERROR, it contains the error message;
• LastCommand: contains the last executed command. If you are trying to execute a list of commands and something goes wrong, it contains the command that resulted in the error.

The specific return structures are the following (with the additional specific properties):

ReturnStructQuery

For the Query method.

• RetDS: a DataSet containing the results of the query;
• RowsFetched: the number of rows fetched by the query. It is equal to RetDS.Tables[0].Rows.Count.

This class also has a CheckExistsResult() method, that returns true if the RetDS object is not null, contains at least one table with at least one row, false otherwise.

ReturnStructQuery

For the Query method.

• RetList: a List<T> containing the results of the query;
• RowsFetched: the number of rows fetched by the query. It is equal to RetList.Count.

This class also has a CheckExistsResult() method, that returns true if the RetList object is not null and contains at least one element, false otherwise.

ReturnStructQueryJson

For the QueryJson method.

• RetDictionary: a List<Dictionary<string, object>> containing the results of the query;
• JSonString: the Json serialization of the RetDictionary property;
• RowsFetched: the number of rows fetched by the query. It is equal to RetList.Count.

This class also has a CheckExistsResult() method, that returns true if the RetDictionary object is not null and contains at least one element, false otherwise.

ReturnStructExecCommand

For the ExecCommand method.

• RowsAffected: the number of rows affected by the executed command.

ReturnStructScalarQuery

For the ScalarQuery method.

• ResultValue: a string containing the single result of the query.

ReturnStructScalarQuery

For the ScalarQuery method.

• ResultValue: a T object which is the result of the query.

ReturnStructExecCommandList

For the ExecCommandList method.

• RowsAffected: a List<int> containing the number of rows affected by each executed command in the corresponding list.
• TotalRowsAffected: the total number of rows affected by all executed commands.

ReturnStructStoredProcedure

For the ExecStoredProcedure method. It is a combination of the ReturnStructQuery and ReturnStructExecCommand classes, because a stored procedure can either return data or not.

• RetDS: a DataSet containing the results of the query;
• RowsFetched: the number of rows fetched by the query. It is equal to RetDS.Tables[0].Rows.Count.
• RowsAffected: the number of rows affected by the executed command.

This class also has a CheckExistsResult() method, that returns true if the RetDS object is not null, contains at least one table with at least one row, false otherwise.