private static string CreateCopyCommand(TransferTable table)
{
StringBuilder sb = new StringBuilder("COPY ");
sb.Append(cb.QuoteIdentifier(table.Name));
sb.Append(" (");
foreach (var columnInfo in table.Columns)
{
sb.Append(cb.QuoteIdentifier(columnInfo.Name));
sb.Append(", ");
}
sb.Length -= 2;
sb.Append(") FROM STDIN (FORMAT BINARY);");
return(sb.ToString());
}
private static void CreateTable(TransferTable table)
{
using (var command = _connection.CreateCommand())
{
StringBuilder sb = new StringBuilder($"CREATE TABLE {cb.QuoteIdentifier(table.Name)} (");
foreach (var columnInfo in table.Columns)
{
sb.Append(cb.QuoteIdentifier(columnInfo.Name));
sb.Append(" ");
sb.Append(GetTypeString(columnInfo.Type));
sb.Append(columnInfo.Nullable ? " NULL," : " NOT NULL,");
}
sb.Length -= 1;
sb.Append(");");
command.CommandText = sb.ToString();
command.ExecuteNonQuery();
}
}
private static IEnumerable <TransferTable> GetTableInfos(Excel.Workbook wb)
{
var sheets = wb.Worksheets;
try
{
List <TransferTable> tables = new List <TransferTable>(sheets.Count);
foreach (Excel.Worksheet sheet in sheets)
{
tables.Add(TransferTable.FromWorkSheet(sheet));
Marshal.ReleaseComObject(sheet);
}
return(tables);
}
finally
{
Marshal.ReleaseComObject(sheets);
}
}
public static void AddTableWithData(TransferTable table)
{
CreateTable(table);
string cmd = CreateCopyCommand(table);
using (var writer =
_connection.BeginBinaryImport(cmd))
{
foreach (var row in table.Rows)
{
writer.StartRow();
foreach (var column in table.Columns)
{
var value = ConvertValue(row[column], column.Type);
writer.Write(value, column.Type);
}
}
}
}
public static TransferTable FromWorkSheet(Excel.Worksheet sheet)
{
List <ColumnInfo> columns = new List <ColumnInfo>();
List <TransferRow> rows = new List <TransferRow>();
sheet.Activate();
Excel.Range usedRange = sheet.UsedRange;
Excel.Range usedRows = usedRange.Rows;
Excel.Range usedColumns = usedRange.Columns;
for (int i = 1; i <= usedColumns.Count; i++)
{
Excel.Range cell;
ColumnInfo ci = null;
cell = usedColumns.Cells[1, i];
ci = new ColumnInfo(i - 1, cell.Value2 as string ?? $"Column {i}");
Marshal.ReleaseComObject(cell);
ColumnTypeInferer ct = new ColumnTypeInferer();
for (int j = 2; j <= usedRows.Count; j++)
{
if (i == 1)
{
rows.Add(new TransferRow(new object[usedColumns.Count]));
}
cell = usedRange.Cells[j, i];
var value = cell.Value2;
if (value == null)
{
ci.Nullable = true;
}
ct.UpdateType(value, cell.NumberFormat);
rows[j - 2][ci] = value;
Marshal.ReleaseComObject(cell);
}
ci.Type = ct.ResultTypeMightBeDateOrTime ? NpgsqlDbType.Timestamp : ct.ResultType;
columns.Add(ci);
}
var table = new TransferTable(sheet.Name, columns, rows);
Marshal.ReleaseComObject(usedRows);
Marshal.ReleaseComObject(usedColumns);
Marshal.ReleaseComObject(usedRange);
return(table);
}
