Author : HASSAN MD TAREQ

What is LocalDB

  • LocalDB is a lightweight version of the SQL Server Express Database Engine that starts on demand and runs in user mode
  • LocalDB runs in a special execution mode of SQL Server Express that enables you to work with databases as .mdf files
  • typically, LocalDB database files are kept in the App_Data folder of a web project
  • LocalDB is installed by default with Visual Studio
  • (localdb)\\mssqllocaldb is the name of LocalDB instance

Connection String for LocalDB

connect to the automatic instance owned by the current user (in visual studio) by using the connection string
appsettings.json

{
  "ConnectionStrings": {
    "FooDB": "Server=(localdb)\\mssqllocaldb;Database=FooDB;Trusted_Connection=True;"
  },
}

Generate .mdf file

Code First

  • create entities & DbContext
  • add DbContext in Startup.cs
  • set connection string in appsettings.json

Open Package Manager Console (View > Other Windows > Package Manager Console)

Add-Migration <migrationName>

Update-Database

If everything goes well, FooDB.mdf will be created in you user folder (Users/foouser)

Database First

  • use SQL Server Management Studio to create database (insert data if you want)
  • right click on database > Detach
  • find .mdf file : Program Files > Microsoft SQL Server > MSSQL14.SQLEXPRESS > MSSQL > DATA

Using .mdf file

Create AppData folder in Project Folder and put .mdf in AppData

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=EmployeeDB;AttachDbFileName=%CONTENTROOTPATH%\\AppData\\EmployeeDB.mdf;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

See: using-dbcontext-with-dependency-injection