0

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.

Community
  • 1
  • 1
Zerotoinfinity
  • 6,290
  • 32
  • 130
  • 206
  • You can't statically type against a mutable database. Regular old ADO datasets would work for you. – asawyer Nov 28 '12 at 21:55
  • But dataset can decrease performance of the application, isn't it ? – Zerotoinfinity Nov 28 '12 at 22:03
  • Not only performance. they will also decrease maintainability, which is even worse. – Darin Dimitrov Nov 28 '12 at 22:07
  • @DarinDimitrov Any suggestion for the approach ? – Zerotoinfinity Nov 28 '12 at 22:08
  • No, I have strictly no idea what you are talking about or asking in this question. That's the reason why I've voted to close as not a real question. Btw I've hesitated whether I should vote to close as not a real question or subjective and argumentative. You seem to be asking things like Code First vs Data First and I am afraid that SO is not the right place for those kind of discussions. At least that's my opinion. Feel completely free to ignore it and hopefully someone will answer your question. – Darin Dimitrov Nov 28 '12 at 22:15
  • Hi Darin, i understand your thought, but i dont see it as a simple Code first V DB first generic Question of which we have seen too many. – phil soady Nov 30 '12 at 10:18

3 Answers3

2

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

Community
  • 1
  • 1
Jan
  • 15,802
  • 5
  • 35
  • 59
1

I'm developing in a similar scenario, creating, removing or updating tables on the go (not a really good scenario). I'm currently using the Database First approach because it's perfect for this scenario.

You should use Database First if:

You'll be doing database structure changes and at the same time users will be inserting or updating data. Otherwise if you choose Code First approach, every time you change the database structure your data will be deleted, you could implement some hacks in order to restore it, but if your app is on live a lot of problems could happend.

How Database first deals with structure changes:

  1. You make some changes in your "Users" table (in your database).
  2. You go to Visual Studio, and using DBContext it will update the "User" class.

How Code firstdeals with structure changes:

  1. You change your "User" class (in your model).
  2. Automatically your database will be updated to fill the required changes in the Users table (losing stored data)
  3. If you handle it, you could restore your data (but if your database is on use it won't be a good thing).

Anyways I don't recommend you to work this way, it's better if your database is totally well designed, and then start coding on top of that.

EDIT:

It seems like my answer is no longer correct, since Entity Framework 4.3 they've added some new cool features: Code First Migrations, thanks to @soadyp for pointing out my mistake.

marcos.borunda
  • 1,486
  • 1
  • 17
  • 34
  • 1
    have you seen Code based Migrations ? No need for data loss since ef4.3 CODE based migrations can be executed automatically or via Package manager Commandlet "add-migration" Prior to 4.3 i would have agreed, but since the migration tool is there, things looks much different. – phil soady Nov 30 '12 at 10:38
  • I wasn't aware of that, I need to get an update on the Code First approach. Thanks! – marcos.borunda Nov 30 '12 at 17:01
1

Jan has nicely pointed out EF isnt the only ORM. But Since you asked with EF tags then he is an EF perspective.

Since EF4.3 EF has supported code-based code-first migrations. Ie Change your model and COde first can alter the DB to add new colum ns and tables. There is a new DB initializer for this. It will even delete columns without data loss to the rest of the row.

Equally you can continue to Import/Update a model from a DB with DB first. The more recent t4 templates with the Import from existing DB create Partial POCO classes. So you can extend the generated MODEL classes safely and continue to re-import and lose nothing.

So you can use either Code first or DB first in mutable DB/Model scenarios.

I have used both techniques in a green field site. I felt more comfortable with the DB first to begin with. Then Once i found the new migration options in ef4.3 i was convinced to convert. It was quick to convert since there is a great power tool from Microsoft. NUGET Entity Framework power Tools (beta 2 as of Nov 2012) http://blogs.msdn.com/b/adonet/ There is an option to reverse engineer code first from a DB. So can can convert from DB first to code first. And then use CODE based Migrations.

phil soady
  • 11,043
  • 5
  • 50
  • 95