What's the right/best way to handle nullable parameters in Insight.Database?

Question

I've been using Insight.Database for about a year to invoke stored procedures, but I've only just run into a situation where the stored procedure has a defaultable nullable parameter that Insight doesn't handle as I was expecting.

Briefly, the parameter is defined as paramName nvarchar(10) = ''. The code to invoke this proc was old fashioned SqlCommand/SqlParam code that simply did not set the param at all. I was replacing this with a .net object:

Class MyObject
    Public Property paramName As String
    ....
End Class

Suppose you create an object of this type, do NOT set paramName and pass it to the proc. I would have expected that the proc would use the default value. But what happens instead is that Insight sends a NULL value to the proc, which is NOT the same as not sending anything, the proc sees a specified parameter and so doesn't use the default value, and this particular proc actually handled NULL is a different way from the default, which broke the app.

I would argue that the bug is in the proc, but I still need a way to control nullable parameter behavior so I can call procs of this kind that I can't modify.

Answer 1

I'm happy that you've had a year of success with Insight.Database.

When Insight binds an object to a parameter list, it maps any properties that match.

So:

class Foo { string Param; }
CREATE PROC MyProc (@Param varchar(50))

Foo.Param maps to @Param

In MOST simple cases, coders want NULL to map to DBNull.Value. So if Param is NULL, Insight will bind the parameter and set @Param to (DB)NULL.

When you use ADO.NET to call a stored proc with a default parameter, you usually omit the parameter or don't set the value. ADO.NET then tells the server to use the default.

CREATE PROC MyProc (@Param varchar(50) = NULL)

You can do this with Insight by omitting the parameter entirely:

class Foo { /* Param not bound */ }

Insight then leaves the value unbound and ADO.NET will use the default.

I haven't run across a case where you have to use both a default parameter AND NULL values on the same proc.

So the simplest solution is to omit the parameter, but if that doesn't work, I can probably add a feature that lets you control what NULL means. Like:

class Foo { [NullMeansUseDefault] string Param; }

Of course, I'd want a better name for the attribute.

If you think the feature would solve your problem, please open an issue over at https://github.com/jonwagner/Insight.Database/issues