Translate

Saturday 13 July 2013

DBFit + Slim + LocalDB

Cool!

Only word to  describe the way you can work with databases nowadays.

As I tried to figure out a way to work with a complex set of data using stored procedures (add to to this a SQL Server error that disallowed attaching a database file to the server), I realized the incredible number of options available to work with the database file - 1. Azure storage  2. Visual Studio  Data Connection  3. LocalDB  4. Read data in XML and import from XML file into DB and the list could go on, if one applies one's mind to it! Although I have not tried this but it may even be possible to use Excel to work with the DB (with the DataConnection or is it UDL or F?)...

So, first I tried to connect with VS to the LocalDB and succeeded in attaching the .mdf file.

Next, I wanted to test the stored procedures in the DB and since writing code to execute the procs looked tedious, decided to use the Management Studio 2012 to connect to the LocalDB and succeeded without any error!

So, executing the stored procedures is a simple step but I was not satisfied and so decided to use DBFit and since I wanted to write some functional acceptance tests, my first thought went to Slim!

A fantastic tool, Slim, just got better with support (this word 'support' is contentious as the DBFit team (or was it the Slim team)?) :D insists that there is no Slim support for DBFit) for  DBFit!

All you need to do is download DBFit and extract it into the Fitnesse folder and run the StartFitnesse.bat file (in Windows).

One of the more likely problem areas will be the connection string to the database from DBFit. If  you are a VS user for long, you will manage but if  you are not here is one connection string that you would like to use

Below is the LocalDB connection string, for integrated security, that you may not find anywhere else -

!|connect|Data Source=(LocalDB)\v11.0;AttachDbFilename='path to database file';Integrated Security=True;Connect Timeout=30|

The simpler one is

!|Connect|JV-PC|YourUserName|YourPassword|YourDatabaseName|

You can use almost any command you may otherwise use in the database environment and so the script to test 'Transaction' is

!|DatabaseEnvironment|
|commit|

!|DatabaseEnvironment|
|rollback|

Use the Rollback script in the TearDown fixture as is so that it could use the default, or the current connection/transaction.

Super cool!

Connect to http://localhost:8085/ after starting the Fit server with StartFitnesse.bat

Create a page by typing it in the address bar and type or copy/paste the below code, Save, Change the
properties of the page to Test, save the properties and theTest menu item will appear.

A simple query syntax to start with DBFit and Slim

!define COMMAND_PATTERN {%m -r fitnesse.fitserver.FitServer,"FitSharp\fit.dll" %p}
!define TEST_RUNNER {FitSharp\Runner.exe}
!define PATH_SEPARATOR {;}
!path FitSharp\dbfit.sqlserver.dll

!|dbfit.SqlServerTest|

|Query|SELECT n FROM ( SELECT 1 as n union all select 2 union all select 1 ) x |
|n|
|2|
|1|
|3|

|Ordered Query|SELECT n FROM ( SELECT 1 as n union select 2 union select 3 ) x|
|n|
|1|
|2|
|3|


You can also execute stored procedures, functions and any query that you may otherwise be executing with SSMS.


No comments: