Connecting Oracle database instances through ASP.net tutorial

Connecting Oracle database instances through ASP.net tutorial

For a long time, I have been using the MS SQL server/access database, access to Ms Home through. NET almost nothing trouble. In the recent project, Oracle was used as a database to learn about some. NET access to Oracle, and found a lot of problems.
1. System.Data.OracleClient and System.Data.OleDb namespaces
Although a class with both namespaces can access an Oracle database, it is similar to SQL Server (the class efficiency of the System.Data.SqlClient namespace is higher than the class in the System.Data.OleDb namespace). The class in the System.Data.OracleClient namespace is more efficient than the class of the System.Data.OleDb namespace (which I did not personally verify, but most places say it, and since it is specifically for Oracle What should be done in theory should be specifically tailored to optimize it.
The other point, of course, is that System.Data.OracleClient is better:
For example, data types, the System.Data.OleDb.OleDbType enumeration does not include those in the System.Data.OracleClient.OracleType enumeration; In addition, Oracle number Type if the number is large and beyond the scope of the. NET data type, you must use the specialized class in System.Data.OracleClient-the OracleNumber type.
Well, no more repeating these two comparisons, the following is mainly about the types in the System.Data.OracleClient namespace, that is, the ado.net for Oracle data Provider.
2. Database connection:
Both System.Data.OleDb and System.Data.OracleClient access Oracle need to install the Oracle client component on a machine that is running in. NET (the asp.net is the WEB server). (This is different from the MS's two databases, Ms things installed Mdac:microsoft Data access Component more than 2.6 version, you can no longer install SQL Server client or Office software to 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) version. MS only ensures access to Oracle 8.1.6, Oracle 8.1.7, Oracle 9i servers. MDAC over 2.6.
(2) If using System.Data.OleDb to access Oracle, the client component version 7.3.3.4.0 above or 8.1.7.4.1 above. MDAC over 2.6.
If the server is above oracle8i, the client component version should be 8.0.4.1.1c.
In the. NET run machine, install the Oracle client, and then open NET Manager (Oracle 9i)/Easy Config (Oracle 8i) to set the mapping of the local service in your previous experience (the service name here is used for the database connection string).
The connection string to access the Oracle database in System.Data.OracleClient is:
User id= username; password= password; Data source= Service Name
(The above is a generic connection string, and detailed connection string items can be found in the documentation for the System.Data.OracleClient.OracleConnection.ConnectionString property.) )
The connection string to access the Oracle database in System.Data.OleDb is:
Provider=MSDAORA.1; User id= username; password= password; Data source= Service Name
3. Data types in Oracle:
Oracle's data type is "strange" compared to SQL Server: Most types of SQL Server are easy to find in. NET, the type in Oracle is far from the. NET type, after all, Oracle is close to Java According to the library.
Number: Numeric type, generally m,n, M is a valid number, N is the number of digits after the decimal point (default 0), which is said in decimal.
NVARCHAR2: Variable long character (Unicode), this is more like SQL Server nvarchar (but I don't know why Oracle added "2"). (remove "n" as non-Unicode, hereinafter.) ）
NCHAR: fixed-length character (Unicode).
NCLOB: A "writing" field that is used when storing a large number of characters (Unicode).
Date: Dates type, compared to datetime of SQL Server.
A field in Oracle cannot be a type of bit or bool, typically a number (1) instead.
Like SQL Server in SQL commands, character types need to be separated by single quotes ('), and two single quotes (') are escape (for example: I ' m fat. Write an SQL command is: UPDATE ... SET ... = ' I ' m fat. ）。
The more special is the date type: For example, to write to 2004-7-20 15:20:07 this time need to write as follows:
UPDATE ... SET ... = TIMESTAMP ' 2004-7-20 15:20:07 ' ...
Note that the TIMESTAMP keyword is used and is separated by single quotes, and note the date format, which is recognizable and that Oracle recognizes a format that is not as much as SQL Server. This is a different place 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. accessing Oracle processes/functions (1)
SQL Server often uses stored procedures as a program, and processes are also available in Oracle, and functions can also be used. Oracle's process does not seem to have a return value, there is a return value is the function (this is somewhat like BASIC, function/process distinction is very meticulous.) SQL Server stored procedures can have return values.
. NET access to Oracle procedures/functions is very 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 ("Parameters 1", Oracletype.nvarchar, 10),
New OracleParameter ("Parameter 2", Oracletype.datetime),
New OracleParameter ("Parameters 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; Receive function return value
Connection. Close ();
Parameter DbType settings See System.Data.OracleClient.OracleType enumerated documents, such as: The value of a parameter of type number in an Oracle database can be used with. NET decimal or System. The Data.OracleClient.OracleNumber type specifies that the value of an Integer type parameter can be specified with a. NET int or a oraclenumber type. Wait a minute.
The above example has seen that the function return value is specified with a parameter named "ReturnValue", which is a parameterdirection.returnvalue parameter.
5. accessing Oracle processes/functions (2)
Procedures/functions that do not return a recordset (without a SELECT output) are called more like SQL Server. But if you want to return a recordset through a process/function, it's more cumbersome in Oracle.
In SQL Server, such as the following stored procedure:
CREATE PROCEDURE Getcategorybooks
(
@CategoryID int
)
As
SELECT * FROM Books
WHERE CategoryID = @CategoryID
Go
In Oracle, use the following procedure:
(1) Create a package that contains 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,--here is the type in the bread, output parameters
P_catogoryid INTEGER
)
As
BEGIN
OPEN P_cursor for
SELECT * FROM Books
WHERE Categoryid=p_catogoryid;
End Getcategorybooks;
(3) in. NET Programs:
Oracleparameters parameters = {
New OracleParameter ("P_cursor", OracleType.Cursor, Watts, 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. You don't need me to teach this, do you?
}
Connection. Close ();
Another point to note is that if you use DataReader to get a recordset, the program will not be able to access the data for the output parameters and return values until the DataReader is closed.