如何使用Dapper将字符串作为NULL发送到SQLServer？ [英] How can I send a string as NULL to SQLServer using Dapper?

问题描述

我有一种方案，其中C＃中的字符串可以为 null 。我需要它在SQLServer上为 NULL 。

I've got a scenario where a string in C# can be null. I need it to be NULL on SQLServer.

我正在使用Dapper通过查询将其发送到SQLServer：

I'm sending it to SQLServer using Dapper with a query like:

connection.Query<MyObject>("[dbo].[sp_MyStoredProcedure]"), new
{
    StartDate: startDate
}, commandType: CommandType.StoredProcedure);

其中 startDate 是有时可以等于 null 。

存储过程的参数为

@StartDate varchar(10) = NULL

当它是 NULL 它返回所有记录。我已经通过SSMS确认了这种行为。

When it's is NULL it returns all records. I've confirmed this behavior works via SSMS.

我阅读了此帖子，作者Marc Gravell指出：

I read this post by Marc Gravell that states:

null vs DBNull问题一直是导致混乱;但是，通常如果人们在C＃中说 null ，他们打算在SQL中说 null 。这就是dapper所采用的方法。

The null vs DBNull issue is a constant cause of confusion; however, generally if people say null in C# they intend null in SQL. This is the approach that dapper adopts.

这使我相信，当 string 是设置为 null ，它应该将 DBNull.Value 发送到SQLServer。

This leads me to believe that when the string is set to null, it should send DBNull.Value to SQLServer.

但是，事实并非如此。发送 null 字符串时，我从SQLServer中获取了0条记录。这似乎表明发送空字符串，而不是发送 DBNull.Value 。

However, this doesn't appear to be the case. I get back 0 records from SQLServer when sending a null string. This seems indicative of sending an empty string, rather than a DBNull.Value.

此外，我无法发送 DBNull.Value 直接：

Also, I can't send DBNull.Value directly:

connection.Query<MyObject>("[dbo].[sp_MyStoredProcedure]"), new
{
    StartDate: DBNull.Value
}, commandType: CommandType.StoredProcedure);

这会在Dapper中产生异常：

This produces an exception within Dapper:

类型System.DBNull的成员StartDate不能用作参数值

The member StartDate of type System.DBNull cannot be used as a parameter value

问题

如何发送当我在C＃中有一个 string 可以为 null时，使用Dapper将 NULL 转换为SQLServer ？

Question

How can I send NULL to SQLServer, using Dapper, when I have a string in C# that can be null?

Dapper确实发送了 NULL 当字符串为 null 时。基于错误的信息，这一假设对我而言是错误的。尽管如此，这个问题还是可以帮助做出同样错误假设的其他人。

Dapper does indeed send NULL when a string is null. This assumption was a mistake on my part based on faulty information. Nonetheless, this question may serve to help someone else who makes an equally faulty assumption.

此外，被接受的答案为处理可选或条件参数提供了一个很好的机制。

Furthermore, the accepted answer provides a good mechanism for dealing with optional or conditional parameters.

推荐答案

您可以选择不发送StartDate。

You can choose not to send StartDate.

示例：

dynamic parameters = new {

};

if (!string.IsNullOrWhiteSpace(startDate)) 
{
   parameters.StartDate = startDate;
}

connection.Query<MyObject>("[dbo].[sp_MyStoredProcedure]"), parameters, commandType: CommandType.StoredProcedure);

编辑：

此外，您存储的过程必须接受空值。这是一个例子：

Also, your stored procedure must accept nulls. Here's an example:

CREATE PROCEDURE [ExampleProc]
   @StartDate datetime = null
AS
   Select @StartDate

这篇关于如何使用Dapper将字符串作为NULL发送到SQLServer？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！