postgresqlstored-procedurestelerik-open-access

Returning number of records updated to Telerik Entity Framework from PostgreSQL


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.


Solution

  • 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?