|
Developer: .NET
Build a .NET Application on the Oracle Database
by John Paul Cook
Learn the basic yet essential processes involved in building a .NET application that uses an Oracle database.
Updated October 2005
With the rising popularity of Microsoft's .NET Framework, many developers are hungry for information about the best means of integrating .NET applications with Oracle—not only in terms of basic connectivity, but also in relationship to effective and efficient application development using Visual Studio.NET (VS.NET).
In this article, I'll explain the basic yet essential processes involved in building a .NET application that uses an Oracle database, including:
- How to add project references to support Oracle class libraries in your .NET project
- How to create Oracle Database connection strings
- How to work with Connection, Command, and DataReader objects
You will have the opportunity to apply what you have learned in three practice labs, ranging in difficulty from the relatively simple to the more complex.
For information and labs about how to secure your application, see my article "Securing a .NET Application on the Oracle Database." (Also, see the entire "Mastering .NET Application Development with Oracle" series for technical articles covering a range of application life cycle issues.)
Note that the free Oracle Developer Tools for .NET, available for download from OTN, provides a Visual Studio .NET add-in that makes the development of .NET apps on Oracle much easier and more intuitive. That subject is beyond our scope here, however; see the Oracle Developer Tools for .NET Product Center for more information.
.NET Data Provider
In addition to basic Oracle client connectivity software, .NET applications require the use of what is known as a managed data provider (where "managed" refers to code managed by the .NET framework). The data provider is the layer between the .NET application code and the Oracle client connectivity software. In almost every case, the best performance is achieved by using a provider optimized for a specific database platform instead of the generic .NET OLE DB data provider.
Oracle, Microsoft, and third-party vendors all offer data providers optimized for Oracle. Oracle and Microsoft make their Oracle data providers available for free. (Microsoft's provider for version 1.1 of the .NET framework is included in the framework and no separate download or installation is required.) Some third-party data providers support older versions of Oracle or do not require the installation of Oracle client software. In this article, we will assume the use of the Oracle Data Provider for .NET (ODP.NET), which is available as a separate download.
When ODP.NET and any required Oracle client connectivity software is installed, application development using Visual Studio.NET can begin. It's a good idea to confirm client connectivity before starting development. If you can connect to Oracle using SQL*Plus on the same machine as VS.NET, then you know that your Oracle client-side software is properly installed and configured.
If you are new to Oracle, see the section "Connecting to the Oracle Database" in the Oracle Data Provider for .NET Developer's Guide 10g for background information regarding ODP.NET specifically, or to the Oracle Database Administrator's Guide 10g for information about managing the Oracle Database generally. You can also consult the "Connect to an Oracle Database Using ODP.NET" sample code "how-to" document.
Creating a Project in Visual Studio.NET
After starting VS.NET, the first task is to create a project. You can either click the New Project button or select File | New | Project... as shown below.
|
| Figure 1: Creating a new project in Visual Studio.NET |
A New Project dialog box appears. On the left side of the dialog box under Project Types, select the programming language of your choice. In our example, VB.NET was chosen. On the right side under Templates, choose a project template. To keep things simple, a Windows Application is selected here.
|
| Figure 2: Using the New Project dialog |
You'll want to specify meaningful names for the project name (we used OtnWinApp) and the solution name (we used OtnSamples). A solution contains one or more projects. When a solution contains only one project, many people use the same name for both.
Adding a Reference
Because our project must connect to an Oracle database, it is necessary to add a reference to the dll containing the data provider of our choice. Within the 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.
|
| Figure 3: Adding a reference |
The Add Reference dialog box appears.
|
| Figure 4: Selecting the ODP.NET Managed Data Provider |
Select the Oracle.DataAccess.dll from the list, then click the Select button, and finally click the OK button to make the ODP.NET data provider known to your project.
|
| Figure 5: Solution Explorer after selecting the Oracle Managed Provider |
VB.NET/C# Statements
After adding references, it is standard practice to add VB.NET Imports statements, C# using statements, or J# import statements. Technically these statements are not required but they do allow you to refer to database objects without using lengthy, fully qualified names.
By convention, these statements appear at or near the top of a code file, before the namespace or class declaration.
Imports System.Data ' VB.NET
Imports Oracle.DataAccess.Client ' ODP.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 Strings and Objects
An Oracle connection string is inseparable from Oracle names resolution. Suppose we had a database alias of OraDb defined in a tnsnames.ora file as follows:
OraDb=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=OTNSRVR)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=ORCL)
)
)
To use the OraDb alias defined in the tnsnames.ora file shown above, you would 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#
You can modify the connection string to obviate the need for the tnsnames.ora file, however. Simply replace the name of the alias with how it would be defined in a tnsnames.ora file.
' VB.NET
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;"
string oradb = "Data Source=(DESCRIPTION=" // C#
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=OTNSRVR)(PORT=1521)))"
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));"
+ "User Id=scott;Password=tiger;";
As you can see above, the username and password are embedded in the connection string in clear text. This is the simplest approach to creating a connection string. However, the clear text approach is undesirable from a security perspective. In particular, you must understand that compiled .NET application code is only marginally more secure than the clear text source code files. It is very easy to decompile .NET dll and exe files and view the original clear text contents. (Encryption is in fact the appropriate solution, but that subject would be a quite lengthy digression 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#
Notice that the connection string is associated with the connection object by being passed through the object's constructor, which is overloaded. The constructor's other overload allows the following alternative syntax:
Dim conn As New OracleConnection() ' VB.NET
conn.ConnectionString = oradb
OracleConnection conn = new OracleConnection(); // C#
conn.ConnectionString = oradb;
After associating a connection string with a connection object, use the Open method to make the actual connection.
conn.Open() ' VB.NET
conn.Open(); // C#
We'll cover error handling later.
Command Object
The Command object is used to specify the SQL command text that is executed, either a SQL string or a stored procedure. Similar to the Connection object, it must be instantiated from 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;
Using different overloads, the syntax can be structured slightly differently. The Command object has methods for executing the command text. Different methods are appropriate for different types of SQL commands.
Retrieving a Scalar Value
Retrieving data from the database can be accomplished by instantiating a DataReader object and using the ExecuteReader method, which returns an OracleDataReader object. VB.NET developers can access returned data by passing either the column name or zero-based column ordinal to the Item property. Another option is to use accessor type methods 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 accessor type methods for retrieving data. There are typed accessors for returning .NET native data types and others for returning native Oracle data types. Zero-based ordinals are passed to the accessors 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 returned value of dname is a string and is used to set the value of the label control's text property, which is also a string. But if deptno, which is not a string, had been retrieved instead, there would be a data type mismatch. The .NET runtime attempts to implicitly convert from one data type to another when the source and destination data types don't match. Sometimes the data types are incompatible and the implicit conversion fails, throwing an exception. But even when it works, it's still better to use explicit data type conversions instead of implicit data type conversion.
An explicit cast to integer is shown below:
Label1.Text = CStr(dr.Item("deptno")) ' VB.NET integer to string cast
C# is not as forgiving as VB.NET on implicit conversions. You'll find yourself doing explicit conversions:
string deptno = dr.GetInt16("deptno").ToString(); // C#
You can explicitly cast scalar values as well as arrays.
Close and Dispose
Either the connection object's Close or the Dispose method should be called 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#
Alternatively, C# offers a special syntax that automatically disposes a connection when it goes out of scope. The using keyword enables 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 some of the concepts we've learned here in Lab 1 (Retrieving Data from the Database) and Lab 2 (Adding Interactivity).
Error Handling
Try-Catch-Finally structured error handling is a part of .NET languages. Here is a relatively minimalist example of 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 approach will gracefully capture any errors in attempting to get data from the database, it is not user friendly. For example, look at the following message displayed when the database is unavailable:
|
| Figure 6: An ORA-12545 error caught and displayed to the user. |
An ORA-12545 is quite meaningful for an Oracle DBA or developer, but not for an end user. A better solution is to add an additional Catch statement to trap for the most common database errors and provide user-friendly messages.
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())
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());
}
Notice the two Catch statements in the code sample above. If there aren't any Oracle errors to catch, the first Catch statement branch is skipped, leaving any other type of error to be caught by the second Catch statement. Catch statements must be ordered in the code from most specific to most general. After implementing the user-friendly exception handling code, the ORA-12545 error message appears as follows:
|
| Figure 7: A user-friendly error message for an ORA-12545 error |
The Finally code block is always executed regardless of whether or not an error occurred. By placing the connection object's Close or Dispose method call in the Finally code block, the database connection will always be closed when after executing the Try-Catch-Finally code block. Attempting to close a database connection that is not open does not cause an error. For example, if the database is unavailable, the database connection is not opened, so the Finally code block attempts to close a connection that does not exist. It's irrelevent if a superfluous Close or Dispose is attempted. What matters is that placing a Close or Dispose method in the Finally code block guarantees that the connection is closed.
Retrieving Multiple Values Using a DataReader
So far our examples have only showed how to retrieve a single value. A DataReader can retrieve values for multiple columns and multiple rows. First consider a multiple column, single row query:
select deptno, dname, loc from dept where deptno = 10
To obtain the values of the columns, either zero-based ordinals or column names can be used. Ordinals are relative to the order in the query. Thus, the loc column's value can be retrieved in VB.NET by using either dr.Item(2) or dr.Item("loc").
Here is a code snippet that concatenates the dname and loc columns 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 consider a query that returns multiple rows:
select deptno, dname, loc from dept
To process multiple rows returned from a DataReader, some type of looping construct is needed. Furthermore, a control that can display multiple rows is desirable. A DataReader is a forward-only, read-only cursor, so it can't be bound to an updateable or fully scrollable control such as a Windows Forms DataGrid control. A DataReader is compatible with a ListBox control, as the following code snippet illustrates:
While dr.Read() ' VB.NET
ListBox1.Items.Add("The " + dr.Item(1) + " department is in " + dr.Item("loc")) End While
while (dr.Read()) // C#
{
listBox1.Items.Add("The " + dr.GetString(1) + " department is in " + dr.GetString(2);
}
Lab 3 (Retrieve Multiple Columns and Rows with a DataReader) highlights some of these concepts.
Summary
This article has introduced you to the process of accessing Oracle databases using VS.NET programming languages. You should now have the capability to connect to the database and retrieve multiple columns and rows.
John Paul Cook (johnpaulcook@email.com) is a database and .NET consultant based in Houston. He is the author of numerous 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 an Oracle certified DBA and a Microsoft MCSD for .NET.
|
Lab 1: Retrieving Data from the Database
- Begin by adding a button control and a label control to the Windows form. Be sure to leave room above the controls to allow additions that will be made in Lab 2.
|
| Figure 8: Lab 1 form with button and label controls |
- Add code to retrieve data from the Oracle database and display the results on the form. Put the code in a click event handler for the button. The easiest way to get started with this task is to double click the button because it will create a stub for the event handler.
|
| Figure 9: Click event handler stub. |
- Add VB.NET Imports statements before the Public Class declaration or C# using statements before the namespace declaration.
Imports System.Data ' VB.NET
Imports Oracle.DataAccess.Client ' ODP.NET Oracle managed provider
using System.Data; // C#
using Oracle.DataAccess.Client; // ODP.NET Oracle managed provider
- Add the VB.NET version of the click event handler code between the Private Sub and End Sub statements (be sure to replace OTNSRVR with your server's host name)::
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.NET
conn.Open()
Dim cmd As New OracleCommand
cmd.Connection = conn
cmd.CommandText = "select dname from dept where deptno = 10"
cmd.CommandType = CommandType.Text
Dim 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 click event handler between the { and }
curly braces for the button's click event handler (be sure to replace OTNSRVR with your server's host name):
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;";
OracleConnection conn = new OracleConnection(oradb); // C#
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);
conn.Dispose();
- Run the application. Click the button. You should see the following:
|
| Figure 10: Data successfully retrieved |
|
|
Lab 2: Adding Interactivity
Now that the basics of database access are implemented in the code, the next step is to add interactivity to the application. Instead of running the hard coded query, a textbox control can be added to accept a user input for the department number (deptno).
- Add a textbox control and another label control to the form as shown below. Set the text property of the Label2 control to Enter Deptno: and make sure that the Text property of TextBox1 isn't set to anything.
|
| Figure 11: Lab 2 form with button and label controls. |
- Modify the code that defines the select string:
cmd.CommandText = "select dname from dept where deptno = " + TextBox1.Text 'VB.NET
cmd.CommandText = "select dname from dept where deptno = " + textBox1.Text; // C#
- Run the application. Test the application by entering 10 for the deptno. Retest the application by entering an invalid deptno (e.g., 50). The application will abort.
|
| Figure 12: An unhandled exception |
- Modify your code to prevent an error when an invalid deptno is entered. 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 If
if (dr.Read()) // C#
{
label1.Text = dr.GetString(0);
}
Else
{
label1.Text = "deptno not found";
}
- Test the application by entering a deptno that does not exist. Now the application no longer aborts. Enter the letter A instead of a number and click the button. The application aborts. Clearly, our application needs a better approach to handling errors.
Although it could be argued that the application should not allow the user to make invalid inputs that would cause an error, ultimately the application must have robust error handling added. Not all errors are preventable, so error handling must be implemented.
|
|
Lab 3: Retrieve Multiple Columns and Rows with a DataReader
Now that a single value has been retrieved, the next step is to retrieve multiple columns and rows with a DataReader. A ListBox control is added 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 as shown below.
|
| Figure 13: Form with ListBox added |
- Remove the where clause from the query and add the additional columns:
cmd.CommandText = "select deptno, dname, loc from dept" ' VB.NET
cmd.CommandText = "select deptno, dname, loc from dept"; // C#
- Modify your VB.NET code to look like this:
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.NET
conn.Open()
Dim cmd As New OracleCommand
cmd.Connection = conn
cmd.CommandText = "select deptno, dname, loc from dept";
cmd.CommandType = CommandType.Text
Dim dr As OracleDataReader = cmd.ExecuteReader()
While dr.Read()
ListBox1.Items.Add("The " + dr.Item(1) + _
" department is in " + dr.Item("loc"))
End While
conn.Dispose()
Modify your C# code to look like this:
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;";
OracleConnection conn = new OracleConnection(oradb); // C#
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select deptno, dname, loc from dept";
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ListBox1.Items.Add("The " + dr.GetString(1) +
" department is in " + dr.GetString(2));
}
conn.Dispose();
The code downloads have error handling implemented.
|
| 
Bookmark
