ASP.NET使用Oralce過程/函數（一）

前期準備
create table mistest
(
id number not null,
name varchar2(200),
password varchar2(255),
constraint PK_mistest primary key(id)
);

create sequence SEQ_mistest_id
increment by 1
start with 1
maxvalue 999999;

create or replace trigger TR_mistest_insert
before insert on mistest
for each row
declare
m_seq number;
begin
select SEQ_mistest_id.nextval into m_seq from dual;
:new.id:=m_seq;
end;

有輸入，有返回結果集

CREATE OR REPLACE PACKAGE SELECT_SINGLE AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetById
(
    p_id IN NUMBER,
    cur_Single OUT T_CURSOR
);
END SELECT_SINGLE;

CREATE OR REPLACE PACKAGE BODY SELECT_SINGLE AS
PROCEDURE GetById
(
    p_id IN NUMBER,
    cur_Single OUT T_CURSOR
)
IS
BEGIN
    OPEN cur_Single FOR
    SELECT * FROM mistest
        WHERE id = p_id;

END GetById;
END SELECT_SINGLE;

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
namespace MisTest
{
    /**//// <summary>
    /// WebForm4 的摘要說明。
    /// </summary>
    public class WebForm4 : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DataGrid DataGrid1;
    
        private void Page_Load(object sender, System.EventArgs e)
        {
          OracleParameter[] parameters= 
         {
          new OracleParameter("cur_Single", OracleType.Cursor, 2000, ParameterDirection.Output, true, 0, 0, "",
                                              DataRowVersion.Default, Convert.DBNull),
          new OracleParameter("p_id", OracleType.Int32)
         };
        parameters[1].Value = 1;
        string connectionString = "Data Source=oracle9i;User ID=hucong;Password=hucong";
        string queryString = "SELECT_SINGLE.GetById";
        OracleConnection cn = new OracleConnection(connectionString);
        OracleCommand cmd = new OracleCommand(queryString,cn);
        cmd.CommandType = CommandType.StoredProcedure;
        foreach(OracleParameter parameter in parameters)
        {
            cmd.Parameters.Add( parameter );
        }

        cn.Open();
        OracleDataReader dr = cmd.ExecuteReader();
        while(dr.Read())
        {
            for(int i = 0; i < dr.FieldCount; i++)//擷取當前行中的列數
                Response.Write(dr[i].ToString() + ";");
            
        }
        cn.Close();

        }

        Web 表單設計器產生的程式碼#region Web 表單設計器產生的程式碼
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: 該調用是 ASP.NET Web 表單設計器所必需的。
            //
            InitializeComponent();
            base.OnInit(e);
        }
        
        /**//// <summary>
        /// 設計器支援所需的方法 - 不要使用代碼編輯器修改
        /// 此方法的內容。
        /// </summary>
        private void InitializeComponent()
        {    
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion
    }
}