I need to bulk upsert to a PostgreSQL table that has a SERIAL "id" column. I am trying to use PgSqlDataTable like this:
Code: Select all
PgSqlCommand select = new PgSqlCommand();
select.CommandText = "select * from table ...";
PgSqlDataTable itemsToUpdate = new PgSqlDataTable( select, conn );
itemsToUpdate .FetchAll = true;
itemsToUpdate .Active = true;
itemsToUpdate .RefreshMode = Devart.Common.RefreshRowMode.Both;
itemsToUpdate .RefreshingFields = "id";
itemsToUpdate .Columns[ "id" ].AllowDBNull = true;
foreach( newOrUpdatedItem )
{
if( item is in itemsToUpdate )
update the item;
else
{
newItem = itemsToUpdate.NewRow();
set all values of item except "id";
itemsToUpdate.Rows.Add( newItem );
}
}
But calling Update() after each added item defeats my whole purpose, which is to do a bulk upsert.
How do I do this? Update the PgSqlDataTable rows for items that already exist and use a Loader for new items?