About. Net accessing Oracle Database

1. system. Data. oracleclient and system. Data. oledb namespaces

Although
However, the Oracle database can be accessed through the classes in the two namespaces, but similar to SQL Server (system. Data. sqlclient
The namespace class efficiency is higher than that in the system. Data. oledb namespace), system. Data. oracleclient
The class in the namespace is more efficient than the class in the system. Data. oledb namespace (I did not personally verify this, but most of them will say this, and since
In theory, Oracle should also be specially optimized ).

Of course, another point is to make system. Data. oracleclient better:

Ratio
For example, if the data type is set to system. Data. oledb. oledbtype
Which are specific in the system. Data. oracleclient. oracletype enumeration. In addition, the Oracle number
If the number of types is large and exceeds the. NET data type range, you must use a special class in system. Data. oracleclient --
Oraclenumber type.

Well, I will not repeat these two comparisons. The following mainly discusses the types in the system. Data. oracleclient namespace, that is, ADO. Net for Oracle Data Provider (data provider ).

2. database connection:

None
Whether it is system. Data. oledb or system. Data. oracleclient, accessing Oracle must be in. net
Install the Oracle client component on a running machine (ASP. NET is a web server. (This is different from the two types of MS databases, MS stuff Installation
MDAC: after Microsoft Data Access Component version 2.6 or later, you do not need to install the SQL Server Client or
Office software .)

System Requirements:

(1) For Use
System. Data. oracleclient accesses oracle. The client component version should be in Oracle 8i client Release 3
(8.1.7) or later. The MS only ensures access to Oracle 8.1.6, Oracle 8.1.7, and Oracle 9i servers. MDAC
More than 2.6.

(2) If you use system. Data. oledb to access Oracle, the client component version 7.3.3.4.0 or later may be 8.1.7.4.1 or later. MDAC 2.6 or above.

If the server is Oracle8i or above, the client component version should be 8.0.4.1.1c.

In. net running machine, install the Oracle client, and then open net manager (Oracle 9i)/easy config (Oracle 8i) set the local service ing based on your previous experience (the service name here is used for database connection strings ).

The connection string used to access the Oracle database in system. Data. oracleclient is:

User ID = user name; Password = password; Data Source = service name

(The above is a general connection string. Detailed connection string items can be found in the document of the system. Data. oracleclient. oracleconnection. connectionstring attribute .)

The connection string used to access the Oracle database in system. Data. oledb is:

Provider = msdaora.1; user id = user name; Password = password; Data Source = service name

3. Data Types in Oracle:

Compared with SQL Server, Oracle data types are somewhat "odd": most data types of SQL Server are easy to find.. NET is close to the type in Oracle. the net type is far away. After all, Oracle is a database close to Java.

Number: Number type. Generally, it is number (m, n), M is a valid number, and N is the number of digits after the decimal point (0 by default). This is in decimal format.
Varchar2: variable-length Unicode, which is similar to the nvarchar of SQL Server (but I don't know why oracle adds "2 "). (Remove "N" from non-Unicode, the same below .)
CHAR: Unicode ).
Clob: a field used to store a large number of characters (UNICODE.
Date: date type, which is closer to the datetime of SQL Server.
In Oracle, fields cannot be of the BIT or bool type. They are generally replaced by number (1.

Like SQL Server, in SQL commands, character types must be separated by single quotation marks ('). Two single quotation marks ('') are escape characters in single quotation marks (for example, I'm fat. write an SQL command: update... set... = 'I'm fat. '...).

The special date type is as follows:

Update... set... = timestamp '2017-7-20 15:20:07 '...

Note that the timestamp keyword is used and separated by single quotation marks. Note that the date format is recognizable. The format recognized by Oracle is not as many as that recognized by SQL Server. This is different from SQL Server.

By the way, the date types in access are separated by the well number (#). Update... set... = #15:20:07 #...

4. Accessing Oracle process/function (1)

SQL
Stored procedures are often used when the server is used as a program. Procedures and functions can also be used in Oracle. Oracle
The process does not seem to have a return value, and the function that has a return value (this is somewhat like basic, and the function/process is very detailed. SQL Server
Stored Procedures can return values ).

. Net accesses the Oracle process/function in a way similar to SQL Server, for example:

Oracleparameter [] parameters = {
New oracleparameter ("returnvalue", oracletype. int32, 0, parameterdirection. returnvalue, true, 0, 0 ,"",
Datarowversion. Default, convert. dbnull)
New oracleparameter ("parameter 1", oracletype. nvarchar, 10 ),
New oracleparameter ("parameter 2", oracletype. datetime ),
New oracleparameter ("parameter 3", oracletype. Number, 1)
};

Parameters [1]. value = "test ";
Parameters [2]. value = datetime. now;
Parameters [3]. value = 1; // It can also be new oraclenumber (1 );

Oracleconnection connection = new oracleconnection (connectionstring );
Oraclecommand command = new oraclecommand ("function/process name", connection );
Command. commandtype = commandtype. storedprocedure;

Foreach (oracleparameter parameter in parameters)
Command. Parameters. Add (parameter );

Connection. open ();
Command. executenonquery ();
Int returnvalue = parameters [0]. value; // receives the function return value.
Connection. Close ();

Parameter
For more information about dbtype settings, see system. Data. oracleclient. oracletype enumeration. For example
You can use. Net decimal or system. Data. oracleclient. oraclenumber
The value of an integer parameter can be specified by the. NET Int or oraclenumber type. And so on.

In the preceding example, we can see that the function return value is specified using the parameter "returnvalue", which is the parameter of parameterdirection. returnvalue.

5. Accessing Oracle process/function (2)

Processes/functions that do not return record sets (without select output) are called in a similar way as SQL Server. However, if you want to return a record set through a process/function, it is more troublesome in Oracle.

In SQL Server, the following stored procedure:

Create procedure getcategorybooks
(
@ Categoryid int
)
As
Select * From books
Where categoryid = @ categoryid
Go

In Oracle, follow these steps:

(1) create a package containing a cursor type: (only once in a database)

Create or replace package test
As
Type test_cursor is ref cursor;
End test;

(2) process:

Create or replace procedure getcategorybooks
(
P_cursor out test. test_cursor, -- the type in the upper part of the bread. The output parameter
P_catogoryid integer
)
As
Begin
Open p_cursor
Select * From books
Where categoryid = p_catogoryid;
End getcategorybooks;

(3). Net Program:

Oracleparameters parameters = {
New oracleparameter ("p_cursor", oracletype. cursor, 2000, parameterdirection. Output, true, 0, 0 ,"",
Datarowversion. Default, convert. dbnull ),
New oracleparameter ("p_catogoryid", oracletype. int32)
};

Parameters [1]. value = 22;

Oracleconnection connection = new oracleconnection (connectionstring );
Oraclecommand command = new oraclecommand ("getcategorybooks", connection );
Command. commandtype = commandtype. storedprocedure;

Foreach (oracleparameter parameter in parameters)
Command. Parameters. Add (parameter );

Connection. open ();
Oracledatareader DR = command. executereader ();

While (dr. Read ())
{
// Your specific operation. I don't need to teach this?
}
Connection. Close ();

In addition, if datareader is used to obtain a record set, the program cannot access the data of output parameters and returned values before datareader is disabled.