Introduction to Microsoft.Data.dll

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 < 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.

Read more: Unhandled Exception

Posted via email from .NET Info