Nice new features

Hot onto NuGet now, Simple.Data 0.6.6 has a couple of nice new features to make life even easier.

Cleverer type handling in Simple.Data.Ado

The Ado adapter now tries much harder to make the data that it is given work with the underlying provider. In practice, this means that it will check to see whether the value is a “known-good type”, and if it’s not, it will try calling “ToString” to see if it returns something other than the type’s full name; if it does, it will pass that string. This means that, amongst other things, you can pass the dynamic values from Nancy’s Request objects across without casting them. So technically, you can now say

db.Comments.Insert(Request.Form)

although I wouldn’t recommend it.

Child rows returned as Query

Up to now, if you used the inferred detail property feature in Simple.Data, what you got from that property was an IEnumerable<dynamic>. Now, you get a SimpleQuery, which means you can do filtering, ordering and so on on those detail rows.

This feature means that the query comes pre-created with some criteria, so I’ve changed the way the Where method works (still in beta, I’m allowed). Now, Where will combine any existing criteria with the specified criteria. If you want to completely replace any existing criteria, use the new ReplaceWhere method.

ToScalarOrDefault

I don’t think I mentioned this last time, but there is now a SimpleQuery.ToScalar method which, as long as your query returns one row with one column, will return that single value. If there is not exactly one row, an exception will be thrown. 0.6.6 adds ToScalarOrDefault, which will return null if no row was found, and ToScalarOrDefault<T>, which returns default(T).

Detail properties on Queries

By “detail properties” I mean those properties on records returned from Simple.Data which execute a further query on the database and return detail (or master) records. These are now available on SimpleQuery objects, so you can say:

db.Customers.QueryByTown(“London”).Orders.Count();

for example. This code runs only one command against your database; the Orders pseudo-property returns a new query which incorporates the criteria from the original Customers query. Thus your DBA is happy, because you are not doing that SELECT N+1 thing he/she hates so much. (Of course, if they’d just upgrade the server to that one with a PCI-e SSD array it wouldn’t matter anyway. But no. So here we are.)

An aside on writing APIs

If you check the commit history for Simple.Data, you’ll see that I implemented that last change twice. The first time I did something super-awesome with “rebasing” expressions and object references, and it all worked as if by magic. But then on the drive home, I realised that (a) that was the hard way, and (b) it hides too much information from adapter authors, who might be implementing this kind of functionality in a totally different way. So I re-implemented it in a much more obvious way, which also included a bit of much-needed refactoring, and it all works as it should and adapter authors won’t hate me any more than they already do.

That’s it for Simple.Data coding for this week; I’ve got to polish up my demo apps for DDD South West this weekend. The same mini-app created twice: once in WebMatrix with the data access layer that started all this; and once in Nancy using Simple.Data. Guess which one’s better.

Tweet by Brad Wilson:

“The Simple.Data blog post is built upon half-truths and sleight of hand. Microsoft.Data supports parameters. Your stuff is vuln to concat.”

Apology by me:

I didn’t know that Microsoft.Data supported parameters. Sorry.

And yes, Simple.Data is also vulnerable to SQL injection through concatenated SQL, as is anything that lets developers execute text SQL statements against a database.

However, what I’m trying to do with this project is explore other, non-text-based ways of interacting with a database that are not as complicated as full-blown ORMs.

One of the comments on my previous post asked about working with “real types”. It was something I intended to try, and I had a couple of ideas about how it might work. But one of the issues is that any value returned from a method or property on a dynamic object is also dynamic. So to get, for example, a User object back, the developer would need to use a cast or “as” conversion.

While I was working on the DynamicTable class in the Simple.Data code, I noticed one of the available overrides from the DynamicObject subclass was “TryConvert”. It turns out that this method is called when an implicit or explicit cast is attempted on the object.

ExpandoObject, which is what was being returned by the Find methods, doesn’t implement this method, so I created my own ExpandoObject called DynamicRecord and added the TryConvert override. So now, using the Simple.Data.Database class, you can do this:

var db = Database.Open(); // Connection string in config.
User user = db.Users.FindByName("Bob");

And the dynamic object will be magically implicitly cast to the User type, with compile-time checking and IntelliSense intact.

I’ve only proved this with happy-path tests so far (exact matches for all properties), and obviously it’s going to rely on the database table having column names which match the type, but it’s a very neat solution and extremely new-developer-friendly.

Updated code at http://github.com/markrendle/Simple.Data

Update – 06-Aug-2010

This post was inaccurate, in that it failed to acknowledge that Microsoft.Data supports parameters in its text querying. I have made changes to address this inaccuracy.

What is it?

It’s a very lightweight data access framework that I wouldn’t dream of calling an ORM. It uses the dynamic features of Microsoft .NET 4 to provide very expressive ways of retrieving data. It provides non-SQL ways of doing things, inspired by the Ruby ActiveRecord library. It also provides SQL ways of doing things, but with features to protect against SQL injection.

Why?

Because Microsoft have identified a gap in the data access toolkit ecosystem which they think scares casual web developers who are used to environments like PHP. These developers neither want nor need a full-blown ORM like NHibernate or Entity Framework, and they don’t want to deal with the complexity of ADO.NET connections, commands and readers.

Fair enough.

Microsoft have recently released a preview of a new library, Microsoft.Data.dll, which aims to serve these developers. It provides various simple ways of opening up a database, and then lets you run SQL against it, which is how casual developers are used to working with MySQL in the PHP world. They build up their SQL strings and then they execute them.

The problem is, that’s wrong. And I believe that attempting to attract people to your stack by giving them a really easy way to carry on doing things wrong is also wrong. The right thing to do is give them a way to do things right that is as easy as, if not easier than, what they have been using.

Simple.Data is my attempt at that.

Example?

Using Microsoft.Data, you would should query a Users table like this:

var db = Database.Open(); // Connection specified in config.
string sql = "select * from users where name = @0 and password = @1";
var user = db.Query(sql, name, password).FirstOrDefault();

But you could query it like this:

var db = Database.Open(); // Connection specified in config.
string sql = "select * from users where name = '" + name
+ "' and password = '" password + "'";
var user = db.Query(sql).FirstOrDefault();

A lot of people are quite cross about this. One of the main problems they have is that building query strings in this way opens your application up to SQL injection attacks. This is a common pattern in PHP applications written by new developers.

To achieve the same task with Simple.Data, you can do this:

var db = Database.Open(); // Connection specified in config.
var user = db.Users.FindByNameAndPassword(name, password);

That’s pretty neat, right? So, did we have to generate the Database class and a bunch of table classes to make this work?

No.

In this example, the type returned by Database.Open() is dynamic. It doesn’t have a Users property, but when that property is referenced on it, it returns a new instance of a DynamicTable type, again as dynamic. That instance doesn’t actually have a method called FindByNameAndPassword, but when it’s called, it sees “FindBy” at the start of the method, so it pulls apart the rest of the method name, combines it with the arguments, and builds an ADO.NET command which safely encapsulates the name and password values inside parameters. The FindBy* methods will only return one record; there are FindAllBy* methods which return result sets. This approach is used by the Ruby/Rails ActiveRecord library; Ruby’s metaprogramming nature encourages stuff like this.

[A section which wrongly implied that Microsoft.Data did not support parameterized queries has been removed here.]

More examples…

Inserting using named parameters:

db.Users.Insert(Name: "Steve", Password: "Secret", Age: 21);

Executing an update:

db.Execute("update Stock set Current = Current - 1 where ProductId = ?", productId);

(Planned) inserting/updating using an object:

db.Stock.Insert(newStockItem);
db.Stock.UpdateByProductId(stockItem);

Roadmap

This project is more of a constructive criticism of Microsoft’s preview than anything else. I’m going to develop it a bit further (for example, the “planned” syntax support above), mainly as an exercise. One thing I’d maybe like to explore further is whether it can be used as a layer over NoSQL stores as well as RDBMS.

The project is hosted at http://github.com/markrendle/Simple.Data for anybody who wants to download and play with it.

I’d really appreciate feedback, so please do use the comments if you’ve got any criticisms, suggestions or encouragement to express.