Walkthrough: Access Dynamic Data

Article
08/18/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

This walkthrough illustrates using Workflow Designer to create a simple speech application demonstrating dynamic data access.

Prerequisites

To complete this walkthrough, you need:

Speech Server.
Access to the Northwind sample database with access granted to the user NETWORK SERVICE. By default, the Northwind and pubs sample databases are not installed on Microsoft SQL??Server 2005. These databases can be downloaded from the Microsoft Download Center.
Sufficient permissions to create and debug ASP.NET Web application projects on the computer where Speech Server is installed.

Connecting to SQL??Server

There are three options for connecting a Speech Server application to SQL??Server:

The recommended option is to use Windows Authentication and add NETWORK SERVICE as a user to the database. By default, a Speech Server application connects as NETWORK SERVICE.
Alternatively, switch to SQL??Server Authentication and use the connection string to pass in a user name and password. There are security issues with this option. For more information, see Securing Connection Strings.
As a third option, impersonate another account, and then carefully undo the impersonation in the finally block.

Accessing Dynamic Data

To create a voice response application

Create a new voice response application.

For more information, see How to: Create a Voice Response Application.
Add a grammar that recognizes the two semantic values: suppliers and products.

For more information, see Walkthrough: Use Conversational Grammar Builder to Create a Keyword Grammar.

To add a welcome prompt using a QuestionAnswer

In Solution Explorer, right-click VoiceResponseWorkflow1.cs, and then click View Designer.
On the Microsoft Visual Studio 2005 toolbar, click Toolbox.
In the Toolbox, click Speech Dialog Components to display speech activities.
Drag and drop a QuestionAnswer speech dialog component onto the design surface between answerCallActivity1 and disconnectCallActivity1.
In the Properties window, change the Name property to askFirstQuestion.
On the design surface in askFirstQuestion, click Edit Prompts.
In the QuestionAnswer Property Builder dialog box, type Do you want information on products or suppliers in the Main box, and then click OK.
Click Attach Grammar.
In the QuestionAnswer Property Builder dialog box, select the response node in the tree view pane, and then click OK.

This grammar should recognize a user selection between two options: products or suppliers.

To add an IfElse activity and a QuestionAnswer

In the Toolbox, click Windows Workflow to display workflow activities.
Drag and drop an IfElse activity onto the design surface between askFirstQuestion and disconnectCallActivity1.
In the Properties window, change the Name property to chooseProductsOrSuppliers.
In the Toolbox, click Speech Dialog Components to display speech activities.
Drag and drop a QuestionAnswer speech dialog component onto the design surface, and then position it on the left branch of chooseProductsOrSuppliers.
In the Properties window, change the Name property to askWhichSupplier.
On the design surface in askWhichSupplier, click Edit Prompts.
In the QuestionAnswer Property Builder dialog box, click Show Event Handler.

In the event handler, add a statement to ask about suppliers.

this.askWhichSupplier.MainPrompt.SetText("which supplier are you interested in");

In Solution Explorer, right-click VoiceResponseWorkflow1.cs, and then click View Designer.

To add a code condition to the IfElse

On the red exclamation at the top of chooseProductsOrSuppliers, click the down arrow, and then click Property 'Condition' is not set.
In the Properties window for IfElseBranchActivity1, set the Condition property to Code Condition in the list box, and then expand the Condition property.
Type ChooseCategory as a name for the condition.
To set the property, click the design surface away from the Properties window.
In the event handler, add a statement to set the Result property to true or false based on the answer to askFirstQuestion.
```
e.Result = (this.askFirstQuestion.RecognitionResult.Text == "suppliers");
```
The left branch is now set to execute if the user answers that they want information about suppliers.

To add a second QuestionAnswer to the IfElse

In Solution Explorer, right-click VoiceResponseWorkflow1.cs, and then click View Designer.
Drag and drop a QuestionAnswer speech dialog component onto the design surface, and then position it on the right branch of chooseProductsOrSuppliers.
In the Properties window, change the Name property to askWhichProduct.
On the design surface in askWhichProduct, click Edit Prompts.
In the QuestionAnswer Property Builder dialog box, click Show Event Handler.
In the event handler, add a statement to clear the prompt, and then add a second statement to ask about products.
```
this.askWhichProduct.MainPrompt.ClearContent();
this.askWhichProduct.MainPrompt.AppendText("which product are you interested in");
```
askWhichProduct executes if the user answers that they want information about products.

To add code to generate a dynamic grammar

In Solution Explorer, right-click VoiceResponseWorkflow1.cs, and then click View Code.

In the code window, add the following using statements:

using Microsoft.SpeechServer.Recognition;
using Microsoft.SpeechServer.Recognition.SrgsGrammar;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

Add the following two procedures at the end of the Workflow1 class.

private void GetSQLData()
  {
    SqlConnection cnn = new SqlConnection("Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;");
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandType = CommandType.Text;
    //Select the 1st 4 company names in the table
    cmd.CommandText = "SELECT Top 4 CompanyName FROM Suppliers";
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    DataSet ds = new DataSet();
    da.Fill(ds, "Suppliers");
    DataRow[] adr = ds.Tables["Suppliers"].Select();

    foreach (DataRow row in adr)
      {
        _choices.Add(row[0].ToString());
      }
  }

private SrgsDocument CreateGrammar()
  {
    SrgsDocument suppliersGrammar = new SrgsDocument();
    suppliersGrammar.Mode = SrgsGrammarMode.Voice;

    SrgsRule rule = new SrgsRule("Items");
    SrgsOneOf oneOf = new SrgsOneOf(_choices.ToArray());

    rule.Elements.Add(oneOf);

    suppliersGrammar.Rules.Add(rule);
    suppliersGrammar.Root = rule;

    //The following 5 statements save the grammar to a .grxml file
    //Change the path to match your project folders.
    string filePath = @"C:\My Documents\Visual Studio 2005\Projects\DynamicDataAccess\DynamicDataAccess\Grammars";
    XmlTextWriter writer = new XmlTextWriter(filePath, System.Text.Encoding.UTF8);
    suppliersGrammar.WriteSrgs(writer);
    writer.Flush();
    writer.Close();

    return suppliersGrammar;
  }

Add the following three statements to the beginning of the askWhichSupplier_TurnStarting event handler.

  GetSQLData();
  Grammar suppliersGrammar = new Grammar(CreateGrammar());
  this.askWhichSupplier.Grammars.Add(suppliersGrammar);

Add the following declaration just above the Workflow1 constructor.
```
private List<string> _choices = new List<string>();
```

To add a confirmation statement

In Solution Explorer, right-click VoiceResponseWorkflow1.cs, and then click View Designer.
Drag and drop a Statement speech dialog component onto the design surface between chooseProductsOrSuppliers and disconnectCallActivity1.
In the Properties window, change the Name property to summary.
On the design surface in summary, click Edit Prompts.
In the Statement Property Builder dialog box, click Show Event Handler.

In the event handler, add the following code.

if (this.askFirstQuestion.RecognitionResult.Text == "suppliers")
  {
    this.summary.MainPrompt.AppendText("you asked for information on " + askWhichSupplier.RecognitionResult.Text);
  }
else 
  {
    this.summary.MainPrompt.AppendText("you asked for information on " + askWhichProduct.RecognitionResult.Text);
}

To run the application

Answer "suppliers" to the first question.
Answer "Bigfoot Breweries" to the second question.

To extend the application

Modify the application so it also responds to a request for product information.

This might require changes to the workflow, as well as querying the Northwind database for data and building a speech recognition grammar using the query results.