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.
-Brock
DevelopMentor
http://staff.develop.com/ballen 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.
