Best practice for multiple result sets?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for universal data access
Post Reply
tag2453
Posts: 3
Joined: Fri 16 Feb 2007 19:26

Best practice for multiple result sets?

Post by tag2453 » Mon 19 Feb 2007 20:07

We need to support both SQL Server and Oracle with our application.
In SQL Server we have stored procedures that return multiple resultsets used to return 2 datatables such as order information and the order line items records.

In Oracle we have a package with 2 REF Cursors to return the same 2 resultsets. Schemas and data are the same in both system.

Can you provide some example code or information on how to best code this scenario using unidirect to retrieve the data from either database without having to change any of the application code?

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Tue 20 Feb 2007 06:46

Please provide us with your databases' objects definitions.

tag2453
Posts: 3
Joined: Fri 16 Feb 2007 19:26

Post by tag2453 » Tue 20 Feb 2007 22:42

Okay here's a rough example...

CREATE TABLE [Store_Order](
[orderID] [int] IDENTITY(1,1) NOT NULL,
[orderNumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[orderDate] [smalldatetime] NOT NULL CONSTRAINT [DF_CMRC_Orders_OrderDate_1] DEFAULT (getdate()),
[orderStatusID] [int] NOT NULL CONSTRAINT [DF_CMRC_Orders_OrderStatusID] DEFAULT ((0)),
[userName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[email] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[firstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[shippingMethod] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[subTotalAmount] [money] NOT NULL CONSTRAINT [DF_CMRC_Orders_OrderSubTotal] DEFAULT ((0)),
[shippingAmount] [money] NOT NULL CONSTRAINT [DF_CMRC_Orders_Shipping_1] DEFAULT ((0)),
[handlingAmount] [money] NOT NULL CONSTRAINT [DF_CMRC_Orders_HandlingAmount] DEFAULT ((0)),
[taxAmount] [money] NOT NULL CONSTRAINT [DF_CMRC_Orders_Tax_1] DEFAULT ((0)),
[taxRate] [numeric](18, 0) NOT NULL CONSTRAINT [DF_CMRC_Orders_taxRate] DEFAULT ((0)),
[couponCodes] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[discountAmount] [money] NOT NULL CONSTRAINT [DF_CMRC_Orders_discountAmount] DEFAULT ((0)),
[shipToAddress] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[billToAddress] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Store_Order] PRIMARY KEY CLUSTERED
(
[orderID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [Store_OrderItem](
[orderItemID] [int] IDENTITY(1,1) NOT NULL,
[orderID] [int] NOT NULL,
[productID] [int] NOT NULL,
[sku] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[productName] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[quantity] [int] NOT NULL,
[originalPrice] [money] NOT NULL,
[pricePaid] [money] NOT NULL,
[isShipped] [bit] NOT NULL CONSTRAINT [DF_CSK_Store_OrderItem_isShipped] DEFAULT ((0)),
[shipDate] [datetime] NULL,
CONSTRAINT [PK_CMRC_OrderItems] PRIMARY KEY CLUSTERED
(
[orderItemID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [Store_OrderItem] WITH CHECK ADD CONSTRAINT [FK_Store_OrderItem_Store_Order] FOREIGN KEY([orderID])
REFERENCES [Store_Order] ([orderID])


Create Procedure GetOrderInfo(@OrderID int)
as
Select * from Store_Order where orderID = @OrderID
Select * from Store_OrderItem where orderID = @OrderID



Oracle:

CREATE OR REPLACE PROCEDURE GETORDERINFO(
OrderID IN Number,
RCORDER IN OUT sys_RefCursor,
RCDETAILS IN OUT sys_RefCursor)
AS

BEGIN

Open RCORDER for
Select * from SA.CSK_Store_Order where CSK_Store_Order.orderID = OrderID;

Open RCDetails for
Select * from SA.CSK_Store_OrderItem where CSK_Store_OrderItem.orderID = OrderID;



END GETORDERINFO;

Now I need to know how to write one function in my data access layer that retruns a dataset with these 2 tables from either database depending on which db I specify to use in config file.

Thanks...

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Wed 21 Feb 2007 10:42

Take a look at the following code snippet:

Code: Select all

  DataSet RetrieveResults(UniConnection uniConnection, string procedureName, int id) {

      if (uniConnection.State != ConnectionState.Open)
        uniConnection.Open();
      UniCommand uniCommand = uniConnection.CreateCommand();
      uniCommand.CommandType = CommandType.StoredProcedure;
      uniCommand.CommandText = procedureName;
      UniCommandBuilder.DeriveParameters(uniCommand);
      uniCommand.Parameters["OrderID"].Value = id;
      UniDataReader reader = uniCommand.ExecuteReader();
      DataSet results = new DataSet();
      DataTable table1 = new DataTable("Result1");
      table1.Load(reader); // reader.NextResult is called implicitly
      results.Tables.Add(table1);
      DataTable table2 = new DataTable("Result2");
      table2.Load(reader);
      results.Tables.Add(table2);
      return results;
    }

    private void TestButton_Click(object sender, EventArgs e) {

      using (UniConnection connection = new UniConnection(OracleConnStr)) {

        dataGrid1.DataSource = RetrieveResults(connection, "scott..GetOrderInfo", 34);
        connection.ConnectionString = SqlConnStr;
        dataGrid2.DataSource = RetrieveResults(connection, "GetOrderInfo", 20);
      }
    }
The connection strings are:

Code: Select all

    const string OracleConnStr = "Provider=Oracle;data source=ora; user id=scott; pwd=tiger";
    const string SqlConnStr = "Provider=Sql Server;data source=server; user id=sa; database=test";

tag2453
Posts: 3
Joined: Fri 16 Feb 2007 19:26

Post by tag2453 » Wed 21 Feb 2007 18:41

That looks great and exactly what I was hoping for...

So if I understand this correctly then the DeriveParameters method is automatically figuring out that when using Oracle, the 2 output parameters are ref cursors, and automatically creating datareaders from them so NextResult can be used? Can you explain this a little further...

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Fri 23 Feb 2007 07:49

DeriveParameters method populates the specified uniCommand's Parameters collection with parameter information for the stored procedure specified in the CommandText. Then datareader is created using ExecuteReader method of the uniCommand object. NextResult method is called by firing DataTable.Load(IDataReader) method.

rcmetta
Posts: 1
Joined: Tue 31 Mar 2009 11:53

Post by rcmetta » Tue 31 Mar 2009 12:12

Hi,

I need to return multiple result sets from an oracle stored procedure. the number of result sets is dynamic. I sent a list of customer names to the procedure and for each customer, I send a list of fields to be retrieved. the procedure should execute one query for each customer and return the data. my front end is in VB6. Can you please let me know what is the process for this?

rcm

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 01 Apr 2009 13:06

1. The next result set can be accessed with the help of the NextResult() method of the UniDataReader object.
2. In Oracle the amount of the returned result sets, received like this, equals to the amount of the ref cursor parameters that are declared only once when creating the procedure. Therefore you can not change the amount of the result sets this way.
3. Take a look at the following implementations of your task:
a) stored procedure receives the only customer name and returns the fixed amount of the result sets for it. If you want to obtain the whole set of necessary result sets, call this procedure in a loop changing its parameters.
b) return the only result set for the whole list of customers and work with it using the Read() method of the UniDataReader object.

BenW
Posts: 9
Joined: Thu 14 May 2009 04:35

dbxpress best practice for multiple result sets

Post by BenW » Thu 14 May 2009 05:49

Hi I have a MSSQL stored proc that returns multiple result sets. What is the best practice using dbexpsda40.dll?

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Thu 14 May 2009 08:49

dbExpress has some bugs in support of multiple result sets. So it is recommended to create a separate stored procedure for each result set.

But you can try the following code:

Code: Select all

  SQLStoredProc1.Open;
  while not SQLStoredProc1.Eof do begin
    a := SQLStoredProc1.Fields[0].Value;
    SQLStoredProc1.Next;
  end;

  SQLStoredProc1.NextRecordSet;
  while not SQLStoredProc1.Eof do begin
    a := SQLStoredProc1.Fields[0].Value;
    SQLStoredProc1.Next;
  end;
  SQLStoredProc1.Close;

BenW
Posts: 9
Joined: Thu 14 May 2009 04:35

Post by BenW » Thu 14 May 2009 22:22

Thanks for the prompt reply. Do you have any idea which version of dbExpress will support multiple result sets?

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Fri 15 May 2009 08:01

This code works in Delphi 6 and Delphi 7.
But new versions of Dephi - 2007 and 2009 - have a bug with the NextRecordSet method. Please contact CodeGear support to fix this bug.

Post Reply