Scripting database changes

On my team we use SQL Server Database Projects to version our databases. We previously used EF6 for our data layer, but recently started using EF Core which gave me the opportunity to optimize my workflow. Here are the steps we would manually take to modify the schema.

  1. Open the database project and alter the schema.
  2. Build the project.
  3. Publish the project to a local test database.
  4. Make the corresponding change in the EF Core model.

I decided to script it and add the script as a shortcut in Launchy. Here is my script.

"C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin\msbuild.exe" C:\code\prism\DB\PrismDB.sln /property:Configuration=Release

if %errorlevel% neq 0 exit /b %errorlevel%

"C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:"C:\path\to\sql\project\bin\Release\MyDatabase.dacpac" /TargetServerName:localhost /TargetDatabaseName:MyDatabase /p:AllowIncompatiblePlatform=True /p:BlockOnPossibleDataLoss=False /TargetTimeout:120 /p:ScriptDatabaseOptions=False

if %errorlevel% neq 0 exit /b %errorlevel%

pushd C:\path\to\sql\project

dotnet ef dbcontext scaffold "Server=localhost;Database=MyDatabase;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer --output-dir ../Data/Entities --context Entities --force --project Data.Dummy

if %errorlevel% neq 0 exit /b %errorlevel%

My workflow with the script is:

  1. Open the database project and alter the schema.
  2. Press Alt+Spacebar and type database to execute my script.
  3. Go back to coding in Visual Studio and everything is up to date.

To improve the error experience, you can create a second script that calls the above script with the /k switch to prevent the command shell from closing at the end.

cmd /k "Database Run All Inner.bat"

Leave a Reply

Your email address will not be published. Required fields are marked *