/// <summary>
///
/// </summary>
/// <param name="importer"></param>
/// <param name="data"></param>
/// <param name="npgsqlDbType"></param>
private static void BinaryImportWrite(NpgsqlBinaryImporter importer,
object data,
NpgsqlDbType?npgsqlDbType)
{
if (data == null)
{
importer.WriteNull();
}
else
{
if (npgsqlDbType != null)
{
if (data is Enum)
{
if (npgsqlDbType == NpgsqlDbType.Integer ||
npgsqlDbType == NpgsqlDbType.Bigint ||
npgsqlDbType == NpgsqlDbType.Smallint)
{
data = Convert.ToInt32(data);
}
else if (npgsqlDbType == NpgsqlDbType.Text)
{
data = Convert.ToString(data);
}
}
importer.Write(data, npgsqlDbType.Value);
}
else
{
importer.Write(data);
}
}
}
public static void WriteValue(this NpgsqlBinaryImporter binaryImporter, StatusEnum?value)
{
if (value.HasValue)
{
binaryImporter.Write((byte)value.Value, NpgsqlDbType.Smallint);
}
else
{
binaryImporter.WriteNull();
}
}
public static void WriteNullable <T>(this NpgsqlBinaryImporter writer, T value, NpgsqlDbType npgsqlDbType)
{
if (value == null)
{
writer.WriteNull();
}
else
{
writer.Write(value, npgsqlDbType);
}
}
/// <summary>
/// when used within npgsql conn BeginBinaryImport allow to write safely nullable as null
/// </summary>
public static void SafeWrite <T>(this NpgsqlBinaryImporter wr, T obj)
{
if (obj == null)
{
wr.WriteNull();
}
else
{
wr.Write(obj);
}
}
protected void Write <T>(NpgsqlBinaryImporter importer, T?value, NpgsqlDbType type) where T : struct
{
if (value == null)
{
importer.WriteNull();
}
else
{
importer.Write(value.Value, type);
}
}
protected void Write <T>(NpgsqlBinaryImporter importer, T value, NpgsqlDbType type)
{
if (value == null)
{
importer.WriteNull();
}
else
{
importer.Write(value, type);
}
}
static void Main(string[] args)
{
Console.WriteLine("Hello World!");
var table = new DataTable("practicetable2");
table.Columns.Add("num", typeof(int));
table.Columns.Add("numstring", typeof(string));
table.Columns.Add("description", typeof(string));
table.Columns.Add("someid", typeof(Guid));
for (int i = 0; i < 10; i++)
{
var row = table.NewRow();
row["num"] = i;
row["numstring"] = i.ToString();
// Commented below line to test for null case.
// row["description"] = "sfsdf";
row["someid"] = Guid.NewGuid();
table.Rows.Add(row);
}
for (int i = 0; i < table.Columns.Count; i++)
{
var column = table.Columns[i];
Console.WriteLine($"full name: {column.DataType.FullName}");
}
using (var conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;Database=joyn_fdgcommon_dev;User Id=postgres;Password=admin;"))
{
conn.Open();
using (NpgsqlBinaryImporter writer = conn.BeginBinaryImport("copy stage.practicetable2 from stdin(FORMAT BINARY)"))
{
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
writer.StartRow();
for (int j = 0; j < table.Columns.Count; j++)
{
var column = table.Columns[j];
var value = row[column.ColumnName];
if (value != null)
{
writer.Write(row[column.ColumnName]);
}
else
{
Console.WriteLine("writing null");
writer.WriteNull();
}
}
}
writer.Complete();
}
}
}
public static void WriteValue(this NpgsqlBinaryImporter binaryImporter, long?value)
{
if (value.HasValue)
{
binaryImporter.Write(value.Value, NpgsqlDbType.Bigint);
}
else
{
binaryImporter.WriteNull();
}
}
/// <summary>
/// Writes text value in bulk manner.
/// </summary>
public static void WriteNullable(this NpgsqlBinaryImporter writer, string?value, NpgsqlDbType dbType)
{
if (value != null)
{
writer.Write(value, dbType);
}
else
{
writer.WriteNull();
}
}
/// <summary>
/// Writes nullable structure in bulk manner.
/// </summary>
public static void WriteNullable <T>(this NpgsqlBinaryImporter writer, T?value, NpgsqlDbType dbType) where T : struct
{
if (value.HasValue)
{
writer.Write(value.Value, dbType);
}
else
{
writer.WriteNull();
}
}
/// <summary>
/// Mapping values from properties which have a corresponding data row.
/// Matching is done based on Column name and property key
/// </summary>
private void WriteAdditionalColumns(NpgsqlBinaryImporter writer, LogEvent logEvent)
{
foreach (var column in _columnOptions.AdditionalDataColumns)
{
logEvent.Properties.TryGetValue(column.PropertyName, out LogEventPropertyValue propertyValue);
var scalarValue = propertyValue as ScalarValue;
if (scalarValue != null)
{
writer.Write(scalarValue.ToString(), column.ColumnType);
}
else
{
writer.WriteNull();
}
}
}
/// <summary>
/// Пишет значение в импортер
/// </summary>
protected virtual void WriteValue(NpgsqlBinaryImporter writer, object value, bool valueSpecified, NpgsqlDbType?type = null)
{
if (!valueSpecified || value == null || value == DBNull.Value)
{
writer.WriteNull();
}
else if (type.HasValue)
{
writer.Write(value, type.Value);
}
else
{
writer.Write(value);
}
//Запись о том что столбец передан
writer.Write(valueSpecified);
}
public static void SerializeColumn(Column c, object value, NpgsqlBinaryImporter importer)
{
if (value == DBNull.Value)
{
importer.WriteNull();
}
else
{
var type = GetPgType(c.Type, c.IsMax);
var s = value as string;
if (s == null)
{
importer.Write(value, type);
}
else
{
s = Regex.Replace(s, "\0", ""); //null characters are not allowed in Postgres
importer.Write(s, type);
}
}
}
public static async Task PerformBulkCopyAsync(this DbDataReader dr, NpgsqlBinaryImporter writer, DataColumnCollection columns = null)
{
columns ??= dr.GetSchemaTable().Columns;
while (await dr.ReadAsync())
{
await writer.StartRowAsync();
foreach (DataColumn col in columns)
{
switch (dr[col.Ordinal])
{
case bool x:
await writer.WriteAsync(x);
break;
case byte x:
await writer.WriteAsync(x);
break;
case sbyte x:
await writer.WriteAsync(x);
break;
case short x:
await writer.WriteAsync(x);
break;
case ushort x:
await writer.WriteAsync(x);
break;
case int x:
await writer.WriteAsync(x);
break;
case uint x:
await writer.WriteAsync(x);
break;
case long x:
await writer.WriteAsync(x);
break;
case ulong x:
await writer.WriteAsync(x);
break;
case Guid x:
await writer.WriteAsync(x);
break;
case DateTime x:
await writer.WriteAsync(x);
break;
case TimeSpan x:
await writer.WriteAsync(x);
break;
case string x:
await writer.WriteAsync(x);
break;
case null:
writer.WriteNull();
break;
}
}
}
await writer.CompleteAsync();
}
private bool InternalExecScript(string table, string schema, object script, out uint count)
{
count = 0;
try
{
if (script is DataTable dt)
{
using (NpgsqlBinaryImporter writer = conn.BeginBinaryImport(
$"copy {ProcessTableName(table, schema)} from stdin binary"))
{
foreach (DataRow row in dt.Rows)
{
object[] values = row.ItemArray;
writer.StartRow();
for (int i = 0; i < values.Length; i++)
{
object value = values[i];
if (value == DBNull.Value || value == null)
{
writer.WriteNull();
}
else if (dt.Columns[i].DataType == typeof(bool))
{
writer.Write((bool)value, NpgsqlDbType.Bit);
}
else
{
writer.Write(value);
}
}
}
}
count = (uint)dt.Rows.Count;
return(true);
}
else if (script is MergeScript ms)
{
if (Execute(ms.PrepareSQL, null, out _))
{
try
{
if (InternalExecScript(ms.TableName, schema, ms.Data, out count))
{
if (Execute(ms.UpdateSQL, null, out _))
{
if (string.IsNullOrEmpty(ms.InsertSQL) || Execute(ms.InsertSQL, null, out _))
{
return(true);
}
}
}
}
finally
{
Execute(ms.CleanSQL, null, out _);
}
}
}
else if (script is UpdateScript us)
{
if (Execute(us.UpdateSQL, null, out count))
{
if (count == 0)
{
return(Execute(us.InsertSQL, null, out count));
}
else
{
return(true);
}
}
}
else if (script is string sql)
{
return(Execute(sql, null, out count));
}
}
catch (Exception ex)
{
LastError = $"{table}：{ex.Message}";
Logger.WriteLogExcept(LogTitle, ex);
}
return(false);
}
/// <summary>
/// Writes a single null column value.
/// </summary>
public void WriteNull()
{
_binaryImporter.WriteNull();
}
public static void PerformBulkCopy(this IDataReader dr, NpgsqlBinaryImporter writer, DataColumnCollection columns = null)
{
columns ??= dr.GetSchemaTable().Columns;
while (dr.Read())
{
writer.StartRow();
foreach (DataColumn col in columns)
{
switch (dr[col.Ordinal])
{
case bool x:
writer.Write(x);
break;
case byte x:
writer.Write(x);
break;
case sbyte x:
writer.Write(x);
break;
case short x:
writer.Write(x);
break;
case ushort x:
writer.Write(x);
break;
case int x:
writer.Write(x);
break;
case uint x:
writer.Write(x);
break;
case long x:
writer.Write(x);
break;
case ulong x:
writer.Write(x);
break;
case double x:
writer.Write(x);
break;
case float x:
writer.Write(x);
break;
case Guid x:
writer.Write(x);
break;
case DateTime x:
writer.Write(x);
break;
case TimeSpan x:
writer.Write(x);
break;
case char x:
writer.Write(x);
break;
case string x:
writer.Write(x);
break;
case null:
writer.WriteNull();
break;
}
}
}
writer.Complete();
}