Data Access Layer

[Mike]

Hi,

I am trying to design a generic data access layer that can connect to
multiple data sources (MS SQL, Access...). I have come across some issues.
I have looked at Microsoft's Data Access Layer and have noticed it made all
the methods static. Is this a good idea? In my layer I have an interface
that each of my data helper classes implement with these signatures:

IDataReader ExecuteReader(string connString, CommandType commandType,
string commandText, params IDbDataParameter[] commandParameters);

object ExecuteScalar(string connString, CommandType commandType,
string commandText, string[] tableNames, params IDbDataParameter[]
commandParameters);

void FillDataSet(string connString, DataSet dataSet, string sql);

DataSet ExecuteDataset(string connString, CommandType commandType,
string commandText, string[] tableNames, params IDbDataParameter []
commandParameters);

and so on...

I am trying to decide is it really worth designing a layer that can handle
different data sources. I pass enum type to my data access layer and it
creates the correct data components and in each method signature I pass the
correct connection string. I am just wondering what others are doing.

Thanks

 

[William Ryan eMVP]

Mike:

Here's my two cents...

I think that if at all possible, using implementation specific providers
should be used unless there is a really good reason not to. I've seen some
cool implementations of totally generic data layers, but I'm not sure I'd
use them in most scnearios. For one thing, it's fairly common to upgrade
from Access to Sql Server or Oracle, but other than that, Switching DB's
doesn't usually happen very often in production. There are a whole lot of
shops who hestitate to upgrade versions of the same db let alone shift to
another system very often. XML and web services can push back data in an
agnostic fashion so if layers are seperated correctly, transitions shouldn't
be that big of a deal in comparison to everything else and in many
instances, the client code could be totally oblvious to the change.

I think using a Factory pattern to build your data objects and using the
same concept with your enums to specify what you need would get you to the
same place (perhaps a little more coding up front, but well worth it if you
need something generic). This effectively gives you the best of both
worlds. In most instances, you are returning a scalar value, a datatable or
a dataset and all of these can be sent back without the client knowing or
caring where the data came from. This is effecitvely what the MS DAAB does
for you but it can certainly be expanded. If it were me (rather, what I
did) was build in some support for OleDb, Oralce and SQLClient and spend my
time building in multithreaded functionality.

HTH,

Bill

Hi,

I am trying to design a generic data access layer that can connect to
multiple data sources (MS SQL, Access...). I have come across some issues.
I have looked at Microsoft's Data Access Layer and have noticed it made all
the methods static. Is this a good idea? In my layer I have an interface
that each of my data helper classes implement with these signatures:

IDataReader ExecuteReader(string connString, CommandType commandType,
string commandText, params IDbDataParameter[] commandParameters);

object ExecuteScalar(string connString, CommandType commandType,
string commandText, string[] tableNames, params IDbDataParameter[]
commandParameters);

void FillDataSet(string connString, DataSet dataSet, string sql);

DataSet ExecuteDataset(string connString, CommandType commandType,
string commandText, string[] tableNames, params IDbDataParameter []
commandParameters);

and so on...

I am trying to decide is it really worth designing a layer that can handle
different data sources. I pass enum type to my data access layer and it
creates the correct data components and in each method signature I pass the
correct connection string. I am just wondering what others are doing.

Thanks

 

[Maxim V. Karpov]

Mike,
I would recommend you use DAAB!
http://ipattern.com/simpleblog/PermLink.aspx?entryid=24

Maxim

[www.ipattern.com do you?]

 

[Garry Davlin]

Mike,

For some ideas, you might also want to take a look at the Data Access
Object pattern from:

http://java.sun.com/blueprints/patterns/DAO.html

If you search the web, you will find other descriptions of the same
thing. It uses the factory pattern to serve up an appropriate data
layer object based on a configuration file setting, etc.

There are some drawbacks to this pattern - lots of code, requires a
lot more design work, and changes to existing code can become a bit
tedious. It does separate out the exact implementation from the
business classes, though.

This pattern does not rely on anything specific to Java - it works
well on both C# and VB.NET (or any language with classes and
interfaces).

Microsoft also has a lot of information on the data tier at their
Patterns and Practices site:

http://www.microsoft.com/resources/practices/

HTH
-Garry