Introduction to Microsoft.Data.dll
UPDATE!
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)) {
connection.Open();
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");
@ObjectInfo.Print(db.Connection)
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;
AttachDbFilename=|DataDirectory|\Northwind.mdf;
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):
-
A database file under the data directory (App_Data) that matches the name plus one of our supported extensions (.sdf or .mdf)
-
A connection registered with and Database.RegisterConnectionString, Database.RegisterFile APIs
-
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)
Database.Open("Northwind")
Register API
// _start.cshtml
Database.RegisterConnectionString("MyDatabaseServer", "Data Source=192.168.1.20;Initial Catalog=MyDb")
// SomeFile.cshtml
Database.Open("MyDatabaseServer")
Configuration
// web.config
<configuration>
<connectionStrings>
<add name="Northwind2"
connectionString="Data Source=.\SQLEXPRESS;Integrated Security = true;Initial Catalog=Northwind"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
Database.Open("Northwind2")
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.
Caveats
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:
<configuration>
<appSettings>
<add key="systemData:defaultProvider" value="System.Data.SqlClient" />
</appSettings>
</configuration>
Today I covered connecting to the database, next time I’ll concentrate on querying. Stay tuned…