asp.net中挪用oracle存儲進程的辦法。本站提示廣大學習愛好者:(asp.net中挪用oracle存儲進程的辦法)文章只能為提供參考,不一定能成為您想要的結果。以下是asp.net中挪用oracle存儲進程的辦法正文
存儲進程(Stored Procedure)是在年夜型數據庫體系中,一組為了完成特定功效的SQL 語句集,存儲在數據庫中經由第一次編譯後再次挪用不須要再次編譯,用戶經由過程指定存儲進程的名字並給出參數(假如該存儲進程帶有參數)來履行它。
存儲進程是數據庫中的一個主要對象,任何一個設計優越的數據庫運用法式都應當用到存儲進程。
不多說了,本文經由過程兩種辦法引見asp.net中挪用oracle存儲進程的辦法,詳細內容請看上面代碼。
挪用oracle存儲進程辦法一:
ORACLE代碼
CREATE OR REPLACE PROCEDURE gd_CURSOR(MYCS1 OUT SYS_REFCURSOR,MYCS2 OUT SYS_REFCURSOR,a out varchar)as BEGIN a:='test'; OPEN MYCS1 FOR SELECT 1 from dual; OPEN MYCS2 FOR SELECT 2 from dual; END;
C#代碼
/// <summary> /// 履行oracle存儲進程前往多個成果集 /// </summary> /// <param name="strProcName">存儲進程稱號</param> /// <param name="ResultCount">前往個數</param> /// <param name="paras">參數</param> /// <returns>隨意率性對象數組</returns> public object[] ExcuteProc_N_Result(string strProcName, int ResultCount, params OracleParameter[] paras) { using (OracleConnection conn = new OracleConnection("User ID=用戶名;Password=暗碼;Data Source=數據庫;")) { OracleCommand cmd = new OracleCommand(strProcName, conn); if (paras != null && paras.Length > 0) { for (int j = 0; j < paras.Length; j++) { if (paras[j].Value == null) { paras[j].Value = DBNull.Value; } } } cmd.Parameters.AddRange(paras); cmd.CommandType = CommandType.StoredProcedure; conn.Open(); cmd.ExecuteNonQuery(); int i = 0; //int nOutputParametersCount = 0; object[] objResult = new object[ResultCount]; foreach (OracleParameter p in cmd.Parameters) { if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.InputOutput) { if (p.Value is OracleDataReader) { OracleDataReader reader = p.Value as OracleDataReader; objResult[i++] = ConvertDataReaderToDataTable(reader); } else { objResult[i++] = p.Value; } } } return objResult; } } /// <summary> /// 將DataReader 轉為 DataTable /// </summary> /// <param name="DataReader">OleDbDataReader</param> protected DataTable ConvertDataReaderToDataTable(OracleDataReader reader) { DataTable objDataTable = new DataTable("TmpDataTable"); try { int intFieldCount = reader.FieldCount;//獲得以後行中的列數; for (int intCounter = 0; intCounter <= intFieldCount - 1; intCounter++) { objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter)); } //populate datatable objDataTable.BeginLoadData(); //object[] objValues = new object[intFieldCount -1]; object[] objValues = new object[intFieldCount]; while (reader.Read()) { reader.GetValues(objValues); objDataTable.LoadDataRow(objValues, true); } reader.Close(); objDataTable.EndLoadData(); return objDataTable; } catch (Exception ex) { throw new Exception("轉換失足失足!", ex); } }
挪用辦法
OracleParameter[] oracleParameter = new OracleParameter[]{ new OracleParameter("MYCS1",OracleType.Cursor), new OracleParameter("MYCS2",OracleType.Cursor), new OracleParameter("a",OracleType.VarChar,200), }; oracleParameter[0].Direction = ParameterDirection.Output; oracleParameter[1].Direction = ParameterDirection.Output; oracleParameter[2].Direction = ParameterDirection.Output; object[] xxx = ExcuteProc_N_Result("gd_CURSOR", 3, oracleParameter);
挪用oracle存儲進程辦法二:
存儲進程構造以下:
Create or WordStr Procedure xx_yy ( i_OrderID in number, i_ReturnValue out number ) is v_RealValue number; v_TotalValue number; v_AdvendorID number; begin 本身寫就行 end;
上面講一下挪用:
表構造
create table ORDERTABLE ( ORDERID NUMBER not null, TEXT NUMBER not null )
存儲進程
( i_OrderID in number, i_ReturnValue out number ) is spass ordertable.text%type; begin select text into spass from ordertable where orderid=i_OrderID; i_ReturnValue:=spass; exception when no_data_found then i_ReturnValue:=-1; end;
源碼:
using System.Data .OracleClient ;//(別忘了添加) OracleConnection Oraclecon = new OracleConnection ("Password=dloco;User ID=dloco;Data Source=dloco;"); OracleCommand myCMD = new OracleCommand(); OracleParameter[] parameters = { new OracleParameter("i_OrderID", OracleType.Number, 10),new OracleParameter("i_ReturnValue",OracleType.Number,10 )}; parameters[0].Value = 1; parameters[1].Direction = ParameterDirection.Output; myCMD.Connection = Oraclecon; myCMD.CommandType = CommandType.StoredProcedure; myCMD.CommandText = "dloco.xx_yy"; myCMD.Parameters .Add (parameters[0]); myCMD.Parameters .Add (parameters[1]); myCMD.Connection.Open(); myCMD.ExecuteNonQuery(); string result=myCMD.Parameters["i_ReturnValue"].Value.ToString(); MessageBox.Show (result); Oraclecon.Close();
以上就是asp.net中挪用oracle存儲進程的全體內容,願望對年夜家有所贊助。