Sprint 2

Goal

Refactor the import script so it is fit for purpose.

Tasks

1. Work out how to deploy database project to local database and how to connect to it.
2. Create stored procs for crud operations required by script – include find rider.
3. 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")