void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
foreach (DataRow dr in dtActs.Rows)
{
id = int.Parse(dr[0].ToString());
string an = dr[1].ToString();
int fp = int.Parse(dr[2].ToString());
string st = dr[3].ToString();
string et = dr[4].ToString();
conn.Close();
conn.Open();
NpgsqlCommand command1 = new NpgsqlCommand("insert into acts(name, showid, free_placement, start_time, end_time) values(:nm, :shid, :fp, :st, :et)", conn);
command1.Parameters.Add(new NpgsqlParameter("nm", an));
command1.Parameters.Add(new NpgsqlParameter("shid", addedshowid));
command1.Parameters.Add(new NpgsqlParameter("fp", fp));
command1.Parameters.Add(new NpgsqlParameter("st", st));
command1.Parameters.Add(new NpgsqlParameter("et", et));
command1.ExecuteNonQuery();
NpgsqlCommand command2 = new NpgsqlCommand("select currval('acts_actid_seq');", conn);
NpgsqlDataReader read2;
read2 = command2.ExecuteReader();
read2.Read();
addedactid = read2[0].ToString();
conn.Close();
int nr = int.Parse(addedactid.ToString());
conn.Open();
NpgsqlCommand cmd2 = new NpgsqlCommand("Copy available_seats(actid, seatid) from STDIN", conn);
NpgsqlCopySerializer ser = new NpgsqlCopySerializer(conn);
NpgsqlCopyIn copy = new NpgsqlCopyIn(cmd2, conn, ser.ToStream);
copy.Start();
foreach (DataRow row in cSeats.Rows)
{
if (int.Parse(row[0].ToString()) == id)
{
int seatid = int.Parse(row[1].ToString());
ser.AddInt32(nr);
ser.AddInt32(seatid);
ser.EndRow();
ser.Flush();
}
}
copy.End();
ser.Close();
}
}
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));
}
}
}
}
// Serializer success test
static public 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 bool export()
{
NpgsqlCommand cmd = new NpgsqlCommand(CreateInsertCmd(), connection);
NpgsqlCopySerializer serializer = new NpgsqlCopySerializer(connection);
NpgsqlCopyIn copyIn = new NpgsqlCopyIn(cmd, connection, serializer.ToStream);
const int FLUSH_ROWS = 200000;
copyIn.Start();
var linecounter = 0;
foreach (var queryList in _queryLists)
{
bool[] array = new bool[200];
foreach (int i in queryList.Value)
{
array[i] = true;
}
serializer.AddInt32(queryList.Key);
for (int i = 0; i < 200; i++)
{
serializer.AddBool(array[i]);
}
serializer.EndRow();
if (linecounter++ % FLUSH_ROWS == 0)
{
serializer.Flush();
}
}
serializer.Flush();
serializer.Close();
copyIn.End();
return(true);
}
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");
}
}
/// <summary>
/// Wrzuca dane do bazy danych.
/// </summary>
private bool importData()
{
tic("Importing data:", true, true);
const int FLUSH_ROWS = 200000;
String[] toImport = new String[]
{
sessionTableName, queryTableName, queryTermTableName, queryUrlTableName, clickTableName, urlTableName
};
foreach (String tableName in toImport)
{
tic(tableName, false, true);
int[] types = getTableTypes(tableName);
NpgsqlCommand cmd = new NpgsqlCommand(buildInsertCommand(tableName), connection);
NpgsqlCopySerializer serializer = new NpgsqlCopySerializer(connection);
NpgsqlCopyIn copyIn = new NpgsqlCopyIn(cmd, connection, serializer.ToStream);
copyIn.Start();
using (BufferedBinaryReader reader = new BufferedBinaryReader(workDir + tableName))
{
int lineCounter = 0;
while (reader.PeekChar() > -1)
{
lineCounter++;
for (int i = 0; i < types.Length; i++)
{
if (types[i] == 0)
{
int value = reader.ReadInt32();
serializer.AddInt32(value);
}
if (types[i] == 1)
{
bool value = reader.ReadBool();
serializer.AddBool(value);
}
}
serializer.EndRow();
if ((lineCounter + 1) % FLUSH_ROWS == 0)
{
serializer.Flush();
}
}
Console.Write(String.Format("{0,-15}", String.Format("({0})", lineCounter)));
}
serializer.Flush();
serializer.Close();
copyIn.End();
toc();
}
toc(true);
return(true);
}