Multi-result Stored Procedure

Question

i am using Devforce 2012, i have a need to return a multi-result set from a stored procedure to bind to a report (related entities or DataSet). Can this be done in DF 2012?

I have added the stored procedure to the EDM but the result contains only the first table.

Thankyou in advance.

Answer 1

You can't do this directly with the StoredProcQuery, but you can via an invoked server method. It's a little tortuous, the server method needs to use the native connection to execute a reader and process the results, and then return a DevForce EntityCacheState. You then load the EntityCacheState into your EntityManager. It would look something like this:

-- on the client --

  var em = new NorthwindEntities();
  var ecs = em.InvokeServerMethod("Mynamespace.MyClass, MyExe", "TestProc") as EntityCacheState;
  em.CacheStateManager.RestoreCacheState(ecs);

On the server, it's a bit tricky to get the connection - you can easily create an EF ObjectContext from the connection string, and then from that you create a DbContext, and then open and use the connection. For example:

-- on the server

[AllowRpc]
public static EntityCacheState TestProc(IPrincipal principal, EntityManager em, params Object[] args) {
  var cs = ((ClientEdmKey)em.DataSourceResolver.GetDataSourceKey("NorthwindEntities")).ConnectionString;
  using (var ctx = new ObjectContext(cs))
  using (var db = new DbContext(ctx, true)) {
    var cn = db.Database.Connection;
    cn.Open();
    var cmd = cn.CreateCommand();
    cmd.CommandText = "[dbo].[mystoredproc]";

    // Let's say this proc returns Customers and Suppliers
    // Read each result set and attach entities to the EM

    var reader = cmd.ExecuteReader();
    var customers = ctx.Translate<Customer>(reader);
    em.AttachEntities(customers);

    reader.NextResult();
    var suppliers = ctx.Translate<Supplier>(reader);
    em.AttachEntities(suppliers);

    // Return the EntityCacheState to the client
    return em.CacheStateManager.GetCacheState();
  }
}