oracledba.help
Windows

Visual Studio 2010 Pro and Oracle

<- Windows

Overview

The following covers the steps to install Visual Studio 2010 Pro and connect to an Oracle database. One advantage of using VS 2010 is that your applications will work with Windows XP and later. Newer versions of VS will only work with Vista and later.

Prerequisites

  1. Oracle client software installed.
    • If on server Oracle Database Extensions for .NET.
    • If on client ODAC installed.
  2. You can connect to your Oracle database from SQLPlus.
  3. Download installation files from MS.
    Downloaded files will be similar to: VS2010ProTrial_4PartsTotal.part1...4
  4. Create a directory for your development projects.
    Example: C:\app\vs\dev

Note: You will have to acquire a MS product key for your product to be active more than 30 days.

Create .iso From .rar Files

  1. Run VS2010ProTrial_4PartsTotal.part1.exe
    This will extract .rar files creating an .iso file.
  2. Select directory to extract .rar files (use default if you can).
    Select Install.
    Extracting process runs...

Visual Studio Installation

  1. Run setup.exe.
  2. Select Install Microsoft Visual Studio 2010
    When visible unselect: [ ] Yes, send information about my setup ...
  3. When loading complete: Next
  4. License Terms
    • (x) I have read and accept the license terms
  5. Select Custom.
  6. Select just features you need to avoid bloatware and excessive software installations.
    Example:
    • [x] Visual Basic
    • [x] Visual C#
    • [x] Graphics Library
  7. Select Install
    Some of the components installed include:
    • VC 9.0, VC 10.0
    • .NET Framework 4, 4.5
    • SQL Server components: Publishing Wizards, CLR types...
    • ADO.NET Entity Framework Tools
  8. After above step done select Restart Server.
    • VS install continues after restart...
    • Success will be indicated after software install phase.
  9. Select Install Documentation
    • Accept default location.
    • Add required documentation library.

Initialize New Project

Setting your application directory from the get-go will jump start a new project. Change MyAppName with each new project name.

First Launch of VS Note

The first time you run VS you will get a prompt to Choose your default environment settings. If you want Visual Basic for example, select Visual Basic Development Settings then Start Visual Studio.

Also set your root projects directory. Tools -> Options -> Projects & Solutions -> General -> Project location: C:\app\vs

  1. Launch Visual Studio
  2. New Project
    • Select Windows Forms Application.
    • Enter a Name for your project.
    • Select OK.
  3. File -> Close Project
  4. At the Close Project Prompt select Save.
    • Name: MyAppName
    • Location: C:\app\vs\dev
      Use the root of your dev directory as shown above. A sub-directory will be created below it.
    • Solution Name: MyAppName
    • [x] Create directory for solution.
    • Select Save.
  5. Then reopen your project ensuring to save all newly added components to that path.

Create Oracle Reference

  1. Within the Solution Explorer on the right side of Visual Studio, select the project name, right click and select Add Reference.
    • Alternatively, you can go to the menu bar and select Project and then select Add Reference.
  2. Select the Browse tab and enter the below to match your environment.
    %ORACLE_HOME%\ODP.NET\bin\4\Oracle.DataAccess.dll

Database Connection Snippet

Requires making a reference to %ORACLE_HOME%\ODP.NET\bin\4\Oracle.DataAccess.dll.
No ODBC entry required for this example. Just an entry in the tnsnames.ora file (MyTnsSvcName).

Imports Oracle.DataAccess.Client

'Create Connection to Oracle Database
Dim oConnStr As String = "User Id=scott;Password=tiger;Data Source=localhost/MyTnsSvcName"
Dim oConn As New OracleConnection(oConnStr)
oConn.Open()

'Create SQL Cmd
Dim sSQL As String = "SELECT 'HelloWorld' FROM dual"
Dim oCmd As New OracleCommand(sSQL, oConn)
oCmd.CommandType = CommandType.Text

'Create a ResultSet (AKA Cursor, DataReader, RecordSet ... Ahhh!)
Dim oRS As OracleDataReader = oCmd.ExecuteReader()
oRS.Read()

'Output Returned Value
MsgBox(vbCrLf & _
"From Ordinal SQL Cmd Position: " & oRS.Item(0) & vbCrLf & _
"As a .NET DataType: " & oRS.GetString(0).ToString() & vbCrLf & _
"As an Oracle DataType: " & oRS.GetOracleString(0).ToString() & vbCrLf & _
"", MsgBoxStyle.OkOnly, "Get Value")

'As a column name: oRS.GetString("MyColName") 'UPPER case may be required
'To a control: MyLabel.text = oRS.Item(0)

'Housekeeping
oConn.Close()
oConn.Dispose()

Oracle Database Configuration (for Binding Controls)

  1. From the start-up menu load your project (if not already done).
  2. Select 'Add New Data Sources (on left).
    1. Select Database.
    2. Select Dataset.
    3. Select New Connection
      • Data source: <other>
        Data provider: .NET Framework Data Provider for OLE DB
      • OLE DB Provider: Oracle Provider for OLE DB
      • Server or file name: MyInstanceName
      • User name: MyOracleUserName
      • Password: MyOracleUserPassword
  3. Select the Test Connection button.
  4. Select OK to save your new database connection.
  5. At Choose Your Data Connection select OraOLEDB.Oracle
    Edit connection string to your liking.
  6. At Save the Connection String save it as ConnectionString (default).
    Database info will be retrieved. This may take a few minutes.
    • Select the Database Objects you want associated with this database connection and give it a Dataset name.

If Oracle Client Installed

 Data source: Oracle Database 
Data provider: .NET Framework Data Provider for Oracle
[Continue] Server or file name: MyInstanceName (in tnsnames.ora) User name: MyOracleUserName Password: MyOracleUserPassword [Test Connection]

Comments

This is the QuickStart version. There are a crazy collection of ways to connect to Oracle. One for each month of the year it seems! This way works out-of-the-box. If VB6 is supported through EOL of Windows 8 the methods shown here will be for some time too. That being said, it can be useful to know the latest and greatest connection architecture.

Save your favorite Projects as templates to improve application creation.

<- Windows