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); }