Build. NET applications on the Oracle database

As Microsoft's. NET Framework becomes increasingly popular, many developers are eager to learn about applying. net Program The best way to integrate with Oracle-not only in terms of basic connectivity, but also in relation to effective application development using Visual Studio. NET (vs. net.

In this article, I will illustrate the basic but indispensable process involved in building. NET applications using Oracle databases, including:

• How to add project references to support Oracle classes in your. Net project
• How to create an Oracle database connection string
• How to Use the connection, command, and datareader objects.

You will have the opportunity to apply the content you learned from the three machine operations practices, and the difficulty ranges from easier to more complex.

. NET data provider

In addition to the basic Oracle client connectivity software,. NET applications also need to useManaged Data Provider("Managed" indicatesCodeIs managed by the. NET Framework. The data provider is the layer between the. NET application code and the Oracle client connectivity software. In almost all cases, the optimal performance is achieved by using a supplied program optimized for a specific database platform rather than a general. Net ole db data supply program.

Oracle, Microsoft, and third-party vendors provide data supply programs optimized for Oracle products. Oracle and Microsoft provide their Oracle data providers for free. (Microsoft supplies for the. NET Framework Version 1.1 are included in the Framework and do not need to be downloaded or installed separately .) Some third-party data providers support earlier versions of Oracle, or do not need to install the Oracle client software. In this article, we assume that Oracle data provider for. Net (ODP. net) is used and the download is provided separately.

When ODP. NET and all required Oracle client connectivity software are installed, you can start to use Visual Studio. NET for application development. Before starting development, check the client connectivity. If you use SQL * Plus on the computer where vs. NET is located to connect to Oracle, it proves that you have correctly installed and configured the Oracle client software.

If you are new to Oracle, seeOracle data provider for. Net developer guide 10G version 1 (10.1)To learn background information about ODP. net, or seeOracle Database Administrator guide 10G version 1 (10.1)For more information about managing Oracle databases. You can also refer to the sample code "method" document "connecting to Oracle Database Using ODP. Net.

Create a project in Visual Studio. NET

After vs. NET is started, the first task is to create a project. You can clickNew projectButton or selectFIle |NEW |PRoject...(As shown below ).

Figure 1: Create a new project in Visual Studio. NET

ANew projectDialog box. In the left-sidePRoject typesUnder, select yourProgramming Language. In this example, we select VB. NET. InTEmplatesSelect a project template. For simplicity, select windows application.

Figure 2: UseNew projectDialog Box

You will need to specify a meaningful name for the project (we use otnwinapp) and solution (we use otnsamples. A solution contains one or more projects. When a solution contains only one project, many people use the same name for the two.

Add reference

Because our project must be connected to the Oracle database, we must add a reference to the DLL that contains the selected data supply program. In Solution Explorer, right-click the references node and selectAdd reference. Alternatively, you can go to the menu bar and selectPRojectAnd then selectAddREference.

Figure 3: Add a reference

AppearsAdd referenceDialog box.

Figure 4: select the data provider managed by ODP. net

Select oracle. dataaccess. dll from the list, and then clickSELectButton, and then clickOKButton to enable your project to find the ODP. NET data provider.

Figure 5: solution browser after Oracle managed provider is selected

VB. NET/C # statements

After adding a reference, the standard practice is to add the VB. NET imports statement, C # using statement, or J # Import Statement. Technically, these statements are not necessary, but they allow you to reference database objects without lengthy and complete names.

By convention, these statements appear at or near the top of the code file, before the namespace or class declaration.

Imports system. data 'vb. net imports oracle. dataaccess. client 'oss. net Oracle managed provider using system. data; // C # using Oracle. dataaccess. client; // ODP. net Oracle managed provider import system. data. *; // J # import oracle. dataaccess. client; // ODP. net Oracle managed provider
 Connection string and Object  

Oracle connection strings and Oracle name parsing are inseparable. Suppose we define a database alias oradb in the tnsnames. ora file, as shown below:
Oradb = (description = (address_list = (address = (Protocol = TCP) (host = otnsvr) (Port = 1521) (CONNECT_DATA = (Server = dedicated) (SERVICE_NAME = orcl )))
 
 
To use the oradb alias defined in the tnsnames. ora file described above, you need to use the following syntax:
 
 
Dim oradb as string = "Data Source = oradb; user id = Scott; Password = tiger;" 'vb. net string oradb = "Data Source = oradb; user id = Scott; Password = tiger;"; // C #
 
 
However, you can modify the connection string so that you do not need to use the tnsnames. ora file. You only need to replace the alias with the statement that defines the alias in the tnsnames. ora file.
 
'Vb. net dim oradb as string = "Data Source = (description =" _ + "(address_list = (address = (Protocol = TCP) (host = otnsvr) (Port = 1521 ))) "_ +" (CONNECT_DATA = (Server = dedicated) (SERVICE_NAME = orcl); "_ +" User ID = Scott; Password = tiger; "string oradb =" Data Source = (description = "// C # +" (address_list = (address = (Protocol = TCP) (host = otnsvr) (Port = 1521) "+" (CONNECT_DATA = (Server = dedicated) (SERVICE_NAME = orcl); "+" User ID = Scott; Password = tiger ;";
 
 
As you can see above, the user name and password are embedded into the connection string in unencrypted text form. This is the easiest way to create a connection string. However, in terms of security, text encryption is not feasible. In addition, you need to know that the compiled. NET application code is only in the form of unencrypted textSource codeFiles are slightly safer. You can easily decompile. Net DLL and exe files to view the original unencrypted text content. (Encryption is actually the right solution, but this topic is too far different from our discussion here .)
 
 
Next, you must instantiate a connection object from the connection class. The connection string must be associated with the connection object.
 
Dim conn as new oracleconnection (oradb) 'vb. Net oracleconnection conn = new oracleconnection (oradb); // C #
 
 
Note: by passing the connection string to the constructor of the connection object (this constructor is overloaded), the connection string is associated with the connection object. Other overloads of the constructor allow the following alternative syntaxes:
 
 
Dim conn as new oracleconnection () 'vb. Net conn. connectionstring = oradb oracleconnection conn = new oracleconnection (); // C # conn. connectionstring = oradb;
 
 
After the connection string is associated with the connection object, use the open method to create the actual connection.
 
 
Conn. open () 'vb. Net conn. open (); // C #
 
 
We will introduce error handling later.
 
 
Command object 
 
 
The command object is used to specify the executed SQL command text-SQL string or stored procedure. Similar to the connection object, it must complete the instantiation of its class, and it has an overloaded constructor.
 
Dim SQL as string = "select dname from Dept where deptno = 10" 'vb. net dim cmd as new oraclecommand (SQL, Conn) cmd. commandtype = commandtype. text string SQL = "select dname from Dept where deptno = 10"; // C # oraclecommand cmd = new oraclecommand (SQL, Conn); cmd. commandtype = commandtype. text;
 
 
The syntax structure is slightly different for different reloads. The command object can be used to execute command text. Different methods are applicable to different types of SQL commands.
Retrieve Scalar Value 
 
 
Retrieving data from a database can be achieved by instantiating a datareader object and using the executereader method (which returns an oracledatareader object. You can pass the column name or the column serial number based on zero to the B. Net developer of the item attribute to access the returned data. Another option is to use the access program type method to return column data.
 
 
Dim Dr as oracledatareader = cmd. executereader () 'vb. net dr. read () label1.text = dr. item ("dname") 'retrieve by column name label1.text = dr. item (0) 'retrieve the first column in the select list label1.text = dr. getstring (0) 'retrieve the first column in the select list
 
C # developers must use accessors to retrieve data. There are appropriate types of accessors used to return the. NET local data type, and other accessors used to return the local Oracle data type. The zero-based sequence number is passed to the access program to specify which column to return.
 
 
Oracledatareader DR = cmd. executereader (); // C # dr. Read (); label1.text = dr. getstring (0); // C # retrieve the first column in the select list
 
 
In this simplified example, the return value of dname is a string, which is used to set the attribute value (also a string) of the text of the tag control ). However, if deptno is retrieved rather than a string, the data type does not match. If the source data type does not match the target data type, the. NET operation will implicitly convert the data type. If the data type is incompatible, implicit conversion fails and an exception alert is reported. However, even if implicit conversion can be performed, explicit data type conversion is better than implicit data type conversion.
 
 
The Explicit conversions to integer types are shown as follows:
 
 
Label1.text = CSTR (dr. Item ("deptno") 'vb. Net integer to string cast
 
 
In implicit conversion, the fault tolerance capability of C # is inferior to that of VB. NET. You must perform the explicit conversion on your own:
 
 
String deptno = dr. getint16 ("deptno"). tostring (); // C #
 
 
You can explicitly convert scalar values and arrays.
 
 
Close and clear 
 
 
You can call the close method or dispose method of the connection object to close the connection to the database. The dispose method calls the close method.
 
Conn. Close () 'vb. Net conn. Dispose () 'VB. NET conn. Close (); // C # conn. Dispose (); // C #
 
 
As an option, C # provides a special syntax to automatically clear a connection when the connection is out of range. You can use the using keyword to enable this feature.
 
 
Using (oracleconnection conn = new oracleconnection (oradb) {Conn. open (); oraclecommand cmd = new oraclecommand (); cmd. connection = conn; cmd. commandtext = "select dname from Dept where deptno = 10"; cmd. commandtype = commandtype. text; oracledatareader DR = cmd. executereader (); Dr. read (); label1.text = dr. getstring (0 );}
 
 
You can experiment with the concepts learned in machine operation 1 (retrieving data from the database) and machine operation 2 (adding interactivity.
Error Handling 
 
 
Try-catch-Finally structure error handling is part of the. NET language. The following is a relatively minimal example using the try-catch-finally Syntax:
 
 dim conn as new oracleconnection (oradb) 'vb. net try Conn. open () dim cmd as new oraclecommand cmd. connection = conn cmd. commandtext = "select dname from Dept where deptno =" + textbox1.text cmd. commandtype = commandtype. text if dr. read () Then label1.text = dr. item ("dname") 'or use dr. item (0) end if catch ex as exception 'catches any error MessageBox. show (ex. message. tostring () Finally Conn. dispose () end try oracleconnection conn = new oracleconnection (oradb); // C # Try {Conn. open (); oraclecommand cmd = new oraclecommand (); cmd. connection = conn; cmd. commandtext = "select dname from Dept where deptno =" + textbox1.text; cmd. commandtype = commandtype. text; If (dr. read () // C # {label1.text = dr. getstring (0) ;}} catch (exception ex) // catches any error {MessageBox. show (ex. message. tostring ();} finally {Conn. dispose () ;}
Although this method will properly capture any errors that occur when attempting to retrieve data from the database, this method is unfriendly to users. For example, see the following message displayed when the database is unavailable.
 

 
 
 

  
  
 
   
 
    
 
   
 
   
 
    
Figure 6: capture a ORA-12545 error and display it to the user.
 
   
 
  
 
 
 
 
Oracle DBA or developers are clear about the meaning of the ORA-12545, but end users are not. A better solution is to add an additional catch statement to capture the most common database errors and display user-friendly messages.
 
Catch ex as oracleexception 'catches only oracle errors if instr (1, Ex. message. tostring (), "ORA-1:", comparemethod. text) Then MessageBox. show ("error attempting to insert duplicate data. ") elseif instr (1, Ex. message. tostring (), "ORA-12545:", comparemethod. text) Then MessageBox. show ("the database is unavailable. ") else MessageBox. show ("Database Error:" + ex. message. tostring () end if catch ex as exception 'catches any error MessageBox. show (ex. message. tostring () catch (oracleexception ex) // catches only oracle errors {Switch (ex. number) {Case 1: MessageBox. show ("error attempting to insert duplicate data. "); break; Case 12545: MessageBox. show ("the database is unavailable. "); break; default: MessageBox. show ("Database Error:" + ex. message. tostring (); break;} catch (exception ex) // catches any error {MessageBox. show (ex. message. tostring ());}
Note the two catch statements in the preceding code example. If no oracle error is captured, the first error is skipped.
The catch statement branch allows the second catch statement to catch any other types of errors. In the code, it is recommended that
Sort catch statements. After implementing a user-friendly exception handling code, the ORA-12545 error message is displayed as follows:
 

 
 
 

  
  
 
   
 
    
 
   
 
   
 
    
Figure 7: user-friendly messages for ORA-12545 errors
 
   
 
  
 
 
 
 
Finally code is always executed, regardless of whether the error occurs. Add the close or
The dispose method is called. After a try-catch-finally code segment is executed, the database connection is always closed.
Attempting to close a database connection that has not been opened will not cause errors. For example, if the database is unavailable and the database connection is not enabled,
The finally code block will try to close a connection that does not exist. The execution of redundant close or dispose operations is invalid.
You only need to put a close or dispose method into the finally code block, which will ensure that the connection is closed.
 
 
Use datareader to retrieve multiple values 
 
 
So far, our example only shows how to retrieve a single value. Datareader can retrieve values of multiple columns and multiple rows. First, perform multi-row and single-column queries:
 
 
Select deptno, dname, LOC from Dept where deptno = 10
 
To obtain the column value, you can use a zero-based sequence number or column name. The sequence number is related to the order in the query. Therefore, you can use dr. Item (2) or dr. Item ("Loc") in VB. NET to query the value of the LOC column.
 
 
The following code snippet Concatenates the dname and the LOC column from the previous query:
 
 
Label1.text = "the" + dr. item (1) + "department is in" + dr. item ("Loc") 'vb. net label1.text = "the" + dr. getstring (1) + "department is in" + dr. getstring (2); // C #
 
 
Now we can perform a query that returns multiple rows:
 
 
Select deptno, dname, LOC from Dept
 
 
To process multiple rows returned from datareader, a certain type of loop structure is required. In addition, you need a control that can display multiple rows. Datareader is a forward-only read-only cursor. Therefore, datareader cannot be bundled with updatable or completely scrollable controls (such as the Windows Forms DataGrid Control. Datareader is compatible with the ListBox control, as shown in the following code segment:
 
 
While Dr. read () 'vb. net listbox1.items. add ("the" + dr. item (1) + "department is in" + dr. item ("Loc") end while (dr. read () // C # {listbox1.items. add ("the" + dr. getstring (1) + "department is in" + dr. getstring (2 );}
 
 
Operations 3 (using datareader to retrieve multiple columns and multiple rows) focuses on some of these concepts.
 
Summary 
 
 
This article describes how to access the Oracle database using the Vs. NET programming language. Now you should be able to connect to the database and retrieve multiple columns and multiple rows.