public bool ImportFrom(ExcelImportBase import)
{
string columNames = import.ColumnNames.Aggregate(new StringBuilder(),
(current, next) => current.Append(current.Length == 0 ? "" : ",").Append(next)).ToString();
using (var trx = conn.BeginTransaction())
{
int rowCount = 0;
try
{
var truncateCmd = conn.CreateCommand();
truncateCmd.CommandText = $"DELETE FROM {import.TableName}";
truncateCmd.ExecuteNonQuery();
var query = $"COPY {import.TableName} ({columNames}) FROM STDIN (FORMAT BINARY)";
using (var writer = conn.BeginBinaryImport($"COPY {import.TableName} ({columNames}) FROM STDIN (FORMAT BINARY)"))
{
rowCount = 1;
import.ResetId();
foreach (var r in import.GetRows())
{
rowCount++;
var result = import.GetValues(r);
if (result == null)
{
continue;
}
writer.WriteRow(result);
}
writer.Complete();
}
}
catch (PostgresException e)
{
trx.Rollback();
Console.WriteLine($"Excel Name: {import.eName}");
Console.WriteLine($"Row: {rowCount}");
Console.WriteLine($"Msg: {e.Message}");
Console.WriteLine($"Detail: {e.Detail}");
return(false);
}
catch (Exception e)
{
trx.Rollback();
Console.WriteLine($"Excel Name: {import.eName}");
Console.WriteLine($"Row: {rowCount}");
Console.WriteLine($"Msg: {e.Message}");
return(false);
}
trx.Commit();
}
Console.WriteLine($"{import.TableName} 완료");
return(true);
}
public static bool TryCopyToDB(PostgreSql db, ExcelImportBase import)
{
var dbRows = new List <Dictionary <String, object> >();
if (!db.ExecuteQuery($"select * from {import.TableName};", ref dbRows))
{
Console.WriteLine($"DB query failed, {import.TableName}");
return(false);
}
var dbValues = new List <object[]>();
if (!GetAllValues(dbRows, import.ColumnNames, ref dbValues))
{
Console.WriteLine($"Get db query failed, {import.TableName}");
return(false);
}
var tableValues = new List <object[]>();
import.ResetId();
foreach (var row in import.GetRows())
{
try
{
var tableValue = import.GetValues(row);
tableValues.Add(tableValue);
}
catch (Exception e)
{
errorMsgQ.Enqueue($"{import.FileName} 테이블에서 예외가 발생하였습니다.");
return(false);
}
}
var addedTable = new List <object[]>();
var missingTable = new List <object[]>();
if (!GetAllChanges(ref tableValues, ref dbValues, ref addedTable, ref missingTable))
{
Console.WriteLine($"{import.TableName} 테이블 데이터의 바뀐 내용이 없습니다.");
return(true);
}
else
{
var modifiedTable = new List <object[]>();
GetModifiedTable(ref addedTable, ref modifiedTable, ref missingTable, import.FileName);
var changes = new Tuple <ExcelImportBase, List <object[]>, List <object[]>, List <object[]> >(import, addedTable, modifiedTable, missingTable);
changesQ.Enqueue(changes);
Console.WriteLine($"{import.TableName} 테이블의 수정 사항이 생겼습니다.");
return(true);
}
}