Differences between SQL Server and Oracle Database Operations

oracle. net Framework database access Program namespace: system. data. oracleclient. file Name: system. data. oracleclient. DLL, which is located in the global assembly cache. By default, VS 2005 does not reference this component. when used , you only need to add a reference.
similar to sqlclient, The oracleclient namespace consists of oracleconnection, oraclecommand, oracledatareader, oracleparameter, and oracletype. The above lists the most commonly used classes. For more detailed class views, see msdn.

Field Type, parameter type
The field type is generally involved when parameter is used. In SQL Server, we generally use sqldbtype enumeration to represent various field types in the database, while in Oracle, oracletype is used. In Oracle, character fields often use varchar2 or nvarchar2, while Numeric Fields use number. Varchar is followed by a 2 character, so I didn't go deep into Oracle. I don't know what the meaning of this 2 character is. When parameter (parameter) is used.
Oracletype. varchar indicates varchar2 in the database,
Oracletype. nvarchar indicates nvarchar2 in the database,
Oracletype. number indicates number
Oracletype. datetime indicates date
For example, oracletype. int32 because it does not have the corresponding field type in Oracle, it is generally small. If the value of the number type field in the database does not have a decimal place, you can also use oracletype. int32 corresponds to number.

Execute SQL statements

":" Is used in SQL statements to indicate parameters.
In SQL Server, we can use the SQL statement as follows: "insert into table (field1, field2) values (@ value1, @ value2)", and then we will create several new paramters: new sqlparameter ("@ value1", value )...
The @ + character is used in the query string to describe the parameter. The parameter name in sqlparameter must also use the "@" symbol.
In Oracle, SQL statements cannot use the @ symbol instead of the colon ":". For example:

Oraclecommand ocmd = new oraclecommand ("insert into s_tables (tblname, caption, keyfields, tbltype, Relations) values (: name,: caption,: ID,: type,: INFO )", ocon );
Oracleparameter OPA = new oracleparameter ("name", oracletype. longvarchar, 30 );
Opa. value = Name;
Ocmd. Parameters. Add (OPA );
Opa = new oracleparameter ("caption", oracletype. longvarchar, 30 );
Opa. value = Caption;
Ocmd. Parameters. Add (OPA );
Opa = new oracleparameter ("ID", oracletype. longvarchar, 100 );
Opa. value = ID;
Ocmd. Parameters. Add (OPA );
Opa = new oracleparameter ("type", oracletype. longvarchar, 100 );
Opa. value = type;
Ocmd. Parameters. Add (OPA );
Opa = new oracleparameter ("info", oracletype. longvarchar, 100 );
Opa. value = Info;
Ocmd. Parameters. Add (OPA );
Ocmd. executenonquery ();

Or (relatively concise)

Title

Ocmd. Parameters. addrange (New oracleparameter [] {
New oracleparameter ("name", oracletype. varchar, 30 ),
New oracleparameter ("captain", oracletype. varchar, 30 ),
New oracleparameter ("ID", oracletypes. varchar, 100 ),
New oracleparameter ("type", oracletype. varchar, 100 ),
New oracleparameter ("info", oracletype. varchar, 100 ),
});
Ocmd. Parameters [0] = Name;
Ocmd. Parameters [1] = Caption;
Ocmd. Parameters [2] = ID;
Ocmd. Parameters [3] = type;
Ocmd. Parameters [4] = Info;

Use of Stored Procedures

A stored procedure in Oracle is called a packages. A package is divided into a packet header and a package body, similar to the class declaration in C ++. The header defines the name and parameters of a stored procedure. Besides the name and parameters, the package body also contains all statements of the stored procedure. Unlike SQL Server, the stored procedures in Oracle are generally written as functions rather than procedure. Oracle stored procedures do not directly return record sets. Record Sets are returned through parameters in the form of cursors. A package can contain multiple stored procedures.Package name. Stored Procedure nameThe following is a typical Oracle stored procedure. It is located in the packages named "test". Its usage should be:Test. getlist

Function Getlist (keywords In   Varchar2
, P_info_list_cursor out get_data_cur_type)
Return   Number  
As  

Begin

Open P_info_list_cursor For  
Select   *   From Test Where   Key = Keywords
;
Return   0 ;
End ;

The stored procedure returns only one number, and the record set is returned as an out parameter. The method of calling in. NET is as follows:

1 Oracleconnection oracleconn =   New Oracleconnection (connection string );
2 Oraclecommand cmd =   New Oraclecommand ( " Test. getlist " , Oracleconn );
3 Cmd. Parameters. addrange (
4 New Oracleparameter []
5 {
6 New Oracleparameter ( " Keywords " , Oracletype. varchar ),
7 New Oracleparameter ( " Returnvalue " , Oracletype. Number, 0 , Parameterdirection. returnvalue, True , 0 , 0 , "" , Datarowversion. Default, convert. dbnull ),
8 New Oracleparameter ( " P_info_list_cursor " , Oracletype. cursor, 2000 , Parameterdirection. output, True , 0 , 0 , "" , Datarowversion. Default, convert. dbnull)
9 } );
10 Cmd. Parameters [ 0 ]. Value =   ' Beauty ' ;
11 Cmd. Parameters [ 0 ]. Direction = Parameterdirection. input;
12 Cmd. commandtype = Commandtype. storedprocedure;
13 Oracleconn. open ()
14 Oracledatareader RDR = Cmd. executereader ();
15 // Other code
16 Oracleconn. Close ();
17

The parameter name in oracleparameter must be the same as the name in the stored procedure, which can be case-insensitive. A Stored Procedure defines two parameters, one keywords and the other a cursor of the out type: p_info_list_cursor. Because the function has a return value, we also need to add aReturnvalue"Parameter, which is fixed. The record set is returned through p_info_list_cursor. After the parameter configuration is complete, you can directly use the exec method of CMD. Although we use an out parameter to accept the record set cursor, this parameter does not need to be processed and oraclecommand will automatically process it, we only need to get the datareader and then read the data like sqlcommand in the past.