之前筆者一直用C#連接SQL Server數據庫。近期由於工作需要,需要用C#連接Oracle數據庫,並且要通過存儲過程,來操作數據庫中的數據,實現增、刪、改、查(有分頁功能)功能。並且,為了今後對數據庫擴展方便,對現在的代碼改動最小的情況下,實現數據庫的平穩切換,必須采用OleDB的方式連接Oracle數據庫、操作存儲過程。
首先去網上搜索資料,確實找到了很多C#連接Oracle數據庫,和用存儲過程操作數據庫的資料。但是仔細研究發現,清一色的全部是使用OracleClient(Oracle專用驅動)的方式對Oracle數據庫及存儲過程進行操作。沒辦法,只能自己動手、豐衣足食。經過3天左右的辛苦努力,終於實現了用OleDB連接Oracle數據庫,並且用存儲過程來操作Oracle數據庫的方法。下面將實現方式總結一下。
用OleDB操作Oracle存儲過程,有幾個難題;
1、 返回記錄集,也就是Oracle的游標;
2、 對於某些特殊的應用,有可能需要返回多個記錄集;
3、 將存儲過程中的數據,通過輸出參數的形式返回給程序(因為要做分頁,需要將記錄數輸出來);
4、 對Text大文本字段(對應Oracle的數據類型是CLOB)的增、改、查操作;
5、 對特殊字符的查詢,如查詢文本中是否包含“%”、“”、“’”、“_”等符號;
接下來,主要針對以上問題,進行介紹;
一、連接Oracle數據庫
連接Oracle數據庫,主要有2種方式。一種是使用微軟的數據庫驅動進行連接;另外一種是使用Oracle的數據庫驅動進行連接;
經過在網上查資料,說是Oracle的數據庫驅動,中文可能會出現亂碼;微軟的驅動雖然中文沒有亂碼,但是對於CLOB類型的字段,無法操作;經過對比,決定使用Oracle的數據庫驅動。因為應用中,肯定要有CLOB類型的字段操作;但是經過測試,未發現中文出現亂碼的情況;
Ø Oracle數據庫連接方式:
Provider=OraOLEDB.Oracle.1;User ID=username;password=dbpassword;Data Source=databasename;Persist Security Info=True;Extended Properties=PLSQLRSet=1;
Extended Properties=PLSQLRSet=1
注意:上面這個屬性一定要帶上,否則無法操作有返回游標參數的存儲過程;
Ø 微軟的數據庫連接方式:
Provider=MSDAORA.1;Data Source=allrun;User ID=allrunadmin;Password=allrun;Persist Security Info=True;
建議使用Oracle數據庫的連接方式。
二、存儲過程,返回多個記錄集
Oracle存儲過程腳本:
--包
create or replace
PACKAGE "DESKTOP_PAGE_PACKAGE" as
TYPE T_CURSOR IS REF CURSOR;
procedure GetDesktopPage (RecordTotal OUT NUMBER, curRecordTotal OUT T_CURSOR, curDesktopPage OUT T_CURSOR);
end;
--包體
create or replace
PACKAGE BODY "DESKTOP_PAGE_PACKAGE" as
procedure GetDesktopPage (RecordTotal OUT NUMBER, curRecordTotal OUT T_CURSOR, curDesktopPage OUT T_CURSOR) is
begin
--記錄集1
OPEN curRecordTotal FOR
SELECT 1679 FROM DUAL;
--記錄集2
OPEN curDesktopPage FOR
SELECT * FROM DESKTOP_PAGE;
--輸出參數
RecordTotal := 1688;
end;
end;
C#程序操作存儲過程:
OleDbCommand store = new OleDbCommand ();
store.Parameters.Clear ();
store.Connection = con;
store.CommandType = CommandType.StoredProcedure;
store.CommandText = "DESKTOP_PAGE_PACKAGE.GetDesktopPage";
store.Parameters.Add ("RecordTotal", OleDbType.Numeric).Value = null;
store.Parameters["RecordTotal"].Size = -1;
store.Parameters["RecordTotal"].Scale = 0;
store.Parameters["RecordTotal"].Precision = 0;
store.Parameters["RecordTotal"].Direction = ParameterDirection.Output;
try
{
OleDbDataAdapter da = new OleDbDataAdapter (store);
DataSet ds = new DataSet ();
da.Fill (ds);
Response.Write ("<span mce_>輸出參數返回記錄數:</span>" + store.Parameters["RecordTotal"].Value + "<br/>");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Response.Write ("<span mce_>記錄集1返回記錄數:</span>" + ds.Tables[0].Rows[i][0] + "<br/>");
}
Response.Write ("<br/>記錄集2返回數據:<br/>");
for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
{
Response.Write (ds.Tables[1].Rows[i][0] + ", " + ds.Tables[1].Rows[i][1] + ", " + ds.Tables[1].Rows[i][2] + "<br/>");
}
}
finally
{
con.Close ();
}
因為主要是介紹操作數據庫存儲過程的方法,如何連接數據庫請自己寫;
一、對CLOB字段的增、改、查;
假設表名為:DESKTOP_PAGE,包含2個字段:USERCODE(普通字符串)、CONTENT(CLOB類型)
Oracle存儲過程腳本為:
--包
create or replace
PACKAGE DESKTOP_TEXTLIST_PACKAGE AS
TYPE T_CURSOR IS REF CURSOR;
--CrudAction:0:增;1:讀;2:改;3:刪;
--IOFields:查、改的字段列表字符串,使用分號分隔
--OrderBys:排序字段
--PageNo:頁碼
--PageSize:每頁記錄數
--RecordTotal:總記錄數
--RecordSet:返回記錄集
procedure DesktopTextListStorer (CrudAction IN INTEGER, fldUserCode IN NVARCHAR2, fldContent IN NCLOB, IOFields IN NVARCHAR2, OrderBys IN NVARCHAR2, PageNo IN INTEGER, PageSize IN INTEGER, RecordTotal OUT NUMBER, RecordSet IN OUT T_CURSOR);
END DESKTOP_TEXTLIST_PACKAGE;
--包體:
create or replace
PACKAGE BODY DESKTOP_TEXTLIST_PACKAGE AS
strFields VARCHAR2 (4000);
strOrderBys VARCHAR2 (4000);
strJoin VARCHAR2 (10);
strCond VARCHAR2 (4000);
strLike VARCHAR2 (500);
strWhere VARCHAR2 (10);
strAnd VARCHAR2 (10);
strSQLCalc VARCHAR2 (4000);
strSQLView VARCHAR2 (4000);
strEscape VARCHAR2 (500);
--CrudAction:0:增;1:讀;2:改;3:刪;
procedure DesktopTextListStorer (CrudAction IN INTEGER, fldUserCode IN NVARCHAR2, fldContent IN NCLOB, IOFields IN NVARCHAR2, OrderBys IN NVARCHAR2, PageNo IN INTEGER, PageSize IN INTEGER, RecordTotal OUT NUMBER, RecordSet IN OUT T_CURSOR) AS
BEGIN
RecordTotal := 0;
IF CrudAction < 0 OR CrudAction > 3 OR CrudAction IS NULL THEN
RETURN;
END IF;
IF CrudAction = 0 THEN
INSERT INTO DESKTOP_TEXTLIST ("USERCODE", "CONTENT")
VALUES (fldUserCode, fldContent);
RETURN;
END IF;
IF CrudAction = 2 then
IF IOFields IS NULL THEN
UPDATE DESKTOP_TEXTLIST
SET "CONTENT" = FLDCONTENT,
"FUNCODE" = FLDFUNCODE
WHERE "USERCODE" = fldUserCode;
ELSE
strCond := , + IOFields + ,;
strCond := REPLACE (strCond, ,USERCODE,, ,USERCODE=