Auto Add Sql Files to Database Project

Date posted: Post reading time: 4 minutes

When working on large SQL Database Projects in Visual Studio files and folders can get accidently dropped or duplicated during git merges etc. Here is a way to automatically add all sql files in the project folder to the Sql Database Project so that the repository is kept clean.

Within your .sqlproj file add a label of “Folders” to the <ItemGroup> containing all the <Folder ... /> entries and a label of “SqlFiles” to the <ItemGroup> containing all the <Build ... /> entries like so:

...
<ItemGroup Label="Folders">
    <Folder Include="###" />
</ItemGroup>
<ItemGroup Label="SqlFiles">
    <Build Include="###.sql" />
</ItemGroup>

Copy this script into a file called AutoAddSql.ps1:

$Filename = $args[0]

# Make .NET's current directory follow PowerShell's current directory, if possible.
if ($PWD.Provider.Name -eq 'FileSystem') {
    [System.IO.Directory]::SetCurrentDirectory($PWD)
}

# Open SqlProj file as XmlDocument
$SqlProj = New-Object -TypeName System.Xml.XmlDocument
$Namespace = New-Object -TypeName System.Xml.XmlNamespaceManager -ArgumentList $SqlProj.NameTable
$SqlProj.Load($Filename)
$Namespace.AddNamespace("ns", $SqlProj.Project.xmlns)

# Fetch Folders and SqlFiles ItemGroups
$FoldersXml = $SqlProj.SelectSingleNode("//ns:ItemGroup[@Label='Folders']", $Namespace)
$SqlFilesXml = $SqlProj.SelectSingleNode("//ns:ItemGroup[@Label='SqlFiles']", $Namespace)

# Drop all ChildNodes of the ItemGroups
@($FoldersXml.ChildNodes) | % { $FoldersXml.RemoveChild($_) } > $null
@($SqlFilesXml.ChildNodes) | % { $SqlFilesXml.RemoveChild($_) } > $null

# Get Folders and Files
$Dirs = @(Get-ChildItem -Exclude @(".git",".vs","bin","obj") -Directory  | %{ $_ | Resolve-Path -Relative })
$Dirs += $Dirs | %{ $_ | Get-ChildItem -Recurse | Where-Object -Property PSIsContainer -Eq true | Resolve-Path -Relative }
$Dirs = $Dirs | Sort-Object -Unique

$Folders = $Dirs -replace "^\.\\",""
$Files = $Dirs | Get-ChildItem -Recurse -Filter *.sql | % { $($_.FullName | Resolve-Path -Relative) -replace "^\.\\","" } | Sort-Object -Unique

# Update SqlProj
$Folders | % {
    $Element = $SqlProj.CreateElement("Folder", $SqlProj.Project.xmlns)
    $Element.SetAttribute("Include", $_)
    $FoldersXml.AppendChild( $Element )
} > $null
$Files | % {
    $Element = $SqlProj.CreateElement("Build", $SqlProj.Project.xmlns)
    $Element.SetAttribute("Include", $_)
    $SqlFilesXml.AppendChild( $Element )
} > $null

# Output
$SqlProj.Save($Filename)

You can then run the script from the working directory containing you .sqlproj project file like so:

./AutoAddSql.ps1 "database_name.sqlproj"

This will ensure that all .sql scripts and folder paths are represented within your SQL Database Project. You could even add this as a PreBuild event to ensure it’s always up-to-date.

Next Oldest Post