data layer design- I am a walking WTF

  • 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:

    <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";
            DBCommandWrapper dbCommandWrapper
    = db.GetStoredProcCommandWrapper(sqlCommand);

                // 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");

                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.

  • 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
    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.

  • <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. 🙂

  • @MrWilder said:

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

Log in to reply

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.