Shamil Salakhetdinov
shamil at users.mns.ru
Sat Oct 6 12:06:10 CDT 2007
Hello John,
Yes, your code should work with some fixes:
Imports System.Data
Imports System.Data.OleDb
Imports System.Xml.Serialization
Imports System.IO
Namespace TEST2
''
'' sample
''
''Sub Main()
'' Dim rootDir As String = "E:\Temp\"
'' Dim accDbFileName As String = "testXML.mdb"
'' Dim accConnection As String = _
'' "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _
'' rootDir + accDbFileName
'' Try
'' Dim o As New TEST2.myTestClass()
'' o.mSave(accConnection, "tblTEST")
'' Catch ex As Exception
'' Console.WriteLine(ex.Message)
'' End Try
''End Sub
''' <summary>
''' Custom serializer
''' </summary>
''' <remarks></remarks>
Public MustInherit Class CustomSerializer
Protected Function mSave(ByVal stream As Stream) As String
Dim className As String = Me.GetType().ToString()
Dim serializer As New XmlSerializer(Me.GetType)
Try
serializer.Serialize(stream, Me)
Return className
Catch ex As Exception
Return ""
End Try
End Function
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()
Dim className As String = mSave(myStream)
If (className.Length = 0) Then
Throw New ApplicationException("Can't persist object
data to memory stream")
End If
Dim dotIndex As Integer = className.LastIndexOf(".")
If (dotIndex > 0) Then className =
className.Substring(dotIndex + 1)
' 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 [" &
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, className)
cnn.Close()
End Using
Return 0
Catch ex As Exception
Console.WriteLine(ex.Message)
Return -1
End Try
End Function
End Class
''' <summary>
''' Sample test class
''' </summary>
''' <remarks></remarks>
Public Class myTestClass
Inherits CustomSerializer
Public Sub New()
' needed for XML serializer
End Sub
Public One As String = "1"
Public Two As String = "2"
End Class
End Namespace
<<<
Is it possible to tell the serializer to include data
types in the xml file? Is this part of the XML standard?
>>>
John, have a look in MSDN => XmlSerializer Constructor - it has many
overloads, some of them allow to define RootElement....
--
Shamil
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Saturday, October 06, 2007 5:15 PM
To: 'Access Developers discussion and problem solving'
Cc: dba-vb at databaseadvisors.com
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com