[2005] Reading XML file

[kiwis]

I have the below script which works fine, however it takes a very long time to download as my XML file has around 1300 Member nodes...

        Try
            Dim m_xmld As Xml.XmlDocument
            Dim m_nodelist As Xml.XmlNodeList
            Dim m_node As Xml.XmlNode
            m_xmld = New Xml.XmlDocument()
            m_xmld.Load("http://www.mysite.com/application/downloads/members.xml")
            m_nodelist = m_xmld.SelectNodes("/Members/Member")


            For Each m_node In m_nodelist

                Dim Count = m_node.ChildNodes.Item(0).InnerText
                Dim LastUpdated = m_node.ChildNodes.Item(1).InnerText
                Dim MemberID = m_node.ChildNodes.Item(2).InnerText
                Dim UserName = m_node.ChildNodes.Item(3).InnerText
                Dim FirstName = m_node.ChildNodes.Item(4).InnerText
                Dim LastName = m_node.ChildNodes.Item(5).InnerText
                Dim Password = m_node.ChildNodes.Item(6).InnerText
                Dim Level = m_node.ChildNodes.Item(7).InnerText

                If LastUpdated < CurrentTime Then

                    Dim connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DataBase\test.mdb;Jet OLEDB:Database Password=***;"
                    Dim sqlqry As String = "DELETE FROM members WHERE id = " & MemberID
                    Dim conn As Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(connstring)
                    Dim cmd As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand(sqlqry, conn)
                    conn.Open()
                    cmd.ExecuteNonQuery()
                    conn.Close()


                    Dim connstring2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DataBase\test.mdb;Jet OLEDB:Database Password=***;"
                    Dim sqlqry2 As String
                    sqlqry2 = "INSERT INTO members (`id`,`username`,`firstname`,`lastname`,`password`,`level`) Values ('" & MemberID & "','" & UserName & "','" & FirstName & "','" & LastName & "','" & Password & "','" & Level & "')"
                    Dim conn2 As Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(connstring2)
                    Dim cmd2 As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand(sqlqry2, conn2)
                    conn2.Open()
                    cmd2.ExecuteNonQuery()
                    conn2.Close()
                    Status = Status + 1
                    If Status > Update Then
                        Status = Update
                    End If
                    ProgressBar1.Value = Status

                End If
            Next

        Catch errorVariable As Exception
            MessageBox.Show(errorVariable.ToString)
        End Try

How can I make this faster, any ideas???

[bgmacaw]

Dim YourData As New DataSet
YourData.ReadXml(YourSourceUrl)

That will read your remote XML data into a dataset. You may need to apply a schema to the data if it can't be inferred. Then you should be able to use the Update method to save the information in your local DB as long as the schemas match. If they don't or if you need to do the delete, then you'll need to do a loop.

In your loop, should you use one, don't close and open your connections on each iteration. That is a performance killer. You might also use a Using block to insure objects are being released.

[kiwis]

Well not all records will be there, there might be some new rows as well, so update will not work on them.

* So I could open the connection, and leave it open till I'm finished
* Download the data to a dataset then work with the data

Will these two make my script run faster???

[stanav]

I don't know how much faster these will help but they definately help.
1. Turn Option Strict On and fix any errors that pop out.
2. Declare your variables once outside the loop and re-use them inside the loop instead of declare the same variables in every single execution of the loop.