C # connect to the Oracle database and operate the database through the Stored Procedure

Previously, I used C # to connect to the SQL Server database. Recently, due to work requirements, you need to use C # to connect to the Oracle database and operate the data in the database through the stored procedure to implement the function of adding, deleting, modifying, and querying data (with paging function. In addition, to facilitate database expansion in the future and achieve stable database switching with minimal changes to the current Code, you must use OleDB to connect to the Oracle database and operate the stored procedures.

First, I went to the Internet to search for materials. I found a lot of C # documents for connecting to the Oracle database and operating the database using stored procedures. However, careful research shows that Oracle database and stored procedures are all operated using OracleClient (dedicated Oracle driver. No way, you can only do it yourself. After about three days of hard work, we finally realized how to use OleDB to connect to the Oracle database and use the stored procedure to operate the Oracle database. The following is a summary of the implementation methods.

There are several difficulties in using OleDB to operate Oracle stored procedures;

1. Return record set, that is, Oracle cursor;

2. For some special applications, multiple record sets may need to be returned;

3. Return the data in the stored procedure to the program in the form of output parameters (because the number of records needs to be output for paging );

4. Add, modify, and query Text fields (corresponding to Oracle's data type is CLOB;

5. query special characters, such as whether the query text contains symbols such as "%", "", "'", and;

Next, we will introduce the above issues;

 

1. Connect to the Oracle database

There are two methods to connect to the Oracle database. One is to use the Microsoft database driver for connection; the other is to use the Oracle database driver for connection;

After checking the information on the Internet, it is the Oracle database driver, and Chinese characters may be garbled; while Microsoft's drivers are not garbled in Chinese, they cannot operate on CLOB fields. After comparison, decides to use the Oracle database driver. In the application, you must perform field operations of the CLOB type. However, no garbled Chinese characters are found after tests;

Oracle Database Connection method:

Provider = OraOLEDB. Oracle.1; User ID = username; password = dbpassword; Data Source = databasename; Persist Security Info = True; Extended Properties = PLSQLRSet = 1;

 

Extended Properties = PLSQLRSet = 1

Note: The preceding attribute must be included; otherwise, the stored procedure that returns the cursor parameter cannot be operated;

 

Ø Microsoft Database Connection Methods:

Provider = MSDAORA.1; Data Source = allrun; User ID = allrunadmin; Password = allrun; Persist Security Info = True;

 

We recommend that you use the connection method of the Oracle database.

Ii. Stored Procedure, multiple record sets are returned

Oracle Stored Procedure script:

-- Package
Create or replace
PACKAGE "maid"
TYPE T_CURSOR is ref cursor;
Procedure getasktoppage (RecordTotal out number, curRecordTotal OUT T_CURSOR, currenttoppage OUT T_CURSOR );
End;

-- Package body
Create or replace
Package body "maid"
Procedure getasktoppage (RecordTotal out number, curRecordTotal OUT T_CURSOR, currenttoppage OUT T_CURSOR) is
Begin
-- Record set 1
OPEN curRecordTotal
SELECT 1679 from dual;

-- Record set 2
OPEN curtailtoppage
SELECT * FROM orders top_page;

-- Output parameters
RecordTotal := 1688;
End;
End;

 

 

C # procedure:

 

OleDbCommand store = new OleDbCommand ();

Store. Parameters. Clear ();
Store. Connection = con;
Store. CommandType = CommandType. StoredProcedure;
Store. CommandText = "shorttop_page_package.getshorttoppage ";

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; "> Number of records returned by the output parameter: </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; "> Number of records returned from record set 1: </span>" + ds. tables [0]. rows [I] [0] + "<br/> ");
}
Response. Write ("<br/> record set 2 return data: <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 ();
}

 

 

This is because it mainly describes how to operate database stored procedures. Please write your own instructions on how to connect to the database;

 

1. Add, modify, and query CLOB fields;

Assume that the table name is shorttop_page and contains two fields: USERCODE (common string) and CONTENT (CLOB type)

The Oracle Stored Procedure script is:

-- Package
Create or replace
PACKAGE upload top_textlist_package
TYPE T_CURSOR is ref cursor;
-- CrudAction: 0: increment; 1: read; 2: Modify; 3: delete;
-- IOFields: Query and modify the field list string, separated by semicolons
-- OrderBys: Sorting Field
-- PageNo: page number
-- PageSize: number of records per page
-- RecordTotal: Total number of records
-- RecordSet: returns the record set.
Procedure merge (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 vertex top_textlist_package;

-- Package body:
Create or replace
Package body upload top_textlist_package
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: increment; 1: read; 2: Modify; 3: delete;
Procedure merge (CrudAction in integer, fldUserCode IN NVARCHAR2, fldContent in nclob, IOFields IN second, OrderBys IN second, PageNo in integer, PageSize in integer, RecordTotal out number, RecordSet IN t_out 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 top_textlist ("USERCODE", "CONTENT ")
VALUES (fldUserCode, fldContent );

RETURN;
End if;

IF CrudAction = 2 then
IF IOFields IS NULL THEN
UPDATE into top_textlist
SET "CONTENT" = FLDCONTENT,
"FUNCODE" = FLDFUNCODE
WHERE "USERCODE" = fldUserCode;
ELSE
StrCond: =, + IOFields + ,;
StrCond: = REPLACE (strCond, USERCODE, USERCODE =