Understanding SqlMapper.ICustomQueryParameter

Question

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(
    "MyProc",
    new
    {
        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.

Answer 1

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!

Answer 2

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.