[dba-VB] [AccessD] Importing XML into a database

Shamil,

First of all, thanks for your assistance on this.  I am so new to VB.Net
that I would never get there on my own, but given example code I can usually
adapt it to my needs.

OK, here is the adaptation I am trying.  The concept is that I create a
class which knows how to serialize an object (class).  The following
function performs that process using XMLSerializer to serialize the current
class onto a stream passed in.

    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

Now, here is your code for using a memory stream as you called it.  This
mSave method uses a connection string and a table name parameter.  You set
up a memory stream and call the mSave method shown above to serialize the
object into that stream.  Next your create a data set which reads the xml
data back out of the stream, which creates a table in the data set using the
xml  file element names as field names in the resulting table (very nice).
Finally the "Using" block of code writes the data now in the data set out to
a table in the database.  I left in the two lines of code (commented out the
original, then modified in a second copy) where I assume I need to
substitute in my passed in table name for your hard coded table names. 

    Public Function mSave(ByVal lcnn As String, ByVal strTblName As String)
As Int16
        Try
            ' serialize DataSet data into memory stream
            ' (use your custom object instance here)
            Dim myStream As System.IO.Stream = New IO.MemoryStream()
            mSave(myStream)

            ' 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(lcnn)
                'Dim cmd As New OleDbCommand("select * from [LogData]")
                Dim cmd As New OleDbCommand("select * from [" & strTblName &
"]")
                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")
                adapter.Update(ds, strTblName )
                cnn.Close()
            End Using
            Return 0
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Return -1
        End Try
    End Function

So with these two methods, I build a clsSerializableData.  This is inherited
by any class that I want to be able to write (and read back in) its data.
Assuming this works (I haven't yet tested it) I now can write any class'
data to a file on disk (using a different existing mSave overload) or to
table (using this new mSave overload).  Is overload the right term here?

Once I get this functioning I need to go back and generate the matching
mLoad() method.  I already have one to load the class from an xml disk file.
Now I have to build one to load it from an existing table in the database.

I will let you know how the testing goes.  I am curious what the data types
will be for this table.  The data types of the class are known to the
xmlSerializer but the data type does not show up in the XML file produced
from the stream.  Is it possible to tell the serializer to include data
types in the xml file?  Is this part of the XML standard?

At any rate, again, thanks a million for your assistance on this Shamil.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Friday, October 05, 2007 6:41 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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