By: Daniel Calbimonte | Updated: 2022-02-15 | Comments | Related: > Azure
Problem
I have several places where we collect and store text data in Azure SQL DB tables. How can we detect and extract key phrases from the collected data? Is there an Azure service to assist with this task?
Solution
In a previous article we looked at how to Detect the Language with Azure AI Language Service. In this article we are going to create an application that can extract key phrases from Azure SQL tables.
Here is an application I wrote to detect key phrases using AI. If you click on the phrase, it will show a list of key phrases. We will show how this can be done in this article.
Requirements
- A machine with Visual Studio (VS).
- An Azure Account to create an Azure SQL Database
- A database in Azure SQL. If you do not have one, you can check the C# to Azure SQL related article.
Getting Started
The tip will have 3 parts:
- Create an Azure SQL table
- Create an Azure AI Text Analysis Service
- Create a Windows Form to get the Azure SQL data and extract key phrases
Create an Azure SQL table
First, we need to create an Azure SQL Database. In this database, we will create a table named phrase with the columns ID and phrases.
Our article C# to Azure SQL explains how to create an Azure SQL database. Note that the Server admin is admindaniel. If you do not remember the password of the Server admin, you can reset the password. Also, the server’s name will be useful to connect to the database later.
The database name in this example is mymssqltips. This information will be used later. Press the Query editor to create a table with data.
Run the following T-SQL commands to create the dbo.phrase table and insert some rows of data.
CREATE TABLE [dbo].[phrase]( [id] [smallint] NULL, [phrases] [nchar](4000) NULL ) ON [PRIMARY] GO INSERT [dbo].[phrase] ([id], [phrases]) VALUES (1, N'Obama was born in Honolulu, Hawaii. After graduating from Columbia University in 1983, he worked as a community organizer in Chicago. In 1988, he enrolled in Harvard Law School, where he was the first black president of the Harvard Law Review. ') INSERT [dbo].[phrase] ([id], [phrases]) VALUES (2, N'Barack Hussein Obama (About this soundlisten) bə-RAHK hoo-SAYN oh-BAH-mə;[1] born August 4, 1961) is an American politician, lawyer, and author who served as the 44th president of the United States from 2009 to 2017. A member of the Democratic Party, Obama was the first African-American president of the United States.[ ') INSERT [dbo].[phrase] ([id], [phrases]) VALUES (3, N'Obama signed many landmark bills into law during his first two years in office. ') GO
Create an Azure AI Text Analysis Service
Azure provides services with AI to do different things like detect key phrases, detect the language used, answer questions and more.
In this example, we will use the text analysis to read text and extract key phrases. To do that, we need to create an endpoint in the Azure Portal and get the endpoint’s URL and the key.
In the Azure Portal, create a resource AI+Machine Learning and select Language Service and press Create.
For more information about creating a Language service, please refer to our Detect the language with AI for more details.
Once created, we need 2 things: the Endpoint’s URL and the key. Click the option to manage keys.
You can copy the key to the clipboard. That information will be used to connect to the Azure Service from the application.
Create a Windows Form to get Azure SQL Data and extract key phrases
In the previous sections, we created an Azure SQL table with some phrases. We then created an Azure Endpoint to provide AI text analysis. Now, we will create a C# application to get the data and extract the information.
In VS, go to File > New Project and select Windows Forms App.
In the Solution Explorer, right click on Packages and select Manage NuGet Packages to add packages.
Search for the Microsoft.Data.SqlClient and install it. This package allows us to connect to SQL Server or Azure SQL.
Also, install the Azure.AI.TextAnalytics to access the AI TextAnalytics service. This is the library to connect to the Azure AI Service for text analysis.
In the Windows form, drag and drop a DataGridView, a ListBox and a label.
The form should look like this:
Now add this code to the form:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using Azure;
using Azure.AI.TextAnalytics;
namespace keyphrase
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
//Endpoint url and credentials
private static readonly AzureKeyCredential credentials = new AzureKeyCredential("f3d03934563b9d3987f5f678e88717");
private static readonly Uri endpoint = new Uri("https://textanalysisms.cognitiveservices.azure.com/");
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
//Clear the listbox
listBox1.Items.Clear();
DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
//Connect using the credentials
var client = new TextAnalyticsClient(endpoint, credentials);
//Extract the key phrases of the cell selected
var response = client.ExtractKeyPhrases(row.Cells[1].Value.ToString());
foreach (string keyphrase in response.Value)
{
//Add the keypharase to the listbox
listBox1.Items.Add(keyphrase);
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
//Create a string connection
string myconn = "Server=mssqltipsserver.database.windows.net;Database=mymssqltips;User ID=admindaniel;Password=thipwd2334!;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
//create a query to Azure
string Azurequery = "SELECT * FROM dbo.phrase";
SqlConnection myconnection = new SqlConnection(myconn);
//Create a sql data adapter
SqlDataAdapter mydapter = new SqlDataAdapter(Azurequery, myconnection);
//Create a new dataset
DataSet mydataset = new DataSet();
//Open the connection
myconnection.Open();
//Fill Data Adapter with the information from phrase table
mydapter.Fill(mydataset, "phrase");
//close connection
myconnection.Close();
//Bind the datagridview1 with the data set mydataset
dataGridView1.DataSource = mydataset;
dataGridView1.DataMember = "phrase";
//Increase the size of the column
DataGridViewColumn column = dataGridView1.Columns[1];
column.Width = 350;
}
}
}
Let’s explain the code.
First, we invoke the namespaces.
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Data.SqlClient; using Azure; using Azure.AI.TextAnalytics;
They are all defaults, except these ones:
- System.Data.SqlClient is used to connect to the Azure SQL databases.
- Azure.AI.TextAnalytics is used to connect to the Azure AI text service.
Then we provide the endpoint URL and the keys. This information is from the "Create an Azure AI Text Analysis Service" section above. You need the Azure key and the endpoint URL.
//Endpoint url and credentials
private static readonly AzureKeyCredential credentials = new AzureKeyCredential("f3d03934563b9d3987f5f678e88717");
private static readonly Uri endpoint = new Uri("https://textanalysisms.cognitiveservices.azure.com/");
When we load the form, we load with the Azure SQL information from the dbo.phrase table.
private void Form1_Load(object sender, EventArgs e)
{
The string connection, stores the following information.
- The Azure Server name (mssqltipsserver.database.windows.net)
- The Azure database name (mymssqltips).
- The Azure administrator for the Azure Server (admindaniel)
- The Azure administrator password
//Create a string connection
string myconn = "Server=mssqltipsserver.database.windows.net;Database=mymssqltips;User ID=admindaniel;Password=thipwd2334!;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
We create a query to the dbo.phrase Azure SQL table. Creating a SqlDataAdapter.
//create a query to Azure
string Azurequery = "SELECT * FROM dbo.phrase";
SqlConnection myconnection = new SqlConnection(myconn);
//Create a sql data adapter
SqlDataAdapter mydapter = new SqlDataAdapter(Azurequery, myconnection);
We will create a DataSet, fill the Adapter with the dataset and send the information to the dataGridView1 to show the data.
//Create a new dataset
DataSet mydataset = new DataSet();
//Open the connection
myconnection.Open();
//Fill Data Adapter with the information from phrase table
mydapter.Fill(mydataset, "phrase");
//close connection
myconnection.Close();
//Bind the datagridview1 with the data set mydataset
dataGridView1.DataSource = mydataset;
dataGridView1.DataMember = "phrase";
//Increase the size of the column
DataGridViewColumn column = dataGridView1.Columns[1];
column.Width = 350;
}
}
}
Then in the dataGridView1, we will create an event when clicking on the cell to get the key phrases of the selected cell. The code will detect the cell selected and add the key phrases to the listbox1.
The listBox1 will be cleared each time a new cell is selected.
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
//Clear the listbox
listBox1.Items.Clear();
DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
//Connect using the credentials
var client = new TextAnalyticsClient(endpoint, credentials);
//Extract the key phrases of the cell selected
var response = client.ExtractKeyPhrases(row.Cells[1].Value.ToString());
foreach (string keyphrase in response.Value)
{
//Add the keypharase to the listbox
listBox1.Items.Add(keyphrase);
}
}
}
If everything is setup correctly, you will be able to get the phrases from Azure SQL in the Datagridview and get the key phrases in the listbox.
Conclusion
In this article, we learned how to create an Azure Language Service and how to get key phrases using the Azure AI services. We used data from Azure SQL and then extract the key phrases from the data.
Next Steps
For more information refer to the following links:
- How to Get Started with SQL Server and .NET
- .NET Framework Data Providers
- Azure Text Analytics and Power BI
- Detect the Language with Azure AI Language Service
About the author
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-02-15
