.NET Access Oracle Database
For a long time, I have always used the MS SQL Server / Access database, and I have almost have never touched the MS from home. In recent projects, Oracle is used as a database, learn some .NET access to Oracle, and find a lot of problems.
1. System.Data.OracleClient and System.Data.Oledb Namespace
Although classes through these two namespaces can access the Oracle database, but similar to SQL Server (System.Data.sqlClient namespace is higher than those in the system.Data.oledb namespace), System. Data.OracleClient Namespaces are more efficient than system.data.oledb namespace (I don't have personally verified, but most places will say this, and since it is specifically for Oracle, it should also Specially made targeted optimization).
Of course, there is another point to say from targeted, system.data.OracleClient is better:
For example, the data type, system.data.oledb.oledbType enumeration is not specified in the system.data.OracleClient.Oracletype enumeration; in addition, Oracle's Number Type If the number is huge, it is exceeded .NET data type In the case of the scope, you must use the special class - ORACLENUMBER type in System.Data.OracleClient.
Ok, these two comparisons will not be described, and the types in the System.Data.OracleClient namespace are discussed, that is, ADO.NET for Oracle Data Provider (Data Provider).
2. Database Connectivity:
Whether it is System.Data.OleDb or System.Data.OracleClient Access Oracle is required to run the Oracle client component (the web server in the ASP.NET) is installed in the .NET. (This is different from the two databases of MS, MS's things installed MDAC: Microsoft Data Access Component 2.6 or later, you can access the SQL Server client or Office software, you can access.)
System Requirements:
(1) If you use System.Data.OracleClient to access Oracle, the client component version should be in Oracle 8i Client Release 3 (8.1.7). MS only ensures access to Oracle 8.1.6, Oracle 8.1.7, and Oracle 9i servers. MDAC 2.6 or more.
(2) If you use System.Data.OleDb to access Oracle, the client component version is 7.3.3.4.0 or more or 8.1.7.4.1 or more. MDAC 2.6 or more.
If the server is Oracle8i, the client component version should be 8.0.4.1.1c.
In the .NET running machine, install the Oracle client, then open NET Manager (Oracle 9i) / Easy Config (Oracle 8i) to set a map of your previous experience (here the service name here is used for database connection strings).
The connection string of access to the Oracle database in System.Data.OracleClient is: user id = username; password = password; data source = service name
(The above-mentioned general connection string, detailed connection string project can be found in the documentation of the System.Data.OracleClient.OrCleConnection.Connectionstring property.)
The connection string of the access Oracle database in System.Data.OleDB is:
Provider = msdaora.1; user ID = username; password = password; Data Source = Service Name
3. Data type in Oracle:
Oracle's data type and SQL Server compared to SQL Server: SQL Server's data types are easy to find. NET is more close, and the type in Oracle is much smaller from the .NET type, after all, Oracle is Java close database.
Number: Digital Type, usually Number (M, N), m is a valid number, N is the number of digits after the decimal point (default 0), which is called by decimal. NVARCHAR2: Unicode, this is more like SQL Server NVARCHAR (but I don't know why oracle adds "2"). (Remove "N" as non-Unicode, the same.) Nchar: Unicode. NClob: "Writing" field, store a large number of characters (Unicode). Date: Date type, which is relatively close to DateTime for SQL Server.
Oracle in the field cannot be a type of BIT or BOOL, typically Number (1) instead.
Like SQL Server In the SQL command, the character type requires separation of single quotes ('), two single quotes (' ') are character escapes of single quotes (such as I'm FAT. Write a SQL command is : Update ... set ... = 'i''m fat.' ...).
The more special is the date type: For example, you need to write this time you need to write this time:
Update ... set ... = timestamp '2004-7-20 15:20:07' ...
Note that the TimeStamp key is used, and single quotes are separated; in addition, please pay attention to the date format, the above format is identifiable, and the format of Oracle does not have more SQL Server. This is different from SQL Server.
By the way: The date type in Access is separated by a well number (#), Update ... set ... = # 2004-7-20 15: 20: 07 # ...
4. Access Oracle Process / Functions (1)
The stored procedure is often used when the SQL Server is programmed, and the process can also be used in Oracle, and functions can also be used. The process of Oracle seems to have a return value, and there is a function of the return value (this is some like BASIC, the function / process is very meticulous. SQL Server stored procedure can have a return value). .NET Access Oracle Process / Function is 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 ("Parameters 2", ORACLEPE.DATETIME, New OracleParameter ("Parameters 3", ORACletype.Number, 1)}; Parameters [1] .Value = "Test"; parameters [2]. Value = datetime.now; parameters [3] .value = 1; // 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; // Receive function return value connection.close ();
Parameter's DBTYPE setting, please refer to the documentation of System.Data.OracleClient.Oracletype enumeration, such as the value of the Number type in the Oracle database, can be specified with .NET DECIMAL or System.Data.OracleClient.OrcleNumber type; Integer type parameter The value can be specified with the .NET INT or ORACLENUMBER type. and many more.
The above example has seen the function return value is specified by parameters named "returnvalue", which is parameter for parameterDirection.ReturnValue.
5. Access Oracle Process / Functions (2)
Do not return the process / function of the record set (without SELECT output), call up and SQL Server is more similar. But if you want to return to records through the process / function, you will be more troublesome in Oracle.
In SQL Server, the following stored procedures:
Create Procedure getcategoryBooks (@categoryid int) assselect * from bookswhere categoryId = @categoryIdgo In Oracle, follow these steps:
(1) Create a package with 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, - this is the type of bread, output parameters p_CatogoryID INTEGER) ASBEGIN OPEN p_CURSOR FOR SELECT * FROM Books WHERE CategoryID = p_CatogoryID; END GetCategoryBooks;
(3) In the .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.executeRead (); while (Dr.Read ()) {// your specific operation. Don't you need to teach this? } Connection.Close ();
There is another thing to point out that if a recordset is obtained using DataReader, the program cannot access the output parameter and the return value before the DataReader is closed.
Ok, first, in short. Net visit Oracle, there are still many places and SQL Server, and learn slowly.
(Reprinted please indicate: break Po (percyboy) Source: blog Church)
Published in Friday, August 06, 2004 3:35 PM
