SirChristian.net

I’m convinced that Powershell is the way that .NET web applications should be installed on the server(s). In the past I’ve created a separate executable file to do setup for me. Recently I’ve found executing an exe through Powershell (particularly when using Powershell for your deployment script) takes away from the Powershell goodness.

On pretty much every project I’ve needed to use a database on in the past 5 or 6 years I’ve used the excellent Insight Micro-ORM as my ORM. As a companion to the ORM @jonwagnerdotcom has also released Insight.Database.Schema that can automatically install the DB schema and upgrade the schema.

Insight.Database.Schema is a .NET dll so we can reference it through Powershell.

function InstallDatabase([string] $ConnectionString) {
	Write-Host "Installing Database"

	# Get the insight schema dll
	[System.Reflection.Assembly]::LoadFrom("c:\Insight.Database.Schema.dll")
	
	# Make sure the DB Exists
	[Insight.Database.Schema.SchemaInstaller]::CreateDatabase($ConnectionString)

	# Connect to the DB
	$db = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString
	$db.Open()

	# Get the sql files to install
	$sqlFiles = Get-ChildItem .\Database
	
	# Create the schema by looping through our sql files
	$schema = New-Object -TypeName Insight.Database.Schema.SchemaObjectCollection
	foreach($sqlFile in $sqlFiles) {
		Write-Host "Adding SQL from $sqlFile to schema"
		$schema.Load($sqlFile.FullName)
	}
	$schema.StripPrintStatements = $true

	# Install the schema into the DB
	$installer = New-Object -TypeName Insight.Database.Schema.SchemaInstaller -ArgumentList $db
	(New-Object -TypeName Insight.Database.Schema.SchemaEventConsoleLogger).Attach($installer);
	$installer.Install("SourceInquiry", $schema)

	# Cleanup
	$db.Close()
	Write-Host "Finished installing Database"
}

Doing it this way requires only management of the SQL files and Insight does the rest! Leave any questions in the comments or reach out to me on Twitter.

This entry was posted on Wednesday, November 6th, 2013 at 6:08 am and is filed under PowerShell, SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.