Filling multiple DataTables with 1 OracleCommand

Question

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.Fill(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),
   ...)

Answer 1

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