locked
Data Access Layer using Stored Procs RRS feed

  • Question

  • I am currently working on an next generation application that have the requirements to use the existing legacy database.  All data access has to call stored procedures.  The current application is written in ASP pages which calls the stored procedures directly.  There are 1400 stored procs in the database.  The new front end will be written in MVC.  We are going to use domain objects to pass between the data and business layers.  I am currently researching the best way to design the DAL so it can call stored procs, map the results to domain objects, and return the domain objects to the BL layer.  

    Some possible solutions we are looking into are.....

    (1) Entity Framework - I think this is just over kill for my current requirements.

    (2) Linq To SQL - Seems like a litter weight solution compared to entity framework.

    (3) Data Application Blocks

    (4) Data Set - map to domain objects

    (5) Data Reader - map to domain objects

    Does anyone have any suggestions on which would be better solution based on the requirements.

    Tuesday, October 2, 2012 1:35 PM

All replies

  • As you say I think adapting to EF will be quite costly.

    The easiest approach is to use your options 4/5, particullarly if you use reflection to do the matching between the sp returns and the datatable/datareader. Just try to move Datasets around tiers as it is a costly process.

    I work in a system were we have a similar layout as you (but no asp .net, its a desktop 3 tiers app) and we use the DataReader approach. Time has prove that moving towards a LINQ-SQL approach will be a good option as for performance reasons sometimes you end up building your SQL dynamically in the stored procedure, which is kind of a pain to do.


    Juan Casanova http://jawsofdotnet.blogspot.com - My .Net babling blog

    Wednesday, October 3, 2012 6:47 AM
  • Article "Choosing an ORM strategy"

    by Jimmy Bogard is very informative. Following is the url to his article

    http://lostechies.com/jimmybogard/2012/07/20/choosing-an-orm-strategy/


    Thursday, October 4, 2012 4:15 AM
  • Hi,

    4/5 Option looks good for your requirement.

    For easy mapping from DS-DR to domain objects  , try auto mapper api.

    http://automapper.codeplex.com/


    Regards Tushar - Mark "Propose As Answer" if your query got answered Vote "Helpful" if Answer is helpful


    Friday, October 5, 2012 8:52 AM
  • Could you give an indication of how different the data shapes to domain objects will be? Will it be a close mapping or are they nothing like each other?


    http://pauliom.wordpress.com

    Friday, October 12, 2012 8:52 AM
  • It's a mixture of both close mapping and some are nothing like each other. 
    Friday, October 12, 2012 11:53 AM
  • The results from the stored procedures, typically how close to the underlying tables are the results?

    http://pauliom.wordpress.com

    Friday, October 12, 2012 3:40 PM
  • It can be different than what the tables have. Usually stored procedures will fetch the data that matches the domain model values.

    If you are mapping domain model straight from table, it will be close mapping. 

     
    Thursday, October 18, 2012 7:40 AM
  • It can be different than what the tables have. Usually stored procedures will fetch the data that matches the domain model values.

    If you are mapping domain model straight from table, it will be close mapping. 

     

    That's why I asked the question :) I'm guessing there will also be a mixture. These are the important issues about what ORM (if any) to use. The next set of questions will be about identity management, another important choice when thinking about to ORM or not.

    http://pauliom.wordpress.com

    Friday, October 19, 2012 2:31 PM