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.
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