存儲過程
---------oledb返回多個結果集----------
--創建包
create or replace package pkg_tb_test is
type type_cursor is ref cursor;
procedure SP_TB_TEST_SELECT(my_cursor out type_cursor);
end pkg_tb_test;
--創建包體
create or replace package body pkg_tb_test is
procedure SP_TB_TEST_SELECT(my_cursor out type_cursor)
is
begin
OPEN my_cursor for select * from tb_test; --查詢tb_test表
end SP_TB_TEST_SELECT;
end pkg_tb_test;
Default5.aspx頁面
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default5.aspx.cs" Inherits="Default5" %>
<!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>未命名頁面</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="id" HeaderText="編號" />
<asp:BoundField DataField="name" HeaderText="名字" />
<asp:BoundField DataField="sex" HeaderText="性別" />
<asp:BoundField DataField="age" HeaderText="年齡" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
<head runat="server">
<title>未命名頁面</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="id" HeaderText="編號" />
<asp:BoundField DataField="name" HeaderText="名字" />
<asp:BoundField DataField="sex" HeaderText="性別" />
<asp:BoundField DataField="age" HeaderText="年齡" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
Default5.aspx.cs和Model後臺代碼
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Collections.Generic;
using Model;
public partial class Default5 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.GridView1.DataSource = GetAll();
this.GridView1.DataBind();
}
{
string queryString = "{call pkg_tb_test.SP_TB_TEST_SELECT('my_cursor')}"; //oledb調用存儲過程返回多個結果集
OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["CN"].ToString());//數據庫連接字符串
OleDbCommand cmd = new OleDbCommand(queryString, conn);
OleDbDataReader read = null;
List<tb2> listTb = new List<tb2>();
try
{
conn.Open();
read = cmd.ExecuteReader();
{
tb2 tb = new tb2();
tb.Id = Convert.ToInt32(read["id"]);
tb.Name = read["name"].ToString();
tb.Sex = read["sex"].ToString();
tb.Age = Convert.ToInt32(read["age"]);
tb.Address = read["address"].ToString();
tb.Tel = read["tel"].ToString();
tb.Email = read["email"].ToString();
}
catch (Exception)
{
throw;
}
finally{
read.Close();
conn.Close();
}
}
}
using System.Collections.Generic;
using System.Text; namespace Model
{
[Serializable]
public class tb3
{
{
get { return tb_from; }
set { tb_from = value; }
}
private string tb_to;
{
get { return tb_to; }
set { tb_to = value; }
}
private string tb_title;
{
get { return tb_title; }
set { tb_title = value; }
}
private string tb_body;
{
get { return tb_body; }
set { tb_body = value; }
}
}
}
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Collections.Generic;
using Model;
public partial class Default5 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.GridView1.DataSource = GetAll();
this.GridView1.DataBind();
}
}
{
string queryString = "{call pkg_tb_test.SP_TB_TEST_SELECT('my_cursor')}"; //oledb調用存儲過程返回多個結果集
OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["CN"].ToString());//數據庫連接字符串
OleDbCommand cmd = new OleDbCommand(queryString, conn);
OleDbDataReader read = null;
List<tb2> listTb = new List<tb2>();
try
{
conn.Open();
read = cmd.ExecuteReader();
while (read.Read())
{
tb2 tb = new tb2();
tb.Id = Convert.ToInt32(read["id"]);
tb.Name = read["name"].ToString();
tb.Sex = read["sex"].ToString();
tb.Age = Convert.ToInt32(read["age"]);
tb.Address = read["address"].ToString();
tb.Tel = read["tel"].ToString();
tb.Email = read["email"].ToString();
listTb.Add(tb);
}
}
catch (Exception)
{
throw;
}
finally{
read.Close();
conn.Close();
}
}
}
Model層代碼
using System.Collections.Generic;
using System.Text; namespace Model
{
[Serializable]
public class tb3
{
private string tb_from;
public string Tb_from
{
get { return tb_from; }
set { tb_from = value; }
}
private string tb_to;
public string Tb_to
{
get { return tb_to; }
set { tb_to = value; }
}
private string tb_title;
public string Tb_title
{
get { return tb_title; }
set { tb_title = value; }
}
private string tb_body;
public string Tb_body
{
get { return tb_body; }
set { tb_body = value; }
}
}
}
<connectionStrings>
<add name="CN" connectionString="Provider=msdaora;Data Source=MDSTEST;User Id=MGSSFCS;password=MDS"/>
</connectionStrings>
<add name="CN" connectionString="Provider=msdaora;Data Source=MDSTEST;User Id=MGSSFCS;password=MDS"/>
</connectionStrings>