DataLayer.Oracle
DataLayer.Oracle is a basic DataLayer implementation for calling PLSQL Stored Procedures. Built on .NET 5.0 framework and uses Oracle.ManagedDataAccess package for Oracle Provider.
Example Usage
Case #1: Getting Cursors
provider.ExecuteReader method fetchs SYS_REFCURSOR result into given T type which is a class represents the entity for the cursor.
- If your PLSQL stored procedure does not have any Input parameters, you can pass null to the second parameter of ParameterCollection constructor.
- If your PLSQL stored procedure has any Output parameters, you can pass List of Tuple<string, object> as third parameter to ParameterCollection constructor.
OracleProvider provider = new OracleProvider();
string connStr = "User Id=ORACLE_USR;Password=PWD;Data Source=DDB_NAME";
provider.SetConnectionString(connStr);
ParameterCollection param = new ParameterCollection("STORED_PROCEDURE_NAME",
new List<Tuple<string, object>>
{
new Tuple<string, object>("INPUTPARAMETER1", "value1"),
new Tuple<string, object>("INPUTPARAMETER2", "value2")
});
provider.ExecuteReader<T>(ref param);
Case #2: Executing PLSQL Stored Procedures for CRUD Operations
provider.ExecuteBasicStoredProcedure method executes given PLSQL Stored Procedure and commits or rollbacks automatically. If you execute a Function, return parameter will be stored in ResultMessage property of ExecuteBasicStoredProcedure's response.
- If your PLSQL stored procedure does not have any Input parameters, you can pass null to the second parameter of ParameterCollection constructor.
- If your PLSQL stored procedure has any Output parameters, you can pass List of Tuple<string, object> as third parameter to ParameterCollection constructor.
OracleProvider provider = new OracleProvider();
string connStr = "User Id=ORACLE_USR;Password=PWD;Data Source=DDB_NAME";
provider.SetConnectionString(connStr);
ParameterCollection param = new ParameterCollection("STORED_PROCEDURE_NAME",
new List<Tuple<string, object>>
{
new Tuple<string, object>("INPUTPARAMETER1", "value1"),
new Tuple<string, object>("INPUTPARAMETER2", "value2")
});
provider.ExecuteBasicStoredProcedure(ref param);
Also, you can use ExecuteStoredProcedure method for your CRUD operations which also returns cursor. Cursor will be fetched into List of given T type.
OracleProvider provider = new OracleProvider();
string connStr = "User Id=ORACLE_USR;Password=PWD;Data Source=DDB_NAME";
provider.SetConnectionString(connStr);
ParameterCollection param = new ParameterCollection("STORED_PROCEDURE_NAME",
new List<Tuple<string, object>>
{
new Tuple<string, object>("INPUTPARAMETER1", "value1"),
new Tuple<string, object>("INPUTPARAMETER2", "value2")
});
var result = provider.ExecuteStoredProcedure<T>(ref param);
if (string.IsNullOrEmpty(result.ResultMessage))
{
foreach (var item in result.List)
{
Console.WriteLine("...");
}
}
else
{
Console.WriteLine("Error occured when executing PLSQL SP: " + result.ResultMessage);
}