C# &SQL Server:如果字符串值为空,如何在命令参数中插入 DBNull.Value? [英] C# & SQL Server : how to Insert DBNull.Value in command parameter if string value is empty?
问题描述
我已经搜索了几个小时,但找不到解决方案.
I've been searching for a couple of hours now and cannot find a solution.
我将一些字符串插入到 SQL 中,但有时,我用来执行此操作的方法可能包含空字符串,即 ""
I am inserting some strings into SQL, however on on occasions, the method I use to do this may contain strings that are empty, i.e ""
因此,我想在 SQL Server 中插入一个空值.
I therefore want to insert a null value into SQL Server instead.
首先,我测试我的方法以确保我能够使用以下内容手动插入 DBNull.Value
:
Firstly I test my method to make sure I am able to insert a DBNull.Value
manually by using the following:
cmd.Parameters.AddWithValue("@surname", DBNull.Value); // THIS WORKS OK
然而,当我尝试以下操作时,该方法运行时没有任何错误异常,但 SQL Server 列不显示空值,它只是空的,没有任何内容:
HOWEVER when I try the following below, the method runs without any error exception BUT the SQL Server column does not show a null value, it's just empty with nothing in it:
cmd.Parameters.AddWithValue("@surname", (object)surname ?? DBNull.Value);
我只想要一个有效的解决方案,而不需要处理任何更常见的类扩展和编写大量额外的代码.我在论坛上看到的每一个答案都太复杂了,这本该是一个简单的任务.
I just want an efficient solution without messing about with any more common class extensions and writing lots of extra code. Every answer I see on the forums is just over complicated for what should be such a simple task.
有什么想法吗?提前致谢...
Any ideas? Thanks in advance...
这里是完整的方法:
public static void AddUserToUserTable(string username,
string forename,
string surname,
string emailAddress,
string password,
string accountActive,
string userGroup)
{
string cmdText1 = "INSERT INTO Users (Username, Forename, Surname, EmailAddress, Password, AccountActive, UserGroup) VALUES (@username, @forename, @surname, @emailAddress, @password, @userGroup, @accountActive)";
try
{
// The using statement will take care of the disposing of the reader and the command object.
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(cmdText1, connection);
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@forename", forename);
cmd.Parameters.AddWithValue("@surname", surname, null);
cmd.Parameters.AddWithValue("@emailAddress", emailAddress);
cmd.Parameters.AddWithValue("@password", password);
cmd.Parameters.AddWithValue("@userGroup", accountActive);
cmd.Parameters.AddWithValue("@accountActive", userGroup);
connection.Open();
cmd.ExecuteNonQuery();
log.Info("NEW User [" + username + "] Created");
}
}
catch (Exception ex)
{
log.Error(ex);
}
}
推荐答案
1) 最简单的方案大家都已经提过:
1) The simplest solution everyone has already mentioned:
cmd.Parameters.AddWithValue("@surname",
String.IsNullOrEmpty(surname) ? DBNull.Value : surname);
<小时>
2) 如果你可以修改数据库本身,你可以添加一个触发器来在 INSERT
和 上用
操作.如果有其他开发人员和/或应用程序更改数据库,这具有确保一致性的优势.NULL
替换空字符串>UPDATE
2) If you can modify the database itself, you could add a trigger to replace empty strings with NULL
on INSERT
and UPDATE
operations. This has an advantage of ensuring consistency if there are other developers and/or applications altering the database..
CREATE TRIGGER User_ReplaceEmptyWithNull
ON
Users
AFTER
INSERT,
UPDATE
AS
UPDATE
Users
SET
Users.Forename = IIF(inserted.Forename != '', inserted.Forename, NULL),
Users.Surname = IIF(inserted.Surname != '', inserted.Surname, NULL)
FROM
inserted INNER JOIN Users
ON inserted.Username = Users.Username
免责声明:我不是数据库触发器方面的专家.我根据另一个SO问题的答案
3) 您可以为 String
对象创建一个扩展方法.
3) You could make an extension method for String
objects.
namespace YOUR_NAMESPACE
{
public static class MyExtensions
{
public static object OrDBNull( this String value )
{
return String.IsNullOrEmpty(value) ? DBNull.Value : value;
}
}
}
...
cmd.Parameters.AddWithValue("@surname", surname.OrDBNull());
<小时>
4) 您可以为 SqlParameterCollection
对象创建一个扩展方法.
4) You could make an extension method for SqlParameterCollection
objects.
namespace YOUR_NAMESPACE
{
public static class MyExtensions
{
public static void AddString( this SqlParameterCollection collection, string parameterName, string value )
{
collection.AddWithValue(parameterName, String.IsNullOrEmpty(value) ? DBNull.Value : value);
}
}
}
...
cmd.Parameters.AddString("@surname", surname);
免责声明:未经测试.我可能在某个地方搞砸了.
这篇关于C# &SQL Server:如果字符串值为空,如何在命令参数中插入 DBNull.Value?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!