oledb返回連接多個結果集（oracle 遊標）

存儲過程

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

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();
        }

    }
    public List<tb2> GetAll()
    {
        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);
            }
            return listTb;
        }
        catch (Exception)
        {
           throw;
        }

        finally{
        read.Close();
        conn.Close();
        }
    }
}

Model層代碼
using System;
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; }
        }
    }
}

夏楚枫

公告