What's the point of DataTable.AsTableValuedParameter method which returns a SqlMapper.ICustomQueryParameter when having to pass a datatable as TVP to DB using Dapper?

I could send TVPs to DB just as normal datatable and execute queries just fine. I am not sure what performing AsTableValuedParameter on it buys additionally.

For e.g. this works:

int rowsAffected = await conn.ExecuteAsync(
        myVar = (DataTable)GetThatDataTable(),
    commandType: CommandType.StoredProcedure);

Also, an additional question, what is the need for typeName optional parameter in AsTableValuedParameter method? Works fine without it too.

  • 1
    I might be really obtuse, but what is c? You're showing a method executed on an object but it's not clear (at least to me) what that object is. – Scott Hannen Feb 14 '18 at 15:10
  • 1
    Sometimes people like myself explore and learn something we don't already know so that we can answer questions. We learn something new and help other people at the same time. The question should really specify on what class the method is called. – Scott Hannen Feb 14 '18 at 15:23
  • @ScottHannen oops I am sorry. It is a connection object, some IDbConnection. ExecuteAsync is an extension method on IDbConnection from Dapper's side. – nawfal Feb 14 '18 at 15:33

It works fine in your case because you are using a stored procedure; the user-defined type needs to come from somewhere, and in the case of stored procedures: that somewhere can be the declared parameter type.

However, dapper also works with ad-hoc CommandText queries. In that case, without this call: there is no type name, and the call will fail. It is important to me that features support CommandText usage too, since that is a very common use-case.

If it works fine for you without: do that!

  • Fair enough. So is the point of AsTableValuedParameter just to give a type name? In other words, what would be the difference between passing a System.Data.DataTable and ICustomQueryParameter implementation ? Looking at the source I couldn't find a use for latter (looks like a wrapper around DataTable). – nawfal Feb 15 '18 at 20:23
  • @newfal the only extra thing it needs to work is: a UDF type name... so: yes? – Marc Gravell Feb 15 '18 at 22:33

Passing a SQL Server table valued parameter requires a type name, so it's not optional. It has to come from somewhere.

SqlMapper has an extension method that sets the type name for a DataTable in its ExtendedProperties. If you don't specify the type name in the AsTableValuedParameter method, it checks ExtendedProperties for the type name.

So the type name is still getting specified, just not in the method where you're looking. It's probably happening in the method that creates and returns the DataTable. Look for use of public static void SetTypeName(this DataTable table, string typeName).

SqlMapper source, line 3672.

That's convenient because if a given DataTable type exists only to be used to pass as a table valued parameter then it makes sense that you should be able to associate it with the type of that parameter. That makes it easier to identify which schema the DataTable must conform to.

  • Passing a SQL Server table valued parameter requires a type name, so it's not optional. Not sure. One, even if I dont specify a type name the DB operation works. It doesn't if I pass a wrong Type name. It should be either empty (or null) or match the exact type. Two, even if I pass a DataTable (not icustomqueryparameter) with no type name anywhere executions passes. – nawfal Feb 15 '18 at 12:21
  • Not sure how reliable this answer is, but the poster says TypeName is required only for parameterized sql, not TVPs. – nawfal Feb 15 '18 at 12:31

Your Answer

