使用一个OracleCommand填充多个DataTable

Question

我在SOF上找到了一些有关如何运行多个Oracle查询（BEGIN END块，匿名存储过程）的问题/答案。我想做的事情基本相同，但我希望这些查询能够“一次性”填充多个数据表：

因此，与我们通常的一个查询对应一个数据表不同，像下面这样：（这是“伪代码”，不是可用的示例！）

Odp.Fill(SomeQuery, SomeDataTable, SomeParameters);

我想做一些类似于这样的事情

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

- Laoujin

---

你为什么想要使用单个调用而不是多个？是因为易于使用，期望获得显著的性能增益，原子执行，事务处理，错误处理等吗？ - Codo

“期望获得（显著的）性能提升”实现这个新的“BulkFill”方法也是一个很好的点来添加（自定义？即New Query(sql, dt, customErrMsg)）错误处理，减少DAL代码量，提高填充方法的可读性... - Laoujin

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

希望这可以帮助您。- Harvey Sather

- Harv

---

太好了。这正是我需要解决问题的方法。 - Holt

请注意，数据集“ds”中的表顺序与添加到“OracleCommand”的参数顺序相匹配。这可能与存储过程中定义的形式参数顺序不同。 - pius