Topics on connecting ado.net to Oracle

I. Overview

Two data providers accessing ORACLE: lmicrosoft. NET Framework data provider for Oracle
Oracle. NET data providers are provided along with. NET Framework 1.1. If you are using. NET Framework 1.0, you will need to download. Net managed provider for Oracle. Regardless of the version, the data provider class is located in the namespace of system. Data. oracleclient. Loledb L "provider = oraoledb.1; Data Source = Ds; user id = system; Password = manager"

Microsoft. NET Framework data provider for Oracle lmicrosoft. NET Framework data provider for Oracle is a. NET Framework component. This component provides great convenience for us to access the Oracle database using. net. L This component is designed very similar to Microsoft. NET Framework data provider for SQL Server built in. net. l add system. Data. oracleclient. dll reference
Ii. core categories
The namespace used for organizing classes and other types in the. NET for Oracle component is system. Data. oracleclient. The namespace contains four core classes: oracleconnection, oraclecommand, oracledatareader, and oracledataadapter. L you can use a subset of classes in the system. Data. oracleclient namespace to execute Oracle stored procedures and functions. Its usage is almost the same as sqlconnection, sqlcommand, sqldatareader, and sqldataadapter. L add using system. Data. oracleclient namespace
Oracleconnection object

L // set the connection string
String connstring = "Data Source = eims; user = zbmis; Password = zbmis ;";
// Instantiate the oracleconnection object
Oracleconnection conn = new oracleconnection (connstring); l open the connection conn. open (); l close the connection conn. Close ();

Example Oracle table

L create a table named oracletypestable
"Create table oracletypestable (myvarchar2 varchar2 (3000), mynumber number (28, 4)
Primary Key, mydate date, myraw raw (255 ))";
L insert a row of data
"Insert into oracletypestable values ('test', 4, to_date ('2017-01-11
12:54:01 ', 'yyyy-mm-dd hh24: MI: ss'), '123 ')";

Oraclecommand object

L oraclecommand cmd = Conn. createcommand (); L or: l oraclecommand cmd = new oraclecommand (); L cmd. connection = conn; lcmd. commandtext = "select * From zbmis. oracletypestable ";

Oracledatareader

L oracledatareader oracledatareader1 = cmd. executereader ();
// Read data
While (oracledatareader1.read () l {
.... L}

Read character data

L // read and display the data in the first column of the First row
Oraclestring oraclestring1 = oracledatareader1.getoraclestring (0 );
Response. Write ("oraclestring" + oraclestring1.tostring ());
Read numeric data

L // read and display the data in the second column of the First row
Oraclenumber oraclenumber1 = oracledatareader1.getoraclenumber (1 );
Response. Write ("oraclenumber" + oraclenumber1.tostring ());

Read time data

L // read and display the data in the third column of the first row
Oracledatetime oracledatetime1 = oracledatareader1.getoracledatetime (2 );
Response. Write ("oracledatetime" + oracledatetime1.tostring ());

Read Binary data

L // read and display the data in the fourth column of the first row
Oraclebinary oraclebinary1 = oracledatareader1.getoraclebinary (3 );
If (oraclebinary1.isnull = false)
{
Foreach (byte B in oraclebinary1. Value)
{
Response. Write ("Byte" + B. tostring ());
}
}
Iii. Execute the Oracle Stored Procedure

L The execution of Oracle stored procedures is similar to the execution of SQL Server Stored Procedures. The loracle stored procedure cannot return a value as part of the return statement, but as an out parameter. L in addition to the ref cursor output parameter, you cannot return the result set. L you can only use the return parameter to retrieve the return value of an Oracle function L the following steps describe how to execute an oracle stored procedure and retrieve the returned results.

1. Create a stored procedure named count_job_history In the HR architecture to calculate the number of records in the job_history table.

2. Add the reference of system. Data. oracleclient. dll (Microsoft. NET Framework data provider for Oracle) to the project. L

3. Use the using command to import the types in the oracleclient class. Using system. Data. oracleclient;

4. Create an oracleconnection object. Oracleconnection conn = new oracleconnection ("Data Source = oracledb; user id = userid; Password = password;"); replace the name, user name, and password of the Oracle database with your value.

5. Create an oraclecommand object. Set the connection attribute to the connection created in step 4th. Set commandtext to the name of the stored procedure, and set its commandtext attribute to commandtype. storedprocedure. When you call the execute () method described in step 1, the command object executes the specified stored procedure.
Oraclecommand cmd = new oraclecommand (); lcmd. Connection = conn; lcmd. commandtext = "count_job_history"; lcmd. commandtype = commandtype. storedprocedure;

6. Create an oracleparameter object for input, output, and return values, and add it to the parameter set of the oraclecommand object. Cmd. Parameters. Add ("reccount", oracletype. Number). Direction = parameterdirection. output; L
This line of code is short for the following two lines of code: cmd. parameters. add ("reccount", oracletype. number); lcmd. parameters ["reccount"]. direction = parameterdirection. output;

If you want to retrieve the result set, create a dataset, able, or datareader. In this example, we only obtain the count in the output parameters created in step 1.

Use an execute method of the oraclecommand object to open the connection and execute the stored procedure. Do not forget to close the connection after the connection is used. Lconn. open (); lcmd. executenonquery (); lconn. Close (); L if you want to use dataadapter to fill the datatable or dataset, you can use dataadapter to open and close the connection.

9. processing result. In our example, you can obtain the number of records in the output parameters displayed on the console: console. writeline (CMD. Parameters ["reccount"]. Value);