This is something very simple, but very useful. Something Microsoft should've put in Visual Studio in the first place long ago. When I develop data-aware applications I usually end up writing database scripts to make it easy to (re)deploy and version control my database model. I usually write several files just to make things controllable and manageable. But deploying them is a different task altogether.
And a tedious one as well. I have to fire up SQL Management Studio and then run all those scripts in correct order.
Since this is done rather frequently during application development it seems like a very good candidate for automation or simplification. So let's do just that.Managing database scriptsSome development teams split database scripts into several files to make it easier to version control and handle file merging.
They may put every DB object drop/creation into a separate file or they may split the whole script into functional parts like dropping, model creation etc. This greatly depends on several factors as well as on developer preference. I tend to use the latter approach, so I don't end up with a huge amount of files (which may become very unmanageable on their own) and each file has a manageable content length. I usually split my script into these files:. Drop Objects.sql.
Create Model.sql. User Functions.sql. Stored Procedures.sql. Static Data.sql. Test Data.sql - this one is only used during development time to provide enough data to develop againstThis is the starting list, that also needs to be executed in the same order as listed here. If there's a particular lookup table with lots of insert statements I may put those into a separate file so it makes Static/Test Data scripts more manageable.Automate/simplify executionRunning these scripts in correct orders is prone to human errors.
We may mess up the order of execution or forget to run (or even double run) a certain script. So let's make it possible to execute these scripts directly from within Visual Studio.
The first thing to do is to add a batch file (i.e. Deploy DB.bat) to the project that would probably look similar to this:@sqlcmd -S servername -d dbname -U username -P password -i 'Drop Objects.sql','Create Model.sql','User Functions.sql','Stored Procedures.sql','Static Data.sql','Test Data.sql'This script will deploy all scripts to our desired database in a single run in correct order and all of the scripts so none will be left out. Test Data.sql script has to be omitted in final deployment scenario. The @ sign at the beginning only suppresses the command itself to be displayed in the command window.Ok so we created a batch file that makes it simple to deploy all database scripts at once.
But this still means we have to fire up command window, navigate to our file and run it. So let's now make it possible to run these from within Visual Studio then. Follow this list of steps to accomplish just that:.
Open Windows explorer and navigate to%UserProfile%DocumentsVisual Studio 20xx. Add a new file here and name it ExecuteBatch.cmd. Edit this file and put these two lines in@cmd /c%1@pause. Go to Visual Studio and right click previously added batch file ( Deploy DB.bat) and select Open With. Context menu option.
This dialog will open where you have to click on the Add. Button. When you click the Add.
Tfs Batch Script Task
Button this next window will appear:. Browse for the ExecuteBatch.cmd that you've created previously and give it a nicer friendly name as shown above. Confirm by clicking the OK button. The dialog will now display our new new 'editor' which will execute batch files. If you want to, you can also set this one as the default 'editor' for batch files (since we started off by right clicking on a batch file).
That will mean that by double clicking on a batch file, it will be immediately executed. Confirm everything by clicking on the OK button.Voila! That's it really. Until you reinstall your machine, you'll be able to run batch files from within Visual Studio. Deploying database scripts will become bliss.
Azure Pipelines Batch Script
One thing less to worry about.