The oracle| program understands the basic but essential processes involved in building a. NET application that uses an Oracle database
The downloads covered in this article
· Sample code
· Oracle Data Provider for. NET (odp.net)
With the growing popularity of Microsoft's. NET Framework, many developers are eager to learn about the best way to integrate. NET applications with Oracle-not only in terms of basic connectivity, but also with the use of Visual Studio.NET (vs.net) The relationship of the row valid application development.
In this article, I will describe the basic but essential processes involved in building a. NET application that uses an Oracle database, including:
How to add a project reference to support Oracle classes in your. NET Project
How to create an Oracle database connection string
How to use Connection, Command, and DataReader objects.
You will have the opportunity to apply what you have learned in three of hands-on practices, ranging from easier to more complex.
. NET Data Provider
In addition to basic Oracle client connectivity software,. NET applications also use tools called managed data provider, where "managed" refers to code that is managed by the. NET Framework. The data supply program 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 provisioning program optimized for a particular database platform rather than a generic. NET OLE DB data provider.
Oracle, Microsoft, and third party vendors provide optimized data supply programs for Oracle products. Oracle and Microsoft provide their Oracle data supply programs free of charge. (The supply program provided by Microsoft for version 1.1 of the. NET framework is included in this framework and does not need to be downloaded or installed separately.) Some third-party data providers support older versions of Oracle, or do not need to install Oracle client software. In this article, we hypothesized to use Oracle Data Provider for. NET (odp.net) and provide downloads separately.
When Odp.net and all required Oracle client connectivity software is installed, you can begin application development using Visual Studio.NET. Before you start development, verify client connectivity. If you are able to connect to Oracle using Sql*plus on the computer where Vs.net resides, it is proven that you have installed and configured the Oracle client software correctly.
If you have just contacted Oracle, see the "Connect to Oracle Database" section in Oracle Data Provider for. NET Developer's Guide 10g version 1 (10.1) For background information about odp.net, or see Oracle Database Administrator's Guide 10g version 1 (10.1) For general information on managing Oracle databases. You can also refer to the sample code "methods" documentation for the "Connect using Odp.net with Oracle database."
Create a project in Visual Studio.NET
After starting vs.net, the first task is to create a project. You can click the New Project button or select File | New | Project ....
A New Project dialog box appears. In the left of the dialog box, under Project Types, select your programming language. In this example, we choose VB.net. Under Templates on the right, select an engineering template. For the sake of simplicity, choose Windows application here.
You will need to specify a meaningful name for the project (we use Otnwinapp) and the solution (we use otnsamples). One solution contains one or more projects. When a solution contains only one project, many people use the same name for both.
Adding references
Because our project must be connected to an Oracle database, we must add a reference to the DLL that contains the data provider we selected. In Solution Explorer, select the References node, right-click and select Add Reference. Alternatively, you can go to the menu bar and select Project, and then select Add Reference.
The Add Reference dialog box appears.
Select Oracle.DataAccess.dll from the list, click the Select button, and finally click the OK button to enable your project to find the Odp.net data provider.
vb.net/c# statement
After adding a reference, the standard practice is to add vb.net Imports statements, C # using statements, or J # import statements. Technically these statements are not necessary, but they allow you to refer to database objects without lengthy and full names.
By convention, these statements appear near the top or top of the code file, before the namespace or class declaration.
To use the ORADB alias that is defined in the Tnsnames.ora file as described above, you need to use the following syntax:
Dim oradb as String = "Data source=oradb;" User Id=scott; Password=tiger; " ' Vb.netstring oradb = ' Data source=oradb; User Id=scott; Password=tiger; "; C#
However, you can modify the connection string so that no Tnsnames.ora files are required. Simply replace the alias with the statement that defines the alias in the Tnsnames.ora file.
As you can see above, the username and password are embedded in the connection string in unencrypted text. This is the easiest way to create a connection string. However, the method of unencrypted text from a security perspective is undesirable. Also, you need to understand that the compiled. NET application code is only a little more secure than the source code files in the form of unencrypted text. You can easily decompile. NET DLLs and EXE files, and then view the original, unencrypted text in the form of content. (encryption is actually the correct solution, but this topic is far from what we discussed here.) ）
Next, you must complete the instantiation of a connection object from the connection class. The connection string must be associated with a connection object.
Dim Conn as new OracleConnection (ORADB) ' Vb.netoracleconnection conn = new OracleConnection (ORADB); C#
Note that the connection string is associated with the connection object by passing the connection string to the constructor of the connection object, which is overloaded. The other overloads of the constructor allow the following alternative syntax:
Dim Conn as new OracleConnection () ' VB.NETconn.ConnectionString = oradboracleconnection 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.NETconn.Open (); C#
We'll 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 be instantiated from the completion of its class, and it has an overloaded constructor.
Dim SQL as String = "Select Dname from dept where deptno =" ' VB. NETDim cmd as New oraclecommand (SQL, conn) cmd. CommandType = commandtype.textstring sql = "Select Dname from dept where deptno = 10"; C#oraclecommand cmd = new OracleCommand (SQL, conn), CMD. CommandType = CommandType.Text;
Different overloads, the structure of the syntax is somewhat different. The command object has a method for executing the command-line text. Different methods apply to different types of SQL commands.
Retrieving scalar values
Retrieving data from a database can be implemented by instantiating a DataReader object and using the ExecuteReader method, which returns a OracleDataReader object. A developer can access the returned data by passing the column name or a zero-base column ordinal to the Item property b.net. Another option is to use the accessor type method to return the column data.
Dim dr as OracleDataReader = cmd. ExecuteReader () ' VB.NETdr.Read () Label1.Text = Dr. Item ("Dname") ' Retrieve by column Namelabel1.text = Dr. Item (0) ' Retrieve the ' in the ' Select listlabel1.text = Dr. GetString (0) ' Retrieve the ' the ' the ' in ' the ' select list '
C # developers must use accessor methods to retrieve data. The appropriate type of accessor is used to return the. NET local data type, and other accessors are used to return the local Oracle data type. A zero-base ordinal is passed to the accessor to specify which column to return.
OracleDataReader dr = cmd. ExecuteReader (); C#dr. Read (); Label1. Text = Dr. GetString (0); C # Retrieve the in the select list
In this simplified example, the return value of the dname is a string that is used to set the property value (also a string) of the text of the label control. However, if you retrieve Deptno instead of a string, the data type mismatch will occur. When the source data type does not match the target data type, the. NET runtime attempts to implicitly convert the data type. Sometimes the data type is incompatible, the implicit conversion fails and an exception alert is thrown out. However, even if an implicit conversion is possible, using explicit data type conversions is still better than using an implicit data type conversion.
An explicit conversion to an integral type appears as follows:
On implicit conversions, C # has less fault-tolerant capabilities than vb.net. You must perform an 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 turn off the connection to the database. The Dispose method calls the Close method.
As an option, C # provides a special syntax for automatically clearing connections when a 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 = ten"; cmd.commandtype = CommandType.Text; OracleDataReader dr = cmd. ExecuteReader (); Dr. Read (); Label1. Text = Dr. GetString (0);}
You can experiment with some of the concepts learned in the machine operation 1 (retrieving data from a database) and on machine Operation 2 (increasing interactivity).
Error handling
Error handling for try-catch-finally structures is part of the. NET language. The following is a relatively minimal example of using the try-catch-finally syntax:
Dim Conn as New OracleConnection (oradb) ' VB. Nettry Conn. Open () Dim cmd as New oraclecommand cmd. Connection = conn cmd. CommandText = "Select Dname from dept where deptno =" + TextBox1.Textcmd.CommandType = CommandType.Text If Dr. Read () Then Label1.Text = Dr. Item ("dname") ' or use Dr. Item (0) End Ifcatch ex as Exception ' catches any error MessageBox.Show (ex. Message.tostring ()) Finally Conn. Dispose () END Tryoracleconnection 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 approach will appropriately capture any errors that occur when trying to fetch data from the database, this approach is unfriendly to the user.
Oracle DBAs or developers know the meaning of ORA-12545, but the end user is unclear. A better solution is to add an additional catch statement to catch 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 Ifcatch The 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 DA Tabase 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 caught, the first catch statement branch is skipped, allowing the second catch statement to catch any other type of error. In code, Catch statements should be sorted according to the order from special to general. After implementing the user-friendly exception handling code, ORA-12545 the error message
Finally code will always execute regardless of whether the error occurred. By adding a close or Dispose method call to a connection object in a Finally code block, the database connection is always closed after the try-catch-finally code snippet has been executed. Attempting to close a database connection without opening does not cause an error. For example, if the database is unavailable and the database connection is not open, then the Finally code block attempts to close the nonexistent connection. It is not valid to perform extra close or Dispose. Simply place a close or Dispose method in the Finally code block, which will ensure that the connection is closed.
Retrieving multiple values using DataReader
So far, our example shows only how to retrieve a single value. DataReader can retrieve multiple columns and multiple rows of values. Start with a multiline, Single-column query:
Select Deptno, Dname, loc from dept where deptno = 10
To get the value of a column, you can use a zero-cardinality ordinal or a column name. The ordinal is associated with the order in the query. As a result, you can use Dr in vb.net. Item (2) or Dr. Item ("loc") to query the value of the LOC column.
The following is a code snippet that concatenates dname and the LOC column from the previous query:
Label1.Text = "the" + Dr. Item (1) + "department is in" + Dr. Item ("loc") ' VB.NETLabel1.Text = ' + Dr. GetString (1) + "department is in" + Dr. GetString (2); C#
Now we do a query that returns multiple rows:
Select Deptno, Dname, loc from Dept
To handle multiple rows returned from the DataReader, some type of looping structure is required. In addition, you need a control that can display multiple rows. DataReader is a forward-only read-only cursor and cannot be bundled with updatable or fully scrollable controls, such as the Windows Forms DataGrid control. DataReader is compatible with the ListBox control, as shown in the following code snippet:
While Dr. Read () ' VB.net ListBox1.Items.Add ("the" + dr). Item (1) + "department is in" + Dr. Item ("loc")) End Whilewhile (Dr. Read ())//c#{ListBox1.Items.Add ("the" + dr). GetString (1) + "department is in" + Dr. GetString (2);}
On-machine Operation 3, which uses DataReader to retrieve multiple columns and multiple rows, focuses on a subset of these concepts.
Summarize
This article introduces you to the process of accessing an Oracle database using the Vs.net programming language. You should now be able to connect to the database and retrieve multiple columns and multiple rows.
John Paul Cook (johnpaulcook@email.com) is a database and. NET consultant residing in Houston. He has written many articles on. NET, Oracle, and other topics, and has been developing relational database applications since 1986. His current interests include Visual Studio 2005 and Oracle 10g. He is Oracle certified DBA and Microsoft MCSD for. NET.
Machine Action 1: Retrieving data from a database
First, add a button control and a label control to the Windows form. Be sure to reserve space above these controls to add controls to the Machine action 2.
Add code that retrieves data from an Oracle database and displays the results on the form. Place the code in the Click event handler of the button. The easiest way to start this task is to double-click the button, because it will create a stub for the event handler.
Add a vb.net Imports statement before the public Class declaration, or add a C # using statement before the namespace declaration.
Add the VB.net version event statement code between Private Sub and End Sub statements (be sure to replace OTNSRVR with the host name of your server):
Dim oradb as String = "Data source=" (Description= (address_list= "_ +" (Address= (PROTOCOL=TCP) (HOST=OTNSRVR) (port=1521)) "_ +" (Connect_data= (server=dedicated) (SERVICE_NAME=ORCL)); "_ +" User Id=scott; Password=tiger; " Dim Conn as New OracleConnection (oradb) ' VB. Netconn.open () Dim cmd as New oraclecommandcmd.connection = Conncmd. CommandText = "Select Dname from dept where deptno = ten" cmd. CommandType = Commandtype.textdim dr as OracleDataReader = cmd. ExecuteReader () Dr. Read () Label1.Text = Dr. Item ("dname") ' or Dr. Item (0) Conn. Dispose ()
Add the following C # code to the parentheses {and} in the button's Click event handler, and make sure to replace OTNSRVR with the host name of your server:
String oradb = "Data source=" (description= address_list= "+" (address= (PROTOCOL=TCP) (HOST=OTNSRVR) (port=1521)) "+" ( Connect_data= (server=dedicated) (SERVICE_NAME=ORCL)); "+" User Id=scott; Password=tiger; "; O Racleconnection conn = new OracleConnection (ORADB); C#conn. Open (); OracleCommand cmd = new OracleCommand (); cmd. Connection = Conn;cmd. CommandText = "Select Dname from dept where deptno = ten"; CommandType = Commandtype.text;oracledatareader dr = cmd. ExecuteReader ();d R. Read (); Label1. Text = Dr. GetString (0); Conn. Dispose ();
Run the application. Click the button. You will see the following:
On-machine Operation 2: Increase interactivity
Now that you have implemented the basics of database access in your code, the next step is to add interactivity to your application. Instead of running a hard-coded query, you can add a text box to receive the department number (DEPTNO) entered by the user.
Add a text box control to the form and another label control: Set the Text property of the Label2 control to Enter Deptno: And make sure that the TextBox1 Text property is not set to anything.
To modify the code that defines a selection string:
Cmd. CommandText = "Select Dname from dept where deptno =" + TextBox1.Text ' VB. Netcmd.commandtext = "Select Dname from dept where deptno =" + TextBox1.Text; C#
Run the application. In Deptno enter 10, test the application. Enter an invalid DEPTNO to test the application again. The application will exit.
Modify the code to prevent an error when entering an invalid Deptno. Let's recall that the ExecuteReader method actually returns an object.
If Dr. Read () Then ' vb.net label1.text = Dr. Item (' dname ') Else Label1.Text = ' deptno not found ' end ifif (Dr. Read ())//c#{Label1. Text = Dr. Item ("Dname");} else{Label1. Text = "Deptno not Found";}
Enter the DEPTNO number that does not exist to test the application. The application does not exit now. Enter the letter A instead of the number, and then click the button. The application exits. Obviously, our application needs a better way to handle errors.
It may be noted that the application should not allow the user to make invalid input that would result in an error, but the end application must add robust error-handling capabilities. Not all errors are preventable, so error handling must be implemented.
On-Machine Operation 3: Retrieving multiple rows and columns with DataReader
Now that a single value is retrieved, the next step is to retrieve multiple rows and columns using DataReader. Add a ListBox control to the form to display the results.
Add a ListBox control to the form. Resize a control to fill most of the width of the form
Remove the WHERE clause from the query and add the following:
Cmd.commandtext = "Select Deptno, Dname, loc from dept" ' VB.NETcmd.CommandText = "Select Deptno, Dname, loc from dept"; C#
Modify the VB.net code, the final result is as follows:
Dim oradb as String = "Data source=" (Description= (address_list= "_ +" (Address= (PROTOCOL=TCP) (HOST=OTNSRVR) (port=1521)) "_ +" (Connect_data= (server=dedicated) (SERVICE_NAME=ORCL)); "_ +" User Id=scott; Password=tiger; " Dim Conn as New OracleConnection (oradb) ' VB. Netconn.open () Dim cmd as New oraclecommandcmd.connection = Conncmd. CommandText = "Select Deptno, Dname, loc from dept" CMD. CommandType = Commandtype.textdim dr as OracleDataReader = cmd. ExecuteReader () while Dr. Read () ListBox1.Items.Add ("the" + dr). Item (1) + _ "department is in" + Dr. Item ("loc")) End Whileconn.dispose ()
Modify your C # code, and the end result is as follows:
String oradb = "Data source=" (description= address_list= "+" (address= (PROTOCOL=TCP) (HOST=OTNSRVR) (port=1521)) "+" ( Connect_data= (server=dedicated) (SERVICE_NAME=ORCL)); "+" User Id=scott; Password=tiger; "; O Racleconnection conn = new OracleConnection (ORADB); C#conn. Open (); OracleCommand cmd = new OracleCommand (); cmd. Connection = Conn;cmd. CommandText = "Select Dname from dept where deptno = ten"; CommandType = Commandtype.text;oracledatareader dr = cmd. ExecuteReader (); while (Dr. Read ()) {LISTBOX1.ITEMS.ADD ("the" + dr). Item (1) + "department is in" + Dr. GetString (0));} Conn. Dispose ();
Error handling has been implemented in the provision of code downloads.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
