26

Please excuse the noob question as I am new to integrating data with my applications. I've tried to find answers on the net, but not there yet.

I have an application I'm developing in C# on VS2010 which requires data in/out from a database. I am trying to figure out if its a DataSet or Entity Data Model I need to use when setting up a data source. My understanding was that it was the EDM which allowed me to treat tables/fields in a database as objects, but somehow it looks like I can do that with a DataSet too.

Some sources explain that a DataSet makes a cached copy of the Database which can then be manipulated.

Essentially my question is which should I use and what are the (dis)advantages of one over the other.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Vim
  • 365
  • 1
  • 4
  • 6
  • 3
    What about LINQ to SQL, or NHibernate, or Micro ORMs, or just plain old ADO.net? Your question is enormous and pretty much unanswerable. A data access layer is of course a key part of any data driven application, but it's far too big a subject to cover in a single SO answer. I'd suggest investing in several books. – James Gaunt Feb 29 '12 at 23:54
  • At present I'm reading up on LINQ, but then to use LINQ to query my database, I need access to my database, for which I need to figure out if I'm to use DataSet or EDM. I appreciate it is a too big a subject to cover in a single answer, but was wondering if someone could give me some pointers which I could then pursue. Thank you for your prompt reply. – Vim Feb 29 '12 at 23:59
  • The only thing I'd add is that you can't really decide on a data access layer without considering how it fits in to the rest of your architecture. I've tried most of the options and at the end for the data access layer I just use plain SQL via ADO.net (admittedly all generated via a code generation system). It's quick, clean and doesn't tie your application into today's fad technology. – James Gaunt Mar 01 '12 at 00:02
  • Further, my understanding was ADO.NET is the god almighty of all data access in .NET and that DataSet/EDF comes further down in the hierarchy. James, you've said why do I not use plain old ADO.NET. It appears I'm very misguided. – Vim Mar 01 '12 at 00:05
  • I meant really just using plain old ADO.Net in the sense of just using connection objects to execute SQL. A dataset is a further abstraction on top of that, I'd recommend just using datareaders as these are the lowest level you can get to. Everything else introduces a barrier between you and the database. But this is just my opinion - some people like weighty abstractions. If you're using them for an architectural reason that's great - but I think often we use them because they are 'easy' to get in to - they are - but they're hard to get out of. – James Gaunt Mar 01 '12 at 00:08
  • Thanks a lot James. I shall head back to the drawing board again! – Vim Mar 01 '12 at 00:16
  • Please don't let me put you off - you're asking the right questions - just make sure you get a wide range of answers and don't just fix on some technology because it's in fashion or it's easy to use. EF and DataSets obviously have their use or they wouldn't exist. It's just the way you come at the question with these two as the only options that made me comment! – James Gaunt Mar 01 '12 at 00:19
  • This is pretty much an open ended question. You might as well asked "I have an application that needs to read/write from the database. What language should I use?" – Mr. Young Jan 19 '13 at 00:17

5 Answers5

12

You have several options open to you when it comes to storing and retrieving data to/from a database:

  1. At the very simplest level, use ADO.NET to open a connection to the DB, create a command and execute it. If you expect results back (i.e. SELECT ...) then you could call the command's ExecuteReader(...). Working in this manner results in very quick execution and the minimum of overhead, but you have to do more of the heavy lifting. If your app is simple, this is probably a good way to go. If your app is, or is likely to be more complex, you may want to consider other options...
  2. ADO.NET DataSets are a reasonable DB IO mechanism, particularly for reading data from a DB. However, they can be a little cumbersome when trying to update the DB.
  3. You could use an Object-Relational Mapper (ORM) like nHibernate or Entity Framework, but, frankly, that often results in your learning curve increasing dramatically while you figure out how to plug together the moving parts and make them work well together.
  4. You might also consider a new variant of Entity Framework called Code First (CF): This allows you to pretty much design your code and CF will generate your EDM and handle the majority of the DB operations required for you to build your system. Scott Hanselman wrote up a nice intro into EF CF.

Having used practically every DB API and ORM on Windows over the last 20+ years, I am delighted with how CF is shaping up! EF 4.3 that shipped just a couple of weeks ago includes some key new improvements to CF including migrations which allow you to handle changes to your DB schema as it evolves. I've build 3-4 systems using EF CF over the last couple of months and am very happy - it's my favorite relational database IO mechanism at present.

If you want to really get into EF CF, I strongly recommend Julia Lerman's book EF CF - it's a short, nicely written, very useful guide that should take you no more than a day or two to work through the main sections of.

Hope this helps.

Rich Turner
  • 10,800
  • 1
  • 51
  • 68
  • I binned EF the minute I saw a pretty reasonable looking query generate an SQL query of over 9000 lines! It's fine in simple scenarios but it makes claims about handling more complex mappings that in reality it can't handle - or it handles them very very badly. I think the main failings are in LINQ to Entities - once they sort that out I might give it another look. – James Gaunt Mar 01 '12 at 19:25
  • 1
    Id be interested in seeing that query and generated SQL. Im using ef 5.0 and seems to behave well based on the SQL profiler. – phil soady Dec 21 '12 at 14:42
  • @James: I'd echo soadyp's comment - chances are that you've missed something fundamental in your EF code for it to have ballooned the generated SQL to that degree. – Rich Turner Jan 27 '13 at 22:40
  • 1
    You can see the code in this question: http://stackoverflow.com/questions/6175616/entity-framework-efficient-navigationproperty-oftype-query I eventually managed to fix this particular query, but there was no warning the original query would generate a multi-screen SQL queries. I didn't manage to fix other similar issues and ended up dumping EF. I think most problems relate to multi-table per entity models however. – James Gaunt Jan 28 '13 at 13:07
7

If you add a LocalDB data source to your project (because you want a small local database file) then when the Data Source Configuration Wizard pops up, it explicitly asks you whether you want to use a Dataset or Entity Data Model database model. Is this the situation you were facing? That was the problem I had that brought me to this entry.

There is no question that for an enterprise class application, or a website, you would want to investigate ADO.NET or an ORM, but it doesn't help answer this question, which has to do with what are the differences between choosing Dataset vs Entity Data Model in the wizard.

Essentially, Entity Data Model is the more recent technology. If you are unfamiliar with Dataset, then this is probably not the time to start using it.

dylanT
  • 1,080
  • 1
  • 13
  • 26
  • +1 for answering the question better than anyone else, but I would have loved to have read more on the actual differences. – kmote Aug 06 '15 at 19:51
  • In my humble opinion, the major distinguishing feature is that Datasets are an in-memory database that you load from whatever source. When you execute queries, they run against the in-memory data. EDO is closer to EF, in that the queries as far as I am aware always run against the original source database. With datasets you might find yourself writing views in your database to retrieve a subset of data for performance reasons. With EDO this is not necessary. – dylanT Aug 07 '15 at 01:07
  • what's EDO? Did you mean EDM? – kmote Aug 10 '15 at 18:02
2

If you're asking what are the pros and cons for ADO.NET (DataSet) vs EntityFramework (Entity Data Model) then there is a discussion that may help at ADO.NET Entity Framework or ADO.NET

EF will get you up and running pretty quickly but in my (very limited) experience its been a pain to maintain.

What is it that has determined that these are your only two options? There are far more available to you including many ORMs.

Community
  • 1
  • 1
kaj
  • 5,133
  • 2
  • 21
  • 18
  • Thanks KAJ, I'll check the thread to see what I can pull from that. I think my problem is I know too little or too confused to explain my problem clearly. Ah! the wonders of dissertations! – Vim Mar 01 '12 at 00:13
  • "Going on"? That thread "went on" almost 2 years ago before EF4 shipped! EF 4.3 shipped just a couple of weeks ago and includes some extremely powerful features (hint: code first & migrations). – Rich Turner Mar 01 '12 at 00:45
  • 1
    Point of note: "going on" doesn't appear in my answer (and I haven't edited it out :-)). – kaj Mar 01 '12 at 08:47
1

If your application is supporting a business application than queries get complex pretty soon. In such scenario, stored-procedures save a lot of time and are much easier to maintain and they work better with ADO.NET. In almost all scenarios, I would suggest using stored-procedures and ADO.NET. Move as much of the business rules and logic to stored procedures as you can...much easier to maintain this way.

Use Datasets (datatables) only to retrieve and read data. Any data that needs to be saved to database should be directly manipulated in the database ... no point doing it in dataset and then saving the same. In a multi-user environment it is almost always better to save the changes to database as soon as the user has clicked "save".

You may (should) use business objects within the application for business-logic processes.

Let us take a simple example of where you are saving a Contact (name, phone, email, address etc) and then retrieving a list of contacts added today...I would suggest you do it as follows:

1) Adding the contact - Client (web or otherwise) collects data --> data is saved in a Contact business object --> validate Contact object --> Call repository layer to save Contact object (adding a repository layer is useful but not-necessary to keep the data layer abstract from the client) --> Repository calls the data layer to save the contact object (here a simple ADO.NET call, using Command object, can be made to call the stored procedure to save the contact in database). No dataset was used in this use case.

2) Retrieving list of contacts -- Client calls the repository layer to get the list of contacts --> repository layer call the data layer to retrieve the data --> here the list of data is retrieved as a dataset(datatable) --> return the datatable back to the client and let the client read the data directly from datatable while rendering the data. Even a single contact can be retrieved as a dataset.

P.S: ORM is almost always an overkill. It is almost always used because certain developers like to keep everything object-oriented...so an extra layer gets added even though it does nothing useful (IMHO).

Metal Latem
  • 101
  • 4
  • 4
    I would NOT do this recommendation. Storing business rules in the DATA layer is a HUGE mistake. What happens when it's decided to chang the backend storage solution from MSSQL to Oracle, or to a cloud solution, or to NoSQL? The point about business objects is to control how data is fetched, validated, and saved to the backend store. Repository patterns are a better fit as it abstracts away the storage solution. Your business objects can then employ the repo. Leave simple data CRUD operations to the data layer and leave the Business Rules to the Business Objects. – Mr. Young Jan 19 '13 at 00:15
0

But, what if you have business logic (stored procedures) which can be used in many different applications. So depends: if you make your application for different users with different backend storage, or you make many applications for users which doesn't change backend storage so often. It is very important to have database integrity and rules independent from application (inner or outsource)

nadavesela
  • 11
  • 1