private string CreateCopySql(StaticDB.Table table) { var sb = new StringBuilder(); var tableName = FauFau.SDBrowser.SDBrowser.GetTableOrFieldName(table.Id); sb.Append($"COPY {Schema}.\"{tableName}\" ("); for (var i = 0; i < table.Columns.Count; i++) { var col = table.Columns[i]; var name = FauFau.SDBrowser.SDBrowser.GetTableOrFieldName(col.Id); sb.Append($"\"{name}\""); if (table.Columns.Count - 1 != i) { sb.Append(", "); } } sb.Append(") FROM STDIN (FORMAT BINARY)"); return(sb.ToString()); }
public static void ImportDataToTable(SdbTableMapping.TableMapping tableMapping, StaticDB.Table table, NpgsqlConnection conn = null) { if (conn == null) { conn = Conn; } var copySql = CreateCoprySql(tableMapping); using (var writer = conn.BeginBinaryImport(copySql)) { for (var i = 0; i < table.Rows.Count; i++) { var row = table.Rows[i]; writer.StartRow(); for (var index = 0; index < row.Fields.Count; index++) { var field = row.Fields[index]; var fieldinfo = tableMapping.Colums[index]; if (field == null) { writer.WriteNull(); } else if (IsBasicType(fieldinfo.SdbType)) { if (fieldinfo.SdbType == StaticDB.DBType.UShort) { writer.Write(Convert.ToInt32(field)); } else if (fieldinfo.SdbType == StaticDB.DBType.UInt) { writer.Write(Convert.ToInt64(field)); } else if (fieldinfo.SdbType == StaticDB.DBType.ULong) { var val = (long)(ulong)field; writer.Write(val); } else if (fieldinfo.SdbType == StaticDB.DBType.Half) { writer.Write(Convert.ToSingle(field)); } else if (field is string fieldStr) // clean nulls from the end of strings { var val = fieldStr.Replace("\0", ""); writer.Write(val); } else if (field is char fieldChar) { if (fieldChar == '\0') // feels abit eh, TODO: check incase of char trouble { Console.WriteLine($"Got an invalid char {fieldChar} in table {tableMapping.Name} ({tableMapping.Idx}) on colum {fieldinfo.Name} row {i}"); writer.Write(' '); } else { writer.Write(fieldChar); } } else { writer.Write(field); } } else if (IsCustomType(fieldinfo.SdbType)) { writer.Write(field); } else if (IsArrayType(fieldinfo.SdbType)) { if (field is List <ushort> shortsList) { var val = shortsList.Select(x => Convert.ToInt32(x)).ToList(); writer.Write(val); } else if (field is List <uint> intsList) { var val = intsList.Select(x => Convert.ToInt64(x)).ToList(); writer.Write(val); } else { writer.Write(field); } } else { Console.WriteLine("Unhandled type"); writer.WriteNull(); } } } writer.Complete(); } Console.WriteLine($"Imported data to table {tableMapping.Name} ({tableMapping.Idx})"); }