Click here to Skip to main content
15,499,155 members
Articles / Database Development / SQL Server
Posted 19 Sep 2017


6 bookmarked

Paradox to SQL Server

Rate me:
Please Sign up or sign in to vote.
5.00/5 (6 votes)
19 Sep 2017CPOL1 min read
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 also find this code 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.

Image 1

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.

Image 2

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)

Written By
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.

Comments and Discussions

QuestionUnexpected error from external database driver (11010) Pin
Loek Huijs18-Oct-22 21:52
MemberLoek Huijs18-Oct-22 21:52 
AnswerRe: Unexpected error from external database driver (11010) Pin
Loek Huijs19-Oct-22 1:22
MemberLoek Huijs19-Oct-22 1:22 
QuestionNot show data Pin
xblopa7-Sep-22 9:21
Memberxblopa7-Sep-22 9:21 
AnswerRe: Not show data Pin
Igor Krupitsky8-Sep-22 5:47
MemberIgor Krupitsky8-Sep-22 5:47 
Questiondate / datetime / timestamp conversion Pin
Allan Batteiger15-Oct-21 13:23
MemberAllan Batteiger15-Oct-21 13:23 
QuestionI need exe only Pin
Bartolomeo Giorgio10-May-21 0:14
MemberBartolomeo Giorgio10-May-21 0:14 
AnswerRe: I need exe only Pin
Igor Krupitsky31-Oct-21 8:48
MemberIgor Krupitsky31-Oct-21 8:48 
GeneralMy vote of 5 Pin
jfragas28-Nov-20 14:10
Memberjfragas28-Nov-20 14:10 
QuestionAn Error Pin
Member 1476738925-Mar-20 4:02
MemberMember 1476738925-Mar-20 4:02 
QuestionThank you, it helped me a lot. Pin
Arilson Brito23-Aug-19 11:02
MemberArilson Brito23-Aug-19 11:02 
PraiseExcelent Code. Thanks. Pin
pancer200825-Oct-18 2:36
Memberpancer200825-Oct-18 2:36 
BugSlight Bug in Datehandling Pin
Member 106212624-Jan-18 23:43
MemberMember 106212624-Jan-18 23:43 
QuestionTables not copying FIXED Pin
Ken Hull22-Nov-17 2:08
MemberKen Hull22-Nov-17 2:08 
SuggestionRe: Tables not copying FIXED Pin
Member 106212625-Jan-18 1:30
MemberMember 106212625-Jan-18 1:30 
QuestionTables with booleans don't copy Pin
Ken Hull21-Nov-17 10:10
MemberKen Hull21-Nov-17 10: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.