C# - 使用 SYS_REFCURSOR 來回傳資料集 | 張小呆的碎碎唸 - 點部落

2011-06-28

C# - 使用 SYS_REFCURSOR 來回傳資料集

7620
0
Visual C#

摘要:C# - 使用 SYS_REFCURSOR 來回傳資料集

在學習 Oracle 的這段時間中，大多都是利用一些工具來下下語法，或者寫個 Procedure 做一些簡單的傳入參數與簡易的傳出參數(指的是一些 ID 或者 INT 的回傳)，所以沒有甚麼太過複雜的玩法。

這篇的目的主要是記錄有關於，使用 Procedure 來回傳一個資料集，在 MS SQL 的 Procedure 來說，要做到這點，是在基本不過的事，因為我只要寫好一個 Procedure，如下：

MS SQL：

CREATE PROCEDURE [dbo].[usp_SelectData]
@p_Year char(4)
AS
BEGIN
SELECT *
FROM Table_A
WHERE Column_Year = @p_Year
ORDER BY Column_Year;
END

然後再搭配程式，就可以將資料集呈現在UI上了

程式：

dataGridView1.DataSource = null;

DataSet ds = new DataSet("dsDataList");

using (SqlConnection sc = new SqlConnection(Ms SQL Connection String))
{
sc.Open();

SqlTransaction st = sc.BeginTransaction("SampleTransaction");

try
{
  using (SqlCommand cmd1 = new SqlCommand("usp_SelectData", sc, st))
  {
   cmd1.CommandType = CommandType.StoredProcedure;

cmd1.Parameters.Add(new SqlParameter("@p_Year", SqlDbType.Char)).Value = txtYear.Text;

SqlDataAdapter da = new SqlDataAdapter(cmd1);

da.Fill(ds, "dtDataList");

dataGridView1.DataSource = ds.Tables["dtDataList"];
}

  using (SqlCommand cmd2 = new SqlCommand())
  {
   cmd2.Connection = sc;
   cmd2.Transaction = st;
   cmd2.CommandType = CommandType.Text;

cmd2.CommandText = "INSERT INTO Table_A (...) VALUES(...)";
cmd2.ExecuteNonQuery();

   st.Commit();
  }
}
catch (Exception ex)
{
  st.Rollback("SampleTransaction");
  MessageBox.Show(ex.ToString());
}
}

滿簡單且基礎的寫法，也算是基本功了。但我的困惱是，那 Oracle 呢!? 在 Oracle 中，似乎不是這樣寫就可以讓你把資料集給傳給前端了。所以就稍微花一點時間，找了一些相關資料，就完成了，以下就來看看嚕...

步驟一：建立一個 Procedure

PL/SQL:

CREATE OR REPLACE PROCEDURE USP_SELECTDATA(P_PARAM IN VARCHAR2, P_COLLECTION OUT SYS_REFCURSOR) AS

BEGIN

OPEN P_COLLECTION FOR
SELECT *
FROM TABLE_A
WHERE COLUMN_A = P_PARAM
ORDER BY COLUMN_A;

END USP_SELECTDATA;

※紅色的部份就是重點啦...

步驟二：撰寫前端程式

程式：

dataGridView1.DataSource = null;

DataSet ds = new DataSet("dsDataList");

using (OracleConnection sc = new OracleConnection(strOracle))
{
sc.Open();

try
{
  using (OracleCommand cmd1 = new OracleCommand("dcms.usp_SelectData", sc))
  {
   cmd1.CommandType = CommandType.StoredProcedure;

cmd1.Parameters.Add(new OracleParameter("p_Param_Code", OracleType.NVarChar)).Value = "AS400_UPLOAD_PROGRAM";

   OracleParameter param1 = cmd1.Parameters.Add("p_Collection", OracleType.Cursor);

   param1.Direction = ParameterDirection.Output;

OracleDataAdapter da = new OracleDataAdapter(cmd1);

da.Fill(ds, "dtDataList");

   dataGridView1.DataSource = ds.Tables["dtDataList"];
  }
}
catch (Exception ex)
{
  MessageBox.Show(ex.ToString());
}
}

參考：
cursor 與refcursor及sys_refcursor的區別
 How to call an Oracle Stored Procedure that returns one or more REF CURSORS, using ADO from C++
Using REFCURSOR Bind Variables

回首頁