Code First development with ASP.NET MVC

 In this tutorial, you will develop a data driven web application using ASP.NET MVC, SQL Server, and Entity Framework. We shall use Visual Studio Code for our editor. The data model will be based on a Team/Player relationship in sports. We will use SQL Server running in a Docker Container.

Source code: https://github.com/medhatelmasry/TeamPlayersMvc

Assumptions

It is assumed that you have the following installed on your computer:

• Visual Studio Code
• .NET 7.0 SDK
• Docker Desktop

Visual Studio Code Extension

Add this Visual Studio Code Extension if you do not have it already:

The Data Model

We will use the following class to represent a Team:

public class Team {

    [Key]

    public string? TeamName { get; set; }

    public string? City { get; set; }

    public string? Province { get; set; }

    public string? Country { get; set; }

    public List<Player>? Players { get; set; }

}

The primary key is the TeamName and a team has many players.

The following class represents a Player:

public class Player {

    public int PlayerId { get; set; }

    public string? FirstName { get; set; }

    public string? LastName { get; set; }

    public string?  Position { get; set; }

    public string? TeamName { get; set; }

    [ForeignKey("TeamName")]

    public Team? Team { get; set; }

}

The primary key is PlayerId and each player must belong to a team.

Getting started

In a working directory, run the following command in a terminal window to create a Razor Pages web application in a folder names TeamPlayers:

dotnet new mvc -f net7.0 -o TeamPlayersMvc

Change to the newly created folder with terminal command:

cd TeamPlayersMvc

For the application to work with SQL Server, we will need to add some packages by running the following commands:

dotnet add package Microsoft.EntityFrameworkCore

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore.Design

 

We will use code generation to scaffold MVC controllers & views. For that purpose, you will also need to add this package:

dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design

If you have not done so already, you will need to globally install the following tools for Entity Framework and Code Generation respectively:

dotnet tool install -g dotnet-aspnet-codegenerator

dotnet tool install -g dotnet-ef

NOTE: If these tools are already installed, run the above commands while replacing ‘install’ with ‘update’ to get the latest version of the tool.

Creating the model classes

Open your app in Visual Studio Code with this command:

code .

Add to the Models Teamclasses Team & Player mentioned under title “The Data Model” above.

The Context Class

We will need to create a database context class to work with relational databases using Entity Framework. To this end, create a Data folder. Inside the Data folder, create a class named ApplicationDbContext with the following code:

 

public class ApplicationDbContext : DbContext {

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)

        : base(options) {}

    public DbSet<Team>? Teams { get; set; }

    public DbSet<Player>? Players { get; set; }

}

Seeding the database with sample data

It is always useful to have some sample data to visualize what the app does. Therefore, we will create a class dedicated to seeding data. In the Data folder, create a static class named SeedData and add to it the following code that contains sample data for teams and players:

public static class SeedData {

    // this is an extension method to the ModelBuilder class

    public static void Seed(this ModelBuilder modelBuilder) {

        modelBuilder.Entity<Team>().HasData(

            GetTeams()

        );

        modelBuilder.Entity<Player>().HasData(

            GetPlayers()

        );

    }

    public static List<Team> GetTeams() {

        List<Team> teams = new List<Team>() {

            new Team() {    // 1

                TeamName="Canucks",

                City="Vancouver",

                Province="BC",

                Country="Canada",

            },

            new Team() {    //2

                TeamName="Sharks",

                City="San Jose",

                Province="CA",

                Country="USA",

            },

            new Team() {    // 3

                TeamName="Oilers",

                City="Edmonton",

                Province="AB",

                Country="Canada",

            },

            new Team() {    // 4

                TeamName="Flames",

                City="Calgary",

                Province="AB",

                Country="Canada",

            },

            new Team() {    // 5

                TeamName="Leafs",

                City="Toronto",

                Province="ON",

                Country="Canada",

            },

            new Team() {    // 6

                TeamName="Ducks",

                City="Anaheim",

                Province="CA",

                Country="USA",

            },

            new Team() {    // 7

                TeamName="Lightening",

                City="Tampa Bay",

                Province="FL",

                Country="USA",

            },

            new Team() {    // 8

                TeamName="Blackhawks",

                City="Chicago",

                Province="IL",

                Country="USA",

            },

        };

        return teams;

    }

    public static List<Player> GetPlayers() {

        List<Player> players = new List<Player>() {

            new Player {

                PlayerId = 1,

                FirstName = "Sven",

                LastName = "Baertschi",

                TeamName = "Canucks",

                Position = "Forward"

            },

            new Player {

                PlayerId = 2,

                FirstName = "Hendrik",

                LastName = "Sedin",

                TeamName = "Canucks",

                Position = "Left Wing"

            },

            new Player {

                PlayerId = 3,

                FirstName = "John",

                LastName = "Rooster",

                TeamName = "Flames",

                Position = "Right Wing"

            },

            new Player {

                PlayerId = 4,

                FirstName = "Bob",

                LastName = "Plumber",

                TeamName = "Oilers",

                Position = "Defense"

            },

        };

        return players;

    }

}

Note that the SeedData class is static because it contains an extension method named Seed() to ModelBuilder.

The Seed() method needs to be called from somewhere. The most appropriate place is the ApplicationDbContext class. Add the following OnModelCreating() method to the ApplicationDbContext class:

protected override void OnModelCreating(ModelBuilder builder)

{

    base.OnModelCreating(builder);

    builder.Entity<Player>().Property(m => m.TeamName).IsRequired();

    builder.Entity<Team>().Property(p => p.TeamName).HasMaxLength(30);

    builder.Entity<Team>().ToTable("Team");

    builder.Entity<Player>().ToTable("Player");

    builder.Seed();

} 

In addition to seeding data, the above code ensures the following:

• TeamName is required
• The maximum length of TeamName is 30 characters
• The names of the tables that get created in the database are Team & Player. Otherwise, the names get created as Teams & Players.

The database

You can use any SQL Server database you wish. In my case, so that this app works on Linux, Windows, Mac Intel, and Mac M1, I will run SQL Server in a Docker container. To run SQL Server in a Docker container, run the following command:

docker run --cap-add SYS_PTRACE -e ACCEPT_EULA=1 -e MSSQL_SA_PASSWORD=SqlPassword! -p 1444:1433 --name azsql -d mcr.microsoft.com/azure-sql-edge

The connection string to the database is setup in the appsettings.json (or appsetting.Development.json) file. Edit this file and make the following highlighted updates to it:

{

  "Logging": {

    "LogLevel": {

      "Default": "Information",

      "Microsoft.AspNetCore": "Warning"

    }

  },

  "AllowedHosts": "*",

  "ConnectionStrings": {

    "DefaultConnection": "Server=tcp:127.0.0.1,1444;Database=TeamPlayersDB;UID=sa;PWD=SqlPassword!;TrustServerCertificate=True;"

  }

}

 

To make our app work with SQL Server, you will need to add the following code to Program.cs just before “var app = builder.Build();”: 

string connStr = builder.Configuration.GetConnectionString("DefaultConnection")!;

builder.Services.AddDbContext<ApplicationDbContext>(

    options => options.UseSqlServer(connStr)

);

Migrations

We can now create a migration named m1 with:

dotnet ef migrations add M1 -o Data/Migrations

This creates a migrations file in Data/Migrations folder. To execute the migrations and create the database and seed data, run the following command:

dotnet ef database update

If all goes well and no errors are generated, we can assume that a database named TeamPlayersDB was created, and data is seeded into tables Team & Player.

NOTE: If you wish to drop the database for whatever reason, you can run command: dotnet ef database drop

 

This is what the tables in the database look like:

Scaffolding Teams & Players controller and views

To incorporate pages into our app that allow us to manage Team & Player data, we will scaffold the necessary controller & views using the aspnet-codegenerator utility. Run the following command from a terminal window in the root of the project to generate files pertaining to teams and players respectively:

dotnet aspnet-codegenerator controller -name TeamsController -outDir Controllers -m Team -dc ApplicationDbContext -udl -scripts

dotnet aspnet-codegenerator controller -name PlayersController -outDir Controllers -m Player -dc ApplicationDbContext -udl -scripts

This produces controllers in the Controllers folder and fiews files in folders Views/Teams & Views/Players respectively. To add menu items on the home page that point to Team & Player pages, edit Views/Shared/_Layout.cshtml and add the following HTML to the <ul> block around line 28:

<li class="nav-item">

    <a class="nav-link text-dark" asp-area="" asp-controller="Teams" asp-action="Index">Teams</a>

</li>

<li class="nav-item">

    <a class="nav-link text-dark" asp-area="" asp-controller="Players" asp-action="Index">Players</a>

</li>

The final product

Run the web app and notice the main menu:

Click on Teams:

Click on Players:

Conclusion

You just learned how to use the code-first database approach with ASP.NET MVC. The same priciples work with ASP.NET Razor Pages. 

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.