Sprint 1 Retrospective 1 – one hell of an overrun

_Overview

I’ll cover all that happened in subsequent blog entries – but it took three hours to accomplish the one hour goal that I set in Sprint 1.
Event though I’d never used VS DB Projects before, I created the project and schema and got it deployed in about 25 minutes which I thought was fair enough.  DB project was pretty easy – as I expected it to be.  To be honest I just created the tables with the tool and then typed in the columns, constraints etc. in the SQL window!
It seems to create a local database too – I’ll have to work out where that is for future testing as I now have live data.
Then came the PowerShell task.
I decided fairly late on to parse the excel rather than csv as I thought it would get rid of any comma separation issues.  A couple of google searches showed it was relatively easy.
It was – but using .Value instead of .Value2 (why?) cost me some time then building sql by concatenating strings was a stupid idea and I still had to work out how to cope with special characters like the ‘ which is prevalent in O’Tool, O’Connor etc.

Lessons

1. Really need to break down the user stories to tiny bits!  Not sure how to deliver useable features at the same time though.
2. Do the right thing – I new I should be creating stored procs
1. they’d certainly add valiue down the line
2. Strong typing is always the way to go
3. Don’t do new stuff in a limited time – I’d never parsed powershell with excel and was caught out by lack of experience.
4. Check for extra features/gold plating.  I added in the Organiser table because it’s in my schema but had no date for it or defined columns.  I then had to work out how to insert a row into a database with just an identity column!

The Script

Not much to publish – I’ll get the schema out – but here is the powershell that I used.  It’s probably full of holes!

#
# Script.ps1
#
function parseExcel([String] $fileName){
 $excel=new-object -com excel.application
 $wb=$excel.workbooks.open($fileName)
 $sh = $wb.Sheets.Item(1)
 $startrow = 2
 $endrow = 16#Connection and Query Info

$serverName="uj6arxo0jh.database.windows.net" 
$databaseName='AudaxDB' 
$connString = "Server=$serverName;Database=$databaseName;uid=stiona;pwd=2difficult4u2c#" 
$conn = new-object System.Data.SqlClient.SqlConnection $connString 
$conn.Open()
try{



 for($i = $startrow; $i -le $endrow; $i++) {
$Name = $sh.Cells.Item($i,1).Value2
$Surname = $sh.Cells.Item($i,2).Value2
[String]$Address = $sh.Cells.Item($i,3).Value2
$Tel = $sh.Cells.Item($i,4).Value2
$email = $sh.Cells.Item($i,5).Value2
$Club = $sh.Cells.Item($i,6).Value2
$CI = $sh.Cells.Item($i,7).Value2
$ClubNo = $sh.Cells.Item($i,8).Value2();
$EC = $sh.Cells.Item($i,9).Value2
$ECTel = $sh.Cells.Item($i,10).Value2
$PaidE = $sh.Cells.Item($i,11).Value2
$PaidS = $sh.Cells.Item($i,12).Value2 

$Command =  $Command = New-Object System.Data.SQLClient.SQLCommand

    $Command.Connection = $conn

    $Command.CommandText = "INSERT INTO tblRider VALUES( '" + $Name  + "'" +
    ",'" + $Surname.Replace("'","''") + "'" +
    ",'" + $Address.Split(',')[0] + "'" +
    ",'" + $Address.Split(',')[1] + "'" +
    ",'" + $Address.Split(',')[2] + "'" +
    ",NULL" + 
    ",NULL" +
    ",'" + $Tel + "'" +
    ",'" + $Club + "'" +
    ",'" + $ClubNo + "'" +
    ",'" + $EC + "'" +
    ",'" + $ECTEL + "'" +
        ")`n`r" + 
        "SELECT @@IDENTITY `n`r"
        
        
        $Command
        [int]$ID = $Command.ExecuteScalar()
        $ID
$Command =  $Command = New-Object System.Data.SQLClient.SQLCommand

    $Command.Connection = $conn
    $Command.CommandText = 
       "INSERT INTO tblEventParticipant VALUES( " +  $ID  +",1,'" + $PaidS + "','" + $PaidE +"')" 
    $Command
    $Command.ExecuteScalar()


if($CI -ne $null -and $CI.Trim() -ne '') { 
$Command =  $Command = New-Object System.Data.SQLClient.SQLCommand

    $Command.Connection = $conn
    $Command.CommandText = 
       "INSERT INTO tblLicense  VALUES( 2016, " +  $ID  + ",'" + $CI +"')" 
    $Command
        $Command.ExecuteScalar()

    }

 }
}
Finally
{
$conn.Close()
}
$wb.Close()
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

 }

 parseExcel("C:\Users\stuartm\OneDrive\Documents\Audax\A300_2016\A30002016Entrants.xlsx")