(如何)使用XDocument读取XML文件并建立动态SQL查询? [英] (How to)Read XML file using XDocument and build dynamic SQL query?

问题描述

您好，我在这里编写了一个函数ReadXML().该函数读取XML文件并返回一个字符串.该字符串包含所有XML文件节点值以动态构建SQL查询.无论如何，我的代码都可以正常工作.这是正确的方法吗?您能否建议我此函数中不必要的代码(循环...)，以便我可以更好地进行代码处理.

这是函数.

Hi, Here I wrote a function ReadXML().This function reads XML file and returns a string.The string contains all the XML file nodes values to build SQL query dynamically. Any way My code is working. Is this right way? Can you please suggest me the unnecessary code(loops...) in this function, So that I can do the code in a better way.

Here is the function.

Public Function ReadXML(ByVal tblName As String) As String

        Dim strBuil As New StringBuilder
        Dim listSource As New List(Of String)
        Dim listTarget As New List(Of String)
        Dim dictionary As New Dictionary(Of String, String)
        Dim xmlDoc As XDocument = XDocument.Load("C:\\MappingFile.xml")

        Dim q = (From c In xmlDoc.Descendants("Entity") Where c.Attribute("Source").Value = tblName
                 Select New With {
                     .EntityTarget = c.Attribute("Target").Value,
                     .PropertySource = c.Elements("Property").Attributes("Source"),
                     .PropertyTarget = c.Elements("Property").Attributes("Target")
                     })


        For Each itm In q

            Dim entitytarget As String = itm.EntityTarget



            For Each propertysrce In itm.PropertySource
Dim prpsource As String = propertysrce.ToString().Remove(0, 8) //Here propertytrgt value is like Source="...". So I am removing unnecessary part and adding value to list.


                prpsource = prpsource.Remove(prpsource.Length - 1)

                listSource.Add(prpsource)
            Next
            listSource.Add(entitytarget)
For Each propertytrgt In itm.PropertyTarget  //Here propertytrgt value is like Target="...". So I am removing unnecessary part and adding value to list.

                Dim prptarget As String = propertytrgt.ToString().Remove(0, 8)
                prptarget = prptarget.Remove(prptarget.Length - 1)
                listTarget.Add(prptarget)
            Next
            listTarget.Add(entitytarget)
        Next


    // HERE adding two lists(listTarget  and  listSource) to Dictionary

        For Each sourceValue In listSource
            Dim Source As String = sourceValue
            Dim count As Int32 = listTarget.Count
            If I <> count Then
                Dim Target As String = listTarget.Item(I)
                dictionary.Add(Source, Target)
                I = I + 1
            End If

        Next

       '===============STRING BUILDER


        strBuil.Append("select ")

        For Each itm In dictionary
            If n <= dictionary.Count - 2 Then
                strBuil.Append(itm.Value.ToString + " " + "as " + itm.Key.ToString + ",")
                n = n + 1
            ElseIf n = dictionary.Count - 1 Then

                strBuil = strBuil.Remove(strBuil.Length - 1, 1)

                strBuil.Append(" from " + itm.Value.ToString)
            Else
                Exit For
            End If
        Next


        Return strBuil.ToString()

    End Function

这是XML文件.

Here is the XML file.

<?xml version="1.0" encoding="utf-8" ?>
<Entities>

  <Entity Source="E_cdclient" Target="cd_client">
    <Property Source="Name" Target="client_name"/>
    <Property Source="Client_ShortDesc" Target="client_name_short"/>
    <Property Source="PeriodStart" Target="client_period_start"/>
    <Property Source="PeriodEnd" Target="client_period_end"/>
    <Property Source="Comments" Target="client_remark"/>
  </Entity>
  <Entity Source="E_cdclient_cdclientcontact" Target="cd_client_contact">
    <Property Source="Surname" Target="Surname"/>
    <Property Source="Familyname" Target="Familyname"/>
    <Property Source="Phone" Target="Phone"/>
    <Property Source="EMail" Target="EMail"/>
    <Property Source="Street" Target="Street"/>
    <Property Source="City" Target="City"/>
    <Property Source="ZIP" Target="ZIP"/>
    <Property Source="Responsibility" Target="Responsibility"/>
  </Entity>
</Entities>

谢谢&问候，
JN

Thanks & Regards,
JN

推荐答案

我不明白为什么需要字典，甚至在遍历第二遍时也不能随便构建字符串环形.我还认为您可以选择Attributes("Source").Value仅获取值，而不必剥离字符串.

I don''t see why you need a dictionary and can''t just build your string as you go, perhaps even as you go through the second loop. I also think you can select Attributes("Source").Value to get only the value, and not have to strip the strings.

这篇关于(如何)使用XDocument读取XML文件并建立动态SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！