Azure AI services - AI Search - Search service - Indexing Azure SQL Data using .NET SDK and Azure AI Search

Azure AI services - AI Search - Search service - Indexing Azure SQL Data using .NET SDK and Azure AI Search:

using Azure;
using Azure.Search.Documents.Indexes;
using Azure.Search.Documents.Indexes.Models;
using System.Spatial;
using System.Text.Json.Serialization;
 
public class Program
{
    public static async Task Main(string[] args)
    {
        var SearchServiceEndPoint = "https://searchservice-sree.search.windows.net";
        var SearchServiceAdminApiKey = "7kezUod5F4Yju3lEavWvxRgfEBimt6c";
        var AzureSQLConnectionString = "Server=tcp:sreesqlserver1.database.windows.net,1433;Initial Catalog=sreemysql1;Persist Security Info=False;" +
            "User ID=user1;Password=user@123;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
        SearchIndexClient indexClient = new SearchIndexClient(new Uri(SearchServiceEndPoint), new AzureKeyCredential(SearchServiceAdminApiKey));
        SearchIndexerClient indexerClient = new SearchIndexerClient(new Uri(SearchServiceEndPoint), new AzureKeyCredential(AzureSQLConnectionString));
        Console.WriteLine("Creating index...");
        FieldBuilder fieldBuilder = new FieldBuilder();
        var searchFields = fieldBuilder.Build(typeof(Hotel));
        var searchIndex = new SearchIndex("hotels-sql-idx", searchFields);
        CleanupSearchIndexClientResources(indexClient, searchIndex);
        indexClient.CreateOrUpdateIndex(searchIndex);
        Console.WriteLine("Creating data source...");
        var dataSource = new SearchIndexerDataSourceConnection(
                "hotels-sql-ds", SearchIndexerDataSourceType.AzureSql,
                AzureSQLConnectionString, new SearchIndexerDataContainer("hotels"));
 
        indexerClient.CreateOrUpdateDataSourceConnection(dataSource);
        Console.WriteLine("Creating Azure SQL indexer...");
        var schedule = new IndexingSchedule(TimeSpan.FromDays(1))
        {
            StartTime = DateTimeOffset.Now
        };
        var parameters = new IndexingParameters()
        {
            BatchSize = 100,
            MaxFailedItems = 0,
            MaxFailedItemsPerBatch = 0
        };
        var indexer = new SearchIndexer("hotels-sql-idxr", dataSource.Name, searchIndex.Name)
        {
            Description = "Data indexer",
            Schedule = schedule,
            Parameters = parameters,
            FieldMappings =
                {
                    new FieldMapping("_id") {TargetFieldName = "HotelId"},
                    new FieldMapping("Amenities") {TargetFieldName = "Tags"}
                }
        };
        CleanupSearchIndexerClientResources(indexerClient, indexer);
        await indexerClient.CreateOrUpdateIndexerAsync(indexer);
        Console.WriteLine("Running Azure SQL indexer...");
        try
        {
            await indexerClient.RunIndexerAsync(indexer.Name);
        }
        catch (RequestFailedException ex) when (ex.Status == 429)
        {
            Console.WriteLine("Failed to run indexer: {0}", ex.Message);
        }
        Console.WriteLine("Waiting for indexing...\n");
        System.Threading.Thread.Sleep(5000);
        CheckIndexerStatus(indexerClient, indexer);
        Console.WriteLine("Press any key to continue...");
        Console.ReadKey();
        Environment.Exit(0);
        Console.ReadKey();
    }
 
    private static void CleanupSearchIndexClientResources(SearchIndexClient indexClient, SearchIndex index)
    {
        try
        {
            if (indexClient.GetIndex(index.Name) != null)
            {
                indexClient.DeleteIndex(index.Name);
            }
        }
        catch (RequestFailedException e) when (e.Status == 404)
        {
            Console.WriteLine("If an index of the same name is detected, it's deleted now so that we can reuse the name.");
        }
    }
    private static void CheckIndexerStatus(SearchIndexerClient indexerClient, SearchIndexer indexer)
    {
        try
        {
            string indexerName = "hotels-sql-idxr";
            SearchIndexerStatus execInfo = indexerClient.GetIndexerStatus(indexerName);
 
            Console.WriteLine("Indexer has run {0} times.", execInfo.ExecutionHistory.Count);
            Console.WriteLine("Indexer Status: " + execInfo.Status.ToString());
 
            IndexerExecutionResult result = execInfo.LastResult;
 
            Console.WriteLine("Latest run");
            Console.WriteLine("Run Status: {0}", result.Status.ToString());
            Console.WriteLine("Total Documents: {0}, Failed: {1}", result.ItemCount, result.FailedItemCount);
 
            string errorMsg = (result.ErrorMessage == null) ? "none" : result.ErrorMessage;
            Console.WriteLine("ErrorMessage: {0}", errorMsg);
            Console.WriteLine(" Document Errors: {0}, Warnings: {1}\n", result.Errors.Count, result.Warnings.Count);
        }
        catch (RequestFailedException ex) when (ex.Status == 429)
        {
            Console.WriteLine("Failed to run indexer check: {0}", ex.Message);
        }
    }
    private static void CleanupSearchIndexerClientResources(SearchIndexerClient indexerClient, SearchIndexer indexer)
    {
        try
        {
            if (indexerClient.GetIndexer(indexer.Name) != null)
            {
                indexerClient.ResetIndexer(indexer.Name);
            }
        }
        catch (RequestFailedException ex) when (ex.Status == 404)
        {
            Console.WriteLine("The indexer doesn't exist.");
        }
    }
}
internal class Hotel
{
    [SimpleField(IsKey = true, IsFilterable = true)]
    [JsonPropertyName("hotelId")]
    public string HotelId { get; set; }
 
    [SimpleField(IsFilterable = true, IsSortable = true, IsFacetable = true)]
    [JsonPropertyName("baseRate")]
    public double? BaseRate { get; set; }
 
    [SearchableField]
    [JsonPropertyName("description")]
    public string Description { get; set; }
 
    [SearchableField(AnalyzerName = LexicalAnalyzerName.Values.FrLucene)]
    [JsonPropertyName("description_fr")]
    public string DescriptionFr { get; set; }
 
    [SearchableField(IsFilterable = true, IsSortable = true)]
    [JsonPropertyName("hotelName")]
    public string HotelName { get; set; }
 
    [SearchableField(IsFilterable = true, IsFacetable = true, IsSortable = true)]
    [JsonPropertyName("category")]
    public string Category { get; set; }
 
    [SearchableField(IsFilterable = true, IsFacetable = true)]
    [JsonPropertyName("tags")]
    public string[] Tags { get; set; }
 
    [SimpleField(IsFilterable = true, IsFacetable = true)]
    [JsonPropertyName("parkingIncluded")]
    public bool? ParkingIncluded { get; set; }
 
    [SimpleField(IsFilterable = true, IsFacetable = true)]
    [JsonPropertyName("smokingAllowed")]
    public bool? SmokingAllowed { get; set; }
 
    [SimpleField(IsFilterable = true, IsSortable = true, IsFacetable = true)]
    [JsonPropertyName("lastRenovationDate")]
    public DateTimeOffset? LastRenovationDate { get; set; }
 
    [SimpleField(IsFilterable = true, IsSortable = true, IsFacetable = true)]
    [JsonPropertyName("rating")]
    public int? Rating { get; set; }
 
    [SimpleField(IsFilterable = true, IsSortable = true)]
    [FieldBuilderIgnore]
    [JsonPropertyName("location")]
    public GeographyPoint Location { get; set; }
}
 
OutPut:

Source code:
git clone https://github.com/Azure-Samples/search-dotnet-getting-started.git