public void CancelTextImport() { using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn); var writer = (NpgsqlCopyTextWriter)conn.BeginTextImport("COPY data (field_text, field_int4) FROM STDIN"); writer.Write("HELLO\t1\n"); writer.Cancel(); Assert.That(ExecuteScalar(@"SELECT COUNT(*) FROM data", conn), Is.EqualTo(0)); } }
public void CloseDuringCopy() { // TODO: Check no broken connections were returned to the pool using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn); conn.BeginBinaryImport("COPY data (field_text, field_int4) FROM STDIN BINARY"); } using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn); conn.BeginBinaryExport("COPY data (field_text, field_int2) TO STDIN BINARY"); } using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn); conn.BeginRawBinaryCopy("COPY data (field_text, field_int4) FROM STDIN BINARY"); } using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn); conn.BeginRawBinaryCopy("COPY data (field_text, field_int4) TO STDIN BINARY"); } using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn); conn.BeginTextImport("COPY data (field_text, field_int4) FROM STDIN"); } using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn); conn.BeginTextExport("COPY data (field_text, field_int4) TO STDIN"); } }
public void Insert(IEnumerable items, string tableName, Action<long> notifyRowsCopied = null, Action<Exception> onError = null) { var targetMethod = typeof (ObjectReader).GetMethod("Create", BindingFlags.Static | BindingFlags.Public); var targetGenericMethod = targetMethod.MakeGenericMethod(new Type[] {_type}); var castMethod = typeof (Enumerable).GetMethod("Cast", BindingFlags.Static | BindingFlags.Public); var castGenericMethod = castMethod.MakeGenericMethod(new Type[] {_type}); var destinationColumnsString = ColumnMappings.GetSqlBulkInsertMappings() .Select(x => x.DestinationColumn) .Aggregate((a, b) => a + "," + b); var sourceColumnsArray = ColumnMappings.GetSqlBulkInsertMappings().Select(x => x.SourceColumn).ToArray(); var objectReader = (ObjectReader) targetGenericMethod.Invoke(null, new[] {castGenericMethod.Invoke(null, new[] {items}), sourceColumnsArray}); using (var connection = new NpgsqlConnection(_connectionString)) { try { connection.Open(); } catch (Exception x) { Log.Error(x); if (onError == null) throw; onError(x); } var copyCommand = string.Format(CultureInfo.InvariantCulture, "COPY {0}({1}) FROM STDIN WITH CSV", tableName, destinationColumnsString); using (var writer = connection.BeginTextImport(copyCommand)) { Log.DebugFormat("Starting bulk insert into '{0}'", tableName); try { var row = 0; IDataReader reader = objectReader as IDataReader; while (reader.Read()) { for (var i = 0; i < reader.FieldCount; i++) { if (i > 0) writer.Write(CsvDelimiter); object value = reader.GetValue(i); if (value != null) { writer.Write(CsvQuote); writer.Write(_csvRegex.Replace(value.ToString(), CsvReplacement)); writer.Write(CsvQuote); } } writer.WriteLine(); row++; if (row%NotifyAfter == 0) { if (notifyRowsCopied != null) { notifyRowsCopied(row); } } } writer.Flush(); } catch (Exception x) { Log.ErrorFormat("Error bulk inserting", x); if (onError == null) throw; onError(x); } } } }