Stored procedures in FunkyORM

FunkyORM can run stored procedures on the same provider you use for queries. Those procedure calls ride a .NET micro-ORM, FunkyORM, which maps plain C# classes to SQL Server, PostgreSQL, MySQL, and SQLite. Version 3.7.0 of Funcular.Data.Orm is the release that adds those procedure calls to the provider you already hold.

Introducing Funcular ORM walks the class-to-table mapping, and the GitHub project is the provider source.

Lambda queries, inserts, and updates were already on IOrmDataProvider, but a lot of production databases still keep a procedure for a report, a batch, or a permissioned write, and until 3.7.0 you had to step around the ORM to run it, which meant a second connection path and a mapping you maintained by hand.

Calling the procedure

Call ExecProcedure, ExecScalar, or ExecNonQuery (and the async twins) on the same provider you already hold. ExecProcedure is the one that returns mapped rows, the same pipeline a lambda query uses, so the objects that come back look like the objects you already query, while ExecScalar is for a single value and ExecNonQuery is for work that does not return rows. Input-only parameters can be an anonymous object when you do not need a value written back. When you need OUTPUT or INOUT, use SqlParam, and after the call returns you read the value the procedure wrote back off that SqlParam instead of guessing from a result set. The procedure name can be an argument, a [Procedure] attribute, or a convention.

Support differs by engine

Each engine exposes only what it actually supports. That is the capability-based rule, and it is why a call that works on SQL Server can throw on SQLite instead of inventing a fake CALL. SQL Server and MySQL get the full set: ExecProcedure, ExecScalar, ExecNonQuery, output parameters, and the async forms. PostgreSQL is partial: CALL works for ExecNonQuery and ExecScalar, and INOUT values write back onto the SqlParam you passed, but ExecProcedure for a typed result set throws on purpose. PostgreSQL procedures are not SQL Server’s, so use a FUNCTION RETURNS TABLE and query it the way you already query tables. SQLite has no stored procedures, so every Exec* throws an exception that names the engine; you get that exception, not a mapped result set.

Your own provider type

If you implement IOrmDataProvider yourself, the new members are source-breaking: your class will not compile until those methods exist, which is the same kind of break ISqlDialect already caused. Code that derives from OrmDataProvider is unaffected, because the base type already has the new members.

Those procedure methods landed in Funcular.Data.Orm 3.7.0; PostgreSQL throwing on ExecProcedure is the documented hole, so query a FUNCTION RETURNS TABLE, or spell out the miss on the 3.7.0 release page.