ODP.NET

I've been banging my head against the wall all day on this, searching high and low, and trying all kinds of different things. I need to store the results of a StoredProcedure's query in a DataGridView (WinForms, C#).

I can retrieve data, just not put it in the DGV. Here's my code:

using System;
using System.Data;
using System.Windows.Forms;
using Oracle.DataAccess.Client; // C# ODP.NET Oracle managed provider

// This code based on tutorial at: http://www.oracle.com/technetwork/articles/dotnet/vs2010-oracle-dev-410461.html
namespace OracleDataProvider
{
public partial class Form1 : Form
{
//string oradb = "Data Source=DEVL.world;User Id=CSHANNON;Password=password123;";
// Or, could bypass the TNSNames file this way: (This works, the above didn't)
string oradb = "Data Source=(DESCRIPTION="
+ "(ADDRESS=(PROTOCOL=TCP)(HOST=135.63.14.232)(PORT=1521))"
+ "(CONNECT_DATA=(SERVICE_NAME=DEVL.world)));"
+ "User Id=cshannon;Password=password123;";

public Form1()
{
InitializeComponent();
}

// Will need to allow Inserting a record as well as Updating it (also to the dotConnect
// version of these twin apps)
private void button1_Click(object sender, EventArgs e)
{
try
{
int iFromYear = dateTimePickerFrom.Value.Year;
int iFromMonth = dateTimePickerFrom.Value.Month;
int iFromDay = dateTimePickerFrom.Value.Day;
int iToYear = dateTimePickerTo.Value.Year;
int iToMonth = dateTimePickerTo.Value.Month;
int iToDay = dateTimePickerTo.Value.Day;

string sql = "REPORT.CONN_THRU_DOTNET";
OracleConnection conn = new OracleConnection(oradb);
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;

// from http://docs.oracle.com/cd/E14072_01/appdev.112/e10767/building_odp.htm#CEGHAFDH
OracleDataAdapter da;
OracleCommandBuilder cb;
DataSet ds;

// http://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleParameterCollectionClass.htm)
OracleParameter[] prm = new OracleParameter[4];
prm[0] = cmd.Parameters.Add("paramFromDate", OracleDbType.Date,
new DateTime(iFromYear, iFromMonth, iFromDay), ParameterDirection.Input);
prm[1] = cmd.Parameters.Add("paramToDate", OracleDbType.Date,
new DateTime(iToYear, iToMonth, iToDay), ParameterDirection.Input);
prm[2] = cmd.Parameters.Add("paramCategoryID", OracleDbType.Int32,
114, ParameterDirection.Input);
prm[3] = cmd.Parameters.Add("outRefPrm", OracleDbType.RefCursor,
DBNull.Value, ParameterDirection.InputOutput); //<-- or .Output?

conn.Open();

// also from http://docs.oracle.com/cd/E14072_01/appdev.112/e10767/building_odp.htm#CEGHAFDH
da = new OracleDataAdapter(cmd);
cb = new OracleCommandBuilder(da);
ds = new DataSet();
da.Fill(ds);
//dataGridView1.DataSource = ds.
//dataGridView1.DataSource = da.Fill(ds);

//dataGridView1.DataSource = cmd.ExecuteReader();
//dataGridView1.DataBindings.Add(da);

OracleDataReader dr = cmd.ExecuteReader();
try
{
dataGridView1.DataSource = dr;
//dataGridView1.DataMember = dr.GetValues(

// Need to put what’s read into the DataGridView; the loop below works, but is a pain to sit through, so I commented it out
while (dr.Read())
{
;// MessageBox.Show(dr.GetString(0));
}
}
finally
{
cmd.Parameters.Clear();

prm[0].Dispose();
prm[1].Dispose();
prm[2].Dispose();
prm[3].Dispose();

conn.Dispose();
cmd.Dispose();
dr.Dispose();
}
}
catch (Exception ex) // catches any error
{
MessageBox.Show(ex.Message.ToString());
}
}
}
}