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!