. Net programmers getting started with Oracle

Although Oracle and sqlserver are both relational databases, most of the SQL statements are similar, but there are still many inapplicable places from sqlserver to Oracle. This article aims to help the majority of the first oracle. net Program Quick Start. 1. Install the Oracle 11g server (optional) 1.1 Why install the server? Theoretically, you only need to install the Oracle client on the local machine. However, it is more convenient to learn from a server on the local machine. For example, Database Import/Export and database creation require the server. Note: the Oracle server really occupies a lot of memory. If you have less than 2 GB of memory in your iji, skip this step. 1.2 The installation file of the server is ipvr2, so select the specific win2008 x64 version (the installation file is about 1.9 GB). The following prompt is displayed when the operating system does not meet the installation requirements when installing ipvr2, for example, you can directly check the check box before the problem item, and then proceed to the next step. On the last installed interface, it is best to unlock the Scott account in password management (this is a classic Oracle account. Many examples and tutorials use this account for connection, the default password is Tiger) 1.3 To test whether the server is correctly installed, first open net manager to expand the service name and click the icon under the "Red Cross" button on the left. If the test is successful, the server is running normally. 2. Install odac with Oracle Developer Tools for Visual Studio on a specific client of for. net This is the connection client of for. Net officially launched by Oracle. The performance is superior to that of the system. Data. oracleclient provided by Microsoft, and it is also the best. Net client in comprehensive performance.
Bytes

Iii. Install PL/SQL developer After oracle is installed, it comes with a query tool called SQL Plus, which is equivalent to the query analyzer in SQL Server and can be used to practice SQL. However, this feature is too weak and is really difficult to use. Therefore, we recommend that you use a third-party PL/SQL developer. Baidu will be able to find the registration code. The interface for the first startup of the software is as follows:
However, if Scott/tiger @ orcl is input, it cannot be connected correctly.
Because we have not configured Tnsnames. ora File (this can be understood as web. config or machine. config, used to save all the connection information of the local Oracle client. Only after correct configuration can the Oracle client correctly connect to the Oracle DB server.) Enter % ORACLE_HOME % \ Network \ admin \ sample (note: % ORACLE_HOME % refers to the root directory of the Oracle client after installation) Find tnsnames. copy the ora file to % ORACLE_HOME % \ Network \ admin \ and open it in Notepad. Modify the file as follows:# Every line that begins with # Is a comment line
#
# Create Oracle Net service names, or aliases, for each Database Server
# You need to connect.
#
# Tnsnames. ora sample entry
#
# Alias =
# (Description =
# (Address = (Protocol = TCP) (host = myserver.mycompany.com) (Port = 1521 ))
# (CONNECT_DATA =
# (Server = dedicated)
# (SERVICE_NAME = orcl)
#)
#)
#
# You can modify the entry below for your own database.
# <Data source alias> = Name to use in the connection string data source
# <Hostname or ip> = Name or IP of the database server machine
# <Port> = database server machine port to use
# <Database Service Name> = Name of the Database Service on the server Local =
(Description =
(Address = (Protocol = TCP) (host = 127.0.0.1 ) (Port = 1521 ))
(CONNECT_DATA =
(Server = dedicated)
(SERVICE_NAME = Orcl )
)
)
Explanation: Local is the name defined by myself. You can change it as long as it is not repeated. The part after the host is the Server IP address, and the port is the port number, SERVICE_NAME is the name of the Oracle server instance when it is installed. After modification, save it. Open PL/SQL again and you will find a local in the database drop-down box, for example, enter Scott as the user name, tiger as the password, select local, and log on successfully!

Iii. Connection Between. NET and Oracle This is the biggest headache for beginners. Oracle provides four methods for. net connection. 3.1 Old ODBC To connect to the data source, open "Microsoft ODBC Administrator", as shown in figure
The setting page is displayed.
In the Server Explorer panel of vs2010, create a connection and select change, and then select the next thing for the ODBC Data Source. You just need to follow the prompts. However, I found that, ODBC in vs.net 2010/win2008 R2 cannot be connected, but test connection is successful in the data source on the control panel. The reason may only be known by Microsoft, so I did not succeed in this method and gave up! I do not like ODBC. 3.2 Microsoft built-in System. Data. oracleclient Similarly, in the Server Explorer panel, add a connection. On the displayed page, select change and switch to the input username and password. The connection is successful. Note: Since Oracle officially launched the for. Net client, Microsoft has announced that in future. Net versions, it will remove the system. Data. oracleclient namespace and will no longer provide the Microsoft version of Oracle client! See: http://go.microsoft.com/fwlink? Linkid = 144260 unless your project is not planned to be upgraded in the future, we do not recommend that you use this method. To reference system. data. oracleclient, which must be added to the system. data. oracleclient. DLL reference. The default value is c: \ Program Files (x86) \ reference assemblies \ Microsoft \ framework \. after a reference is successfully added to the netframework \ v4.0 directory, you can use the following Code Query:

Using system; using system. data. oracleclient; namespace sample {class program {static void main (string [] ARGs) {string connstring = "Data Source = Local; persist Security info = true; user id = Scott; password = tiger; Unicode = true "; using (oracleconnection conn = new oracleconnection (connstring) {oraclecommand cmd = new oraclecommand (" select * from EMP ", Conn); Conn. open (); oracledatareader DR = cmd. executereader (); While (dr. read () {console. writeline ("{0} \ t {1}", Dr [0]. tostring (), Dr [1]. tostring ();} Dr. close ();} console. readline ();}}}
 

3.3 Oledb The connection string is
Provider = msdaora; Data Source = Local; persist Security info = true; user id = Scott; Password = tiger sample code:

 
Using system; using system. data. oledb; namespace sample {class program {static void main (string [] ARGs) {string connstring = "provider = msdaora; Data Source = Local; persist Security info = true; user ID = Scott; Password = tiger "; using (oledbconnection conn = new oledbconnection (connstring) {oledbcommand cmd = new oledbcommand (" select * from EMP ", Conn); Conn. open (); oledbdatareader DR = cmd. executereader (); While (dr. read () {console. writeline ("{0} \ t {1}", Dr [0], Dr [1]);} Dr. close ();} console. readline ();}}}
 

3.4 Oracle official ODP. net The connection string is Data Source = Local; persist Security info = true; user id = Scott; Password = tiger use ODP. net, must first add to Oracle. dataaccess. DLL reference. The sample code in % ORACLE_HOME % \ 11.2.0 \ odp.net \ bin \ 4 is as follows:

Using system; using Oracle. dataaccess. client; using system. data; using system. data. common; namespace sample {class program {static void main (string [] ARGs) {string connstring = "Data Source = Local; persist Security info = true; user id = Scott; password = tiger "; string providername =" oracle. dataaccess. client "; dbproviderfactory factory = dbproviderfactories. getfactory (providername); Using (dbconnection conn = factory. createconnection () {Conn. connectionstring = connstring; Conn. open (); dbcommand cmd = Conn. createcommand (); cmd. commandtext = "select * from EMP"; cmd. commandtype = commandtype. text; dbdatareader DR = cmd. executereader (); While (dr. read () {console. writeline ("{0} \ t {1}", Dr [0], Dr [1]);} Dr. close ();} console. readline ();}}}

To test the performance of these three methods (not considered by ODBC), I wrote a piece of code to test it:

Using system; using Oracle. dataaccess. client; using system. data; using system. data. oledb; using system. data. common; using system. diagnostics; using msoracle = system. data. oracleclient; namespace sample {class program {static void main (string [] ARGs) {string temp = ""; string connstring = "Data Source = Local; persist Security info = true; user ID = Scott; Password = tiger "; string providername =" oracle. dataaccess. client "; dbproviderfactory factory = dbproviderfactories. getfactory (providername); int max = 5000; stopwatch Sw = new stopwatch (); Sw. start (); For (INT I = 0; I <Max; I ++) {using (dbconnection conn = factory. createconnection () {Conn. connectionstring = connstring; Conn. open (); dbcommand cmd = Conn. createcommand (); cmd. commandtext = "select * from EMP"; cmd. commandtype = commandtype. text; dbdatareader DR = cmd. executereader (); While (dr. read () {// console. writeline ("{0} \ t {1}", Dr [0], Dr [1]); temp = Dr [0]. tostring ();} Dr. close (); // console. writeline ("{0} Times \ t --------------------------------------------", I) ;}} SW. stop (); console. writeline ("oracle. dataaccess. client \ t {0} Time consumed: {1} millisecond ", Max, SW. elapsedmilliseconds); string connstring2 = "provider = msdaora; Data Source = Local; persist Security info = true; user id = Scott; Password = tiger"; Sw. reset (); Sw. start (); For (INT I = 0; I <Max; I ++) {using (oledbconnection conn = new oledbconnection (connstring2) {Conn. open (); oledbcommand cmd = new oledbcommand ("select * from EMP", Conn); oledbdatareader DR = cmd. executereader (); While (dr. read () {// console. writeline ("{0} \ t {1}", Dr [0], Dr [1]); temp = Dr [0]. tostring ();} Dr. close (); // console. writeline ("{0} Times \ t --------------------------------------------", I) ;}} SW. stop (); console. writeline ("system. data. oledb \ t {0} Time consumed: {1} millisecond ", Max, SW. elapsedmilliseconds); string connstring3 = "Data Source = Local; persist Security info = true; user id = Scott; Password = tiger; Unicode = true"; Sw. reset (); Sw. start (); For (INT I = 0; I <Max; I ++) {using (msoracle. oracleconnection conn = new msoracle. oracleconnection (connstring3) {Conn. open (); msoracle. oraclecommand cmd = new msoracle. oraclecommand ("select * from EMP", Conn); msoracle. oracledatareader DR = cmd. executereader (); While (dr. read () {// console. writeline ("{0} \ t {1}", Dr [0], Dr [1]); temp = Dr [0]. tostring ();} Dr. close (); // console. writeline ("{0} Times \ t --------------------------------------------", I) ;}} SW. stop (); console. writeline ("system. data. oracleclient \ t {0} Time consumed: {1} millisecond ", Max, SW. elapsedmilliseconds); console. readline ();}}}

Running result:

Oracle. dataaccess. Client 5000 elapsed time:4711Millisecond
System. Data. oledb: 5000 milliseconds
System. Data. oracleclient: 5000 milliseconds

I believe everyone knows how to choose it. Oracle is more familiar with its products.