Paradox 到 SQL Server

  ↵

• 下载源代码 - 245.8 KB

引言

我开发这个应用程序是为了帮助我将 Paradox 数据库迁移到 SQL Server。希望其他人也能觉得这段代码有用。

背景

这个应用程序使用了 Petr Briza 开发的 "Paradox 数据库原生 .NET 读取器" 库。它非常简单：您选择 Paradox 数据库文件所在的文件夹，选择要复制表的 SQL Server 数据库，选择要复制的表，然后单击“复制表”。应用程序将在 SQL Server 数据库中创建表并复制数据。如果它们位于网络驱动器上，它还会尝试将文件复制到本地。

Using the Code

该应用程序使用 Petr Briza 库来计算每个表的记录数。它使用 Microsoft Jet OLEDB 提供程序以 32 位模式读取数据。该提供程序在 64 位下无法工作。这就是为什么它以 32 位模式编译的原因。

Pradox DB 文件可以设置密码保护。该应用程序使用 JET OLEDB 连接字符串属性“Jet OLEDB:Database 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 & ";"
        Else
            Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFolderPath & ";_
            Extended Properties=Paradox 5.x;"
        End If
    End Function 

CopyTableJet 函数执行实际的数据复制工作。如果您选择“SQL Ser 2008+”，它将一次插入 1000 条记录。

    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 函数将在 SQL Server 中不存在时创建表。

    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