Shulou(Shulou.com)06/02 Report--

This article is about how to use VS2010 to build .NET applications on Oracle database. I think it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it.

In this article, I will explain the basic but indispensable processes involved in building a C # or Visual Basic .NET application that uses an Oracle database, including:

How to add a project reference to support the Oracle class library 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 computer operations, and the difficulty has evolved from relatively simple to complex. If you are already familiar with .NET applications that build Oracle databases using Visual Studio 2005 Oracle 2008, you will find that the experience of using Visual Studio 2010 is very similar.

.net data provider

In addition to the basic Oracle client connectivity software, .NET applications also need to use a tool called a managed data provider, where "managed" means that the code is managed by the .NET Framework. A data provider is the layer between .NET application code and Oracle client connectivity software. In almost all cases, optimal performance is achieved by using a provider optimized for a specific database platform rather than a general .NET OLE DB data provider.

Oracle, Microsoft, and third-party vendors all provide .NET data providers optimized for Oracle databases. Both Oracle and Microsoft provide their own Oracle data providers free of charge. Microsoft's provider for .NET Framework is obsolete. Oracle continues to support and develop its .NET data provider, Oracle Data Provider for .NET (ODP.NET). In this article, we will use ODP.NET, which is included in Oracle Database or is available for download separately.

ODP.NET provides standard ADO.NET data access, as well as features specific to Oracle databases, such as XML DB, data access performance optimization, and real application cluster load balancing and fast connection failover. The latest ODP.NET version (11.2) supports connecting to Oracle Database 9i version 2 servers and later. The database server can be located on any other operating system platform supported by Windows, Linux, UNIX, or Oracle databases.

After you install the ODP.NET and Oracle client software, you can begin application development using Visual Studio. Please confirm the client connectivity before starting the development. If you are able to connect to the Oracle database using Oracle client software, such as SQL*Plus, on the computer where Visual Studio is installed, you have installed and configured the Oracle client software correctly.

If you are new to Oracle, see the "installing .NET products" section of the Oracle Database two-day Express developer's Guide, which is devoted to background information on installing and configuring ODP.NET, or see the Oracle database document library for general information about Oracle databases.

Create a project in Visual Studio 2010

Now let's create an ODP.NET application for retrieving data from the Oracle database. Then we will learn how to use ODP.NET to perform error handling and how to handle other data retrieval situations.

After starting Visual Studio, the first task is to create a project. You can click New Project as shown below, or select File | New | Project.

Figure 1 create a new project in Visual Studio 2010

The New Project dialog box appears. Under Installed Templates on the left side of the dialog box, select your programming language. In this example, we choose Visual Basic. In the middle of the dialog box, select a project template. For simplicity, we chose Windows Forms Application.

Figure 2 using the New Project dialog box

You will need to specify meaningful names for the project name (we use OraWinApp) and the solution name (we use OraWinApp). A solution contains one or more projects. When a solution contains only one project, many people use the same name for both. Notice that there is a drop-down list box in the dialog box where you can specify the version of .NET Framework you want to target. If you are writing an application for an earlier version of .NET Framework, you should select the appropriate version from the drop-down list. Click the OK button to continue.

Add referenc

Because our project must connect to the Oracle database, we must add a reference to the ODP.NET DLL that contains the selected data provider. In the Solution Explorer to the right of Visual Studio, select the project name, right-click and select Add Reference. Alternatively, you can go to the menu bar and select Project, and then select Add Reference.

Figure 3 adding references

The Add Reference dialog box appears. Select the .NET tab. Visual Studio will construct a list of .NET components that can be added to the project. This may take a few seconds. When you are finished, you can click the Component Name column to arrange the list of components alphabetically.

Figure 4 Select the ODP.NET managed data provider for .NET 4

ODP.NET is located under the name of the Oracle.DataAccess component. Select Oracle.DataAccess from the list, and then click the OK button to let the project know about the ODP.NET data provider. Be sure to choose the right version. Since this is a .NET 4 project, you chose version 4.112.2.0 of Oracle.DataAccess here, but you can choose any version of ODP.NET that starts with 4.

Visual Basic/C# statement

After adding a reference, the standard practice is to add a Visual Basic Imports statement or a C # using statement. Technically, these statements are not required, but they allow you to reference database objects without using lengthy fully qualified names.

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

Imports Oracle.DataAccess.Client 'Visual Basic ODP.NET Oracle managed provider using Oracle.DataAccess.Client; / / C # ODP.NET Oracle managed provider

After adding the reference, Intellisense will help you add the Imports or using statement, as shown in figure 5.

Figure 5 adding Imports statements to Visual Basic

Connect strings and objects

The Oracle connection string and Oracle name resolution are inseparable. In this article, we will connect to Oracle's sample HR schema using the user ID "hr" and the password "hr". The tnsnames.ora file is an Oracle network configuration file that defines the database address used to establish a connection. Suppose we define a database alias OraDb in the tnsnames.ora file, as follows:

OraDb= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP) (HOST=ORASRVR) (PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=ORCL)

The OraDb alias defines the database address connection information for the client. 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=hr;Password=hr;" 'Visual Basic string oradb = "Data Source=OraDb;User Id=hr;Password=hr;"; / / C #

However, you can modify the connection string so that you do not need to use the tnsnames.ora file. Simply replace the alias with a statement that defines the alias in the tnsnames.ora file. The database aliases in this article are specific to my database settings. (click here for more information about creating database aliases for the database. )

'Visual Basic Dim oradb As String = "Data Source= (DESCRIPTION=" _ + "(ADDRESS= (PROTOCOL=TCP) (HOST=ORASRVR) (PORT=1521))" _ + "(CONNECT_DATA= (SERVICE_NAME=ORCL);" _ + "User Id=hr;Password=hr "/ / C # string oradb =" Data Source= (DESCRIPTION= "+" (ADDRESS= (PROTOCOL=TCP) (HOST=ORASRVR) (PORT=1521)) "+" (CONNECT_DATA= (SERVICE_NAME=ORCL); "+" User Id=hr;Password=hr; "

As you can see above, the user name and password are embedded in the connection string in clear text. This is the easiest way to create a connection string. However, from a security point of view, it is not advisable not to encrypt the text. In particular, you need to understand that compiled .NET application code is only slightly safer than source code files in the form of unencrypted text. You can easily decompile .NET DLL and EXE files to view the original content in unencrypted text. Encryption is actually the right solution, but this topic is a far cry from what we are talking about 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) 'Visual Basic OracleConnection 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. Other overloads of the constructor allow the following alternative syntax:

Dim conn As New OracleConnection () 'Visual Basic 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 () 'Visual Basic conn.Open (); / / C #

We will introduce error handling later.

Command object

The command object is used to specify the SQL command text-SQL string or stored procedure to execute. Similar to the connection object, it must complete the instantiation of its class from, and it has an overloaded constructor. In this example, ODP.NET will execute the SQL query in the departments table and return department_name with a department_id of 10.

Dim sql As String = "select department_name from departments where department_id = 10" 'Visual Basic Dim cmd As New OracleCommand (sql, conn) cmd.CommandType = CommandType.Text string sql = "select department_name from departments where department_id = 10"; / / C # OracleCommand cmd = new OracleCommand (sql, conn); cmd.CommandType = CommandType.Text

When using different overloads, the structure of the syntax is slightly different. The command object has methods for executing the command text, which we'll talk about in the next section. Different methods apply to different types of SQL commands.

Retrieve scalar values

Retrieving data from a database can be achieved by instantiating an OracleDataReader object and using OracleCommand's ExecuteReader method, which returns an OracleDataReader object. The returned data can be accessed by passing the column name or zero-based column sequence number to OracleDataReader.

Dim dr As OracleDataReader = cmd.ExecuteReader () 'Visual Basic dr.Read () Label1.Text = dr.Item ("department_name")' retrieve by column name Label1.Text = dr.Item (0) 'retrieve the first column in the select list Label1.Text = dr.GetString (0)' return a .NET data type Label1.Text = dr.GetOracleString (0) 'return an Oracle data type

C # developers must use access program type methods to retrieve data. There are appropriate types of accessors for returning .NET local data types, and other accesses are used to return local Oracle data types, all of which are supported by C #, Visual Basic, or any other .NET language. The zero-based sequence number is passed to the access program to specify the column to be returned.

OracleDataReader dr = cmd.ExecuteReader (); / / C # dr.Read (); label1.Text = dr ["department_name"] .ToString (); / / C # retrieve by column name label1.Text = dr.GetString (0). ToString (); / / return a .NET data type label1.Text = dr.GetOracleString (0). ToString (); / / return an Oracle data type

In this simplified example, the return value of department_name is a string that is used to set the property value (also a string) of the text of the label control. However, if you are retrieving department_id (not a string), there will be a data type mismatch. When the source data type does not match the target data type, the. NET runtime attempts to implicitly convert the data type Sometimes if the data types are not compatible, the implicit conversion will fail and an exception will be thrown. But even if implicit conversion is possible, it is still better to use explicit data type conversion than implicit data type conversion.

The explicit conversion to an integer is shown as follows:

Label1.Text = CStr (dr.Item ("department_id")) 'Visual Basic integer to string cast

In implicit conversion, C # is not as fault tolerant as Visual Basic. You must perform an explicit conversion yourself:

Label1.Text = dr.GetInt16 ("department_id"). ToString (); / / C #

You can explicitly convert scalar values and arrays.

Close and Dispose

You can call the connection object's Close method or the Dispose method to close the connection to the database. The Dispose method implicitly calls the Close method.

Conn.Close () 'Visual Basic conn.Dispose ()' Visual Basic conn.Close (); / / C # conn.Dispose (); / / C #

If you use the Using keyword of VB or the using keyword of C #, you do not have to explicitly call Close or Dispose.

Using (OracleConnection conn = new OracleConnection (oradb)) / / C # {conn.Open (); OracleCommand cmd = new OracleCommand (); cmd.Connection = conn; cmd.CommandText = "select department_name from departments where department_id = 10"; cmd.CommandType = CommandType.Text; OracleDataReader dr = cmd.ExecuteReader (); dr.Read (); label1.Text = dr.GetString (0);}

In addition, OracleCommand includes Dispose methods; OracleDataReader includes Close methods and Dispose methods. Closing and deleting .NET objects frees up system resources, ensuring efficient application performance, which is especially important in high load situations. You can experiment with some of the concepts you learned in Operation 1 (retrieving data from the database) and Operation 2 (increasing interactivity).

Error handling

When an error occurs, the. NET application should handle the error appropriately and notify the user with a meaningful message Error handling of Try-Catch-Finally structures is part of the .NET language. The following is a relatively minimal example of using the Try-Catch-Finally syntax:

'Visual Basic Try conn.Open () Dim cmd As New OracleCommand cmd.Connection = conn cmd.CommandText = "select department_name from departments" _ + "where department_id =" + TextBox1.Text cmd.CommandType = CommandType.Text If dr.Read () Then Label1.Text = dr.Item ("department_name")' or use dr.Item (0) End If Catch ex As Exception' Catches any error MessageBox.Show (ex.Message.ToString ()) Finally'In a real application Put cleanup code here. End Try / / C # try {conn.Open (); OracleCommand cmd = new OracleCommand (); cmd.Connection = conn; cmd.CommandText = "select department_name from departments where department_id =" + textBox1.Text; cmd.CommandType = CommandType.Text; if (dr.Read ()) / / C # {label1.Text = dr ["department_name"] .ToString () / / or use dr.GetOracleString (0). ToString ()}} catch (Exception ex) / / catches any error {MessageBox.Show (ex.Message.ToString ());} finally {/ / In a real application, put cleanup code here. }

Although this method will properly catch any errors that occur when trying to get data from the database, it is not user-friendly. For example, consider the following message that appears when the database is not available:

Figure 6 captures ORA-12545 errors and displays them to the user

Oracle DBA or developers are well aware of the meaning of ORA-12545, but end users are not. A better solution is to add an additional Catch statement to catch the most common database errors and display user-friendly messages.

'Visual Basic Catch ex As OracleException 'catches only Oracle errors Select Case ex.Number Case 1 MessageBox.Show ("Error attempting to insert duplicate data.") Case 12545 MessageBox.Show ("The database is unavailable.") Case Else MessageBox.Show ("Database error:" + ex.Message.ToString ()) End Select Catch ex As Exception 'catches any error MessageBox.Show (ex.Message.ToString ()) / / C # 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 not previously caught {MessageBox.Show (ex.Message.ToString ());}

Notice the two Catch statements in the code example above. If no Oracle errors are caught, the first Catch statement branch is skipped and the second Catch statement is used to catch any other non-Oracle errors. In the code, Catch statements should be sorted in order from special to general. After executing the user-friendly exception handling code, the ORA-12545 error message is displayed as follows:

Figure 7 user-friendly error messages for ORA-12545 errors

The Finally code block always executes whether or not an error occurs. The cleanup code should be included in this code block. If you are not using Using or using, you should clear connections and other objects in the Finally code block.

Using DataReader to retrieve multiple values

So far, our example only shows how to retrieve a single value. OracleDataReader can retrieve values for multiple columns and rows. Start with a multi-column, single-row query:

Select department_id, department_name, location_id from departments where department_id = 10

For the sake of brevity in this article, we use a table, the departments table. To get the value of a column, you can use a zero-based ordinal number or column name. The sequence number is related to the order in the query. Therefore, you can retrieve the value of the location_id column in Visual Basic by using dr.Item (2) or dr.Item ("location_id").

Here is the code snippet that concatenates department_name with the location_id column from the previous query:

Label1.Text = "The" + dr.Item ("department_name") + "department is in" _ + dr.Item ("location_id")'VB label1.Text = "The" + dr ["department_name"] .ToString () + "department is in" + dr ["location_id"] .ToString (); / / C #

Now let's do a query that returns multiple rows:

Select department_id, department_name, location_id from departments

To handle multiple rows returned from OracleDataReader, you need some type of loop structure. In addition, you need a control that can display multiple rows. OracleDataReader is a forward-only read-only cursor, so it cannot be bundled with updatable or fully scrollable controls, such as Windows Forms DataGrid controls. OracleDataReader is compatible with the ListBox control, as shown in the following code snippet:

While dr.Read () 'Visual Basic ListBox1.Items.Add ("The" + dr.Item ("department_name") _ + "department is in" + dr.Item ("location_id") End While while (dr.Read ()) / / C # {listBox1.Items.Add ("The" + dr ["department_name"]. ToString () + "department is in" + Dr ["location_id"] .ToString () }

Operation 3 (retrieving multiple columns and rows using OracleDataReader) focuses on some of these concepts.

Build and run on Windows x64

When running Visual Studio 2010 on a Windows x64 operating system, you can use Configuration Manager to change the target platform type. Select the solution in Solution Explorer, right-click and select Configuration Manager.

Figure 8 when building on Windows x64, you can use Configuration Manager to change the target platform type.

Summary

This paper introduces the process of accessing Oracle database using .NET programming language. You should now be able to connect to the database and retrieve multiple columns and rows.

Computer operation 1: retrieve data from the database

The prerequisite is that you have created a project and added a reference (as described earlier in this article).

Then add a button control and a label control to the Windows form. Be sure to leave space above these controls so that other controls can be added in Operation 2.

Figure 9 form containing buttons and label controls (computer operation 1)

Add code that retrieves data from the Oracle database and displays the results on the form. Place the code in the click event handler for 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.

Figure 10 Click the event handler stub.

Add a Visual Basic Imports statement before the Public Class declaration, or a C # using statement before the namespace declaration.

Imports Oracle.DataAccess.Client 'Visual Basic, ODP.NET Oracle managed provider

Using Oracle.DataAccess.Client; / / C#, ODP.NET Oracle managed provider

Add the Visual Basic version of the click event handler code between the Private Sub and End Sub statements (be sure to replace ORASRVR with the hostname of your server and replace the service name entry with the service name of the database):

Add the following C# code between the {and} curly braces in the button click event handler (be sure to replace ORASRVR with your server's hostname and replace the service name entry with the database's service name):

String oradb = "Data Source= (DESCRIPTION= (ADDRESS_LIST=")

+ "ADDRESS= (PROTOCOL=TCP) (HOST=ORASRVR) (PORT=1521))"

+ "(CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=ORCL));

+ "User Id=hr;Password=hr;"

OracleConnection conn = new OracleConnection (oradb); / / C #

Conn.Open ()

OracleCommand cmd = new OracleCommand ()

Cmd.Connection = conn

Cmd.CommandText =

"select department_name from departments where department_id = 10"

Cmd.CommandType = CommandType.Text

OracleDataReader dr = cmd.ExecuteReader ()

Dr.Read (); / / replace this statement in next lab

Label1.Text = dr ["department_name"] .ToString (); / / remove in next lab

Dr.Dispose ()

Cmd.Dispose ()

Conn.Dispose ()

Dim oradb As String = "Data Source= (DESCRIPTION= (ADDRESS_LIST=" _)

+ "(ADDRESS= (PROTOCOL=TCP) (HOST=ORASRVR) (PORT=1521))" _

+ "(CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=ORCL));" _

+ "User Id=hr;Password=hr;"

Dim conn As New OracleConnection (oradb) 'Visual Basic

Conn.Open ()

Dim cmd As New OracleCommand

Cmd.Connection = conn

Cmd.CommandText = _

"select department_name from departments where department_id = 10"

Cmd.CommandType = CommandType.Text

Dim dr As OracleDataReader = cmd.ExecuteReader ()

Dr.Read () 'replace this statement in next lab

Label1.Text = dr.Item ("department_name")'or dr.Item (0), remove in next lab

Dr.Dispose ()

Cmd.Dispose ()

Conn.Dispose ()

Run the application. Click the button. You will see the following:

Figure 11 successfully retrieved data

Computer operation 2: increase interactivity

Now that the basic functions of database access are implemented in the code, the next step is to increase interactivity for the application. Instead of running a hard-coded query, you can add a text box control to accept the department number (that is, department_id) entered by the user.

Add a text box control and another label control to the form (shown below): set the text property of the Label2 control to "Enter department_id:" and make sure that the Text property of TextBox1 is not set.

Figure 12 A form containing buttons and label controls (computer operation 2)

Modify the code that defines the select string:

Cmd.CommandText = _

"select department_name from departments where department_id =" _

+ TextBox1.Text'VB

Cmd.CommandText = "select department_name from departments where department_id =" + textBox1.Text; / / C #

Run the application. Enter 10 in department_id to test the application. Enter an invalid department_id (for example, 12) to retest the application. The application will be terminated.

Figure 13 unhandled exceptions

Modify the code to prevent errors when entering an invalid department_id. Let's recall that the ExecuteReader method actually returns an object. Replace the line of code that contains dr.Read with all of the following statements.

Enter a department_id number that does not exist to test the application. Now the application is no longer terminated. Enter the letter An instead of the number, and then click the button. The application terminates. Obviously, our application needs a better way to handle errors.

One might point out that the application should not allow the user to make invalid input that causes the error, but fundamentally the application must add robust error handling. Not all errors are preventable, so you must have error handling.

If dr.Read () Then 'Visual Basic

Label1.Text = dr.Item ("department_id") .ToString

Else

Label1.Text = "department_id not found"

End If

If (dr.Read ()) / / C #

{

Label1.Text = dr ["department_id"] .ToString ()

}

Else

{

Label1.Text = "department_id not found"

}

、

Operation 3: use OracleDataReader to retrieve multiple columns and rows

Now that you have retrieved a single value, the next step is to use OracleDataReader to retrieve multiple columns and rows. Add a ListBox control to the form to display the results.

Add a ListBox control to the form. Resize the control to fill most of the width of the form (shown below).

Figure 14 adds the form of ListBox

Remove the where clause from the query and add the following:

Cmd.CommandText = _

"select department_id, department_name, location_id from departments"'VB

Cmd.CommandText = "select department_id, department_name, location_id from departments"; / / C #

The query results will be read in a while loop and the ListBox control will be populated. Modify the Visual Basic code as follows, and modify the host and service name accordingly for your database:

Dim oradb As String = "Data Source= (DESCRIPTION= (ADDRESS_LIST=" _)

+ "(ADDRESS= (PROTOCOL=TCP) (HOST=ORASRVR) (PORT=1521))" _

+ "(CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=ORCL));" _

+ "User Id=hr;Password=hr;"

Dim conn As New OracleConnection (oradb) 'Visual Basic

Conn.Open ()

Dim cmd As New OracleCommand

Cmd.Connection = conn

Cmd.CommandText = _

"select department_id, department_name, location_id from departments"'VB

Cmd.CommandType = CommandType.Text

Dim dr As OracleDataReader = cmd.ExecuteReader ()

While dr.Read ()

ListBox1.Items.Add ("The" + dr.Item ("department_name") + _

"department is in" + dr.Item ("location_id") .ToString ()

End While

Dr.Dispose ()

Cmd.Dispose ()

Conn.Dispose ()

Modify your C# code to look like this:

String oradb = "Data Source= (DESCRIPTION= (ADDRESS_LIST=")

+ "ADDRESS= (PROTOCOL=TCP) (HOST=ORASRVR) (PORT=1521))"

+ "(CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=ORCL));

+ "User Id=hr;Password=hr;"

OracleConnection conn = new OracleConnection (oradb); / / C #

Conn.Open ()

OracleCommand cmd = new OracleCommand ()

Cmd.Connection = conn

Cmd.CommandText = "select department_id, department_name, location_id from departments.168"

Cmd.CommandType = CommandType.Text

OracleDataReader dr = cmd.ExecuteReader ()

While (dr.Read ())

{

ListBox1.Items.Add ("The" + dr ["department_name"] .ToString () +

"department is in" + dr ["location_id"] .ToString ()

}

Dr.Dispose ()

Cmd.Dispose ()

Conn.Dispose ()

Run the application. The ListBox should populate all department names and locations in the departments table. Error handling is already available in the downloadable code.

This is how to use VS2010 to build .NET applications on Oracle databases. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.