. NET Access to the Oracle database

For a long time, I have been using the MSSQLServerAccess database. I have hardly encountered any trouble in accessing MS through. NET. 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. Da

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. Da

For a long time, I have been using ms SQL Server/Access, through. NET Access MS home things almost never encountered any trouble. Recently, I used Oracle as a 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 using System. Data. OleDb or System. Data. OracleClient to access Oracle, you must install the Oracle client component on a machine running in. NET (Web 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. 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.
Nvarchar2: variable-length Unicode, which is similar to SQL Server nvarchar (but I don't know why Oracle adds "2 "). (Remove "n" from non-Unicode, the same below .)
Nchar: Unicode ).
Nclob: The 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 #...