Of course, I googled my problem : "visual basic xml to sql how to insert many rows XmlReader". I even changed 'many' to 'multiple'.
So far, I was able to add only the first entry into my database. I was happy to be able to use XmlReader, since I heard it's quite efficient. I was happy as well to be able to use parameters, since I can't concatenate properly anyway (and yes, I like to code elegantly - I do not talk about my clothes here).
My VB.Net code:
Imports System.Data.SqlClient
Imports System.Xml
Public Class Form1
Private Sub ButtonOK_Click(sender As Object, e As EventArgs) Handles ButtonOK.Click
Dim xmlFile As String
Dim connectionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim ds As New DataSet
Dim reader As XmlReader
Dim sqlStatement As String
'Tag names inside xml document, will have to be inserted into table
Dim company As Integer
Dim name As String
Dim streetaddress As String
Dim city As String
Dim status As Char
connectionString = "this connection string works"
xmlFile = "MyXMLFile.xml"
connection = New SqlConnection(connectionString)
reader = XmlReader.Create(xmlFile, New XmlReaderSettings())
ds.ReadXml(reader)
Dim doc As New XmlDocument()
doc.Load(xmlFile)
Dim nodelist As XmlNodeList = doc.SelectNodes(".//siteelement/site")
For Each node As XmlElement In nodelist
Dim i As Integer
For i = 0 To ds.Tables(0).Rows.Count - 1
company = Convert.ToInt32(ds.Tables(0).Rows(i).Item(0))
name = ds.Tables(0).Rows(i).Item(1)
streetaddress = ds.Tables(0).Rows(i).Item(2)
city = ds.Tables(0).Rows(i).Item(3)
status = ds.Tables(0).Rows(i).Item(4)
sqlStatement = "INSERT INTO [dbo].[SITE] ([COMPANY], [NAME], [STREETADDRESS], [CITY], [STATUS])" &
"VALUES (@COMPANY, @NAME, @STREETADDRESS, @CITY, @STATUS)"
command = New SqlCommand(sqlStatement, connection)
command.Parameters.AddWithValue("@COMPANY", company)
command.Parameters.AddWithValue("@NAME", name)
command.Parameters.AddWithValue("@STREETADDRESS", streetaddress)
command.Parameters.AddWithValue("@CITY", city)
command.Parameters.AddWithValue("@STATUS", status)
Try
connection.Open()
Dim rowsAffected As Integer = command.ExecuteNonQuery()
MessageBox.Show(rowsAffected & " entries were inserted into Site table")
'Release the resources
command.Dispose()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'Execute the sql statement against the connection
command.ExecuteNonQuery()
Next
Next
connection.Close()
End Sub
End Class
What my xml file look like:
<?xml version="1.0" encoding="UTF-8"?>
<siteelement>
<site>
<company>1000</company>
<name>STORE #2336</name>
<streetAddress>123 London Calling</streetAddress>
<city>London</city>
<status>I</status>
</site>
<site>
<company>1001</company>
<name>STORE #2332</name>
<streetAddress>123 City of New Orleans</streetAddress>
<city>New Orleans</city>
<status>A</status>
</site>
<site>
<company>1002</company>
<name>STORE #2333</name>
<streetAddress>123 Bla bla bla</streetAddress>
<city>Somewhere</city>
<status>A</status>
</site>
</siteelement>
Question : How can I make my reader reads through all my xml document? What did I miss? What is incorrect?
Any help would be appreciated. Please notice I am completely new to Visual Basic (less than a week!) and if you think I can betterize my code...
Thanks in advance.
On the first iteration of your loop you are opening your SqlConnection, but never closing it. Then, on the next iteration of your loop, you are trying to open your SqlConnection again, which throws the following exception since the connection was already open:
The connection was not closed. The connection's current state is open.
I have made some minor changes to your code to demonstrate the proper way to handle a SqlConnection (or any other object that implements the IDisposable interface).
Dim xmlFile As String
Dim connectionString As String
Dim ds As New DataSet
Dim reader As XmlReader
Dim sqlStatement As String
'Tag names inside xml document, will have to be inserted into table
Dim company As Integer
Dim name As String
Dim streetaddress As String
Dim city As String
Dim status As Char
connectionString = "this connection string works"
xmlFile = "MyXMLFile.xml"
reader = XmlReader.Create(xmlFile, New XmlReaderSettings())
ds.ReadXml(reader)
Dim doc As New XmlDocument()
doc.Load(xmlFile)
Dim nodelist As XmlNodeList = doc.SelectNodes(".//siteelement/site")
For Each node As XmlElement In nodelist
Dim i As Integer
For i = 0 To ds.Tables(0).Rows.Count - 1
company = Convert.ToInt32(ds.Tables(0).Rows(i).Item(0))
name = ds.Tables(0).Rows(i).Item(1)
streetaddress = ds.Tables(0).Rows(i).Item(2)
city = ds.Tables(0).Rows(i).Item(3)
status = ds.Tables(0).Rows(i).Item(4)
sqlStatement = "INSERT INTO [dbo].[SITE] ([COMPANY], [NAME], [STREETADDRESS], [CITY], [STATUS]) VALUES (@COMPANY, @NAME, @STREETADDRESS, @CITY, @STATUS)"
Using connection As New SqlConnection(connectionString)
Using command As New SqlCommand(sqlStatement, connection)
command.Parameters.AddWithValue("@COMPANY", company)
command.Parameters.AddWithValue("@NAME", name)
command.Parameters.AddWithValue("@STREETADDRESS", streetaddress)
command.Parameters.AddWithValue("@CITY", city)
command.Parameters.AddWithValue("@STATUS", status)
Try
connection.Open()
Dim rowsAffected As Integer = command.ExecuteNonQuery()
Console.WriteLine(rowsAffected & " entries were inserted into Site table")
'Release the resources
command.Dispose()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
'Execute the sql statement against the connection
command.ExecuteNonQuery()
End Using
End Using
Next
Next
You'll notice that I wrapped your SqlConnection and SqlCommand in a funny-looking Using block rather than using the Dim keyword. The purpose of the Using block is to ensure that ALL of the resources are properly disposed of when the end of the Using block is reached.
So in my code sample, the SQLConnection object is completely destroyed and re-created on each iteration of the loop and therefore the connection.Open() statement is valid for every iteration.
Obviously destroying and re-creating a new SqlConnection on every iteration is not the most efficient method, but I didn't want to modify your code too much since you said you're still learning.
Alternatively, you could have simply added a line of code to close the connection such as connection.Close(), but I feel that this demonstrates better coding practice.