A short take on Microsoft.Data


Yesterday, David Fowler blogged about the Microsoft.Data APIs that come with ASP.NET WebMatrix.  While there were some positive comments, the majority were negative.  The reason for that is that a lot of readers completely missed the point of the API.  David is partially to blame for that, as he had not properly put it into context.  He since wrote a follow up post which fills in those gaps (and here is another one by Andrew Nurse), but I’ll try to do a slightly different take on this.

So in what sense did some readers ‘miss the point’?  They read it as:

Microsoft is now recommending a new data API.  That API uses raw SQL rather than an ORM.  Bunch of Microsoft idiots, what are they thinking!? 🙂

The reality is of course quite different.  As great as ORMs are, there is a measurable number of web developers who favor writing raw SQL over using an ORM.  That is a fact, and if you’re trying to pretend that it is not the case, you’re in denial.  Though they tend to be less vocal, there are a lot of people who prefer the raw SQL approach, for a variety of reasons.  But the point of this post is not about whether one should use an ORM vs raw SQL, as enough blood has been poured over that. 🙂

Instead, this post is about this new Microsoft.Data API.  Here is a one liner that describes its goal:

Given that someone will use raw SQL, how can we make it easier.

The first part of the sentence is key, and is what makes all the comments along the lines of “they should not use raw SQL because …” completely moot.  Simply put, if you are an ORM user this technology is not for you.

Instead, the only relevant way to look at this API is to discuss whether it is indeed an improvement over the old ADO.NET API.  And in my opinion that is no brainer.  Pretty much everyone who has seen it in this light has agreed.  Among the advantages over ‘classic’ ADO.NET:

  • It uses far fewer concepts to achieve the same things.  Having to deal with SqlConnection/SqlCommand/SqlDataReader can be overwhelming.
  • It makes it easier to use parameterized queries, hence reducing the risks of SQL injection issues (Andrew’s post has some simple examples).
  • It uses ‘dynamic’ to make it more natural to access row values.

Is it revolutionary compared to classic ADO.NET?  Clearly not, and no one claims that!  But it is a long overdue simplification to the messy ADO.NET APIs, and in that sense it is pure goodness.

Now you can get back to your favorite ORM and feel safe that your world is not under assault. 🙂


Comments (10)

  1. Some One says:

    I have one table in my database and do very simple to no I/O to the database from a web application. Why would I want to build an extensive website with ORMs when a direct SQL call will fit the bill? Microsoft.Data seems to work, but so does ADO.Net. Why not just simplify ADO.Net?

    IMHO I think it is best to figure out what technology fits the solution. Some programmers maybe advance enough to create many different solutions. Not all of us are monolithic and use over and over the one thing we were taught. We might know many different things and having the option to choose is nice.

    But, my biggest concern is that I think we are going in circles. First there was ADO.NET then LINQ to SQL then EF, and now we are going back to an ADO.NET type solution called Microsoft.Data? This also reminds me when LINQ to SQL came out, it was going to be the end all be all. Well if you use MS SQL, oracle people and others were left to their own implementation (IQueryable). Guess what MS you dropped LINQ to SQL like a hot potato for Entity Framework. Besides all of the new features in EF then main reason is that it works with any database. Are we going to see Microsoft.Data evolve into Microsoft.Data.Linq (again)?

    Where is the 2, 3, or 5 year road map from Microsoft in regards to interfacing with a database? How does all of this fit (ADO.NET, LINQ to SQL, Entity Framework, Microsoft.Data) and where is it all going? Five years from now will there still be Microsoft.Data?

    IMHO I see it like this.

    ——————————–

    ADO.NET – Dead

    LinqToSql – Dying

    EF – The IN Thing (for now). Used for big databases with many tables and lots of I/O.

    Micorsoft.Data – Resurrection of ADO.Net Used for smaller databases with few tables and minimal I/O.

    Micorsoft.Data.Linq – Resurrection of the dying LinqToSql (soon come).

  2. davidebb says:

    @SomeOne: note that Microsoft.Data is really just a thin layer over ADO.NET.  So the better way to describe it is that it is in fact just a simplification of ADO.NET, and not an entirely new thing.  Namespaces don't match yet because it's just a preview release, but things should integrate more tightly later.

    And I wouldn't say that we're going in circles.  ADO.NET is sort of its own 'branch', and we're just making that branch easier to use, independently from what's going on in the L2S/EF worlds.

  3. sachab says:

    So, Microsoft.Data is intended to WebMatrix programmers. WebMatrix programmers are not traditional .net programmers. Please rename your assembly into WebMatrix.Data and do so for all you toy-r-us assemblies…

  4. Vincent Evans says:

    I would really appreciate if you post a link to a comprehensive argument that supports your claim that ORM replaces SQL use? I haven't observed this as some sort of consensus, but maybe i somehow missed the trend – since your post goes a great length toward flat out stating that "if you are writing SQL – you are doing it wrong".

    From personal experience i have observed many cases where poor-performing multiple calls to obtain data that ORM layer generated – were replaced by a sophisticated SQL query (with GROUP BYs, subqueries, mixed type JOINS, computes, HAVING and WITH clauses etc – the syntax that ORM appears to not have the depth or maybe even the aim to support).

    Further i am alarmed by the recent trend of writing (what appears to me as a very illconsidered idea) ,that rather than using SQL to aggregate and pre-process data on server-side – instead executes multiple database calls gettng raw entities and then proceeds to perform grouping, filtering and aggregation operations inside loops in C#.

    This is so far out of what i consider as "normal" – that upon reading your article matter of factly supporting theses practices – i have crossed from simple disagreement into the territory of utter disbelief and now feel that maybe i somehow missed some big development in the world of database programming altogether. Please provide a link to a good source where i can read about it, i would really appreciate it!

    Thanks!

    P.S: I see nothing wrong with using ORM for transactional CRUD, persistence of DTOs, or rudimentary querying – but i don't think this is what you are saying at all.

  5. davidebb says:

    @sachab: eventually the goal is to merge this functionality into ADO.NET, so it won't really be a new API.  Hence if you were not looking at ADO.NET before, you will not see this API either.

  6. davidebb says:

    @Vincent: my post is not about "ORM vs raw SQL – which one is better".  Instead it is about how this new API makes it easier to use raw SQL, and how ORM users should not be 'threatened' by it as it is not for them.  I reworded a few sentences to make this clearer.

    Also, I'm a bit confused by some of your comments about my post supporting certain SQL pratices over others, as it doesn't even touch on those topics.  I'm not saying that your points are not valid, but they are quite orthogonal to anything discussed here.

  7. NotMyself says:

    I think you miss the point of the criticism completely. Microsoft keeps marketing tools to non developers that allow them to paint themselves into a corner and selling it as NO CODE NEEDED. We as professional developers then have to come into these horrible train wrecks after the damage is done and clean up.

    If MS is going to make a tool for non developers to do development with, at least make them in such a way that the user falls in the pit of success producing code that follows at the minimum common sense best practices that we learned the hard way.

  8. davidebb says:

    @NotMyself: have you looked at WebMatrix? It is absolutely not something that claims to be NO CODE NEEDED.  It has a very simple page model, but is very much code driven, and allows small web sites to be easily written.

    Of course, that is mostly orthogonal to this discussion, which is simply about making raw SQL use easier than it was before.

  9. wills says:

    David, I really appreciate this article. Thank God somebody is trying to spread a little context. Please keep up the good work.

  10. Mike says:

    "Given that someone will use raw SQL, how can we make it easier."

    I prefer "Given that someone will use raw SQL, how can we make them use an ORM"

    And I believe the response from the "someone" was "doh, how do you spell ORM? M-O-O-N" and then Scott Guthrie said "Let's give up, SQL injection FTW" and in 2 years our worlds will be under assault by management saying "you guys need to fix this invoicing app the intern wrote last year, apparently first somebody hacked it and we lost some money, and now the Google crawler deleted all the data from the database (because the app only used GET methods as in the MSDN examples)" and then you can write another short take on Microsoft.Data, okay? Okay!