Data Access Layer Design

[JP Green]

Hi guys,

Just came back to ASP.NET after a couple year hiatus and I have a
feeling that time has passed me by on some of this stuff re:
application design.

My biggest gripe as a programmer is how we handle the data access
layer- basically we have a class called DataAccess.cs with a bunch of
methods that return what we need to wherever is calling it. We're
using the data access app blocks, and here
is an example:

public string GetUserStatus( int UserID)
{
// Create the Database object, using the default database
service.
// The default database service is determined through
configuration.
Database db = DatabaseFactory.CreateDatabase();

// Set up the command: The stored procedure name
string sqlCommand = "usr_GetUserStatus";
DBCommandWrapper dbCommandWrapper =
db.GetStoredProcCommandWrapper(sqlCommand);

// Add in the parameter values
dbCommandWrapper.AddInParameter("@StudentID", DbType.Int32,
StudentID);
dbCommandWrapper.AddOutParameter("@Status", DbType.String,
4);

db.ExecuteNonQuery(dbCommandWrapper);

// Return the statusID
string result = (string)
dbCommandWrapper.GetParameterValue("@Status");

return result;
}

Every one of these methods repeats most of these lines! It's ugly!
It's time consuming! It sucks. The sprocs are kinda verbose as well.
Having to set up a different method for each little thing we
create/read/update/delete and then having to
bubble that up through 4 different layers (db->sproc->data
layer->business logic or whatever) is really really time consuming as
well as ugly and difficult to manage (this app isn't doing a ton of
stuff but we still probably have 50 or 60 sprocs.)

What's the solution? This sucks, right? Is an O/R mapper the
solution? Something like nHibernate? Are all my problems gonna
magically be solved when we move to .NET 2.0? Please help, I just
don't know the right way to attack this.

 

[Mr Newbie]

//
What's the solution? This sucks, right? Is an O/R mapper the
solution? Something like nHibernate? Are all my problems gonna
magically be solved when we move to .NET 2.0? Please help, I just
don't know the right way to attack this.
\\

I dont think ASP.NET 2.0 will make much difference when designing multi tier
systems. The fundementals of that requirement wont have changed with the new
revision.

I know what you mean about the wades of code one has to write, and its also
difficult sometimes to persaude yourself to not go directly to the DataLayer
when it seems to have no benefit to have to write something in the Business
Logic to redirect your request just because it 'Should' go through it.

I'm still pretty new to this so, in some ways Im a bit similar to your with
your long holiday, but Im sure perseverance will improve things.

In short though, there is no easy way to do this stuff if your going to do
it right ( or at least to the de Facto standard ) then you jsut have to put
in the work.

That in itself is difficult when you only want to create a few records and
find yourself coding and application that looks as if its going to be able
to perform remote brain surgery.

;-D

 

[tdavisjr]

Code generators, Code generators, Code generators. You got it!
Something like nHibernate or CodeSmith or Codus is the way to go. I
hate coding this repetative code as well. Using these tools is the way
around that.

 

[Ersin Gençtürk]

"Every one of these methods repeats most of these lines! It's ugly!"


if you are using ms sql server then try using Microsoft Data Access
Application Block for .NET

http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
regards,

ersin gençtürk

 

[tdavisjr]

He is using the DAAB from Microsoft which is found in their Enterprise
Library. But, using that cuts out some of the low level database access
code, however, writing the code to access this library can still be
repetitive.

 

[Jason Kester]

Try this as an alternative. Spend a month creating your dream
GenericDataLayer that will accept calls like:

GenericDataLayer.MagicallyInsert("CustomerTable", 12, "sammy", true,
"notes...");

Now try debugging into it when it breaks!

The beautiful thing about using code generators to create all that
boilerplate wrapper code that you hate so much is that it is completely
readable and completely debugable. If you try to hand it a param of
the wrong type, it will break on the exact line that you need to deal
with.

Trust me. I once inherited a Giant Magic DataLayer that worked "most
of the time". After a week of sifting through ArrayLists of ArrayLists
of Objects trying to find the spot where it was miscasting my nullable
integer, I started quietly writing some CodeSmith templates that
produced output surprisingly similar to the code you posted.

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/