proper way to abstract db access?

[Guest]

Hello all,
can someone possibly clear up what the 'proper' way to abstract DB access
is? (Using C#, though it shouldnt matter)

For example my asp page calls a web service that has an authenticate method
that uses a username/pass table in a DB..

I set up a DB class that connects to the DB and has a public OleConnection.
The webservice builds the olecommand and runs it against that public
OleConnection, but that somehow seems wrong. Sticking every possible
olecommand in my db connection class also doesnt make any sense.

So whats the right layout? Im trying to build it so that its easy to
implement connection pooling and caching later if i need to, and so I can
easily port to Oracle/MS SQL.

 

[Yunus Emre ALPÖZEN [MCAD.NET]]

You can implement a webservice or ATL to access database. But INMH proper
way is using a Service Oriented approach. My advice u to implement it using
Microsoft .NET Remoting technologies and a server activated approach and
singleton pattern. You can easily built up a caching mechanism on it.

 

[Scott Allen]

You might want to look at the source code and examples for the data
access block in Enterprise Library [1].

[1]
http://www.microsoft.com/downloads/...7A-9534-4349-8038-D56B38EC394C&displaylang=en

 

[Brock Allen]

This is a hard question to answer. You could code everything in every single
method, but as you might suspect, this ends up with a lot of duplicated code.
Even if every method ultimately does a different SQL operation, there is
the common connection setup and teardown. So you think about ways to abstract
and refactor this common code. Scott mentioned the MS DataAccess Block. That
or an idea like that (personally I don't use it, as I have my own that I
prefer over it) is a good first start. If that's sufficient, then great.
But when if the SQL starts to be duplicated method by method... Well, then
that's when you start to look at factoring out that bit too. This is when
you look to move all of your data access out form the page and/or webservice
and put it all into its own assembly. This then hides the data access behind
an API that just returns you the data. You let the data access code sort
out the details. Once you've done this, then that's an opportunity to cache
data in the data access layer (if possible/appropriate), as the UI/service
code doesn't know (or care) how the data was acquired (BTW, I'm using data
access layer loosly here, as some people have very specific connotations
to that term (DAL), but it is certainly related). Once you've factored out
the work into a seperate location, then it's much easer to reuse that work.

Anyway, there's nothing that says you have to build your systems like this...
It's just something to consider.