Chapter 2: Connecting to Oracle
From this chapter on, we will start programming with ODP.NET. This chapter mainly concentrates on the following:
- Introducing the Provider-Independent Model in ADO.NET 2.0
- Working with .NET data providers
- Different ways to connect to Oracle database from ADO.NET 2.0
- Connection pooling, system privileged connection, Windows authentication
Provider-Independent Model in ADO.NET 2.0
ADO.NET internally works with .NET data providers (or .NET data bridge provider) to connect to and access data from different kinds of data sources (including databases). The same data provider model existing in ADO.NET 1.1 is further enhanced in ADO.NET 2.0 (with new factory classes) to leverage the flexibility of developing database-independent applications.
What exactly is a factory class? The purpose of a factory class is to provide an interface for creating families of related objects, with or without specifying their concrete (method implementation) classes. If the factory class is created without one or more implementations of methods, we call it as an abstract factory class.
The provider-independent programming model in ADO.NET 2.0 revolves around the classes in the System.Data.Common namespace. There are mainly two new factory classes that implement the provider-independent model (within the same namespace):
DbProviderFactories DbProviderFactory
Listing All Installed .NET Data Providers
Now, let us start our programming with listing all .NET data providers installed on your machine. All .NET data provider-related information gets listed in the machine.config file on your machine. Each provider is generally identified with its invariant name. The invariant name (in most cases) is the same as its namespace.
The following code gives out the list of all .NET data providers installed on your machine:
Imports System.Data.Common
Public Class Form1
Private Sub btnProviders_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnProviders.Click
Dim dt As DataTable = _
DbProviderFactories.GetFactoryClasses()
Me.DataGridView1.DataSource = dt
End Sub
End Class
Within the above code, the DbProviderFactories class is mainly used to enumerate all .NET data providers installed on your machine. Using the same class, we can also create instances related to a specific provider (to access databases specific to that provider). To list all the .NET data providers installed on your machine, we can use a GetFactoryClasses() method available in the DbProviderFactories class.
The highlighted line of code finds and lists all the .NET data providers installed on your machine (and populates them into a data table). When that code gets executed, the output should look similar to the following:
According to the preceding figure, you can see that the machine has six .NET data providers installed. The third column represents the invariant names to identify each of those providers.
Enumerating all Oracle Data Sources Available
In the previous section, we enumerated the list of all .NET data providers installed on the machine. In the previous screenshot, you should observe that the machine in this example has the Oracle Data Provider for .NET installed, which is identified with invariant name Oracle.DataAccess.Client.
In this section, we shall enumerate the list of all Oracle data sources available. Let us go through the following code first:
Imports System.Data.Common
Public Class Form2
Private Sub btnDataSources_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnDataSources.Click
Dim ProviderName As String = _
"Oracle.DataAccess.Client"
Dim fctry As DbProviderFactory = _
DbProviderFactories.GetFactory(ProviderName)
If (fctry.CanCreateDataSourceEnumerator) Then
Dim dsenum As DbDataSourceEnumerator = _
fctry.CreateDataSourceEnumerator()
Dim dt As DataTable = dsenum.GetDataSources()
Me.DataGridView1.DataSource = dt
Else
MessageBox.Show("No datasources found")
End If
End Sub
End Class
Let us go through the above code step by step.
The following is the statement that selects the ODP.NET data provider:
Dim ProviderName As String = "Oracle.DataAccess.Client"
The .NET data provider name is nothing but the invariant name available for the respective .NET data provider. In the previous screenshot, you can observe that there is a special column named InvariantName to identify the respective .NET data provider.
The following statement creates a factory instance of the data provider selected:
Dim fctry As DbProviderFactory = _
DbProviderFactories.GetFactory(ProviderName)
Once the factory instance is created, we need to determine whether the provider (or instance) supports enumerating of data sources or not. This is easily accomplished with the CanCreateDataSourceEnumerator() method (which returns a Boolean).
If the underlying .NET data provider supports enumerating the data sources, we can find and retrieve all the data sources for respective .NET data provider using the following code:
If (fctry.CanCreateDataSourceEnumerator) Then
Dim dsenum As DbDataSourceEnumerator = _
fctry.CreateDataSourceEnumerator()
Dim dt As DataTable = dsenum.GetDataSources()
Me.DataGridView1.DataSource = dt
Else
MessageBox.Show("No datasources found")
End If
The CreateDataSourceEnumerator() method simply creates an enumerator. The method GetDataSources() enumerates through all existing Oracle data sources.
When the above code gets executed, the output should look similar to the following:
Here, the XE is nothing but the name of the Oracle instance (SID) running on the system, which has Oracle 10g Express Edition installed.
So far we have just enumerated all the .NET data providers installed on our machine and the list of Oracle data sources. We haven't connected to an Oracle database yet in the preceding code.
Connecting to Oracle Databases from .NET
There are several ways to connect to Oracle database from within .NET. Each of those methods has its own pros and cons as described in Chapter 1. Now, we will explore the most popular methodologies to connect to Oracle database through .NET.
To connect to Oracle, we need to have proper connection descriptors configured on the system. This is usually taken care by the tnsnames.ora file. TNS stands for Transparent Network Substrate. It provides a uniform application interface to enable network applications to access the underlying network protocols. tnsnames.ora is simply a text file that provides SQL*Net with the Oracle server location and the necessary connection strings to connect to Oracle databases. This file always resides in the Oracle home's Network\Admin folder.
If the Oracle client (or SQL*Plus) is already able to connect to the Oracle database server, the tnsnames.ora file is already correctly configured and you need not disturb it. But, it is beneficial for you to look at the content of tnsnames.ora to have a better understanding of the connection descriptors. The following is an example entry available in the tnsnames.ora file on a machine to get connected to Oracle (yours could be different):
XE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = xe)
)
)
The above configuration script shows that the Oracle database server is available at 127.0.0.1 (local machine) and listening at port 1521. The service name (or SID) to connect to the server is xe. The whole description is assigned to a name XE.
We will make use of the above specification in most of the connection strings available in the examples.
Before building the connection strings, make sure that you configured and tested tnsnames.ora properly and can connect to the Oracle database. If you can already connect to the Oracle database server, you need not modify further. But you should know to which host you are going to connect. This is essential, as an Oracle client could be configured to connect to more than one Oracle database server simultaneously. You can also configure and test these connections using a graphical wizard, Net Configuration Assistant.
Connecting Using .NET Data Provider Factory Classes
The previous topic introduced .NET data provider factory classes and this section will use those classes to connect to an Oracle database.
The following code demonstrates how to connect to an Oracle database using the .NET data provider factory classes:
Imports System.Data.Common
Public Class Form3
Private Sub btnConnect_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnConnect.Click
Dim ProviderName As String = _
"Oracle.DataAccess.Client"
Dim fctry As DbProviderFactory = _
DbProviderFactories.GetFactory(ProviderName)
Dim Connection As Data.Common.DbConnection
Connection = fctry.CreateConnection
Connection.ConnectionString = _
"Data Source=xe;user id=scott;password=tiger"
Try
Connection.Open()
Connection.Close()
MessageBox.Show("Succesfully connected")
Catch ex As Exception
MessageBox.Show("Unable to connect. " & ex.Message)
End Try
End Sub
End Class
From the preceding code we have the following statements that are used to create a factory instance for the .NET data provider selected (in this case it is Oracle.DataAccess.Client).
Dim ProviderName As String = _
"Oracle.DataAccess.Client"
Dim fctry As DbProviderFactory = _
DbProviderFactories.GetFactory(ProviderName)
Further moving down, we have the following:
Dim Connection As Data.Common.DbConnection
Connection = fctry.CreateConnection
Data.Common.DbConnection can simply hold any type of database connection irrespective of the data source or data provider. To create a database connection object from the factory instance, we can make use of the CreateConnection() method, which in turn returns an object of the type Data.Common.DbConnection. Once the DbConnection object is created (for the respective .NET data provider through the factory instance), it needs to be provided with database connection string information as follows:
Connection.ConnectionString = _
"Data Source=xe;user id=scott;password=tiger"
Once the DbConnection object is ready, we can open the connection to connect and work with the database. It is always suggested to open a database connection as late as possible and close it as early as possible. The following code fragment tries to open the connection using the Open() method and closes using the Close() method:
Try
Connection.Open()
Connection.Close()
MessageBox.Show("Succesfully connected")
Catch ex As Exception
MessageBox.Show("Unable to connect. " & ex.Message)
End Try
This model (and method) of connectivity is mostly preferred when you are trying to develop database-independent applications.
Connecting Using .NET Data Provider for OLEDB
This method is mostly preferred when you are trying to develop database-independent applications based on ADO.NET 1.1. If you are trying to develop a database-independent application based on ADO.NET 2.0, the method provided in the previous section is preferred.
The following is the code to connect to Oracle database using .NET data provider for OLEDB:
Imports System.Data.OleDb
Public Class Form4
Private Sub btnConnect_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnConnect.Click
Dim cn As New OleDbConnection
cn.ConnectionString = "Provider=msdaora;
Data Source=xe;User Id=scott;Password=tiger;"
Try
cn.Open()
cn.Close()
MessageBox.Show("Succesfully connected")
Catch ex As Exception
MessageBox.Show("Unable to connect. " & ex.Message)
End Try
End Sub
End Class
In the above code, the System.Data.oleDb namespace is used to deal with .NET Data Provider for OLEDB. When we are working with OLEDB data sources, we need to connect through the OleDbConnection class. The connection string information would also be different when we deal with .NET Data Provider for OLEDB to connect to Oracle.
The following is the new connection string used to get connected to Oracle database using .NET Data Provider for OLEDB:
cn.ConnectionString = "Provider=msdaora;
Data Source=xe;User Id=scott;Password=tiger;"
Connecting Using .NET Data Provider for ODBC
This method is used when you are trying to develop multi-platform database‑independent applications using ADO.NET. This method is preferable, if you want to connect to legacy systems or database systems existing on other platforms.
The following is the code to connect to Oracle database using .NET data provider for ODBC:
Imports System.Data.odbc
Public Class Form5
Private Sub btnConnect_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnConnect.Click
Dim cn As New OdbcConnection
cn.ConnectionString =
"Driver={Microsoft ODBC for Oracle};
Server=xe;Uid=scott;Pwd=tiger;"
Try
cn.Open()
cn.Close()
MessageBox.Show("Succesfully connected")
Catch ex As Exception
MessageBox.Show("Unable to connect. " & ex.Message)
End Try
End Sub
End Class
In the preceding code, the System.Data.odbc namespace is used to deal with .NET Data Provider for ODBC. When we are working with ODBC data sources, we need to connect through the OdbcConnection class. The connection string information would also be different when we deal with .NET Data Provider for ODBC to connect to Oracle. The following is the new connection string used to get connected to Oracle database using .NET Data Provider for ODBC:
cn.ConnectionString = "Driver={Microsoft ODBC for Oracle};
Server=xe;Uid=scott;Pwd=tiger;"
Connecting using Microsoft's .NET Data Provider for Oracle
This provider is added by Microsoft to facilitate developers connecting and accessing Oracle databases. This method is mostly preferred when you are trying to access only Oracle databases and when you don't have ODP.NET installed on your machine.
Before you start working with this provider, you need to add a reference to the assembly System.Data.OracleClient as shown in following figure:
Once you add a reference as shown in the preceding figure, you can proceed with the following code to connect to Oracle database using Microsoft's .NET data provider for Oracle:
Imports System.Data.OracleClient
Public Class Form6
Private Sub btnConnect_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnConnect.Click
Dim cn As New OracleConnection
cn.ConnectionString = _
"Data Source=xe; User Id=scott;Password=tiger;"
Try
cn.Open()
cn.Close()
MessageBox.Show("Succesfully connected")
Catch ex As Exception
MessageBox.Show("Unable to connect. " & ex.Message)
End Try
End Sub
End Class
In the above code, we are making use of the System.Data.OracleClient namespace to deal with Microsoft's .NET Data Provider for Oracle. The OracleConnection class used in the above code is available as part of the same namespace (and not to be confused with the same class available in Oracle.DataAccess.Client).
Connecting Using Oracle Data Provider for .NET (ODP.NET)
This provider is contributed by Oracle to facilitate developers connecting and accessing Oracle databases with tight integration (along with best performance) and advanced features. This method is the best even when you are trying to access Oracle, as ODP.NET has tight integration with Oracle database. To use this method, you must have ODP.NET downloaded (available free) and installed on your machine.
Once you have ODP.NET installed on your machine, you need to add a reference to the assembly Oracle.DataAccess. If you have more than one version installed, you may have to choose the right one. If you are using Visual Studio 2005 and ODP.NET 10.2.0.2.20 (with support for ADO.NET 2.0) choose as shown in following figure:
Once you add a reference as shown in the above figure, you can proceed with the following code to connect to Oracle database using ODP.NET:
Imports oracle.DataAccess.Client
Public Class Form7
Private Sub btnConnect_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnConnect.Click
Dim cn As New OracleConnection
cn.ConnectionString = _
"Data Source=xe;User Id=scott;Password=tiger;"
Try
cn.Open()
cn.Close()
MessageBox.Show("Succesfully connected")
Catch ex As Exception
MessageBox.Show("Unable to connect. " & ex.Message)
End Try
End Sub
End Class
In the above code, the namespace Oracle.DataAccess.Client is used to deal with Oracle Data Provider for .NET (ODP.NET). The OracleConnection class used in the above code is available as part of the same namespace (and not to be confused with the same class available in System.data.OracleClient). The connection string information for this data provider and .NET data provider factory classes could be the same (as both of them deal with the namespace internally).
Connecting with Connection Pooling
Opening and maintaining a database connection for each client (or application/user) is expensive and wastes lots of resources. This is true especially during web application development. To overcome such scenarios, Connection Pooling can be implemented.
A Connection Pool is simply a cache of database connections. These connections can be reused when the database receives future requests from clients (or applications) for data. The clients (or applications) will feel as if each of them has a separate connection to the database.
Connection Pooling is enabled by default and it is not only limited to ODP.NET but also available with other .NET data providers. You can simply add pooling=false to your connection string to disable Connection Pooling. You can customize pooling with your own specification within the connection string.
The following is a simple demonstration of customizing the Connection Pooling as part of the connection string:
Imports oracle.DataAccess.Client
Public Class Form7
Private Sub btnConnect_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnConnect.Click
Dim cn As New OracleConnection
cn.ConnectionString = "Data Source=xe;
User id=scott;Password=tiger;
Min Pool Size= 5;
Connection Lifetime=120;
Connection Timeout=60;
Incr Pool size=2;
Decr Pool size=1"
Try
cn.Open()
cn.Close()
MessageBox.Show("Succesfully connected")
Catch ex As Exception
MessageBox.Show("Unable to connect. " & ex.Message)
End Try
End Sub
End Class
The connection string in the code above is defined with several parameters. ConnectionLifetime sets the maximum duration in seconds of the connection object in the pool. ConnectionTimeout is the maximum number of seconds to wait for the connection to the server (before raising an error). MinPoolSize is the number of connection objects it needs to hold at any time (similarly MaxPoolSize is also available). Based on the demands of requests and activity, the number of connections in the pool gets decreased or increased based on the specification of IncrPoolsize and DecrPoolsize.
Connecting with System-Level Privileges or DBA Privileges
DBA-level privileges are primarily focused on database object-level access of a particular user. System-level privileges are more special when compared with ordinary database-level (or even object-level) privileges. When connecting with system-level privileges, you have the opportunity to administer the database, even before it starts up.
The two main system-level privileges are SYSDBA and SYSOPER. When you log in as SYSDBA, the default schema is SYS, whereas with SYSOPER the default schema is PUBLIC. SYSDBA is a superset of SYSOPER.
While connecting with system-level privileges, it is obvious to work with DBA privileges as well. If you don't need to work at system level, and simply want to access few of the DBA objects, it is not really necessary to connect using system-level privileges.
If you need .NET applications to connect to Oracle with system-level privileges, you just need to add connection parameters to the existing connection string as follows:
Imports oracle.DataAccess.Client
Public Class Form7
Private Sub btnConnect_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnConnect.Click
Dim cn As New OracleConnection
cn.ConnectionString = "Data Source=xe;
User id=system;Password=manager;
DBA Privilege=SYSOPER"
Try
cn.Open()
cn.Close()
MessageBox.Show("Succesfully connected")
Catch ex As Exception
MessageBox.Show("Unable to connect. " & ex.Message)
End Try
End Sub
End Class
In the above statement, you can observe that the user name is system (which is a DBA user) and privilege is SYSDBA.
Dynamic Connecting String Using OracleConnectionStringBuilder and app.config
You can dynamically build a connection string using the OracleConnectionStringBuilder class available in ODP.NET 10.2.0.2. This is very helpful if you have any Oracle connectivity parameters in the .NET configuration files like app.config or web.config.
Now, let us add few of the Oracle connectivity parameters to the app.config file by using solution properties as follows:
Once you add the parameters as shown in the above figure, you can develop the code as follows to dynamically create a connection string using OracleConnectionStringBuilder (explained later):
Imports Oracle.DataAccess.Client
Public Class Form9
Private Function getConnectionString() As String
Dim cnBuilder As New OracleConnectionStringBuilder
With cnBuilder
.DataSource = My.Settings.DataSource
.UserID = My.Settings.UserID
.Password = My.Settings.Password
End With
Return cnBuilder.ConnectionString
End Function
Private Sub btnConnect_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnConnect.Click
Dim cn As New OracleConnection
cn.ConnectionString = getConnectionString()
Try
cn.Open()
cn.Close()
MessageBox.Show("Succesfully connected")
Catch ex As Exception
MessageBox.Show("Unable to connect. " & ex.Message)
End Try
End Sub
End Class
From the above code, you can observe that we are trying to retrieve all the connection parameters from the app.config file using the My object introduced in .NET Framework 2.0. The OracleConnectionStringBuilder object simply needs to have a few properties (like DataSource, UserID, Password etc.) set. Once the properties are set, it automatically frames a connection string internally and returns this when used with the ConnectionString property.
Embedding a "tnsnames.ora" Entry-like Connection String
In all of the above examples, we directly used the specification available in the tnsnames.ora file. You can even define your own entry in the style of tnsnames.ora, directly within the connection string. The following is the code for a tnsnames.ora-less connection:
Imports oracle.DataAccess.Client
Public Class Form7
Private Sub btnConnect_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnConnect.Click
Dim cn As New OracleConnection
Dim ConnStr As String
ConnStr = "Data Source = "
ConnStr &= "(DESCRIPTION = "
ConnStr &= " (ADDRESS_LIST ="
ConnStr &= " (ADDRESS = (PROTOCOL = TCP)
(HOST = 127.0.0.1)(PORT = 1521))"
ConnStr &= " )"
ConnStr &= " (CONNECT_DATA ="
ConnStr &= " (SERVICE_NAME = xe)"
ConnStr &= " )"
ConnStr &= ");"
ConnStr &= "User Id=scott;"
ConnStr &= "password=tiger;"
cn.ConnectionString = ConnStr
Try
cn.Open()
cn.Close()
MessageBox.Show("Succesfully connected")
Catch ex As Exception
MessageBox.Show("Unable to connect. " & ex.Message)
End Try
End Sub
End Class
In the above code, we simply copied and pasted the entry available in tnsnames.ora and it worked like a charm. You can also make the above connection string dynamic (say, if you want to connect to different data sources at different times), by adding text boxes to your form and concatenating those values with the above connection string.
Connecting to a Default Oracle Database
In all of the previous methods, within the connection string, we specified the data source or server values to connect to an Oracle instance (using SID). Sometimes, it may be necessary for us to get connected to the default Oracle database existing on the same machine as of the .NET application (but not on any other network server).
Connecting to a default Oracle database is purely dependent on the ORACLE_SID key available in your registry (as shown in the following). You can even add it manually if it is not available in your Oracle home. Once that is added, you can define connection strings without the specification of data source or server.
Even though you can add this ORACLE_SID using the "Environment Variables" dialog box, this method is not suggested if you have multiple versions of Oracle installed on the same machine.
Once you set up the default Oracle database using the ORACLE_SID registry key in your registry, the connection string could be modified and made simple (without specifying any data source or server specification) as follows:
cn.ConnectionString = "User Id=scott;Password=tiger;"
Connecting Using Windows Authentication (Single Sign‑On)
This is totally a different scenario from any of the previous types of connectivity to Oracle databases. A Windows Authentication is simply a process of authenticating against Oracle database using the Windows-user credentials. A Single Sign-on is the process of authenticating against Oracle database even without providing any credentials (by taking into the account of existing Windows-user credentials).
There exists no direct solution to achieve 100% single sign-onto authenticate against Oracle database. However, we need to provide the user ID as "/", which automatically carries our current Windows-user credentials to authenticate against Oracle database. By using this facility, we can develop .NET applications implementing 100% single sign-on against Oracle databases.
Primarily, a Windows Authentication to an Oracle database is not a straight process. Even though, it is not very complicated process, we do have some configuration, which needs to be set up using database administrator privileges. To get a Windows user for a successful Windows authentication (or single sign-on) against Oracle database, we must start by finding two important values as follows:
- Operating System Authentication Prefix (
os_authent_prefix parameter in the init.ora file) - Windows user name (along with either host name or domain name)
The Operating System Authentication Prefix gets configured during Oracle installation and is available as an os_authent_prefix parameter in the init.ora file. We need to use this value as a prefix to the Windows-user credentials. To retrieve the value of that parameter, you need to use the following statement:
SQL> show parameter os_authent_prefix
You may need to have DBA privileges (or log in as system/sysdba/sysoperuser) to carry out these tasks.
You can easily get your complete user name (along with your host name or domain name) from your login dialog box. You can even get it dynamically using the following VB.NET code:
Private Sub btnWindowsUser_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnWindowsUser.Click
Dim WindowsUser As String = My.User.Name
MessageBox.Show(WindowsUser)
End Sub
Once you find out those two values, you need to create a user in Oracle with the same Windows user name (along with host/domain name) preceded with the value of os_authent_prefix and grant enough privileges to get the user connected.
Sometimes, the value of os_authent_prefix could be empty (or no value). In such scenarios, you need not prefix the Windows user with any value.
You can issue the following statements to create and grant privileges to the Windows user in Oracle:
SQL> CREATE USER "PS$LAPTOP2K3\ADMINISTRATOR"
IDENTIFIED EXTERNALLY;
SQL> GRANT connect, resource TO
"PS$LAPTOP2K3\ADMINISTRATOR"
In the above commands, PS$ is the parameter value of os_authent_prefix on my machine and LAPTOP2K3\ADMINISTRATOR is the Windows user. If there is no value (or empty) for os_authent_prefix, you need not prefix the Windows user with any value. Once the above setup is perfectly configured, you must be able to connect to that user using the following command at the SQL prompt:
SQL> connect /
You can observe that it is quite simple to connect to Oracle database using "/", which informs it to use a Windows authentication. In the same manner, you can modify your connection string in .NET as follows to achieve a single sign-on authentication (with Windows authentication) to Oracle database:
Dim cn As New OracleConnection
cn.ConnectionString = "Data Source=xe;User Id=/;"
Summary
In this chapter, we have reviewed the strategy of the Provider-Independent Model in ADO.NET 2.0, used this model to list installed .NET data providers and data sources, and finally developed code to connect to Oracle database from .NET using all the available methods.
