使用XML插入/更新数据库 [英] Insert/Update database using XML

问题描述

紧急帮助...
提前谢谢..

我正在VB.NET中做一个项目
目的:-从文件夹中读取xml文件，并将此数据用作更新或插入访问数据库的参数.

程序:
我有以下XML文件.首先，我想从一个文件夹(即:C:\ Backup)中读取所有xml文件，并将此表名用作一个表(SM_TRANSFER_DATA)上的参数，以获取更新查询和插入查询

然后读取此XML列值，并将其用作更新或插入查询的参数.我在这里想要的真正的事情是基于xmlcoloumn计数动态生成参数.

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:

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

推荐答案

首先，如今，不必使用oledb来读取XML文件，而通过VB.Net，您可以使用XML文字来解析您的XML文件.在您的情况下，我会做这样的事情:

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 :

'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

这篇关于使用XML插入/更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！