Still Hangin' with the Ministry Platform API Part 3

This is the third installment of the Ministry Platform API series. For a complete look, take a look at my other posts here:

1. MP API Part 1
2. MP API Part 2

Okay so we're here to take another crack at getting the most out of Ministry Platform's API. Last time we set most of the foundation for what we're going to do by using reflection and the factory pattern. This time around we'll look at how to wrap the stored procedure method and then discuss how to tackle updating.

Using Stored Procedures

Getting the stored procedure method working is huge when using Ministry Platform's API. It's currently the only supported way to read anything from the database. Which is weird. But that's something to address in another post.

To get started, first we need an extension method that can turn a DataTable object to an IEnumberable of type T.

public static IList<T> ToList<T>(this DataTable table) where T : new()
{
    IList<PropertyInfo> properties = typeof(T).GetProperties().ToList();
    IList<T> result = new List<T>();

    foreach (var row in table.Rows)
    {
        var item = CreateItemFromRow<T>((DataRow)row, properties);
        result.Add(item);
    }

    return result;
}

private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties) where T : new()
{
    T item = new T();
    foreach (var property in properties)
    {
        property.SetValue(item, row[property.Name], null);
    }
    return item;
}

The above does as advertised using reflection. Not much to add in commentary. Now onward to our new stored procedure methods.

    public IEnumerable<T> ExecuteStoredProcedure<T, S>(string storedProcedureName, S requestObject)
        where T : new()
    {
        var data = _client.ExecuteStoredProcedure(_userName, _password, storedProcedureName, SerializeForAPI(requestObject));

        if (data.Tables.Count < 0)
            return null;
        return data.Tables[0].ToList<T>();
    }

    public IEnumerable<T> ExecuteStoredProcedure<T>(string storedProcedureName, dynamic requestObject)
        where T : new()
    {
        var data = _client.ExecuteStoredProcedure(_userName, _password, storedProcedureName, (String) SerializeForAPI(requestObject));
        if (data.Tables.Count < 0)
            return null;
        return data.Tables[0].ToList<T>();
    }

The first method accepts two generic type parameters - the first one is the return type and the second one is the object type that contains the parameters you want to send to the stored procedure. And in case you're not into that, you can just pass a dynamic object in instead. But what if you have a stored procedure that returns multiple sets?

    public GridReader ExecuteStoredProcedureMultiSet<S>(string storedProcedureName, S requestObject)
    {
        return new GridReader(_client.ExecuteStoredProcedure(_userName, _password, storedProcedureName, SerializeForAPI(requestObject)));
    }

    public GridReader ExecuteStoredProcedureMultiSet(string storedProcedureName, dynamic requestObject)
    {
        return new GridReader(_client.ExecuteStoredProcedure(_userName, _password, storedProcedureName, SerializeForAPI(requestObject)));
    }

The GridReader class is a class that contains all the of the sets returned from the stored procedure. You just have to call the Read method and pass the correct type. The catch? You have to know the order in which the sets are returned. So if you're returning, for example, car objects in the first set, pass the Car type in on the first read. Here's the class:

public class GridReader : IDisposable
{
    private readonly DataSet dSet;
    private int counter = 0;

    public GridReader(DataSet initial)
    {
        dSet = initial;
    }

    public IEnumerable<T> Read<T>()
        where T : new()
    {
        IEnumerable<T> list = dSet.Tables[counter].ToList<T>();
        counter++;
        return list;
    }

    public void Dispose()
    {
        dSet.Dispose();
    }
}

So if you want to call a stored procedure that returns only one data set use this:

var dataContext = new MinistryPlatformDataContext();

var result = dataContext.ExecuteStoredProcedure<Contact>("api_GetContacts", new { contactid = 1 });
//do something here

And if you want to call a stored procedure that returns multiple sets...

var dataContext = new MinistryPlatformDataContext();

GridReader result = dataContext.ExecuteStoredProcedure("api_GetContacts", new { contactid = 1 });
var firstResultSet = result.Read<Contact>();
//do something here

Okay... But what about Updating Records?

Ahhhh... yes. That thing. We were doing so good until we asked THAT question. Well, unfortunately there's no easy answer for that one. Can we make something that works as easy as the create and stored procedure methods? Um, maybe. At least not using the same tactic we did for those two.

What we've discovered over the past several posts is that we're basically trying to create an ORM. Or at least a very simple one. And we didn't even know it. Weird how that happens huh?

This is really why there's a lot (and I mean, alot) of ORMs out there that have trail-blazed these issues long before most of us even knew how to. What we need in our situation is something called tracking. It's a basic function of most ORMs. Most ORMs will keep track of how an object is changed after it's been retrieved from a database. Then upon updating, it already knows which fields have been modified and updates ONLY those. This is really our only option to take with regards to the MP API.

Wrap it Up

Well that's it for this post. We'll take a look at how to implement tracking next time, and maybe we'll even get crazy and pull out those old rusty T4 template skills.

Tagged with ASP.NET Ministry Platform