How do I call stored procedures with Output parameters in Ormlite.Mysql ?
I’m currently generating the query (example: CALL IncreaseWeblinkClicksCount(@id,@amount)) and using db.SqlList<T>(generatedQuery, commandParameters) to get list, but this doesn’t work with parameters with direction = output.
I get the error
"OUT or INOUT argument 4 for routine -db.GetCompanySearchResult is not a variable or NEW pseudo-variable in BEFORE trigger"
OrmLite didn’t have good API’s for output params so you would’ve had to just use the underlying ADO.NET IDbConnection/IDbCommand to add them (e.g. http://codingboys.blogspot.com/2012/07/output-parameter-in-stored.html)
But I’ve just added some new API’s to help with this in this commit: https://github.com/ServiceStack/ServiceStack.OrmLite/commit/0f17bd3a683d858aec816ea757b5d48d541ae96c
Basically it now lets you call a SP with:
var cmd = db.SqlProc(“spSearchLetters”, new { pLetter = “C” });
var pTotal = cmd.AddParam(“pTotal”, direction: ParameterDirection.Output);
var results = cmd.ConvertToList<LetterFrequency>();
Where results is populated with the returned resultset whilst pTotal.Value has the value of the out parameter.
You can also use a new overload for SqlList which takes a closure but requires a little more boilerplate:
IDbDataParameter pTotal = null;
var results = db.SqlList<LetterFrequency>(“spSearchLetters”, cmd =>
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.AddParam(“pLetter”, “C”);
pTotal = cmd.AddParam(“pTotal”, direction: ParameterDirection.Output);
});
This change is now on MyGet: https://github.com/ServiceStack/ServiceStack/wiki/MyGet