ASP.NET 2.0 Oracle Connection

So I wanted to create a connection to my Oracle Database 10g using Visual Studio 2008 in Visual Basic. I was not sure how to go about this to start with as I’ve been teaching myself the .NET framework with some nice books ordered from Amazon.  My problem was that the books would detail nicely how to make connections to MS SQL, but tell you in a small paragraph that you can connect to other database’s such as Oracle or My SQL, just not how to actually do it.

So after a bit of searching on the Web, reading material from Oracle and trying to match up the methods that were used in my books for the MS SQL connection I finally got it working. This is what I did.

First I needed to download the ODP.NET provider from Oracle. Head to the Oracle site and download Oracle 11g ODAC 11.1.0.7.20 with Oracle Developer Tools for Visual Studio.  Oracle always say that the client software needs to be at the same level or higher than the database, so using 11g Client on a 10g database is fine.  Once downloaded, you’ll need to unzip and run the Setup.exe which will launch the Universal Installer (which does need to be in Administrator mode if running on Vista).

Once installed go to All Programs, Oracle – OraClient11g_home1, Configuration and Migration Tools, and launch Net Configuration Assistant.  Now configure a Listener for your selected protocol (normally TCP and default Port number).  Of course if you have different values then make sure you key in the correct values before saving. Once complete exit from the program.

Now you should be able to start up Visual Studio 2008. Once loaded you create a new ASP.NET Website. Next you’ll need to add a Reference to the Oracle Provider. Click on Website from the menu and then Add Reference. Once the box has loaded, look under the .NET tab for Oracle.DataAccess, highlight and press OK.

Now for some code. First let me tell you about the table within Oracle that I did my testing on.  It’s a simple table called STORE with two columns:

NO NAME
10 Oxford
20 London
30 Leeds
40 Aberdeen
50 Manchester

 

Now I created a Web Form called readOrcTable.aspx with the following code:

<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="head1" runat="server">
    <title>Show Store Details</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
      <asp:GridView 
        ID="grdStores"
        DataSourceId="srcStores"
        Runat="server" />
        
      <asp:ObjectDataSource 
        ID="srcStores"
        TypeName="Stores1"
        SelectMethod="GetAll"
        Runat="server" />
        
    </div>
    </form>
</body>
</html>

 

Next I created a VB Class called OracleSQL.vb with the following code:

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports Oracle.DataAccess.Client
Imports System.Collections.Generic

Public Class Stores1

    Private Shared ReadOnly _connectionString As String
    Private _Number As String
    Private _Name As String

    Public Property Number() As String
        Get
            Return _Number
        End Get
        Set(ByVal value As String)
            _Number = value
        End Set
    End Property

    Public Property Name() As String
        Get
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name = value
        End Set
    End Property

    Public Function GetAll() As List(Of Stores1)
        Dim results As New List(Of Stores1)
        Dim con As New OracleConnection(_connectionString)
        Dim cmd As New OracleCommand("SELECT NO,NAME FROM STORE ORDER BY NO", con)

        Using con
            con.Open()
            Dim reader As OracleDataReader = cmd.ExecuteReader()

            While reader.Read()
                Dim NewStore As New Stores1()
                NewStore.Number = CType(reader("NO"), String)
                NewStore.Name = CType(reader("NAME"), String)
                results.add(NewStore)
            End While

        End Using

        Return results

    End Function

    Shared Sub New()
        _connectionString = "User Id=LoginID;Password=LoginPwd;Data Source=ORACLE;"
    End Sub

End Class

I’ve removed my User Id and Password, which you will have to swap for your own.  And that is all that there is too it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: