Stored procedures

As of v6.0.344-beta, PetaPoco includes native support for stored procedures. The new methods are:

// Execute a stored proc that returns a data set as IEnumerable<T>
IEnumerable<T> QueryProc<T>(string storedProcedureName, params object[] args);

// Execute a stored proc that returns a data set as List<T>
List<T> FetchProc<T>(string storedProcedureName, params object[] args);

// Execute a stored proc that returns a single scalar value
T ExecuteScalarProc<T>(string storedProcedureName, params object[] args);

// Execute a stored proc that does not return any data
int ExecuteNonQueryProc(string storedProcedureName, params object[] args);

Because parameters need to be added with names that correspond to the parameter names in the stored procedure, each argument to these functions needs to be either a POCO (each readable property will be turned into a named parameter) or a pre-constructed IDbDataParameter. Passing any value types or strings will throw an exception.

Here's an example of usage:

using (var db = new Database())
{
    var records = db.FetchProc<MyClass>("GetFooByCategory", new { Category = "Bar" });

    // OR

    var param = new SqlParameter("Category", "Bar");
    var records = db.FetchProc<MyClass>("GetFooByCategory", param);
}

Users of Firebird should note an oddity in the Firebird ADO.NET provider. While it looks like it supports named parameters for stored procedures, it actually just sends them in order, without regard for the name. So you'll need to be careful to pass things in order and not just assume that the names will sort everything out. See https://www.tabsoverspaces.com/227021-firebird-net-provider-and-calling-stored-procedures-with-parameters for more details.