oracle表的历史数据转储过程,C#连接Oracle数据库通过存储过程操作数据库 - cuizm的专栏 - CSDN博客...

C#连接Oracle数据库通过存储过程操作数据库 收藏 此文于2011-06-07被推荐到CSDN首页

此文于2011-06-08被推荐到CSDN首页

如何被推荐？

之前笔者一直用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存储过程脚本：

view plaincopy to clipboardprint?

--包

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;

--包

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#程序操作存储过程：

view plaincopy to clipboardprint?

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 ("输出参数返回记录数：" + store.Parameters["RecordTotal"].Value + "
");

for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

{

Response.Write ("记录集1返回记录数：" + ds.Tables[0].Rows[i][0] + "
");

}

Response.Write ("
记录集2返回数据：
");

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] + "
");

}

}

finally

{

con.Close ();

}

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 ("输出参数返回记录数：" + store.Parameters["RecordTotal"].Value + "
");

for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

{

Response.Write ("记录集1返回记录数：" + ds.Tables[0].Rows[i][0] + "
");

}

Response.Write ("
记录集2返回数据：
");

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] + "
");

}

}

finally

{

con.Close ();

}

因为主要是介绍操作数据库存储过程的方法，如何连接数据库请自己写；

一、对CLOB字段的增、改、查；

假设表名为：DESKTOP_PAGE，包含2个字段：USERCODE(普通字符串)、CONTENT(CLOB类型)

Oracle存储过程脚本为：

view plaincopy to clipboardprint?

--包

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;

--包

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脚本：

view plaincopy to clipboardprint?

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;

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

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/cuizm/archive/2011/06/07/6528869.aspx