Click here to Skip to main content
Click here to Skip to main content
Go to top

ODP.NET Developer’s Guide: Oracle Database 10g Development with Visual Studio 2005 and the Oracle Data Provider for .NET - Chapter 2: Connecting to Oracle

, 10 Aug 2007
Rate this:
Please Sign up or sign in to vote.
A practical guide for developers working with the Oracle Data Provider for .NET and the Oracle Developer Tools for Visual Studio 2005
Screenshot - cover.jpg
Title ODP.NET Developer's Guide: Oracle Database 10g Development with Visual Studio 2005 and the Oracle Data Provider for .NET
Authors Jagadish Chatarji Pulakhandam, Sunitha Paruchuri
Publisher Packt Publishing
Published June 2007
ISBN 1847191967
Price US 49.99
Pages 300

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:

Screenshot - 1960_02_01.jpg

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:

Screenshot - 1960_02_02.jpg

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

    'specify provider's invariant name
    Dim ProviderName As String = _
        "Oracle.DataAccess.Client"
    'create factory instance for the provider
    Dim fctry As DbProviderFactory = _
        DbProviderFactories.GetFactory(ProviderName)
    'create connection based on the factory
    Dim Connection As Data.Common.DbConnection
    Connection = fctry.CreateConnection
    'specify connection string
    Connection.ConnectionString = _
        "Data Source=xe;user id=scott;password=tiger"
    Try
        'try connecting to oracle
        Connection.Open()
        'close the connection before exiting
        Connection.Close()
        MessageBox.Show("Succesfully connected")
        Catch ex As Exception
        'display error message if not connected
        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
    'try connecting to oracle
    Connection.Open()
    'close the connection before exiting
    Connection.Close()
    MessageBox.Show("Succesfully connected")
Catch ex As Exception
    'display error message if not connected
    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
            'try connecting to oracle
            cn.Open()
            'close the connection before exiting
            cn.Close()
            MessageBox.Show("Succesfully connected")
        Catch ex As Exception
            'display error message if not connected
            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
            'try connecting to oracle
            cn.Open()
            'close the connection before exiting
            cn.Close()
            MessageBox.Show("Succesfully connected")
        Catch ex As Exception
            'display error message if not connected
            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:

Screenshot - 1960_02_03.jpg

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
            'try connecting to oracle
            cn.Open()
            'close the connection before exiting
            cn.Close()
            MessageBox.Show("Succesfully connected")
        Catch ex As Exception
            'display error message if not connected
            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:

Screenshot - 1960_02_04.jpg

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
            'try connecting to oracle
            cn.Open()
            'close the connection before exiting
            cn.Close()
            MessageBox.Show("Succesfully connected")
        Catch ex As Exception
            'display error message if not connected
            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
            'try connecting to oracle
            cn.Open()
            'close the connection before exiting
            cn.Close()
            MessageBox.Show("Succesfully connected")
        Catch ex As Exception
            'display error message if not connected
            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
            'try connecting to oracle
            cn.Open()
            'close the connection before exiting
            cn.Close()
            MessageBox.Show("Succesfully connected")
        Catch ex As Exception
            'display error message if not connected
            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:

Screenshot - 1960_02_05.jpg

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
            'try connecting to oracle
            cn.Open()
            'close the connection before exiting
            cn.Close()
            MessageBox.Show("Succesfully connected")
        Catch ex As Exception
            'display error message if not connected
            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
            'try connecting to oracle
            cn.Open()
            'close the connection before exiting
            cn.Close()
            MessageBox.Show("Succesfully connected")
        Catch ex As Exception
            'display error message if not connected
            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.

Screenshot - 1960_02_06.jpg
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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Packt Publishing
Researcher
India India
No Biography provided

Comments and Discussions

 
Questionconnect to Oracle database using ODP.NET Pinmembersuecnus31-Oct-07 7:32 
I have Oracle client 10g and ODP.net installed. When I tried to connect to the database through "Connect to Database" wizard, I chose "Oracle Database" in the Data source, but in the Data Provider dropdown box, I don't see DPD.NET listed. It still only has .NET framework data provider for oledb and .NET framework data provider for Oracle listed. Is there anything else I should in order to enable selection of ODP.NET to my project?
 
Sue
AnswerRe: connect to Oracle database using ODP.NET PinmemberPackt Publishing1-Nov-07 19:16 
GeneralRe: connect to Oracle database using ODP.NET Pinmembermariahayek11-Jan-08 23:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140926.1 | Last Updated 10 Aug 2007
Article Copyright 2007 by Packt Publishing
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid