1 / 14

Visual Studio Database Projects

Visual Studio Database Projects. Erik van Ballegoij Arrow Digital Erik.vanballegoij@arrowdigital.com. Please support our valuable sponsors. Agenda. What is this about? Development Deployment Looking ahead. What is a Database Project.

brigitted
Download Presentation

Visual Studio Database Projects

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Visual Studio Database Projects Erik van Ballegoij Arrow Digital Erik.vanballegoij@arrowdigital.com

  2. Please support our valuable sponsors

  3. Agenda • What is this about? • Development • Deployment • Looking ahead

  4. What is a Database Project A database project is the offline representation of a database schema. This representation contains the object definitions and deployment scripts that you would need to create a separate instance of that database or to update an existing instance. By creating a database project, you can put your database under version control.

  5. What is a Database Project Core DNN Module Deploy multiple database projects to the same target database

  6. What is a Database Project Auth (Users, Roles, Permissions) DNN Feature (feature specific tables) Module Deploy multiple database projects to the same target database

  7. Development • Start from existing database or start from scratch • Full intellisense • Compilation support - build fails on errors • Full support for tools like RedGate

  8. Deployment • From Visual Studio • Using SQLPackage.exe (C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin) • Using DacServices (Microsoft.SqlServer.Dac namespace)

  9. Programmatic Deployment – DacServices.Deploy using (DacPackagedacpac = DacPackage.Load(FilePath)) { dacServices.Deploy( package: dacpac, targetDatabaseName: targetDatabaseName, upgradeExisting: true, options: dacDeployOptions); }

  10. Programmatic Deployment - DacDeployOptions DacDeployOptionsdacDeployOptions = new DacDeployOptions { CreateNewDatabase = false, ExcludeObjectTypes = new[] { ObjectType.Users, ObjectType.Permissions, ObjectType.StoredProcedures, ObjectType.RoleMembership, ObjectType.Aggregates, ObjectType.ExtendedProperties }, BlockOnPossibleDataLoss = true, IgnoreFileAndLogFilePath = true };

  11. Looking Ahead • Remove {ObjectQualifier} • DNN Database project(s) – use different schemas for different areas of application • Create separate database projects – each schema should have its own database project • Publish all database projects as separate NuGet packages (.dacpac files) • Add NuGet package support to database projects • Add deployment support in DNN Installer, using DacServices • Module developers reference dacpac files they need

  12. Looking Ahead • No more manual versioning of db scripts • (deployment tooling can generate diff scripts on the fly) • Better quality of db scripts – compile time reference and syntax checking • Easier for module developers to integrate with DNN database • Separation of concerns in Core database

  13. Further reading • https://docs.microsoft.com/en-us/visualstudio/data-tools/creating-and-managing-databases-and-data-tier-applications-in-visual-studio • https://msdn.microsoft.com/en-us/library/aa833292(v=vs.80).aspx • https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.dacservices(v=sql.120).aspx • https://blog.prowareness.com/2015/12/deploying-database-programmatically-using-data-tier-applications-dac-2/

More Related