如何将可为Null的int转换为SQL Null值

本文关键字：Null 转换 SQL int | 更新日期: 2023-09-27 18:00:35

我需要使用DataContext的ExecuteCommand方法对具有多个底层表的View执行更新。我之所以使用这种方法，是因为在对具有多个底层表的视图执行这种类型的操作时，linqToSQL有已知的限制。

我现有的SQL语句类似于下面的语句，我将newFieldID设置为null值，只是为了说明这个问题。在应用程序中，newFieldID被分配了一个传递的参数，实际上可能是一个整数值；但我的问题是特定于提供的值为null类型的情况：

using (var _ctx = new MyDataContext())
{
   int? newFieldID = null;
   var updateCmd = "UPDATE [SomeTable] SET fieldID = " + newFieldID + " 
   WHERE keyID = " + someKeyID;
   try
   {
      _ctx.ExecuteCommand(updateCmd);
      _ctx.SubmitChanges();
   }
   catch (Exception exc)
   {
      // Handle the error...
   }
}

这段代码将失败，原因很明显，在newFieldID为null的情况下，updateCmd将不会完成。那么，如何用SQL null替换或转换CLR null值来完成语句呢？

我知道我可以将所有这些转移到存储过程中，但我正在寻找现有场景的答案。我尝试过使用DBNull进行实验。值，但除了将其替换为语句中使用的newFieldID这一挑战之外，简单地将其放入字符串中就会破坏语句的有效性。

此外，将其括在单引号内：

var updateCmd = "UPDATE [SomeTable] SET fieldID = '" + DBNull.Value + "'
   WHERE keyID = " + someKeyID;

将完成该语句，但字段的值被转换为整数0，而不是SQL null。

那么，在这种情况下，如何将CLR null或可为null的int转换为SQL null值呢？

如何将可为Null的int转换为SQL Null值

正确的方法：使用ExecuteCommand的覆盖，不仅接受命令文本，还接受参数数组，并使用参数化查询而不是命令字符串串联：

var updateCmd = "UPDATE [SomeTable] SET fieldID = {0} WHERE keyID = {1}";
_ctx.ExecuteCommand(updateCmd, new [] {newFieldID, someKeyID});

它不仅会阻止您进行sql注入，而且还会为您执行以下操作（来自MSDN描述）：

如果其中任何一个参数为null，则会将其转换为DBNull。价值

尝试检查newFieldID==null并相应地更改语句。类似于下面的内容或使用单独的if/else语句。

var updateCmd = "UPDATE [SomeTable] SET fieldID =" + (newFieldID == null ? "null" : Convert.ToString(newFieldID)) + " WHERE keyID = " + someKeyID;

通常，在使用"存储过程"或"准备语句"时，使用"参数"来赋值。如果有DbParameter，则可以将null或DBNull.Value指定给Value属性或参数。

如果您想在语句中使用null作为文本，只需使用SQL关键字NULL

var updateCmd = "UPDATE [SomeTable] SET fieldID = NULL WHERE keyID = " + someKeyID;

正如Andy Korneyev等人所指出的，在使用Prepared语句时，参数化数组方法是最好的，可能也是更合适的方法。由于我使用的是LinqToSQL，因此建议使用带有第二个参数的ExecuteCommand方法，该方法采用参数数组，但它的用法有以下注意事项。

  1. 查询参数的类型不能为System。可为Null的`1[System.Int32][]（本例中我试图解决的主要问题）
  2. 所有参数必须为相同类型

Shankar的答案是有效的，尽管它可能很快变得非常冗长，因为参数的数量可能会增加。

因此，我解决这个问题的方法在某种程度上涉及Andy建议的参数使用和Shankar的建议之间的混合，通过创建一个助手方法来处理空值，该方法将使用带有参数映射的SQL语句和实际参数。

我的助手方法是：

private static string PrepareExecuteCommand (string sqlParameterizedCommand, object [] parameterArray) 
    {
        int arrayIndex = 0;
        foreach (var obj in parameterArray)
        {
            if (obj == null || Convert.IsDBNull(obj)) 
            {
                sqlParameterizedCommand = sqlParameterizedCommand.Replace("{" + arrayIndex + "}", "NULL"); 
            }
            else
                sqlParameterizedCommand = sqlParameterizedCommand.Replace("{" + arrayIndex + "}", obj.ToString());
            ++arrayIndex;
        }
        return sqlParameterizedCommand;
    }

因此，我现在可以使用具有潜在空值的参数来执行我的语句，如下所示：

int? newFieldID = null;
int someKeyID = 12;
var paramCmd = "UPDATE [SomeTable] SET fieldID = {0} WHERE keyID = {1}";
var newCmd = PrepareExecuteCommand(paramCmd, new object[] { newFieldID });
_ctx.ExecuteCommand(newCmd);
_ctx.SubmitChanges();

这减轻了之前引用的带有参数数组的ExecuteCommand的两个限制。空值会得到适当的转换，并且对象数组可能会发生变化。NET类型。

我将Shankar的提议视为答案，因为它引发了这个想法，但我发布了我的解决方案，因为我认为它增加了更多的灵活性。

相关文章：