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

存儲過程---------oledb返回多個結果集------------創建包createorreplacepackagepkg_tb_testistypetype_cursorisrefcursor;procedureSP_TB_TEST_SELECT(my_cursorouttype_cursor);endpkg_tb_test;--創建包體cre...

weixin_30418341

85人浏览 · 2010-08-12 16:43:00

weixin_30418341 · 2010-08-12 16:43:00 发布

存儲過程

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

< connectionStrings >
< add name ="CN" connectionString ="Provider=msdaora;Data Source=MDSTEST;User Id=MGSSFCS;password=MDS" />
</ connectionStrings >

转载于:https://www.cnblogs.com/xiachufeng/archive/2010/08/12/1798256.html

AI编程社区

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

Oracle 游标详解（cursor）

文章目录1 概述1.1 思维导图2 语法2.1 基本写法（4步）2.2 游标4大属性3 分类3.1 静态游标3.1.1 隐式游标 dml3.1.2 显式游标 cursor3.2 动态游标3.2.1 自定义类型 ref cursor3.2.2 系统类型 sys_refcursor4 扩展4.1 三种游标循环效率对比4.2 实例：实际开发中，游标遍历数据1 概述1. 游标是什么？用来存储多条查询数据的