Click here to Skip to main content
13,454,296 members (57,888 online)
Click here to Skip to main content
Add your own
alternative version


4 bookmarked
Posted 19 Sep 2017

Paradox to SQL Server

, 19 Sep 2017
Rate this:
Please Sign up or sign in to vote.
Application lets you copy tables from Paradox to SQL Server



I developed this application to help me migrate Paradox database to SQL Server. I hope someone else will find this code also useful.


This application uses the "Paradox database native .NET reader" library developed by Petr Briza. It is fairly simple: you select the folder where Paradox database files reside, select the SQL Server database you want to copy the tables, select the tables you want to copy and click "Copy tables".  The application will create tables in the SQL server database and copy the data.  It will also try to copy files locally in case they are located on a network drive.

Using the code

The application uses Petr Briza library to do records counts for each table.  It uses Microsoft Jet OLEDB provider to read data in 32 bit mode.   The provider will not work in 64 bit.  This is why it is compiled in 32 bit mode.

Pradox db files can be password protected. The application uses JET OLEDB connection string property "Jet OLEDB:Database Password" for the password.

    Function GetParadoxConnectionString(ByVal sFolderPath As String, ByVal sPassword As String) As String
        If sFolderPath = "" Then
            Return ""
        End If

        If sPassword <> "" Then
            Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFolderPath & ";Extended Properties=Paradox 5.x;Jet OLEDB:Database Password=" & sPassword & ";"
            Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFolderPath & ";Extended Properties=Paradox 5.x;"
        End If
    End Function

CopyTableJet function does the actual work of copying data. It will insert 1000 records at a time if you select "Sql Ser 2008+".

    Private Sub CopyTableJet(ByVal sTableName As String, dr As OleDbDataReader, ByRef cnDst As OleDbConnection)

        Dim oSchemaRows As Data.DataRowCollection = dr.GetSchemaTable.Rows
        Dim sRow As String
        Dim i As Integer
        Dim iRow As Integer = 0
        Dim iRowCount As Integer = 0

        'Get Header
        Dim sHeader As String = ""
        For i = 0 To oSchemaRows.Count - 1
            Dim sColumn As String = oSchemaRows(i)("ColumnName")
            If i <> 0 Then
                sHeader += ", "
            End If
            sHeader += PadSqlColumnName(sColumn)

        Dim sValues As String = ""

        While dr.Read()
            iRowCount += 1
            sRow = ""

            For i = 0 To oSchemaRows.Count - 1
                If sRow <> "" Then
                    sRow += ", "
                End If

                sRow += GetValueString(dr.GetValue(i))

            If chkSQL2008.Checked Then
                If sValues <> "" Then sValues += ", "
                sValues += "(" & sRow & ")"

                If iRowCount >= 1000 Then
                    Dim sSql1 As String = "INSERT INTO " & PadSqlColumnName(sTableName) & " (" & sHeader & ") VALUES " & sValues
                    ExecuteSql(sSql1, cnDst)
                    iRowCount = 0
                    sValues = ""
                End If
                Dim sSql1 As String = "INSERT INTO " & PadSqlColumnName(sTableName) & " (" & sHeader & ") VALUES (" & sRow & ")"
                ExecuteSql(sSql1, cnDst)
            End If

            iRow += 1
            ProgressBar1.Value = Math.Min(ProgressBar1.Maximum, iRow)
            lbCount.Text = iRow.ToString()

            'Listen for the user to press Cancel button
            If bStop Then
                Log("Copied table " & sTableName & " stopped. ")
                Exit While
            End If

        End While

        If chkSQL2008.Checked And sValues <> "" Then
            Dim sSql1 As String = "INSERT INTO " & PadSqlColumnName(sTableName) & " (" & sHeader & ") VALUES " & sValues
            ExecuteSql(sSql1, cnDst)
        End If

    End Sub

GetCreateTableSqlFromParadox function will create the table in SQL server if it does not exist.

    Private Function GetCreateTableSqlFromParadox(ByVal sTableName As String, dr As OleDbDataReader) As String

        Dim sb As New System.Text.StringBuilder()
        Dim oSchemaRows As Data.DataRowCollection = dr.GetSchemaTable.Rows
        Dim sKeyColumns As String = ""
        Dim i As Integer = 0

        sb.Append("CREATE TABLE " & PadSqlColumnName(sTableName) & " (" & vbCrLf)

        For iCol As Integer = 0 To oSchemaRows.Count - 1
            Dim sColumn As String = oSchemaRows(iCol).Item("ColumnName").ToString() & ""
            Dim sColumnSize As String = oSchemaRows(iCol).Item("ColumnSize").ToString() & ""
            Dim sDataType As String = oSchemaRows(iCol).Item("DATATYPE").FullName.ToString()
            Dim bAllowDBNull As Boolean = oSchemaRows(iCol).Item("AllowDBNull")    'Does not always work

            If i > 0 Then
            End If

            sb.Append(" " & PadAccessDataType(sDataType, sColumnSize))

            If bAllowDBNull Then
                sb.Append(" NULL")
                sb.Append(" NOT NULL")
            End If

            i += 1


        If i = 0 Then
            Return ""
            Return sb.ToString()
        End If

    End Function


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Igor Krupitsky
Web Developer
United States United States
Igor is a business intelligence consultant working in Tampa, Florida. He has a BS in Finance from University of South Carolina and Masters in Information Management System from University of South Florida. He also has following professional certifications: MCSD, MCDBA, MCAD.

You may also be interested in...


Comments and Discussions

BugSlight Bug in Datehandling Pin
Member 106212624-Jan-18 22:43
memberMember 106212624-Jan-18 22:43 
QuestionTables not copying FIXED Pin
Ken Hull22-Nov-17 1:08
memberKen Hull22-Nov-17 1:08 
SuggestionRe: Tables not copying FIXED Pin
Member 106212625-Jan-18 0:30
memberMember 106212625-Jan-18 0:30 
QuestionTables with booleans don't copy Pin
Ken Hull21-Nov-17 9:10
memberKen Hull21-Nov-17 9:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180321.1 | Last Updated 19 Sep 2017
Article Copyright 2017 by Igor Krupitsky
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid