Dapper.NET Temp Tables Temp Table that exists while the connection remains open
Example
When the temp table is created by itself, it will remain while the connection is open.
// Widget has WidgetId, Name, and Quantity properties
public async Task PurchaseWidgets(IEnumerable<Widget> widgets)
{
using(var conn = new SqlConnection("{connection string}")) {
await conn.OpenAsync();
await conn.ExecuteAsync("CREATE TABLE #tmpWidget(WidgetId int, Quantity int)");
// populate the temp table
using(var bulkCopy = new SqlBulkCopy(conn)) {
bulkCopy.BulkCopyTimeout = SqlTimeoutSeconds;
bulkCopy.BatchSize = 500;
bulkCopy.DestinationTableName = "#tmpWidget";
bulkCopy.EnableStreaming = true;
using(var dataReader = widgets.ToDataReader())
{
await bulkCopy.WriteToServerAsync(dataReader);
}
}
await conn.ExecuteAsync(@"
update w
set Quantity = w.Quantity - tw.Quantity
from Widgets w
join #tmpWidget tw on w.WidgetId = tw.WidgetId");
}
}
