So – sprint 2 took about an hour and a half of coding – so a lot closer than the first.
I then noticed that I still had some inline nasty SQL in my powershell – so I cracked out another hour and got another proc and changes in.
Main lesson so far – an hour is a really short time – keep pebbleising the tasks.
Outcome
Ok – so the work got done and hopefully I’ll have a list of riders on production soon.
I’ve thought hard about it and its definitely a useful task towards the end goal as the first “Minimum” viable product is for riders to sign up. I’m going to allow google authentication/Oath which will strongly (enough for this) validate the users email which I can then use to look up there details in the data I have.
A good start.
Some feedback on the tasks here – really putting this code here for anyone to look @ until I work out if/what to open source. Unless you particularly want to critique my sql or look at how I use powerhell to call stored procs then not much excitement.
“Work out how to deploy database project to local database and how to connect to it. “
this was pretty easy – just set the PowerShell script to depend on the database project and then it deploys all your changes as it builds – pretty nifty stuff
“Create stored procs for crud operations required by script – include find rider.”
CREATE PROCEDURE [dbo].[ups_RiderInsert]
@vcrFirstName NVARCHAR (100) ,
@vcrSurname NVARCHAR (100) ,
@vcrAddressLine1 NVARCHAR (100) ,
@vcrAddressLine2 NVARCHAR (100) ,
@vcrTown VARCHAR (100) ,
@vcrCountryCode VARCHAR (2) ,
@vcrCountry VARCHAR (100) ,
@vcrPhoneNumber VARCHAR (20) ,
@vcrClub VARCHAR (50) ,
@vcrClubNumber VARCHAR (20) ,
@vcrECName VARCHAR (100) ,
@vcrECTelephone VARCHAR (100)
AS
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT INTO tblRider
VALUES(
@vcrFirstName,
@vcrSurname ,
@vcrAddressLine1 ,
@vcrAddressLine2 ,
@vcrTown ,
@vcrCountryCode ,
@vcrCountry ,
@vcrPhoneNumber ,
@vcrClub ,
@vcrClubNumber ,
@vcrECName ,
@vcrECTelephone)
SELECT SCOPE_IDENTITY() AS INT
COMMIT TRANSACTION
CREATE PROCEDURE [dbo].[usp_EventParticipentInsert]
@intRiderId INT ,
@intEventId INT ,
@decPaidSTG MONEY,
@decPaidEUR MONEY
AS
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT INTO tblEventParticipant
VALUES(
@intRiderId ,
@intEventId ,
@decPaidSTG ,
@decPaidEUR
)
SELECT SCOPE_IDENTITY() AS INT
COMMIT
CREATE PROCEDURE [dbo].[usp_LicenceGetByRiderAndYear]
@intRiderId int,
@intYear int
AS
SELECT * FROM dbo.tblLicense
WHERE
intRiderId = @intRiderId
AND intYear = @intYear
CREATE PROCEDURE [dbo].[usp_LicenseInsert]
@intYear INT,
@intRiderId INT,
@vcrLicenceNumber NVARCHAR(20)
AS
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT INTO tblLicense
VALUES(@intYear , @intRiderId, @vcrLicenceNumber)
COMMIT
CREATE PROCEDURE [dbo].[usp_RiderSearchByName]
@FirstName nvarchar(100) ,
@SurName nvarchar(100)
AS
SELECT * FROM tblRider r
WHERE (r.vcrFirstName like @FirstName + '%' OR @FirstName IS NULL)
AND (r.vcrSurname like @SurName + '%' OR @SurName IS NULL)
“Enahance script / tidy up for stored proc use and other comments in script below.”
param (
[Parameter(Mandatory=$true)][string]$fileName,
[Parameter(Mandatory=$true)][string]$connString,
[Parameter(Mandatory=$true)][int]$intEventId,
[Parameter(Mandatory=$true)][int]$intYear
)
function ToDBNull($var) {
if($var -eq $null) {return [System.DBNull]::Value}
return $var;
}
#
# 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 Parameters
$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;
($sh.Cells.Item($i,1).Value2 -ne $null) -and ($sh.Cells.Item($i,1).Value2.Trim() -ne "") ;
$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
[System.Data.SQLClient.SQLCommand]$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $conn
$Command.CommandType = [System.Data.CommandType]::StoredProcedure
# - lookup first - do within proc?.
$Command.CommandText = "[dbo].[usp_RiderSearchByName]"
$Command.Parameters.aDD("@FirstName",[System.Data.SqlDbType]::NVarChar)
$Command.Parameters["@FirstName"].Value = $Name
$Command.Parameters.Add("@SurName",[System.Data.SqlDbType]::NVarChar ).Value = $Surname
[int]$ID = $null
[System.Data.SqlClient.SqlDataReader ]$reader = $Command.ExecuteReader()
try{
if($reader.Read()){
$ID = $reader["intRiderID"]
}
}
finally{
$reader.Close()
}
# if we didn'te find a rider then create on
if($ID -eq 0) {
[System.Data.SQLClient.SQLCommand]$Command = $Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $conn
$command.CommandType = [System.Data.CommandType]::StoredProcedure
$Command.CommandText = "[dbo].[ups_RiderInsert]"
$Command.Parameters.Add("@vcrFirstName",[System.Data.SqlDbType]::NVarChar ).Value = $Name
$Command.Parameters.Add("@vcrSurname",[System.Data.SqlDbType]::NVarChar ).Value = $Surname
$Command.Parameters.Add("@vcrAddressLine1",[System.Data.SqlDbType]::NVarChar ).Value = ToDBNull($Address.Split(',')[0])
$Command.Parameters.Add("@vcrAddressLine2",[System.Data.SqlDbType]::NVarChar ).Value = ToDBNull($Address.Split(',')[1])
$Command.Parameters.Add("@vcrTown",[System.Data.SqlDbType]::NVarChar ).Value = ToDBNull($Address.Split(',')[2])
$Command.Parameters.Add("@vcrCountryCode",[System.Data.SqlDbType]::NVarChar ).Value = ToDBNull($null)
$Command.Parameters.Add("@vcrCountry",[System.Data.SqlDbType]::NVarChar ).Value = ToDBNull($null)
$Command.Parameters.Add("@vcrPhoneNumber",[System.Data.SqlDbType]::NVarChar ).Value = ToDBNull($Tel)
$Command.Parameters.Add("@vcrClub",[System.Data.SqlDbType]::NVarChar ).Value = ToDBNull($Club)
$Command.Parameters.Add("@vcrClubNumber",[System.Data.SqlDbType]::NVarChar ).Value = ToDBNull($ClubNo)
$Command.Parameters.Add("@vcrECName",[System.Data.SqlDbType]::NVarChar ).Value = ToDBNull($EC)
$Command.Parameters.Add("@vcrECTelephone",[System.Data.SqlDbType]::NVarChar ).Value = ToDBNull($ECTel)
[int]$ID = $Command.ExecuteScalar()
}
[System.Data.SQLClient.SQLCommand]$Command = $Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $conn
$command.CommandType = [System.Data.CommandType]::StoredProcedure
$Command.CommandText = "[dbo].[usp_EventParticipentInsert]"
$Command.Parameters.Add("@intRiderId",[System.Data.SqlDbType]::Int).Value = $ID
$Command.Parameters.Add("@intEventId",[System.Data.SqlDbType]::Int).Value = $intEventId
$Command.Parameters.Add("@decPaidSTG",[System.Data.SqlDbType]::Money).Value = If($PaidS -ne $null) {$PaidS} else {0}
$Command.Parameters.Add("@decPaidEUR",[System.Data.SqlDbType]::Money).Value = If($PaidE -ne $null) {$PaidE} else {0}
$Command.ExecuteScalar()
# if got a CI number
if($CI -ne $null -and $CI.Trim() -ne '') {
# search for a licence
[System.Data.SQLClient.SQLCommand]$Command = $Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $conn
$command.CommandType = [System.Data.CommandType]::StoredProcedure
$Command.CommandText = "[dbo].[usp_LicenceGetByRiderAndYear]"
$Command.Parameters.Add("@intRiderId",[System.Data.SqlDbType]::Int).Value = $ID
$Command.Parameters.Add("@intYear",[System.Data.SqlDbType]::Int).Value = $intYear
[int]$LID = $null
[System.Data.SqlClient.SqlDataReader ]$reader = $Command.ExecuteReader()
try{
if($reader.Read()){
$LID = $reader["intRiderID"]
}
}
finally{
$reader.Close()
}
# no licence found - create one
if($LID -eq 0){
[System.Data.SQLClient.SQLCommand]$Command = $Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $conn
$command.CommandType = [System.Data.CommandType]::StoredProcedure
$Command.CommandText = "[dbo].[usp_LicenseInsert]"
$Command.Parameters.Add("@intRiderId",[System.Data.SqlDbType]::Int).Value = $ID
$Command.Parameters.Add("@intYear",[System.Data.SqlDbType]::Int).Value = $intYear
$Command.Parameters.Add("@vcrLicenceNumber",[System.Data.SqlDbType]::NVarChar).Value = $CI
$Command.ExecuteScalar()
}
}
}
}
Finally
{
$conn.Close()
}
$wb.Close()
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
}
parseExcel($fileName)
