how do I populate grid in updatepanel from refcursor

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

Answers


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">

<html xmlns="http://www.w3.org/1999/xhtml">
<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


See also: Binding Gridview to an Oracle Sys-RefCursor


Need Your Help

ORM and .NET Code Protectors

c# .net orm obfuscation source-code-protection

We are about to use Code Protectors (Obsfucation as well as Native Compilation), I assume ORMs will be dependent little bit on Reflection and I am worried will Obsfucation and Native Compilation

Can I use InnoDB and MyISAM tables in ONE database?

mysql database-design innodb myisam

Obviously both have their benefits. MyISAM is fast but may get currupted easily, InnoDB is slow but is more stable thanks to transactions and foreign keys. So it could be good to mix both engines i...