tags:

views:

599

answers:

3

Hi,

I am using ASP.NET 2.0 and VS 2005. I need to populate a grid on an update panel from an Oracle refcursor after the user clicks a button. I have an example from another project, but it is pretty complicated. Is there an easy way to display the data in a grid in an updatepanel? We don't want the data to be displayed when the tab is first opened, just after the user clicks a button.

TIA, Theresa

A: 

Hi Theresa, I'm posting some code here because this stuff is hard to figure out and research. here's one simple way to do it using the Microsoft Oracle Data Provider (I prefer ODP.NET):

<%@ Page Language="C#" AutoEventWireup="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OracleClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;

<html xmlns="http://www.w3.org/1999/xhtml"&gt;
<head runat="server">
<title>GridView w/ Oracle Ref Cursor</title>
<style type="text/css">
    body {padding:25px;}
    .Button1 {margin:35px 0;}
</style>
<script runat="server" type="text/C#">
    protected void Page_Load(object sender, EventArgs e) {
    }

    protected void Button1_Click(object sender, EventArgs e) {
        var dataSet = new DataSet();
        // get connection string from web.config
        var connStr = ConfigurationManager.ConnectionStrings["ConnStr1"].ConnectionString;
        // create connection
        using (var conn = new OracleConnection(connStr)) {
            // create & define the parameter
            var refCursorParam = new OracleParameter();
            refCursorParam.ParameterName = "RET";
            refCursorParam.OracleType = OracleType.Cursor;
            refCursorParam.Direction = ParameterDirection.Output;
            // create & define the command
            var cmd = new OracleCommand();
            cmd.CommandText = "GET_ALL_EMPS";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(refCursorParam);
            cmd.Connection = conn;
            // use data adapter to fill dataset
            using (var adapter = new OracleDataAdapter(cmd))
                adapter.Fill(dataSet);
        }
        // set some gridview properties
        GridView1.AllowPaging = true;
        GridView1.PageSize = 5;

        // bind dataset to grid
        GridView1.DataSourceID = null;
        var dv = dataSet.Tables[0].AsDataView();
        // save dataview to session so gridview can be re-bound later
        Session["dataView"] = dv;
        GridView1.DataSource = dv;
        GridView1.DataBind();

        pCount.InnerText = "Total Row Count: " + dataSet.Tables[0].Rows.Count.ToString();

        // dispose of dataset
        dataSet.Dispose();
    }

    void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) {
        // get dataview from session
        var dv = (DataView)Session["dataView"];
        GridView1.DataSource = dv;
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataBind(); // re-bind data
    }
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<div>
    <asp:UpdatePanel id="updatepanel1" runat="server">
        <ContentTemplate>
            <asp:Button ID="Button1"
                runat="server"
                Text="Refresh GridView"
                OnClick="Button1_Click" />
            <p id="pCount" runat="server" />
            <asp:GridView ID="GridView1"
                          runat="server"
                          OnPageIndexChanging="GridView1_PageIndexChanging" />
        </ContentTemplate>
    </asp:UpdatePanel>
</div>
</form>
</body>
</html>

try this out and let me know if you have any questions. here's some good references:

-gabe

gabe
Thanks, Gabe! This code sent me in the right direction and I got it working.
Theresa
hey, that's great! i'm glad it helped.
gabe
A: 

Gabe,

Thanks for the code and links. I'll try that tomorrow and let you know if it worked for me.

Thanks! Theresa

Theresa
A: 

See also: Binding Gridview to an Oracle Sys-RefCursor

Brian Schmitt