Dump whole dataset to a new access table

[Marcolino]

Hi all,
I have an application that needs to import XML files into Database.
This XML file was generated by this:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim cmdXML As OleDb.OleDbCommand
Dim DScmdXML As OleDb.OleDbDataAdapter
Dim DSXML As New DataSet()

Dim sqlXML As String = "SELECT * FROM List_Piante"
cmdXML = New OleDb.OleDbCommand(sqlXML, conn)
DScmdXML = New OleDb.OleDbDataAdapter(cmdXML)
DScmdXML.Fill(DSXML, "List_Piante")
DSXML.WriteXml("Piante.xml", XmlWriteMode.WriteSchema)
Dim xmlSW2 As System.IO.StreamWriter = New
System.IO.StreamWriter("c:\Piante.xml")
DSXML.WriteXml(xmlSW2, XmlWriteMode.WriteSchema)
xmlSW2.Flush()
xmlSW2.Close()

End Sub

I need to import this XML file into a NEW created ms-access table, but
i don't know how, and i cannot find any helpful suggestion on google.

Many Thanks

 

[Michel Posseth [MCP]]

Well i guess you can do 2 things

1 . really create a database from scratch , you can do this with ADOX (
Com interop in .Net ) as there is no managed libraray availlable wich can do
this

OR

2. Create a empty database , with empty tables in a Access database ( repair
and compress it after doing so in Access ) and now add this as a resource
in your executable so instead of creating a new database , your app simply
copies a empty database from it`s resource and fills this with the data
contained in the XML files ( no need for COM interop , much faster )

I have done option 2 numerous times and can show you an example if you want

Michel

 

[Marcolino]

Well i guess you can do 2 things

1 . really create a database from scratch , you can do this with ADOX   (
Com interop in .Net ) as there is no managed libraray availlable wich can do
this

OR

2. Create a empty database , with empty tables in a Access database ( repair
and compress it after doing so in Access )  and now add this as a resource
in your executable   so instead of creating a new database , your app simply
copies a empty database from it`s resource and fills this with the data
contained in the XML files ( no need for COM interop , much faster )

I have done option 2 numerous times and can show you an example if you want

Michel

"Marcolino" <[email protected]> schreef in bericht








- Mostra testo tra virgolette -

MIchel,
very thanks for your help.
can you provide me the example for option 2?

Thanks

 

[Michel Posseth [MCP]]

MIchel,
very thanks for your help.
can you provide me the example for option 2?

Thanks

Okay here it comes

First go to the properties screen of your project , choose the tab
"resources"
now select "Add resource" ( 2nd menu item ) in this menu select "add
existing file" select your empty database .

The database will now be embedded in the resource manifest on the next
compile


now add this code to your project

Private Function CopyNewDB(Optional ByVal NoMessage As Boolean = False) As
Boolean
Try

If My.Computer.FileSystem.FileExists("mijnistaDB.mdb") Then
bMsg("database bestaat al, deze wordt overschreven")
If My.Computer.FileSystem.FileExists("mijnistaDB.ldb") Then
Try
My.Computer.FileSystem.DeleteFile("mijnistaDB.ldb")
Catch ex As Exception
'file open notify user
' errMsg("Access bestand is reeds geopend , eerst
sluiten a.u.b.")
Return False
End Try
End If
'we gebruiken de huidige database
'Return ClearOldVals()
End If
Try

'start copy from resource manifest
Dim asm As Assembly = Assembly.GetExecutingAssembly
'namespacename.filename.fileExtension

asm.GetManifestResourceStream("GetmijnistaData.mijnistaDB.mdb")

With
asm.GetManifestResourceStream("GetmijnistaData.mijnistaDB.mdb")
Dim count As Integer
Dim byteArray As Byte()

'read first 20 bytes of the stream
byteArray = _
New Byte(CType(.Length, Integer)) {}
count = .Read(byteArray, 0, 20)

'read remaining byte by byte .
While (count < .Length)
byteArray(count) = _
Convert.ToByte(.ReadByte())
count += 1
End While

Using sw As Stream = File.Open("mijnistaDB.mdb",
FileMode.Create, FileAccess.Write)
Using bw As New BinaryWriter(sw)
'schrijf de byte array weg als een file
bw.Write(byteArray)
bw.Close()
End Using
sw.Close()
End Using
End With

Catch ex As Exception
errMsg(String.Concat(Environment.NewLine, ex.ToString,
Environment.NewLine))
Return False
End Try
' finished !!
Return True
Catch ex As Exception
If Not NoMessage Then
Select Case MessageBox.Show(ex.ToString, "Could not copy
file", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error)
Case Windows.Forms.DialogResult.Retry
Return CopyNewDB()
End Select
End If
End Try
End Function

HTH

Michel