Connecting to Databases Using the Office 2003 Research Service Class Library Wizard

 

Chris Kunicki
OfficeZealot.com

September 2004

Applies to:
     Microsoft Office 2003 Editions

Summary: One of four articles, read this article to discover the requirements and steps to use the Microsoft Office 2003 Research Service Class Library (RSCL) Wizard to connect to existing data providers. The Microsoft Office 2003 Research Service Class Library (RSCL) Wizard includes a pluggable architecture to allow you to add new data providers to the wizard or connect to existing providers. (7 printed pages)

Download Office2003_ResearchServicesDevelopmentExtras.msi.

Contents

Introduction
Using the Research Service Class Library Wizard
Wizard-Generated Code
Conclusion
Additional Documentation for the Research Service Development Extras Toolkit

Introduction

You can use the Microsoft Office 2003 Research Service Class Library Wizard to generate a new research service using the Research Service Class Library (RSCL). The wizard collects information about the new research service and generates the appropriate Microsoft Visual C or Microsoft Visual Basic .NET project and source code.

Using the Research Service Class Library Wizard

  1. In Microsoft Visual Studio .NET 2003, on the File menu, click New Project. The New Project dialog box appears.
  2. In the New Project dialog box, click either the Visual Basic Projects or Visual C Projects folder.
  3. Select the Research Service Wrapper project from the list of project types.
  4. Type a URL for the project and click OK. The Research Service Class Library Wizard appears.

The first page of the wizard collects information about the research service provider, as shown in Figure 1.

Figure 1. Collecting provider information with the wizard

Table 1. Information collected about the research service provider

Information Description
Name A name for the provider. Typically, the provider name is the name of the organization responsible for the research service being built.
Message A message the provider displays to the client when they register the Research service.
About Path A URL the client can use to find out more information about the provider.
ID A unique ID for the provider. The provider ID needs to be a GUID. You can either provide your own GUID or click the New GUID button to generate a new one.

The second page of the wizard collects information about the research service, as shown in Figure 2.

Figure 2. The wizard collects information about the research service

Table 2. Information collected about the research service

Information Description
Name The name of the research service. This name displays in the Research task pane in any Microsoft Office 2003 application.
Description A description of the research service. This description is viewable in the Research Service Properties page accessed through the Research task pane in any Office 2003 application.
Category The Research task pane category for the research service in any Office 2003 application.
ID A unique ID for the research service. The research service ID needs to be a GUID. You can either provide your own GUID or click New GUID to generate a new one. Note   This ID must differ from the ProviderID.

The third page of the wizard collects license information to display to the client when they register the research service through the Research task pane in any Office 2003 application, and in the property page for the research service. If Require License is set to yes, the client is required to click a check box accepting the license before they can continue registering the Research service. Figure 3 shows the License Information page of the wizard.

Figure 3. The License Information page gathers licensing information

The fourth page provides the option to specify a data provider that connects the generated research service to a database. To generate an RSCL-enabled research service not connected to a database select "No Data Provider" in the Data Provider drop down list. This generates an empty RSCL-enabled research service.

To learn more about creating an empty RSCL-enabled research service using the wizard read the article, Introduction to the Office 2003 Research Services Class Library.

Figure 4. The data provider collection page is straightforward

You can use the OleDB provider to access a Microsoft Office Access database; use the SqlDB provider to access a Microsoft SQL Server database.

The OleDB provider, shown in Figure 5, collects the information specified in and is shown in Figure 5.

Table 3. OLE DB information collected by the wizard

Information Description
Connection String The connection string to connect to the database. For example:
PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=C:\Program Files\Microsoft Office 2003 
Developer Resources\Research Service Development Extras\Samples\CSharp\
DataDocSample\Northwind.mdb
SQL Statement A SQL statement to run against the database and return the results. The keyword "SearchTerm" in the SQL statement is replaced by the Query Text submitted when the user queries the research service. For example:
SELECT ContactName AS Title, Phone AS Description 
FROM Customers WHERE ContactName LIKE '%SearchTerm%'

Figure 5. The OleDb provider page collects data for accessing Access databases

The SqlDB provider is the same as the OleDB provider, unless the Query Type is set to Stored Procedure, as shown in Figure 6. In this case, you must provide a stored procedure name instead of a SQL statement. The stored procedure must have a single parameter called SearchTerm. The Query Text submitted when the user queries the research service is passed into the stored procedure as the value for the SearchTerm parameter.

Figure 6. Type the stored procedure name on the SqlDB provider page

Note   The wizard supports a pluggable architecture allowing you to add more data providers. For more information read the Wizard Data Provider Plug-in document.

The next page provides formatting options and displays only when you select a data provider. This page offers the choice of a document style display, where each result has a title and a description in an expanding node, or as a simple two-column table, as shown in Figure 7.

Figure 7. Choosing the display style

In the case of the document style, the DataTable returned by the data provider must have a column named "Title" and a column named "Description." The wizard uses the data in the "Title" column as the title for each result; the wizard uses the data in the "Description" column as the description for each result.

In the case of the two-column table, the title of each column returned by the data provider is in the left column of the table, the value of the row corresponding to the column is in the right column of the table. A new row is added for each field returned by the data provider.

You also have the option to use paging. Paging allows the results returned by the research service to display in groups based on the number entered in the results per page box.

The final page provides a Finish button. Once you click Finish, the wizard generates the project and code for the new research service.

Wizard-Generated Code

The generated project and source code is ready for you to build and use. The web.config file contains the information about the provider and the research service. The Datalayer.VB or Datalayer.CS file contains the code generated by the data provider and is to connect to a database, query it, and return a datatable object to ResearchService.asmx. ResearchService.asmx is the page the user accesses to register and query the new Research service.

Conclusion

Using the Research Service Class Library Wizard is a good point to start with when learning how to use the Research Service Class Library. By using the wizard, you can develop your own custom research service while allowing the wizard to create the required XML code.

Additional Documentation for the Research Service Development Extras Toolkit

Read the following articles for more information about the Research Service Development Extras Toolkit:

About the Author

Chris Kunicki is a longtime enthusiast of Office development and has been evangelizing Office as an important platform for building solutions for many years. As the founder of OfficeZealot.com, a leading Web site on Office solutions development, Chris builds enterprise solutions, designs tools for developers, delivers presentations, and writes extensively on the topic of Microsoft Office. Find out more about Chris and Office at www.OfficeZealot.com or send him an e-mail message at chris@officezealot.com.

© Microsoft Corporation. All rights reserved.