开发者

(How to)Read XML file using XDocument and build dynamic SQL query?

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 duild 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
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="Str开发者_如何学Goeet" Target="Street"/>
    <Property Source="City" Target="City"/>
    <Property Source="ZIP" Target="ZIP"/>
    <Property Source="Responsibility" Target="Responsibility"/>
  </Entity>
</Entities>

Thanks & Regards, JN


Okay, first things first:

  1. One should never insert C++ comment slashes into a vb.net code,makes your code uncopyable
  2. One should try to keep his code clear from redundant linebreaks, makes your code unreadable
  3. XML was developed as a means to keep things simple, you do not keep things simple
  4. You should really make your life easier; use elements instead of attributes every time its possible
  5. With that said you could explain us what are you trying to do, because if you are trying something like XML w/ SQL you should really use LINQ to XML in conjuction with LINQ to SQL, i can see you have the LINQ to XML cleared
  6. If you need to export a database table to XML using this code:

    db.ExecuteCommand("SELECT * FROM TABLENAMES FOR XML AUTO,ELEMENTS ROOT('filename')")

    //(where db is a fully qualified datacontext object, or if you prefer use datareader and ExecuteNonQuery)

    or if you're fixated on using attributes use:

    db.ExecuteCommand("SELECT * FROM TABLENAMES FOR XML AUTO,ELEMENTS ROOT('filename')")

    //(where db is a fully qualified datacontext object, or if you prefer use datareader and ExecuteNonQuery)

  7. Oh and your code does not complie because of the undeclared variable I...

I hope this helps.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜