ASP .Net + Oracle Example for Beginner: Get the resultset using SELECT statement (and not stored procedure)

By | May 1, 2015 | 0 Comment
Category: ASP .Net Tags: ,

When I started working on a .Net application which uses Oracle database, I had a very simple requirement. I need to execute SELECT statement, get the resultset into the .Net and show the data in a grid view. Having a SQL Server background, I thought a quick search in google would provide me an example where I can imitate the logic and jump start the application development. When I searched online, all the examples required me to create a stored procedure and get the resultset. Having a read only access on the database and not wanting to add a stored procedure in the database, I had to pull my hair for couple of days to find what I see as a very simple example. Here is a pretty simple example which requires Oracle managed drivers to be installed on machine or a solution using Nuget package for Oracle.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Oracle.ManagedDataAccess.Types;

namespace OracleConnectivity
public partial class _Default : System.Web.UI.Page
protected void Page_Load(object sender, EventArgs e)
OracleCommand cmd = new OracleCommand();
DataSet ds = null;
cmd.Connection = con;
cmd.CommandText = @”
OPEN :cur1 FOR SELECT ‘SUCCESS’ AS Status, ‘its good’ as Message

OPEN :cur2 FOR SELECT * from all_tables where rownum < 10; END;"; cmd.CommandType = System.Data.CommandType.Text; OracleParameter param1 = new OracleParameter("cur1", OracleDbType.RefCursor, ParameterDirection.Output); cmd.Parameters.Add(param1); OracleParameter param2 = new OracleParameter("cur2", OracleDbType.RefCursor, ParameterDirection.Output); cmd.Parameters.Add(param2); using (OracleDataAdapter da = new OracleDataAdapter(cmd)) { ds = new DataSet(); da.Fill(ds); } cmd.Dispose(); con.Close(); } gvDetails.DataSource = ds.Tables[0]; gvDetails.DataBind(); gvDetails0.DataSource = ds.Tables[1]; gvDetails0.DataBind(); } } } [/code]


sql statement contains two output cursors “cur1” and “cur2” which are defined as two oracle output parameters. Tables[0] in dataset refers to the output from “cur1” and Tables[1] in dataset refers to the output from “cur2” cursor. I am assigning these table data to the two separate gridviews. Both the grid views has “AutoGenerateColumns” = True.

About Vishal Monpara

Vishal Monpara is a full stack Solution Developer/Architect with 13 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1's and 0's from geographically dispersed hard disks to geographically dispersed user leveraging geographically dispersed team members.

Leave a Reply

Your email address will not be published. Required fields are marked *