472,780 Members | 1,717 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,780 software developers and data experts.

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

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
select CityCode, CityName
from Cities
where CountryCode = @pCountryCode
-- ORACLE Procedure
type RefCursorType is ref cursor;
CREATE PROCEDURE SPR_GetCities (pCountryCode in number, pMyCursor out
open pMyCursor for
select CityCode, CityName
from Cities
where CountryCode = pCountryCode;
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_Get Cities");
cmdWrapper.AddInParameter("@pCountryCode", DbType.Int32, countryCode);
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
cmdWrapper=db.GetStoredProcCommandWrapper("SPR_Get Cities");
cmdWrapper.AddInParameter("pCountryCode", DbType.Int32, countryCode);
cmdWrapper.AddOutParameter("pMyCursor", DBType.Object, DBNull.Value);

<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
Jul 21 '05 #1
0 5250

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Joseph | last post by:
Hi I'm writing a commercial program which must be reliable. It has to do some basic reading and writing to and from files on the hard disk, and also to a floppy. I have foreseen a potential...
by: RO|_F | last post by:
Hello, Our salesreps will get an access 2000 database on their laptops, which contains business) private data. Are there programs which act as kind of front end , e.g. only through the (3rd...
by: sedefo | last post by:
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...
by: Jan | last post by:
Have an SQL create/import script. Running this on SQL would make it create a table with some values. Can Access2003 somehow use such and SQL script? I went into SQL query view to try it out, but...
by: cheyennemtnman | last post by:
In MS Access how can I skip to the next page on section break if section can not be printed entirely on the preceding page. Say if I had 20 print lines on the page and I have section 1 with 4...
by: larry | last post by:
I am working on a DB for family data, and in this application the data spans variable amount of rows in multiple tables (one for the adults data, one for "family", one for the kids, another for...
by: amit | last post by:
assume we have 10,000 cities. imagine those distance calculating tables, where city A (row) to city Z (col) would equal a float value. city Z (row) to city Z(col) would be 0. The only problem is...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.