I'm working on an application that accepts user input and in some cases feeds that input directly into a SQL Server Stored Procedure (via parameterised SqlCommand) and in other cases runs that input through a series of transformations prior to executing the SP.
I'm using SqlCommandBuilder.DeriveParameters to get the list of parameters from the DB and then assigning those parameters' values as strings.
So far I haven't found a way to validate that the string can be parsed into the appropriate SqlDbType before executing the SP. I was hoping for something like
parameter.SqlDbType.TryParse(inputValue);
but such a method doesn't seem to exist as SqlDBType is just an enum. So far my only option is executing the SP and catching any errors that are thrown, but this doesn't seem to tell me which parameter is causing the problem, so my user is not amused.
I know I could use a switch statement on the SqlDbType and validate the input myself but this seems like a bad idea.
I have tried searching SO for this issue but the related questions all seem to revolve around parsing a string to an SqlDbType enum member, not to the actual type. The closest I found was this but the only answer talks about checking it a System.Type is compatible with any SqlDbType values.