/// <summary>
/// 2020.08.07增加PGSQL批量写入
/// </summary>
/// <param name="table"></param>
/// <param name="tableName"></param>
private void PGSqlBulkInsert(DataTable table, string tableName)
{
List <string> columns = new List <string>();
for (int i = 0; i < table.Columns.Count; i++)
{
columns.Add("\"" + table.Columns[i].ColumnName + "\"");
}
string copySql = $"copy \"public\".\"{tableName}\"({string.Join(',', columns)}) FROM STDIN (FORMAT BINARY)";
using (var conn = new Npgsql.NpgsqlConnection(_connectionString))
{
conn.Open();
using (var writer = conn.BeginBinaryImport(copySql))
{
foreach (DataRow row in table.Rows)
{
writer.StartRow();
for (int i = 0; i < table.Columns.Count; i++)
{
writer.Write(row[i]);
}
}
writer.Complete();
}
}
}
public WriteData(TableDefinition TableDefinition, string InputXml, string connString, int BulkSize = 50000)
{
typesList = (new XSD2PGTypes()).Types;
tableDefinition = TableDefinition;
ConnectionString = connString;
// Задаю соединение
_conn = new Npgsql.NpgsqlConnection(ConnectionString);
_conn.Open();
// Открываем операцию копирования
_dbTableName = tableDefinition.schemaName + "." + tableDefinition.tableName;
Console.WriteLine(tableDefinition.schemaName);
_dbColumns = string.Join(",", tableDefinition.columns.Select(c => c.DBcolumn).ToArray());
_wrt = _conn.BeginBinaryImport(string.Format("COPY {0} ({1}) FROM STDIN (FORMAT BINARY)", _dbTableName, _dbColumns));
xmlFile = InputXml;
bulkSize = BulkSize;
}