I ran into this Microsoft Patterns & Practices Enterprise Library while i
was researching how i can write a database independent data access layer. In
my company we already use Data Access Application Block (DAAB) in our .Net
projects. We use SqlHelper in SQL based projects, and OracleHelper in Oracle
based ones. OracleHelper was not published officially by Microsoft as part of
the DAAB but it was given as a helper code in a sample .Net project using
Oracle database on the msdn site. OracleHelper has the same functionality as
the SqlHelper.
But now we need to find a solution to write a data access layer which will
be able to work both in SQL and Oracle. Many articles suggest using the
Factory design pattern to write different sets of data access components,
each of which will be used with a specific database. These different sets of
components should be implementing a common interface or inheriting a common
abstract class which shows the functionality of that specific data access
class. For example, if you need to have a "Customer" data access object, you
first write ICustomerData interface and then write one
CustomerData object with methods written appropriately to work on SQL
database (using SqlClient data provider) and another CustomerData object with
methods written appropriately to work on Oracle database (using OracleClient
data provider). A factory class creates an instance of the
appropriate data access object at run-time, deciding whether CustomerData
for SQL or CustomerData for Oracle will be used based on a configuration
parameter. The bussiness layer objects create the data acess objects with the
help of the factory classes and use them through interfaces which the data
access classes implement. I think this is a clean way of seperating data
access for each type of data source that might be used. BUT i also think that
it's quite a lot of work which requires significant effort in keeping all the
data access layer classes for different databases up to date. For example, if
you need to add a new method in a data access class, you have to add it first
to the interface or the base abstract class and then to each data access
class for each different database. Because of this overhead, i suppose there
must be a way for using different databases with just one set of data access
classes. With this in mind, i started to write a DBHelper class which made
use of SqlHelper and OracleHelper classes. This DBHelper has the same methods
as SqlHelper, with one difference. The parameters of the methods in DBHelper
or the return values of the methods are not database specific, instead they
are of the common interface types which the provider specific classes
implement. DBHelper calls either the appropriate SqlHelper or the
OracleHelper method.
However, when i found the Microsoft Enterpise Library and read that the new
DAAB in the library provides a database transparent way of doing database
operations i got quite happy. I examined the quick start sample code and the
documentation but couldn't find the answer to a question in my mind.
Here is the question after this LOOONG entrance:
Result set returning stored procedures in SQL return the result of a SELECT
statement. They do not have any output parameter for the result set.
However in Oracle, stored procedures have "out" parameters of type ref
cursor to return a record set. For example, a stored procedure returning the
cities in a given country would be written as following:
-- SQL Procedure
CREATE PROCEDURE SPR_GetCities @pCountryCode int
AS
select CityCode, CityName
from Cities
where CountryCode = @pCountryCode
GO
-- ORACLE Procedure
type RefCursorType is ref cursor;
CREATE PROCEDURE SPR_GetCities (pCountryCode in number, pMyCursor out
RefCursorType)
begin
open pMyCursor for
select CityCode, CityName
from Cities
where CountryCode = pCountryCode;
end;
As seen above, the number of parameters of the two procedures are not the
same. How can a single data access method, let's call it GetCities(), be
written to call the appropriate procedure? If we are to use the SQL
procedure, we can write the GetCities() method as following:
public DataSet GetCities(int countryCode){
Database db=DatabaseFact ory.CreateDatab ase();
db.ExecuteDataS et("SPR_GetCiti es", countryCode);
// or the following code may be used instead
DBCommandWrappe r cmdWrapper=db.G etStoredProcCom mandWrapper("SP R_GetCities");
cmdWrapper.AddI nParameter("@pC ountryCode", DbType.Int32, countryCode);
db.ExecuteDataS et(cmdWrapper);
}
However, to call the stored procedure in the Oracle database, the code
should be something like this:
public DataSet GetCities(int countryCode){
Database db=DatabaseFact ory.CreateDatab ase();
db.ExecuteDataS et("SPR_GetCiti es", countryCode, DBNull.Value); // the
second parameter value DBNull.Value is for the cursor parameter
// or the following code may be used instead
DatabaseCommand Wrapper
cmdWrapper=db.G etStoredProcCom mandWrapper("SP R_GetCities");
cmdWrapper.AddI nParameter("pCo untryCode", DbType.Int32, countryCode);
cmdWrapper.AddO utParameter("pM yCursor", DBType.Object, DBNull.Value);
db.ExecuteDataS et(cmdWrapper);
}
<b>Question #1:</b> So, both the number of parameters and the name of the
parameters differ in these methods. Stored procedure parameter names in SQL
start with the character @, however Oracle procedure parameters don't have
this parameter token. Can someone tell me how we can write a single
GetCities() method which will handle both the SQL and the Oracle procedure?
<b>Question #2:</b> In Oracle, we can write stored procedures in packages.
Let's say we have Pck_Cities package in Oracle which includes the
SPR_GetCities procedure. We should be calling the procedure as
Pck_Cities.SPR_ GetCities in Oracle whereas we should be calling it as
SPR_GetCities in SQL. How can we handle this situation of differing names?
I will really appreciate if someone can help me clarify these questions. As
i mentioned before, i'm against the idea of writing different data access
layers for different databases. There must be a way to have a single data
access layer which can work in different databases with this new DAAB in
the Enrerprise Library. After all, isn't it the whole idea behind the new
DAAB?
