SQLCLR .NET 错误:未将对象引用设置为对象的实例 [英] SQLCLR .NET Error: Object reference not set to an instance of an object

问题描述

我当前在尝试执行一个简单的 SELECT 语句时收到错误，该语句引用了一个程序集，该程序集包含用于 Jaro-Winkler 距离算法的 C# 代码.这是我第一次使用 SQLCLR.

I am currently receiving an error when trying to execute a simple SELECT statement that references an assembly that contains the C# code for the Jaro-Winkler distance algorithm. This is my first time working with SQLCLRs.

我能够在没有额外的OR语句

示例:

SELECT
    * 
FROM
    USERS
WHERE
(
    DATE_OF_BIRTH IS NOT NULL 
    AND DBO.JAROWINKLER(CONVERT(VARCHAR(6),DATE_OF_BIRTH,12),@DOB) > 0.9
)
OR
(
    USERID = @USERID
)

但是，当我包含 OR 语句时，我收到此错误消息:

However when I include the OR statement I receive this error message:

在执行用户定义的例程或聚合JaroWinkler"期间发生 .NET Framework 错误:

A .NET Framework error occurred during execution of user-defined routine or aggregate "JaroWinkler":

System.NullReferenceException:未将对象引用设置为对象的实例.System.NullReferenceException:在 JaroWinklerDistanceCLR.JaroWinklerDistance.proximity(String aString1, String aString2)

System.NullReferenceException: Object reference not set to an instance of an object. System.NullReferenceException: at JaroWinklerDistanceCLR.JaroWinklerDistance.proximity(String aString1, String aString2)

下面的代码可以工作并完成需要的操作.我只是想知道是否有其他方法?理想情况下，我希望它包含在一个 SELECT 语句中.我不知道上面的错误指的是什么，UserID 列中没有 NULL 值.

The code below works and does what is needed. I just wondered if there was another way? Ideally, I would like it contained to one SELECT statement. I have no idea what the error above is referring to, there are no NULL values in the UserID column.

为程序集设置的权限设置为安全.

The permission set for the assembly is set to Safe.

工作示例:

SELECT
    *
FROM
    USERS
WHERE
    DATE_OF_BIRTH IS NOT NULL 
    AND DBO.JAROWINKLER(CONVERT(VARCHAR(6),DATE_OF_BIRTH,12),@DOB) > 0.9

UNION ALL

SELECT
    *
FROM
    USERS
WHERE
    USERID = @USERID

推荐答案

另外两个答案是变通方法，而不是解决方案.并且这个变通方法必须在每个使用这个函数的地方重复，这很容易出错并且难以维护.

The other two answers are work arounds, not solutions. And this work-around will have to be duplicated in every place where this function is used, and that is very error-prone and difficult to maintain.

在进入这里的主要问题之前，有一个次要的相关问题需要先解决:输入参数类型不正确.对于 SQLCLR 方法，您应该使用 Sql* 类型而不是标准的 .NET 类型.对于这个特定的代码，这意味着使用 SqlString 而不是 String.有关为什么 SqlString 而不是 String 的更多详细信息，请参阅我对以下 S.O. 的回答.问题:我应该使用 SqlString 或字符串作为 SQLCLR UDF 的参数类型.

Before getting into the main problem here, there is a minor, related problem that should be fixed first: the input parameter types are incorrect. For SQLCLR methods, you should use the Sql* types instead of the standard .NET types. For this particular code, that means using SqlString instead of String. For more details on why SqlString instead of String, please see my answer to the following S.O. question: Should I use SqlString or string as parameter type to SQLCLR UDF's.

现在，问题是 SQLCLR 代码没有正确处理 NULLs.幸运的是，让它处理它们并不难.有两个选项，取决于 any 输入参数是否可以接受 NULL:

Now, the problem is that the SQLCLR code is not properly handling NULLs. Fortunately, it is not difficult to get it to handle them. There are two options, depending on if any of the input parameters can accept a NULL or not:

• 如果任何的输入参数可以有效地传入一个NULL，那么你需要在代码中处理它(这也适用于所有情况使用表值函数和存储过程时).然后您通过所有 Sql* 类型都具有的 .IsNull 属性签入代码.例如(假设 aString2 可以传入一个 NULL):

• If any of the input parameters can validly pass in a NULL, then you need to handle this in the code (and this also applies to all cases when working with Table-Valued Functions and Stored Procedures). And you check in the code via the .IsNull property that all of the Sql* types have. For example (assuming aString2 can pass in a NULL):

if (aString1.IsNull)
{
  return SqlDouble.Null;
}

如果 none 的输入参数可以有效地接受 NULL，那么您应该通过创建标量 UDF 绕过所有处理而不首先输入代码使用 CREATE FUNCTION 语句的 WITH RETURNS NULL ON NULL INPUT 选项.设置此选项后，如果 any 输入参数为 NULL，则跳过代码并假定返回值是 NULL.请注意，这仅适用于标量 UDF 和用户定义类型方法.

If none of the input parameters can validly accept NULL, then you should bypass all processing without entering the code in the first place by creating the Scalar UDF with the WITH RETURNS NULL ON NULL INPUT option of the CREATE FUNCTION statement. With this option set, if any input parameter is NULL, then the code is skipped and a NULL return value is assumed. Please note that this only works with Scalar UDFs and User-Defined Type methods.

有关使用 SQLCLR 的更多信息，请参阅我在 SQL Server Central 上撰写的关于此主题的系列文章(需要免费注册才能阅读该站点上的内容):通往 SQLCLR 的阶梯.

For more information on working with SQLCLR in general, please see the series of articles I am writing on this topic on SQL Server Central (free registration is required to read content on that site): Stairway to SQLCLR.

在相关说明中，我会质疑使用字符串距离函数来执行相当简单的日期计算.我认为此代码将基于将字符串 @DOB 转换为 DATE 或 用 DATEDIFF 替换 JaroWinkler 函数而受益匪浅>日期时间.

On a related note, I would question the use of a string distance function to do what is a rather simple date calculation. I would think that this code would benefit greatly from replacing the JaroWinkler function with a DATEDIFF based on converting the string @DOB to DATE or DATETIME.

这篇关于SQLCLR .NET 错误:未将对象引用设置为对象的实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！