Visual Studio – Create SQL Database Project Template

A project template is a way to provide a reusable structure, that others can customize for their own purposes.

There are a couple of ways to create a template, but I’m going to create one by starting a new SQL Database project. I should also mention I will be using Visual Studio 2017 to handle all of this:

From here, you can create your first template by going to Project -> Export Template…

If you want to use images that will display in the New Project window, the icon size is 90×90 while the preview image is 200×200.

With the “Automatically import the template into Visual Studio” box checked, this will import the template and you should be able to use it. Open a new instance of Visual Studio to where you create a new SQL Server database project. You will now see your custom template:

If you create a new project with this template, you’ll see it will have nothing because your template project was empty. You can now go back and add database objects or folders, reexport and overwrite your files, and you will then see the changes reflected.

Exported Template Files

When you export the template, Visual Studio creates a zip file here: %USERPROFILE%\Documents\Visual Studio 2017\My Exported Templates\Custom Template Name.zip

If you unzip it, you’ll see the barebones of your project and a template file named MyTemplate.vstemplate. This file is where you can manually fix things that Visual Studio may not have done for you. This file will become important later when specifying if template parameters should be used in files. We’ll learn a little more on that later.

Import Template into Visual Studio

If you exported the template but did not have the “Automatically import the template into Visual Studio” box checked, you can still manually do it by copying over the zip file that is created during export to the template directory.

For Visual Studio 2017:

Copy from: %USERPROFILE%\Documents\Visual Studio 2017\My Exported Templates\Custom Template Name.zip

To: %USERPROFILE%\Documents\Visual Studio 2017\Templates\ProjectTemplates

Renaming Files, Variables, and Others with Project Name

With the use of template parameters, you can rename files, variables within files, and other objects such as the assembly name when creating a project. A simple reason to use template parameters is if you need to supply some value to rename objects or code to help save time. Say we want to include the project name in a publish file, we could use the $safeprojectname$ parameter. Most of these you can do right in Visual Studio, but there are others that you’ll have to edit in a text editor.

Here is a link to the MSDN page about template parameters and what you can use. For this tutorial, I’ll be utilizing the $safeprojectname$ parameter:

Here is a sample project that we will use to rename files, variables, and the assembly name:

File Names

For most files you add into the project, Visual Studio will not allow the use of the ‘$’ character. For example, adding a procedure named “sp$safeprojectname$Test” will be converted to “sp_safeprojectname_Test” like in the image above. The best way I could handle this was to create my files, rename them outside of Visual Studio, and edit the project’s sqlproj file. Let’s change the stored procedure and the publish file.

Rename the files outside of Visual Studio:

Update the file names in MyDatabaseTemplate.sqlproj in a text editor:

Save the file and reload in Visual Studio. Your file names should now be updated:

Variables in Files

Say we wanted to use parameters in files. We can do that by adding in the parameter within the file. For example, in the sp$safeprojectname$ procedure we created, the CREATE PROCEDURE statement also has the $safeprojectname$ parameter. To ensure this value will be replaced during project instantiation, you need to export the template and look in the MyTemplate.vstemplate file. In there, the ReplaceParameters must be set to true for that file:

Assembly Name

To replace the assembly name, you can do this in the Project Settings:

Visual Studio will not let you change the namespace because of the invalid characters. To do this, we’re going to have to edit the MyDatabaseTemplate.sqlproj file in a text editor. Next, replace the value in the <RootNameSpace> element with $safeprojectname$ and save it: