ASP.net data layer design- I am a walking WTF

TsaiChen

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 what to do.

John_Smallberries

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.

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
SqlParameter("@StudentId", studentId));

 return new Student(ds.Tables[0].Rows[0]);

 }

 }

 /* public IStudent members, properties, etc. */

}



Not O/R mapped, but conveniently abstracted and reasonably easy to maintain.

MrWilder

Hi There,

I found your above post very interesting, a quick question if i may!

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?

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!

Thanks,

MrW

quamaretto

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
software.)

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. :)

John_Smallberries

@MrWilder said:

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?

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.