1

Sql Server Schema that I am working with has sql_variant DataType as a part of Table Type Parameter Column. Also there's non TVP parameter which is sql_variant. For the TVP parameter, I have used the option provided in the following link:

https://stackoverflow.com/a/52736573/1559611

It have used List<SqlDataRecord>, which can be added to the DynamicParameters, since AsTableValuedParameter() has an extension method for IEnumerable<SqlDataRecord>.

Before trying the above option I have tried mapping an object and string type to sql_variant for a TVP column, but that leads to the exception.

Now I am trying to figure out:

  • How to map a non TVP parameter for Dapper, since I don't see an option similar to SqlDataRecord, would a simple object type work in that case ?
  • When return collection contains column, which is of variant type, will it map to an object type ?

My only option, if Dapper doesn't support is to switch back to SqlClient, which does have an explicit support for Sql_Variant data type, which doesn't seems to be case for Dapper as it use DbType, which has no explicit Variant data type

Any help or pointer on these cases ?

Mrinal Kamboj
  • 11,300
  • 5
  • 40
  • 74
  • `DbType.Object` seems like it ought to be the equivalent of `SqlDbType.Variant` but I've not tried to use it myself (since the quirks and limitations of the type means I avoid it. TBH, I thought it had been deprecated but it would appear not) – Damien_The_Unbeliever Oct 16 '18 at 14:17
  • @Damien_The_Unbeliever No that doesn't help, leads to exception, nothing which maps to `SqlDbType.Variant`, cursing the database developer using the pointless variant type – Mrinal Kamboj Oct 16 '18 at 16:12
  • According to [SQL Server Data Type Mappings](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings), `Sql_variant` maps to `SqlDbType.Variant` and `DbType.Object` - However I must Agree with Damien on this - `sql_variant` is something I avoid using. In fact, in over a decade of working with sql server, I don't remember using that data type even once. – Zohar Peled Oct 18 '18 at 08:44
  • 1
    Can dapper map to the supported sql_variant data type? – jwrightmail Jan 25 '19 at 18:05
  • It does except a specific case where Sql Variant is part of TVP, there it doesn't and needs ADO.NET – Mrinal Kamboj Jan 26 '19 at 02:53

0 Answers0