Archive for the ‘SQL’ Category

Monday, January 12th, 2015

This is a quick start for getting SQL Server unit tests going inside Visual Studio. This assumes that you already have a database and that you already have a development process and an installation process for your database. If you are starting completely from scratch then you may want to just follow Microsoft’s Walkthrough for Creating and Running SQL Server Unit tests.

Steps to create

  1. Open your solution add a new ‘Unit Test Project’
    NewProject
  2. Add a new Sql Server Unit Test file by right clicking the new unit test project and selecting ‘Add New Item…’
    AddSqlServerUnitTest
  3. You will be prompted to setup your SQL Server connection. Setup a connection to a new DB.
  4. Double click on SqlServerUnitTest1.cs to bring up database test designer.
    Database Test Designer
  5. The database test designer take a little bit getting used to. The top navigation bar has 3 areas. The first area is a drop down to select a test, the second area (circled in red in the image above) lets you pick pre-test, test, or post-test, and the 3rd area is for test management (add/remove/rename).
  6. In the database test designer on the top bar click the green plus sign to create a new test.
  7. Use the 2nd drop down in the top bar (circled in red) to select a pre-test. In the main window pane write whatever SQL is needed to setup your test. Note of caution: The pre-tests and post-tests also allow for test conditions. If you accidently add a condition to a pre-test your test may fail.
  8. Again using the 2nd drop down in the top bar (circled in red) select the test. Here you will write SQL that you want to test. For example this would be where you would call a stored procedure that you wish to test.
  9. The test will need test conditions. The bottom of the designer are test conditions. There are a limited set of built in test conditions. To create your own follow the Microsoft article on Custom Test Conditions. Just select a condition and click the green plus to add it. To edit what it tests use the properties window.

At this point you will have a SQL server test that you can run. There should have been a file, SqlDatabaseSetup.cs, that was automatically created that you can edit to automatically install your DB. You can also put any setup that you need to do, i.e. dropping the DB and creating test data inside this file.

Posted in SQL | No Comments

Wednesday, November 6th, 2013

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.

Posted in PowerShell, SQL | 1 Comment

Saturday, August 4th, 2012

Smaller entry today. Wrote some cool SQL that I just had to share, but didn’t feel like it needed to be wrapped in a lager entry.

TSQL makes it easy to ORDER BY and SELECT TOP when there is a set of data well defined by your schema. Given a table:

---------------------------------------------
ID | Manufacturer | Model      | UnitsSold
---------------------------------------------
1  |  Ferrari     | 599 GTO    |   392
2  |  Ferrari     | Enzo       |   34
3  |  Ferrari     | Testarossa |   218
4  |  Ferrari     | Dino       |   87
5  |  Porsche     | 911        |   1302
6  |  Porsche     | Boxster    |   1511
7  |  Porsche     | 928        |   819
---------------------------------------------


DECLARE @CarTable TABLE
(
ID INT,
Manufacturer VARCHAR(128),
Model VARCHAR(128),
UnitsSold INT
)
INSERT INTO @CarTable (ID, Manufacturer, Model, UnitsSold) VALUES (1,'Ferrari','599 GTO',392)
INSERT INTO @CarTable (ID, Manufacturer, Model, UnitsSold) VALUES (2,'Ferrari','Enzo',340)
INSERT INTO @CarTable (ID, Manufacturer, Model, UnitsSold) VALUES (3,'Ferrari','Testarossa',218)
INSERT INTO @CarTable (ID, Manufacturer, Model, UnitsSold) VALUES (4,'Ferrari','Dino',87)
INSERT INTO @CarTable (ID, Manufacturer, Model, UnitsSold) VALUES (5,'Porsche','911',1302)
INSERT INTO @CarTable (ID, Manufacturer, Model, UnitsSold) VALUES (6,'Porsche','Boxster',1511)
INSERT INTO @CarTable (ID, Manufacturer, Model, UnitsSold) VALUES (7,'Porsche','928',819)

Getting the top 2 selling models of cars is a fairly straight forward SQL query.

SELECT TOP 2
Manufacturer,
Model,
UnitsSold
FROM @CarTable
ORDER BY UnitsSold DESC

Things get more complicated if we wanted to get the top 2 selling model of cars per manufacturer. The trick is to use the RANK function.

SELECT
Manufacturer,
Model,
UnitsSold
FROM
(
SELECT
Manufacturer,
Model,
UnitsSold,
Rank = RANK() OVER (PARTITION BY Manufacturer ORDER BY UnitsSold DESC)
FROM @CarTable
) cars
WHERE cars.[Rank] <= 2 -- Get top 2 from each Manufacturer
ORDER BY Manufacturer

Posted in Code, SQL | No Comments