Today we’ll look at the server side code portion of the solution. I’m definitely in favor of stored procs, so that’s what you’ll see here. I typically only handle errors within a proc if I have the ability to do something there to fix the problem, otherwise I raise them back to the application. I was trying for the minimum amount of code, both to save time writing and in testing.

We start with the main proc we call every time someone hits the voting page. We may or may not get ballotID and VoterID, and you can see I’m doing something you probably don’t see often, returning both output parameters and a resultset.

ALTER proc [Voting].[GetBallotDetails]
    @BallotID uniqueidentifier,
    @VoterID uniqueidentifier,
    @IsEligibleToVote bit output,
    @HasVoted bit output

as

set nocount on
set xact_abort on

select @IsEligibleToVote = voting.IsEligibleToVote(@BallotID, @VoterID)
select @HasVoted = voting.HasVoted(@BallotID, @VoterID)

select
    B.BallotID,
    B.Title as ItemTitle,
    B.MaxSelection,
    B.MinSelection,
    B.Description,
    B.ClosingDate,
    BD.BallotDetailID,
    BD.Title as DetailTitle
from voting.BallotDetail BD Inner join voting.Ballot B on B.BallotID = BD.BallotID
where
    B.BallotID = @BallotID
    and B.ClosingDate > getutcdate()

You’ll notice I’m calling two functions. I thought I’d be able to reuse them and it made it easy to follow:

ALTER FUNCTION [Voting].[HasVoted]
(
    @BallotID uniqueidentifier,
    @VoterID uniqueidentifier
)
RETURNS bit
AS
BEGIN
    — Declare the return variable here
    DECLARE @Result bit

    if exists (select * from Voting.EligibleVoters where BallotID = @BallotID and VoterID = @VoterID and HasVoted = 1)
        set @Result = 1
    else
        set @Result  = 0

    — Return the result of the function
    RETURN @Result

END

ALTER FUNCTION [Voting].[IsEligibleToVote]
(
    @BallotID uniqueidentifier,
    @VoterID uniqueidentifier
)
RETURNS bit
AS
BEGIN
    — Declare the return variable here
    DECLARE @Result bit

    if exists (select * from Voting.EligibleVoters where BallotID = @BallotID and VoterID = @VoterID)
        set @Result = 1
    else
        set @Result  = 0

    — Return the result of the function
    RETURN @Result

END

It worked out ok. It was one of those cases where in hindsight I wonder if programming by exception (throwing an error if anything didn’t match) might have been cleaner on the app side, but it’s not a technique I normally use and am biased against.

Casting a vote is a big deal of course, and hopefully I got this right. Here I don’t just rely on what I “know” in the UI, I want to make sure I enforce the voting rules. You can see I did reuse my functions. I cheated here a little by only allowing voters to vote once, that means I don’t have to check for scenarios such as picking one option and submitting, then picking a second option and submitting. Restricting to one call means I can just check all the conditions then, with little complexity.

USE [Vote]
GO
/****** Object:  StoredProcedure [Voting].[CastVote]    Script Date: 06/11/2010 09:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [Voting].[CastVote]
    @Details varchar(8000),
    @BallotID uniqueidentifier,
    @VoterID uniqueidentifier
as

declare @MaxVotes int
declare @MinVotes int
declare @ActualCount int
declare @Temp table (DetailID uniqueidentifier)

set xact_abort on
set nocount on

–check that they are eligible to vote
if Voting.IsEligibleToVote(@BallotID, @VoterID) = 0
    begin
        raiserror (‘You are not an eligible voter for this ballot.’, 16, 1)
        return
    end

–see if user has already voted on this ballot
if Voting.HasVoted(@BallotID, @VoterID) = 1
    begin
        raiserror (‘You have already voted on this ballot.’, 16, 1)
        return
    end

–get the max number of selections allowed for this ballot
select
    @MaxVotes = MaxSelection,
    @MinVotes = MinSelection
from Ballot B
where
    BallotID = @BallotID

–convert list of votes to a table
insert into @Temp (DetailID) select convert(uniqueidentifier, Param) from voting.SplitCommaDelimitedString (@Details)

–get count of votes
select @ActualCount = count(*) from @Temp

–make sure no nulls
if @ActualCount is null
    set @ActualCount = 0

–check that #votes don’t exceed max
if @ActualCount > @MaxVotes
    begin
        raiserror (‘Selected more choices than are allowed on this ballot.’, 16, 1)
        return
    end

–and min
if @ActualCount < @MinVotes
    begin
        raiserror (‘Selected fewer than the required number of choices on this ballot.’, 16, 1)
        return
    end

begin transaction

–record the vote   
insert into voting.VotesCast (
    BallotDetailID,
    VoterID)
select
    DetailID,
    @VoterID
from @Temp

–mark as voted
update voting.EligibleVoters set
    HasVoted = 1
where
    BallotID = @BallotID
    and VoterID = @VoterID
commit transaction

To provide a voting link to a voter, we need to build an email message, which here we do by inserting a row into the Voting.Email synonym, which points to a different db and a process that converts the row into the final SMTP message. The work of populating the template is done by a function also shown below:

ALTER proc [Voting].[SendVoterLink] @BallotID uniqueidentifier, @EmailAddress varchar(150)

as

declare @Template varchar(max)
declare @VoterId uniqueidentifier

set nocount on
set xact_abort on

–log the request
insert into Voting.VoterInquiries (
    BallotID,
    EmailAddress)
values (
    @BallotID,
    @EmailAddress)

–see if possible
if not exists (select VoterID from voting.EligibleVoters where EmailAddress = @Emailaddress and ballotID = @ballotID)
    begin
        raiserror (‘You are not listed as an eligible voter for this ballot using the provided email address’, 16, 1)
        return
    end    

–get the template once
select
    @Template = TemplateHTML
from Voting.Templates
where
    TemplateName = ‘BallotEmail’

insert into Voting.Email (
    MsgFrom,
    MsgTo,
    Subject,
    Message,
    Priority,
    DBName)
select
    ‘hq@sqlpass.org’,
    EV.EmailAddress,
    ‘PASS Vote: ‘ + B.Title,
    Voting.MergeBallotInfo (@Template, @BallotID, EV.VoterID, B.Title, B.Description, B.ClosingDate),
    2,
    db_name()
from Voting.EligibleVoters EV inner join Voting.Ballot B on EV.BallotID = B.BallotID
where
    EV.HasVoted = 0
    and EV.BallotID = @BallotID
    and B.ClosingDate > GetUTCDate()
    and Ev.EmailAddress = @EmailAddress

 

USE [Vote]
GO
/****** Object:  UserDefinedFunction [Voting].[MergeBallotInfo]    Script Date: 06/11/2010 09:37:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [Voting].[MergeBallotInfo]
(
    @Template varchar(max),
    @ballotID uniqueidentifier,
    @voterid uniqueidentifier,
    @Title varchar(50),
    @Description varchar(4000),
    @ClosingDate datetime
)
RETURNS varchar(max)
AS
BEGIN

    declare @Temp varchar(max)

    set @Temp = Replace(@Template, ‘{BALLOTID}’, @BallotID)
    set @Temp = Replace(@Temp, ‘{VOTERID}’, @VoterID)
    set @Temp = Replace(@Temp, ‘{TITLE}’, @Title)
    set @Temp = Replace(@Temp, ‘{DESCRIPTION}’, @Description)
    set @Temp = Replace(@Temp, ‘{CLOSINGDATE}’, @ClosingDate)

    RETURN @Temp

END

Tomorrow we’ll cover sending out the notifications to the entire eligible voter list and the view used for reporting.