return multiple tables from sql db store procedure in wcf service

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.

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>();

Select allOpen 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; }
}

Select allOpen 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;
	}
}

Select allOpen 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
      }

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.