Goal
Refactor the import script so it is fit for purpose.
Tasks
- Work out how to deploy database project to local database and how to connect to it.
- Create stored procs for crud operations required by script – include find rider.
- Enahance script / tidy up for stored proc use and other comments in script below.
Outcome
I can parse all the sheets I have to load an initial dataset. I have a set of re-useable stored procedures for the application.
The script to fix
#
# Script.ps1
# TODO - rename the bit above and document this method
function parseExcel([String] $fileName){
$excel=new-object -com excel.application
$wb=$excel.workbooks.open($fileName)
$sh = $wb.Sheets.Item(1)
# TODO de hard code teh endrow.
$startrow = 2
$endrow = 16#Connection and Query Info
# prpbably best to pass the database name etc. through as paramaters
$serverName="uj6arxo0jh.database.windows.net"
$databaseName='AudaxDB'
#TODO And this really shouldn't be here!
$connString = "Server=$serverName;Database=$databaseName;"
$conn = new-object System.Data.SqlClient.SqlConnection $connString
#TODO Should the open be in the try/catch?
$conn.Open()
try{
# Main for loop for parsing sheet
# TODO rather than hard coding rows do until no end.
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
# TODO - do we need to close the command - check documentation
# to see if it implements IDisposable - and is there a using equivalent in PowerShell
$Command = $Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $conn
# TODO - convert to strongly typed stored procedure.
# also - lookup first - do within proc?.
$Command.CommandText = "INSERT INTO tblRider VALUES( '" + $Name + "'" +
",'" + $Surname.Replace("'","''") + "'" + # notice the fancy replace here for the O' people
",'" + $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
# TODO - remove not required
#TODO create stronly typed proc.
$Command.Connection = $conn
$Command.CommandText =
"INSERT INTO tblEventParticipant VALUES( " + $ID +",1,'" + $PaidS + "','" + $PaidE +"')"
$Command
$Command.ExecuteScalar()
# TODO again - needs stored proc.
#
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")
