2

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.

Joshua Frank
  • 13,120
  • 11
  • 46
  • 95

1 Answers1

2

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

Jon Wagner
  • 667
  • 4
  • 6
  • I'm happy too, because it's a great library that has saved me a ton of work. I don't think the problem is that the proc uses "both a default parameter AND NULL values", it's that the proc doesn't expect null at all. The param has a default value (the empty string ''), and has code in it like `if @paramName = ''`. Because Insight is passing NULL, this fails. I think this would be an issue any time a proc has a default value that isn't NULL, and the calling code can't simply omit the parameter--in this case, because sometimes you DO want to specify it. – Joshua Frank Nov 07 '14 at 15:07
  • Does Insight work in this way because it has no knowledge of the proc *other* than the properties, and so it has to assume you want to pass them all, because it doesn't know what params have defaults? – Joshua Frank Nov 07 '14 at 15:11
  • It knows the types and which ones have defaults, but I'm not sure that I would change the meaning of null to default. – Jon Wagner Nov 07 '14 at 21:25
  • I added issue #157 to track this: https://github.com/jonwagner/Insight.Database/issues/157 – Jon Wagner Nov 07 '14 at 21:29
  • Thanks. That issue captures it well, and I'll look into the workaround, and comment there. – Joshua Frank Nov 08 '14 at 12:29