public void BulkUpsert <T>(string index, List <T> items) where T : class, IRepositoryItem
{
try
{
this.OpenConnection();
this.TransactionBegin();
var TableName = typeof(T).Name;
TableName = TableName.ToLower();
var TempTableName = TableName + "temp";
this.CreateTempTable(TempTableName);
var query = PostGresQueryBuilder.BulkInsert(TempTableName);
this.WriteDataToStream(index, items, query);
this.InsertDataFromTempTable(index, TableName, TempTableName);
this._transaction.Commit();
}
catch (NpgsqlException ex)
{
_transaction.Rollback();
}
finally
{
this._transaction.Dispose();
this._transaction = null;
this.CloseConnection();
}
}
private int ExecuteNonQuery(PostGresQueryBuilder query)
{
int result;
OpenConnection();
using (var command = new NpgsqlCommand(query.GetQuery(), this._connection, this._transaction))
{
result = command.ExecuteNonQuery();
}
return(result);
}
private void WriteDataToStream <T>(string index, List <T> Items, PostGresQueryBuilder query) where T : class, IRepositoryItem
{
this.OpenConnection();
using (var writer = _connection.BeginBinaryImport(query.GetQuery()))
{
foreach (var Item in Items)
{
writer.StartRow();
writer.Write(Item.GetId(), NpgsqlDbType.Varchar);
writer.Write(index, NpgsqlDbType.Varchar);
writer.Write(Item.CompanyId, NpgsqlDbType.Varchar);
writer.Write(Item.ProjectId, NpgsqlDbType.Varchar);
writer.Write(JsonConvert.SerializeObject(Item), NpgsqlDbType.Jsonb);
}
writer.Complete();
}
}
private void InsertDataFromTempTable(string index, string tableName, string tempTableName)
{
var query = PostGresQueryBuilder.InsertDataFromTable(tempTableName, tableName, index);
this.ExecuteNonQuery(query);
}
private void CreateTempTable(string tempTableName)
{
var queryBuilder = PostGresQueryBuilder.CreateTemporaryTable(tempTableName);
this.ExecuteNonQuery(queryBuilder);
}
