jwcolby
jwcolby at colbyconsulting.com
Fri Oct 5 14:54:28 CDT 2007
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
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Friday, October 05, 2007 3:01 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Importing XML into a database
Hello John,
Here is the code to store your XML file (let's call it clsLogData.xml) into
MS Access database "testXML.mdb" in table named [clsLogData] with field
names equal to XML file elements' names:
Imports System.Data
Imports System.Data.OleDb
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
Dim ds As New DataSet()
Dim xmlFileFullPath As String = rootDir + "clsLogData.xml"
ds.ReadXml(xmlFileFullPath)
Using cnn As New OleDbConnection(accConnection)
Dim cmd As New OleDbCommand("select * from [clsLogData]")
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
End Module
Of course XML file has to have a root element if you wanted to import
several records....
--
Shamil
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, October 05, 2007 8:53 PM
To: 'Access Developers discussion and problem solving';
dba-sqlserver at databaseadvisors.com
Subject: [AccessD] Importing XML into a database
I have found / written code in VB.Net to allow a class to write (and read)
it's data out to a text file on disk. I use this ATM to log my import
process. When I am done I have a log file per file imported. I will
eventually be switching to a direct write to a data table but for now I need
to know if I can import these log files into a table. The XML produced by
the code looks like the following (actual file written):
<?xml version="1.0"?>
<clsLogData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<pTblName>AK</pTblName>
<pStatus>Complete</pStatus>
<pCommittedID>205453</pCommittedID>
<pFileSpec>\\Stonehenge\psm\Data\FirstAmerican\TaxRolls\AK.txt</pFileSpec>
<pStartTime>2007-10-03T12:55:49.890375-04:00</pStartTime>
<pStopTime>2007-10-03T12:57:15.49325-04:00</pStopTime>
<pStartID>0</pStartID>
<pStopID>205453</pStopID>
<pMemo>90071218 : 10/3/2007 12:55:49 PM : Read Directory: TaxRolls
90071234 : 10/3/2007 12:55:49 PM : Loading CSVReader10/3/2007 12:55:49 PM
90071234 : 10/3/2007 12:55:49 PM : Read File:
\\Stonehenge\psm\Data\FirstAmerican\TaxRolls\AK.txt
90071250 : 10/3/2007 12:55:49 PM : File Loaded:
\\Stonehenge\psm\Data\FirstAmerican\TaxRolls\AK.txt
90071296 : 10/3/2007 12:55:49 PM : Bulk Copy: tblTaxRolls 90116640 :
10/3/2007 12:56:35 PM : Bulk Copy Finished: tblTaxRolls
90116656 : 10/3/2007 12:56:35 PM : RECORDS READ: 205453
90116656 : 10/3/2007 12:56:35 PM : RECORDS BULK COPIED: 205453
10/3/2007 12:56:35 PM : Copy Temp table AK_X to the permanent table
tblTaxRolls
10/3/2007 12:57:15 PM : Temp table copied AK_X to the permanent table
tblTaxRolls
10/3/2007 12:57:15 PM : DROPPED Temp table AK_X
90156765 : 10/3/2007 12:57:15 PM : Move to Archive:
\\Stonehenge\psm\Data\FirstAmerican\TaxRolls\Archive\AK.txt</pMemo>
</clsLogData>
Can SQL Server just read these files? Does anyone have any VB.Net code to
read a file such as this and append the data to a table?
John W. Colby
Colby Consulting
www.ColbyConsulting.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com