Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server

Paradox to SQL Server

Rate me:
Please Sign up or sign in to vote.
5.00/5 (6 votes)
19 Sep 2017CPOL1 min read 29.5K   1.3K   7   21
Application lets you copy tables from Paradox to SQL Server

  

Introduction

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

Background

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.

VB.NET
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 & ";"
    Else
        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+".

VB.NET
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)
    Next

    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))
        Next

        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
                OpenConnections(cnDst)
                ExecuteSql(sSql1, cnDst)
                iRowCount = 0
                sValues = ""
            End If
        Else
            Dim sSql1 As String = "INSERT INTO " & PadSqlColumnName(sTableName) & _
                                  " (" & sHeader & ") VALUES (" & sRow & ")"
            OpenConnections(cnDst)
            ExecuteSql(sSql1, cnDst)
        End If

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

        'Listen for the user to press Cancel button
        Windows.Forms.Application.DoEvents()
        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.

VB.NET
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
            sb.Append(",")
            sb.Append(vbCrLf)
        End If

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

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

        i += 1
    Next

    sb.Append(")")

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

End Function

License

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

 
Questionerror on copying tables Pin
ErikPerik19-Dec-23 0:43
ErikPerik19-Dec-23 0:43 
QuestionTables created but no data filled Pin
Member 159170227-Feb-23 6:41
Member 159170227-Feb-23 6:41 
AnswerRe: Tables created but no data filled Pin
Member 159170228-Feb-23 0:08
Member 159170228-Feb-23 0:08 
AnswerRe: Tables created but no data filled Pin
Igor Krupitsky8-Feb-23 6:24
mvaIgor Krupitsky8-Feb-23 6:24 
QuestionError 11265 executing dataset fill Pin
grefu30-Nov-22 23:00
grefu30-Nov-22 23:00 
AnswerRe: Error 11265 executing dataset fill Pin
grefu1-Dec-22 1:20
grefu1-Dec-22 1:20 
QuestionUnexpected error from external database driver (11010) Pin
Loek Huijs18-Oct-22 20:52
Loek Huijs18-Oct-22 20:52 
AnswerRe: Unexpected error from external database driver (11010) Pin
Loek Huijs19-Oct-22 0:22
Loek Huijs19-Oct-22 0:22 
QuestionNot show data Pin
xblopa7-Sep-22 8:21
xblopa7-Sep-22 8:21 
AnswerRe: Not show data Pin
Igor Krupitsky8-Sep-22 4:47
mvaIgor Krupitsky8-Sep-22 4:47 
Questiondate / datetime / timestamp conversion Pin
Allan Batteiger15-Oct-21 12:23
Allan Batteiger15-Oct-21 12:23 
QuestionI need exe only Pin
Bartolomeo Giorgio9-May-21 23:14
Bartolomeo Giorgio9-May-21 23:14 
AnswerRe: I need exe only Pin
Igor Krupitsky31-Oct-21 7:48
mvaIgor Krupitsky31-Oct-21 7:48 
GeneralMy vote of 5 Pin
jfragas28-Nov-20 13:10
jfragas28-Nov-20 13:10 
QuestionAn Error Pin
Member 1476738925-Mar-20 3:02
Member 1476738925-Mar-20 3:02 
QuestionThank you, it helped me a lot. Pin
Arilson Brito23-Aug-19 10:02
Arilson Brito23-Aug-19 10:02 
PraiseExcelent Code. Thanks. Pin
pancer200825-Oct-18 1:36
pancer200825-Oct-18 1:36 
BugSlight Bug in Datehandling Pin
Member 106212624-Jan-18 22:43
Member 106212624-Jan-18 22:43 
QuestionTables not copying FIXED Pin
Ken Hull22-Nov-17 1:08
Ken Hull22-Nov-17 1:08 
SuggestionRe: Tables not copying FIXED Pin
Member 106212625-Jan-18 0:30
Member 106212625-Jan-18 0:30 
QuestionTables with booleans don't copy Pin
Ken Hull21-Nov-17 9:10
Ken 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.