Multiple Result to Dataset

Question

Dear All,

 

I am using storesprocedure to get multiple table results. how to bind the multiple result to Dataset or datatable?

 

please anyone give me the solution .....

 

 

CREATE PROCEDURE [dbo].[SP_GETMASTERDETAILS]

AS

BEGIN

--Table1 - DEPARTMENT DETAILS

SELECT [DeptId]

,[Department_Code]

,[Depart_Name]

FROM EMP_DEPARTMENT_MASTER

--Table2 -DESIGNATION DETAILS

SELECT [DesignationID]

,[DesignationName]

FROM EMP_DESIGNATION_MASTER

END

 

 

[Function(Name="dbo.SP_GETMASTERDETAILS")]

[ResultType(typeof(EMP_DEPARTMENT_MASTER))]

[ResultType(typeof(EMP_DESIGNATION_MASTER))]

public IMultipleResults SP_GETMASTERDETAILS()

{

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));

return (IMultipleResults)result.ReturnValue;

}

 

 

Regards,

Pon Kumar

Answer 1

I am facing the exact same problem!  It worked in VS 2003, but not in VS 2005 or VS 2008.

 

Answer 2

This should work, here are different things you can try.

 

Code Snippet

using System;
using System.Data;
using System.Data.SqlClient;

public class T {
 public static void Main() {
  try {
   SqlDataAdapter adapter = new SqlDataAdapter("select 'A' as [a]; select 'B' as [b]", "server=.\\SqlExpress;integrated security=sspi");
 
   DataSet ds = new DataSet();
   adapter.Fill(ds);
   Print(ds); // will have "Table" & "Table1"

   ds = new DataSet();
   adapter.Fill(ds, "Foo");
   Print(ds); // will have "Foo" & "Foo1"

   ds = new DataSet();
   adapter.TableMappings.Add("Foo", "Green");
   adapter.TableMappings.Add("Foo1", "Red");
   adapter.Fill(ds, "Foo");
   Print(ds); // will have "Green" & "Red"

   ds = new DataSet();
   adapter.TableMappings.Clear();
   adapter.TableMappings.Add("Foo", "Bar");
   adapter.TableMappings.Add("Foo1", "Bar");
   adapter.Fill(ds, "Foo");
   Print(ds); // will have "Bar" with 2 columns

   ds = new DataSet();
   adapter.TableMappings.Clear();
   adapter.Fill(ds.Tables.Add("Apple"));
   Print(ds);  // will have "Apple", using only first result
  }
  catch(Exception e) { Console.WriteLine(e); }
 }
 
 public static void Print(DataSet ds) {
  Console.WriteLine("--");
  foreach(DataTable dt in ds.Tables) {
   Console.WriteLine(dt.TableName);
   foreach(DataColumn dc in dt.Columns) {
    Console.WriteLine("\t{0}", dc.ColumnName);
   }
  }
 }
}

 

/* output is

--
Table
        a
Table1
        b
--
Foo
        a
Foo1
        b
--
Green
        a
Red
        b
--
Bar
        a
        b
--
Apple
        a

*/