How to save whole DataTable using dapper?

Question

I have a DataTable object filled with data, and want to save it to SQL-DB (I don't want to run a foreach loop to do the insertion), I code like this:

var dt = new DataTable();
//
// ... data loading to table dt
//

string sql = @"
INSERT INTO TB_Book (
  BookID,
  BookName
) SELECT
  BookID,
  BookName
FROM 
@DataTable";

conn.execute(sql, new { DataTable = dt.AsTableValuedParameter("DataTable") });

When executed, it throws the following exception:

Column, parameter, or variable @DataTable. : Cannot find data type DataTable

How can I improve my code to make this work?

Answer 1

I have not worked with the AsTableValuedParameter yet, but i do use the Unit tests written at dappers GIT Repository to teach myself anything dapper. When i went there to investigate your question i located the following:

https://github.com/StackExchange/dapper-dot-net/blob/61e965eed900355e0dbd27771d6469248d798293/Dapper.Tests/Tests.Parameters.cs#L251

It looks like you will want to use the AsTableValuedParameter() without the "DataTable" type provided.

Here is the example from StackExchange:

[Fact]
public void DataTableParameters()
{
    try { connection.Execute("drop proc #DataTableParameters"); }
    catch { }
    try { connection.Execute("drop table #DataTableParameters"); }
    catch { }
    try { connection.Execute("drop type MyTVPType"); }
    catch { }
    connection.Execute("create type MyTVPType as table (id int)");
    connection.Execute("create proc #DataTableParameters @ids MyTVPType readonly as select count(1) from @ids");

    var table = new DataTable { Columns = { { "id", typeof(int) } }, Rows = { { 1 }, { 2 }, { 3 } } };

    int count = connection.Query<int>("#DataTableParameters", new { ids = table.AsTableValuedParameter() }, commandType: CommandType.StoredProcedure).First();
    count.IsEqualTo(3);

    count = connection.Query<int>("select count(1) from @ids", new { ids = table.AsTableValuedParameter("MyTVPType") }).First();
    count.IsEqualTo(3);

    try
    {
        connection.Query<int>("select count(1) from @ids", new { ids = table.AsTableValuedParameter() }).First();
        throw new InvalidOperationException();
    }
    catch (Exception ex)
    {
        ex.Message.Equals("The table type parameter 'ids' must have a valid type name.");
    }
}