Thanks to @j_mcmullan
Apperently there's a lot more info on these tables
select distinct--c.session_id
c.client_net_address
,s.database_id
,db_name( s.database_id)
from sys.dm_exec_connections c
inner join sys.dm_exec_sessions s on c.session_id = s.session_id
where s.database_id = db_id('ASPState')
GO
(I beleiveit may be ironical that I was looking at sessions to a session database).
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
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)
"Give me six hours to chop down a tree and I will spend the first four sharpening the axe."
Always use the ISO 8601 date format
yyyy-mm-ddThh:mm:ss[.mmm]
You must specify everything except the [.mmm] e.g. 2014-12-24T00:00:00
OK – not too complex this but quick and easy.
Firstly I create a history table – to do this I get SQL management studio to generate a create script for the table I want to audit. I add the postscript “History” to the table name, remove the constraints and indexes and add in 2 extra columns, one for the type of update and one for the date/time e.g.
CREATE TABLE [dbo].[tblProductCodeHistory]
(
chrChangeType nchar(1) NOT NULL,
dteChangeTime DateTime NOT NULL,
[intProductId] [int] NOT NULL,
[intSalesAccountId] [int] NOT NULL,
[intSalesTaxCodeId] [int] NOT NULL,
[vcrDescription] [nvarchar](100) NULL,
[intBusinessId] [int] NOT NULL,
[decNetPrice] [money] NULL,
[vcrProductCode] [nvarchar](100) NOT NULL,
[decCost] [money] NULL,
[intSupplierAccountId] [int] NULL,
[intProductTypeId] [int] NOT NULL,
[intQuantity] [decimal](10, 3) NOT NULL ,
[intUserId] [int] NULL,
[dteUpdated] [datetime] NULL,
[vcrUnit] [dbo].[ShortString] NULL DEFAULT (NULL),
) ON [PRIMARY]
GO
Then I create a couple of triggers on the original table as follows -
CREATE TRIGGER [dbo].[ProductCode_Delete]
ON [dbo].[tblProductCode]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO tblProductCodeHistory
SELECT 'D',GetDate(),* FROM deleted
-- Insert statements for trigger here
END
GO
/****** Object: Trigger [dbo].[Transaction_Update] Script Date: 08/11/2009 16:54:16 ******/
CREATE TRIGGER [dbo].[ProductCode_Update]
ON [dbo].[tblProductCode]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO tblProductCodeHistory
SELECT 'U',GetDate(),* FROM deleted
-- Insert statements for trigger here
END
Once you’ve done this once then just do a search and replace to create the next set of trigger e.g. replace ProductCode in the above script with MyTable.
One thing to bear in mind is if you have a username in the original table then you might want to update this as part of the delete stored proc so the last username gets in the history table.
A colleague of mine has been promising to create a script to create this off the sysobjects – but I’m not holding my breath!