Implementing Azure Search with SQL Server and ASP.NET MVC

In this series I'll cover Azure Search, SQL Server and putting it all together in a ASP.NET MVC web app. The complete list can be found below:

Last week we've learned that Azure Search is a search-as-a-service that connects to a variety of data sources such as SQL Server. We've created our SQL Server DB, and stood up Azure Search and even query the index through the Azure Portal. In this final section, we'll work with C# and query the index.

Open the Azure Portal, and search for Search Services and click on the Search Services that we created earlier and look for Keys. Copy and paste the key b/c we'll be using it shortly.

You'll also want to remember the name of your search service. In my case it is - mcadventureworks

Once that is complete, head into Visual Studio and create a Console Application. Use NuGet to pull in references to Microsoft.Azure.Search as shown below.

Add the following code to Program.cs to search the index:

static void Main(string[] args)
{

    var searchServiceName = "yoursearchservice";
    var apiKey = "yourapikey";

    var searchClient = new SearchServiceClient(searchServiceName, new SearchCredentials(apiKey));
    var indexClient = searchClient.Indexes.GetClient("azuresql-index"); //check this to match your index

    DocumentSearchResult<MySQLDB> results;

    Console.WriteLine("Search the entire index for the term 'Michael' \n");

    results = indexClient.Documents.Search<MySQLDB>("Michael");

    WriteDocuments(results);

    Console.Read();
}

private static void WriteDocuments(DocumentSearchResult<MySQLDB> searchResults)
{
    foreach (SearchResult<MySQLDB> result in searchResults.Results)
    {
        Console.WriteLine(result.Document.FirstName + " " + result.Document.LastName);
    }

    Console.WriteLine();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

Create a class named MySQLDB and add the following:


[SerializePropertyNamesAsCamelCase]
class MySQLDB
{
    [IsFilterable, IsSortable, IsFacetable]
    public string CustomerID { get; set; }
    [IsFilterable, IsSortable, IsFacetable]
    public string FirstName { get; set; }
    [IsFilterable, IsSortable, IsFacetable]
    public string LastName { get; set; }
    [IsFilterable, IsSortable, IsFacetable]
    public string EmailAddress { get; set; }
    [IsFilterable, IsSortable, IsFacetable]
    public string ModifiedDate { get; set; }
    
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

When you run the app, it will search the entire index for the term 'Michael' and display the results in your Console window. If you've followed the tutorial so far, then you should get around 17 results.

Search the entire index for the term 'Michael'

Michael Blythe
Michael Blythe
Michael Bohling
Michael Vanderhyde
Michael Vanderhyde
Michael Galos
Michael Galos
Michael Brundage
Michael Brundage
Michael Graff
Michael Graff
Michael Sullivan
Michael Sullivan
Michael Lee
Michael Lee
Michael John Troyer
Michael John Troyer
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19