[AccessD] Importing XML into a database

Hello John,

Yes, I think you can use MemoryStream - here is a sample code (watch line
wraps):

Imports System.Data
Imports System.Data.OleDb
Imports System.Xml.Serialization

Module TestImportXmlFile

    Sub Main()
        Dim rootDir As String = "E:\Temp\XML\"
        Dim accDbFileName As String = "testXML.mdb"
        Try
            Dim accConnection As String = _
                 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _
                 rootDir + accDbFileName

            ' simulate custom object instance by 
            ' using DataSet instance, which data
            ' is loaded from external XMl file
            Dim xmlFileFullPath As String = rootDir + "clsLogData.xml"

            Dim textImportStream As System.IO.StreamReader = _
                  New IO.StreamReader(xmlFileFullPath)
            Dim dsObjectSimulator As New DataSet()
            dsObjectSimulator.ReadXml(textImportStream, _
				XmlReadMode.InferSchema)

            ' serialize DataSet data into memory stream
            ' (use your custom object instance here)
            Dim myStream As System.IO.Stream = New IO.MemoryStream()
            mSave(myStream, dsObjectSimulator)

            ' read memory stream's data into dataset
            Dim ds As New DataSet()
            myStream.Position = 0
            ds.ReadXml(myStream)

            ' write DataSet's data into database table
            Using cnn As New OleDbConnection(accConnection)
                Dim cmd As New OleDbCommand("select * from [LogData]")
                Dim adapter As New OleDbDataAdapter(cmd)
                Dim builder As New OleDbCommandBuilder(adapter)
                cnn.Open()
                cmd.CommandType = CommandType.Text
                cmd.Connection = cnn
                adapter.Update(ds, "clsLogData")
                cnn.Close()
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

    End Sub

    'Public Function mSave(ByVal stream As IO.Stream, ByVal t As Type) As
Boolean
    Public Function mSave(ByVal stream As IO.Stream, ByVal t As Object) As
Boolean
        Dim serializer As New XmlSerializer(t.GetType())
        Try
            serializer.Serialize(stream, t)
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

End Module

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, October 05, 2007 11:54 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Importing XML into a database

Shamil,

I generate the XML file using the serialization code provided by .Net.  

Imports System.IO
Imports System.Xml.Serialization
Public Class clsSerializableData
    ''' <summary>
    ''' Serializes the object to disk
    ''' </summary>
    ''' <param name="strFileName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function mSave(ByVal strFileName As String) As Int16
        '
        'Make a temp filename
        Dim strTmpFileName As String
        strTmpFileName = strFileName & ".tmp"
        Dim TmpFileInfo As New FileInfo(strTmpFileName)

        'check if it exists
        If TmpFileInfo.Exists Then
            Try
                TmpFileInfo.Delete()
            Catch ex As Exception
                Return -1
            End Try
        End If

        '
        'Open the file
        Dim stream As New FileStream(strTmpFileName, FileMode.Create)

        '
        'Save the object
        mSave(stream)

        '
        'Close the object
        stream.Close()

        'Remove the existing file
        Try
            TmpFileInfo.CopyTo(strFileName, True)
        Catch ex As Exception
            Return -2
        End Try
        '
        'Rename the temp file
        Try
            TmpFileInfo.Delete()
        Catch ex As Exception
            Return -3
        End Try
    End Function
    ''' <summary>
    ''' Performs the serialization
    ''' </summary>
    ''' <param name="stream"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function mSave(ByVal stream As Stream) As Boolean
        Dim serializer As New XmlSerializer(Me.GetType)
        Try
            serializer.Serialize(stream, Me)
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function 

What I really want to do is to add code to this class to allow it to
serialize itself to a table.  Your code is tantalizing because it takes the
end result of my serialization (the xml file) and loads it back in to a
table which it then saves back to a data store.  Can that code be adapted to
short cut the actual storage of the data out to a file?  I.e. open a stream
that that the object can serialize itself to, but then hand that stream off
to be immediately read back and stored in a data set?  

.Net has provided a very nice facility for serializing a class, but (AFAIK)
it can only serialize itself to an XML file.  It does so however by
serializing itself onto a stream.  If that stream can be simly held and not
written to file, and then that stream handed back to a dataset to process
into a table I would have the holy grail of serialization to a table.  Any
class could then serialize itself directly into a database table. 

Of course I am completely incapable of actually coding such a solution if it
can even be done.  Or perhaps this is doable directly and I am so ignorant
that I do not even know it?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

<<< tail skipped >>>