Copying Data from a Secondary Datasource to a Child Table in a Main Datasource

Article
02/23/2007

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")