c# - 无法将 DBNull 与 NULL 列值匹配

Question

我有一个 Web 应用程序，我在其中设置一个参数以调用存储过程作为 DBNull，如果值为 null，如下所示 -

new SqlParameter(Utilities.Constants.SpParameters.EffectiveDate,r["EffectiveDate"] == null? DBNull.Value:r["EffectiveDate"]),

其中 r["EffectiveDate"] 是数据表列“EffectiveDate”的行中的值。现在在存储过程中，我正在检查表中是否存在由参数提供的值的记录。如果是，那么我更新记录，否则插入它。

表中有一条 EffectiveDate 为 NULL 的记录，但是当我尝试使用 IF EXISTS 条件检查和更新它时，不知何故，它在尝试更新时找不到与具有 DBNull 值的条目匹配并继续插入新纪录。

我尝试了以下选项但无济于事 -

1. CAST(EffectiveDate as datetime) = CAST(@EffectiveDate as Datetime) AND 

2. ISNULL (EffectiveDate ,'1900-01-01 00:00:00.000') = CASE 
WHEN @EffectiveDate IS NULL THEN '1900-01-01 00:00:00.000'
WHEN ISDATE(@EffectiveDate) = 0 THEN '1900-01-01 00:00:00.000'
WHEN CAST(@EffectiveDate as datetime) = '1900-01-01 00:00:00.000' THEN '1900-01-01 00:00:00.000'
ELSE @EffectiveDate END

3. SET @EffectiveDate = NULLIF(@EffectiveDate, '')  before the if condition

完整的存储过程如下 -

USE [Process1]
GO
/****** Object:  StoredProcedure [dbo]. 
[sp_Insert_Update_History]    Script Date: 4/19/2021 7:55:12 PM 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Insert_Update_History] 
-- Add the parameters for the stored procedure here
    @CompanyName                VARCHAR(500),
    @Bank                       VARCHAR(100) NULL,      
    @EffectiveDate      DATETIME NULL,
    @ExpirationDate     DATETIME NULL,
    @Amount                 DECIMAL(18,2) NULL,
    @Adjustment         DECIMAL(18,2) NULL,     
    @Rec_Insert_Update_By       BIGINT,
    @Rec_Insert_Update_Flag     CHAR(1)
AS
BEGIN

BEGIN TRY
--SET @EffectiveDate = NULLIF(@EffectiveDate, '')

IF EXISTS (SELECT 1 FROM dbo.LOC_HISTORY WHERE 
                CompanyName = @CompanyName AND              
                    
                CAST(EffectiveDate as datetime) = CAST(@EffectiveDate as 
Datetime) AND 
                --ISNULL (EffectiveDate ,'1900-01-01 00:00:00.000') = CASE 
                --WHEN @EffectiveDate IS NULL THEN '1900-01-01 00:00:00.000'
                --WHEN ISDATE(@EffectiveDate) = 0 THEN '1900-01-01 
00:00:00.000'
                --WHEN CAST(@EffectiveDate as datetime) = '1900-01-01 
00:00:00.000' THEN '1900-01-01 00:00:00.000'
                --ELSE @EffectiveDate END AND

            
                CAST(ExpirationDate as datetime) = CAST(@ExpirationDate as 
datetime)
                )

    BEGIN
        UPDATE dbo.LOC_HISTORY SET
        
        CompanyName                     =  @CompanyName        
        ,Bank                       =  @Bank  
        ,EffectiveDate              =  @EffectiveDate          
        ,ExpirationDate             =  @ExpirationDate                 
        ,Amount                         =  @Amount         
        ,Adjustment                 =  @Adjustment         
        
        ,Rec_Insert_Update_Dt               =  GETDATE()                   
        ,Rec_Insert_Update_By               =  @Rec_Insert_Update_By                   
        ,Rec_Insert_Update_Flag             =  'U'

        WHERE           
        CompanyName = @CompanyName AND
                        
                EffectiveDate = @EffectiveDate AND
                ExpirationDate = @ExpirationDate        

    END
 IF NOT EXISTS (SELECT 1 FROM dbo.LOC_HISTORY WHERE 
                CompanyName = @CompanyName AND                                      
                EffectiveDate = @EffectiveDate AND
                ExpirationDate = @ExpirationDate 
                )
    BEGIN
        INSERT INTO dbo.LOC_HISTORY(
          [CompanyName]
          ,[Bank]             
          ,[EffectiveDate]
          ,[ExpirationDate]
          ,[Amount]
          ,[Adjustment]           
          ,[Rec_Insert_Update_Dt]
          ,[Rec_Insert_Update_By]
          ,[Rec_Insert_Update_Flag]
          )
        
        VALUES ( 
        @CompanyName
          ,@Bank              
          ,@EffectiveDate
          ,@ExpirationDate
          ,@Amount
          ,@Adjustment                             
        , GETDATE()                
        ,@Rec_Insert_Update_By                 
        ,'I')       

                

    END 
END TRY

BEGIN CATCH

SELECT ERROR_MESSAGE()
END CATCH
END

我错过了什么？

标签： c#.netsql-serverentity-frameworkado.net