Insert/Update database using XML

Question

0.00/5 (No votes)

See more:

Urgent Help...
Thanks in advance..

Hi, I am doing a project in VB.NET
AIM:- Read xml files from a folder and use this data as a parameter to update or Insert an access databse.

Procedure:
I have the following XML file. First i want to read all xml files from a folder(ie:C:\Backup) and use this table name as a parameter on one table(SM_TRANSFER_DATA) to get Update Query and Insert Query

Then read this XML column values and use this as a parameter for Update or insert query. The real thing i want here is generating parameters dynamically based on xmlcoloumn count.

- <documentelement>
- <employee>
  <empid>320</empid> 
  <accesscode>0</accesscode> 
  <shortname>NJEFF000</shortname> 
  <fullname>jeffrey</fullname> 
  <status>N</status> 
  </employee>
- <employee>
  <empid>333</empid> 
  <accesscode>0</accesscode> 
  <shortname>KAMRAN000</shortname> 
  <fullname>KAMRAN</fullname> 
  <status>N</status> 
  </employee>
- <employee>
  <empid>12</empid> 
  <accesscode>1</accesscode> 
  <shortname>KAMRA</shortname> 
  <fullname>KAMAN</fullname> 
  <status>N</status> 
  </employee>
  </documentelement>

Here is the code what i have:

VB

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        'Declerations of Variable
        '-------------------------
        Dim strRootDir As String = "C:\Backup"
        Dim arr_strFile() As String = Directory.GetFiles(strRootDir, "*.xml")
        Dim dsxml As New DataSet
        Dim xmlfilelen As String = arr_strFile.Length
        Dim j As Integer
        'loop through all xml file in the Backup folder
        '---------------------------------------------
        For j = 0 To xmlfilelen - 1
            cnn.Open()
            dsxml.ReadXml(arr_strFile(j))
            ''Splitting Parts of file Path and getting file name
            '---------------------------------------------------
            Dim fn() As String = arr_strFile(j).Split(New Char() {"\"c})
            Dim sxmlfilename() As String = fn(2).Split(New Char() {"."c})
            Dim Nxmlfilename As String = sxmlfilename(0)
            Dim sSQL As String = "SELECT * FROM SM_TRANSFER_DATA WHERE TR_PARAM = '" & Nxmlfilename & "'"
            Dim cmd As New OleDbCommand(sSQL, cnn)
            cmd.CommandText = sSQL
            cmd.CommandType = CommandType.Text
            Dim dts1 As New DataTable
            dts1.Load(cmd.ExecuteReader)

            For Each drow As DataRow In dts1.Rows
                QryCheck = drow("TR_CHECK_QUERY")
                QryUpdate = drow("TR_UPDATE_QUERY")
                QryInsert = drow("TR_INSERT_QUERY")
                ChkField = drow("TR_CHECK_FIELDS")
            Next


            Try
                Dim dsxml1 As New DataSet
                dsxml1.ReadXml("C:\Backup\" & Nxmlfilename & ".xml)
                For Each xmlrow As DataRow In dsxml1.Tables(0).Rows
                    cmd.CommandText = QryCheck
                    cmd.Parameters.Add(New OleDbParameter("@P1", OleDbType.VarChar)).Value = xmlrow.Item(ChkField)     
                    sSQL = cmd.CommandText
                    Dim reader As OleDbDataReader = cmd.ExecuteReader()

                    If reader.HasRows = True Then
                        cmd.CommandText = QryUpdate
                        For i As Byte = 1 To dsxml1.Tables(0).Columns.Count
                            Dim P As New OleDbParameter("@P" & i + 1, "")
                            If i = dsxml1.Tables(0).Columns.Count Then
                                P.Value = xmlrow.Item(ChkField)
                            Else
                                P.Value = xmlrow(i)
                            End If
                        Next
                        reader.Close()
                        cmd.ExecuteNonQuery()
                    Else
                        cmd.CommandText = QryInsert
                        For i As Byte = 1 To dsxml1.Tables(0).Columns.Count
                            Dim p As New OleDbParameter("@P" & i + 1, "")
                            p.Value = xmlrow(i)
                            cmd.Parameters.Add(p)
                        Next
                        reader.Close()
                        cmd.ExecuteNonQuery()
                    End If
                    reader.Close()
                Next
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
            cnn.Close()
        Next
    
    End Sub

Posted 9-Jun-10 18:11pm

anwarntde

Updated 9-Jun-10 19:09pm

Sandeep Mewara

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wayne Gaylard · Answer 1 · 2010-06-09T22:53:00

First off, nowadays it is unneccessary to use oledb to read XML files, rather, with VB.Net you can use XML literals to parse your XML file. In your case I would be doing something like this :

VB.NET

'Load the XML file
       Dim NewXDoc As XDocument = XDocument.Load("FileName")
       'set up database connection
       Dim conEmployees As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxxx.mdb;")
       'Set up a transaction to commit all inserts at once
       Dim transEmployee As OleDbTransaction
       conEmployees.Open()
       transEmployee = conEmployees.BeginTransaction
       Try
           'iterate through employees in xml doc
           For Each emp In From element In NewXDoc...<employee>
               Dim strSQL As String = "INSERT INTO Employees (empid, accesscode, shortname, fullname, status) VALUES (@EmpId, @AccessCode, @ShortName, @FullName, @Status )"
               Dim cmdEmployees As New OleDbCommand(strSQL, conEmployees)
               cmdEmployees.Transaction = transEmployee
               Dim strID As String = emp.@empid
               cmdEmployees.Parameters.AddWithValue("@EmpID", emp...<empid>.Value)
               cmdEmployees.Parameters.AddWithValue("@AccessCode", emp...<accesscode>.Value)
               cmdEmployees.Parameters.AddWithValue("@ShortName", emp...<shortname>.Value)
               cmdEmployees.Parameters.AddWithValue("@FullName", emp...<fullname>.Value)
               cmdEmployees.Parameters.AddWithValue("@Status", emp...<status>.Value)
               cmdEmployees.ExecuteNonQuery()
           Next
           transEmployee.Commit()
       Catch exOledb As OleDbException
           transEmployee.Rollback()
           'other Error handling here
       Finally
           If conEmployees.State = ConnectionState.Open Then
               conEmployees.Close()
           End If
       End Try

Happy Coding