SQL Project for dotnet core (.NET 6.0/7.0/8.0)

0

Hi AWS, I am creating a SQL Database Project. I tried creating it with both Azure Data Studio and Visual Studio and I figured out that by default it is creating the project in .NET MVC Framework (4.7/4.8). Below is the screenshot attached.SQL Database Projects.

The content for .sqlproj file is:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <Name>sql_cicd</Name>
    <SchemaVersion>2.0</SchemaVersion>
    <ProjectVersion>4.1</ProjectVersion>
    <ProjectGuid>{c415a16b-af8c-4c65-9d78-5ebf51c50e3d}</ProjectGuid>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <OutputType>Database</OutputType>
    <RootPath>
    </RootPath>
    <RootNamespace>sql_cicd</RootNamespace>
    <AssemblyName>sql_cicd</AssemblyName>
    <ModelCollation>1033, CI</ModelCollation>
    <DefaultFileStructure>BySchemaAndSchemaType</DefaultFileStructure>
    <DeployToDatabase>True</DeployToDatabase>
  ** <TargetFrameworkVersion>v4.7.2</TargetFrameworkVersion>**
    <TargetLanguage>CS</TargetLanguage>
    <AppDesignerFolder>Properties</AppDesignerFolder>
    <SqlServerVerification>False</SqlServerVerification>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseSet>True</TargetDatabaseSet>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
    <OutputPath>bin\Release\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>False</TreatWarningsAsErrors>
    <DebugType>pdbonly</DebugType>
    <Optimize>true</Optimize>
    <DefineDebug>false</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <OutputPath>bin\Debug\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>false</TreatWarningsAsErrors>
    <DebugSymbols>true</DebugSymbols>
    <DebugType>full</DebugType>
    <Optimize>false</Optimize>
    <DefineDebug>true</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup>
    <VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">11.0</VisualStudioVersion>
    <!-- Default to the v11.0 targets path if the targets file for the current VS version is not found -->
    <SSDTExists Condition="Exists('$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets')">True</SSDTExists>
    <VisualStudioVersion Condition="'$(SSDTExists)' == ''">11.0</VisualStudioVersion>
  </PropertyGroup>
  <Import Condition="'$(NetCoreBuild)' != 'true' AND '$(SQLDBExtensionsRefPath)' != ''" Project="$(SQLDBExtensionsRefPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
  <Import Condition="'$(NetCoreBuild)' != 'true' AND '$(SQLDBExtensionsRefPath)' == ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
  <ItemGroup>
    <Folder Include="Properties" />
  </ItemGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.NETFramework.ReferenceAssemblies" Condition="'$(NetCoreBuild)' == 'true'">
      <Version>1.0.0</Version>
      <PrivateAssets>All</PrivateAssets>
    </PackageReference>
  </ItemGroup>
  <Import Project="$(NETCoreTargetsPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" Condition="'$(NetCoreBuild)' == 'true'" />
  <Target Name="BeforeBuild">
    <Delete Files="$(BaseIntermediateOutputPath)\project.assets.json" />
  </Target>
</Project>

I have a couple of questions around this:

  1. What is the difference between dotnet publish and sqlpackage publish.
  2. How to transform this .NET MVC project to .NET core SQL Database project.
  3. When I am running the dotnet publish command dotnet publish /t:publish /p:SqlPublishProfilePath="C:\database-projects\sql-server-deployment\sql-server-deployment-profile.publish.xml", I am getting the error below:

MSBuild version 17.8.3+195e7f5a3 for .NET Determining projects to restore... All projects are up-to-date for restore. C:\database-projects\sql-server-deployment\bin\Debug\sql-server-deployment.publish.sql : Deploy error : The connection st ring is not valid [C:\database-projects\sql-server-deployment\sql-server-deployment.sqlproj]

Please help.

profile picture
asked 3 months ago212 views
1 Answer
0

sqlpublish will handling SQL Server database deployments. It's a command-line utility that automates the process of deploying a SQL Server database by working with DACPAC and BACPAC files

dotnet publish is used for compiling .NET code, including dependencies, into a set of files that can be deployed to a hosting system

Solution for the problem :Migrate your MVC project to .NET Core: Update the project file, refactor code for .NET Core compatibility, update dependencies, etc. For the database, you might need to handle it separately if it's a SQL Server database. You can use Entity Framework Core for database operations.

profile picture
Jagan
answered 2 months ago
profile picture
EXPERT
reviewed 15 days ago
  • Thanks for answering, one last question how to add connectionString in .sqlproj file?

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions