using C# or Stored Proc RRS feed

  • Question

  • We started building our first 3 tier web application. We started out with a BLL, DAL and Model class for every table in the database. Also we have the necessary stored procs (Insert, Update, Select, Delete, SubSetSorted and RowCount) for each table.

    Now that we have laid the groundwork we start getting into some more problematic scenarios where we need data from 2 tables. For example we have a Threads and Posts table and in certain cases we need data from both tables.

    1.       We build a ThreadPost BLL, DAL and Model class with one storedProc that returns the necessary fields. Now that we are done we are thinking of maybe better ways of doing this without creating  the extra classes or stored procs.

    2.       Reuse what we have. For example could we use the Thread classes and Post classes to return 2 objects and create a ThreadPostInfo (Model) class out of those.  I was thinking to have 2 DataReaders in the DAL where one calls the Thread stored proc and the other call the Post stored  proc and create a ThreadPostInfo object.

    What is more efficient or more common 1 or 2?


    Thursday, April 10, 2008 2:24 AM

All replies


    Sounds like you will end up with atleast two DB calls in case 2 where you could have managed with just one in case 1.  And perhaps you will also pull more information from the DB than you need and then discard the rows on the server when you do a join like operation using your model classes ?

    Model classes have never been a great favourite of mine. Not only is there an overhead of creating a class for every table,  the server logic also will have a more intimate knowledge of the underlying data-structure than perhaps is necessary.  

    What benefits do you accrue by using the model classes in your design ?

    Thursday, April 10, 2008 2:56 AM
  • Here are the things I would consider:

    How many trips are you taking to the database (the less the better)

    How much data do you return for the total task (all trips)

    Stored procedures allways run faster than TSQL code


    Thursday, April 10, 2008 4:14 PM