.netTiers Not Handling Nullable Types as Input Parameters Correctly

Use the following script to create a table in SQL Server:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NullableTypeBug](
    [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [ContractId] [int] NOT NULL,
    [SubContractId] [int] NULL,
 CONSTRAINT [PK_NullableTypeBug] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY],
 CONSTRAINT [IX_NullableTypeBug_1] UNIQUE NONCLUSTERED 
(
    [ContractId] ASC,
    [SubContractId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

Use NetTiers to generate an entity and a service layer.

You will then get a NullableTypeBugService class with a 
GetByContractIdSubContractId(int contractId, int? subContractId) method. Now, 
let's say you call this method with 1000 as ContractId and with Null as 
subContractId, the SQL generated for the Where Clause will be:

WHERE
[ContractId] = @ContractId
AND [SubContractId] = @SubContractId

and further down the SQL, the parameters will be defines as such:

',N'@ContractId int,@SubContractId int',@ContractId=1000,@SubContractId=NULL

However, the where clause is erroneous in the case where @SubContractId is 
null. "[SubContractId] = @SubContractId" would have to be replaced by 
"[SubContractId] IS NULL".

One solution is to replace SubContractId with a zero, an empty space, or any 
other appropriate value in the Where Clause:

WHERE
[ContractId] = @ContractId
AND ISNULL([SubContractId], 0) = ISNULL(@SubContractId, 0)

This may not be a solution in all cases however. There has to be a value by 
which a Null can be replaced.

I found this bug in .NetTiers 2.3.0 and I am using CodeSmith Standard 4.1.4

Original issue reported on code.google.com by viave...@gmail.com on 9 Aug 2011 at 8:05

yasser777 / nettiers

.netTiers Not Handling Nullable Types as Input Parameters Correctly #394