daheads blog²

Ich habe heute nach einer Möglichkeit gesucht, Daten, die ich aus einer SQL Tabelle eines Servers "A" in ein XML-Dataset geschrieben habe, wieder aus diesem XML Dataset zurück in die SQL Tabelle auf dem Server "B" zu schreiben.

Aufgabe war, vier Tabellen von unserem Firmenserver auf die Server draußen bei unseren Mitarbeiter per Programm zu übertragen.

Mein Hauptproblem bei solchen Aufgaben ist eigentlich immer das Erstellen der SQL-Befehle fürs Updaten oder Inserten. Wobei, Problem ist eventuell zu viel gesagt, viel eher nervt es, manuell diese Statements zusammenbauen zu müssen.

Da .NET ja intern über die TableAdapter und DataProvider die Funktion besitzt, eben diese SQL Befehle automatisch zu generieren, dachte ich mir, warum nicht diese Fähigkeit nutzen und mir damit das Getippe und das Prüfen der selbst erstellten SQL Statements zu ersparen.

Die Hauptarbeit in der wichtigsten Funktion ImportTableToSQL übernimmt dabei die Klasse SQLCommandBuilder, die einen DataAdapter mit dem einzutragenden DataSet übergeben bekommt. Anschließend weisen wir dem SQLCommandBuilder für alle Parameter einfach die Werte zu, die in unserem DataSet bereits drin stehen.

Zu guter letzt führen wir den automatisch generierten Insert-Befehl des SQLCommandBuilders aus, et voila, die Tabellen sind aktuell.

Hier der Code, um Daten aus dem SQL-Server in ein XML Dataset zu schreiben. Die wichtigste Stelle hierbei ist Dataset.WriteXML().

''' <summary>
  ''' Exports a Table from a SQL Database to a XML file
  ''' </summary>
  ''' <param name="aSelectStatement">Select Statement for the data which should be exported</param>
  ''' <param name="aTableName">Name of the Table (needed for creating the filename)</param>
  ''' <param name="aOutputXMLDirectory">Ausgabe-Verzeichnis</param>
  ''' <param name="aOutputXMLFilename">Ausgabe-Dateiname ohne Dateiendung und ohne Pfadangabe</param>
  ''' <param name="aDatasetName"></param>
  ''' <param name="Append">An bestehende XML-Datei anhängen (INFER-Modus)</param>
  ''' <remarks></remarks>
  Public Shared Sub ExportTable(ByVal strCon As String, _
                                ByVal aSelectStatement As String, _
                                ByVal aTableName As String, _
                                ByVal aOutputXMLDirectory As String, _
                                Optional ByVal aOutputXMLFilename As String = "", _
                                Optional ByVal aDatasetName As String = "NewDataSet", _
                                Optional ByVal CreateSchemaFiles As Boolean = False, _
                                Optional ByVal Append As Boolean = False)
 
    Using con As SqlClient.SqlConnection = New SqlClient.SqlConnection(strCon)
 
      Try
        con.Open()
        Dim ds As New DataSet(aDatasetName)
        Dim da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(aSelectStatement, con)
 
        da.FillSchema(ds, SchemaType.Source, aTableName)
        da.Fill(ds, aTableName)
 
        ' Create Filename
        Dim strFullFilename As String
 
        ' Trailing backslash?
        If Not aOutputXMLDirectory.EndsWith("\") Then aOutputXMLDirectory += "\"
 
        ' Create dir
        If Not System.IO.Directory.Exists(aOutputXMLDirectory) Then
          System.IO.Directory.CreateDirectory(aOutputXMLDirectory)
        End If
 
        ' Filename given?
        If aOutputXMLFilename = "" Then
          If aTableName = "" Then
            aTableName = My.Computer.FileSystem.GetTempFileName
          End If
          aOutputXMLFilename = aTableName
        End If
 
        ' Create filename
        strFullFilename = aOutputXMLDirectory & aOutputXMLFilename
 
        ' If XML-File already exists use it and add information to it
        If Append Then
          If System.IO.File.Exists(strFullFilename & ".XML") Then
            ds.ReadXml(strFullFilename & ".XML", XmlReadMode.InferSchema)
          End If
        End If
 
        ' Write XML
        ds.WriteXml(strFullFilename & ".XML")
 
        ' Write Schema?
        If CreateSchemaFiles Then ds.WriteXmlSchema(strFullFilename & ".XSD")
 
        ' Done
      Catch ex As Exception
        Debug.Print(ex.message)
      End Try
 
    End Using
  End Sub
end

Die folgende Funktion durchsucht ein Verzeichnis nach XML- und XSD-Dateien. Aus den gefundenen Dateien wird ein Dataset erstellt und dies weiter an die Prozedur ImportDatasetToSQL gegeben.

''' <summary>
  ''' Importiert Daten aus XML Dateien in eine SQL Server Tabelle
  ''' </summary>
  ''' <param name="aXMLSourceDirectory">Quellverzeichnis der XML-Dateien, die importiert werden sollen</param>
  ''' <param name="aSQLConnectionString">SQL Verbindungsstring</param>
  ''' <remarks></remarks>
  Public Shared Sub ImportTablesToSQL(ByVal aXMLSourceDirectory As String, ByVal aSQLConnectionString As String)
 
    Dim filters As String() = {"*.XML"}
 
    Dim lstFiles As System.Collections.ObjectModel.ReadOnlyCollection(Of String) = _
    My.Computer.FileSystem.GetFiles(aXMLSourceDirectory, FileIO.SearchOption.SearchAllSubDirectories, filters)
 
    For Each fn As String In lstFiles
 
      Dim ds As New DataSet
 
      Try
 
        ' Read the schema
        ' Filename is the same as the xml file except its extension
        ds.ReadXmlSchema(fn.Replace(".XML", ".XSD"))
 
        ' Read the XML File
        ds.ReadXml(fn)
 
        'For Each tbl As DataTable In ds.Tables
        '  Debug.Print("Table: " & tbl.TableName)
        '  For Each cc As System.Data.Constraint In tbl.Constraints
        '    Debug.Print("Constraint: " & cc.ConstraintName)
        '  Next
        '  For Each pkcol As DataColumn In tbl.PrimaryKey
        '    Debug.Print("Primary Key: " & pkcol.ColumnName)
        '  Next
        'Next

        ImportDatasetToSQL(ds, aSQLConnectionString)
 
      Catch ex As Exception
        Debug.Print(ex.Message)
        Continue For
      End Try
 
    Next
 
  End Sub
end

Diese Funktion importiert jede Tabelle des Datasets in die SQL-Tabelle mit dem selben Namen.

Private Shared Sub ImportDatasetToSQL(ByRef aDataset As DataSet, ByVal aSQLConnectionString As String)
 
    Using con As New SqlClient.SqlConnection(aSQLConnectionString)
 
      Try
        con.Open()
      Catch ex As Exception
        Debug.Print(ex.Message)
        Exit Sub
      End Try
 
      Try
 
        For Each tbl As DataTable In aDataset.Tables
          ImportTableToSQL(tbl, con)
        Next
 
      Catch ex As Exception
        Debug.Print(ex.Message)
      End Try
 
      con.Close()
    End Using
 
  End Sub
end

Hier nun die eigentliche Import-Funktion.

Wir übergeben hier ein DataTable und einen SQL Connectionstring. Im DataTable sind die Werte drin, die in die SQL Datenbank geschrieben werden sollen.

Private Shared Sub ImportTableToSQL(ByRef aDataTable As DataTable, ByRef aConnection As SqlClient.SqlConnection)
 
    ' Create a command for the DataAdapter
    Dim cmd As New SqlClient.SqlCommand("Select * From " & aDataTable.TableName, aConnection)
 
    ' Create the DataAdapter (needed for creating the sql-statement)
    Dim da As New SqlClient.SqlDataAdapter(cmd)
 
    ' Fill our DataAdapter with Data from our DataTable
    da.FillSchema(aDataTable, SchemaType.Source)
    da.Fill(aDataTable.DataSet, aDataTable.TableName)
 
    ' Create the SQL Command Builder
    Dim cb As New SqlClient.SqlCommandBuilder(da)
    cb.SetAllValues = False
 
    ' Go thru all rows and insert the data
    For Each r As DataRow In aDataTable.Rows
 
      ' Create the Insert-Command
      Dim cmdResult As New SqlClient.SqlCommand(cb.GetInsertCommand.CommandText, aConnection)
      cmdResult.Parameters.Clear()
 
      Dim i As Integer = 0
 
      ' Go thru all columns in our DataTable and create a SQLParameter for our Statement with the value
      ' from our DataTable.
      For Each col As DataColumn In aDataTable.Columns
        i += 1
        ' cmdResult.Parameters.Add("@p" & i, col.DataType).Value = r.Item(col.ColumnName)
        cmdResult.Parameters.AddWithValue("@p" & i, r.Item(col.ColumnName))
      Next
 
      Debug.Print("ImportTableToSQL: " & cmdResult.CommandText)
 
      Dim iAffectedRecords As Integer = cmdResult.ExecuteNonQuery()
      Debug.Print("Records Affected: " & iAffectedRecords)
 
    Next
 
  End Sub
end

Achja, wie immer gilt, gerne darf man mir Verbesserungen, Anregungen und natürlich auch Kritik zukommen lassen.