[Hide] accessing the Oracle database through. net

For a long time, I have been using ms SQL Server/Access database, through. Net Access Ms home things almost never encountered any trouble. Recently, I used oracle as a database in my project. I learned some things about. Net accessing Oracle and found that there are actually a lot of problems.

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

Although the classes in the two namespaces can access the Oracle database. data. the class efficiency of the sqlclient namespace is higher than that of the system. data. the class in the oledb namespace is higher), system. data. the classes in the oracleclient namespace are larger than those in the system. data. the oledb namespace class is more efficient (I did not personally verify this, but most places will say so, moreover, since the things specifically designed for Oracle should also be specifically optimized in theory ).

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

For example, the data type, system. data. oledb. system is not listed in oledbtype enumeration. data. oracleclient. which are specific in the oracletype enumeration. In addition, if the number type of Oracle is large, it exceeds. NET data type range, you must use system. data. special Class in 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:

Whether you use system. Data. oledb or system. Data. oracleclient to access Oracle, you must install the Oracle client component on the. NET running machine (Web server in ASP. NET. (This is different from the two types of MS databases, MS things install MDAC: Microsoft Data Access Component 2.6 or later, there is no need to install the SQL Server Client or office software, .)

System Requirements:

(1) If you use system. Data. oracleclient to access Oracle, the client component version should be later than Oracle 8i client Release 3 (8.1.7. The MS only ensures access to Oracle 8.1.6, Oracle 8.1.7, and Oracle 9i servers. MDAC 2.6 or above.

(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, which is generally 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.
• Nvarchar2: Unicode, which is similar to nvarchar of SQL Server (but I don't know why oracle adds 2 "). (Remove "N" from non-Unicode, the same below .)
• Nchar: Fixed-length Unicode ).
• Nclob: "Write composition" field, used for storing a large number of characters (UNICODE.
• Date: Date type, which is close to 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)

Stored procedures are often used when SQL Server is used as a program. Procedures and functions can also be used in Oracle. The Oracle process does not seem to have a return value, but the function that has a return value (such as basic. 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 ();

For the dbtype setting of parameter, see system. data. oracleclient. documents of oracletype enumeration. For example, the value of number type parameters in Oracle Database can be used. net decimal or system. data. oracleclient. the value of an integer parameter can be used.. 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_cursorOutTest. 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.