DOTNET Boolean T-SQL variable/parameter assign

[dgholstein]

I'm using DOTNET System.Data to assign T-SQL variables/parameters, it works for numerics and strings, but not for Boolean (I keep getting NULLs).

Here's the diagram working values for singles:

And here's the disfunctional diagram for boolean:

Note the Anything to .NET VI on the diagram also yields NULLs

From MSDN documentation, DotNET Boolean should be the compatible type to SQL Server "Bit".

NOTE:  To run the code, you'll need a custom DotNET DLL I wrote, I'm willing to zip it all up but hoping that won't be necessary (and will need a SQL Server sandbox)

 

[tyk007]

I've use the ADO.NET API before in LabVIEW without any problems. Without your code it won't be possible to be sure, but here are some ideas:

• Is Variant to Data returning an error ie. does the variant actually contain a single Boolean data value? 
• What is the cast for? You don't need it and we can't see what you are casting to (I assume System.Object).
• You also don't need to create the Sql data type instance in this case (nor for the Single) - just use "To .NET Object" and pass the .NET equivalent type to the Value property directly. Note it isn't "anything to .NET" - the VI description clearly indicates what types are supported and numeric and booleans both are. The VI returns a null reference (along with an error) if the incoming variant doesn't contain a compatible type (or is empty)
• If you want to support Nulls as part of your application (eg. your variant is empty) then you should detect that and pass in the return from the static DBNull.Value property.

[dgholstein]

• Is Variant to Data returning an error ie. does the variant actually contain a single Boolean data value? 
  • No error, and yes, the variant contains a non-Null boolean
• What is the cast for? You don't need it and we can't see what you are casting to (I assume System.Object).
  • The cast from variant is necessary since I'd been working with the System.Data.SqlTypes, which don't take input from variants.
• You also don't need to create the Sql data type instance in this case (nor for the Single) - just use "To .NET Object" and pass the .NET equivalent type to the Value property directly. Note it isn't "anything to .NET" - the VI description clearly indicates what types are supported and numeric and booleans both are. The VI returns a null reference (along with an error) if the incoming variant doesn't contain a compatible type (or is empty)
  • You are correct, the results are the same for either implementation.  It does look cleaner with  "To .NET Object"
• If you want to support Nulls as part of your application (eg. your variant is empty) then you should detect that and pass in the return from the static DBNull.Value property.
  • Actually, I don't need to support Nulls, yet.  That's the problem, I'm getting Nulls when I should have a boolean

[dgholstein]

If the DLL file is executable (wasn't stripped from the ZIP and compatible with your DotNET, 4.8), kudos to your stick-to-it-iveness. I will pray to the gods of programming that you have the right versions of SQL Server, LabVIEW, DOTNET, etcetera.

[tyk007]

---

@dgholstein wrote:

• Is Variant to Data returning an error ie. does the variant actually contain a single Boolean data value? 
  • No error, and yes, the variant contains a non-Null boolean
• What is the cast for? You don't need it and we can't see what you are casting to (I assume System.Object).
  • The cast from variant is necessary since I'd been working with the System.Data.SqlTypes, which don't take input from variants.
• You also don't need to create the Sql data type instance in this case (nor for the Single) - just use "To .NET Object" and pass the .NET equivalent type to the Value property directly. Note it isn't "anything to .NET" - the VI description clearly indicates what types are supported and numeric and booleans both are. The VI returns a null reference (along with an error) if the incoming variant doesn't contain a compatible type (or is empty)
  • You are correct, the results are the same for either implementation.  It does look cleaner with  "To .NET Object"
• If you want to support Nulls as part of your application (eg. your variant is empty) then you should detect that and pass in the return from the static DBNull.Value property.
  • Actually, I don't need to support Nulls, yet.  That's the problem, I'm getting Nulls when I should have a boolean

---

Just a couple of points:

• By cast I'm referring to the "to more generic class" node. But you of course don't need that if you use the .NET "value" types directly.
• From what you are saying, the "To .NET Object" in your case isn't returning the appropriate .NET type. Can you show us the probes for the variant, the LabVIEW Boolean from that, and the outputs of the "To .NET Object" when passed in that LabVIEW Boolean? 

EDIT: Here is an example of what I mean:

[dgholstein]

Well, I'll be a monkey's uncle!  Here's the probe, it appears the assignment was good:

DOH!!! Dopey me, my query was wrong, I hadn't added the extra field 😳