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

By | May 1, 2015

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;
using (OracleConnection con = new OracleConnection("DATA SOURCE=ORACLE_CONNECTION;USER ID=USERNAME;PASSWORD=PASSWORD;PERSIST SECURITY INFO=True;"))
{
cmd.Connection = con;
cmd.CommandText = @"
BEGIN
OPEN :cur1 FOR SELECT 'SUCCESS' AS Status, 'its good' as Message
FROM DUAL;

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();
}
}
}

Explanation

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.

Vishal Monpara is a full stack Solution Developer/Architect with 12 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’s mind leveraging geographically dispersed team members.

Leave a Reply

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