7 Replies Latest reply: Jan 14, 2014 2:03 AM by IgorKhvan RSS

Tutorial on how to bind a DataGridView to the resultset/cursor of an SP?

923964 Newbie
Currently Being Moderated
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());
}
}
}
}
  • 1. Re: Tutorial on how to bind a DataGridView to the resultset/cursor of an SP?
    gdarling - oracle Expert
    Currently Being Moderated
    Here's a DataGrid RefCursor aspx example I had laying around, maybe it will show you what you need?

    Hope it helps,
    Greg
    <%@ Page CompilerOptions='/R:"d:app\gdarling\product\11.2.0\dbhome_1\odp.net\BIN\2.x\Oracle.DataAccess.dll"' Language="c#" %>
    <%@ import Namespace="System.Data" %>
    <%@ import Namespace="Oracle.DataAccess.Client" %>
    <%@ import Namespace="Oracle.DataAccess.Types" %>
    <%@ import Namespace="System.Web.UI" %>
    <script runat="server">
    /*
    CREATE OR REPLACE PACKAGE tp2 AS
    TYPE empcur IS REF CURSOR;
      Procedure GetEmpRecordsAsProc(ecur out empcur,deptnum in number);
    END tp2;
    /
    
    CREATE OR REPLACE PACKAGE BODY tp2 AS
    Procedure GetEmpRecordsAsProc( ecur out empcur, deptnum in number) is
        BEGIN
        OPEN ecur FOR
            SELECT * FROM emp where deptno=deptnum;
       end getemprecordsasproc;
    end tp2;
    /
    */
    
        private string constr = "user id=scott;password=tiger;data source=orcl";
         protected void Page_Load(Object sender, EventArgs e) {
            using(OracleConnection con = new OracleConnection(constr))
            {
                using (OracleCommand cmd = new OracleCommand("tp2.GetEmpRecordsAsProc", con))
                {
                    OracleParameter oparam = cmd.Parameters.Add("refcursor", OracleDbType.RefCursor);
                    oparam.Direction = ParameterDirection.Output;
                    OracleParameter oparam2 = cmd.Parameters.Add("deptno", OracleDbType.Int32);
                    oparam2.Direction = ParameterDirection.Input;
                    oparam2.Value = 10;
                    cmd.CommandType = CommandType.StoredProcedure;
                    using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))
                    {
                        DataSet myset = new DataSet("empcur");
                        adapter.Fill(myset);
                        MyDataGrid.DataSource = myset;
                        MyDataGrid.DataBind();
                    }
                    oparam.Dispose();
                    oparam2.Dispose();
                }
             }
         }
    
    </script>
    <html>
    <head>
    </head>
    <body>
        <form runat="server">
                <asp:DataGrid id="MyDataGrid" runat="server"></asp:DataGrid>
        </form>
    </body>
    </html>
    Edited by: gdarling on Mar 16, 2012 8:35 AM

    I initially had the refcur param as IN OUT (which does work), but i'm not sure why i had it that way. It really should just be OUT in this case, and I've edited the post.
  • 2. Re: Tutorial on how to bind a DataGridView to the resultset/cursor of an SP?
    923964 Newbie
    Currently Being Moderated
    I am not seeing how the DataGridView knows what data to which to bind.

    The "empcur" string passed to the Dataset constructor seems pulled out of a hat - simply any old string is used, apparently. So when DataBind() is called, to what is it actually binding?

    Edited by: Clay Shannon on Mar 19, 2012 11:36 AM
  • 3. Re: Tutorial on how to bind a DataGridView to the resultset/cursor of an SP?
    923964 Newbie
    Currently Being Moderated
    And, DataGridView does not have a "DataBind" method.

    Edited by: Clay Shannon on Mar 19, 2012 1:45 PM
  • 4. Re: Tutorial on how to bind a DataGridView to the resultset/cursor of an SP?
    gdarling - oracle Expert
    Currently Being Moderated
    "empcur" was indeed pulled out of a hat. You can also leave that string empty. It just populates ds.DataSetName.

    Is your question about what happens when you bind a dataset to a gridview generically? Or how the dataset got populated in the first place?

    If you're asking about how the dataset got populated, that's an Oracle question and the ODP.NET documentation may help if you're not familiar with working with Ref Cursors:
    http://docs.oracle.com/cd/E11882_01/win.112/e23174/featRefCursor.htm

    If your question is how .NET works in general with respect to DataSets and DataGridViews, you may get better answers in a Microsoft forum as that's not related to Oracle.

    Greg
  • 5. Re: Tutorial on how to bind a DataGridView to the resultset/cursor of an SP?
    gdarling - oracle Expert
    Currently Being Moderated
    Here's a Windows Form example, using the same stored proc. The app is nothing but a DataGridView and a button, with this code inside the button_click.

    Hope it helps,
    Greg

    string constr = "data source=orcl;user id=scott;password=tiger";
                using (OracleConnection con = new OracleConnection(constr))
                {
                    con.Open();
                    using (OracleCommand cmd = new OracleCommand("tp2.GetEmpRecordsAsProc", con))
                    {
                        OracleParameter oparam = cmd.Parameters.Add("refcursor", OracleDbType.RefCursor);
                        oparam.Direction = ParameterDirection.Output;
                        OracleParameter oparam2 = cmd.Parameters.Add("deptno", OracleDbType.Int32);
                        oparam2.Direction = ParameterDirection.Input;
                        oparam2.Value = 10;
                        cmd.CommandType = CommandType.StoredProcedure;
                        using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))
                        {
                            DataSet myset = new DataSet("empcur");
                            adapter.Fill(myset);
                            dataGridView1.DataSource = myset.Tables[0];
                        }
    
                    }
                }
  • 6. Re: Tutorial on how to bind a DataGridView to the resultset/cursor of an SP?
    71bc5b5a-9f95-4b5e-a2f4-a7c9b4966b93 Newbie
    Currently Being Moderated

    Did anyone get it to work yet? I am also designing the same and can't get the data to show in the DataGridView. I am using VS 2012 and the headers show up but not the data.

     

    I am suspecting it may be due to the date column not showing up???  not sure.

  • 7. Re: Tutorial on how to bind a DataGridView to the resultset/cursor of an SP?
    IgorKhvan Newbie
    Currently Being Moderated

    Hi everyone!

     

    Try this code

    Separate module:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using Oracle.DataAccess.Client;
    using Oracle.DataAccess.Types;
    namespace OracleTools
    {
        class OracleConnect
        {
            private OracleConnection conn;
            private DataTable data;
            private OracleDataAdapter da;
            private OracleCommandBuilder cb;
            public void Dispose()
            {
                conn.Dispose();
            }
            public void ExecuteInsertQuery(string query)
            {
                conn.Open();
                //MySqlCommand cmd = new MySqlCommand(query, conn);
                //cmd.ExecuteNonQuery();
            }
            String connStr = String.Empty;
            
            public OracleConnection Connect(String dataSource, String user, String password)
            {
                connStr = String.Format("Data Source={0};User ID={1};Password={2};",
                    dataSource, user, password);
                conn = new OracleConnection(connStr);
                conn.Open();
                return conn;
            }
            public DataTable ExecuteQuery(String query)
            {
                data = new DataTable();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandText = query;
                cmd.CommandType = CommandType.Text;
                da = new OracleDataAdapter(cmd);
                //da.SelectCommand.CommandTimeout = 28800;
                cb = new OracleCommandBuilder(da);
                if (data != null) da.Fill(data);
                return data;
            }
        }
    }

     

    Main program:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using Oracle.DataAccess.Client;
    using Oracle.DataAccess.Types;
    using OracleTools;
    namespace PMS
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            private void button1_Click(object sender, EventArgs e)
            {
                OracleConnect oraConn = new OracleConnect();
                oraConn.Connect("orcl", "postmn", "Password1");
                dataGridView1.DataSource = oraConn.ExecuteQuery("select * from sys_user");
                oraConn.Dispose();
            }
        }
    }

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points