public void InvariantCultureNpgsqlCopySerializer()
{
// Test for https://github.com/npgsql/Npgsql/pull/92
// SetCulture is used to set a culture where a comma is used to separate decimal values (0,5) which will cause problems if Npgsql
// doesn't convert correctly to use a point. (0.5)
var cmd = new NpgsqlCommand("COPY data (field_int4, field_int8, field_float4) FROM STDIN", Conn);
var npgsqlCopySerializer = new NpgsqlCopySerializer(Conn);
var npgsqlCopyIn = new NpgsqlCopyIn(cmd, Conn, npgsqlCopySerializer.ToStream);
npgsqlCopyIn.Start();
npgsqlCopySerializer.AddInt32(300000);
npgsqlCopySerializer.AddInt64(1000000);
npgsqlCopySerializer.AddNumber(0.5);
npgsqlCopySerializer.EndRow();
npgsqlCopySerializer.Flush();
npgsqlCopyIn.End();
NpgsqlDataReader dr = new NpgsqlCommand("select field_int4, field_int8, field_float4 from data", Conn).ExecuteReader();
dr.Read();
Assert.AreEqual(300000, dr[0]);
Assert.AreEqual(1000000, dr[1]);
Assert.AreEqual(0.5, dr[2]);
}
public void Bug188BufferNpgsqlCopySerializer()
{
var cmd = new NpgsqlCommand("COPY data (field_int4, field_text) FROM STDIN", Conn);
var npgsqlCopySerializer = new NpgsqlCopySerializer(Conn);
var npgsqlCopyIn = new NpgsqlCopyIn(cmd, Conn, npgsqlCopySerializer.ToStream);
string str = "Very long string".PadRight(NpgsqlCopySerializer.DEFAULT_BUFFER_SIZE, 'z');
npgsqlCopyIn.Start();
npgsqlCopySerializer.AddInt32(12345678);
npgsqlCopySerializer.AddString(str);
npgsqlCopySerializer.EndRow();
npgsqlCopySerializer.Flush();
npgsqlCopyIn.End();
NpgsqlDataReader dr = new NpgsqlCommand("select field_int4, field_text from data", Conn).ExecuteReader();
dr.Read();
Assert.AreEqual(12345678, dr[0]);
Assert.AreEqual(str, dr[1]);
}
// Serializer success test
public static void CopyInWithSerializer()
{
NpgsqlCopySerializer sink = new NpgsqlCopySerializer( conn );
String q = "COPY copy2(field_int4, field_int8, field_text, field_timestamp, field_bool) FROM STDIN";
cin = new NpgsqlCopyIn( q, conn );
cin.Start();
if(! cin.IsActive)
{
throw new Exception("Copy started inactive");
}
sink.AddInt32(-13);
sink.AddNull();
sink.AddString("First row");
sink.AddDateTime(new DateTime( 2020, 12, 22, 23, 33, 45, 765 ));
sink.AddBool(true);
sink.EndRow();
sink.AddNull();
sink.AddNull();
sink.AddString("Second row");
sink.Close();
Console.Out.WriteLine("Copy through serializer ok");
}
public void Bug221MillisecondsFieldNotCopied()
{
// Test for https://github.com/npgsql/Npgsql/issues/221
// The milliseconds field is not properly copied in NpgsqlCopySerializer.cs in method AddDateTime
var cmd = new NpgsqlCommand("COPY data (field_timestamp) FROM STDIN", Conn);
var npgsqlCopySerializer = new NpgsqlCopySerializer(Conn);
var npgsqlCopyIn = new NpgsqlCopyIn(cmd, Conn, npgsqlCopySerializer.ToStream);
var testDate = DateTime.Parse("2002-02-02 09:00:23.005");
npgsqlCopyIn.Start();
npgsqlCopySerializer.AddDateTime(testDate);
npgsqlCopySerializer.EndRow();
npgsqlCopySerializer.Flush();
npgsqlCopyIn.End();
NpgsqlDataReader dr = new NpgsqlCommand("select field_timestamp from data", Conn).ExecuteReader();
dr.Read();
Assert.AreEqual(testDate, dr[0]);
}
public void Bug219NpgsqlCopyInConcurrentUsage()
{
try
{
// Create temporary test tables
ExecuteNonQuery(@"CREATE TABLE Bug219_table1 (
id integer,
name character varying(100)
)
WITH (
OIDS=FALSE
);");
ExecuteNonQuery(@"CREATE TABLE Bug219_table2 (
id integer,
null1 integer,
name character varying(100),
null2 integer,
description character varying(1000),
null3 integer
)
WITH (
OIDS=FALSE
);");
using (var connection1 = new NpgsqlConnection(ConnectionString))
using (var connection2 = new NpgsqlConnection(ConnectionString))
{
connection1.Open();
connection2.Open();
var copy1 = new NpgsqlCopyIn("COPY Bug219_table1 FROM STDIN;", connection1);
var copy2 = new NpgsqlCopyIn("COPY Bug219_table2 FROM STDIN;", connection2);
copy1.Start();
copy2.Start();
NpgsqlCopySerializer cs1 = new NpgsqlCopySerializer(connection1);
//NpgsqlCopySerializer cs2 = new NpgsqlCopySerializer(connection2);
for (int index = 0; index < 10; index++)
{
cs1.AddInt32(index);
cs1.AddString(string.Format("Index {0} ", index));
cs1.EndRow();
/*cs2.AddInt32(index);
cs2.AddNull();
cs2.AddString(string.Format("Index {0} ", index));
cs2.AddNull();
cs2.AddString("jjjjj");
cs2.AddNull();
cs2.EndRow();*/
}
cs1.Close(); //Exception
//cs2.Close();
copy1.End();
copy2.End();
}
}
catch (Exception)
{
throw;
}
finally
{
ExecuteNonQuery(@"DROP TABLE IF EXISTS Bug219_table1");
ExecuteNonQuery(@"DROP TABLE IF EXISTS Bug219_table2");
}
}
private void AddValueToSerializer(NpgsqlCopySerializer serializer, object value)
{
if (value.GetType() == typeof(Int32))
serializer.AddInt32((int)value);
if (value.GetType() == typeof(Int64))
serializer.AddInt64((Int64)value);
if (value.GetType() == typeof(string))
serializer.AddString(value.ToString());
if (value.GetType() == typeof(float))
serializer.AddNumber((float)value);
if (value.GetType() == typeof(double))
serializer.AddNumber((double)value);
if (value.GetType() == typeof(bool))
serializer.AddBool((bool)value);
if (value.GetType() == typeof(DateTime))
serializer.AddDateTime((DateTime)value);
if (value == null || value == DBNull.Value)
serializer.AddNull();
if (value.GetType() == typeof(Guid))
serializer.AddString(value.ToString());
}
protected void InsertDataToDbBulkMethod(DataTable table)
{
List<string> columns_names = new List<string>();
for (int i = 0; i < table.Columns.Count; i++)
columns_names.Add(table.Columns[i].ColumnName);
string sql = string.Format("COPY {0}({1}) FROM STDIN", table.TableName, string.Join(",", columns_names.ToArray()));
_cmd = CreateCommand(sql);
_cmd.CommandType = CommandType.Text;
var serializer = new NpgsqlCopySerializer(_conn as NpgsqlConnection);
NpgsqlCopyIn copyIn = new NpgsqlCopyIn((_cmd as NpgsqlCommand), (_conn as NpgsqlConnection), serializer.ToStream);
try
{
copyIn.Start();
foreach (DataRow dr in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
AddValueToSerializer(serializer, dr[i]);
serializer.EndRow();
serializer.Flush();
}
copyIn.End();
serializer.Close();
}
catch (Exception e)
{
try
{
copyIn.Cancel("Exception has occured!");
}
catch (NpgsqlException ex)
{
if (ex.BaseMessage.Contains("Exception has occured!"))
throw new Exception(string.Format("Copy was uncanceled. exception1: {0};exception2: {1}", e.Message, ex.Message));
}
}
}
