C#连接Oracle数据库通过存储过程操作数据库

之前笔者一直用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 style="color:red;" mce_style="color:red;">输出参数返回记录数：</span>" + store.Parameters["RecordTotal"].Value + "<br/>"); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { Response.Write ("<span style="color:red;" mce_style="color:red;">记录集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=' || fldUserCode || ','); strSQLView := 'UPDATE DESKTOP_PAGE' || ' SET ' || strCond || ' WHERE USERCODE=''' || fldUserCode || '''; EXECUTE IMMEDIATE strSQLView; IF INSTR (IOFIELDS, ',CONTENT,') > 0 THEN UPDATE DESKTOP_TEXTLIST SET "CONTENT" = fldContent WHERE "USERCODE" = FLDUSERCODE; END IF; END IF; RETURN; END IF; strCond := NULL; strJoin := NULL; strEscape := 'ESCAPE CHR (92 USING NCHAR_CS)'; IF fldUserCode IS NOT NULL THEN strCond := strCond || strJoin || ' USERCODE = ''' || REPLACE (fldUserCode, '''', '''''') || ''''; strJoin := ' AND'; END IF; IF FLDCONTENT IS NOT NULL THEN strLike := REPLACE (fldContent, '''', ''''''); strCond := strCond || strJoin || ' DBMS_LOB.INSTR (CONTENT, ''' || strLike || ''') > 0'; strJoin := ' AND'; END IF; IF strCond IS NULL THEN strWhere := ''; strAnd := ''; ELSE strWhere := ' WHERE'; strAnd := ' AND'; END IF; IF CrudAction = 1 THEN IF IOFields IS NULL THEN strFields := 'DESKTOP_TEXTLIST.*'; ELSE strFields := IOFields; END IF; IF OrderBys IS NULL THEN strOrderBys := 'USERCODE, PAGECODE, FUNCODE'; ELSE strOrderBys := REPLACE (OrderBys, ':0', ''); strOrderBys := REPLACE (strOrderBys, ':1', ' DESC'); END IF; strSQLCalc := 'SELECT COUNT(*)' || ' FROM DESKTOP_TEXTLIST' || STRWHERE || STRCOND; EXECUTE IMMEDIATE strSQLCalc INTO RecordTotal; IF PageNo IS NULL THEN strSQLView := 'SELECT ' || strFields || ' FROM DESKTOP_TEXTLIST' || strWhere || strCond || ' ORDER BY ' || strOrderBys; ELSE strSQLView := 'SELECT * FROM (' || ' SELECT ' || strFields || ', rownum rn FROM DESKTOP_TEXTLIST' || ' WHERE rownum <= ' || (PageSize * PageNo) || ' ORDER BY ' || strOrderBys || ')' || ' WHERE rn >= ' || (PageSize * (PageNo -1)) || strAnd || strCond || ' ORDER BY ' || strOrderBys; END IF; OPEN RecordSet FOR strSQLView; return; ELSE strSQLView := 'DELETE DESKTOP_TEXTLIST' || strWhere || strCond; EXECUTE IMMEDIATE strSQLView; END IF; END DesktopTextListStorer; END DESKTOP_TEXTLIST_PACKAGE;

 

 

四、对特殊字符的查询，如查询文本中是否包含“%”、“/”、“’”、“_”等符号；

Oracle脚本：

strEscape := 'ESCAPE CHR (92 USING NCHAR_CS)'; strLike := REPLACE (fldPageName, '%', '/%'); STRLIKE := REPLACE (STRLIKE, '_', '/_'); strLike := REPLACE (strLike, '/', '//'); strLike := REPLACE (strLike, '''', ''''''); strCond := strCond || strJoin || ' PAGENAME LIKE ''%' || strLike || '%'' ' || strEscape;

以上是笔者操作Oracle数据库总结出来的一些经验，然后对大家有所帮助。s