The two methods below are similar, except that one processes null values and the other does not. To handle null values, it uses the SqlString type and checks the get_IsNull property.
Why can the first cause an error "A .NET Framework error occurred during execution of user-defined routine or aggregate "CheckMailingAddress": ."when running inside the SQL CLR, and the second can not?
In particular, the TSQL error "Msg 10329, Level 16, State 49, Line 1 .Net Framework execution was aborted."
.method public hidebysig static bool CheckMailingAddress(valuetype [System.Data]System.Data.SqlTypes.SqlString param0) cil managed
{
.maxstack 8
L_0000: ldarga.s param0
L_0002: nop
L_0003: nop
L_0004: call instance bool [System.Data]System.Data.SqlTypes.SqlString::get_IsNull()
L_0009: brfalse L_0010
L_000e: ldc.i4.1
L_000f: ret
L_0010: ldarga.s param0
L_0012: nop
L_0013: nop
L_0014: call instance string [System.Data]System.Data.SqlTypes.SqlString::get_Value()
L_0019: call class DatabaseValues.MailingAddress DatabaseValues.MailingAddress::op_Explicit(string)
L_001e: pop
L_001f: ldc.i4.1
L_0020: ret
}
.method public hidebysig static bool CheckMailingAddress(string param0) cil managed
{
.maxstack 8
L_0000: ldarg.0
L_0001: call class DatabaseValues.CheckMailingAddress DatabaseValues.CheckMailingAddress::op_Explicit(string)
L_0006: pop
L_0007: ldc.i4.1
L_0008: ret
}
Keep in mind that MSIL is correct, as far as I know, because both of these methods work when called in a standalone application. It is only when called inside the SQL CLR that the first of two crashes. In the SQL CLR, a function is defined with the type "nvarchar (4000)", which, as far as I know, should work well with SqlString.
, , , "string", , SqlString, INULable "IsNull" "Value", , Sql *.
:
, MSIL ; . , , , "SqlString", "string", , CLR , NULL, TRUE.
.method public hidebysig static bool CheckMailingAddress(valuetype [System.Data]System.Data.SqlTypes.SqlString param0) cil managed
{
.maxstack 8
L_001f: ldc.i4.1
L_0020: ret
}
.method public hidebysig static bool CheckMailingAddress(string param0) cil managed
{
.maxstack 8
L_0007: ldc.i4.1
L_0008: ret
}