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:
- Retrieves the data from Oracle through a
- Converts the
OracleDataReader to a
- Creates the SQL Server Tables based on the
- Performs a
SQLBulkCopy of the
DataTable into the new SQL Server tables.
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
DataTable. Most conversion functions are done using built in procedures.
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:
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
connection.ConnectionString = connectionString
Dim command As OracleCommand = connection.CreateCommand
Dim sql As String = TextBox2.Text
command.CommandText = sql
Dim reader As OracleDataReader = command.ExecuteReader
Dim dra As DataUtils.DataReaderAdapter = &_
Dim dt As DataTable = New DataTable
DataGridView1.DataSource = dt
oConn = New SqlConnection(txtDestinationConnString.Text)
Dim sqlTableCreator As SQLCreateTable = New SQLCreateTable
sqlTableCreator.Connection = oConn
sqlTableCreator.DestinationTableName = txtDestTableName.Text
sqlTableCreator.DropTableIfExists = CheckBox1.Checked
Catch ex As Exception
To convert the
OracleDataReader to a
DataTable, I overrode a protected
Fill method that allows you to pass a
DataReader. For more information on this, please refer to the article that I used: http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=628
Public Class DataReaderAdapter
Public Function FillFromReader(ByVal dataTable As DataTable, &_
ByVal dataReader As IDataReader) As Integer
Return Me.Fill(dataTable, dataReader)
... (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
Private Sub CopyData(ByVal sourceTable As DataTable, &_
ByVal destConnection As SqlConnection)
Dim s As SqlBulkCopy = New SqlBulkCopy(destConnection)
s.DestinationTableName = "OraTest3"
s.NotifyAfter = 10000
AddHandler s.SqlRowsCopied, AddressOf s_SqlRowsCopied
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.