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
orsa
permission) on the database server to execute deployment
Steps
Create a simple class library
.csproj
project by running commanddotnet 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 oneFirst, 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 commanddotnet build /p:NetCoreBuild=true
command will produce the.dacpac
file calledSample.DB.dacpac
underbin
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 tableTable1
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!
comments powered by Disqus