Creating a .NET SDK-Style Microsoft SQL Server Database Project

In this article, let’s see how we can create a dotnet SDK style database project .sqlproj for Microsoft Sql Server.

This helps us in creating a database project .sqlproj without depending on Visual Studio Database Tools, Visual Studio or even a Windows Based Operating System

In addition, we can also see how we can deploy the code into a targeted Microsoft Sql Server

Our high level goal here, is to have Microsoft Sql Server database As Code and deploy it seemlessly. Let’s get started on achieveing the same.

Getting Started

Prerequisites

• Latest .NET SDK
• Elevated previlages (dbowner or sa permission) on the database server to execute deployment

Steps

• Create a simple class library .csproj project by running command dotnet new classlib -n Sample.DB

• There are couple of manual steps required, to convert this to a database project .sqlproj, which we will see one by one

• First, rename the project file extension to .sqlproj. then, follow the instructions given here OR simply copy paste the below project file xml content..

    <Project DefaultTargets="Build" ToolsVersion="4.0">
      <Sdk Name="Microsoft.Build.Sql" Version="0.1.3-preview" />
      <PropertyGroup>
        <Name>Sample.DB</Name>
        <SchemaVersion>2.0</SchemaVersion>
        <ProjectVersion>4.1</ProjectVersion>
        <DSP>Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvider</DSP>
        <OutputType>Database</OutputType>
        <RootNamespace>Sample.DB</RootNamespace>
        <AssemblyName>Sample.DB</AssemblyName>
        <ModelCollation>1033, CI</ModelCollation>
        <DefaultFileStructure>BySchemaAndSchemaType</DefaultFileStructure>
        <SqlServerVerification>False</SqlServerVerification>
        <IncludeCompositeObjects>True</IncludeCompositeObjects>
        <TargetDatabaseSet>True</TargetDatabaseSet>
      </PropertyGroup>
  </Project>
  

  Note: Current version of SDK is in preview mode (at the time of writing this article)

• Delete any existing .cs files.

• At this point, we have a database project. So, we are good to create the database objects using Transact-SQL, where we can create a simple table by following the next steps.

• Create a sql file called Table1.sql as below.

  CREATE TABLE [dbo].[Table1]
  (
    [Id] INT NOT NULL,
    [Text] varchar(50) NOT NULL,
    [Description] varchar(500) NULL
  ) 
  

• There we go, we have the database project ready with a table named Table1. Compiling the project by running the command dotnet build /p:NetCoreBuild=true command will produce the .dacpac file called Sample.DB.dacpac under bin folder.

• We can deploy this database into a sql server by simply using my dacpac-utility OR using the sqlpackage utility.

  Note: We can control the database name using the connection string OR by using additional configuration in the project file

• After successful deployment, we should be able to see the database Sample created with the table Table1

Sample Project

A complete sample project can be found here, which includes..

• SDK Style database project with a sample database object (table)
• Compiling and publishing the project as .dacpac
• Creation of Microsoft Sql Server in docker
• Deployment the project (from published artifact .dacpac) into the Microsoft Sql Server

Refer to the repo readme for usage instructions.

If you are interested in learning How to write and execute an unit test case for a Microsoft Sql Server database object using Transact-SQL, refer my article here

Love helping the community!

• ← Previous Post
• Next Post →