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); }