.net - 使用 1 个 OracleCommand 填充多个数据表

Question

我在 SOF 上找到了一些关于如何针对 Oracle 运行多个查询(BEGIN END block 、匿名存储过程)的问题/答案。我想做几乎相同的事情，但我希望这些查询“一次性”填充多个数据表:

因此，与我们通常的做法不同:每个数据表一个查询，例如(这是“伪代码”，不是一个有效的示例!)

Odp.Fill(SomeQuery, SomeDataTable, SomeParameters);

我想做一些类似的事情

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

Answer 1

这只是您可以在一个查询中获取多个表的多种方法之一。

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# 代码

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

希望这有帮助

哈维·萨瑟