Connect Azure OpenAI Service to your custom SQL Server data

In this tutorial, we will lear how to connect your custom SQL Server data to Azure OpenAI. You need to have an active Azure subscription in order to proceed with this tutorial.

Create Azure Cognitive Search

Point your browser to https://portal.azure.com/. Enter 'search' in the search field at the top of the page, then click on "Cognitive Search" from the short list of services.

 

On the resulting "Azure AI services | Cognitive search" page, click on the Create button.

On the "Create a search service" page:

• choose a suitable subscription if you have more than one
• create a new resource group named openai-and-sql-server
• let the "Service name" be openai-and-sql-server-service-name
• let the location be a data center closer to where you are. In my case I chose "East US".
• leave the "Pricing tier" as Standard

Click on the blue "Review + create" button, then click on blue "Create" button. Once deployment is complete, click on the blue "Go to resource" button. This takes you to the page of the search resource that you just created.

Click on the blue Import button under "Connect your data".

On the import data page, let "Data Source" be Samples. Then click on realestate-us-sample.

Click on the blue "Next: Add cognitive skills (Optional)" button at the bottom of the page.

On the "Import data" page, click on the blue "Skip to: Customize target index" at the bottom of the page.

You will be taken to the "Customize target index" tab. Click on the blue "Next: Create an indexer" button at the bottom of the page.

In the "Create an indexer" tab, click on the blue Submit button.

In your main service page, click on indexers on the left side. Wait until the indexer you just created shows that it is successfully provisioned.

Click on Overview on the left-side menu, then click on the "Search explorer" link.

On the "Search explorer" page, we can query the database. The Index field is defaulted to the index that was just created. In the "Query string" field, enter search=condo, then click on the blue Search button.  You will see results for condos.

Azure OpenAI Service

We are now ready to connect this cognitive search service with OpenAI. Click on the Home link in the top left corner of the page.

In the filter field at the top, enter the word openai, then select Azure OpenAI.

Click on the "+ Create" link. 

Fill out the form parameters. I entered the data shown below.

Click on the blue Next button at the bottom of the page. Accept default values on the Network tab then click on blue Next button.

Also, accept default values on the Tags tab then click on blue Next button.

Click on the blue Create button when it appears at the bottom of tthe page.

Deployment takes some time. When it is complete, cclik on the blue "Go to resource" button.

Click on the Explore button.

Click on "Bring your own data".

We will need to create a deployment. Click on "Create new deployment".

Expand "Advanced options. Choose the gpt-35-turbo model, give the deployment a name (gpt-35-turbo-deploy), leave Default for "Content Filter". Click on the Create button.

On the "Data source" tab, choose as follows:

Select data source: Azure Cognitive Search
Subscription: {your azure subscription}

Azure Cognitive Search service: {the cognitive service you created earlier}

Azure Cognitive Search Index: {the cognitive search index that was created earlier.

Enable "I acknowledge that connecting to an Azure Cognitive Search account will incue usage too my account", then click on Next.

Choose all the fields for "Content data". 

Choose description for "Field name" and Title fields then click on Next.

Chooose Keyword for "Search type", then click oon Next.

Finally, click on the "Save and close" button on the "Review and finish" tab.

On the "Chat playground" page, the data source is automatically connected to the chat session.

Enter the following in the chat field: property in renton. Then, hit ENTER on your keyboard.

A response similar to the following will appear:

Conclusion

We were able to link Azure OpenAI with custom data and generate output through ChatGPT chat. The data source, of course, can be any enterprise relational data.

Reading appsettings.json from a C# static method

Overview

In this tutorial I will demonstrate an easy way to read configuration settings in appsettings.json from a static C# method. This technique comes in handy when you are building a non-traditional C# application where you cannot use dependency injection to access the configuration object. I will demonstrate my solution with a simple C# console application.

Getting Started

dotnet new console -o ConfigDemo

cd ConfigDemo

We need to install a package to help us read JSON based configuration files:

dotnet add package Microsoft.Extensions.Configuration.Json

In the root folder of your application, create a file name appsettings.json with the following content that specifies a database connection string:

{

  "ConnectionStrings": {

    "DefaultConnection": "DataSource=foo.db;Cache=Shared;"

  }

}

When our application gets built and packaged, we want this file to get copied to the output directory. Therefore, we need to add the following XML to the ConfigDemo.csproj file just before the closing </Project> tag.

<ItemGroup>

  <None Include="*.json" CopyToOutputDirectory="PreserveNewest" />

</ItemGroup> 

The Code

Let us create a helper class with a static method named GetConfigValue() that reads from the appsettings.json file. Create a C# class named Utils.cs and add to it the following code:

public class Utils {

    public static string GetConfigValue(string config) {

        IConfigurationBuilder builder = new ConfigurationBuilder();

        if (System.IO.File.Exists("appsettings.json"))

            builder.AddJsonFile("appsettings.json", false, true);

        if (System.IO.File.Exists("appsettings.Development.json"))

            builder.AddJsonFile("appsettings.Development.json", false, true);

        IConfigurationRoot root = builder.Build();

        return root[config]!;

    }

}

The above code first checks appsettings.json for a configuration setting. If it does not find it there then it looks into appsettings.Development.json.

Note that you need to add the following using statement at the top of Utils.cs:

using Microsoft.Extensions.Configuration;

Using our static method

Replace the code in Program.cs with the following:

var connStr = Utils.GetConfigValue("ConnectionStrings:DefaultConnection");

Console.WriteLine($"Connection string: {connStr}");

Run the application. The output should look like this:

Connection string: DataSource=foo.db;Cache=Shared;

Placing database connection strings in appsettings.json is not a good idea. It is best to save it in appsettings.Development.json while making sure that the latter is in your .gitignore so it does not get pushed into source control.

Copy appsettings.json to appsettings.Development.json. Thereafter, delete the following from appsettings.json:

"ConnectionStrings": {

    "DefaultConnection": "DataSource=foo.db;Cache=Shared;"

}

Run the application again. You should get the same results with the connection string being read from appsettings.Development.json instead of appsettings.json.

I hope this helps in making you an even better C# developer.