Copying Data from a Secondary Datasource to a Child Table in a Main Datasource
This code sample documents a block of VB.Net code I had to write recently for retrieving a block of data from a secondary datasource, and then copying that data into a child table of a dataset in the main datasource. I've copied it over as is for my benefit as much as yours - I lost this code recently in a machine rebuild so want to have a copy somewhere! I believe the syntax shown here will be helpful to many of you.
Public Sub GetEscalations_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)
' Write your code here.
' Define constants
Const secondaryDataSourceName As String = "GetUserEscalations"
Const secondaryListXPath As String = "/dfs:myFields/dfs:dataFields/ns3:GetUserEscalationsResponse/ns3:GetUserEscalationsResult/NewDataSet/Table"
Const secondaryRelativeEscalationIDXPath As String = "EscalationID"
Const secondaryRelativeDataXPath As String = "CompanyName"
Const mainObservationEvidenceName As String = "KeyObservationEvidence"
'Define strings for mapping values to main datasource evidence table.
Const mainEvidenceKOID As String = "KOID"
Const mainEvidenceKOEvidenceID As String = "KOEvidenceID"
Const mainEvidenceKEID As String = "KEID"
Const mainEvidenceKEDetail As String = "KEDetail"
Const mainEvidenceKECreatedBy As String = "KECreatedBy"
Const mainEvidenceKECreatedDate As String = "KECreatedDate"
Const mainEvidenceKELastUpdatedBy As String = "KELastUpdatedBy"
Const mainEvidenceKELastUpdatedDate As String = "KELastUpdatedDate"
Const mainEvidenceKETypeID As String = "KETypeID"
Const mainEvidenceKETypeName As String = "KETypeName"
Const mainEvidenceKEDeleted As String = "KEDeleted"
'Get navigator to the root of the main datasource. There will only ever be one record in the
'main datasource.
Dim mainDOMNavigator As XPathNavigator = MainDataSource.CreateNavigator()
'Explicitly create a pointer to the root element of the KO. This is the pointer that will drive the appending of the child Key Evidences.
Dim mainDOMObservationNavigator As XPathNavigator
mainDOMObservationNavigator = mainDOMNavigator.SelectSingleNode(
"/dfs:myFields/dfs:dataFields/tns:GetKOsDataSetByQuickSearchResponse/tns:GetKOsDataSetByQuickSearchResult/NewDataSet/KeyObservation", NamespaceManager)
'Get navigator to the root of the secondary datasource.
Dim secondaryDataSource As DataSource = DataSources.Item(secondaryDataSourceName)
Dim secondaryDOMNavigator As XPathNavigator = secondaryDataSource.CreateNavigator()
'Setting up some other XPathNavigator Pointers
Dim secondaryEscalationIDNavigator As XPathNavigator
Dim secondaryEscalationEvidenceNavigator As XPathNavigator
Dim secondaryListItem As XPathNavigator
Dim escalationIDMapping As String
Dim escalationEvidence As String
'Get all the list items from the secondary datasource.
Dim secondaryListItems As XPathNodeIterator = secondaryDOMNavigator.Select(secondaryListXPath, NamespaceManager)
Do While secondaryListItems.MoveNext()
secondaryListItem = secondaryListItems.Current
'Get observation Id for the list item.
secondaryEscalationIDNavigator = secondaryListItem.SelectSingleNode(secondaryRelativeEscalationIDXPath, NamespaceManager)
escalationIDMapping = secondaryEscalationIDNavigator.Value
'Get the evidence to be copied into the main DOM.
secondaryEscalationEvidenceNavigator = secondaryListItem.SelectSingleNode(secondaryRelativeDataXPath, NamespaceManager)
escalationEvidence = secondaryEscalationEvidenceNavigator.Value
If Nothing IsNot mainDOMObservationNavigator Then
'Append the evidence child
Using evidenceChild As XmlWriter = mainDOMObservationNavigator.AppendChild()
Dim prefix As String = mainDOMObservationNavigator.Prefix
Dim namespaceUri As String = mainDOMObservationNavigator.NamespaceURI
evidenceChild.WriteStartElement(prefix, mainObservationEvidenceName, namespaceUri)
evidenceChild.WriteStartElement(prefix, mainEvidenceKOID, namespaceUri)
evidenceChild.WriteValue(
"-1")
evidenceChild.WriteEndElement()
evidenceChild.WriteStartElement(prefix, mainEvidenceKOEvidenceID, namespaceUri)
evidenceChild.WriteValue(
"-1")
evidenceChild.WriteEndElement()
evidenceChild.WriteStartElement(prefix, mainEvidenceKEID, namespaceUri)
evidenceChild.WriteValue(escalationIDMapping)
evidenceChild.WriteEndElement()
evidenceChild.WriteStartElement(prefix, mainEvidenceKEDetail, namespaceUri)
evidenceChild.WriteValue(escalationEvidence)
evidenceChild.WriteEndElement()
evidenceChild.WriteStartElement(prefix, mainEvidenceKECreatedBy, namespaceUri)
evidenceChild.WriteValue(currentUser())
evidenceChild.WriteEndElement()
evidenceChild.WriteStartElement(prefix, mainEvidenceKECreatedDate, namespaceUri)
evidenceChild.WriteValue(currentDateTime())
evidenceChild.WriteEndElement()
evidenceChild.WriteStartElement(prefix, mainEvidenceKELastUpdatedBy, namespaceUri)
evidenceChild.WriteValue(currentUser())
evidenceChild.WriteEndElement()
evidenceChild.WriteStartElement(prefix, mainEvidenceKELastUpdatedDate, namespaceUri)
evidenceChild.WriteValue(currentDateTime())
evidenceChild.WriteEndElement()
evidenceChild.WriteStartElement(prefix, mainEvidenceKETypeID, namespaceUri)
evidenceChild.WriteValue(
"1")
evidenceChild.WriteEndElement()
evidenceChild.WriteStartElement(prefix, mainEvidenceKETypeName, namespaceUri)
evidenceChild.WriteValue(
"Escalation SR#")
evidenceChild.WriteEndElement()
evidenceChild.WriteStartElement(prefix, mainEvidenceKEDeleted, namespaceUri)
evidenceChild.WriteValue(
False)
evidenceChild.WriteEndElement()
evidenceChild.WriteEndElement()
End Using
End If
Loop
End Sub
Private Function currentDateTime() As String
Dim mainDomNavigator As XPathNavigator = MainDataSource.CreateNavigator()
Dim todayNavigator As XPathNavigator = mainDomNavigator.SelectSingleNode("/dfs:myFields/my:TodaysDate", NamespaceManager)
Return todayNavigator.Value
End Function
Private Function currentUser() As String
Dim mainDomNavigator As XPathNavigator = MainDataSource.CreateNavigator()
Dim userNavigator As XPathNavigator = mainDomNavigator.SelectSingleNode("/dfs:myFields/my:CurrentUser", NamespaceManager)
Return userNavigator.Value
End Function