Easy data handling using Massive [a first look]

Massive is a nice ~400 LOC’s C# class that wraps your SQL tables and gives them a set of nice dynamic functionality. It’s written by Rob Conery (the creator of Subsonic among other things).

I made my first attempt using it the other day for a current project of mine and I came to like it a lot very quick – and I can already say I will probably use it for most of my data needs in the near future.

Here’s some notes from my first experiences with it. The official source of information about Massive should of course be with the author himself: WekeRoad: Rob Conery’s blog

Git’ting the source

To begin using it within an asp.net web application, you need to drop a copy of massive.cs from the Massive repository at GitHub into your App_Code folder.

Connection string

Having the cs in your App_Code you need to set up a connection string in web.config. Here’s one for an Sql CE 4 database:

<connectionStrings>
  <add name="MyConnectionString" connectionString="data source=|DataDirectory|MyDatabase.sdf"  providerName="System.Data.SqlServerCe.4.0"/>
</connectionStrings>

Getting some data

After doing so you can start getting data out of your database like this:

// Create a DynamicModel agains the db defined in a connectionstring and a named table
var tbl = new Massive.DynamicModel("MyConnectionString", "[MyProductTable]");
// Get all rows in the table filtered by a where expression
var products = tbl.All(where:"Category=@0", args:"MyCategory");
foreach (var item in products) {
    <div>@item.Name</div>
}

The above creates a T-SQL “SELECT * FROM MyProductTable WHERE Category=’MyCategory'” and runs it on the database, retrieving the resulting rows as IEnumerable<ExpandoObject> where the row objects has got all the columns dynamigacally mapped as properties – item.Name, item.Price and so on.

Massive assumes you are using “id” as the primary key column. If you’re not you need to define it:

var tbl = new Massive.DynamicModel("MyConnectionString", "[MyProductTable]", "myId");

You can also add column selection, orderby and limit:

var products = tbl.All(where:"Category=@0", orderby:"Name", columns:"Name, Category", limit:10, args:"MyCategory");
foreach (var item in products) {
    <div>@item.Name</div>
}

Add more parameters to the Where:

var products = tbl.All(where:"Category=@0 AND Date>@1 AND Deleted=0", args:new object[]{"MyCategory", myDate});

The result is an IEnumerable<dynamic> and you can run Linq-stuff on it (but remember it’s most efficient to filter the data with the SQL as above):

var products = tbl.All().Where(p => p.Price> 1000).OrderBy(p=>p.Name);
var grid = new WebGrid(products);
@grid.GetHtml()

Inserting new data

An insert can look like this:

tbl.Insert(new {Name="New product", Category="MyCategory"});

Or like this:

dynamic newProduct = new ExpandoObject();
newProduct.Name="My Product";
newProduct.Price=1200;
newProduct.Category="My Category";
tbl.Insert(newProduct);

Updating data

A single update can look like this:

// Get single row with id = 193
var product = tbl.Single(193);
if (product!=null)
{
 product.Price = 190;
 tbl.Save(product);
}

Or like this:

// Get single row with productname = "MyProduct"
var product = tbl.All(where:"Name=@0",limit:1,args:"MyProduct").SingleOrDefault();
if (product!=null)
{
 product.Price = 190;
 tbl.Save(product);
}

Or like this which is using Id (the primary key) to make Massive understand it’s supposed to be an update:

dynamic existingProduct = new ExpandoObject();
existingProduct.Id=100;
existingProduct.Name="My Product";
existingProduct.Price=1200;
existingProduct.Category="My Category";
tbl.Save(existingProduct);

If you try to save a non existing record (with id=something) you will get an exception. However if you leave out the id the Save will work as an Insert.

Updating multiple records at a time

Update multiple records within one transaction:

// Get all products with price 180 and make them to an array to be able to edit them
var products = tbl.All(where:"Price=@0",args:180).ToArray();
foreach (var product in products)
{
 product.Price = 190;
}

// Save all in one DB transaction:
tbl.Save(products);

Delete

// Delete record with id = 999:
tbl.Delete(999);

or

tbl.Delete(where:"Category=@0",args:"MyCategory");

POCOs

In the next part we’re going to look at how to use common classes together with Massive.

Current issues (2011-03-11)

If you get an error on saveing an update. cannot modify name = Id” it might be because Massive is case sensitive on pk-column when it’s trying to create an update query. Fix that by defining your id-column yourself, “Id” and then use that exact caseing in your code: myrow.Id = 1;

Also there’s a small issue with the pager: https://github.com/robconery/massive/issues/16#comment_852654

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s