Database – Alex Dunn

In this Alexa.Tip series, we explore some little bits of code that can make your life easier in developing Alexa Skills in many languages including C# + .NET, node.jS + TypeScript, Kotlin, etc. We look at concepts that developers might not be aware of, design patterns that and how they can be applied to voice application development, best practices, and more!

In the previous Alexa.Tip, we explored Accessing Lambda Environment Variables in .NET in order to access a secure connection string and initialize a DbContext to use.

If you don’t know how to access Lambda Environment variables already, PLEASE read that post first as this post’s samples will NOT show the use of hard coded connection strings – let’s follow some best practices! 😀

Alright, let’s get into it. If you’re developing Alexa Skills in .NET, you probably need to access data somewhere! At least if you’re building a real-world scale voice application. Also, if you’re a .NET developer, you’re probably used to accessing data through Entity Framework at this point rather than using some of the AWS SDKs and data services such as DynamoDB.

It’s super easy…

Initial Skill

First off, this example uses a super basic skill where the only custom intent is to get facts about animals. Here’s the intent setup in the UI:

You can also find the interactionModel.json here:
https://github.com/SuavePirate/Cross-Platform-Voice-NET/blob/master/src/End/AnimalFacts/AnimalFacts/InteractionModels/alexaInteractionModel.json

Let’s start by abstracting some handlers out so we aren’t writing everything in our Function class – this isn’t JavaScript after all:

WelcomeHandler.cs

public class WelcomeHandler : IResponseHandler
{
    public SkillResponse GetResponse(SkillRequest input)
    {
        return ResponseBuilder.Ask("Welcome to the animal facts voice app! You can ask me about all kinds of animals - try saying, tell me about dogs.");
    }
}

AnimalFactHandler.cs

public class AnimalFactHandler : IAsyncResponseHandler
{
    public async Task<SkillResponse> GetResponse(SkillRequest input)
    {
        // TODO: Do something with data to get the fact.
    }    
}

Then we can setup our Function:

Function.cs

public class Function
{
    // using these interfaces can help us use something like Moq to unit test this bad boy.
    private readonly IResponseHandler _welcomeHandler;
    private readonly IAsyncResponseHandler _animalFactHandler;

    public Function() 
    {
        _welcomeHandler = new WelcomeHandler();
        _animalFactHandler = new AnimalFactHandler();
    }  

    public async Task<SkillResponse> FunctionHandler(SkillRequest input, ILambdaContext context)
    {
        var requestType = input.GetRequestType();

        if (requestType == typeof(IntentRequest))
        {
            if ((input.Request as IntentRequest).Intent.Name == "AnimalFactIntent")
            { 
                return await _animalFactHandler.GetResponse(input);
            }
        }
        else if (requestType == typeof(Alexa.NET.Request.Type.LaunchRequest))
        {
            return _welcomeHandler.GetResponse(input);
        }    

        return ResponseBuilder.Ask("I'm not sure I know how to do that. Try asking me for an animal fact!");
    }
}

So we have a basic setup, but we need to actually implement something in the AnimalFactHandler. In this case, we want to do a SQL search for a fact about the animal that was asked about!

SO let’s start wiring up Entity Framework to this skill:

Add Models and Context

Let’s create a model for our AnimalFact entity to represent our table:

AnimalFact.cs

public class AnimalFact
{
    public int Id { get; set; }
    public string AnimalName { get; set; }
    public string Fact { get; set; }
}

And now let’s create a DbContext implementation with our AnimalFact:

AnimalContext

public class AnimalContext : DbContext
{
    public DbSet<AnimalFact> AnimalFacts { get; set; }
    public AnimalContext(DbContextOptions options)
        : base(options)
    {

    }

    public AnimalContext()
    {

    }
}

NOTE: If you are NOT also using this AnimalContext in a web app that handles the migrations, you’ll need to add either a web app or a console project with .NET Core to handle the migrations since it needs to be an executable project type. The assumption here is that you already have a project running these migrations so you can actually add data to it, but if you are starting here, simply create a new .NET Core web app project or Console app, reference your DbContext from a shared library between the Labmda project and the app project, then run your migrations from the app project.

From here on out, we assume you have the db created from your context and some data in it.

Updating the Skill Lambda

Now that we have a DbContext, let’s add it to our Function and AnimalFactHandler in order to search for the fact!

Let’s go to the AnimalFactHandler and setup the class to take the AnimalContext in the constructor so we can easily unit test and then use the slot value from the request to query for the animal:

AnimalFactHandler.cs

public class AnimalFactHandler : IAsyncResponseHandler
{
    private readonly AnimalContext _context;
    public AnimalFactHandler(AnimalContext context)
    {
        _context = context;
    }

    public async Task<SkillResponse> GetResponse(SkillRequest input)
    {
        // get the animal name from the slot
        var intent = (input.Request as IntentRequest).Intent;
        var animal = intent.Slots["Animal"].Value;

        // query for the fact
        var animalFact = _context.AnimalFacts
                            .Where(a => a.AnimalName.ToLower() == animal.ToLower())
                            .FirstOrDefaultAsync();

        // we found the animal!
        if (animalFact != null)
        {
            return ResponseBuilder.Tell(animalFact.Fact);
        }

        // couldn't find the animal - return a decent response
        return ResponseBuilder.Ask("I don't know about that animal. Try asking me about a different one!");
    }    
}

Now that we have the updated handler, let’s update the entry point to initialize and pass in the AnimalContext.

Function.cs

public class Function
{
    // using these interfaces can help us use something like Moq to unit test this bad boy.
    private readonly IResponseHandler _welcomeHandler;
    private readonly IResponseHandler _animalFactHandler;
    private readonly AnimalContext _context;

    public Function() 
    {
        var connectionString = Environment.GetEnvironmentVariable("DatabaseConnectionString");
        var timeoutSetting = Environment.GetEnvironmentVariable("DatabaseCommandTimeout");
        var optionsBuilder = new DbOptionsBuilder<AnimalFactContext>()
            .UseSqlServer(connectionString, providerOptions => providerOptions.CommandTimeout(int.Parse(timeoutSetting)));
        _context = new AnimalFactContext(optionsBuilder.Build());
        _welcomeHandler = new WelcomeHandler();
        _animalFactHandler = new AnimalFactHandler(_context);
    }  

    public SkillResponse FunctionHandler(SkillRequest input, ILambdaContext context)
    {
        var requestType = input.GetRequestType();

        if (requestType == typeof(IntentRequest))
        {
            if ((input.Request as IntentRequest).Intent.Name == "AnimalFactIntent")
            { 
                return _animalFactHandler.GetResponse(input);
            }
        }
        else if (requestType == typeof(Alexa.NET.Request.Type.LaunchRequest))
        {
            return _welcomeHandler.GetResponse(input);
        }    

        return ResponseBuilder.Ask("I'm not sure I know how to do that. Try asking me for an animal fact!");
    }
}

Now we can publish this and check out our results!

Results

For example, I’ll use Swagger to create an animal fact, and then ask for it!

Boom – created:

And now let’s ask for it!

Conclusion

Adding data-driven responses to your custom Alexa Skills is dead easy using techniques you may already be familiar with if you are a .NET web developer! Show me what you’ve built with .NET and Alexa in the comments!

If you like what you see, don’t forget to follow me on twitter @Suave_Pirate, check out my GitHub, and subscribe to my blog to learn more mobile and AI developer tips and tricks!

Interested in sponsoring developer content? Message @Suave_Pirate on twitter for details.

I’m the Director and Principal Architect over at Voicify. Learn how you can use the Voice Experience Platform to bring your brand into the world of voice on Alexa, Google Assistant, Cortana, chat bots, and more: https://voicify.com/

Thinking about locally storing data and entity structures can be intimidating. You might have a complex data structure in your backend server structure and are trying to match that type of data in your mobile apps. Perhaps you’ve just never done it before and need a solution quickly. You might be over thinking it!

Let’s break it down into some straightforward steps.

Create a Xamarin PCL or Shared project. It doesn’t matter if it’s Xamarin.Forms or native.
Install this nuget package in your PCL or Shared project: https://www.nuget.org/packages/sqlite-net-pcl/
Install the same nuget package in your Android, iOS, UWP, and any other project.

Create your model in your PCL or Shared project. In this case, we will use a basic example model:

public class Profile
{
    public string Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Handle { get; set; }
    public DateTime CreatedDate { get; set; }
}

Create a DbContext class in your PCL or Shared project:

public class DbContext
{
    public static string LocalFilePath; // Set this before creating from platform project
    public SQLiteAsyncConnection Database { get; }
    /// <summary>
    /// Initialized a new DbContext
    /// </summary>
    public DbContext()
    {
        Database = new SQLiteAsyncConnection(LocalFilePath + "localdb.db3");
    }

    /// <summary>
    /// Creates a table for a given type in sql lite
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public async Task<CreateTablesResult> CreateTableAsync<T>() where T : new()
    {
    return await Database.CreateTableAsync<T>();
    }

    /// <summary>
    /// Gets a table by it's type from the db.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public AsyncTableQuery<T> Set<T>() where T : new()
    {
        return Database.Table<T>();
    }
}

Create a GenericRepository Class:

public class GenericSqliteRepository<T> : IGenericRepository<T> where T : new()
{
    protected readonly DbContext _context;
    public GenericSqliteRepository(DbContext context)
    {
        _context = context;
    }
    public virtual async Task InitializeAsync()
    {
        await _context.CreateTableAsync<T>();
    }
    public virtual async Task AddAsync(T entity)
    {
        await _context.Database.InsertOrReplaceAsync(entity);
    }
    public virtual async Task AddRangeAsync(IEnumerable<T> entities)
    {
        await _context.Database.InsertAllAsync(entities);
    }

    public virtual async Task<T> FindAsync(Expression<Func<T, bool>> predicate)
    {
        return await _context.Set<T>().Where(predicate).FirstOrDefaultAsync();
    }

    public virtual async Task<IEnumerable<T>> GetAsync(Expression<Func<T, bool>> predicate)
    {
        return await _context.Set<T>().Where(predicate).ToListAsync();
    }

    public virtual async Task<IEnumerable<T>> GetAsync(int skip, int take)
    {
        return await _context.Set<T>().Skip(skip).Take(take).ToListAsync();
    }

    public virtual async Task<IEnumerable<T>> GetAsync(Expression<Func<T, bool>> predicate, int skip, int take)
    {
        return await _context.Set<T>().Where(predicate).Skip(skip).Take(take).ToListAsync();
    }

    public virtual async Task RemoveAsync(T entity)
    {
        await _context.Database.DeleteAsync(entity);
    }

    public virtual async Task UpdateAsync(T entity)
    {
        await _context.Database.UpdateAsync(entity);
    }
}

Create a ProfileRepository Class:

public class ProfileRepository : GenericSqliteRepository<Post>
{
    public ProfileRepository(DbContext context) : base(context)
    {
    }
}

Set your file path in your platform code:

Android

DbContext.LocalFilePath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);

iOS

var docFolder = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
string libFolder = Path.Combine(docFolder, "..", "Library", "Databases");

if (!Directory.Exists(libFolder))
{
    Directory.CreateDirectory(libFolder);
}
DbContext.LocalFilePath = libFolder;

Use your repository in your shared code or platform code or wherever you want.

var repo = new ProfileRepository(new DbContext());
await repo.InitializeAsync();
...
await repo.AddAsync(new Profile {...});
...
var profiles = await repo.GetAsync(0, 10);
...
var profile = await repo.FindAsync(p => p.Id == "foo");
...

Start storing your things!

This is obviously a simple situation that doesn’t cover all needs, but it’s a place to start for complex data models. Build repositories for each of your types. Control your queries that are very specific in those model-specific repositories.

Look out for a follow up post about some patterns and tips to use for complex and large data sets!

Tag: Database

Alexa.Tip – Using Entity Framework in Your C# Alexa Skill Lambda

Initial Skill

Add Models and Context

Updating the Skill Lambda

Results

Conclusion

Xamarin.Tips – Super Simple Sqlite

Initial Skill

Add Models and Context

Updating the Skill Lambda

Results

Conclusion

Share this:

Share this: