Open Package Manager Console and execute the following commands:
- install-package Simple.Data.Ado
- install-package Simple.Data.MySql
- install-Package Mysql.Data
These install, from the top, the Simple.Data core libraries, the Simple.Data MySql library and the MySql ADO provider.
The MySql dll needs to be in the same folder as the Simple.Data.Mysql library, so ensure the project reference to this dll has its Copy Local setting to True. This will ensure it gets deployed to the bin folder when the project is built.
Now the fun part... Simple.Data is built on the dynamic model in .Net 4; database is a dynamic object and at runtime interprets methods and properties into SQL statements, deriving the names of tables, columns etc from the context of the properties. I'm not sure that makes sense, so here are a couple of examples:
These two lines create a database connection, and insert a row into the city table. The table name city is derived from the property with the same name. ("sd" is a namespace alias, to prevent ambiguity with WebMatrix.Data.Database, ignore it if you can)
This line is an example of how to get Simple.Data to construct a query along the lines of
but with the goodness of parameterisation to defend against Sql injection attacks.
For ordering data, use the OrderBy and ThenBy notation:
A downside with the dynamic model is there's no intellisense, so you can't see the database objects you want to query at development time. However, there's a tool that'll help with that, Simple.Data.Pad. Once you've copied the correct database provider dlls to the runtime directory (I needed Simple.Data.Mysql.Mysql40.dll and mysql.data.dll), you can connect to your database and as you type it'll suggest what you can do with your objects (in a nice way)
In summary, Simple.Data is easy to use and a lot of fun - far more so than hand-crafting Sql statements, especially if you want the additional goodness of being able to swap around your database providers without ruining your client code. I've only scratched the surface here, but you can learn more at http://blog.markrendle.net/