Introduction to Microsoft.Data.dll


Before you continue reading this blog post, check out the prequel.


I’ve been pretty busy recently working on cool features for “ASP.NET WebPages with Razor Syntax” (what a mouth full) and other things. I’ve worked on tons of stuff that I wish I could share with you, but what I can share is something that many people haven’t blogged about - Microsoft.Data.dll.

What is Microsoft.Data

It’s an awesome new assembly/namespace that contains everything you’ll ever need to access a database. In ASP.NET WebPages we wanted people to be able to access the database without having to write too many lines of code. Any developer that has used raw ADO.NET knows this pain:

using (var connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Northwind.mdf;
Initial Catalog=|DataDirectory|\Northwind.mdf;Integrated Security=True;User Instance=True")) {
    using (var command = new SqlCommand("select * from products where UnitsInStock < 20", connection)) {
        using (SqlDataReader reader = command.ExecuteReader()) {
            while (reader.Read()) {
                Response.Write(reader["ProductName"] + " " + reader["UnitsInStock"]);
Wow, that’s a lot of code compared to:
using (var db = Database.OpenFile("Northwind")) {
    foreach (var product in db.Query("select * from products where UnitsInStock < @0", 20)) {
        Response.Write(product.ProductName + " " + product.UnitsInStock);

The user doesn’t have to learn about connection strings or how to create a command with a connection and then use a reader to get the results. Also, the above code is tied to Sql Server since we’re using specific implementations of the connection, command, and reader(SqlConnection, SqlCommand, SqlDataReader).

Compare this with code below it. We’ve reduced the amount of lines required to connect to the database, and the syntax for accessing columns is also a lot nicer, that’s because we’re taking advantage of C#’s new dynamic feature.

Why is it so much easier you ask? Well, the Database class is what you’ll be working with when accessing data. There are several methods that let you perform different kinds of queries and factory methods for connecting to the database.

Connecting to the Database

Sql Compact 4 is our main story when developing locally with web matrix, so we optimized for the “I have a database file under App_Data in my web site and I want to access it” case. The first overload we’re going to look at does exactly that and is named appropriately, Database.OpenFile.

Database.OpenFile takes either a full path or a relative path, and uses a default connection string based on the file extension in order to connect to a database. To see this in action, run the starter site template in webmatrix and add this code to the Default.cshtml:

var db = Database.OpenFile("StarterSite.sdf");
The first line will create a database object with a connection pointing to the sdf file under App_Data. The second line is taking advantage of our ObjectInfo helper 
(more on this later) to show the properties of the database object.

Looking at the properties you can see that the connection state is closed, pretty weird for a method called Open to return a closed connection no? The reason is we want to delay the work as long as possible(we don’t even create the connection up front) i.e. until we actually decide to do a query.

If you look at the ConnectionString property you’ll see |DataDirectory|\StarterSite.sdf, this is one of the default connection strings I mentioned earlier. We assume relative path means within the |DataDirectory| which is “App_Data” in this case.

For simple cases OpenFile works. One of the big downsides is the fact that you are essentially hardcoding that you are using a database file, which will make it harder to migrate to sql server in the future. We have a solution for this that I’ll talk about below.

For those developers that understand connection strings and need a litte more control, then Database.OpenConnectionString might be for you. This API does exactly what you think it does, create a database object with a connection that uses the connection string specified.

var db = Database.OpenConnectionString(@"Data Source=.\SQLEXPRESS;
                                         Integrated Security=True;User Instance=True");
This is nice for more control but, connection strings are things that normally are stored in a web.config so it can be changed without recompiling the application.
Last but not least is the most magical (and controversial) solution. We went back and forth about this one but put it in the beta in order to get some feedback. 
Database.Open allows the user to specify a named connection, which can be one of 3 things (this is where the magic comes in):
  1. A database file under the data directory (App_Data) that matches the name plus one of our supported extensions (.sdf or .mdf)
  2. A connection registered with and Database.RegisterConnectionString, Database.RegisterFile APIs
  3. A connection string from web.config
We had lots of debate (still ongoing) internally about what the fall back order should be and if there should even be a fallback order. Today, we look for a 
connection in all the mentioned places and throw an exception if it is ambiguous (i.e. more than one) to prevent confusion. Database.Open is what we recommend since
it allows
the user to change what connection a name maps to, making a simple migration from Sql Compact 4 to Sql Server possible without any code change.
With this in mind here are some examples on how you can use Database.Open:
File based
// (Assume you have Northwind.mdf in the database)


Register API

// _start.cshtml

Database.RegisterConnectionString("MyDatabaseServer", "Data Source=;Initial Catalog=MyDb")

// SomeFile.cshtml


// web.config
    <add name="Northwind2"
        connectionString="Data Source=.\SQLEXPRESS;Integrated Security = true;Initial Catalog=Northwind"
        providerName="System.Data.SqlClient" />


I mentioned that the code in the very first example was tied to SqlServer. We internally use ADO.NET’s data providers to construct connections, so Microsoft.Data will mostly work with any database has a registered ADO.NET provider.


Since we optimized for what we thought would be the most common scenario we set up some default values and behaviors that may be hard to debug.
One of these is the fact that the default ADO.NET provider is Sql Compact 4. To work around these limitations we added a providerName paramter to methods like OpenConnectionString and RegisterConnectionString. We also recommend that you specify the providerName attribute when defining connection strings in web.config. Following those patterns will mitigate most of the issues.

We also provide a global override so you can change what the default provider is. For example, if you wanted to make the default provider Sql Server then you can do it by adding the following piece of configuration to your website:

    <add key="systemData:defaultProvider" value="System.Data.SqlClient" />
Today I covered connecting to the database, next time I’ll concentrate on querying. Stay tuned…


  • Excellent job, this makes raw ADO.NET operation a lot more straightforward.

    But I do have a question: Why not use Database.Open("Northwind") to open a connection with the conncetion string named Northwind? It makes much more sense (At least, to me it does). The connection string should come from the app.config/web.config file in that case.

    Then you could use Database.Open("Northwind.mdf") to open the northwind database as a file-based database.

  • Think it also does ... from the post:


    // web.config



    &nbsp; &nbsp;&lt;add name=&quot;Northwind2&quot;

    &nbsp; &nbsp; &nbsp; &nbsp;connectionString=&quot;Data Source=.\SQLEXPRESS;Integrated Security = true;Initial Catalog=Northwind&quot;

    &nbsp; &nbsp; &nbsp; &nbsp;providerName=&quot;System.Data.SqlClient&quot; /&gt;




    I could read this wrong, but the above uses the connectionString &quot;Northwind2&quot; and the db described in there.

  • This is the best news the System.Data namespace has had in a long time. Now we can all throw away the wrappers we've been using for half of the last decade.

  • Where do we download the Assembly?

  • @Chuck Conway It comes with the WebMatrix download.

  • Great, yet another way to spread the data layer evenly throughout the code.

  • more inline sql..sad

  • After all the good work with MVC, Razor and testing integration recently, you go and do something like this? This is the worst thing to come out of Microsoft development in years...

  • The "after" code is still not very good. We're in .NET 4 now, with lambdas and Action and everything, why is everyone still writing .NET 1.1 code?

    Try this instead:

    Database.Open("Northwind").Query("select from products where UnitsInStock Response.Write(product.ProductName + " " + product.UnitsInStock));

  • I don't want to defend these examples, but saying that this assembly in and of itself promotes bad practices that lead to SQL Injection attacks isn't really fair. I'm sure there are places where this would be appropriate, as long as the overload that takes parameter values is used.

    There -is- an overload that takes parameter values, right?

  • Nice, but is there any way you can make this code go into my button event handlers automatically?

  • Wooooahh!!! This is awesome. Can you tell me how this fits in with CQRS?

  • David, could you do a follow up post where you explain best practices for creating unit tests for applications using Microsoft.Data? I've been spending a lot of time lately trying to teach test driven development to other developers so that we can better the design and quality of our products and I think I need some help explaining how to use this.

  • We should see the big picture here.
    This is a great framework for professional developers.

    Wanna know why?
    Because it will allow more non-developers, wannabe-developers and dumb people in general to create billions of broken applications that we, professional developers, will be called to rewrite later at very profitable rates.

    Good job David!

  • I concur with Joel Abrahamsson. What are the best practises for TDD and Microsoft.Data.dll?

  • I think your caveats section needs to be much much bigger.

  • Really enjoying the comments here. :D

  • Wow its like a bad asp classic app I was just hired trip rewrite.

    Way to single handley destroy code seperation and testability.

  • There's a huge population of web developers who couldn't care less about testability, separation of concerns, SOLID principles, ORMs, etc. They are the target audience for this, not you.

  • So I'm going to go a way different way than most of the comments you are getting at the moment:

    Can you please stop using blue underlined text for your section headers? I keep trying to click the damned things. :-)

  • @Ouch - the caveats section is the entire post. Or at least it should be, which is why I am surely the thousandth person checking both the current date and that of the post. More and more often, I find myself embarrassed to have been calling myself a .net developer for the last decade. :(

  • @Dan: True enough, but those "developers" aren't reading blog posts about upcoming releases.

  • Hmmm.

    Not testable, not scaleable, reminds me of LinqToSql. Another idea which will be dropped as soon as MS realise we are trying to create quality applications in an ever changing world. I cant think of any application I have written in the past 10 years that hasnt evolved, so what benefit is there in hard coding SQL statements?

  • If this is for beginners, MS has totally missed the mark. Active Record FTW.

  • I don't even know where to start. This is absolutely horrible and can't believe it is being presented as an amazing advancement.

  • This is an improvement that I would have expected to see back in .NET 1.x days, or even VB6. But in the day of .NET 4.0? Really? Why wouldn't you just use Entity Framework or Linq to SQL?

    I understands making something that works off the Dynamic object type, but does it really have to be this? You are just going to undo a ton of work that has been done over the last 8 years to get programmers to write better database access code.

    This may help to get small applications built rapidly, but I still think it is a bad idea!

  • Sigh.

    Do you guys in Redmond not actually follow at all what happens in the industry?

    Data access is a solved problem.

    But every, single, bloody implementation of data access from Redmond makes the same, bloody, mistakes, stubbornly ignoring all the advances made by the competition.

  • Will someone at Microsoft PLEASE stop this from getting out into the wild ? We do NOT need yet another data access method. Plain ADO.Net, EF and LTS have it covered. Please, please stop with anything new for data access.

  • @Troy Goode I removed the underlines from the header.

  • I think it's unfair to say nay to this thing. We have ORM, but doesnt mean everyone should use it. Heck, Java got many of the best ORMs, and they still have JDBC. No one is crying zealot complaint about the presence of JDBC there, and I'm sure everyone would welcome a more fluent API for JDBC.
    Which is exactly this thing.
    ADO.Net has super horrid API, it should really be retired. This API should replace the public API of ADO.NET. It should by no mean replace ORMs.
    I wonder though, people who have left comments here, do you guys always use ORM, create entity obejcts, etc everytime you write .net application? Do you all think ADO.NET API should cease to exist?
    Don't you ever write ETL tasks, data housekeeping and stuff like that? Many times you just want to use ADO.NET DataReader, perhaps DataTable and DataRow even.
    To have a more fluent replacement for ADO.NET API can only mean good thing.
    I'll repeat it again. This library should not replace ORMs. This should replace ADO.NET public API.

  • I am so glad to be dumping Microsoft for Linux and Ruby after being with them 20+ years from GWBASIC to C# 4.0.

    What a joke the stack is - and it just keeps getting worse.

    I dont even know Linux and I'm willing to learn it from scratch to get rid of you guys and all the 3rd party libraries and activations and software maintenance fees.

    Microsoft is a joke. Open source (MS open source doesnt count - it's a joke) certainly cant be any worse than the crap coming out of Redmond.

  • Look I really appreciate what you're trying to do, .NET stack for the PHP minded,but by the response I'm sure you can gauge that people are generally, well, unhappy.

    I see a lot of potential for this but for me the biggest problem is that writing inline SQL is a bit daft in this day and age when you have the likes of LINQ available. Why not use dynamics to rather create an inferred Entity Model?
    (stealing this from Ayendes blog...)
    Something like

    dynamic db = Database.Open("Northwind");
    dynamic query = from p in dc.Products where p.UnitsInStock < 20 select p;

    @foreach (var row in query)
    @row.ProductName @row.UnitsInStock

    That would be pretty awesome, and I think more what people expect in 2010.

  • come on, guys. He can't be serious. He is saying that that kind of code is very common. If someone calls a database, hard coding the entire connection string any time he needs it, without a strategy for transaction management and IN THE CODE BEHIND of a page... he must be kidding.

    BTW, I think if you call new SqlConnection("Northwind") it works.

  • The comments on this post is really astonishing. Almost everyone is unhappy that the library makes connecting to a database and getting data from it easier for the casual weekend website builder.

    I do not think it is intended to be for the enterprise developer building Line of Business software. For the casual website with a Sql Compact 4 database, ORM is totally overboard. So is building Repositories or separate DAL libraries.

  • I agree with you jvanrhyn!

  • OK--it's for the weekend developer. But a LINQ syntax would be MUCH better with an inferred entity model. Weird that some here are asking for unit tests? Who's doing unit testing over the weekend?

  • Jesus. Just what the world needed. Why not put a couple of runtime wrappers around Linq-to-SQL instead? E-z enough to generate the entity classes at runtime with the right mapping attributes, and then let L2S do what it is good at.

    Sorry for not joining the cheerful crowd... :)

  • The simplified API + 10
    The inline query rather than a strongly typed LINQ Query -10000

  • Those of you who are worried about SQL Injection attacks are being stupid. Sure you have to escape your parameters or risk danger. But you have to do that anyways or the query will fail if someone includes a single-quote.

  • Ohh yes, soo much easier to read. (I am being sarcastic)
    Before I atleast got back an SqlDataReader, and I could look up the documentation for that. In most cases I would just know what to do based on intellisense. Now you want to give me a dynamic and make me guess what to do with it? Yes, this is indeed a back-to-2002 post...

  • @Craig
    Sorry this won't compile, can't use anything dynamic with expression trees only with regular funcs

  • Dude!

    Give me ONE benefit for this, instead of using for instance EF? Either you generate the model from an existing db or you let EF generate the db from your models.

    Don't tell me that's overkill for a small hobby-project

  • If you really want to ease the story for those not using VS, designers, whatever, then the LINQPad story is excellent - you just connect to whatever database and it builds the data context for you.

    This is effectively what some other recommendations are giving, but I'd like the returned context to be of a subclass GeneratedDataContext (or whatever) that could include properties for table names, column names, etc. that were generated so someone could loop over those to (for instance) do a simple dump of the schema of the database, or generate some dynamic linq to dump all the contents, or whatever.

    You could still treat the generated context as dynamic and get the linq behavior of querying the tables as individual properties off of the returned context.

  • Just one word: FAIL.

  • It is very sad to see someone from Microsoft promoting this kind of API, even if it's intended for amateur programmers.
    Anyone capable of understanding how to create a dynamic ASP.NET web site, even if it's not a professional programmer, should be capable of using in a basic form something like LINQ to Entities or any other ORM.

    Such a code is filled with bad practices: hard-codded SQL strings for data access, potential for SQL injection, data access code in UI layer etc.. Sad..

  • @Jonathan Allen: any decent programmer nowadays knows that the solution to SQL injection is not to escape the values, but to simply use typed ADO.NET parameters for all data entered by the user - it's much easier and much less error prone than escaping..

  • Even putting aside all the horrible things that come with this approach,
    Why would I write this instead of :

    from product in context.Products
    where product.UnitsInStock < 20
    select product;

    And I get strong typed object instead of a Dynamic...
    I don't see the benefit.

  • I am not going to judge on the merits of what architecture is right, since it varies depending on what you want to do (e.g. An ORM is overkill for a simple web site that displays read-only data). However, I am surprised that we are still looking to code to make things easier. Object and data binding makes things easier as is is point, click, drag and set properties. If you want to compete with LAMP then you need to do it better, not the same.

  • After seeing this, I can't wait to see "ASP.NET WebPages with Razor Syntax"!!!

  • "easier for the casual weekend website builder."

    Too bad the casual website builder is not reading this blog, nor will he/she ever read it. It's more likely that an inexperienced developer will see this and start using it in a real project where it does not belong.

    Your target audience isn't going to visit MSDN or ASP.NET weblogs. If it's difficult enough that they need to keep looking up tutorials to do this or that, then they're just going to use something else that is simpler to create their site with.

  • I always knew that every room in Microsoft is too busy with it's own project that they are not aware of what is being done in the next room. That's why we have EF and LinqToSQL and we have WCF data services and RIA services and so on. But it's the first time I get the impression that there is a room that have been sleeping for a whole decade.

  • It may be one of the following problems;
    *This post is from 2002.
    *This post is from april fools.

  • I think you've made an error with the namespace. Shouldn't it be Microsoft.Bob.Data?

  • @José: your joke is from yesterday. Unfortunately, 235,462 people have done it before.

  • I love the comments... I'm guessing 80% have never delivered a commercial product.

    While the sample leaves a bit to be desired – it would certainly help a lot of people I know who spend < 1% of their time “coding”.

    Now figure out a way to solve the $6.95/month problem (Azure I'm guessing...) and you're headed in the right direction.

    Please don't leave the real cmz developers in the cold (Silverlight’s over simplicity is killing me every day).

  • Pingback from Why can't the beginners and the elites co-exist?

  • Keep going, need more simple light-weight web tools.

  • Ms is trying to reinvent the wheel.
    It had it in VisulFoxPro a much better mechanism of handling data. That was:
    - query a server based engine (like SqlServer, PostGresql)
    - Obtain a cursor (like DataTable in .net)
    - Against that cursor you could program with sql commands (select,insert,etc) This was very powerfull as you could combine the power of server with the power of local engine.

    In .net this postprocessing part is VERY BAD.
    - You have a hybrid language Linq (which does not support insert,delete,update like commands against DataTables, it is very abstractized to work in general against very different objects.
    But we talk about tables here.
    You can buy such a sql based mechanism from, where a guy made this necessary stuff for productivity. Congrat's to him, shame to MS.

    - Now MS presents a local engine file based with easy access. Very good.
    But make a link between server access and this file based access so when querying a sqlserver table the result if i want will be placed directly into a local sdf file(persistent or not-like a temporary cursor depending of developer needs). This should be done directly, fast, with built in mechanism.
    After this we could query the sdf local engine using sql language combining different resultsets and have joy when dealing with data using standard sql language.
    This will make also the datatables persistent with simple mechanism.

    Ms had all the stuff in VisualFoxPro. If it decided not to suport that great tool, at least make in .net those missing stuff.
    A few days ago Ms launched a project called LightSwitch, and the people applaused when at a click at a button they exported in excell some data. In vfp we did from 15 years with a line of code with no applause. Just to understand where the things evolved...

    Hope someone from Ms read this...

    Camil Ghircoias

  • Are you guys serious? This has got to be the worst thing ever from Microsoft. Why would enterprise take you seriously if you keep doing these absurd pony tricks.

  • Anyone saying that EF should be used please explain how to use it with geometry and geography types.

    I also think it doesn't work with hierarchy IDs either but i haven't tried it.

    I guess i shouldn't worry about using them since data access is a solved problem.

  • This is great news. Lets hope Microsoft will complete its work in ADO.NET to integrate all the notion of Cursors, pessimistic locking and such as they are still used even though they are still in COM. For those that wonder, Cursors are used in very specific contexts. They are part of the tools to access a database.

  • I loved it. It seems many comenters here have trouble in accepting alternative solutions and require that there's "just one way to do it".

    Really, really nice. Good job!

  • Another year, another data access mechanism from Microsoft. (sigh).

    Just because some people don't know how to work with databases, the idiot-proofing will continue until only idiots will use it.

    What - frustrated? Me?

  • Thats a very nice article. Thanks buddy.

  • My concern is not the amateur developer creating a little web site; my concern is the vast number of mid-level, in-house IT developers who create business applications. My experience has been that standards are hit-or-miss and often the applications are exposed to the public or handle sensitive data. I have seen this in healthcare, benefits enrollment, financial processing, etc. These developers will be more inclined to use this sort of API without realizing the terrific level of care that is needed to use it safely.

    Perhaps someone who compares me to a "fascist" will have a different opinion once the credentials to his medical account is stolen through a simple SQL injection attack. Please understand, this API will not be used for church or book reader clubs. It will be used in hundreds of thousands of business applications.

    This API by itself is not bad but the mistake is marketing it toward the very people who will use it incorrectly. I see it as an expert-level API, like a kernel driver, rather than something that should ever be introduced to an inexperienced developer.

  • ITT: "Intelligent" developers completely missing the point.

  • The comments are fascinating. Techno astronauts

    My favorite so far is 'Data access is a solved problem'. I am thinking getting a T-shirt saying that.

  • I find it entertaining when a commenter tries to get under the radar by posting a quality comment the first time than spam thereafter.

  • I like how "The smartest guys in the room" think that everything MS does is for them and must be approved by them.

    "Your so vain, you probly think this post is about you"

    and by the way, if your a hobbyist developer and search Google for Microsoft.Data this blog will not be returned in the search results because Google knows that hobbyist developers searching for help do not read blogs.

  • Oh and one more thing, regardless of whether this MS.Data thing was released or not developers that are ok with writing bad code will continue to write bad code even with the available tools designed to help them write good code.

    and lastly... I have seen plenty of over engineered apps that are harder to maintain than anything my 8 year old could write.

  • While I don't support this Microsoft.Data approach because I consider myself a professional developer, to those that complain that it will encourage writing 'bade code' I will say this - many people don't care! What they care about is maximizing profit and minimizing expense in a competitive environment, on a cash flow (i.e short term) basis. Over the years I have known a lot of developers who write bad and sometimes VERY bad code. Guess what - they get paid more than I do and do fine thank you very much. The somewhat brutal reality of doing business these days seems to escape the purists. Sorry guys, you can't win.

  • After installing WebMatrix and searching the file system for Microsoft.Data.dll, no dice. Please reply with directions on how to reference the library.

  • Microsoft.Data.DLL is now named WebMatrix.Data.dll.

  • Wow, it's amazing to see how closed minded so many people are to this. I've used it and I love it.

    Does it work with Oracle however?

Comments have been disabled for this content.