Filling multiple DataTables with 1 OracleCommand

I found some questions/answers on SOF on how to run multiple queries against Oracle (BEGIN END block, anonymous stored procedure). I want to do pretty much the same but I want those queries to fill multiple DataTables in 'one go':

So instead of our usual: One query per DataTable like (This is 'pseudo code', not a working example!)

Odp.Fi开发者_JAVA百科ll(SomeQuery, SomeDataTable, SomeParameters);

I'd like to do something along the lines of

Odp.Fill(
   new Query(SomeQuery, SomeDataTable, SomeParameters),
   new Query(SomeQuery2, SomeDataTable2, SomeParameters),
   ...)

This is only one of many ways that you could get multiple tables in one query.

PL/SQL

CREATE OR REPLACE PACKAGE getBldgRoom AS

/******************************************************************************

   NAME:       getBldgRoom
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2011-5-27    has986       1. Created this package.

******************************************************************************/

PROCEDURE getBldgRoom(rcBuildingData OUT SYS_REFCURSOR, rcRoomData OUT SYS_REFCURSOR);


END getBldgRoom;

/

CREATE OR REPLACE PACKAGE BODY GETBLDGROOM AS
PROCEDURE getBldgRoom(rcBuildingData OUT SYS_REFCURSOR, rcRoomData OUT SYS_REFCURSOR) IS
  BEGIN
        OPEN rcBuildingData FOR
              select bldg_code, bldg_desc  from IH_CSI_OWNER.BUILDING;

        OPEN rcRoomData FOR
              select bldg_code, room_code, room_desc from IH_CSI_OWNER.ROOM;
  END getBldgRoom;

END GETBLDGROOM;

/

C# Code

using System;
using System.Data;
using Oracle.DataAccess.Client; //Needs Oracle Data Access Client (ODAC)

namespace ClassLibrary
{
    public class TwoTableDataSet
    {
        public DataSet getTwoTables()
        {
            OracleConnection conn = new OracleConnection();

            //Normally we get the connection string from the web.config file or the app.config file
            conn.ConnectionString = "Persist Security Info=False;User Id=*USER_NAME*;Password=*USER_PASSWORD*;Data Source=*DataBaseName*";
            DataSet ds = new DataSet();

            try
            {
                conn.Open();

                //------------------------------------------------------------------------------------------------------
                //Set up the select command
                OracleCommand cmd = new OracleCommand();
                cmd.BindByName = true; //If you do not bind by name, you must add parameters in the same order as they are listed in the procedure signature.
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;  //A procedure in an oracle package
                cmd.CommandText = "GETBLDGROOM.GetBldgRoom"; //The name of the procedure

                cmd.Parameters.Add("rcBuildingData", OracleDbType.RefCursor, ParameterDirection.Output);
                cmd.Parameters.Add("rcRoomData", OracleDbType.RefCursor, ParameterDirection.Output);

                OracleDataAdapter da = new OracleDataAdapter();
                da.SelectCommand = cmd;

                //------------------------------------------------------------------------------------------------------

                //get the data from the two tables in the procedure
                da.Fill(ds);
                //ds now contains ds.Tables[0] and ds.Tables[1]

                //Let's give them names
                ds.Tables[0].TableName = "BUILDINGS";
                ds.Tables[1].TableName = "ROOMS";

                //Let's add a relationship between the two tables
                DataColumn parentColumn = ds.Tables["BUILDINGS"].Columns["BLDG_CODE"];
                DataColumn childColumn = ds.Tables["ROOMS"].Columns["BLDG_CODE"];
                DataRelation dr = new System.Data.DataRelation( "BuildingsRooms", parentColumn, childColumn);
                ds.Relations.Add(dr);
            }
            catch (Exception ex)
            {
                //Add a breakpoint here to view the exception
                //Normally the exception would be written to a log file or EventLog in the case of a Web app
                //Alternatively, it could be sent to a WebService which logs errors and then it could work for both Web or Windows apps
                Exception lex = ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return ds;
        }
    }
}

Hope this helps

Harvey Sather