[Question] How can I load the modified project back into VS2019 for editing?

[shawty]

Like many people trying to build dotnet core projects on linux, that include SQL server projects, I ended up here after some internet searching and research.

Like others, I'm editing in Visual Studio, checking into GitSVN then having JenkinsCI build my project on a linux target.

Before finding this project, this was my issue

After following the instructions on your main page, and replacing my project file with that shown (The sql server project was created from the VS2019 template) I now find I can build the project from the command line using "dotnet build", but I can no longer load the project into Visual Studio 2019 for editing, when I try I just get the following:

How can I load the project into VS for editing, but still use your project to enable me to build my solution without the presence of the project breaking the build.

[ErikEJ]

Could you share your .csproj ?

[shawty]

Yea sure Erik, but there's nothing special in it, it's just the standard template one. (sqlproj) I think it is.

This ^^^^^ above, plus the attached ".sqlproj" file, is the original project files

st_database.sqlproj.zip

I then replaced the sqlproj file created by the template with

as suggested in this projects readme.

That got me to the point where I can do this

but where reloading it into VS19, gives me this

[shawty]

I confess I did spend an hour or two last night chopping and changing bits of the project file around, but didn't get anywhere.

[jmezach]

@shawty The project file must have the .csproj extension. It will not work with .sqlproj because then Visual Studio will interpret it as an old style project, but since you've changed the contents of the project file to use SDK-style projects you get the error. I don't think there's currently a way we can support the .sqlproj extension.

[ErikEJ]

Maybe the documentation could be slightly clearer on that?

[shawty]

@jmezach ah ok. So question? It's not 100% clear from the read me, but how would I get an SQL server style project into my .NET core solution?

From scratch?

Basically, I would like to use the features of VS to help me edit, design and build the new tables/sprocs/fuctions etc, and maybe even generate a DACPAC for them (Although this is not a major requirement).

Then when checking into git, I still want my CI/CD to build the solution (I don't care too much if the SQL server stuff is built or not) but I don't want the SQL project to break my CI Build, which is what it's doing at present with just the normal standard SQL Server project files.

I ended up here, after several searches as this being the solution to that problem :-)

Sorry I'm rambling a bit :-)

In a nutshell, I want to be able to use VS19's SQL editing features, and be able to check in the SQL code along side all the CS code, but still have my CI service build the solution and produce the output on the Linux build server it's using. Everything is Core based, and the build is performed using the standard "dotnet" cli tools.

[jmezach]

What I've heard people do is to have a "companion" .csproj next to the existing .sqlproj. I believe @ErikEJ has some experience with that as well. Basically you would create a new project (I believe it has to be in a separate folder as well) and then add the following to your project file:

<ItemGroup>
  <Content Include="../<sql-proj-folder>/**/*.sql" />
</ItemGroup>

You can then use the .csproj for CI and use the .sqlproj for editing.

[shawty]

@jmezach Thanks, I'll give that a try and let you know how I get on.

[shawty]

Maybe the documentation could be slightly clearer on that?

Absolutley Erik.

Everything I've read that lead me to this point, suggests that your project is the solution, for not only my case, but TFS based builds that break with the same tooling errors too.

The best way to test it, is to build a regular .NET core web-api service, then add a new project to that solution, and make the project type be an SQL one. Then use a linux machine set up with the standard .NET core tools, to perform a "dotnet build" from the cli, at solution level, so all the projects are built in one go.

You'll get something similar to my first image in the thread, and searching in those exceptions, will eventually lead you to here.

Some documentation changes, and/or possibly a page in the wiki telling people how to convert an SQL server project to build with the CLI tools in this case would be very helpful.

[ErikEJ]

I have a blog post here describing the "buddy" project scenario: https://erikej.github.io/efcore/2020/05/11/ssdt-dacpac-netcore.html

[ErikEJ]

Maybe a simple change to the docs:

Usage
The simplest usage is to create a new .csproj project file with the following contents:

[shawty]

Yep, that makes it clearer that you shouldn't replace the SQL Project.

I would also put the link to your blog post in there too.

At a minimum I would say

"The simplest usage is to create a new .csproj project file, alongside all the existing files, with the following contents:"

[jeffrosenberg]

Our usage may not be typical, but what we're doing on my team is committing only the .csproj file, but maintaining a script that can be used to generate the .sqlproj file on an adhoc basis. Because the .csproj file supports wildcards, it doesn't need to be changed with each commit like the .sqlproj file does. So most of the time, we rely on just the .csproj file and develop in a text editor. When we need to use SSDT, we generate a new .sqlproj file and open that in SSDT.

[shawty]

thanks @jeffrosenberg more fuel for the fire :-)

[shawty]

Still getting a bit stuck here.

Trying Erik's approach from his blog post, and dotnet core build is getting confused.

[shawty]

and if I try it at solution level:

[ErikEJ]

As @jmezach wrote, and as clearly stated in my blog post, do not have both the .sqlproj and .csproj in the same folder.

[shawty]

ah ok, I missed that bit... dunno why... ok, trying again.

[shawty]

Even with everything in 2 separate projects, the command line build still fails.

In VS19 all is ok :

But the command line has gone back to my very first error now, failing because it can't find the SSDT tools

@ErikEJ as far as I can see, I've done everything the same way as in your blog post

ServiceThreadWeb.zip

[ErikEJ]

You need to exclude the .sqlproj from your build configuration.

[shawty]

Annnnnndddddd.... W0000000 H00000000 :-)

On a hunch, I turned off building of actual db project solution wide, and now everything works perfectly.

🎉🎉🎉🎉

[shawty]

You need to exclude the .sqlproj from your build configuration.

Github didn't update the page until I posted my reply....

[shawty]

@ErikEJ if you don't object, I'm gonna blog this, and point folks both at your blog post, this project/issue and repeat the steps from your post with a bit extra detail.

Cheers
Shawty

[ErikEJ]

Excellent, the more noise the project makes, the better! (as long as you do not copy my blog post, of course)

[shawty]

LOL... I won't copy it, but I will follow the same process, just using my own words.

[ErikEJ]

I have updated my blog post.

[jmezach]

Thanks @ErikEJ for updating the docs. I think there isn't much more we can do here.

FYI: I've recorded a dotnetFlix episode today about this project demoing it which also goes into converting an existing project, etc. Should be coming online soon.

[ErikEJ]

Looking forward to watching!

[shawty]

@jmezach kewl, I'll have to watch that. I'll reference it from my blog post too, which I'll be writing later on this evening.

[shawty]

@jmezach by the way if your interested, I routinely do sessions for .NET channels and such like, so if you'd like me to do a video session on dotNetFlix, do feel free to reach out to me. I'm one of the management team behind "LiDNUG (The Linked .NET Users Group on Linked-in)" (Lidnug.org) if you want to get in touch.

[shawty]

@ErikEJ and others - blogged : https://shawtyds.wordpress.com/2020/08/26/using-a-full-framework-sql-server-project-in-a-net-core-project-build/