.net – 使用1个OracleCommand填充多个DataTable

概述我在SOF上发现了一些关于如何针对Oracle运行多个查询的问题/答案(BEGIN END块,匿名存储过程).我想做的几乎相同,但我希望这些查询能够“一次性”填充多个DataTable： 所以不是我们平常的：每个DataTable一个查询就像 (这是’伪代码’,不是一个有效的例子！) Odp.Fill(SomeQuery, SomeDataTable, SomeParameters); 我想做点什 我在SOF上发现了一些关于如何针对Oracle运行多个查询的问题/答案(BEGIN END块,匿名存储过程).我想做的几乎相同,但我希望这些查询能够“一次性”填充多个Datatable：

所以不是我们平常的：每个Datatable一个查询就像
(这是’伪代码’,不是一个有效的例子！)

Odp.Fill(Somequery,SomeDatatable,SomeParameters);

我想做点什么

Odp.Fill(   new query(Somequery,SomeParameters),new query(Somequery2,SomeDatatable2,...)

解决方法 这只是您可以在一个查询中获取多个表的众多方法之一.

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 ASPROCEDURE 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",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;        }    }}

希望这可以帮助

哈维萨瑟

总结

以上是内存溢出为你收集整理的.net – 使用1个OracleCommand填充多个DataTable全部内容，希望文章能够帮你解决.net – 使用1个OracleCommand填充多个DataTable所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。