11 Replies - 1388 Views - Last Post: 05 July 2011 - 07:01 PM

#1 jens  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 67
  • View blog
  • Posts: 430
  • Joined: 09-May 08

How to talk to your DB?

Posted 14 February 2011 - 06:05 AM

Hi!

How to talk to your DB? Well, there are at least three different ways and I'm getting all confused of pros and cons of each one. Maybe you could help me figure out why I'd prefer one or the other...
1) ADO, by using datareaders, dataadapters and such I can have complete control of what's happening but there is a lot of coding to do.
2) Datasets, just drag a table from the explorer and code away. Seems simple enough but, well, I'm just not comfortable with it. Probably because I've run in to a few problems writing back to the DB.
3) Entity framework, seems very elegant and removes allmost all need for tedious coding. What are the drawbacks? Well, one is that I feel a little lost when working with complex business objects (nested objects, each of some complexity).

Thoughs?

Regards
Jens

Is This A Good Question/Topic? 0
  • +

Replies To: How to talk to your DB?

#2 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: How to talk to your DB?

Posted 14 February 2011 - 07:04 AM

Don't forget about LINQ-To-SQL.

Personally, I would tell a beginner to use ADO.Net. It will help understand what is going on. Once they are comfortable with ADO.Net, I would suggest using EF4 or LINQ-To-SQL.
Was This Post Helpful? 0
  • +
  • -

#3 CodingSup3rnatur@l-360  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 991
  • View blog
  • Posts: 971
  • Joined: 30-September 10

Re: How to talk to your DB?

Posted 14 February 2011 - 07:06 AM

You missed one option off your list; LINQ to SQL :).

ADO.NET SQL is easy to understand (if you know SQL), and a logical process (write SQL query, execute it, read the result set from the reader), but as you said, it does require quite a bit more work/code, and it tends to clutter the code base somewhat. The other options tend to provide more elegant solutions. However, if your more comfortable working with SQL queries, and would rather do that, this option is obviously perfect for you.

You can perform ADO.NET SQL queries and return DataSets for any ADO.NET enabled database. These DataSets can then be queried using LINQ. Thus, you can load the required tables into a DataSet with mimimal SQL code, and then perform some key queries with the elegance of LINQ, thereby eliminating much (but curcially, not all) of the clunky SQL code.

Also, a benefit of DataSets over the object mapping technologies is that it offer more dynamism. With LINQ to SQL for example, the mapping between classes and objects has to be made at compile time. Therefore, DataSets make it possible to perform queries on a database that isn't known until runtime, for example. The use of DataAdapter objects and such help abstract some of detail away from you, and provide the link back to basic ADO.NET SQL queries.

I find DataSets to be a sort of 'happy medium' in between raw SQL queries and LINQ to SQL.

LINQ to SQL is tied to Microsoft SQL Server. LINQ to SQL very obviously cuts down the development effort and amount of code you have to write. It abstracts all the INSERT, UPDATE, DELETE etc SQL queries away from you behind simple method calls. LINQ to SQL uses entity classes to provide a one to one mapping between tables and objects, plus the DataContext object handles connection details for you. This makes it very very easy and elegant to perform really quite complex queries, in just a few short lines of code.

The Entity Framework is, at first glance, very like LINQ to SQL. It too offers mapping to objects. However, it offers a good few more advanced features. For example, it allows you to have one to many mappings between entity classes and tables, i.e. you can map a single entity class to multiple tables. It also allows you to directly model many to many relationships (of which I found particuarly enlightening coming from the ever powerful Microsoft Access :)).

I haven't got a huge amount of experience with the Entity Framework in real projects though, I have to say. I tend to get by perfectly well without it. So I shall leave the specific details of that to someone else :).

Generally speaking, when choosing between Entity Framework and LINQ to SQL (if I am working with Microsoft SQL Server at least), I seriously consider whether or not I need the more complex features of the Entity Framework.

I generally find myself prefering LINQ to SQL and DataSets these days. For Microsoft SQL Server projects where I will be performing a lot of queries, the ease of LINQ to SQL makes it an easy choice. For the odd query here or there, I find DataSets to be a nice solution.

Overall, there is a degree of personal preference involved when deciding which method to choose, but there are also certain situations where one method will be technically preferred.

This post has been edited by CodingSup3rnatur@l-360: 14 February 2011 - 07:22 AM

Was This Post Helpful? 4
  • +
  • -

#4 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4428
  • View blog
  • Posts: 7,696
  • Joined: 08-June 10

Re: How to talk to your DB?

Posted 14 February 2011 - 07:37 AM

I use LINQ to SQL because it's familiar. I've used it on the last two projects I have, and have grown accustomed to it. Likely I would have used Entity Framework if I had known what it was at the time, but I didn't.

I'm practically to the point of suggesting L2S or EF4 to newbies these days. Sure, it's important for them to understand how the ADO.NET model works, but when every question about it you see is all selecting * and using concatenation to build WHERE clauses...it makes me think that L2S or EF4 is a better option for them. Take the security out of the hands of the poor developer and put it in the framework itself.
Was This Post Helpful? 0
  • +
  • -

#5 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1252
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: How to talk to your DB?

Posted 14 February 2011 - 07:49 AM

I've used DataSets before during my first and second year of college. I remember it being quite cumbersome to use. Fill the adapter, build the select clause...hm. Lots of string concatenations and other places ripe for a bug to wiggle in. I don't miss it at all.

I use Entity Framework 100% for my projects these days. It's an ORM that works with a lot of database providers including MySQL, SQLite and Oracle. You just build your repository classes based on an interface and you hot-swap databases without breaking your client.

Tell me this doesn't look natural.

Person sergio = new Person() { Name = "Sergio", Age = 21 };

dbContext.AddToPeople(sergio);
dbContext.SaveChanges();



You can use Linq to create powerful and more importantly, readable queries. It's just easy and fun to work with. You are also kind of forced to normalize your database because the better normalized it is, the more natural your Linq queries will look. It's sort of the way MVC forces you to separate your concerns.

Want a list of People who are Managers and are over 20 years old?

var result = personRepository.People.Where(p => p.Type.Name = "Manager" && p.Age >= 20).ToList();



I recommend EF over Linq to SQL as EF is just more and the differences are minuscule when starting out.

This post has been edited by Sergio Tapia: 14 February 2011 - 07:52 AM

Was This Post Helpful? 1
  • +
  • -

#6 beddet  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 03-June 10

Re: How to talk to your DB?

Posted 11 March 2011 - 11:16 AM

For me it really depends on what I'm doing, if the data I need to persist / retrieve from the db is very easy to model out (fx. band -> album -> song) then I prefer EF and then using LINQ to query it.

For a register user / login in which I don't need to actual query the users in any way, just to login for authentication I'm using ADO with sqlcommands and such (the slow and tedious way).

The same goes for "off" data, in the project I'm currently working on I need to create some graphs from a lot of datapoints, for stuff like that I'm also just binding it via ADO to a DataTable with 2 columns (time and value).

The upside to manually executing a SQL query provides you with everything you can do in the specific database, so you might have some complex queries to do certain stuff and if you know how to write the SQL (or know someone who can help you), ADO is as far as I know the way to do that. I'm not experienced enough with the entity framework or other mappers to know if that's possible.
Was This Post Helpful? 0
  • +
  • -

#7 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4428
  • View blog
  • Posts: 7,696
  • Joined: 08-June 10

Re: How to talk to your DB?

Posted 11 March 2011 - 08:45 PM

Well, you can write stored procedures and add them to your dmbl or edmx file, and call them through the data context object. That lets you execute sql directly.
Was This Post Helpful? 0
  • +
  • -

#8 beddet  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 03-June 10

Re: How to talk to your DB?

Posted 12 March 2011 - 05:19 AM

Ah ok, I didn't know that
Was This Post Helpful? 0
  • +
  • -

#9 DivideByZero  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 238
  • View blog
  • Posts: 551
  • Joined: 02-December 10

Re: How to talk to your DB?

Posted 11 June 2011 - 06:22 AM

I don't have much experience with databases, but I'm currently using datasets/adapters to which I'm finding surprisingly easy. Mostly due to the abstraction that has already been mentioned.

I definitely recommend it to database beginners such as myself :)
Was This Post Helpful? 0
  • +
  • -

#10 T3hC13h  Icon User is offline

  • D.I.C Regular

Reputation: 65
  • View blog
  • Posts: 337
  • Joined: 05-February 08

Re: How to talk to your DB?

Posted 20 June 2011 - 08:12 AM

EF, LINQ and nHibernate do a great job but I don't like all the generated code/config files so for my data access I've been exploring a number of lightweightopensource ORMish projects, here's what I've found:

Simple.Data is pretty awesome as far as just getting at your data with as little fuss as possible.
PetaPoco is an easy to use, super fast ORM which I've been using a lot recently.
Massive is another light weight ORM, I haven't used it but it looks pretty slick.
Was This Post Helpful? 0
  • +
  • -

#11 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4428
  • View blog
  • Posts: 7,696
  • Joined: 08-June 10

Re: How to talk to your DB?

Posted 20 June 2011 - 08:49 AM

You should check out EF Code First.
Was This Post Helpful? 0
  • +
  • -

#12 MATTtheSEAHAWK  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 137
  • View blog
  • Posts: 782
  • Joined: 11-September 10

Re: How to talk to your DB?

Posted 05 July 2011 - 07:01 PM

I know this topic is a little old but I couldn't help popping in here and saying something about using PHP scripts. Every time I have to interact with a database on my server I would put a script on there and use GET to pass parameters to it via a webrequest in my program. This is only for a server though and you won't be able to do it with an embedded database but this is what I use every time :).
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1