ASP.NET Core : how to use existing SQL Server db in Visual Studio Code & CLI?

Question

I am new to asp.net core and I followed this tutorial to build a new mvc webapp, but it uses a new SQLite database.

I found that a lot of tutorials are using SQL Server with visual studio. How can I use a existing database to do the scaffolding in Visual Studio Code? or How can I do the exact same thing like the tutorial?

I now have a completely new mvc webapp and a database like this:

Thank you for your help!

One More Question, please:

According to this page.

It said The Movie class contains an Id field, which is required by the database for the primary key.

However, I don't have a integer ID field in my database, which result in an error primary key not found when I try to use aspnet-codegenerator to scaffold a controller. Is there any solution? Thank you!

Answer 1

How can I use a existing database to do the scaffolding in Visual Studio Code

If you want to reuse an existing database, you can do it as below:

1. Make sure you've added these required tools/packages.

   • the dotnet-ef tool: dotnet tool install --global dotnet-ef
   • other related packages:
     • Microsoft.EntityFrameworkCore.Design
     • Microsoft.EntityFrameworkCore.SqlServer
     • Microsoft.VisualStudio.Web.CodeGeneration.Design

   If you're using 3.1, you can install these package by cli.

   press key Ctrl+` within VSCode to open a terminal:

   > cd <the-folder-of-your-proj>
   > dotnet add package Microsoft.EntityFrameworkCore.Design --version 3.1.*
   > dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 3.1.*
   > dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design --version 3.1.*
   

2. and use the dotnet ef dbcontext scaffold command to scaffold from an existing database:

   dotnet ef dbcontext scaffold -c MyDbContext "your-connection-string" Microsoft.EntityFrameworkCore.SqlServer -o Models
   

   and then a MyDbContext file and some entities files are generated under the Models/ folder.

3. Move the ConnectionStrings from source code to configuration files.

   3.1 Clear the method of OnConfiguring() in MyDbContext:

   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
   {
       if (!optionsBuilder.IsConfigured)
       {
       #warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
           optionsBuilder.UseSqlServer("your-connection-string");
       }
   }
   

   3.2 Add your connection string to appsettings.Development.json. For example:

   {
     "Logging": {
       "LogLevel": {
         "Default": "Information",
         "Microsoft": "Warning",
         "Microsoft.Hosting.Lifetime": "Information"
       }
     },
     "ConnectionStrings": {
       "MyDbContext": "Server=(localdb)\\mssqllocaldb;Database=MyApp;Trusted_Connection=True;MultipleActiveResultSets=true"
     }
   }
   

   (Note here we use double slash (\\) to represent that the \ is escaped.This is different from the powershell command with 1st step.)

4. Finally, add a DbContext Service in your Startup::ConfigureServices():

   services.AddDbContext<MyDbContext>(opts=>{
       opts.UseSqlServer(Configuration.GetConnectionString("MyDbContext"));
   });
   

---

or How can I do the exact same thing like the tutorial?

If you're trying to create a brand new project within VSCode, invoke dotnet new mvc to generate an app. Don't forget to add the tools/packages that we talked above. And then invoke dotnet aspnet-codegenerator to generate controller/views/pages as below:

dotnet aspnet-codegenerator controller -m $model -dc $dcClass -name $controllerName -namespace $controllerNamespace -outDir Controllers --useDefaultLayout

Here the

• $model is your model name
• $dcClass is your DbContext class name
• $controllerName is your controller name
• $controllerNamespace is the namespace

This command will also add a new DbContext if the $dcClass doesn't exist. Feel free to update the connectionstrings in application.json.(In this case, don't forget to invoke dotnet ef migrations add initial && dotnet ef database update to sync the db before you run the MVC app)

This command is tedious. So I wrote a powershell command to simplify my work:

param (
    [Switch]$api = $false ,
    [string]$model = $(Read-Host "Model Name(without namespace)")
)

if( [string]::IsNullOrEmpty($model) ) {
    Write-Host "you need specify the model name (without a namespace)"
    exit
}

function Prompt-Parameter{
    param (
        [Parameter(Mandatory=$true)] [string] $message,
        $defaultValue
    )

    $result = Read-Host "Press enter $message (default value ['$defaultValue'])" 
    if([String]::IsNullOrEmpty($result) )
    {
        $result = $defaultValue
    }

    return $result
}

Write-Host "[+]set class name:"
$controllerName= $(Prompt-Parameter "Controller Name(without namespace)" -defaultValue "$($model)Controller")
$dcName= $(Prompt-Parameter "DbContext Name (without namespace)" -defaultValue "AppDbContext")

Write-Host "[+]set namespace:"
$rootNamespace = $( Prompt-Parameter -message "ROOT namespace" -defaultValue "App")
$controllerNamespace= $(Prompt-Parameter "namespace of Controller" -defaultValue "$rootNamespace.Controllers")
$modelNamespace = $(Prompt-Parameter "namespace of Model" -defaultValue "$rootNamespace.Models")
$dcNamespace= $(Prompt-Parameter "namespace of DbContext" -defaultValue "$rootNamespace.Data")

$modelClass = "$modelNamespace.$model"
$controllerClass = "$controllerNamespace.$controllerName"
$dcClass = "$dcNamespace.$dcName"

Write-Host $rootNameSpace
Write-Host $modelClass
Write-Host $controllerClass
Write-Host $dcClass

# whether to generate a controller with views or an ApiController
$apiSwitch = if($api) {"-api"} else {""}

dotnet aspnet-codegenerator controller $apiSwitch -m $model -dc $dcClass -name $controllerName -namespace $controllerNamespace -outDir Controllers --useDefaultLayout

With this dotnet-controller-generate.ps1, I can generate the Controllers/Views for a model by :

dotnet-controller-generate.ps1 TheModelName

Generate WebAPI for a model by:

dotnet-controller-generate.ps1 -api TheModelName