The Query Builder Walkthrough

The DTC SDK includes a Query Builder sample that shows how a DTC can:

This topic takes some of the Visual Basic code from the sample and breaks it into steps you can use as a pattern for implementing the same functionality in your DTC. The following picture shows the Query Builder DTC on a Visual InterDev document.

For simplicity, this walkthrough covers only the Query Builder portion of the sample. For examples of the launching the URL Picker or the Color Picker, see the Visual Basic project included with this SDK. The description assumes you already have an ActiveX control created and that you understand how to use choices in a DTC. If you want more information on creating a DTC or using choices, see the guide to DTC Development and Deployment.

Also, in your own DTC, you need to add a handler for the SQL string the builder generates; this sample does not provide one. Typically such a handler would generate ASP code to retrieve a data set by establishing a data connection and running the acquired SQL string as a command. For more information about data connections, see the online documentation for ASP and for working with data in Visual InterDev.

Note   When you test your Query Builder DTC in a Web page, make sure the Web project has at least one data connection to a "live" data source. The Query Builder requires that a "live" data source be available in order to create a SQL statement.

The following procedures assume that the control's form has the following simple user interface elements:

When creating a DTC, use property pages instead of inline user interface for setting a DTC's properties. An inline user interface is the graphical representation of the DTC within the hosting document such as a form or other object with controls that accept user input. Although Visual InterDev 6.0 supports inline user interface, this is not a guarantee that all DTC hosts are required to support it now or in the future.

Launching the Query Builder

To call the Query Builder from your DTC, you need the same interfaces required by a DTC plus the following additional items:

The VID60 Data Services Support Type Library provides the services you need for communicating with the shell and calling the Query Builder. In order to use the Query Builder, your DTC needs to get the site information required to use choices and to gain access to the container as soon as the DTC is initialized. To accomplish this, the sample uses the InitServices function. Your DTC needs one as well. This function communicates with the container to locate the Database Services object. It then notifies this object that the DTC needs database services. After establishing communication, the Database Services object responds by publishing a dynamic choice that has a tag, "QueryBuilder," for each data connection in the Web project. For more information about using tags, Defining a Choice or Identifying a Choice of Interest.

To establish communication with Visual InterDev Services

  1. Add a reference to the VID60 Data Services Support Type Library 1.0.

    From the Project Menu, choose References. In the References dialog box, select VID60 Data Services Support Type Library 1.0.

  2. Add the following general declarations to your control.
    Implements IProvideRuntimeText
Implements IDesignTimeControl
Implements IQueryBuilderSink
  3. Declare a variable for the site.
    Dim MySite As IDesignTimeControlSite
  4. Implement a site using the DesignTimeControlSite property.
    Private Property Set IDesignTimeControl_DesignTimeControlSite(ByVal Site As IDesignTimeControlSite)
Set MySite = Site
End Property
  5. Create the InitServices function.
    Private Sub InitServices()
End Sub
  6. In the General Declarations, add constants for the GUID and IID service of the IDesigntimeControl extended services.
    Const guidService = "{af31b8c0-bdcc-11d0-b218-00c04fd70811}"
Const iidService = "{6FF02AB1-809B-11d1-9D27-006008058731}"

    Note   These IDs are not case-sensitive.

  7. In the InitServices function, declare variables for communicating with Visual InterDev that the DTC needs the Database Services.
    Private Sub InitServices()
    Dim srvDTCServices As IDTCExtendedServices
    Dim oleObject As IOleObject
    Dim serviceProvider As IServiceProvider
End Sub
  8. Disable the button that launches the Query Builder. This button should only be available when the Query Builder is actually available.
    Private Sub InitServices()
Dim srvDTCServices As IDTCExtendedServices
Dim oleObject As IOleObject
Dim serviceProvider As IServiceProvider
cmdBuilder.Enabled = False
End Sub
  9. Get the IOleClientSite of this control and query for its IServiceProvider interface.
    Private Sub InitServices()
Dim srvDTCServices As IDTCExtendedServices
Dim oleObject As IOleObject
Dim serviceProvider As IServiceProvider
cmdBuilder.Enabled = False
Set oleObject = Me
Set serviceProvider = oleObject.GetClientSite
End Sub
  10. Query the IServiceProvider interface for the IDTCExtendedServices service.
    Private Sub InitServices()
Dim srvDTCServices As IDTCExtendedServices
Dim oleObject As IOleObject
Dim serviceProvider As IServiceProvider
cmdBuilder.Enabled = False
Set oleObject = MeSet 
serviceProvider = oleObject.GetClientSite
Set srvDTCServices = serviceProvider.QueryService(IIDFromString(guidService), IIDFromString(iidService))
End Sub
  11. Notify the Extended Services to publish the VID60.DataSourcename choice.
    Private Sub InitServices()
Dim srvDTCServices As IDTCExtendedServicesDim oleObject As IOleObject
Dim serviceProvider As IServiceProvider
cmdBuilder.Enabled = False
Set oleObject = Me
Set serviceProvider = oleObject.GetClientSite
Set srvDTCServices = serviceProvider.QueryService(IIDFromString(guidService), IIDFromString(iidService))
If Not srvDTCServices Is Nothing Then
    srvDTCServices.UsesDataBaseServices
End If
End Sub

The Visual InterDev design-time control service now publishes a choice of type "VID60.DataSourceName" for each data source in the Web project.

Before the Query Builder can be used, it needs a ChoiceSink that implements the IQueryBuilderSink interface. After specifying the sink, your DTC can launch the Query Builder. In the Query Builder sample, the launch handler is in the Click event on the button.

To specify the launch handler

  1. In the General Declarations, declare variables for the Query Builder for use in the Click event.
    Dim mQueryBuilder As Object
  2. Add a click event for the button.
    Private Sub cmdBuilder_Click()
If (Not mQueryBuilder Is Nothing) Then
    mQueryBuilder.SetQueryBuilderSink Me
    mQueryBuilder.LaunchQueryBuilder "Query Builder"
End If
End Sub
  3. Add stub functions for the remaining methods of the IDesignTimeControl interface.

    In the sample, those methods are:

  4. Add a call to the InitServices function in the InitProperties event and provide an empty string to the Query Builder when it first opens.
    Private Sub UserControl_InitProperties()
    txtSQL.Text = ""
    InitServices
End Sub

To save the query string

  1. Use a property bag to restore the previous query. In the ReadProperties event, include a call to the InitServices function to provide a query string to the Query Builder when it opens.
    Private Sub UserControl_ReadProperties(PropBag As PropertyBag)
    On Error Resume Next
    txtSQL.Text = PropBag.ReadProperty("SQLText")
    InitServices
End Sub
  2. Write values back to the property bag for storage.
    Private Sub UserControl_WriteProperties(PropBag As PropertyBag)
    PropBag.WriteProperty "SQLText", txtSQL.Text
End Sub
  3. Handle the option to save changes when the Query Builder is closed.
    Private Property Let IQueryBuilderSink_SQLString(ByVal SQLText As String)
txtSQL.Text = SQLText
End Property
  4. Provide the initial query displayed in the Query Builder when it opens.
    Private Property Get IQueryBuilderSink_SQLString() As String
IQueryBuilderSink_SQLString = txtSQL.Text
End Property

Populating a Drop-down List with Choices

The Query Builder sample includes a combo box for selecting from the list of possible data sources. This list is based on the data sources available through the Web project.

To populate the drop-down list with available data sources

  1. In the DropDown event for the Combo box, create a choice collection to note all connections in the project.
    Private Sub comboConnection_DropDown()
    Dim chs As Choices
    Dim ch As Choice
    Dim cf As ChoiceFilter
    Set cf = MySite.CreateChoiceFilter("VID60.DataSourceName")
    comboConnection.Clear
End Sub
   
  2. Get all the available choices and populate the combo box.
    Private Sub comboConnection_DropDown()
    Dim chs As Choices
    Dim ch As Choice
    Dim cf As ChoiceFilter
    Set cf = MySite.CreateChoiceFilter("VID60.DataSourceName")
    comboConnection.Clear
    Set chs = MySite.AvailableChoices.Filter(cf)
    For Each ch In chs
        comboConnection.AddItem ch.Text
    Next
End Sub
  3. Declare the OnRebind event and apply a filter to the dynamic choice collection.

    Note   Since this example is using a dynamic choice, it uses the OnRebind method instead of a ChoiceSink. Dynamic choices only exist during a rebind. This example looks for all the VID60.DataSourceName choices; there is one for each data source in the project. 

    Private Sub IDesignTimeControl_OnRebind(ByVal Choices As DTC60.Choices)
Dim cf As ChoiceFilter
Dim chs As Choices
Dim ch As Choice

Set cf = MySite.CreateChoiceFilter("VID60.DataSourceName")
Set chs = Choices.Filter(cf)
For Each ch In chs
    Dim s As String
    Dim i As Integer
    Dim qb As Object

    For each of the choices, see if there is a query builder. A more advanced DTC would try to match ch.Text against the currently selected data source name, but in the interest of simplicity, we won't do that here.

        On Error Resume Next
    Set qb = ch.Tags("QueryBuilder")
    If Not qb Is Nothing Then

    The tag isn't empty, so the Query Builder is available.

            Set mQueryBuilder = qb
        cmdBuilder.Enabled = True
        Exit Sub
    End If
Next

    If the Query Builder doesn't have any available choices, disable the button that would launch it.

    Set mQueryBuilder = Nothing
cmdBuilder.Enabled = False
End Sub

Specify the Run-time Text

To complete the DTC, you need to specify how you want the DTC to use the query string from the Query Builder. For more information about specifying runtime text, see the Web Design-Time Control SDK or the Runtime Text Reference.