AdoQueryConnection.BuildSqlFromXmlNodes Method (Microsoft.Office.InfoPath)

Returns a SQL command text fragment parsed from the specified XML node that contains query fields values.

Namespace: Microsoft.Office.InfoPath
Assembly: Microsoft.Office.InfoPath (in microsoft.office.infopath.dll)

Syntax

'Declaration
Public MustOverride Function BuildSqlFromXmlNodes ( _
    input As XPathNavigator _
) As String
'Usage
Dim instance As AdoQueryConnection
Dim input As XPathNavigator
Dim returnValue As String

returnValue = instance.BuildSqlFromXmlNodes(input)

public abstract string BuildSqlFromXmlNodes (
    XPathNavigator input
)

Parameters

• input
  An XPathNavigator that is positioned at the dfs:queryFields XML node that contains the query field values to be converted to an SQL fragment.

Return Value

A WHERE clause built from the query fields values in the specified node.

Exceptions

Exception type	Condition
InvalidOperationException	One of the following conditions has occurred: The SQL command string generated from the input node cannot be parsed. The input node is not a table of query field values. The input node contains columns (XML attributes) that are not present in the data source that is being queried.
ArgumentNullException	The parameter passed to this method is null.
ArgumentException	The parameter passed to this method isnot valid. For example, it is of the wrong type or format.

Remarks

The fragment of SQL that the BuildSQLFromXmlNodes method generates is an SQL WHERE clause in the form of fieldname = value pairs. If there are more than one pair, each pair is combined with the AND operator. For example, if the user queried the Employees table of the Northwind database by entering Davolio and Nancy in the query fields for the Last Name and First Name fields, the SQL fragment returned by the BuildSQLFromXmlNode method would be:

("Employees"."LastName"='Davolio' AND "Employees"."FirstName"='Nancy')

The XML node that you specify for the input parameter must be a descendant of the dfs:queryFields node of the form. You can add the SQL command text fragment to the existing SQL command string of the ADOQueryConnection object using the Command property.

Note

To determine the XPath expression for the table that contains the query field values to pass for the input parameter, display the Data Source task pane, expand the queryFields group, right-click the table that contains the query fields you want to work with, and then click Copy XPath.

This member can be accessed only by forms running in the same domain as the currently open form, or by forms that have been granted cross-domain permissions.

This type or member can be accessed from code running in forms opened in Microsoft Office InfoPath 2007 or in a Web browser.

Example

In the following example, the event handler for a Button control uses the BuildSQLFromXMLNodes method of the ADOQueryConnection class to parse the current query field values and display them in a message box.

This example assumes that the form template has a data connection named "Main connection" to the Employees table in the SQL Server Northwind sample database.

public void DisplaySQL_Clicked(object sender, ClickedEventArgs e)
{
   // Get the query fields node.
   string queryFieldsXPath = 
      "dfs:myFields/dfs:queryFields/q:Employees";
   XPathNavigator mainDataSourceNav = MainDataSource.CreateNavigator();
   XPathNavigator queryFieldsNav = mainDataSourceNav.SelectSingleNode(
      queryFieldsXPath, NamespaceManager);

   // Create AdoQueryConnection from the 
   // DataConnections collection.
   AdoQueryConnection myAdoQueryConnection = 
      (AdoQueryConnection)(DataConnections["Main connection"]);
   
   // Display BuildSQLFromXMLNodes output.
   string sqlFromXmlNodes = 
      myAdoQueryConnection.BuildSqlFromXmlNodes(queryFieldsNav);
   MessageBox.Show(sqlFromXmlNodes);
}

Public Sub DisplaySQL_Clicked(ByVal sender As Object, _
   ByVal e As ClickedEventArgs)
   ' Get the query fields node.
   Dim queryFieldsXPath As String = 
      "dfs:myFields/dfs:queryFields/q:Employees"
   Dim mainDataSourceNav As XPathNavigator  = _
      MainDataSource.CreateNavigator()
   Dim queryFieldsNav As XPathNavigator = _
      mainDataSourceNav.SelectSingleNode( _
      queryFieldsXPath, NamespaceManager)

   ' Create AdoQueryConnection from the 
   ' DataConnections collection.
   Dim myAdoQueryConnection As AdoQueryConnection  = _
      DirectCast(DataConnections["Main connection"], _
      AdoQueryConnection)
   
   ' Display BuildSQLFromXMLNodes output.
   Dim sqlFromXmlNodes As String  = 
      myAdoQueryConnection.BuildSqlFromXmlNodes(queryFieldsNav)
   MessageBox.Show(sqlFromXmlNodes)
End Sub

See Also

Reference

AdoQueryConnection Class
AdoQueryConnection Members
Microsoft.Office.InfoPath Namespace