(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:
- One should never insert C++ comment slashes into a vb.net code,makes your code uncopyable
- One should try to keep his code clear from redundant linebreaks, makes your code unreadable
- XML was developed as a means to keep things simple, you do not keep things simple
- You should really make your life easier; use elements instead of attributes every time its possible
- 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
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)
Oh and your code does not complie because of the undeclared variable I...
I hope this helps.
精彩评论