{"id":1139,"date":"2022-02-08T10:31:32","date_gmt":"2022-02-08T18:31:32","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=1139"},"modified":"2022-02-08T10:31:32","modified_gmt":"2022-02-08T18:31:32","slug":"saving-bot-activities-in-azure-sql-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/saving-bot-activities-in-azure-sql-database\/","title":{"rendered":"Saving Bot Activities in Azure SQL Database"},"content":{"rendered":"<p>In this blog post, we\u2019ll walkthrough of how to integrate Bot Framework v4 SDK with\u00a0<a href=\"https:\/\/azure.microsoft.com\/en-us\/products\/azure-sql\/database\/#overview\">Azure SQL Database<\/a>\u00a0and save Bot activities\/ conversations into the database.<\/p>\n<p>We\u2019ll leverage\u00a0<a href=\"https:\/\/www.nuget.org\/packages\/Bot.Builder.Community.Storage.EntityFramework\/\"><code class=\"language-plaintext highlighter-rouge\">Bot.Builder.Community.Storage.EntityFramework<\/code><\/a>\u00a0package to save conversations using EntityFrameworkTranscriptStore with TranscriptLoggerMiddleware into Azure SQL Database.<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2022\/02\/bot-azsql.png\"><img decoding=\"async\" class=\" wp-image-1154 aligncenter\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2022\/02\/bot-azsql-300x256.png\" alt=\"Image bot azsql\" width=\"410\" height=\"349\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2022\/02\/bot-azsql-300x256.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2022\/02\/bot-azsql.png 585w\" sizes=\"(max-width: 410px) 100vw, 410px\" \/><\/a><\/p>\n<h3>Let&#8217;s start &#8211;<\/h3>\n<h3 id=\"create-azure-sql-database\">Create Azure Sql Database:<span class=\"sr-only\">Permalink<\/span><i class=\"fas fa-link\"><\/i><\/h3>\n<p>If you are new to Azure SQL and looking to understand more about different offerings of Azure SQL, you can start with\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/azure-sql\/azure-sql-iaas-vs-paas-what-is-overview\">this article<\/a>.<\/p>\n<p>Once you have decided the type of Azure SQL Database that suits your requirement, you can navigate to the <a href=\"https:\/\/ms.portal.azure.com\/#create\/Microsoft.AzureSQL\">Select SQL Deployment options<\/a>\u00a0page and create your Database.<\/p>\n<blockquote><p>For this demo, I have created a single database in the\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/azure-sql\/database\/serverless-tier-overview\">serverless compute tier<\/a>.<\/p><\/blockquote>\n<p>You can follow\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/azure-sql\/database\/single-database-create-quickstart?tabs=azure-portal\">this article<\/a>\u00a0which explains the steps of creating an Azure SQL Database.<\/p>\n<h3 id=\"connect-to-the-database\">Connect to the database:<span class=\"sr-only\">Permalink<\/span><i class=\"fas fa-link\"><\/i><\/h3>\n<p>Once your Azure sql database is created, you can use the\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/azure-sql\/database\/single-database-create-quickstart?tabs=azure-portal#query-the-database\">Query editor (preview)<\/a>\u00a0in the Azure portal to connect to the database and execute queries. Alternatively, you can also connect your database using\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ssms\/download-sql-server-management-studio-ssms?view=sql-server-ver15\">SSMS<\/a>\u00a0and\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/azure-data-studio\/download-azure-data-studio?view=sql-server-ver15\">Azure Data Studio<\/a>, etc.<\/p>\n<ul>\n<li>In the Azure portal, search for and select SQL databases, and then select your database from the list.<\/li>\n<li>On the page for your database, select Query editor (preview) in the left menu.<\/li>\n<li>Enter your server admin login information and select OK.<\/li>\n<\/ul>\n<p><img decoding=\"async\" title=\"image\" src=\"https:\/\/abhimantiwari.github.io\/Content\/query-editor-login.png\" alt=\"image\" width=\"600\" height=\"400\" border=\"0\" \/><\/p>\n<blockquote><p>Note: Click on the\u00a0<code class=\"language-plaintext highlighter-rouge\">Connection strings<\/code>\u00a0link in the above blade and copy the connection string somewhere which we\u2019ll use later in this demo.<\/p><\/blockquote>\n<h3 id=\"create-tables-in-your-azure-sql-database\">Create tables in your Azure SQL database:<a class=\"header-link\" title=\"Permalink\" href=\"https:\/\/abhimantiwari.github.io\/blog\/Saving-Bot-Activities-in-Azure-SQL\/#create-tables-in-your-azure-sql-database\"><span class=\"sr-only\">Permalink<\/span><i class=\"fas fa-link\"><\/i><\/a><\/h3>\n<p>Now, you need to create tables in your Azure SQL database to store the Bot Activities.<\/p>\n<ul>\n<li>Execute the\u00a0<a href=\"https:\/\/github.com\/abhimantiwari\/botbuilder-community-dotnet\/blob\/develop\/samples\/EntityFramework%20Storage%20Sample\/CreateTableScript.sql\">CreateTableScript.sql<\/a>\u00a0script in your Azure SQL Database.<\/li>\n<li>Once you execute the\u00a0<code class=\"language-plaintext highlighter-rouge\">CreateTableScript.sql<\/code> script in your Azure SQL Database, you should be able to see these two tables (<code class=\"language-plaintext highlighter-rouge\">BotDataEntity<\/code>\u00a0&amp;\u00a0<code class=\"language-plaintext highlighter-rouge\">TranscriptEntity<\/code>) created.<\/li>\n<\/ul>\n<p><img decoding=\"async\" title=\"image\" src=\"https:\/\/abhimantiwari.github.io\/Content\/EmptyBotTables.png\" alt=\"image\" width=\"600\" height=\"400\" border=\"0\" \/><\/p>\n<blockquote><p>We have just created the table, it doesn&#8217;t contain any data yet.<\/p><\/blockquote>\n<h3 id=\"getting-bot-project-ready\">Getting Bot project ready:<a class=\"header-link\" title=\"Permalink\" href=\"https:\/\/abhimantiwari.github.io\/blog\/Saving-Bot-Activities-in-Azure-SQL\/#getting-bot-project-ready\"><span class=\"sr-only\">Permalink<\/span><i class=\"fas fa-link\"><\/i><\/a><\/h3>\n<p>For this Demo, we will be using\u00a0<a href=\"https:\/\/dev.botframework.com\/\">Bot Framework<\/a>\u00a0v4 Echo bot sample which accepts input from the user and echoes it back.<\/p>\n<p>You may download the working code sample from <a href=\"https:\/\/github.com\/abhimantiwari\/botbuilder-community-dotnet\/tree\/develop\/samples\/EntityFramework%20Storage%20Sample\">GitHub<\/a> or make the following changes to your existing Bot project.<\/p>\n<ul>\n<li>Import these packages into your Bot project.<\/li>\n<li><img decoding=\"async\" title=\"image\" src=\"https:\/\/abhimantiwari.github.io\/Content\/BotBuilderPackages.png\" alt=\"image\" width=\"600\" height=\"400\" border=\"0\" \/><\/li>\n<li>Add the following code to your bot project\u2019s Startup.cs.\n<div class=\"language-ruby highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code> <span class=\"n\">var<\/span> <span class=\"n\">loggerConnectionString<\/span> <span class=\"o\">=<\/span> <span class=\"no\">Configuration<\/span><span class=\"p\">[<\/span><span class=\"s2\">\"StoreConnectionString\"<\/span><span class=\"p\">];<\/span>\r\n <span class=\"n\">var<\/span> <span class=\"n\">logger<\/span> <span class=\"o\">=<\/span> <span class=\"n\">new<\/span> <span class=\"no\">EntityFrameworkTranscriptStore<\/span><span class=\"p\">(<\/span><span class=\"n\">loggerConnectionString<\/span><span class=\"p\">);<\/span>\r\n <span class=\"n\">services<\/span><span class=\"o\">.<\/span><span class=\"no\">AddSingleton<\/span><span class=\"o\">&lt;<\/span><span class=\"no\">ITranscriptStore<\/span><span class=\"o\">&gt;<\/span><span class=\"p\">(<\/span><span class=\"n\">logger<\/span><span class=\"p\">);<\/span>\r\n<\/code><\/pre>\n<\/div>\n<\/div>\n<p><img decoding=\"async\" title=\"image\" src=\"https:\/\/abhimantiwari.github.io\/Content\/BotStartupClass.png\" alt=\"image\" width=\"700\" height=\"400\" border=\"0\" \/><\/li>\n<li>Add ITranscriptStore as a parameters to AdapterWithErrorHandler class, and Use TranscriptLoggerMiddleware:\n<div class=\"language-ruby highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code>   <span class=\"kp\">public<\/span> <span class=\"no\">AdapterWithErrorHandler<\/span><span class=\"p\">(<\/span><span class=\"no\">ITranscriptStore<\/span> <span class=\"n\">transcriptLogger<\/span><span class=\"p\">,<\/span> <span class=\"no\">IConfiguration<\/span> <span class=\"n\">configuration<\/span><span class=\"p\">,<\/span> <span class=\"no\">ILogger<\/span><span class=\"o\">&lt;<\/span><span class=\"no\">BotFrameworkHttpAdapter<\/span><span class=\"o\">&gt;<\/span> <span class=\"n\">logger<\/span><span class=\"p\">,<\/span> <span class=\"no\">ConversationState<\/span> <span class=\"n\">conversationState<\/span> <span class=\"o\">=<\/span> <span class=\"n\">null<\/span><span class=\"p\">)<\/span>\r\n          <span class=\"p\">:<\/span> <span class=\"n\">base<\/span><span class=\"p\">(<\/span><span class=\"n\">configuration<\/span><span class=\"p\">,<\/span> <span class=\"n\">logger<\/span><span class=\"p\">)<\/span>\r\n      <span class=\"p\">{<\/span>\r\n          <span class=\"no\">Use<\/span><span class=\"p\">(<\/span><span class=\"n\">new<\/span> <span class=\"no\">TranscriptLoggerMiddleware<\/span><span class=\"p\">(<\/span><span class=\"n\">transcriptLogger<\/span><span class=\"p\">));<\/span>\r\n<\/code><\/pre>\n<\/div>\n<\/div>\n<p><img decoding=\"async\" title=\"image\" src=\"https:\/\/abhimantiwari.github.io\/Content\/AdapterWithErrorHandler.png\" alt=\"image\" width=\"900\" height=\"500\" border=\"0\" \/><\/li>\n<li>The ConnectionString you have copied from Azure sql database, put that into your Bot project against <code class=\"language-plaintext highlighter-rouge\">StoreConnectionString<\/code>\u00a0key in appsettings.json file, something like shown below &#8211;\n<div class=\"language-ruby highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code>  <span class=\"p\">{<\/span>\r\n    <span class=\"s2\">\"MicrosoftAppId\"<\/span><span class=\"p\">:<\/span> <span class=\"s2\">\"\"<\/span><span class=\"p\">,<\/span>\r\n    <span class=\"s2\">\"MicrosoftAppPassword\"<\/span><span class=\"p\">:<\/span> <span class=\"s2\">\"\"<\/span><span class=\"p\">,<\/span>\r\n    <span class=\"s2\">\"StoreConnectionString\"<\/span><span class=\"p\">:<\/span> <span class=\"s2\">\"Server=tcp:YourSQLServerName.database.windows.net,1433;Initial Catalog=YourDatabaseName;Persist Security Info=False;User ID=YourSqlUserId;Password=YourSqlPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;\"<\/span>\r\n  <span class=\"p\">}<\/span>\r\n<\/code><\/pre>\n<\/div>\n<\/div>\n<\/li>\n<\/ul>\n<p><div class=\"alert alert-primary\"><p class=\"alert-divider\"><i class=\"fabric-icon fabric-icon--Info\"><\/i><strong>Note:<\/strong><\/p>I have left MicrosoftAppId and MicrosoftAppPassword blank as I\u2019ll be running my Bot locally in Emulator but if your Bot is deployed on some remote server e.g., Azure app service or Bot needs to communicate to other external services e.g., Cognitive services, then you will need to put MicrosoftAppId and MicrosoftAppPassword as well to authenticate your Bot.<\/div><\/p>\n<p>We are almost done. Now you should be able to run your Bot project.<\/p>\n<h3 id=\"lets-see-it-in-action\">Let\u2019s see it in Action:<span class=\"sr-only\">Permalink<\/span><i class=\"fas fa-link\"><\/i><\/h3>\n<p>Run your bot code\/ project (in case you have already hosted it to some web server e.g., Azure app service, you may skip this part) &#8211;<\/p>\n<ul>\n<li>If you are running from the terminal, navigate to your Bot project folder and run\u00a0<code class=\"language-plaintext highlighter-rouge\">dotnet run<\/code>\u00a0command.\n<div class=\"language-ruby highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"c1\">#run the bot<\/span>\r\n<span class=\"n\">dotnet<\/span> <span class=\"n\">run<\/span>\r\n<\/code><\/pre>\n<\/div>\n<\/div>\n<\/li>\n<li>If you are running from Visual Studio\n<ul>\n<li>Launch Visual Studio<\/li>\n<li>File -&gt; Open -&gt; Project\/Solution<\/li>\n<li>Navigate to your Bot project folder e.g., EntityFrameworkTranscriptStoreExample<\/li>\n<li>Select your Bot project file e.g., EntityFrameworkTranscriptStoreExample.csproj file<\/li>\n<li>Press F5 to run the project<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>Note: As a prerequisite to run this project, make sure you have\u00a0<a href=\"https:\/\/dotnet.microsoft.com\/en-us\/download\">.NET Core<\/a> (&gt;=3.1) version installed on your machine.<\/p>\n<h3 id=\"test-the-bot-using-bot-framework-emulator\">Test the Bot using Bot Framework Emulator:<span class=\"sr-only\">Permalink<\/span><i class=\"fas fa-link\"><\/i><\/h3>\n<p><a href=\"https:\/\/github.com\/microsoft\/botframework-emulator\">Bot Framework Emulator<\/a> is a desktop application that allows Bot developers to Test and debug their bots on localhost or run remotely through a tunnel. You may install the Bot Framework Emulator from <a href=\"https:\/\/github.com\/Microsoft\/BotFramework-Emulator\/releases\">here<\/a>.<\/p>\n<h4 id=\"connect-to-the-bot-using-bot-framework-emulator\">Connect to the bot using Bot Framework Emulator:<span class=\"sr-only\">Permalink<\/span><i class=\"fas fa-link\"><\/i><\/h4>\n<ul>\n<li>Launch Bot Framework Emulator<\/li>\n<li>File -&gt; Open Bot<\/li>\n<li>Enter a Bot URL e.g.,\u00a0<code class=\"language-plaintext highlighter-rouge\">http:\/\/localhost:3978\/api\/messages<\/code>. You may also enter the URL of the remote web server hosting the Bot code but, in that case, you will also need to provide <code class=\"language-plaintext highlighter-rouge\">MicrosoftAppId<\/code>\u00a0and\u00a0<code class=\"language-plaintext highlighter-rouge\">MicrosoftAppPassword<\/code>\u00a0for Bot to successfully authenticate.<\/li>\n<\/ul>\n<p><em>Once connected, send some messages to your Bot, and it should echo them back.<\/em><img decoding=\"async\" title=\"image\" src=\"https:\/\/abhimantiwari.github.io\/Content\/BotActivitiesinEmulator.png\" alt=\"image\" width=\"700\" height=\"400\" border=\"0\" \/><\/p>\n<h3 id=\"logs\">Logs:<span class=\"sr-only\">Permalink<\/span><i class=\"fas fa-link\"><\/i><\/h3>\n<p>Let\u2019s\u00a0<a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/saving-bot-activities-in-azure-sql#connect-to-the-database\">Connect\u00a0the database<\/a> to see if Bot Activities are being recorded. You should see the messages saved into the Azure SQL tables we have created.<\/p>\n<ul>\n<li><code class=\"language-plaintext highlighter-rouge\">BotDataEntity<\/code>\u00a0should store one entry for each unique connection.<\/li>\n<li><code class=\"language-plaintext highlighter-rouge\">TranscriptEntity<\/code>\u00a0should contain all the conversations.\u00a0<img decoding=\"async\" title=\"image\" src=\"https:\/\/abhimantiwari.github.io\/Content\/BotSqlTableTransactions.png\" alt=\"image\" width=\"900\" height=\"600\" border=\"0\" \/><\/li>\n<\/ul>\n<p>If you query the tables, you should be able to fetch all the messages exchanged between the user and Bot.<\/p>\n<p><img decoding=\"async\" title=\"image\" src=\"https:\/\/abhimantiwari.github.io\/Content\/SavedBotActivities.png\" alt=\"image\" width=\"600\" height=\"400\" border=\"0\" \/><\/p>\n<p><div class=\"alert alert-danger\"><p class=\"alert-divider\"><i class=\"fabric-icon fabric-icon--ErrorBadge\"><\/i><strong>Caution:<\/strong><\/p>Please note that depending on your Bot, you may need to specify in your Bot\u2019s terms of use directly to notify your users whenever storing user data is involved.<\/div><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this blog post, we\u2019ll walkthrough of how to integrate Bot Framework v4 SDK with\u00a0Azure SQL Database\u00a0and save Bot activities\/ conversations into the database. We\u2019ll leverage\u00a0Bot.Builder.Community.Storage.EntityFramework\u00a0package to save conversations using EntityFrameworkTranscriptStore with TranscriptLoggerMiddleware into Azure SQL Database. Let&#8217;s start &#8211; Create Azure Sql Database:Permalink If you are new to Azure SQL and looking to understand [&hellip;]<\/p>\n","protected":false},"author":29335,"featured_media":81,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[505,506],"class_list":["post-1139","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","tag-azure-bot","tag-storing-bot-activities"],"acf":[],"blog_post_summary":"<p>In this blog post, we\u2019ll walkthrough of how to integrate Bot Framework v4 SDK with\u00a0Azure SQL Database\u00a0and save Bot activities\/ conversations into the database. We\u2019ll leverage\u00a0Bot.Builder.Community.Storage.EntityFramework\u00a0package to save conversations using EntityFrameworkTranscriptStore with TranscriptLoggerMiddleware into Azure SQL Database. Let&#8217;s start &#8211; Create Azure Sql Database:Permalink If you are new to Azure SQL and looking to understand [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/1139","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/29335"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=1139"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/1139\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/81"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=1139"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=1139"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=1139"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}