How do I specify the User-Defined Table Type for a property with Insight Database

Question

I have a pretty simple stored procedure to just update the status value for a bunch of records. I send in an unknown value of record IDs and it works.

I love using Insight.Database and wouldn't want to use anything else if possible.

The problem is our DBA's created multiple User-Defined Table Types to handle situations. But their naming conventions are identical.

We have a [IntTable] with column [IntValue] and another [TinyIntTable] with column [TinyIntValue]

Insight appears to inspect the type of UDT that could work. And sometimes it chooses [TinyIntTable] (I am guessing because the values in the array being sent are all small enough to fit into a tinyInt. But that [TinyIntTable] isn't compatible with the stored procedure. How do I force Insight.Database to always use the [IntTable]?

Is there an attribute I could use on my c# object definition?

Answer 1

If you’re using stored procedures, then insight will check the type of the parameter and use the correct one. i.e. it should just work.

So your problem is likely something else. Check out the table type section on the wiki, and if that doesn’t solve it for you, please post a simple example on github and we’ll help you out.

Answer 2

What do you mean by, and how have you determined that Inisght chooses the TinyIntTable?

In my experience, as long as the parameter and values you supply in your code is compatible with the parameter in your stored procedure, you will be fine.

I.E. if you cast a short[] into an int[] and then passed the int array into a stored procedure that accepted smallints you should be fine.

Is there any chance your stored procedure has the wrong type for its parameter?