I'm trying to use TVP in dapper to insert several rows at once like this:
con.Execute(@"
INSERT INTO Author (
FirstName,
LastName,
ParentAuthor
)
SELECT
FirstName,
LastName,
ParentAuthor
FROM @TVP
"
, new[]
{
new Author { FirstName = "Roger", LastName = "Smith", ParentAuthor = 1},
new Author { FirstName = "Stan", LastName = "Smith"},
new Author { FirstName = "Haley", LastName = "Smith"},
new Author { FirstName = "Francine", LastName = "Ling"}
} // .AsTableValuedParameter() => not working
);
But is there a convenient way to convert a collection of object into a TableValuedParameter?
I found out about fast-member and it then simply can be done like this:
var items = new[]
{
new Author {FirstName = "Roger", LastName = "Smith", ParentAuthor = 1},
new Author {FirstName = "Stan", LastName = "Smith"},
new Author {FirstName = "Haley", LastName = "Smith"},
new Author {FirstName = "Francine", LastName = "Ling"}
};
var table = new DataTable();
using (var reader = ObjectReader.Create(
items,
nameof(Author.FirstName),
nameof(Author.LastName),
nameof(Author.ParentAuthor)
))
{
table.Load(reader);
}
con.Execute(@"
INSERT INTO Author (
FirstName,
LastName,
ParentAuthor
)
SELECT
FirstName,
LastName,
ParentAuthor
FROM @TVP
"
, new { TVP = table.AsTableValuedParameter("TVP_Author") }
);
If there is a similar functionnality built-in dapper, I'd still like to know.