Generic import service for multiple Database providers.

[ink]

Hi all,

My company does things in a very specific way. Meaning we never query the
database directly, we always use Stored Procedures.

What i would like is to develop one data provider class for database
communication that can easily be added onto as time goes buy.
At the moment it needs to be able to import into SQL and Oracle, but in the
future DB2 and some others.

I want to allow the user of my Import Service to specify the connection
string and Database provider and then it should go of and do the importing
it needs to do.

The import Store Procedures will be the same on all databases.

i am not to sure how to go about this i found a news group post
http://www.dotnet247.com/247reference/msgs/31/156318.aspx
but again i am not sure if this is the best way of going about this.

The things i need to do is as follows.
Run Stored Procedures and return values from them.
Use Transactions to role back after errors.

if someone has seen a simple example of how best to do this i would be
grateful for any help.

Thanks,
ink

[Marc Gravell]

Enterprise library data access application block provides factory
implementation based on the provider from the connection-string
setting (in app.config/web.config):
http://msdn2.microsoft.com/en-us/library/aa480458.aspx

for transaction support, I recommend TransactionScope (part of code
ado.net in 2.0)
http://msdn2.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

Marc

[sloan]

Take a look here:
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/c5a90751a2a122be

I would implement the Factory Pattern AND use the EnterpriseLibrary.

However, I would change

public interface IEmployeeData
{ }

to

public abstract class EmployeeDataBase
{}

and then

public class EmployeeSqlServerData : EmployeeDataBase
{

}

(again, look at the post .. and you'll see what's going on)

Then, you can implement most methods in the BASE class.

However, you make them overrideable, when that quirk arises ... and (as an
example) your import for Oracle is a little bit quirkier than the one for
SqlServer...you'll be prepared.

And you can have different stored procedures names among the various RDBMS's
as well.

Doesn't oracle allow for "overloaded" stored procedures....something I wish
sql server had.

.......

My advice is to plan for a caveat NOW, using a design pattern, instead of
writing some crap IF/ELSE logic when the caveat arises.
Cuz something will arise, I'm 99.99999% sure.

..

Here is a blog to help understand the simple factory a tad bit better:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!126.entry

I looked at the other post (the one you mention).... and yeah... I think the
EnterpriseLibrary does most of that for you....
And 99.9999 you could probably just get by with the EnterpriseLibrary. But
you'd have to have the exact same stored procedure names among all N number
of RDBMS.

I would still code up the factory pattern.... (and still use the
EnterpriseLibrary). You may never have to override a (base) method from the
base class.....but its nice to know you could if something goes screwy.

"ink" <i...@notmyemail.com> wrote in message
news:uVdwpIiF...@TK2MSFTNGP06.phx.gbl...