Click here to Skip to main content
15,867,453 members
Articles / Desktop Programming / WPF

Database Bot using Oscova

Rate me:
Please Sign up or sign in to vote.
5.00/5 (16 votes)
17 Jan 2017CPOL16 min read 34.7K   592   19   8
Use Oscova, a bot development framework, to create a Natural Language Interface to an SQL Database
In this article, we will leverage on Oscova to create a Bot that allows us to talk to our employees database.

Introduction

Long back, I started my journey with SIML to create a Natural Language Interface to Database. I was overwhelmed by the feedback I received. So this time, I decided to get back and take it up a notch with a whole new architecture called Oscova, a recent add-on to the Syn Bot development framework.

What differentiates Oscova from SIML is that unlike SIML, Oscova doesn't rely on strict patterns. Instead, it relies on the semantic and syntactic similarities between user inputs and saved expressions. Oscova enables developers to create Bots that take advantage of concepts and features like Dialogs, Intents, Contexts and Named Entity Recognition along with the added advantage of not requiring to connect to any online API.

Having said all that, if you still prefer scripting strict user input patterns SIML may still hold best in your case.

In this article, we will leverage on Oscova to create a Bot that allows us to talk to our employees database.

Prerequisites

  • Knowledge of programming in C#
  • Database management with SQL
  • Basic idea of WPF applications and XAML

The Architecture

At its core, Oscova's building blocks mainly consist of the following:

  • Dialog - Simply a collection of related intents
  • Intent - Action that is executed/invoked by the Bot when an Expression matches
  • Entity - Recognized named user or system entity
  • Expression - A template pattern or an example that resembles a user input

Steps Involved

  • First, we'll create a WPF application project with all the required GUI elements.
  • Import essential libraries to our project.
  • Create a database utility class for interacting with our database.
  • Connect GUI with Bot events.
  • Create Entity Recognizers for a few database columns.
  • Create Dialogs and Intents
  • Test our Bot.

If you ever get confused or find any concept hard to grasp, just open up the project and refer the code. Things should get a lot easier once you've browsed through the code base of the project.

WPF Cosmetics

I could have used a Console Application for simplicity but I like cherry on top of my cake so let's start by first creating a WPF project that will have all the GUI stuff we'll need to interact with our Bot.

Throughout the article, I am going to assume that you're working with Visual Studio. If you're on Linux environment, Mono with GTK should suffice but I am going to stick with VS in this article.

  • Start Visual Studio.
  • Click on File, choose New and then select Project...
  • Under Templates, select Windows and then choose WPF Application.
  • Name the project NLI-Database-Oscova (Important if you plan to copy the codes below). NLI is an acronym for Natural Language Interface.

The WPF application would need a few components:

  • TextBox - for user input
  • Button - to evaluate user input
  • TabControl - first tab to view the DataGrid (for database) and second tab to view the JSON result

In Solution Explorer, double click on MainWindow.xaml and replace the XAML with the code below:

XML
<c1></c1><Window x:Class="NLI_Database_Oscova.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="Natural Language Interface using Oscova" Height="450" 
        Width="560" WindowStartupLocation="CenterScreen">
    <Grid>
        <TabControl>
            <TabItem Header="Interaction">
                <Grid>
                    <Grid.RowDefinitions>
                        <RowDefinition Height="35"/>
                        <RowDefinition Height="50*"/>
                        <RowDefinition Height="232*"/>
                    </Grid.RowDefinitions>
                    <Grid>
                        <Grid.ColumnDefinitions>
                            <ColumnDefinition Width="414*"/>
                            <ColumnDefinition Width="100"/>
                        </Grid.ColumnDefinitions>
                        <TextBox Name="InputBox" TextAlignment="Center" 
                         CharacterCasing="Upper" KeyDown="InputBox_OnKeyDown"/>
                        <Button Name="ExecuteButton" Content="Evaluate" 
                         Grid.Column="1" Click="EvaluateButton_OnClick"/>
                    </Grid>
                    <Label Grid.Row="1"  Name="ResponseLabel" 
                     Content="No Response Yet" VerticalContentAlignment="Top" />
                    <TabControl Grid.Row="2">
                        <TabItem Header="Table">
                            <DataGrid  Name="EmployeeGrid" FontSize="14" />
                        </TabItem>
                        <TabItem Header="JSON Result">
                            <TextBox Name="ResultBox" IsReadOnly="True" 
                             AcceptsReturn="True" VerticalScrollBarVisibility="Auto" />
                        </TabItem>
                    </TabControl>
                </Grid>
            </TabItem>
            <TabItem Header="Examples">
                <TextBox Name="ExamplesBox" VerticalScrollBarVisibility="Auto" 
                 HorizontalScrollBarVisibility="Auto" IsReadOnly="True"/>
            </TabItem>
        </TabControl>
    </Grid>
</Window>

The above code adds a TabControl and within it, there's a TextBox for user input and an evaluate Button.

The WPF application should look like the following:

Image 1

Note: Ignore any exceptions in the XAML code. We'll be addressing them later on in the article.

Importing Oscova and SQLite

Now that your WPF application got some makeup, let us reference Oscova. Before that, let me clarify that Oscova is part of the Syn.Bot framework and we'll need to use NuGet to reference the package.

  • Click on Tools, select NuGet Package Manager and select Package Manager Console
  • Type Install-Package Syn.Bot
  • Syn.Bot framework will be referenced along with some external dependencies.

To reference SQLite (for Database):

  • Type Install-Package System.Data.SQLite

Now that you've successfully referenced the required libraries. We'll go ahead and get our hands dirty with some C# coding.

DatabaseUtility Class

To make life easier, we'll first create a Database utility class. Right click the project in Solution Explorer and select Class. Name the class DatabaseUtility.

Replace the entire content of DatabaseUtility class with the following:

C#
using System.Data.SQLite;
using System.IO;

namespace NLI_Database_Oscova
{
    public class DatabaseUtility
    {
        const string DataSource = "EmployeesTable.db";
        public SQLiteCommand Command { get; set; }
        public SQLiteConnection Connection { get; set; }
        public MainWindow Window { get; }

        public DatabaseUtility(MainWindow window)
        {
            Window = window;
        }

        public void Initialize()
        {
            if (File.Exists(DataSource)) File.Delete(DataSource);

            Connection = new SQLiteConnection 
                         { ConnectionString = "Data Source=" + DataSource };
            Connection.Open();
            ExecuteCommand("CREATE TABLE IF NOT EXISTS EMPLOYEES 
                          (ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
                          Name VARCHAR(100) NOT NULL, Role VARCHAR(10), 
                          Age INTEGER NOT NULL, Salary INTEGER NOT NULL);");

            ExecuteCommand("INSERT INTO EMPLOYEES VALUES(1, 'Lincoln', 'Manager', 43, 54000)");
            ExecuteCommand("INSERT INTO EMPLOYEES VALUES(2, 'George', 'CEO', 46, 75000)");
            ExecuteCommand("INSERT INTO EMPLOYEES VALUES(3, 'Rick', 'Admin', 32, 18000)");
            ExecuteCommand("INSERT INTO EMPLOYEES VALUES(4, 'Jorge', 'Engineer', 28, 35000)");
            ExecuteCommand("INSERT INTO EMPLOYEES VALUES(5, 'Ivan', 'Tech', 23, 34000)");

            ExecuteCommand("INSERT INTO EMPLOYEES VALUES(6, 'Mark', 'Tech', 25, 34000)");
            ExecuteCommand("INSERT INTO EMPLOYEES VALUES(7, 'Vincent', 'Support', 21, 20000)");
            ExecuteCommand("INSERT INTO EMPLOYEES VALUES(8, 'Wang', 'Support', 20, 20000)");
            ExecuteCommand("INSERT INTO EMPLOYEES VALUES(9, 'Ahmed', 'Tech', 24, 34000)");
            ExecuteCommand("INSERT INTO EMPLOYEES VALUES(10, 'Krishna', 'Admin', 25, 18000)");
        }

        private void ExecuteCommand(string commandText)
        {
            Command = new SQLiteCommand(Connection) { CommandText = commandText };
            Command.ExecuteNonQuery();
        }
    }
}

Wait a minute, there's more to add. Inside the DatabaseUtility class, add the following methods:

C#
public void Evaluate(string commandText)
{
    Window.UpdateDataGrid(commandText);
}

public void UpdatePropertyByName
       (string employeeName, string propertyName, string propertyValue)
{
    var updateString = $"UPDATE EMPLOYEES SET {propertyName}={propertyValue} 
                       WHERE UPPER(Name) LIKE UPPER('%{employeeName}%');";
    var selectString = $"SELECT * FROM EMPLOYEES WHERE UPPER(Name) 
                       LIKE UPPER('{employeeName}%');";

    Evaluate(updateString);
    Evaluate(selectString);
}

public void UpdatePropertyById(string employeeId, string propertyName, string propertyValue)
{
    var updateString = $"UPDATE EMPLOYEES SET {propertyName}={propertyValue} 
                       WHERE ID={employeeId};";
    var selectString = $"SELECT * FROM EMPLOYEES WHERE ID={employeeId};";

    Evaluate(updateString);
    Evaluate(selectString);
}

public void PropertyByName(string employeeName, string propertyName)
{
    var selectString = $"SELECT {propertyName} FROM Employees 
                       WHERE UPPER(Name) LIKE UPPER('%{employeeName}%');";
    Evaluate(selectString);
}

public void PropertyByRole(string employeeRole, string propertyName)
{
    var selectString = $"SELECT DISTINCT {propertyName} FROM Employees 
                       WHERE UPPER(Role) LIKE UPPER('%{employeeRole}%')";
    Evaluate(selectString);
}

public void EmployeeByName(string employeeName)
{
    var selectString = $"SELECT * FROM Employees WHERE UPPER(Name) 
                       LIKE UPPER('%{employeeName}%');";
    Evaluate(selectString);
}

public void EmployeeByRole(string employeeRole)
{
    var selectString = $"SELECT * FROM Employees WHERE UPPER(Role) 
                       LIKE UPPER('%{employeeRole}%');";
    Evaluate(selectString);
}

public void Close()
{
    Command.Dispose();
    Connection.Dispose();
}

This class takes MainWindow as one of its constructor parameter. We'll need it later on in our project.

The Initialize method helps us create dummy entries for our employees Database. Then there are some helper functions like UpdatePropertyByName, UpdatePropertyById and so on. These functions will be used by our Bot when certain intents are invoked.

You don't have to worry about the code at the moment. It will all make sense later on in the article.

MainWindow

In Solution Explorer, right click MainWindow.cs and select View Code.

Add the following references:

C#
using System;
using System.Data;
using System.Data.SQLite;
using System.Windows;
using System.Windows.Input;
using Syn.Bot.Oscova;
using Syn.Bot.Oscova.Collection;
using Syn.Bot.Oscova.Entities;

Add the following properties to the MainwWindow class.

C#
public OscovaBot Bot { get; }
public DatabaseUtility DatabaseUtility { get; }

Add the following within the constructor:

C#
public MainWindow()
{
    InitializeComponent();

    Bot = new OscovaBot();

    Bot.MainUser.ResponseReceived += (sender, args) =>
    {
        ResponseLabel.Content = args.Response.Text;
    };

    DatabaseUtility = new DatabaseUtility(this);
    DatabaseUtility.Initialize();
    UpdateDataGrid("SELECT * From Employees");

    Bot.MainUser.Context.SharedData.Add(DatabaseUtility);

    Bot.CreateRecognizer("set", new[] { "change", "update", "set" });
    Bot.CreateRecognizer("property", new[] { "id", "name", "job", "age", "salary" });

    //Parsers go here

    //Dialogs go here

    //Finally Train the Bot.
    Bot.Train();
}

So what are we upto?

Firstly, we assign Bot property with an instantiated OscovaBot object. Next, we handle the ResponseReceived event of the MainUser to display the text of the response via ResponseLabel.

Then, we create a new instance of DatabaseUtility class and call the Initialize() method. You may ignore the UpdateDataGrid() method for now.

Because we'll be needing the DatabaseUtility throughout the chat session, we next add it to the MainUser's SharedData.

Next, we make use of the overloaded CreateRecognizer method to create a recognizer for specific words like change, update and so on.

Finally, we call the Train() method.

Note: Oscova requires that the Train() method is called once after components have been added, created or modified.

Armed with this, we go ahead and fix the UpdateDataGrid() function. Add the following code to the MainWindow class.

C#
public void UpdateDataGrid(string sql)
{
    var dataSet = new DataSet();
    var dataAdapter = new SQLiteDataAdapter(sql, DatabaseUtility.Connection);
    dataAdapter.Fill(dataSet);
    if (dataSet.Tables.Count > 0)
    {
        EmployeeGrid.ItemsSource = dataSet.Tables[0].DefaultView;
    }
}

The above method enables us to update the DataGrid after every SQL call. You might have already noticed DatabaseUtility making use of this method.

Creating Custom Entity Recognizers

So far so good. It's time to create a few entity recognizers. An Entity Recognizer is simply an implementation of IEntityRecognizer interface that reads the normalized user input and returns a collection of matching items.

For example, a number recognizer will probably use regular expression and return all the matched numbers as a collection of entities.

So long story short, every item in our database is to be treated as an individual entity. This allows user inputs to have entities in them and in turn lets us create better expressions.

Employee Name Recognizer

Instead of creating an entity recognizer from scratch, we'll instead create a private method that leverages on the CreateRecognizer overloaded method whose second parameter takes a function that returns a collection of entities, i.e., an EntityCollection.

Add the following method to MainWindow class:

C#
private void CreateNameParser()
{
    Bot.CreateRecognizer("Name", request =>
    {
        var requestText = request.NormalizedText;
        var entities = new EntityCollection();

        DatabaseUtility.Command.CommandText = "SELECT * FROM EMPLOYEES";
        var reader = DatabaseUtility.Command.ExecuteReader();

        while (reader.Read())
        {
            var name = reader["Name"].ToString();
            var wordIndex = requestText.IndexOf(name, StringComparison.OrdinalIgnoreCase);
            if (wordIndex != -1)
            {
                var entity = new Entity("Name")
                {
                    Value = name,
                    Index = wordIndex
                };
                entities.Add(entity);
            }
        }

        reader.Close();
        return entities;
    });
}

The code above creates a new entity recognizer. The Name is the entity-type name we wish to use to refer to employee names in our database.

The requestText holds the normalized value of user's request text. The normalized value is usually the same user input string but with filters applied. Discussing more on this is beyond the scope of the article. I recommend going through the API documentations for clarifications.

Using the DatabaseUtility class, we iterate through all the items in the name column of our database. We then use the IndexOf method to see if the value exists in user input. If the index value returned is anything but -1, then the word was found. When a name is found, we create a new entity of type name and set the Value and the Index property.

Note: In Oscova, all entities must specify the starting index of the first occurance of matched value.

Employee Job Recognizer

Likewise, we'll also create an Entity Recognizer for the Job titles of each employee. However, unlike the above Entity Recognizer, here we just create an empty recognizer and fill in the Entries property with the values of the Role column.

C#
private void CreateRoleParser()
{
    var parser = Bot.CreateRecognizer("Role");
    DatabaseUtility.Command.CommandText = "SELECT * FROM EMPLOYEES";
    var reader = DatabaseUtility.Command.ExecuteReader();
    while (reader.Read())
    {
        var roleTitle = reader["Role"].ToString();
        if (parser.Entries.Contains(roleTitle)) continue;
        parser.Entries.Add(roleTitle);
    }
    reader.Close();
}

Cool! Now let's call the methods from within the MainWindow constructor. Place the following code just below the //Parsers go here comment in the constructor.

C#
//Parsers go here
CreateNameParser();
CreateRoleParser();

If you've reached so far, I am hoping you’ve gotten a basic understanding of named entity recognizers.

Handling Button and TextBox events

To pass the user input from InputBox to Oscova for evaluation, we'll need to handle the EvaluateButton.Click and InputBox.KeyDown event. Add the following code to the MainWindow class.

C#
private void EvaluateButton_OnClick(object sender, RoutedEventArgs e)
{
    var message = string.IsNullOrEmpty(InputBox.Text) ? "clear" : InputBox.Text;
    var result = Bot.Evaluate(message);
    ResultBox.Text = result.Serialize();
    result.Invoke();
    InputBox.Clear();
}

The code above checks if the InputBox.Text value is empty or not. If empty, then the clear command is sent for evaluation.

After the evaluation result is returned, we set ResultBox.Text property to hold the serialized JSON value representing the evaluation result and later call the Invoke method to execute the highest scoring intent.

private void InputBox_OnKeyDown(object sender, KeyEventArgs e)
{
    if (e.Key == Key.Return)
    {
        EvaluateButton_OnClick(null, null);
    }
}

The above code simply calls the EvaluateButton_OnClick method whenever the Enter key is pressed within the InputBox.

Creating Dialogs

Finally! the moment we've been waiting for. Till now, we've been just setting up the atmosphere for our database Bot. Now hop in and let's start our implementation of a basic dialog class.

A Dialog is nothing but a collection of related intents grouped together. Now what is an Intent you ask?

An Intent is an Intent! Alright that may have not sounded right. Let me explain a bit. An Intent is what maps user input to executable actions.. So simply put an Intent, in Oscova, is a method that is invoked when a user input matches an expression.

An Expression on the other hand is a pattern or example, depending on how you write it, that resembles a user input.

Oscova's scoring system compares user input to all expressions. High scoring expressions along with their intents are then returned as part of the evaluation result.

Alright code veterans, we'll now create our first dialog:

  • Right click the project in Solution Explorer, choose Add and select New Folder.
  • Name the folder Dialogs.
  • Right click the folder, choose Add and select Class...
  • Name the class DatabaseEnquiryDialog and select Add.

In order to convert DatabaseEnquiryDialog to an Oscova dialog, all you've got to do is derive the class from Oscova's Dialog class as shown below:

C#
class DatabaseEnquiryDialog: Dialog
{
}

Creating Intents

Our first intent is going to be a pretty simple one. All we want is that when the user says something like What is the age of Rick? (Rick being an employee), we want the Bot to return the value of the respective Age column.

We'll call our intent PropertyEnquiry. To do so, we first add a void method to the DatabaseEnquiryDialog class as shown below:

C#
public void PropertyEnquiry(Context context, Result result)
{
}

Some information on the code above.

  • An intent method must always have a public modifier.
  • An intent may take 0, 1 (Context or Result) or 2 (Context and Result) parameters.
  • An intent must have atleast 1 Expression. Introduced next.

Adding Expressions to Intents

A expression is added to an intent via the Expression attribute. There are two main types of expressions.

  • Example Expressions - We just write an example input and optionally annotate entities with curly braces.
  • Template Expressions - We write user input but instead of annotation, we down right specify entity types instead.

An example expression for What is the age of Rick? would look like:

C#
[Expression("What is the {age} of {Rick}?")]
[Entity("property")]
[Entity("name")]

The first Entity attribute now links the annotated word age to the entity type property and the word Rick is linked to the entity type name.

Note: The order of the entity attributes is important as the sequence of annotated words or phrases are aligned against the sequence of Entity attributes.

A template expression for What is the age of Rick? would look like:

C#
[Expression("What is the @property of @name?")]

Unlike example expressions, a template expression is simpler to write as annotation is avoided by directly specifying the entity type names (prefixed with @). The only trade off is the ease of code readability.

For the sake of this article, we'll add an example expression to our PropertyEnquiry intent.

C#
[Expression("What is the {age} of {Rick}?")]
[Entity("property")]
[Entity("name")]
public void PropertyEnquiry(Context context, Result result)
{
}

Note: All Oscova attributes are available under the Syn.Bot.Oscova.Attributes namespace.

Now, of what use is an intent if it doesn't do anything? This intent must do two things for us:

  1. Select the age column of Rick from database.
  2. Display a message stating which property of the employee was selected.

To do so, we'll add the following within the method body:

C#
public void PropertyEnquiry(Context context, Result result)
{
    var name = result.Entities.OfType("name").Value;
    var property = result.Entities.OfType("property").Value;

    var utility = context.SharedData.OfType<DatabaseUtility>();
    utility.PropertyByName(name, property);

    result.SendResponse($"{property} of Employee \"{name}\".");
}

The above code first gets the matched value of the name entity and later the matched value of the property entity. All extracted entities are stored in the Result.Entities collection. It also turns out that the entities in the collection are usually arranged in their order of occurance in user input.

Next, we fetch the shared DatabaseUtility object and call the PropertyByName method. This method updates the DataGrid in our WPF application by selecting the specified row in Employees table where the name of the employee is equal to the specified name value.

Lastly, by calling the Result.SendResponse method we pass a text response. This response, as seen in the MainWindow constructor changes the content of the ResponseLabel.

The overall intent code should look something like the following:

C#
[Expression("What is the {age} of {Rick}?")]
[Entity("property")]
[Entity("name")]
public void PropertyEnquiry(Context context, Result result)
{
    var name = result.Entities.OfType("name").Value;
    var property = result.Entities.OfType("property").Value;

    var utility = context.SharedData.OfType<DatabaseUtility>();
    utility.PropertyByName(name, property);

    result.SendResponse($"{property} of Employee \"{name}\".");
}

Testing the Intent

Before we proceed with testing the Intent, we need to add the Dialog to Oscova. In the MainWindow constructor just under the comment //Dialogs go here, add the following:

C#
Bot.Dialogs.Add(new DatabaseEnquiryDialog());

There you go. Now press F5 to run the application and in the InputBox, type:

  • What is the age of Rick?
  • What is the salary of Ahmed?

Image 2

If the outputs are as expected, Kudos! you have successfully created your first intent.

Note: By simply annotating values in expressions, we have eliminated the need of writing redundant expressions for different properties.

On a side note, if you click on the JSON Result tab, you should see a serialized version of the evaluation result.

JSON
{
  "query": "WHAT IS THE AGE OF RICK?",
  "sessionId": "78a8765f-3705-442d-a4ca-fe4dbd4c2e04",
  "intents": [
    {
      "intent": "DatabaseEnquiryDialog.PropertyEnquiry",
      "score": 0.9985714285714286
    },
    {
      "intent": "DatabaseEnquiryDialog.EmployeeName",
      "score":  0.59047619047619047
    }
  ],
  "entities": [
    {
      "entity": "AGE",
      "type": "property"
    },
    {
      "entity": "Rick",
      "type": "Name"
    }
  ],
  "contexts": []
}

Note: Intent scores may vary in different bot setups.

More Intents

Before we move into the next phase of creating contextual intents, we'll go ahead and add a few more intents with template expressions solely for the purpose of better understanding.

Intent for input pattern: What is the salary of the CEO?

C#
[Expression("What is the @property of @role")]
[Expression("@property of @role")]
public void PropertyByRole(Context context, Result result)
{
    var role = result.Entities.OfType("role").Value;
    var property = result.Entities.OfType("property").Value;

    var utility = context.SharedData.OfType<DatabaseUtility>();
    utility.PropertyByRole(role, property);

    result.SendResponse($"{property} of \"{role}\".");
}

Intent for input pattern: Who is Wang?

C#
[Expression("Find employee with the name @name")]
[Expression("Who is @name?")]
public void EmployeeName(Context context, Result result)
{
    var name = result.Entities.OfType("name").Value;

    var utility = context.SharedData.OfType<DatabaseUtility>();
    utility.EmployeeByName(name);

    result.SendResponse($"Employee(s) with the name {name}.");
}

Intent for input pattern: Who is the admin?

C#
[Expression("Find employee whose role is @role")]
[Expression("Who is the @role?")]
public void EmployeeRole(Context context, Result result)
{
    var role = result.Entities.OfType("role").Value;

    var utility = context.SharedData.OfType<DatabaseUtility>();
    utility.EmployeeByRole(role);

    result.SendResponse($"Employee(s) with job role \"{role}\".");
}

Contextual Intents

In this section, we will be learning two new concepts:

  1. Contextual Intents
  2. Using pre-built system entities

Though Oscova comes with dozens of pre-built system entities, we'll be making use of just two of them.

  1. Right click the Dialogs folder, choose Add and select Class...
  2. Name the class DatabaseUpdateByNameDialog and select Add.

Just like our previous dialog, derive the DatabaseUpdateByNameDialog from the base Dialog class as shown below:

C#
class DatabaseUpdateByNameDialog : Dialog
{
}

The intent structure within this dialog is going to be as follows:

  • An intent that allows user to change value of an employee's property (age, salary)
  • Affirmative Intent - If user says Yes, commits changes to database.
  • Negative Intent - If user says No, the commit operation is cancelled.

What is a Context?

A context is a string value that represents the current context of user input. In Oscova, contexts have names and lifespans. The lifespan typically being the number of requests the context would last for.

For better handling constant string values. We'll first go ahead and create a static class to hold a constant string value that we'll be using as the name of our context item.

Add a new class to the project and name it DatabaseContext.

C#
static class DatabaseContext
{
    public const string ConfirmUpdateByName = "ConfirmUpdateByName";
}

Now the first intent that lets user make changes to the Database. Double click on DatabaseUpdateByNameDialog.cs and add the following code:

C#
[Expression("@set @property of @name to @sys.number")]
public void ChangePropertyOfName(Context context, Result result)
{
    context.Add(DatabaseContext.ConfirmUpdateByName);

    var property = result.Entities.OfType("property");
    var name = result.Entities.OfType("name");
    var number = result.Entities.OfType<NumberEntity>();

    context.SharedEntities.Add(property);
    context.SharedEntities.Add(name);
    context.SharedEntities.Add(number);

    var propertyString = property.Value.ToLower();
    if (propertyString != "age" && propertyString != "salary")
    {
        result.SendResponse($"{property} of {name} is readonly.");
        return;
    }

    result.SendResponse
    ($"Are you sure that you want to change {property} of {name} to {number}?");
}

The Expression in the above code enables commands like:

  • Change the age of Mark to 43
  • Set the salary of Krishna to 17000

In the template expression, the value @sys.number is a system entity type that matches numbers. There are loads of system entities available for developers to make use of. I encourage you to go through the API Documentation of Oscova for more details.

Note the context.Add(DatabaseContext.ConfirmUpdateByName);, by adding the string value to the context the Bot in its next request will first search for intents within the specified context.

Because on next intent invocation, the current entities in the result gets cleared, we make use of the Context.SharedEntities property to pass current entities to the next intent.

Finally, when the user sends the aforementioned command, we send a response to the user requesting confirmation.

Let us first deal with simpler No follow-up response from the user. For this, we'll create an intent as shown below:

C#
[Expression("{no}")]
[Entity(Sys.Negative)]
[Context(DatabaseContext.ConfirmUpdateByName)]
public void ChangePropertyByNameDeclined(Context context, Result result)
{
    context.SharedEntities.Clear();
    result.SendResponse("Operating cancelled.");
}

There are two things to note in the above intent.

Firstly, [Entity(Sys.Negative)], an Entity attribute specifying a system entity of the type @sys.negative which matches No, Nope and so on. You can find all the entity types as constant fields of the Sys class.

Secondly, [Context(DatabaseContext.ConfirmUpdateByName)] explicitly specifies that this intent is only available under the ConfirmUpdateByName context.

Moving on to Yes follow-up response from the user. We create the following intent:

C#
[Expression("{Yes}")]
[Entity(Sys.Positive)]
[Context(DatabaseContext.ConfirmUpdateByName)]
public void ChangePropertyConfirmed(Context context, Result result)
{
    var property = context.SharedEntities.OfType("property");
    var name = context.SharedEntities.OfType("name").Value;
    var number = context.SharedEntities.OfType<NumberEntity>().Value;

    var utility = context.SharedData.OfType<DatabaseUtility>();
    utility.UpdatePropertyByName(name, property.Value, 
                                 number.ToString(CultureInfo.InvariantCulture));

    result.SendResponse($"{property} of {name} changed to {number}");
    context.SharedEntities.Clear();
}

The above intent's expression annotates yes as a @sys.positive entity type to capture positive follow up responses. Like the ChangePropertyByNameDeclined intent, we specify that the above intent must also be available under the ConfirmUpdateByName context.

Within the method body, we first retrieve the entities that we previously added to the SharedEntities collection and pass in the values to the DatabaseUtility.UpdatePropertyByName function.

After the commits have been made, we send a text response stating that the requested changes have been made and clear the SharedEntities collection.

With the above intent being in place, if the user says Yes or something similar, the changes will get committed to the Employees database.

Testing the Intents

We'll repeat what we did with the previous dialog and add DatabaseUpdateByNameDialog to the Bot.Dialogs collection by placing the following code below the //Dialogs go here comment in the MainWindow constructor.

Bot.Dialogs.Add(new DatabaseUpdateByNameDialog());

Run the application by pressing F5 and type Change the salary of Vincent to 24000.

If all goes well, you should be presented with the following question:

Are you sure you want to change the salary of Vincent to 24000?

Image 3

If you say yeah, the changes will get committed to the database, otherwise the operation gets cancelled.

Well that's all folks!

Got questions or feedback? Let me know in the comments. Thanks for reading!

Points of Interest

Natural Language Processing: For the sake of keeping this article from being a mouthful, I haven't elaborated on connecting the Bot to a lexical database or loading word embeddings before training the bot. Which according to the official documentations is very essential for improving the Natural Language Processing capabilities of the bot. You may find a good read on doing so in the official tutorials page.

The gist of me preferring Oscova is the joy of being able to build intelligent bots and not having to worry about the technicalities involved as Oscova pretty much takes care of everything. The remarkably pluggable architecture of Oscova enables developers to fully customize every aspect of Natural Language Processing which comes in handy in complex bot projects.

Moreover, due to the layman approach of the article, I have refrained from going into many details in this article. There is a wealth of tutorials floating around the official documentations page. I encourage any enthusiast to go through them to fully grasp Oscova's architecture before attempting to build a production-level product.

History

  • 17th January, 2017: Initial article

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionSplitting the name and searching by contains filter on the name Pin
Member 148519347-Nov-20 22:46
Member 148519347-Nov-20 22:46 
QuestionChanged the datagridView to a listview cant find ID errors Pin
Member 148519347-Nov-20 21:26
Member 148519347-Nov-20 21:26 
QuestionBot Pin
Member 134721828-Nov-17 1:30
Member 134721828-Nov-17 1:30 
AnswerRe: Bot Pin
DaveMathews3-Dec-17 23:03
DaveMathews3-Dec-17 23:03 
Pardon my delayed response. I took different approaches in creating the Entity Recognizers to show how one could either just store entity values in memory or directly iterate through a database column. The former is memory intensive while the latter is CPU intensive.
Questiongreetings message Pin
Tilak Bose17-Aug-17 3:33
Tilak Bose17-Aug-17 3:33 
AnswerRe: greetings message Pin
DaveMathews18-Aug-17 5:02
DaveMathews18-Aug-17 5:02 
QuestionHow to connect to External SQL database? Pin
Jim Mathew12-Jun-17 23:39
Jim Mathew12-Jun-17 23:39 
AnswerRe: How to connect to External SQL database? Pin
DaveMathews14-Jun-17 7:34
DaveMathews14-Jun-17 7:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.