New Posts New Posts RSS Feed: Using Stored Procedures with Silverlight (Async execution) no results returned
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Using Stored Procedures with Silverlight (Async execution) no results returned
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105 		Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post Topic: Using Stored Procedures with Silverlight (Async execution) no results returned
    Posted: 22-Jul-2009 at 2:47pm
Using DevForce Silverlight version 5.1.1.0 beta, I have a SL 3 beta project that was created from the DevForce templates (so 2 projects in the solution).  The database is SQL Server 2008.
 
I'm trying to load an Entity from a stored procedure where the procedure doesn't return a real entity (in this case, it returns a generated key value).   I've gone so far as to define an actual database table so I can create an entity type from it (rather than creating the 'fake' table in the edmx) that has an integer key field along with the other fields that match the select statement from the proc.
 
The proc doesn't use a RETURN value (since that doesn't seem to work with EF/DevForce) and it just returns local variables in a select statement (just to test this concept out).
 
The entity is defined exactly the same as the select statement (same column names; same datatypes).  I've added the function import in the edmx and it's set to return an entity of the type.  I've even tried inserting the row into the db table from the procedure and then just retrieving from it.
 
The problem is that with an Async query, the procedure executes but the entity doesn't get loaded with the data.
 
Since Silverlight needs to run this ASYNC, I build the query using the StoredProcQuery and then I use ExecuteQueryAsync to run it (I have an event handler for the callback with the right event args):
 
    StoredProcQuery query = EntityManager.GetNewKeyQuery('Test', 1);
    EntityManager.ExecuteQueryAsync (query, callback, null);
 
The proc physically runs on the db (I see it in SQL Profiler) but no data exists in the event args once the callback handler is invoked.  It acts like it returns control to the handlre before the results are available.
 
Any thoughts?
 
 
Back to Top
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105 		Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post Posted: 22-Jul-2009 at 5:17pm
Found the problem (just noting it here in case it helps someone else). 
 
If the procedure just does a SELECT on local variables (or I suppose mixes them with actual columns from actual tables) then EF (I think this is an EF thing and not a DevForce issue) doesn't seem to think the datatype matches the entity type. 
 
   DECLARE @myNewKey BIGINT
   SET @myNewKey = 1
   SELECT @myNewKey
 
If I wrap a convert around the variable in the select, then EF populates the entity correctly:
 
   SELECT CONVERT (BIGINT, @myNewKey)
 
I mentioned in my original post that I tried replacing the local variable select with a select against a physical table but it didn't work.  However,  I was inserting a row into that table in the procedure and I had already inserted a test row with the same value (that I forgot to delete) so I thought it didn't work but that was because when I ran it again, there was already a row with that value in the table and after looking at the DebugLog.xml I saw that error.  Clearing the table and then re-running worked. 
 
So the issue is resolved.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down