can we pump sql table in azure cognitive search using C# code?

Question

can we pump the SQL table in azure cognitive search using the C# code?

One approach is available to add JSON format documents in the azure cognitive search index on Microsoft learning document.

But can we do this by writing a SQL query and the index should be created in the same format as the SQL column with all the records?

Answer 1

let me simplify

• Read the data from the SQL table using ADO.NET or Entity Framework etc
• Format the data as JSON documents.
• Add the JSON documents to the Azure Cognitive Search index using Azure Cognitive Search SDK.

how to

1. Install the Azure.Search.Documents NuGet package:

Install-Package Azure.Search.Documents

Use the following code

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Azure;
using Azure.Search.Documents;
using Azure.Search.Documents.Indexes;
using Azure.Search.Documents.Indexes.Models;
using Azure.Search.Documents.Models;
using Newtonsoft.Json;

namespace AzureCognitiveSearchExample
{
    class Program
    {
        // Replace with your connection string
        private static string SqlConnectionString = "your_sql_connection_string";
        private static string SearchServiceName = "your_search_service_name";
        private static string SearchServiceApiKey = "your_search_service_api_key";
        private static string IndexName = "your_index_name";

        static void Main(string[] args)
        {
            // 1. Read data from the SQL table
            var sqlData = ReadSqlData();

            // 2. Format the data as JSON documents
            var jsonDocuments = ConvertToSearchDocuments(sqlData);

            // 3. Add the JSON documents to the Azure Cognitive Search index
            IndexDocuments(jsonDocuments);
        }

        static DataTable ReadSqlData()
        {
            DataTable dt = new DataTable();
            using (SqlConnection connection = new SqlConnection(SqlConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand("SELECT * FROM your_table", connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        dt.Load(reader);
                    }
                }
            }
            return dt;
        }

        static List<SearchDocument> ConvertToSearchDocuments(DataTable dataTable)
        {
            List<SearchDocument> documents = new List<SearchDocument>();
            foreach (DataRow row in dataTable.Rows)
            {
                SearchDocument doc = new SearchDocument();
                foreach (DataColumn column in dataTable.Columns)
                {
                    doc[column.ColumnName] = row[column.ColumnName];
                }
                documents.Add(doc);
            }
            return documents;
        }

        static void IndexDocuments(List<SearchDocument> documents)
        {
            Uri serviceEndpoint = new Uri($"https://{SearchServiceName}.search.windows.net/");
            AzureKeyCredential credential = new AzureKeyCredential(SearchServiceApiKey);
            SearchClient searchClient = new SearchClient(serviceEndpoint, IndexName, credential);

            try
            {
                IndexDocumentsBatch<SearchDocument> batch = IndexDocumentsBatch.Create();
                foreach (var document in documents)
                {
                    batch.Actions.Add(IndexDocumentsAction.Upload(document));
                }
                searchClient.IndexDocuments(batch);
                Console.WriteLine("Indexed documents successfully!");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error indexing documents: " + ex.Message);
            }
        }
    }
}

assuming that you have already created the index in Azure Cognitive Search with the same schema as your SQL table

Answer 2

Hi,

I did not try it but it seems like the following tutorial is exactly what you ask for. please check if this solve your needs:

https://learn.microsoft.com/en-us/azure/search/search-howto-connecting-azure-sql-database-to-azure-search-using-indexers?WT.mc_id=DP-MVP-5001699