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
- Really need to break down the user stories to tiny bits! Not sure how to deliver useable features at the same time though.
- Do the right thing – I new I should be creating stored procs
- they’d certainly add valiue down the line
- Strong typing is always the way to go
- Don’t do new stuff in a limited time – I’d never parsed powershell with excel and was caught out by lack of experience.
- 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")
