dotnetwire.com
  • HOME
  • SITEINFO
  • MEMBERSHIP
  • FAQ
  • PRIVACY
  • TERMS
  • LINKS

welcome to dotnetwire.com



Web Services Development in a Class Library Project

The recent growth of the Microsoft’s .NET Framework popularity calls for the information on the integration of .NET application with Oracle databases. The developers require not just information on basic connectivity – they need to know how to build effective and efficient solutions using such development tools as Visual Studio .NET (VS.NET).

Here we will discuss some basic but still very important processes that take place while building .NET applications on Oracle databases. These processes include:
  • Web-services bring new opportunities to the business by enabling to communicate with customers and partners in a more effective way
  • Web-services deliver the users more personal, integrated experiences using the smart devices – including PCs
  • Web-services help to reduce the development time for new projects
  • The businesses’ Web-services are easily made accessible to its customers thus increasing the profits

The newly gained knowledge can than be applied in three practice labs. The difficulty of the labs is growing from quite simple to more and more complex.

Security-related information and labs can be found in my another article "Securing a .NET Application on the Oracle Database." (Some other issues related to application life cycle are discussed in the series of technical articles titled "Mastering .NET Application Development with Oracle").

It should be noted that the Developer Tools for .NET product are available for free download from OTN. This is a Visual Studio .NET add-in that helps to male the process of development of .NET applications on Oracle easier and more intuitive. We shall not discuss this add-in here, however, you can turn to Oracle Developer Tools for .NET Product Center for detailed information.



.NET Data Provider

Along with basic Oracle client software a specific layer is required between .NET application code and the Oracle client software. This is generally known as a managed data provider (here the term “managed” refers to the application code that is managed by the .NET Framework). The fact is the generic .NET OLE DB data provider is in most cases not as effective as the data provider optimized for the database platform currently in use.

There exists a wide range of Oracle-optimized data providers from Microsoft, Oracle and third-party vendors. Data providers from Microsoft and Oracle are free (one from Microsoft is included in the .NET v.1.1 Framework and no additional installation is required). Some of the data providers offered by third-party vendors may support older versions of Oracle or may not require the installation of Oracle client connectivity software. The material of this article is based on the usage of the Oracle Data Provider for .NET (ODP.NET) that is to be downloaded separately.
The development process can start as soon as the ODP.NET and the necessary Oracle client connectivity software are installed. Client connectivity can be checked before starting the development – a good way to organize the test is to try connecting to Oracle using SQL*Plus on the machine, where VS.NET is installed. If the test is successful, the Oracle client software is ready to be used.

Those who are new to Oracle may find it useful to turn to Oracle Data Provider for .NET Developer's Guide 10g (section "Connecting to the Oracle Database") to get more information on using ODP.NET. General information of Oracle Databases may be found in Oracle Database Administrator's Guide 10g. There also exists a sample code document, titled "Connect to an Oracle Database Using ODP.NET".

New Project creation in the VS.NET

First thing you need to do after you have started Visual Studio .NET is create a new project. The two ways of doing it are to click the New Project button or to select File | New | Project... (see picture below).



The dialog box titled New Project will appear. The left side of the dialog (under Project Types) is intended for choosing the programming language. Here we have chosen VB.NET. The right side lets you choose the project template (under Templates). To simplify the example we’ve chosen Windows Applications.



Meaningful names are to be specified for the project name and the solution name (here OtnWinApp and OtnSamples correspondingly). For solutions containing only one project it is very common to use the same names for both.

Add a Reference

It is very important that the reference is added to the dll, containing the data provider we’ve chosen, for the project is going to connect to the Oracle database. This can be done using Solution Explorer. After selecting the Reference node right click and select Add Reference. Another way is to go to the menu bar Project and then select Add Reference.



This will bring up the Add Reference dialog box.



Here the Oracle.DataAccess.dll is to be selected from the list. After that click the Select button, and than click the OK button. Now the ODP.NET data provider is known to your project.



VB.NET/C# Statements

The commonly accepted order of actions is to add J# import statements, VB.NET Imports statements or C# using statements after the references have been added. These statements are not required; still they make the development process easier allowing you to refer to the database objects without using lengthy, fully qualified names.

The standard practice is to put these statements 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

Oracle connection strings cannot be separated from Oracle names resolution. For example, there may exist a database alias of OraDb, and it is defined in the tnsnames.ora file in the following way: 

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

The syntax for using the OraDb alias defined as shown above will be as follows: 

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#

 

The need for using the tnsnames.ora file may be avoided by modifying the connection string. All you need to do is replace the name of the alias with its definition in the 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;";


The example above shows that the username and the password are embedded into the connection string in the form of clear text, which represents the easiest approach to creating connection strings. Still the clear text approach is undesirable for security reasons. The developer is to understand that the compiled code of the .NET application is not much more secure than the plain text source code of the same application, for the .NET dll and .exe files can easily be decompiled and viewed in the form of source code. (Encryption is a very good idea here; still, this issue is not discussed within this article). The next step is the instantiation of a connection object from the connection class. After that the connection string is to be associated with the string object. 

Dim conn As New OracleConnection(oradb) ' VB.NET
 
OracleConnection conn = new OracleConnection(oradb); // C#


It should be noted that the connection string is associated with the connection object by being passed through the object's constructor which is overloaded. This allows for another syntax (using the constructor’s other overload): 

Dim conn As New OracleConnection() ' VB.NET
conn.ConnectionString = oradb
 
OracleConnection conn = new OracleConnection(); // C#
conn.ConnectionString = oradb;


When the connation string is associated with the connection object the actual connection may be established using the Open method:

conn.Open() ' VB.NET conn.Open(); // C#

Error handling will be discussed further.

Command Object

Use the Command object for specifying the SQL command text that is to be executed (this may be an SQL string or a stored procedure). Just like the above discussed Connection object the Command object is to be instantiated from its class and it also has the 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 usage of other overloads allows for the different syntax structure. The command text can be executed using the Command object’s methods (different methods exist for different kinds of SQL commands).

Retrieval of a Scalar Value

A DataReader object is to be instantiated and the ExecuteReader method is to be used for retrieving data form the database. The ExecuteReader method returns the OracleDataReader object. The data returned is available for access by passing either the column name or zero-based column ordinal to the Item property. The accessor type methods can also be used to obtain 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


Those using C# are to use accessor type methods to retrieve data. There exist typed accessors for retrieving .NET native data types and other accessors which allow for the retrieval of Oracle native data types. The column to be returned is specified by passing zero-based ordinals to the accessors. 

OracleDataReader dr = cmd.ExecuteReader(); // C#
dr.Read();
label1.Text = dr.GetString(0); // C# retrieve the first column in the select list


In the example above the value returned (dname) is of String type. This value is than used to set the Text property of the control’s label, which is also a string value. Here the data type is String in both cases and data mismatch does not occur. But in case if we had retrieved deptno, which is not a string, we would face a data type mismatch. In such cases the .NET runtime attempts to perform implicit data type conversion. Sometimes the data types are incompatible which results in conversion failure and an exception is returned. Still, even though implicit data type conversion works in many cases, explicit conversion is a preferable variant. Explicit conversion is organized as follows: 

Label1.Text = CStr(dr.Item("deptno")) ' VB.NET integer to string cast

C# has a more strict approach to implicit conversions, so, explicit conversions are required:

string deptno = dr.GetInt16("deptno").ToString(); // C#

Scalar values and arrays can also be explicitly casted.


Closing and Disposing

The connection to the database is to be closed by using either the Close or the Dispose method of the Connection object. The Dispose method calls the Close method. 

conn.Close() ' VB.NET
conn.Dispose() ' VB.NET
 
conn.Close(); // C#
conn.Dispose(); // C#


In C# there exists a special mechanism that initiates the closure of the connection once it goes out of scope. The feature is enabled by the using keyword. 

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);
}


Error Handling

.NET programming languages imply the Try-Catch-Finally structured error handling. The Try-Catch-Finally syntax is provided below in simplified form: 

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();
}


This mechanism is quite effective in capturing the errors while retrieving data from the database, yet it is not end-user friendly. The picture below illustrates the situation:



The ORA-12545 is very informative for the developer or Database Administrator, still it is not the same for the end-user. The solution here may be to add an additional Catch statement to capture the most common database errors and provide the end-user with understandable 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());
}


The Close or Dispose method of the Connection object is usually placed into the Finally code block which is executed regardless of if any errors occurred or not. Such placement guarantees that the database connection is always closed after the execution of the Try-Catch-Finally code block. It should be noted that attempting to close the database connection that is not open does not lead to an error, more than that, there can be superfluous calls of Close and Dispose methods – there will still be no error.

Multiple Values Retrieval Using a DataReader

DataReader is capable of retrieving data from multiple rows and columns (all the above examples only showed how to obtain single values). The following example illustrates the “single row – multiple column” query:

select deptno, dname, loc from dept where deptno = 10

We can use zero-based ordinals as well as column names to retrieve column values. The column’s loc value in VB.NET may be retrieved by both dr.Item(2) and dr.Item("loc"), for ordinals are relative to the order in the query. The example below shows how values of the columns may be concatenated (here we use the dname and loc values 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#


Let us turn to the query returning multiple rows:

select deptno, dname, loc from dept

Some kind of a looping construction is required for processing of the multiple rows returned by the DataReader. Again, DataReader is a forward-only and read-only cursor, so, fully scrollable controls (such as Windows Forms DataGrid) cannot be used here. DataReader is compatible with the ListBox control. The usage of this control is illustrated in the following example: 

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);
}
 

Conclusion

In this article we have discussed the basics of accessing the Oracle databases using the programming languages included in Visual Studio .NET. This knowledge should be enough to connect to the databases and retrieve multiple rows and columns.


© Copyright 2000-2008, Microsoft .NET Article Directory
For any site inquiries contact webmaster.