ADO.NET vs. traditional SQL statements

ADO.NET vs. traditional SQL statements

11-08-2009 11:54 PM |

Is it more efficient to use ADO.NET to do queries and updates or is it more efficient to do it via statements?

What are the differences between them?  Why ADO.NET?  Is it just because it’s an “object model” that has certain advantages?

Re: ADO.NET vs. traditional SQL statements

11-09-2009 3:44 AM |

 Are you sure you mean ADO.NET and not ADO.NET Entity Framework?

The reason I ask is that ADO.NET is the preferred way to run SQL Statements on a database.

Re: ADO.NET vs. traditional SQL statements

11-09-2009 1:44 PM |

I was talking about the Entity Framework.

Re: ADO.NET vs. traditional SQL statements

11-09-2009 2:06 PM |

The entity model approach is Microsoft's bet on the future, but it all boils down to what your specific desing scenario is.

Personally, I like EF because I can easily traverse entity relationships, without concerning myself as much with "join this to that, then group these things, etc" (you still have to worry about it sometimes, but less often).  I just use the same Linq expression query that I use for any other collection of data (So Linq-To-Entities for EF and Linq-To-Objects for IEnumerables).

I'm also looking forward to the new "model first" stuff that Microsoft is introducting soon.  Mostly because I find working directly with databases to be quite tedious.

Re: ADO.NET vs. traditional SQL statements

11-09-2009 2:23 PM |

Does this Entity Model approach take up much more resources when executing? 
I’m new to this form of data access and was wondering what kind of efficiencies are involved.
Are there situations where you wouldn’t use this model because it will tie up the database or something?

Re: ADO.NET vs. traditional SQL statements

11-09-2009 2:34 PM |

I don't think that the Entity Framework will in all cases out perform relational stored procedures but that said using EF you can very easily include stored procedures where performance is not up to scratch.

EF was in part created to rapidly create CRUD interfaces and it does work well but there are definetely places you need to monitor your performance which can be improved using views and stored procedures.

Read this article to understand a bit more regarding. Read the comments too.

http://weblogs.asp.net/fbouma/archive/2008/05/19/why-use-the-entity-framework-yeah-why-exactly.aspx

Re: ADO.NET vs. traditional SQL statements

11-09-2009 3:19 PM |

rasmasyean:

Does this Entity Model approach take up much more resources when executing? 
I’m new to this form of data access and was wondering what kind of efficiencies are involved.
Are there situations where you wouldn’t use this model because it will tie up the database or something?

The "entity model" was described back in the mid '70's by Dr. Peter Chen (http://bit.csc.lsu.edu/~chen/pdf/erd-5-pages.pdf) around the same time that the relational database model was conceived.  This is certainly a topic open for debate, but I think one of the reasons that the relational model become popular first was because the resource requirements were smaller.  Now that memory and storage are both relatively cheap, the entity model becomes more attractive, because you can place more of your data into memory and work with it there.  Either way, careful consideration of your hardware resources is not something to be taken lightly...

Re: ADO.NET vs. traditional SQL statements

11-10-2009 2:58 AM |

 Some first hand experience:

I have used the Entity Framework to get more than just a few records out of the database. Obviously it is going to be slower then using a DataReader approach. The reason for this is that it needs to instantiate objects and set properties of those objects. If needed it will also attach events to track any changes made to these objects. However this is only relevant in high throughput scenarios where you need to read (and write) thousands of records at a time.

One pitfall to look out for is the relational data. You do need to think about when to load relational data through the object model and when not to. If you get it wrong you will end up getting only a handful of records with a considerable amount of queries, which doesn't help performance.

HTH.

Re: ADO.NET vs. traditional SQL statements

11-12-2009 8:34 PM |

Would you use the Entity model in server side data access?  Like if you pass parameters to a WCF [OperationContract], would it be good to use the entity framework to process and return data?  Or is it mostly designed for Client side coding / data-modeling?

Re: ADO.NET vs. traditional SQL statements

11-13-2009 5:44 PM |

mrjvdveen:

 In fact you can not use the entity framework in your Silverlight client directly. In case of a Silverlight application it should be in the service. You then use operations to get your data in the client and other operations to save the data back to the database, through Entity Framework, exactly as you described above.

HTH.

OK so I was thinking along the lines of how you would update entities bound to a DataGrid and then do a BeginSaveChanges().  I didn’t mean saving to a database on the “desktop”.

I suppose the real work is behind the scenes at the server as you are indicating.

I was actually referring to making your own WCF service.  Would it be appropriate to use the Entity Model?  Or just use open a connection…etc…

Re: ADO.NET vs. traditional SQL statements

11-16-2009 3:04 AM |

 Off course this depends heavily on your requirements. EF is great for large scale development as it generates the data layer for you. It does work nicely for smaller developments as well.

A plain ADO connection is great if you need to do stuff other then regular CRUD operations.

Another alternative to EF is Linq2Sql if all you'll ever use is Sql Server.

HTH.