Re: Subject:Writing a db independent data access layer with DAAB:How?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Sahil Malik (contactmethrumyblog_at_nospam.com)
Date: 02/25/05 

Date: Fri, 25 Feb 2005 15:34:09 -0500

My 2 cents -
http://codebetter.com/blogs/sahil.malik/archive/2005/02/25/56043.aspx

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/

"sedefo" <sedefo@discussions.microsoft.com> wrote in message
news:142132CE-AA74-4B86-BAD6-9BFCFBBDC2CC@microsoft.com...
> 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=DatabaseFactory.CreateDatabase();
> db.ExecuteDataSet("SPR_GetCities", countryCode);
>
> // or the following code may be used instead
> DBCommandWrapper
cmdWrapper=db.GetStoredProcCommandWrapper("SPR_GetCities");
> cmdWrapper.AddInParameter("@pCountryCode", DbType.Int32, countryCode);
> db.ExecuteDataSet(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=DatabaseFactory.CreateDatabase();
> db.ExecuteDataSet("SPR_GetCities", countryCode, DBNull.Value); // the
> second parameter value DBNull.Value is for the cursor parameter
>
> // or the following code may be used instead
> DatabaseCommandWrapper
> cmdWrapper=db.GetStoredProcCommandWrapper("SPR_GetCities");
> cmdWrapper.AddInParameter("pCountryCode", DbType.Int32, countryCode);
> cmdWrapper.AddOutParameter("pMyCursor", DBType.Object, DBNull.Value);
> db.ExecuteDataSet(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?
>

Relevant Pages