Introduction
In my earlier article
Multiple Result Sets in ADO.net using SqlClient ,we have seen how to retrieve multiple results using Sqlclient against SQL Server. This was a fairly simple and straight forward. We can achieve the same against Oracle database with a slight difference. We need to iron out couple of things before we get to nuts and bolts of it.
- We need System.Data.OracleClient.Dll assembly that is very similar to System.Data.SqlClient.Dll,.NET Managed Provider forOracle is not a part of .NET Version 1.0, a separate download click here to download . For .NET version 1.1, it’s going to be part of the bundle. If you are new to .Net framework provider for Oracle read my articles on code101 Boost performance with .net data provider for oracle
- We need a stored procedure to return multiple results set using REF CURSOR. For novice, A ref cursor is a PL/SQL data type that you can use in a query to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly typed ref cursor.
Lets get feet dirty
Create a oracle package, I assume you know what a package by the way it has two parts Specification and body
Package Specification
CREATE OR REPLACE PACKAGE PKG_MUltiResultset as
TYPE MyRefCur is REF CURSOR;
procedure GetReadOnlyData(EmpCur OUT MyRefCur,
DeptCur OUT MyRefCur,
SalCur OUT MyRefCur);
END;
Package body
CREATE OR REPLACE PACKAGE BODY PKG_MUltiResultset as
PROCEDURE GetReadOnlyData(EmpCur OUT MyRefCur,
DeptCur OUT MyRefCur,
SalCur OUT MyRefCur)
IS
BEGIN
open EmpCur for select * from emp;
open DeptCur for select * from dept;
open SalCur for select * from salgrade;
END;
END;
Lets us get to the client part to access this stored procedure. We will establish connection to Oracle database retrieve data from three tables in one shot and fill up the dataset and bind the data to DataGrids.I am using Dataset you can try it with OracleDataReader as well.
NOTE: The Prefix is always Oracle for all the classes in Syatem.Data.OracleClient name space. Reference name spaces
using System.Data.OracleClient;
using System.Data;
Private void GetData_Click(object sender, System.EventArgs e)
{
try
{
//Connect to Database
OracleConnection oCon=new OracleConnection
("Data source=Firstdb;user id=scott;password=tiger");
oCon.Open();
//Command Object
OracleCommand oCmd =new OracleCommand
("PKG_MUltiResultset.GetReadOnlyData",oCon);
//Stored Procedure
oCmd.CommandType=CommandType.StoredProcedure;
//Create Parameter Object
oCmd.Parameters.Add(new OracleParameter
("empcur",OracleType.Cursor)).Direction=ParameterDirection.Output;
oCmd.Parameters.Add(new OracleParameter
("DeptCur",OracleType.Cursor)).Direction=ParameterDirection.Output;
oCmd.Parameters.Add(new OracleParameter
("SalCur",OracleType.Cursor)).Direction=ParameterDirection.Output;
//Instatiate Dataset
DataSet Ds=new DataSet();
//Instatiate Data Adopter
OracleDataAdapter oAdp=new OracleDataAdapter(oCmd);
//Fill Data Set
oAdp.Fill(Ds);
//Bind Data to Grids
dataGrid1.SetDataBinding(Ds,"Table");
dataGrid2.SetDataBinding(Ds,"Table1");
dataGrid3.SetDataBinding(Ds,"Table2");
}
catch (Exception x)
{
MessageBox.Show(x.Message);
}
}
分享到:
相关推荐
Ways to use and extend the Provider Model for accessing data stores, processes, and more * What freeware tools you need in Scott Hanselman's ASP.NET Ultimate Developer Tools appendix
Retrieve Form for Data Capture (RFD)
An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information 数据库服务器是解决信息管理问题的核心组件。大体上说,数据库服务器 的...
best practices for using MySQL 8, and NoSQL APIs provided by MySQL 8, and will also have a use case on using MySQL 8 for managing Big Data. By the end of this book, you will learn how to efficiently ...
Nhibernate 2 – Rapidly Retrieve Data From Your Database Into .NET Objects
retrieve_data
使用PL/SQL登录ORACLE数据 报错,error while trying to retrieve text for error ORA-01804,的解决方法,操作步骤,在windows server 2008R2 64位,oracle 12C,PLSQL Developer 12 64位,以上使用环境正常,
- **Using Functions**: Invoking functions to perform calculations or retrieve data. - **Working with Packages**: Utilizing packages to organize and access related functionality. - **Debugging ...
Fully updated for Xcode 4.2, Pro Core Data for iOS explains how to use the Core Data framework for iOS SDK 5 using Xcode 4.2. The book explains both how and why to use Core Data, from simple to ...
Oracle data provider -Data type mapping from RAW(16) to ftGuid is added -Now result parameter detection in UniConnection.ExecSQL is similar to the one in ODAC -Bug with connection establishing if the ...
retrieve_sqlite_data
Finally, the book includes a quick primer on accessing and processing data from databases, conduct data exploration, and store and retrieve data quickly using databases. The book will include ...
Welcome to .NET and Visual Basic .NET.As you already know, .NET is a name for a new strategy: a blueprint for building applications for the next decade. It’s actually even more than that. It’s ...
在讨论Oracle的必要性时,通常会涉及到数据库的核心操作,即CRUD,这代表创建(Create)、读取(Retrieve)、更新(Update)和删除(Delete)数据。数据的持久化存储是数据库的基本职能,但在没有数据库管理系统的情况下,对...
We propose a novel Knowledge-driven Encode, Retrieve, Paraphrase (KERP) approach which reconciles traditional knowledge- and retrieval-based methods with modern learning-based methods for accurate ...
Filled with detailed examples and expert strategies from an Oracle ACE, Oracle Database 12c PL/SQL Programming explains how to retrieve and process data, write PL/SQL statements, execute effective ...
### 如何在ASP.NET开发平台中使用DataWindow.NET开发WebForm网站系统 #### 知识点一:ASP.NET与DataWindow.NET的关系 - **ASP.NET**:是微软提供的用于构建Web应用程序的一个框架,它允许开发者利用.NET Framework...
Retrieve data from multiple tables via joins, subqueries, views, and set logic Create, modify, and execute stored procedures Install Microsoft SQL Server, MySQL, or Oracle Table of Contents Chapter 1 ...