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.
- Latest .NET SDK
- Elevated previlages (
sapermission) on the database server to execute deployment
Create a simple class library
.csprojproject 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
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
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=truecommand will produce the
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
Samplecreated with the table
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
- 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!
comments powered by Disqus