In this world of EFCore and migrations support that we have now, it’s hard to remember that at one time the primary means of performing SQL server database builds and changes was to use an “SQL Server” project type in visual studio.
In fact many newer devs who are only used to dotnet core might never have even seen the project type that I’m on about
The thing is though, SQL server projects are actually still quite handy for some scenarios, even if they have largely fallen out of favor these days.
Take for example, the project I’m currently working on.
Watch out for the legacy database!
I’m currently re-writing an old Dot Net Nuke v4 application, from round about 2008 on-wards.
As part of the system, there is a huge SQL server database, that not only has the Dot Net Nuke tables and data in them, but also shares server space with databases from Great Plains and Microsoft Dynamics.
On top of that, there’s a huge number of custom tables, that where not originally part of the Dot Net Nuke install, but where added by the original 3rd party company to DNN’s database to support the extra functionality they added to the system.
Over the year’s lots of stored procedures, functions, views and many other “Custom” assets have been added to the database, making it way to much of a huge undertaking to model all of this stuff using plain old EFCore.
There are many places where stored procedures can be simplified, and where it’s advantageous to write new procs, allowing access across all of the databases in the cluster, from just one EF data context, and so to preserve the existing system (This has to remain running while I work) it was decided that all the SQL stuff that’s being remodeled should be placed in it’s own schema, and be written directly in SQL, rather than using C# and migrations.
This also gives me the opportunity to remodel the data going into the the parts of the new application that I write, without upsetting the existing data shape, letting me experiment but without having to make any changes to the production data shape.
SQL Server Projects are Really useful.
If like me, your more comfortable in C# than in SQL then using an SQL Server project provides many benefits.
All your operations, and the objects you might want to add to a database are all ready templated for you, so in many cases you just have to right click on the project and add the exact “Thing” that you need to add, most of which will be pre-filled in for you
If you have little to no experience with SQL code, then using this type of project in your solution, is actually a great time saver.
Beware the devil
Now that I have you sold on the merits of using an SQL Server project as part of your build, I’m going to tell you that, to get these benefits you also have to go through some pain.
Not much though, because I’ve done the hard work of figuring this out over the last weekend for you dear reader
When you first add the project to your dotnet solution, everything will seem ok, you likely won’t see any major problems, and everything should just build.
If like me however, once you check your code in, you have a CI service that builds and tests your code, you may suddenly find that you have a failed build, and the culprit will be the SQL Server project you just added
You’ll find that if you drop down to a cmd prompt on the machine your using, outside Visual Studio and “type “dotnet build” at the point where your solution file is, that you’ll see the same error messages, even on the machine where everything builds perfectly fine in Visual Studio.
The cause is simply to do with framework version mismatches. The actual tooling (IE: SqlCmd and other command line tools) have been cross platform for years, so the issue is not one of trying to run a windows exe on a Linux system (My build system is Jenkins CI running under Linux, I’m currently doing a 4 part series on how that’s set up on the ‘Hainton Recruitment Blog’)
Searching the issue online didn’t turn up a lot of help either, there’s mention of installing the SSDT tools, of which their doesn’t seem to be a non windows version, and a lot of pointers to the SSDT team blog, most of which is sadly no longer being kept up to date according to the page banner.
The Community to the Rescue
After much searching and reading, I finally stumble across this project on GitHub
Where I find, among other people I find “Erik Ejlskov Jensen”, long time Microsoft MVP and SQL data guru, who if my memory serves me correctly was also the driving force behind the rather excellent SQL Server Compact and SQLite tooling add-on’s for visual studio.
After a bit of fiddling, and obviously not understanding the documentation correctly, I posted this issue asking for help
Side Note: Even after 40 years in this game, I know when I need to ask for help, something many new-comers these days won’t do, or admit to doing, one of the biggest revelations you’ll get in your career is that of learning to know what you don’t know.
I’ll be the first to admit, it took me a few tries, to get things right, and I screwed it up quite a few times in the process (Having things checked into a git svn is such a lifesaver…) but eventually I cracked it with the help of Erik’s blog post on the subject:
Now I have a solution, that will build in Visual Studio, allows me to use all the templates and visual studio features such as intellisense, and doesn’t blow up on me when the build is executed on the build server outside of visual studio.
How do I make it work?
Quite easily, as you can see in my solution, I have my SQL server project, and along side that project I have a .NET standard class library called <database name>.build
Once you’ve added the new project to your solution, remove all code files from it, such as the default class1.cs file that gets generated, even though this is a class library project, you won’t ever be using it to build any code, only the files linked to it that are present in your actual SQL server database project (Which as you can see in the image above show up as linked files in my solution)
With the class library project added, right click on it’s project name (in my case the st_database.build header) and “Unload” the project.
Then right click on the unloaded project file, and “Edit Project File” to open the project file contents in the visual studio editor.
Replace the entire file with the following contents, paying attention to making sure the path specified in the include line in the item group, points to the actual SQL server project in your solution.
<Project Sdk="MSBuild.Sdk.SqlProj/1.1.0"> <PropertyGroup> <TargetFramework>netstandard2.0</TargetFramework> </PropertyGroup> <ItemGroup> <Content Include="..\st_database\**\*.sql" /> </ItemGroup> </Project>
Which again in my case is “st_database”.
Once you’ve made those changes to the “build” project file, you need to do one last thing to the solution as a whole, and that’s turn off building the actual SQL server project.
Right click on the solution node, right at the top of your solution explorer, and click on properties.
In the properties dialog, make sure you select the configuration properties (Red Arrow), select “All Configurations” (Green Arrow) then uncheck “Build” (Blue ring) for both the release and debug versions of the SQL server project.
Ok, out of everything, make sure everything is saved, and you should now find that you can edit your SQL files inside visual studio 2019, and use all of it’s helpful features, and that your build system should now build and produce a dacpac file of your SQL files, ready for you to either auto deploy using your CI system, or pass to your DBA for execution on your target SQL server instance.
Having a dacpac also means that you can use useful tools such as SQL schema compare to understand the changes being made to your existing data base, should you need to keep a record.
The .NET Community is Awesome
If it wasn’t for the .NET developer community, and folks like ErikEJ I would never have solved this problem, well I wouldn’t have solved it in this manner anyway, I would probably have ended up creating my own tool or script, to make some magic happen when things where built in JenkinsCI.
Jonathan Mezach, one of the responder’s on the issue I raised, has recorded an episode to go online at “DotNetFlix” A .NET focused video show, which I didn’t even know existed until a few hours ago when I read his post, so I’m looking forward to watching that episode, and the many others that I’ve missed on that channel, over the years.