OK – so my audax applicaiton has not received an update in 4 months and I have just completed the paperwork for the Titanic Torr 2017.
Failed.
Thinking about it on my bike the MVP was far to big.
All I really want is a list of entrants details – that’s the MVP – one page – fill in the form.
To close for the North to the South and only a select few idiots elite riders do this but Antrim 300 will be a target!
Yes I am still going – and starting to get things done. Spare time projects are hard to work on because code requires lots of long focus. But, I’ve found that by really breaking down tasks (I believe they call that pebbleisation) to items taking less than an hour I can quickly pick things up and get something done. On a full time project you can get the whole thing straight in your head and then go ahead and implement but when there’s days or even weeks between then you need to have really recorded what you were going to do.
Not quite as easy as deploying the sql as connecting locally I’ve been using my own credentials. For an azure connection I need to:
Why don’t I just use the “sa” password.
Well – least privilege – a good security practice.
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.
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.
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
![image_thumb[7]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMFQ0_P2I8xTj_3-fuDcQYRD3SNdKKdjdM_aI2T02W2Dc_z841Q7MPepxc7KVRWpEv9_VbnY9AsO_oO0v1-fmMCE76JNMf6g2w0l2m7I0-HvRveOHKI0CkMHjr2LuO0X9mM97ORnCdG9M/?imgmax=800 "image_thumb[7]")
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)
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)
Refactor the import script so it is fit for purpose.
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.
#
# 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")
This is a part time project and the thinking is taking a lot more time than the coding – which is good because I’ve a lot of time on the bike for thinking and not much time at the keyboard for coding.
Bit of a change in strategy – a sprint is going to take a week or less but will have just one hour of coding! This should try and make me actually do some coding each week rather than continuously think.
Not only did I massively overshoot my one hour development time, I also have doubts as to weather I was really adding value to the “product”.
OK – so I’ve uploaded a load of data – but I’m not sure I can actually use that data. But, upon thinking about it, I might be able to implement user stories whereby I already have the rider data and just get them to confirm it. Since they supplied the data for my events – I’m kind of assuming that they’ve allowed me to use it for my events but there is obviously a massive legal bag of spanners to do with data shareing etc. that I’ll have to try and put something together on.
I guess the value added bit is that I’ve validated the schema.
Going forward the focus needs to be
Epic – 4307 – “I want a rider to be able to enter all there details online when the event opens and to be able to prepare brevet cards for the event.”
"Give me six hours to chop down a tree and I will spend the first four sharpening the axe."
