Using a Full Framework SQL Server Project in a .NET core project build.

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

https://github.com/rr-wfm/MSBuild.Sdk.SqlProj

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

https://github.com/rr-wfm/MSBuild.Sdk.SqlProj/issues/41

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:

https://erikej.github.io/efcore/2020/05/11/ssdt-dacpac-netcore.html

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.

A Little bit of Electronics Madness……

There’s a reason many of the members of my family call me a “Mad Professor”, ever since I was little I’ve had a habit of doing extreme experiments with electronics, and trying to use things in a way they where never really intended to be used.

On top of that, many folks that know me and/or follow my socials, know me better for my software exploits mostly using Microsoft’s .NET framework.

What many don’t know is that my degree is not actually in software, well not entirely anyway,

Using Dotnet Core Logging without using the DI container

Just before Christmas, one of our regular contributors to Lidnug posed the question on how to use the ILogger interface in dotnet core 3+ without injecting logger instances into classes used within a dotnet core application.

Making Blazor validation play nice with Bootstrap 4

There’s no doubt about it, blazor’s forms module and it’s validation features are fantastic.

You get all the benefits of using validation attributes on your models, along with a very fluid and real time UI model that works exactly the same in server and client modes.

There is however, one small problem with it all “Class Names”

Rediscovering Postgres and EF core

Its ok folks, you can relax, yes I’m not ill, yes I have gotten around to writing another blog post, yes I know…. I don’t write enough anymore and I should 🙂

With that out the way, why am I suddenly getting all excited about Postgres and EF again (specifically EF core)

Pure HTML Validation in Blazor

There’s been a lot of talk about Validation in the Blazor gitter chat recently.

The Blazor dev team have added some validation routines in that closley mimic the way validation works in ASP.NET MVC and many folks have been playing with them to see what they can do with them.

Chris Sainty has even produced a blog post [https://chrissainty.com/using-fluentvalidation-for-forms-validation-in-razor-components/] showing how to wire in the “Fluent Validation” libraries to make form validation even more awesome.

Bootstrap 4 Succinctly Released

The 8th book Iv’e written to date for Syncfusion’s ever popular Succinctly eBook series, and the third one one Bootstrap.

As Bootstrap continually gets better and more feature rich, then there is a need to keep up to date with the latest offerings it provides.

In this book, I take you on a just over 100 page tour of what’s new, and what it can now do (Hint: Flexbox rules!!!) and show you how to produce some interesting layouts.

Best part about it all is it’s free!!!

You can grab a copy here: Succinctly eBooks

 

Typescript for the C# developer

Over the past couple of years, my most popular talk that Iv’e taken around user groups has been the one where I describe what Typescript is, and how it relates to the backend C# developer.

Iv’e found that many back-end devs who would like to jump into client side development, are often put off from doing so simply beacuse of the percieved mess that the JavaScript eco system is in at present, and let’s be fair it’s not a compleatly unfounded reason either, beacuse JavaScript is bleeding at the edges in a great many places.

A Twitter Scam

Just a quick post folks to warn you about a scam currently doing the rounds on Twitter.

A user by the name of “Diane Shannon” will attempt to contact you via twitter direct messages (There are likely to be other names too, and this one will most likely stop getting used once this blog gets out).

Build Automation for Dotnet Core Apps

In a previous blog post I documented how I built a “Build Server” to deploy .NET 4.6+ apps on windows 2012 server.

While this worked, and was a reasonably good way to do it, It wasn’t without it’s problems. During it’s use for example I frequently had timing problems, where just one little change to some JS code would cause NPM to overrun a time out by half a second, or where an SSH connection timed out just slightly before the build server completed it’s login, and on top of all that, it regularly used to take about 15 minutes to build and deploy the project it was being used for.