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
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">
<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
Gabe,
Thanks for the code and links. I'll try that tomorrow and let you know if it worked for me.
Thanks! Theresa