Search code examples
asp.net-mvc-3entity-frameworkenterprise-librarycode-firstef-database-first

Connection of MVC 3 application with the ever changing database

I have just started learning ASP.NET MVC 3 and I am deep trouble deciding the best way to connect to the database for my application. As my application database will be enhance in regular intervals so new tables, columns could be added/removed and may be some columns data type also be changed. So, for this scenarios which approach will be best for me so that my code will be manageable and these changes will not impact my code (ex: If I delete and re select table in EF, then new class will be generated and code related to the class will be impacted)? I have read that there are approaches like Code First, Database First, Entity Framework, Enterprise Library Data Access Block, SQL Connection calling stored procedure but I am not sure that which will work best in this scenario and may be I am missing the real flavor of ASP.NET MVC 3 to connect to database.

Edit1

I am not sure why it has been called to close but nevertheless I have no other option rather than this forum. I have found similar question which states the partial content of my question Code First vs Data First. The answer has just created a chaos in my mind regarding database first approach.

Solution

  • At first, the fact that you are developing a MVC application is completely decoupled from the decision which technology you will use to build your data access layer.

    There are many options for data acess with .NET falling into two categories. I list the one at my mind with a few points each

    Direct data access

    1. ADO.NET DataReader (fast, much manual work, have to write manual SQL code)
    2. ADO.NET DataSets

    Object Relational Mapper (ORM)

    1. LINQ2SQL
    2. Entity Framework DB first
    3. Entity Framework Code First
    4. NHibernate
    5. Many commercial third party tools
    6. Dapper

    Manually coding your SQL gives you definitely the fastes access but at the cost, that you have to do everything yourself.

    The ORMs give you some kind of abstraction layer to your database so you can just work with objects. That comes at the cost of some performance loss (Query generation, mapping from SQL results to objects).

    Dapper is somewhat special here, because its a ORM with focus on read performance, but it requires you to write your SQL queries yourself and has only limited write support.

    I personaly have experience with DataSets, LINQ2SQL, EF Db First, EF Code First and Dapper. When i had to start a new app i would use EF Code First if you don't expect a very high load. This is especialy true, when there is no existing database. You just write your classes and get your Database generated. Whats even cooler are automatic migrations where you change your classes and EF will update your database structure. This is even possible without loosing your data if you pay attention to the generated code.

    When high performance is your demand, then i would choose Dapper where you have total control on the SQL, very little overhead but a comfortable mapping engine.

    In addition, you will find many question like this on so (just look for favorite orm):