public static Exception ReadBinary(string query, string conn_string, out byte[] result, int timeoutMilliseconds = 100)
{
try
{
using (var sourceConnection = new NpgsqlConnection(conn_string))
{
sourceConnection.Open();
using (var memoryStream = new MemoryStream())
{
using (var inStream = sourceConnection.BeginRawBinaryCopy($"COPY ({query}) TO STDOUT (FORMAT BINARY)"))
{
var buffer = new byte[BUFFER_SIZE_BYTES];
var tryNumber = 0;
while (tryNumber <= MAX_READ_TRY_COUNT)
{
tryNumber++;
int readLength;
while ((readLength = inStream.Read(buffer, 0, buffer.Length)) > 0)
{
memoryStream.Write(buffer, 0, readLength);
tryNumber = 0;
}
Thread.Sleep(timeoutMilliseconds);
}
}
sourceConnection.Close();
result = memoryStream.ToArray();
return(null);
}
}
}
catch (Exception ex)
{
result = null;
QueryHandler.OnErrorExecute(ex);
return(ex);
}
}
public static Exception Copy(string sourceQuery, string destinationTableName, string sourceConnectionString, string destinationConnectionString, int timeoutMilliseconds = 100)
{
try
{
using (var sourceConnection = new NpgsqlConnection(sourceConnectionString))
using (var destinationConnection = new NpgsqlConnection(destinationConnectionString))
{
sourceConnection.Open();
destinationConnection.Open();
using (var inStream = sourceConnection.BeginRawBinaryCopy($"COPY ({sourceQuery}) TO STDOUT (FORMAT BINARY)"))
using (var outStream = destinationConnection.BeginRawBinaryCopy($"COPY {destinationTableName} FROM STDIN (FORMAT BINARY)"))
{
var buffer = new byte[BUFFER_SIZE_BYTES];
var tryNumber = 0;
while (tryNumber <= MAX_READ_TRY_COUNT)
{
tryNumber++;
int readLength;
while ((readLength = inStream.Read(buffer, 0, buffer.Length)) > 0)
{
outStream.Write(buffer, 0, readLength);
tryNumber = 0;
}
Thread.Sleep(timeoutMilliseconds);
}
}
sourceConnection.Close();
destinationConnection.Close();
}
return(null);
}
catch (Exception exception)
{
QueryHandler.OnErrorExecute(exception);
return(exception);
}
}
public static Exception Copy(string destinationConnectionString, IList <Dictionary <string, object> > datas, IList <string> fieldNames, string destinationTableName)
{
try
{
if (datas == null || !datas.Any())
{
return(null);
}
if (fieldNames == null || !fieldNames.Any())
{
return(null);
}
using (var destinationConnection = new NpgsqlConnection(destinationConnectionString))
{
destinationConnection.Open();
var destinationQuery = $"{destinationTableName}({string.Join(",", fieldNames)})";
using (var writer = destinationConnection.BeginTextImport($"COPY {destinationQuery} FROM STDIN"))
{
foreach (var item in datas)
{
var rowString = string.Join("\t", fieldNames.Select(name => $"{item[name].GenerateFormatText()}"));
writer.Write($"{rowString}\n");
}
writer.Write("\\.\n");
}
destinationConnection.Close();
return(null);
}
}
catch (Exception exception)
{
QueryHandler.OnErrorExecute(exception);
return(exception);
}
}
public static bool ExecutePg(this string query, out DataResult result, string connectionString, int timeout = 30)
{
result = new DataResult();
try
{
using (var conn = new Npgsql.NpgsqlConnection(connectionString))
{
conn.Open();
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SET statement_timeout = " + timeout.ToString() + "000;";
cmd.ExecuteNonQuery();
}
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = query;
cmd.CommandTimeout = timeout;
using (var reader = cmd.ExecuteReader())
{
while (reader.FieldCount > 0)
{
List <Tuple <Type, string> > ft = new List <Tuple <Type, string> >();
Dictionary <string, bool> b_name = new Dictionary <string, bool>();
for (int i = 0; i < reader.FieldCount; i++)
{
Type t = reader.GetFieldType(i);
string field_name = reader.GetName(i);
if (b_name.ContainsKey(field_name))
{
field_name = field_name + i.ToString();
}
ft.Add(new Tuple <Type, string>(t == typeof(DBNull) ? null : t, field_name));
b_name.Add(field_name, true);
}
result.res_types.Add(ft);
List <Dictionary <string, object> > lr = new List <Dictionary <string, object> >();
while (reader.Read())
{
Dictionary <string, object> row = new Dictionary <string, object>();
for (int i = 0; i < ft.Count; i++)
{
object v = ft[i].Item1 == null ? null : reader.GetValue(i);
if (v is DBNull)
{
v = null;
}
row.Add(ft[i].Item2, v);
}
lr.Add(row);
}
result.res.Add(lr);
reader.NextResult();
}
}
}
}
return(true);
}
catch (PostgresException se)
{
result.e = se;
result.se = se;
QueryHandler.OnErrorExecute(se);
}
catch (Exception ex)
{
result.e = ex;
QueryHandler.OnErrorExecute(ex);
}
return(false);
}
public static bool ExecutePg_Step(this string query, out DataResult result, string connectionString, int batch_limit
, Func <List <Tuple <Type, string> >, List <Dictionary <string, object> >, Tuple <bool, Exception> > func, int timeout = 3600)
{
result = new DataResult();
try
{
using (var conn = new Npgsql.NpgsqlConnection(connectionString))
{
conn.Open();
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SET statement_timeout = " + timeout.ToString() + "000;";
cmd.ExecuteNonQuery();
}
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = query;
using (var reader = cmd.ExecuteReader())
{
while (reader.FieldCount > 0)
{
List <Tuple <Type, string> > ft = new List <Tuple <Type, string> >();
Dictionary <string, bool> b_name = new Dictionary <string, bool>();
for (int i = 0; i < reader.FieldCount; i++)
{
Type t = reader.GetFieldType(i);
string field_name = reader.GetName(i);
if (b_name.ContainsKey(field_name))
{
field_name = field_name + i.ToString();
}
ft.Add(new Tuple <Type, string>(t == typeof(DBNull) ? null : t, field_name));
b_name.Add(field_name, true);
}
List <Dictionary <string, object> > lr = new List <Dictionary <string, object> >();
while (reader.Read())
{
Dictionary <string, object> row = new Dictionary <string, object>();
for (int i = 0; i < ft.Count; i++)
{
object v = ft[i].Item1 == null ? null : reader.GetValue(i);
if (v is DBNull)
{
v = null;
}
row.Add(ft[i].Item2, v);
}
lr.Add(row);
if (lr.Count > batch_limit)
{
var f_wr = func(ft, lr);
if (!f_wr.Item1)
{
Exception ex = new Exception("function faild in ExecutePg_Step\r\n" + f_wr.Item2.Message, f_wr.Item2);
result.e = ex;
QueryHandler.OnErrorExecute(ex);
return(false);
}
lr = new List <Dictionary <string, object> >();
}
}
if (lr.Count > 0)
{
var f_wr = func(ft, lr);
if (!f_wr.Item1)
{
Exception ex = new Exception("function faild in ExecutePg_Step\r\n" + f_wr.Item2.Message, f_wr.Item2);
result.e = ex;
QueryHandler.OnErrorExecute(ex);
return(false);
}
}
reader.NextResult();
}
}
}
}
return(true);
}
catch (NpgsqlException se)
{
result.e = se;
result.se = se;
QueryHandler.OnErrorExecute(se);
}
catch (Exception ex)
{
result.e = ex;
result.se = new Exception("fail_to_call");
QueryHandler.OnErrorExecute(ex);
}
return(false);
}
