Best practice for multiple result sets?
Best practice for multiple result sets?
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?
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?
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...
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...
Take a look at the following code snippet:
The connection strings are:
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);
}
}
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";
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...
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...
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.
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
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
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.
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.
dbxpress best practice for multiple result sets
Hi I have a MSSQL stored proc that returns multiple result sets. What is the best practice using dbexpsda40.dll?
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:
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;