Oracle to SQL Server VB.NET Utility application

• Download source files - 36.5 KB

Introduction

This is a small utility application that I wrote to help retrieve data from an Oracle source database, then to create the structure dynamically in SQL Server and perform a Bulk Copy. I put a GUI on top of it so that you can use this to test multiple connections and queries.

This utility performs the following steps:

1. Retrieves the data from Oracle through a OracleDataReader
2. Converts the OracleDataReader to a DataTable
3. Creates the SQL Server Tables based on the DataTable Structure
4. Performs a SQLBulkCopy of the DataTable into the new SQL Server tables.

Background

Why would you ever want to perform this? Let's face it, not all of your data requirements for a given application or report are going to reside in one place, especially in large organizations. I was looking for a quick way to perform a SQL query against Oracle and bring the data into SQL Server. This was finally the solution, and it does NOT require the Oracle client to be installed. Since this utility will be deployed to a server where it will be scheduled to run queries, I didn't want to have to install the Oracle client. So I used the Oracle Instant Client.

The code performs fairly well and I am fairly pleased with the little code it took to perform this. There are no loops through the DataSet or DataTable. Most conversion functions are done using built in procedures.

Prerequisites

Before doing anything, you will need to download the Oracle Instant Client. This is a zip file: instantclient-basic-win32-10.2.0.3-20061115.zip that contains several DLL and jar files that are required to connect to the Oracle database. YOU DO NOT NEED TO INSTALL THE ORACLE CLIENT. Simply place these files in the same folder as your .exe file. You will also need to create or copy a TNSNAMES.ORA file into the same directory.

Download the instant client from here:
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html

Using the code

ConnectandQuery() is the main routine. From here I call all the sub routines and other helper classes to achieve the objectives.

Private Sub ConnectAndQuery()
    Dim connectionString As String = GetConnectionString()
    Dim connection As OracleConnection = New OracleConnection
    Dim oConn As SqlConnection

    Try
    'CONNECT TO ORACLE AND EXECUTE THE QUERY
        connection.ConnectionString = connectionString
        connection.Open()
        Dim command As OracleCommand = connection.CreateCommand
        Dim sql As String = TextBox2.Text
        command.CommandText = sql
        Dim reader As OracleDataReader = command.ExecuteReader

        'CONVERT DATAREADER TO DATATABLE
        Dim dra As DataUtils.DataReaderAdapter = &_
        New DataUtils.DataReaderAdapter
        Dim dt As DataTable = New DataTable
        dra.FillFromReader(dt, reader)
        reader.Close()

        'BIND THE DATATABLE TO THE DATAGRIDVIEW
        DataGridView1.DataSource = dt

        'Create the SQL Server Table
        oConn = New SqlConnection(txtDestinationConnString.Text)
        oConn.Open()
        Dim sqlTableCreator As SQLCreateTable = New SQLCreateTable
        sqlTableCreator.Connection = oConn
        sqlTableCreator.DestinationTableName = txtDestTableName.Text
        sqlTableCreator.DropTableIfExists = CheckBox1.Checked
        sqlTableCreator.CreateFromDataTable(dt)

        'INSERT INTO SQL SERVER DATABASE
        CopyData(dt, oConn)
    Catch ex As Exception
        msgOut(ex.ToString)
    Finally
        CType(connection, IDisposable).Dispose()
        oConn.Close()
    End Try

End Sub

To convert the OracleDataReader to a DataTable, I overrode a protected Fill method that allows you to pass a DataTable and DataReader. For more information on this, please refer to the article that I used: http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=628

Imports System
Imports System.Data
Imports System.Data.Common
Namespace DataUtils
    Public Class DataReaderAdapter
        Inherits DbDataAdapter

        Public Function FillFromReader(ByVal dataTable As DataTable, &_
        ByVal dataReader As IDataReader) As Integer
            Return Me.Fill(dataTable, dataReader)
        End Function
... (rest of code not shown)

SQLBulkCopy in the System.Data.SQLClient namespace is an efficient class that allows you to copy large amounts of data from your .NET application to the SQL Server. This was introduced in the .NET Framework 2.0. Rather than inserting row by row which can take 13-15 seconds for a large recordset, using bulk copy, you can decrease it by 1/3 the time. This was important for me since some of the Oracle queries could return quite a lot of data. Please refer to the following article for more information on SQLBulkCopy:
http://www.sqlteam.com/item.asp?ItemID=26941

Private Sub CopyData(ByVal sourceTable As DataTable, &_
    ByVal destConnection As SqlConnection)
    Dim s As SqlBulkCopy = New SqlBulkCopy(destConnection)
    Try
        s.DestinationTableName = "OraTest3"
        s.NotifyAfter = 10000
        AddHandler s.SqlRowsCopied, AddressOf s_SqlRowsCopied
        s.WriteToServer(sourceTable)
        s.Close()
    Finally
        CType(s, IDisposable).Dispose()
    End Try
End Sub

Points of Interest

I enjoyed writing this program. It took me a while to figure out what the best way was to connect to Oracle. Also the corporate setup we have for connecting to Oracle databases uses SQLNet.ORA instead of TNSNames.ORA. I also had a lot of conflicts with running 2 versions of the Oracle Client. I finally read some articles that Oracle 10g does not play nicely with other Oracle Client versions such as 9i. In the end I decided to just remove all installations of the Oracle client and just use a TNSNames.ORA file. I hope someone is able to reuse this or some of the concepts at some point. Also, please let me know if you find ways to improve this code or a more preferred way to do the transform or bulk copy.

History

• 6/7/07: Original Posting