ASP.net data layer design- I am a walking WTF
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:
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:
<font size="2"> 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";
// Add in the parameter values
dbCommandWrapper.AddInParameter("@StudentID", DbType.Int32, StudentID);
dbCommandWrapper.AddOutParameter("@Status", DbType.String, 4);
// Return the statusID
string result = (string) dbCommandWrapper.GetParameterValue("@Status");
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 what to do.
We use basically the same architecture, with maybe a little more OO rigor.
The DataAccess class is just a handy wrapper for ADO; it has no business logic.
Our biz layer classes typically have a ctor overload that accepts a DataSet or DataRow, and instantiates itself from that.
<font size="2">public class Student : IStudent
private int _studentId;
private string _studentName;
private StudentStatus _studentStatus; // enum
private Student(DataRow row)
_studentId = (int)row["StudentId"];
_studentName = row["StudentName"].ToString();
_studentId = (StudentStatus )Enum.Parse(row["StudentStatus"].ToString());
public static Student GetStudent(int studentId)
using(DataAccess da = new DataAccess())
DataSet ds = da.Execute("GetStudentById", new
return new Student(ds.Tables.Rows);
/* public IStudent members, properties, etc. */
Not O/R mapped, but conveniently abstracted and reasonably easy to maintain.
<FONT face=Arial size=2>Hi There, </FONT>
<FONT face=Arial size=2>I found your above post very interesting, a quick question if i may! </FONT>
<FONT face=Arial size=2>Do you have a public constructor at all ? Are you just relying on the static method to provide an instance of the class? Is this a common design pattern?</FONT>
<FONT face=Arial size=2>I ask as this kind of object is likely to be very useful in situations i am encountering! If you could offer me some advice as to why this method is so useful, i would be most grateful!</FONT>
<FONT face=Arial size=2></FONT>
<FONT face=Arial size=2>Thanks,</FONT>
<FONT face=Arial size=2>MrW</FONT>
Just FYI, for original poster, I have been happy with LLBLGen Pro as an
ORM for about a year now. I haven't tried NHibernate yet. LLBLGen Pro
basically eliminates the need for manual SQL in most cases, and the
simplest uses of it are trivial. It does create a lot of data transfer
for simple one-field queries like you describe, but "premature
optimization is the root of all evil." And, for complicated queries,
LLBLGen Pro exposes stored procedures directly as functions that return
datasets. It's about $250US or something. It is really nice. (You can
try out LLBLGen (not Pro), but it is a completely different piece of
Yah. I only distantly remember doing any manual ADO, though there are still stored procedures floating around.
And wrapping entity collections for reporting tools (C1WebReport) is a bitch.
<font face="Arial" size="2">Do you have a public constructor at all ? Are you just relying on the static method to provide an instance of the class? Is this a common design pattern?</font>
No public ctor. the static "factory" method hands out all instances.
This gives me the opportunity to perform additional checks (security, etc.) and to return a different type (a subclass perhaps...) and to catch exceptions and return null instead of throwing the exception to the caller.
The caller's syntax is a little more verbose, but I like the flexibility.