none
vb.net code to copy a table from a generic database to access RRS feed

  • General discussion

  •  

    Hi.

    I've been looking for a way to make an offline copy from a mySQL/Oracle database table to MS Access, I've read some posts, but none really fullfilled my needs. One day I've run into the solution, but in c#, so I've converted it to vb.net and here it is:

     

    Code Snippet

    Imports System

    Imports System.Collections.Generic

    Imports System.Text

    Imports System.Data

    Module Module1

    ' <summary>

    ' This method will copy the data in a table from one database to another. The

    ' source and destination can be from any type of .NET database provider.

    ' </summary>

    ' <param name="source">Source database connection</param>

    ' <param name="destination">Destination database connection</param>

    ' <param name="sourceSQL">Source SQL statement</param>

    ' <param name="destinationTableName">Destination table name</param>Public Sub CopyTable(ByVal source As IDbConnection, ByVal destination As IDbConnection, ByVal sourceSQL As String, ByVal destinationTableName As String)System.Diagnostics.Debug.WriteLine(System.DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss") + " " + destinationTableName + " load started")

    Dim cmd As IDbCommand = source.CreateCommand()

    cmd.CommandText = sourceSQL

    System.Diagnostics.Debug.WriteLine("" & Chr(9) & "Source SQL: " + sourceSQL) 'debug

    Try

    source.Open()

    destination.Open()

    Dim rdr As IDataReader = cmd.ExecuteReader()

    Dim schemaTable As DataTable = rdr.GetSchemaTable()

    Dim insertCmd As IDbCommand = destination.CreateCommand()

     

    Dim paramsSQL As String = [String].Empty

    'build the insert statement

    For Each row As DataRow In schemaTable.Rows

    If paramsSQL.Length > 0 Then

    paramsSQL += ", "

    End If

    paramsSQL += "@" + row("ColumnName").ToString()

    Dim param As IDbDataParameter = insertCmd.CreateParameter()

    param.ParameterName = "@" + row("ColumnName").ToString()

    param.SourceColumn = row("ColumnName").ToString()

    insertCmd.Parameters.Add(param)

    Next

    insertCmd.CommandText = [String].Format("insert into {0} ( {1} ) values ( {2} )", destinationTableName, paramsSQL.Replace("@", [String].Empty), paramsSQL)

    Dim counter As Integer = 0

    Dim errors As Integer = 0

    While rdr.Read()

    Try

    For Each param As IDbDataParameter In insertCmd.Parameters

    Dim col As Object = rdr(param.SourceColumn)

    param.Value = col

    Next

    insertCmd.ExecuteNonQuery()

    Catch ex As Exception

    If errors = 0 Then

    System.Diagnostics.Debug.WriteLine(ex.Message.ToString())

    End If

    System.Math.Max(System.Threading.Interlocked.Increment(errors), errors - 1)

    End Try

    End While

    System.Diagnostics.Debug.WriteLine(errors + " errors")

    System.Diagnostics.Debug.WriteLine(counter + " records copied")

    System.Diagnostics.Debug.WriteLine(System.DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss") + " " + destinationTableName + " load completed")

    Catch ex As Exception

    Console.WriteLine(ex.StackTrace.ToString())

    System.Diagnostics.Debug.WriteLine(ex)

    Finally

    destination.Close()

    source.Close()

    End Try

    End Sub

    Public Function ExecuteSQL(ByVal connection As IDbConnection, ByVal sqlStatement As String) As Boolean

    Dim cmd As IDbCommand = connection.CreateCommand()

    cmd.CommandText = sqlStatement

    cmd.CommandTimeout = 300

    Try

    connection.Open()

    System.Diagnostics.Debug.WriteLine("" & Chr(9) & "==> " + cmd.CommandText)

    cmd.ExecuteNonQuery()

    Return True

    Catch ex As Exception

    System.Diagnostics.Debug.WriteLine(ex)

    Return False

    Finally

    connection.Close()

    End Try

    End Function

    End Module

     

     

    Tuesday, April 22, 2008 3:37 PM

All replies

  • Hi kroyax,

     

    you did a good job with this converting.

    I don't understand how to use the source and destination parameters. Can you give me an example.

     

    Coen

     

    Wednesday, April 30, 2008 6:59 PM
  • Hi!

     

    You must define fist the connections you need to make:

    For exemple, I'll copy some table from oracle to access, so I need to create those connections:

     

    Code Snippet

    ConDWS = New OdbcConnection("Driver={Microsoft ODBC for Oracle};Server=xxxxx;Uid=xxxxx;Pwd=xxxxx")

    ConDWI = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DW_INSCO.mdb;User ID=Admin;Password=")

     

     

    Than You just have to call the funtion:

    Code Snippet

    CopyTable(ConDWS, ConDWI, "SELECT WH, SKU, DESC_UP FROM PBS_INSCO;", "PBS_INSCO")

     

     

     

    Wednesday, June 11, 2008 11:15 AM
  • Hi.

    Nice code, but could you please post the C# code as well.

    Thanks in advance. And also what about the other way around - from mdb to oracle database Smile

    Monday, June 23, 2008 2:25 PM
  • Brilliant Kroyax, thanks for this bit of code. It's a bit late I suppose but it got me out of a jam.
    Thursday, October 15, 2009 2:12 AM