public List <House> GetListHouse(IEnumerable <int> listGid)
{
using (NpgsqlConnection con = new NpgsqlConnection(_conectString))
{
con.Open();
string query = $"CREATE TEMP TABLE IF NOT EXISTS {tempTableId} (id integer)";
using (NpgsqlCommand cmd = new NpgsqlCommand(query, con))
{
cmd.ExecuteNonQuery();
}
using (var writer = con.BeginBinaryImport($"COPY {tempTableId} (id) FROM STDIN BINARY"))
{
foreach (var gid in listGid)
{
writer.StartRow();
writer.Write(gid, NpgsqlDbType.Integer);
}
writer.Complete();
}
query = $"SELECT H.fiasguid Fias, H.orponid OrponId, H.parent_id ParentId From {tempTableId} S, {houseTable} A WHERE S.gid=H.orponid and A.livestatus=1";
return(con.Query <House>(query).ToList());
}
}
private void InsertTemporaryData(List <JObject> batch, string topic, List <string> columns, NpgsqlConnection conn)
{
var i = 0;
var tableColumns = new StringBuilder();
var geometryFactory = new GeometryFactory();
var rdr = new WKTReader(geometryFactory);
foreach (var column in columns)
{
tableColumns.Append(column + ",");
}
tableColumns = tableColumns.Remove(tableColumns.Length - 1, 1);
var comand = @$ "COPY {topic} ( {tableColumns} ) FROM STDIN (FORMAT BINARY)";
using (var writer = conn.BeginBinaryImport(comand))
{
foreach (var document in batch)
{
i++;
writer.StartRow();
foreach (var column in columns)
{
if (column == "position" || column == "roadRegistrationRoadLine" || column == "geo" || column == "byg404Koordinat" || column == "tek109Koordinat")
{
// TODO add environment variable
rdr.DefaultSRID = _databaseSetting.GeoSRID;
if (document[column].Type == JTokenType.Null)
{
document[column] = "LINESTRING (0 0, 0 0)";
}
var c = rdr.Read((string)document[column]);
writer.Write(c);
}
else if (column == "population")
{
if (document[column].Type == JTokenType.Null)
{
document[column] = 0;
}
writer.Write((int)document[column]);
}
else if (column == "gid")
{
writer.Write(i);
}
else
{
writer.Write((string)document[column]);
}
}
}
writer.Complete();
batch.Clear();
}
}
private void DoCopy <T>(NpgsqlConnection connection, long toBlockNum, List <T> set)
where T : BaseTable
{
if (!set.Any())
{
return;
}
set.Sort();
if (set[0].BlockNum > toBlockNum)
{
return;
}
var save = set.Where(i => i.BlockNum <= toBlockNum).ToArray();
set.RemoveRange(0, save.Length);
var cmd = save[0].CopyCommandText();
using (var binaryImport = connection.BeginBinaryImport(cmd))
{
foreach (var itm in save)
{
itm.Import(binaryImport);
Count--;
}
binaryImport.Complete();
}
}
public void WriteTablePostgres(string tableName)
{
string cols = "";
foreach (DataColumn col in Columns)
{
if (col.DataType != typeof(Identity))
{
cols += $"{col.ColumnName.ToLower()},";
}
}
string sql = $"COPY {tableName.ToLower()} ({cols.TrimEnd(',')}) FROM STDIN (FORMAT BINARY)";
using (var bulkCopy = pgConnection.BeginBinaryImport(sql)) {
foreach (DataRow row in Rows)
{
bulkCopy.StartRow();
foreach (DataColumn col in Columns)
{
if (col.DataType != typeof(Identity))
{
if (row.IsNull(col))
{
bulkCopy.WriteNull();
}
else
{
bulkCopy.Write(row[col], types[col.DataType]);
}
}
}
}
bulkCopy.Complete();
}
}
public void CreateRelation(IEnumerable <EntityPerson> users)
{
using (NpgsqlConnection conn = new NpgsqlConnection(_settingsConnect.GetConnectString()))
{
conn.Open();
string query = $"COPY {TABLE_RELATION} (" +
$"id_user, id_relative, \"name\", \"type\")" +
$"FROM STDIN BINARY";
var usersWithRelatives = users.Where(x => x.Relatives != null && x.Relatives.Any());
using (var writer = conn.BeginBinaryImport(query))
{
foreach (var user in usersWithRelatives)
{
foreach (var item in user.Relatives)
{
writer.StartRow();
writer.Write(user.Id);
writer.Write(item.Id);
writer.Write(item.Name);
writer.Write(item.Type);
}
}
writer.Complete();
}
}
}
public void SaveResponses(BlockingCollection <T> itemsToSaveManager, Action notifyErrorAction)
{
try
{
using (NpgsqlConnection conn = new NpgsqlConnection(_connectionString))
{
conn.Open();
using (NpgsqlBinaryImporter importer = conn.BeginBinaryImport($"COPY {TableName} ({Columns}) FROM STDIN (FORMAT BINARY);"))
{
while (!itemsToSaveManager.IsCompleted)
{
if (itemsToSaveManager.TryTake(out T model, 100))
{
if (!ImportRow(importer, model))
{
notifyErrorAction();
}
}
}
importer.Complete();
}
}
}
catch (Exception err)
{
Log.Error(err, err.ToString());
throw;
}
}
public void AddRange(IUniqueEntityCollection <Stop> entities)
{
using (var writer = _connection.BeginBinaryImport("COPY stop (feed_id, id, stop_code, stop_name, stop_desc, stop_lat, stop_lon, zone_id, stop_url, location_type, parent_station, stop_timezone, wheelchair_boarding, level_id, platform_code) FROM STDIN (FORMAT BINARY)"))
{
foreach (var stop in entities)
{
writer.StartRow();
writer.Write(_id, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(stop.Id, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(stop.Code, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(stop.Name, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(stop.Description, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(stop.Latitude, NpgsqlTypes.NpgsqlDbType.Real);
writer.Write(stop.Longitude, NpgsqlTypes.NpgsqlDbType.Real);
writer.Write(stop.Zone, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(stop.Url, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(stop.LocationType, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(stop.ParentStation, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(stop.Timezone, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(stop.WheelchairBoarding, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(stop.LevelId, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(stop.PlatformCode, NpgsqlTypes.NpgsqlDbType.Text);
}
}
}
public async Task InsertRangeAsync(IEnumerable <TestResult> testResultColl)
{
if (testResultColl == null || !testResultColl.Any())
{
return;
}
var testResultList = testResultColl.ToList();
using (var conn = new NpgsqlConnection(_config.String))
{
await conn.OpenAsync().ConfigureAwait(false);
using (var importer = conn.BeginBinaryImport(InsertRangeSqlCommand))
{
foreach (var testResult in testResultList)
{
importer.WriteRow
(
new object[3]
{
testResult.Test.TestId,
testResult.Test.Language,
JsonConvert.SerializeObject(testResult)
}
);
}
testResultList = null;
}
}
}
public void CreatePhote(IEnumerable <EntityPerson> users)
{
using (NpgsqlConnection conn = new NpgsqlConnection(_settingsConnect.GetConnectString()))
{
conn.Open();
string query = $"COPY {TABLE_PHOTO} (" +
$"id_user, photo_50, photo_100, photo_200, photo_200_orig, photo_max, photo_400_orig, photo_max_orig, photo_id)" +
$"FROM STDIN BINARY";
var usersWithPhoto = users.Where(x => x.HasPhoto == 1);
using (var writer = conn.BeginBinaryImport(query))
{
foreach (var user in usersWithPhoto)
{
writer.StartRow();
writer.Write(user.Id);
writer.Write(user.Photo50);
writer.Write(user.Photo100);
writer.Write(user.Photo200);
writer.Write(user.Photo200_Orig);
writer.Write(user.PhotoMax);
writer.Write(user.Photo400_Orig);
writer.Write(user.PhotoMaxOrig);
writer.Write(user.PhotoId);
}
writer.Complete();
}
}
}
public static void BulkInsert(this DataTable dataTable, NpgsqlConnection connection)
{
var columns = string.Join(",", dataTable.Columns.Cast <DataColumn>().Select(c => "\"" + c.ColumnName + "\""));
var commandFormat = string.Format(CultureInfo.InvariantCulture, "COPY {0} ({1}) FROM STDIN BINARY", dataTable.TableName, columns);
using (var writer = connection.BeginBinaryImport(commandFormat))
{
foreach (DataRow row in dataTable.Rows)
{
writer.StartRow();
foreach (DataColumn column in dataTable.Columns)
{
var value = row[column];
if (column.ColumnName == "ID" && value.IsBlank())
{
value = Guid.NewGuid();
}
if (value == DBNull.Value)
{
writer.WriteNull();
}
else
{
writer.Write(value, column.DataType.ToNpgsqlDbType());
}
}
}
}
}
public async Task InsertAsync(Call[] calls)
{
using (var conn = new NpgsqlConnection(DbCredentials.ConnectionString))
{
await conn.OpenAsync().ConfigureAwait(false);
using (var writer = conn.BeginBinaryImport(
"COPY calls " +
"(start_time,end_time,calling_number," +
"called_number,duration,call_type,call_id) " +
"FROM STDIN (FORMAT BINARY)"))
{
foreach (var call in calls)
{
writer.StartRow();
writer.Write(call.StartTime, NpgsqlDbType.Timestamp);
writer.Write(call.EndTime, NpgsqlDbType.Timestamp);
writer.Write(call.CallingNumber, NpgsqlDbType.Varchar);
writer.Write(call.CalledNumber, NpgsqlDbType.Varchar);
writer.Write(call.Duration, NpgsqlDbType.Integer);
writer.Write(call.CallType);
writer.Write(call.CallId, NpgsqlDbType.Varchar);
}
await writer.CompleteAsync().ConfigureAwait(false);
}
}
}
public void BulkCopy <T>(string tableName, NpgsqlConnection conn,
TargetProperty[] targetProperties, IEnumerable <T> toInsert)
{
var propertyList = new List <string>();
foreach (var property in targetProperties)
{
propertyList.Add(property.Name.ToLower());
}
var columnListString = string.Join(",", propertyList.Select(x => @"""" + x + @""""));
using (var writer = conn
.BeginBinaryImport($@"COPY ""{tableName}"" ({columnListString}) FROM STDIN (FORMAT BINARY)"))
{
foreach (var dto in toInsert)
{
writer.StartRow();
foreach (var property in targetProperties)
{
writer.Write(property.PropertyInfo.GetValue(dto));
}
}
writer.Complete();
}
}
public List <Address> GetListAddress(IEnumerable <int> listId)
{
using (NpgsqlConnection con = new NpgsqlConnection(_conectString))
{
con.Open();
var r = _rnd.Next(111, 999);
string query = $"CREATE TEMP TABLE {tempTableId}{r} (id integer)";
using (NpgsqlCommand cmd = new NpgsqlCommand(query, con))
{
cmd.ExecuteNonQuery();
}
using (var writer = con.BeginBinaryImport($"COPY {tempTableId}{r} (id) FROM STDIN BINARY"))
{
foreach (var id in listId)
{
writer.StartRow();
writer.Write(id, NpgsqlDbType.Integer);
}
writer.Complete();
}
query = $"SELECT A.id Id, A.aolevel Aolevel, A.popclustering_id Cluster, A.parent_id ParentId From {tempTableId}{r} S, {adrTable} A WHERE S.id=A.id and A.livestatus=1";
return(con.Query <Address>(query).ToList());
}
}
public List <House> GetListHouse(IEnumerable <string> listFias)
{
using (NpgsqlConnection con = new NpgsqlConnection(_conectString))
{
con.Open();
var r = _rnd.Next(111, 999);
string query = $"CREATE TEMP TABLE {tempTableFias}{r} (fias text)";
using (NpgsqlCommand cmd = new NpgsqlCommand(query, con))
{
cmd.ExecuteNonQuery();
}
using (var writer = con.BeginBinaryImport($"COPY {tempTableFias}{r} (fias) FROM STDIN BINARY"))
{
foreach (var fias in listFias)
{
writer.StartRow();
writer.Write(fias, NpgsqlDbType.Varchar);
}
writer.Complete();
}
query = $"SELECT H.fiasguid Fias, H.orponid OrponId, H.parent_id ParentId From {tempTableFias}{r} S, {houseTable} H WHERE S.fias=H.fiasguid and H.livestatus=1";
return(con.Query <House>(query).ToList());
}
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="mappings"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="identityBehavior"></param>
/// <param name="dbSetting"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
private static async Task <NpgsqlBinaryImporter> GetNpgsqlBinaryImporterAsync(NpgsqlConnection connection,
string tableName,
IEnumerable <NpgsqlBulkInsertMapItem> mappings,
int?bulkCopyTimeout,
BulkImportIdentityBehavior identityBehavior,
IDbSetting dbSetting,
CancellationToken cancellationToken = default)
{
var copyCommand = GetBinaryImportCopyCommand(tableName,
mappings,
identityBehavior,
dbSetting);
#if NET5_0
var importer = await connection.BeginBinaryImportAsync(copyCommand, cancellationToken);
#else
var importer = await Task.FromResult(connection.BeginBinaryImport(copyCommand));
#endif
// Timeout
if (bulkCopyTimeout.HasValue)
{
importer.Timeout = TimeSpan.FromSeconds(bulkCopyTimeout.Value);
}
// Return
return(importer);
}
public void CreateCareer(IEnumerable <EntityPerson> users)
{
using (NpgsqlConnection conn = new NpgsqlConnection(_settingsConnect.GetConnectString()))
{
conn.Open();
string query = $"COPY {TABLE_CAREER} (" +
$"id_user, company, group_id, country_id, city_id, city_name, \"position\", \"from\", \"until\")" +
$"FROM STDIN BINARY";
var usersWithCareers = users.Where(x => x.Careers != null && x.Careers.Any());
using (var writer = conn.BeginBinaryImport(query))
{
foreach (var user in usersWithCareers)
{
foreach (var item in user.Careers)
{
writer.StartRow();
writer.Write(user.Id);
writer.Write(item.Company);
writer.Write(item.GroupId);
writer.Write(item.CountryId);
writer.Write(item.CityId);
writer.Write(item.CityName);
writer.Write(item.Position);
writer.Write(item.From);
writer.Write(item.Until);
}
}
writer.Complete();
}
}
}
public async Task WriteBulk(List <Item> items)
{
try
{
await _connection.OpenAsync();
var writer = _connection.BeginBinaryImport($"COPY {_tablename} (id,column1,column2,column3,column4,column5,column6,column7,column8,column9) FROM STDIN (FORMAT BINARY)");
using (writer)
{
foreach (var item in items)
{
writer.WriteRow(
item.Id,
item.Column1,
item.Column2,
item.Column3,
item.Column4,
item.Column5,
item.Column6,
item.Column7,
item.Column8,
item.Column9
);
}
}
_connection.Close();
}
catch (Exception e)
{
Console.WriteLine($"ERROR - {e.Message}");
}
}
public void CreateMilitary(IEnumerable <EntityPerson> users)
{
using (NpgsqlConnection conn = new NpgsqlConnection(_settingsConnect.GetConnectString()))
{
conn.Open();
string query = $"COPY {TABLE_MILITARY} (" +
$"id_user, unit, unit_id, country_id, \"from\", \"until\")" +
$"FROM STDIN BINARY";
var usersWithMilitary = users.Where(x => x.Military != null && x.Military.Any());
using (var writer = conn.BeginBinaryImport(query))
{
foreach (var user in usersWithMilitary)
{
foreach (var item in user.Military)
{
writer.StartRow();
writer.Write(user.Id);
writer.Write(item.Unit);
writer.Write(item.UnitId);
writer.Write(item.CountryId);
writer.Write(item.From);
writer.Write(item.Until);
}
}
writer.Complete();
}
}
}
static void BulkInsert(NpgsqlConnection conn, string path)
{
using var writer = conn.BeginBinaryImport(
"copy raw_runtime(storage_path,scope,entity_type,depth,name,is_unsafe,is_partial,is_static,is_abstract,is_sealed,full_qualified,parent) from stdin(format binary)");
var cnt = 0;
foreach (var rec in File.ReadLines(path))
{
if ((++cnt & 4095) == 0)
{
Console.WriteLine($"{cnt}:{rec}");
}
var cursor = new Record(rec);
writer.StartRow();
writer.Write(cursor.StoragePath);
writer.Write(cursor.ScopeAsString);
writer.Write(cursor.IdentityAsString);
writer.Write(cursor.Depth, NpgsqlDbType.Integer);
writer.Write(cursor.Name);
writer.Write(cursor.IsUnsafe, NpgsqlDbType.Boolean);
writer.Write(cursor.IsPartial, NpgsqlDbType.Boolean);
writer.Write(cursor.IsStatic, NpgsqlDbType.Boolean);
writer.Write(cursor.IsAbstract, NpgsqlDbType.Boolean);
writer.Write(cursor.IsSealed, NpgsqlDbType.Boolean);
writer.Write(cursor.FullQualified);
writer.Write(cursor.ParentQualified);
}
writer.Complete();
}
public void CreateRelationPartner(IEnumerable <EntityPerson> users)
{
using (NpgsqlConnection conn = new NpgsqlConnection(_settingsConnect.GetConnectString()))
{
conn.Open();
string query = $"COPY {TABLE_RELATION_PARTNER} (" +
$"id_user, id_relation_partner, first_name, last_name)" +
$"FROM STDIN BINARY";
var usersWithRelationPartner = users.Where(x => x.RelationPartner != null);
using (var writer = conn.BeginBinaryImport(query))
{
foreach (var user in usersWithRelationPartner)
{
var item = user.RelationPartner;
writer.StartRow();
writer.Write(user.Id);
writer.Write(item.Id);
writer.Write(item.FirstName);
writer.Write(item.LastName);
}
writer.Complete();
}
}
}
private void ProcessEventsByCopyCommand(IEnumerable <LogEvent> events, NpgsqlConnection connection)
{
using (var binaryCopyWriter = connection.BeginBinaryImport(GetCopyCommand()))
{
WriteToStream(binaryCopyWriter, events);
}
}
public override async Task InsertTest()
{
string connString =
"Server=127.0.0.1; User Id=postgres; Database=benchmark; Port=5432; Password=pass.123; SSL Mode=Prefer; Trust Server Certificate=true";
var fakeuserUSers = UserUtility.GetFakeUsers(Times).Select(a => JsonConvert.SerializeObject(a));
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
using (var writer =
conn.BeginBinaryImport(
"COPY test.users (\"User\") FROM STDIN (FORMAT BINARY)")
)
{
foreach (var user in fakeuserUSers)
{
writer.StartRow();
writer.Write(user, NpgsqlDbType.Json);
}
writer.Complete();
}
}
}
/// <summary>
/// 通过NpgsqlQL连接把dataTable中的数据整块填充到数据库对应的数据表中
/// 注意，该函数不负责NpgsqlConnection的创建、打开以及关闭
/// </summary>
/// <param name="conn">NpgsqlQL连接</param>
/// <param name="dataTable">数据表</param>
public void BulkInsert(NpgsqlConnection conn, DataTable dataTable)
{
var columns = "";
foreach (var item in PropNames)
{
if (item == "Id")
{
continue;
}
columns += $"\"{item}\",";
}
columns = columns.TrimEnd(',');
var commandFormat = string.Format(CultureInfo.InvariantCulture, "COPY {0} ({1}) FROM STDIN BINARY", FullTableName, columns);
using (var writer = conn.BeginBinaryImport(commandFormat))
{
foreach (DataRow item in dataTable.Rows)
{
writer.WriteRow(item.ItemArray);
}
writer.Complete();
}
}
private void WriteInsertPortion <T>(
IEnumerable <T> list,
EntityInfo mapping,
NpgsqlConnection conn,
string tempTableName,
NpgsqlBulkCodeBuilder <T> codeBuilder)
{
// 2. Import into temp table
using (var importer = conn.BeginBinaryImport($"COPY {tempTableName} ({mapping.CopyColumnsForInsertQueryPart}, __index) FROM STDIN (FORMAT BINARY)"))
{
var opContext = new OperationContext(context, false);
var index = 1;
foreach (var item in list)
{
importer.StartRow();
codeBuilder.WriterForInsertAction(item, importer, opContext);
importer.Write(index, NpgsqlDbType.Integer);
index++;
}
// Temp solution!!!
//importer.Complete();
CompleteMethodInfo.Invoke(importer, null);
}
}
public void AddRange(IEnumerable <StopTime> entities)
{
using (var writer = _connection.BeginBinaryImport("COPY stop_time (feed_id, trip_id, arrival_time, departure_time, stop_id, stop_sequence, stop_headsign, pickup_type, drop_off_type, shape_dist_traveled, passenger_boarding, passenger_alighting, through_passengers, total_passengers, continuous_pickup, continuous_drop_off) FROM STDIN (FORMAT BINARY)"))
{
foreach (var stopTime in entities)
{
writer.StartRow();
writer.Write(_id, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(stopTime.TripId, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(stopTime.ArrivalTime.Value.TotalSeconds, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(stopTime.DepartureTime.Value.TotalSeconds, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(stopTime.StopId, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(stopTime.StopSequence, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(stopTime.StopHeadsign, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(stopTime.PickupType, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(stopTime.DropOffType, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(stopTime.ShapeDistTravelled, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(stopTime.PassengerBoarding, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(stopTime.PassengerAlighting, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(stopTime.ThroughPassengers, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(stopTime.TotalPassengers, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(stopTime.ContinuousPickup, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(stopTime.ContinuousDropOff, NpgsqlTypes.NpgsqlDbType.Integer);
}
}
}
private async ValueTask <ulong> DoSaveAllAsync(NpgsqlConnection connection, IEnumerable <TEntity> entities)
{
await using var binaryCopyWriter = connection.BeginBinaryImport(GetCopyCommand());
await WriteToStream(binaryCopyWriter, entities);
return(await binaryCopyWriter.CompleteAsync());
}
static void Copy(NpgsqlConnection conn, string tablename, string[] colnames, IEnumerable <object[]> data)
{
var sql_tablename = Q(tablename);
var sql_colnames = string.Join(", ", colnames.Select(n => Q(n)));
var qry = $"COPY {sql_tablename} ({sql_colnames}) FROM STDIN (FORMAT BINARY)";
var types = GetColumnTypes(conn, tablename, colnames).Select(s => GetColumnNpgsqlDbType(s)).ToArray();
using (var writer = conn.BeginBinaryImport(qry))
{
int rownum = 0;
foreach (var row in data)
{
if (row.Length != types.Length)
{
throw new ArgumentException($"Row {rownum} has wrong lenth of {row.Length}");
}
int colnum = 0;
try
{
writer.StartRow();
for (colnum = 0; colnum < types.Length; colnum++)
{
writer.Write(row[colnum], types[colnum]);
}
}
catch (Exception e)
{
throw new Exception($"Error importing row {rownum} column {colnum} ({Q(colnames[colnum])})", e);
}
rownum++;
}
}
}
public void SaveAll(NpgsqlConnection connection, IEnumerable <TEntity> entities)
{
using (var binaryCopyWriter = connection.BeginBinaryImport(GetCopyCommand()))
{
WriteToStream(binaryCopyWriter, entities);
}
}
private void BulkCopy(DataTable dt, string copyString, NpgsqlConnection conn, List <DbColumnInfo> columns)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
List <ColumnView> columnViews = new List <ColumnView>();
foreach (DataColumn item in dt.Columns)
{
ColumnView result = new ColumnView();
result.DbColumnInfo = columns.FirstOrDefault(it => it.DbColumnName.EqualCase(item.ColumnName));
result.DataColumn = item;
result.EntityColumnInfo = this.entityInfo.Columns.FirstOrDefault(it => it.DbColumnName.EqualCase(item.ColumnName));
var key = result.DbColumnInfo?.DataType?.ToLower();
if (result.DbColumnInfo == null)
{
result.Type = null;
}
else if (PgSqlType.ContainsKey(key))
{
result.Type = PgSqlType[key];
}
else if (key?.First() == '_')
{
var type = PgSqlType[key.Substring(1)];
result.Type = NpgsqlDbType.Array | type;
}
else
{
result.Type = null;
}
columnViews.Add(result);
}
using (var writer = conn.BeginBinaryImport(copyString))
{
foreach (DataRow row in dt.Rows)
{
writer.StartRow();
foreach (var column in columnViews)
{
var value = row[column.DataColumn.ColumnName];
if (value == null)
{
value = DBNull.Value;
}
if (column.Type == null)
{
writer.Write(value);
}
else
{
writer.Write(value, column.Type.Value);
}
}
}
writer.Complete();
}
}
public bool ImportFrom(ExcelImportBase import)
{
string columNames = import.ColumnNames.Aggregate(new StringBuilder(),
(current, next) => current.Append(current.Length == 0 ? "" : ",").Append(next)).ToString();
using (var trx = conn.BeginTransaction())
{
int rowCount = 0;
try
{
var truncateCmd = conn.CreateCommand();
truncateCmd.CommandText = $"DELETE FROM {import.TableName}";
truncateCmd.ExecuteNonQuery();
var query = $"COPY {import.TableName} ({columNames}) FROM STDIN (FORMAT BINARY)";
using (var writer = conn.BeginBinaryImport($"COPY {import.TableName} ({columNames}) FROM STDIN (FORMAT BINARY)"))
{
rowCount = 1;
import.ResetId();
foreach (var r in import.GetRows())
{
rowCount++;
var result = import.GetValues(r);
if (result == null)
{
continue;
}
writer.WriteRow(result);
}
writer.Complete();
}
}
catch (PostgresException e)
{
trx.Rollback();
Console.WriteLine($"Excel Name: {import.eName}");
Console.WriteLine($"Row: {rowCount}");
Console.WriteLine($"Msg: {e.Message}");
Console.WriteLine($"Detail: {e.Detail}");
return(false);
}
catch (Exception e)
{
trx.Rollback();
Console.WriteLine($"Excel Name: {import.eName}");
Console.WriteLine($"Row: {rowCount}");
Console.WriteLine($"Msg: {e.Message}");
return(false);
}
trx.Commit();
}
Console.WriteLine($"{import.TableName} 완료");
return(true);
}
