(Teleport) Massive – aka .NET wrapper for inline SQL

Ok, maybe while writing this post I was listening to a little too much Bassnectar (hence the title). However, while planning out a new web app I bumped into this little (~400 lines) chunk of code called Massive by Rob Conery (http://wekeroad.com).

The reason it attracted my attention was because I wanted to build an ASP.NET MVC application on an old SQL 2000 database (ugh).

The problem I was having is I wanted an easily enumerable dataset (like with the Entity Framework). Problem was VS2010 doesn’t like using the Entity Framework (or LINQ 2 SQL) with anything lower than SQL 2005.

Luckily I found the perfect drop-in addition in Massive. With Massive you can call in-line (or ad-hoc) SQL, stored procs, and views just like you did back in the web forms days.
However in this case you can use LINQ-y like queries in your selects and it can return Enumerable to easily iterate in your MVC views.

Massive, according to Rob’s github page (https://github.com/robconery/massive), is a “wrapper” for your database tables utilizing the System.Dynamic namespace.

Requirements are as follows:

C# 4.0+
Almost any database (SQL Server 2000, Oracle, PostGre)

All you need to do to get this going is reference the right flavor CS file in your project (Download Massive Code)

 

Add your connection (replacing your information) as shown here:

<add name ="MyConnection" connectionString="Data Source=<your server>;Initial Catalog=<your database>;User Id=<userid>;Password=<password>" providerName="System.Data.SqlClient"/>

Create a class that wraps your table (like a little burrito) utilizing the primary key:

using Massive;

public sealed class TblMyTable:DynamicModel
{
public TblMyTable() : base("MyConnection")
{
PrimaryKeyField = "primaryID";
}
}

Then you can call that class like this on the controller:

/* calling a stored proc and pushing into ViewBag */

dynamic myTbl = new TblMyTable();

Viewbag.MyRecords = myTbl.Query("EXEC spGetMyRecords");

return View();

Then in my view (using Razor) I can put this:

<table width="800" id="myTable">
@{
foreach(var item in ViewBag.MyRecords)
{
<tr>

<td>@item.col1</td>

<td>@item.col2</td>

<td>@item.col3</td>

<td>@item.col4</td>

</tr>
}
}
</table>

Or if you’re feeling really MVC-y add a helper class by adding a HelperMethods.cshtml under a new App_Code directory, then dump the above code into it like this:

@helper MyRecordsIterator(dynamic myRecords)
{
foreach(var item in myRecords)
{
<tr>

<td>@item.col1</td>

<td>@item.col2</td>

<td>@item.col3</td>

<td>@item.col4</td>

</tr>

}

}

By doing this you can reference it in the view like this instead (helps to make it easy on your designers):

<table width="800" id="myTable">
@HelperMethods.MyRecordsIterator()
</table>

Digressing from helpers and getting back to Massive though, it’s a nice little way to pull in your legacy databases to MVC. And you can not only do SELECTS, but INSERTS and UPDATES as well.

Being DBA minded part of what I like about it is it offers a bit more control on the database side and can be less confusing than the Entity Framework to the uninitiated .

(Of course both have their trade-offs).

There is a ton more it can do and if this sounds like something you might want to use I encourage you to check Massive out.

It might be called Massive, but the learning curve really isn’t.