I have a stored procedure in PostgreSQL defined as:
CREATE OR REPLACE FUNCTION g_changename(oldname character varying, newname character varying)
RETURNS integer AS
$BODY$
declare
k integer :=0;
begin
UPDATE test SET name = $2 WHERE name = $1;
GET DIAGNOSTICS k = ROW_COUNT;
return k;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION g_changename(character varying, character varying)
OWNER TO postgres;
The stored procedure tests correctly to return the number of records updated when
Select g_changename('a','b');
is performed within PgAdmin. When Telerik DataAccess is called to update the domain from the database, it compiles this as:
[MappedFunctionAttribute(Name="\"public\".\"g_changename\"", IsDeterministic=false, Backend=Backend.PostgreSql)]
public static Int32 ChangeName(string oldname, string newname)
{
throw new NotImplementedException();
}
Which, from all I've read, seems appropriate. However, how would you write a method to call it?
This fails since it calls the static procedure which is NOT implemented:
public int ChangeName(string OldName, string NewName)
{
var x = Nova.Data.Data.ChangeName(OldName, NewName);
return x;
}
And I don't know how to use the Telerik Metadata that the 'MappedFunctionAttribute' refers too.
Any help would be much appreciated as I've struggled with this for days now.
It appears that stored procedures defined with scalar returns can only be called within a LINQ statement--and then only with non-constant parameters. So the easiest way I could work this out was to by-pass entirely the template method (that was not implemented) and go straight to the database as:
public int ChangeName(string OldName, string NewName)
{
using (var ctx = new Nova.Data.Data())
{
OAParameter p1 = new OAParameter();
p1.ParameterName = "oldname";
p1.Value = OldName;
OAParameter p2 = new OAParameter();
p2.ParameterName = "newname";
p2.Value = NewName;
IList<Int32> rslt =
ctx.ExecuteQuery<Int32>("g_changename", CommandType.StoredProcedure, p1, p2);
ctx.SaveChanges();
return rslt[0];
}
}
Anybody have a better idea?