0

I have the following code:

                Dim oPNParam As IDbDataParameter = oCmd.CreateParameter()
                oPNParam.ParameterName = "@PartNumber"
                oPNParam.DbType = DbType.String
                oPNParam.Value = PartID
                oCmd.Parameters.Add(oPNParam)

This code ends up executing sql side where the parameter type is Nchar(7). I checked out the documentation on MSDN and found a bit about how char isn't suported as the DbType, which I can confirm is not part of System.Data.DbType. My sql admins inform me that this statement executes poorly as a conversion of the partNumber field from the DB from char(7) to nchar(7) occurs, resulting in indexes not being utilized. He claims this is because of the type associated with the parameter I define in the code above.

The question here is how do I modify this code to explicitly tell the parameter it should be type char(7)? I found the StringFixedLength types and such but am really struggling to find any meaningful info on how to accomplish this.

One would think it'd be something like:

                Dim oPNParam As IDbDataParameter = oCmd.CreateParameter()
                oPNParam.ParameterName = "@PartNumber"
                oPNParam.DbType = DbType.StringFixedLength
                oPNParam.Size = 7
                oPNParam.Value = PartID
                oCmd.Parameters.Add(oPNParam)

But I'm looking for confirmation that this would, or a link to documentation which I seem unable to find.

Mark W
  • 2,791
  • 1
  • 21
  • 44
  • 3
    Using `DbType.AnsiStringFixedLength` should do it, though I'd personally prefer using `SqlParameter` explicitly; its `SqlDbType` property offers `Char`. – Jeroen Mostert Feb 23 '21 at 18:07
  • @JeroenMostert Does that mean NChar is Unicode and Char is non unicode ansi? – Mark W Feb 23 '21 at 18:12
  • 1
    That is exactly what it means, although the term "ANSI" is a bad, archaic, deprecated thing that the designers ought to not have used at all. But that's another discussion. Interpreting it as "not Unicode" suffices here. :-P – Jeroen Mostert Feb 23 '21 at 18:14
  • Much appreciated, if you'd add this as an answer I'll accept. Seems the quick check I did supports the nChar vs char bit, so I'm confident this will work. – Mark W Feb 23 '21 at 18:16
  • 2
    By the way, my favourite shorthand syntax for this is `sqlCommand.Parameters.Add("@PartNumber", SqlDbType.Char, 7).Value = PartID` I also have an extension method `.AddVarChar(...` that does the same – Charlieface Feb 23 '21 at 22:01

0 Answers0