Daten aus ODBC-Datenquelle in SQLite-Datenbank schreiben (VB.NET) In diesem Workshop werden Funktionen vorgestellt, mit denen sich das Ergebnis eines SELECT-SQLs, welches auf einer beliebigen ODBC-Datenquelle ausgeführt werden kann, in eine SQLite-Datenbank zu schreiben.
Autor:  Carsten Stuplich	Bewertung:     [ Jetzt bewerten ]	Views:  21.621

Geben Sie hier Ihre persönliche Bewertung ab! Anzahl Bewertungen bisher: 4 Bewertung insgesamt: sehr gut Durchschnittliche Bewertung: 5.0 von 5 Sternen

sehr gut     gut     nützlich     mittelmäßig     mangelhaft

 SQLite ist wie Microsoft-Access ein rein filebasiertes Datenbankmanagementsystemen (DBMS), benötigt also keinen Server. Ein großer Vorteil von SQLite gegenüber MS-Access ist, das SQLite den „ACID-Eigenschaften“ entspricht und zudem bis zu 2 Terrabyte große Dateien unterstützt (Access max. 2GB).

Die folgende Funktion ermöglicht es, das Ergebnis eines SELECT-SQLs, welches auf einer beliebigen ODBC-Datenquelle ausgeführt werden kann, in eine SQLite-Datenbank zu schreiben. Hierfür nutze ich den kostenlos erhältlichen  „SQLite NET-Provider“ , den Sie in Form der „System.Data.SQLite.dll“ in Ihr Projekt (über „Verweise“) einbinden müssen.

Den kompletten Code hab ich in eine neue Klasse ImportDBUtils eingefügt:

Public NotInheritable Class ImportDBUtils

  ''' <summary>
  ''' Mit Hilfe dieser Funktion können Sie Daten aus einer ODBC-Datenbank in eine 
  ''' SQLite-Datenbank schreiben
  ''' </summary>
  ''' <param name="source">Quell-Datenbank</param>
  ''' <param name="destination">Ziel- SQLite-Datenbank 
  ''' (inkl. Dateiname für SQLite) Datenbank</param>
  ''' <param name="sourceSQL">SELECT SQL, das auf der Source Datenbank ausgeführt wird 
  ''' und die gewünschten Daten zurückliefert</param>
  ''' <param name="destinationTableName">Name der Zieltabelle (Wenn schon vorhanden, 
  ''' werden die Daten hinzugefügt, andernfalls wird die Tabelle neu angelegt.)</param>
  ''' <param name="LogInsertErrors">Wenn True, werden INSERT-Fehler in der Tabelle 
  ''' "IMPORTERRORS_Tabellenname" geloggt.</param>
  ''' <param name="CloseDestinationConnectionOnEnd">Bestimmt ob die Verbindung 
  ''' Ziel-Datenbank am Ende geschlossen werden soll</param>
  ''' <param name="CloseSourceConnectionOnEnd">Bestimmt ob die Verbindung 
  ''' Quell-Datenbank am Ende geschlossen werden soll</param>
  ''' <param name="AbortOnInsertError">Bricht den Import ab, sobald ein Fehler beim 
  ''' Einfügen einer Zeile auftritt.</param>
  ''' <param name="UseCharForDBTypeString">Damit bei Zahlen keine führenden Nullen 
  ''' (z.B. 00001234) abgeschnitten werden, muss ein CHAR-Feld anstatt ein String-Feld 
  ''' verwendet werden. Wenn diese Option True ist, wird Char anstatt String verwendet.</param>
  ''' <returns>True/False (True = import erfolgreich)</returns>
  ''' <param name="iCommandTimeout">Bestimmt den CommandTimeout für SELECT- und 
  ''' INSERT-SQL</param>
  ''' <param name="NumberOfRowsForInsertTransaction">Bestimmt die Anzahl Zeilen, die 
  ''' beim INSERT gesammelt werden, bevor eine Transaktion ausgeführt wird. Bei 0 werden 
  ''' die Daten mit nur einer Transaktion eingefügt. Eventuell ist es nötig, bei grossen 
  ''' Datenmengen zwichendurch ein Commit auszuführen. In diesem Fall empfiehtl sich ein 
  ''' Wert zwichen 100.000 und 200.000, da bei zu vielen  Transaktionen (kleiner Wert) 
  ''' die Laufzeit der Funktion steigt.</param>
  Public Function CopyODBCDataToSQLite(ByVal source As IDbConnection, _
    ByVal destination As SQLite.SQLiteConnection, _
    ByVal sourceSQL As String, _
    ByVal destinationTableName As String, _
    Optional ByVal LogInsertErrors As Boolean = True, _
    Optional ByVal CloseDestinationConnectionOnEnd As Boolean = True, _
    Optional ByVal CloseSourceConnectionOnEnd As Boolean = True, _
    Optional ByVal AbortOnInsertError As Boolean = True, _
    Optional ByVal UseCharForDBTypeString As Boolean = True, _
    Optional ByVal iCommandTimeout As Integer = 30, _
    Optional ByVal NumberOfRowsForInsertTransaction As Long = 0) As Boolean
 
    Dim cmd As IDbCommand = source.CreateCommand()
    Dim oDbTransaction As SQLite.SQLiteTransaction = Nothing
    Dim lTableColumnCounter As Long = 0
    Dim iRowsCounterRowsInTransaction As Long = 0
 
    CopyODBCDataToSQLite = True ' Estmal Rückgabe-Ergebnis auf True setzen
    cmd.CommandText = sourceSQL
    cmd.CommandTimeout = iCommandTimeout
 
    Try
      ' Verbindung zu DB aufbauen, wenn noch nicht offen.
      If destination.State = ConnectionState.Closed Then destination.Open()
      If source.State = ConnectionState.Closed Then source.Open()
 
      Dim rdr As IDataReader = cmd.ExecuteReader()
      Dim schemaTable As DataTable = rdr.GetSchemaTable()
      Dim insertCmd As SQLite.SQLiteCommand = destination.CreateCommand()
      Dim insertERRORCmd As SQLite.SQLiteCommand = destination.CreateCommand()
      Dim paramsSQL As String = [String].Empty
 
      Dim typOrderDateType As DbType
      Dim sCreateTable As String = "CREATE TABLE [" & destinationTableName & "] ("
 
      ' Durchläuft alle Spalten des SQlect-SQLs
      For Each row As DataRow In schemaTable.Rows
        typOrderDateType = ConvertODBCTypeToDBType(row("ProviderType"))
 
        ' Insert Statement erzeugen
        If paramsSQL.Length > 0 Then paramsSQL &= ", "
        paramsSQL &= "@" + row("ColumnName").ToString()
        Dim param As SQLite.SQLiteParameter = insertCmd.CreateParameter()
        With param
          .ParameterName = "@" & row("ColumnName").ToString()
          .SourceColumn = row("ColumnName").ToString()
          .DbType = typOrderDateType
        End With
        insertCmd.Parameters.Add(param)
 
        ' CreateTable-Script erzeugen
        lTableColumnCounter += 1
        If lTableColumnCounter > 1 Then sCreateTable &= ", "
 
        ' Wenn DBType String ist, soll Char anstatt String verwendet werden, 
        ' damit(bei) '00001234' nicht die Nullen gelöscht werden.
        If UseCharForDBTypeString = True AndAlso _
          (typOrderDateType <> DbType.String And _
          typOrderDateType <> DbType.StringFixedLength) Then
          sCreateTable &= "[" & row("ColumnName") & "] " & typOrderDateType.ToString
        Else
          sCreateTable &= "[" & row("ColumnName") & "] CHAR"
        End If
 
        ' If typOrderDateType = DbType.StringFixedLength OrElse _
        ' typOrderDateType = DbType.AnsiStringFixedLength OrElse _
        ' typOrderDateType = DbType.String Then _
        ' sCreateTable &= "(" & row("ColumnSize") & ")"
        sCreateTable &= "(" & row("ColumnSize") & ", " & row("NumericPrecision") & ")"
        If CBool(row("AllowDBNull")) = False Then sCreateTable &= " NOT NULL"
        If CBool(row("IsKey")) = True Then sCreateTable &= " PRIMARY KEY"
      Next
      sCreateTable &= ");"
 
      ' Ziel-Tabelle anlegen
      Dim oCreateTableCommand As SQLite.SQLiteCommand = destination.CreateCommand
      Try
        oCreateTableCommand.CommandText = sCreateTable
        oCreateTableCommand.ExecuteNonQuery()
      Catch ex As Exception
        ' Fehler bei erstellen der Tabelle
        ' keinen Fehler auslösen - Fehler eventuell weil Tabelle schon vorhonden
        ' Andernfalls wird ein Fehler beim Insert ausgelöst wenn Tabelle nicht vorhanden
      End Try
 
      ' Transcation erstellen (ist scneller wenn man alle Insert in 
      ' einer Transaction abschickt)
      oDbTransaction = destination.BeginTransaction()
      insertCmd.Transaction = oDbTransaction
 
      ' Insert-Commnad erzeugen
      With insertCmd
        .CommandText = [String].Format( _
          "insert into [{0}] ( {1} ) values ( {2} );", _
          destinationTableName, _
          paramsSQL.Replace("@", [String].Empty), paramsSQL)
        .CommandTimeout = iCommandTimeout
      End With
      ' Insert-Command für Fehler Tabelle erzeugen
      With insertERRORCmd
        .CommandText = "INSERT INTO [IMPORTERRORS_" & _
          destinationTableName.ToUpper & "] " & _
          "(ERROR_DATETIME, ERROR_ROW, ERROR_MSG) " & _
          "VALUES (datetime('now'), @ErrorRow, @ErrorMsg);"
        .Parameters.Add(New SQLite.SQLiteParameter("@ErrorRow"))
        .Parameters.Add(New SQLite.SQLiteParameter("@ErrorMsg"))
        .CommandTimeout = iCommandTimeout
      End With
 
      Dim CountErrors As Long = 0
      Dim RowCounter As Long = 1
 
      While rdr.Read()
        Try
          ' Value Felder den Parametern hinzufügen
          For Each param As IDbDataParameter In insertCmd.Parameters
            param.Value = rdr(param.SourceColumn)
          Next
 
          insertCmd.ExecuteNonQuery()
          iRowsCounterRowsInTransaction += 1
 
          ' Transaction ausführen bei X Zeilen (optional)
          If NumberOfRowsForInsertTransaction <> 0 AndAlso _
            iRowsCounterRowsInTransaction = NumberOfRowsForInsertTransaction Then
 
            ' Transaction ausführen
            oDbTransaction.Commit()
 
            ' Neue Transaction beginnen
            oDbTransaction = destination.BeginTransaction()
            iRowsCounterRowsInTransaction = 0
          End If
 
        Catch ex As Exception
          ' Fehler bei INSERT-Statement
          If LogInsertErrors = True Then
            If CountErrors = 0 Then
              ' Tabelle erstellen, in der Insert-Fehler geloggt wird
              Try
                Dim oCreateErrorTableCommand As SQLite.SQLiteCommand = _
                  destination.CreateCommand()
                oCreateErrorTableCommand.CommandText = _
                  "CREATE TABLE [IMPORTERRORS_" & _
                  destinationTableName.ToUpper & "] " & _
                  "(ERROR_DATETIME DATETIME, ERROR_ROW INTEGER, ERROR_MSG TEXT);"
                oCreateErrorTableCommand.ExecuteNonQuery()
              Catch ex2 As Exception
              End Try
            End If
            ' Fehler in Tabelle schrieben
            Try
              With insertERRORCmd
                .Parameters.Item(0).Value = RowCounter
                .Parameters.Item(1).Value = "Fehler beim Einfügen der Zeile: " & _
                  ex.Message.Replace(vbCrLf, " ")
              End With
              insertERRORCmd.ExecuteNonQuery()
            Catch ex3 As Exception
            End Try
          End If
 
          CountErrors += 1
          If AbortOnInsertError = True Then
            CopyODBCDataToSQLite = False
            Exit While
          End If
        End Try
        RowCounter += 1
      End While
 
    Catch ex As Exception
      CopyODBCDataToSQLite = False
    End Try
 
    ' Transaction ausführen
    If oDbTransaction IsNot Nothing Then oDbTransaction.Commit()
 
    ' Datenbank Verbindungen schließen wenn gewünscht.
    If CloseDestinationConnectionOnEnd = True Then
      destination.Close()
      destination.Dispose()
    End If
    If CloseSourceConnectionOnEnd = True Then
      source.Close()
      source.Dispose()
    End If
  End Function

  ''' <summary>
  ''' Diese Funktion wandelt einen ODBCType in den entsprechenden DBType um
  ''' </summary>
  Private Function ConvertODBCTypeToDBType(ByVal oODBCType As Odbc.OdbcType) As DbType
    Dim oDBType As DbType
 
    Select Case oODBCType
      Case Odbc.OdbcType.BigInt
        oDBType = DbType.Int64
      Case Odbc.OdbcType.Binary
        oDBType = DbType.Binary
      Case Odbc.OdbcType.Bit
        oDBType = DbType.Boolean
      Case Odbc.OdbcType.Char
        oDBType = DbType.String
      Case Odbc.OdbcType.Date
        oDBType = DbType.Date
      Case Odbc.OdbcType.DateTime
        oDBType = DbType.DateTime
      Case Odbc.OdbcType.Decimal
        oDBType = DbType.Decimal
      Case Odbc.OdbcType.Double
        oDBType = DbType.Double
      Case Odbc.OdbcType.Image
        oDBType = DbType.Object
      Case Odbc.OdbcType.Int
        oDBType = DbType.Int32
      Case Odbc.OdbcType.NChar
        oDBType = DbType.StringFixedLength
      Case Odbc.OdbcType.NText
        oDBType = DbType.StringFixedLength
      Case Odbc.OdbcType.Numeric
        oDBType = DbType.UInt64
      Case Odbc.OdbcType.NVarChar
        oDBType = DbType.String
      Case Odbc.OdbcType.Real
        oDBType = DbType.Single
      Case Odbc.OdbcType.SmallDateTime
        oDBType = DbType.DateTime
      Case Odbc.OdbcType.SmallInt
        oDBType = DbType.Int16
      Case Odbc.OdbcType.Text
        oDBType = DbType.String
      Case Odbc.OdbcType.Time
        oDBType = DbType.Time
      Case Odbc.OdbcType.Timestamp
        oDBType = DbType.DateTime
      Case Odbc.OdbcType.TinyInt
        oDBType = DbType.Int16
      Case Odbc.OdbcType.UniqueIdentifier
        oODBCType = DbType.Guid
      Case Odbc.OdbcType.VarBinary
        oODBCType = DbType.Binary
      Case Odbc.OdbcType.VarChar
        oODBCType = DbType.AnsiString
      Case Else
        ' Wenn Type nicht explezit gemappt wurde, dann über 
        ' Ctype nächsten passenden Typ finden
        oDBType = CType(oODBCType, DbType)
    End Select
 
    Return oDBType
  End Function

End Class

Beispiel:

' Connection zu SQLite öffnen
' (In Connection-String wird angegeben, dass Datei 
' erzeugt werden soll, wenn nicht vorhanden)
Dim oSQLiteConnection As SQLite.SQLiteConnection = New SQLite.SQLiteConnection
oSQLiteConnection.ConnectionString = "Data Source=test.db;" & _
  "New=True;Version=3;Pooling=False;Compress=True"
 
' Connection zu Source (ODBC)
Dim oODBCConnection As Odbc.OdbcConnection = New Odbc.OdbcConnection
oODBCConnection.ConnectionString = "DSN=MeineDSN;UID=User;PWD=Geheim"
 
' Daten aus SourceSQL in SQLite-Datenbank schreiben
Dim oImportDBUtils As ImportDBUtils = New ImportDBUtils
oImportDBUtils.CopyODBCDataToSQLite(oODBCConnection, oSQLiteConnection, _
  "SELECT * from MeineTabelle; commit;", "IMPORT_DATEN")

Anmerkungen:
Der Import benötigt einiges an CPU, da die Daten Zeilenweise in die SQLite-Datenbank geschrieben werden müssen. Falls jemand Optimierungsmöglichkeiten findet, die die CPU entlastet und/oder die Laufzeit verringert, bitte ich um Rückmeldung.