使用DataTable作为存储过程参数插入数据时遇EventDateTime非空错误

使用C#将DataTable作为SQL Server存储过程参数插入数据时，触发异常：

Cannot insert the value NULL into column 'EventDateTime', table ABC column does not allow nulls. INSERT fails.
The statement has been terminated.

以下是相关代码：

SQL 表、自定义表类型及存储过程代码 #

create table ABC(
[S] nvarchar(50) Not NULL,
[Type] nvarchar(50) Not NULL,
[R] nVarchar(50) Not NULL,
[C] int Not NULL,
[EventDateTime] datetime NOT NULL,
CONSTRAINT [pk_chillerstatus] 
PRIMARY KEY CLUSTERED ([S] ASC, [Type] ASC, [R] ASC, [EventDateTime])
);


CREATE TYPE [dbo].[ABCType] AS TABLE (
[S] nvarchar(50),
[Type] nvarchar(50),
[R] nVarchar(50),
[C] int 
,[EventDateTime] datetime
);

create procedure [dbo].[spInsertABC]
(
@abc [dbo].[ABCType] READONLY
)
as
begin

INSERT INTO ABC(S, Type,R,C
,EventDateTime
) 
     SELECT p.S, p.Type,p.R,p.C
     ,p.EventDateTime
     FROM @abc p;
end;

C# 生成DataTable代码 #

public DataTable GenerateDatatable(DateTime eventDateTime, string s)
 {
     DataTable dt = new DataTable();
     dt.Columns.Add("S", typeof(string));
     dt.Columns.Add("Type", typeof(string));
     dt.Columns.Add("R", typeof(string));
     dt.Columns.Add("C", typeof(int));
     dt.Columns.Add("EventDateTime", typeof(DateTime));
     
    dt.Rows.Add(s, "E", "A", 0, eventDateTime);
    dt.Rows.Add(s, "E", "M", 2, eventDateTime);
    dt.Rows.Add(s, "E", "N", 3, eventDateTime);
    dt.Rows.Add(s, "E", "S", 4, eventDateTime);
    dt.Rows.Add(s, "E", "No", 5, eventDateTime);
  
    return dt;
 }

C# 调用存储过程代码 #

public async Task AddABC()
 {
     try
     {
         DateTime eventDatetime = DateTime.Now;

         DataTable rdt = GenerateDatatable(eventDatetime, "R");

         using (SqlConnection con = new SqlConnection(devSqlConnectionString))
         {
             using (var com = new SqlCommand("[dbo].[spInsertABC]", con))
             {
                 con.Open();
                 com.CommandType = CommandType.StoredProcedure;
                 com.CommandTimeout = 500;
                 SqlParameter sqlparam = new SqlParameter("@abc", rdt);
                 sqlparam.SqlDbType = SqlDbType.Structured;
                 sqlparam.TypeName = "dbo.ABCType";
                 com.Parameters.Add(sqlparam);
                 com.ExecuteNonQuery();
             }
         }
     }
     catch (Exception ex)
     {
         throw ex;
    }
 }

1. 同步自定义表类型与目标表的列约束 #

当前自定义表类型ABCType的列未设置NOT NULL约束，而目标表ABC的EventDateTime列不允许为空。修改ABCType，让其列约束与ABC表保持一致：

DROP TYPE IF EXISTS [dbo].[ABCType];
CREATE TYPE [dbo].[ABCType] AS TABLE (
[S] nvarchar(50) NOT NULL,
[Type] nvarchar(50) NOT NULL,
[R] nVarchar(50) NOT NULL,
[C] int NOT NULL,
[EventDateTime] datetime NOT NULL
);

这样可以确保传入的表参数中不会出现NULL值，从源头避免插入失败。

2. 强制DataTable列不允许为空 #

在C#生成DataTable时，设置EventDateTime列的AllowDBNull为false，确保添加行时必须赋值：

dt.Columns.Add("EventDateTime", typeof(DateTime)).AllowDBNull = false;

如果后续代码中出现未赋值的情况，会在C#层面直接报错，而不是传到SQL Server触发异常。

3. 验证DataTable中的数据 #

在调用存储过程前，添加代码验证DataTable的每一行EventDateTime是否有值，排查数据是否在生成过程中丢失：

foreach(DataRow row in rdt.Rows)
{
    if(row.IsNull("EventDateTime"))
    {
        throw new InvalidOperationException("EventDateTime字段不能为空");
    }
}

4. 存储过程中增加NULL过滤（临时方案） #

如果暂时无法修改表类型，可以在存储过程的插入语句中过滤掉EventDateTime为NULL的行，避免插入失败：

INSERT INTO ABC(S, Type,R,C,EventDateTime) 
SELECT p.S, p.Type,p.R,p.C,p.EventDateTime
FROM @abc p
WHERE p.EventDateTime IS NOT NULL;

内容的提问来源于stack exchange，提问作者prasanthi