Link to home
Create AccountLog in
Avatar of satmisha
satmishaFlag for India

asked on

return multiple tables from sql db store procedure in wcf service

Hi experts,

I am using wcf to return data from the store proc which is working fine. Issue is that my sql server store procedure return multiple tables or having multiple select statements. While in wcf I am only getting 1st table. Is there any way to show multiple tables through wcf ?

thanks in advance, looking forward to hearing  from you.
Avatar of Alexandre Simões
Alexandre Simões
Flag of Switzerland image

You need to be more specific.
What is the method contract?

WCF doesn't care about your DB, it's the contract that defines what is returned.

My suggestion is that you create your business model POCO object that you want to be returned by the WCF method and fill it with the data that is returned from the Stored Procedure.
Avatar of satmisha

ASKER

Thanks alexandre, I am using
1. vs 2013
2. ADO.Net Data Entity Entity mode
3. Sql 2012

in my proc i am usigin like
select  * from A
Select * from B

and then making a operation contract in the file and returning like

[operationcontract]
List<complexType>

let me know if u require any information.

above return me only first table whereas I want to show data from both the tables.
So you're using Entity Framework and mapping a SP that returns multiple result-set to a a custom complex type.

If you debug your code, I believe that the complex type is not being filled properly, so WCF doesn't have anything to do with this problem.

This article does a great job explaining how to achieve that:
http://www.khalidabuhakmeh.com/entity-framework-6-multiple-result-sets-with-stored-procedures

Soory for the external link, I'll try to find some resources within EE
Thanks Alexandre, let me try this to solve my issue, i'll update you.
feel free to share in case if you find something else as well.
Thanks Alexandre,

Managed to get data from store proc but unable to show through wcf.

Don't know what to write in the return type of the Interface, enclosing snapshot fyr:

[ServiceContract]
    public interface IMultiResult
    {
        [OperationContract]
        void DoWork(); //What should be the return type here for sure not void :-)?
    }

public class MultiResullt : IMultiResult
    {
 public void DoWork()
           {
                EntityModelobj = new EntityModel();
                var results = obj.StoreProc(154081);
                var products = results.GetNextResult<StoreProc_Result2>();
                  // how could I pass result from both results & products through wcf service ?
                 // Also in model browser I could see two complex type
                // 1. StoreProc_Result1
                //2. StoreProc_Result2
           }
}

I followed this link:
http://www.dotnetfunda.com/forums/show/20774/calling-stored-procedure-with-parameter-and-multiple-result-set-entity

Looking forward to hearing from you.
If I understood it correctly,
var results = obj.StoreProc(154081);
var products = results.GetNextResult<StoreProc_Result2>();

Open in new window

hold the data of both result sets that come from the stored procedure, right?

So now, what you need to do is create your WCF response type that have these two as properties.
[DataContract]
public class MyResultType
{
	[DataMember]
	public List<StoreProc_Result1> Results { get; set; }

	[DataMember]
	public List<StoreProc_Result2> Products { get; set; }
}

Open in new window

And this is the object you need to fill and return from the WCF method.
[ServiceContract]
public interface IMultiResult
{
	[OperationContract]
	MyResultType DoWork(); //What should be the return type here for sure not void :-)?
}

public class MultiResullt : IMultiResult
{
	public MyResultType DoWork()
	{
		EntityModelobj = new EntityModel();
		var results = obj.StoreProc(154081);
		var products = results.GetNextResult<StoreProc_Result2>();
		
		var response = new MyResultType();
		response.Results = results;
		response.Products = products;
		
		return response;
	}
}

Open in new window

I have to tell you that these names are horrible, so take your time to name everything properly but in essence, this is the basic idea.

Now, there might be another issue here related to the response serialization.
As far as I remember, Entity Framework generated objects are not marked as Serializable.

Let me know.
Thanks for your prompt response. Yes you are correct that it holds both result set from db proc.

Yes for sure I'll give them meaningful name.

I am unable to see second result set data when i run this.

public MyResultType DoWork()
      {
            EntityModelobj = new EntityModel();
            var results = obj.StoreProc(154081);
            var products = results.GetNextResult<StoreProc_Result2>();
            
            var response = new MyResultType();
            response.Results = results.ToList();
            response.Products = productsToList();
            
            return response;
             
              // in the final result i am still not getting data against Product
              //however if i run them individually I do get
      }
ASKER CERTIFIED SOLUTION
Avatar of Alexandre Simões
Alexandre Simões
Flag of Switzerland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Solution provided was awesome though I also followed provided link:

http://www.codeproject.com/Articles/675933/Returning-Multiple-Result-Sets-from-an-Entity-Fram.

Thanks Alexandre.