So I have been working on a project for myself the past couple of months off and on and lately every time I switch from my laptop to my desktop or try to deploy it into my test environment I have had to update the database and remember to make the same change to my 3 computers.   I went looking for something simple as mud that worked worked exactly like I wanted it to.  I found a lot of good tools to auto generate my database differencing scripts but as I wrote the scripts myself and numbered them so I would remember which ones still needed to be ran those tools didn't help me.  What I needed was something that could run batch scripts against a Sql Server database and that would keep track of the database version for me.   I introduce you to Kiss DB Change Management.

Convention

1.  Create numbered Database Version scripts that are either hand written or created by a tool like DBDiff or RedGate or Whatever.
Examples
001-Creating some tables.sql
002-Creating-some-more-tables.sql
003-Insertingsomedataandcreating_views.sql
2. Place all of the Database change scripts in the same folder.
3. Thats it. What you thought it should be more difficult?

Use

KISS is a single executable file and is invoked in the following manner.(I suggest you create a bat file or run it in your Build script)
KISS.exe /f:Kissprops.xml
Kissprops.xml is a simple xml file that specifies the connection string and Database  Provider that is going to be used see below for an example.

   1: <?xml version="1.0" encoding="utf-16"?> 

   2: <Properties xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 

   3: <Verbose>true</Verbose> 

   4: <Wait>false</Wait> 

   5: <Provider>SQLServer</Provider> 

   6: <ConnectionString>server=.\SQLEXPRESS;database=KISS;User Id=KissUser;Password=$donate$;</ConnectionString> 

   7: <VersionTable>db_schema</VersionTable> 

   8: <VersionScriptsFolder>C:\dev\Schema\SqlServer</VersionScriptsFolder> 

   9: </Properties> 

How does it work

KISS will first discover what version the database is at by reading from the Version Table you specified (If the table doesn't exist KISS will create it so you don't need to worry about it).
KISS will then run each script that has a version above that of the database.
KISS will execute batch scripts so you can create multiple tables, views etc in the same script.
KISS executes each script as a Transaction so if the script fails the Transaction is rolled back the error message is displayed and execution is stopped. Oh it also returns an error code of 400 so that if you use it with MsBuild or Nant your build will Fail.

 

Currently Supported DataBases

SqlServer
I have set it up so that there is only one class and 4 methods needed to create a provider for any other database or to create an alternative SQL Server provider if someone ever needed to.

   

It seems that everyone has been talking about TDD and setting up a Continuous Integration server.   The goal is very simple.   Build software in less time that is more robust and easier to maintain.    One or the tools that helps in this process is FxCop.  FxCop will inspect your compiled dll’s to ensure that they follow design rules and naming conventions that you specify.  FxCop interestingly enough was created by Microsoft internally to ensure that the .Net framework met their own guid lines.  FxCop has a GUI but it will also create an XML file that can be used by CruiseControl.net or most likely any other build server. 

Less talk and more code…   the following is an example of how to call fxcop from the command line.

   1:  FxCopCmd.exe /searchgac /rule:NamingRules.dll /file:My.Framework.dll /out:fxCop.xml

Parameters that are passed in

/searchgac – Tells FxCop to search the gac for any referenced dll's not found in say the bin folder.  If you forget this FxCop will error out.

/rule:Rull.dll – You can specify as many rules as you like to check against your compiled dll just repeat this command with each of the Rules you like.  Unless you have a specific FxCop project you must specify at least one rule.

/file:YourCompiled.dll – Much like the rule parameter you can specify as many .dll’s as you would like to check but need at least one.

/out:someXml.xml – You are going to want to specify an XML file to output the results to so that you can integrate the results in your build log.   CruiseControl.net uses XSL to transform the generated XML into HTML for viewing in its Web Dashboard and auto generated Emails.

Putting it all together and error 9009

Now that you know what its doing lets put it together and show how to call FxCop from MsBuild’s “Exec” Task.

   1:    <Target Name="FxCop" DependsOnTargets="BuildProject>

   2:          <Exec Command="&quot;C:\Program Files\Microsoft FxCop 1.36\FxCopCmd.exe&quot;

 /searchgac /rule:"C:\Program Files\Microsoft FxCop 1.36\Rules\NamingRules.dll"

 /file:C:\Project\bin\My.Framework.dll /out:fxCop.xml" ContinueOnError="true">

   3:          </Exec>

   4:  </Target>

Error 9009- I saw a lot of entries about this error when googling to figure it out myself.  The error is caused by calling C:\Program Files\.. without putting quotes around it (“C:\Program Files..”).  As we are calling it from an MsBuild file which is XML we have to encode the quotes as “"”

ContinueOnError – Unless you have written your own FxCop rules you are most likely going to want to set this value to true so that your build doesn’t fail each time you break one of the many rules that FxCop specifies.