469,268 Members | 962 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,268 developers. It's quick & easy.

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/247referenc...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

Oct 24 '07 #1
2 2175
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/lib...tionscope.aspx

Marc

Oct 24 '07 #2

Take a look here:
http://groups.google.com/group/micro...a90751a2a122be

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" <in*@notmyemail.comwrote in message
news:uV**************@TK2MSFTNGP06.phx.gbl...
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/247referenc...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

Oct 26 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by .Net Newbie | last post: by
16 posts views Thread by tshad | last post: by
reply views Thread by D Lester | last post: by
4 posts views Thread by Ram | last post: by
33 posts views Thread by JamesB | last post: by
reply views Thread by sravan_reddy001 | last post: by
1 post views Thread by Michael Nemtsev [MVP] | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.