public void CopyDataTableToDB(DataTable table, string destinationTable)
{
var filename = table.TableName + "preload.csv";
using (TextWriter writer = new StreamWriter(filename))
{
Rfc4180Writer.WriteDataTable(table, writer, false);
}
using (MySqlConnection conn = new MySqlConnection(_ConnectionString))
{
conn.Open();
// CreateTable(dt.TableName, dt, conn);
var bl = new MySqlBulkLoader(conn);
bl.TableName = destinationTable;
bl.FieldTerminator = ",";
bl.FieldQuotationCharacter = '"';
bl.LineTerminator = "\r\n";
bl.FileName = filename;
// bl.NumberOfLinesToSkip = 1;
bl.Load();
File.Delete(filename);
}
}
public void CopyDataTableToDB(DataTable table, string destinationTable)
{
try
{
//COPY pop_grid(GRID_ID, POP_TOT, YEAR, METHD_CL, CNTR_CODE, DATA_SRC) from 'C:\...\popgrid.csv' DELIMITERS ',' CSV;
var filename = table.TableName + "preload.csv";
filename = @"C:\TAFC\\" + filename;
using (TextWriter writer = new StreamWriter(filename))
{
Rfc4180Writer.WriteDataTable(table, writer, false);
}
//foreach (DataColumn column in destdt.Columns)
//{
// fields.Add(column.ColumnName);
//}
// string headers = string.Join("\",\"", fields);
// filename = AppDomain.CurrentDomain.BaseDirectory + "\\" + filename;
using (NpgsqlConnection conn = new NpgsqlConnection(_ConnectionString))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = conn;
var sql = "COPY " + destinationTable + " FROM '" + filename + "' DELIMITERS ',' CSV";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
try
{
var destdt = getData("select * from " + destinationTable + " limit 1");
destdt.Clear();
foreach (DataRow row in table.Rows)
{
var newrow = destdt.NewRow();
foreach (DataColumn column in table.Columns)
{
newrow[column.ColumnName] = row[column.ColumnName];
}
destdt.Rows.Add(newrow);
}
CopyDataTableToDB(destdt, destinationTable);
}
catch
{
throw (e);
}
}
}