1

I'm getting ready to start a C# web application project and just wanted some opinions regarding pulling data from a database. As far as I can tell, I can either use C# code to access the database from the code behind (i.e. LINQ) of my web app or I can call a stored procedure that will collect all the data and then read it with a few lines of code in my code behind. I'm curious to know which of these two approaches, or any other approach, would be the most efficient, elegant, future proof and easiest to test.

kingrichard2005
  • 7,179
  • 21
  • 86
  • 118

3 Answers3

2

The most future proof way to write your application would be to have an abstraction between you and your database. To do that you would want to use an ORM of some sort. I would recommend using either NHibernate or Entity Framework.

This would give you the advantage of only having to write your queries once instead of multiple times (Example: if you decide to change your database "moving from mssql to mysql or vice versa"). This also gives you the advantage of having all of your data in objects. Which is much easier to work with than raw ado Datatables or DataReaders.

Bivoauc
  • 853
  • 5
  • 10
1

Most developers like to introduce at least one layer between the code behind and the Database.

Additionally there are many data access strategies that people use within that layer. ADO.NET, Entity Framework, Enterprise Library NHibernate, Linq etc.

In all of those you can use SQL Queries or Stored Procedures. I prefer Stored Procedures because they are easy for me to write and deploy. Others prefer to use Parameterized queries.

When you have so many options its usually indicative that there really isn't a clear winner. This means you can probably just pick a direction and go with it and you'll be fine.

But you really shouldn't use non-parameterized queries and you shouldn't do it in the code behind but instead in seperate classes

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
1

  1. Using LINQ to SQL to access your data is probably the worst choice right now. Microsoft has said that they will no longer be improving LINQ to SQL in favor of Entity Framework. Also, you can use LINQ with your EF if you should choose to go that route.

  2. I would recommend using an ORM like nHibernate or Entity framework instead of a sproc/ADO approach. Between the two ORMs, I would probably suggest EF for you where you are just getting the hang of this. EF isn't QUITE as powerful as nHibernate but it has a shorter learning curve and is pretty robust.

Dubmun
  • 478
  • 2
  • 10