/// <summary>
/// Saves a specific DataTable with columns:(datetime, value, flag)
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
public int InsertTimeSeriesTable(DataTable dataTable)
{
/*
* UPDATE "nunit"."public"."pn_daily_jck_af2017aug15162651773" SET "datetime" = @p1, "value" = @p2, "flag" = @p3 WHERE (("datetime" = @p4) AND ((@p5 = 1 AND "value" IS NULL) OR ("value" = @p6)) AND ((@p7 = 1 AND "flag" IS NULL) OR ("flag" = @p8)))
*
*
* INSERT INTO "nunit"."public"."pn_daily_jck_af2017aug15162651773" ("datetime", "value", "flag") VALUES (@p1, @p2, @p3)
*
*
* DELETE FROM "nunit"."public"."pn_daily_jck_af2017aug15162651773" WHERE (("datetime" = @p1) AND ((@p2 = 1 AND "value" IS NULL) OR ("value" = @p3)) AND ((@p4 = 1 AND "flag" IS NULL) OR ("flag" = @p5)))
*
*/
var sql = GetEmptyTableSqlCommand(dataTable);
Performance perf = new Performance();
Logger.WriteLine("Saving " + dataTable.TableName);
DataSet myDataSet = new DataSet();
myDataSet.Tables.Add(dataTable.TableName);
NpgsqlConnection conn = new NpgsqlConnection(ConnectionString);
NpgsqlCommand myAccessCommand = new NpgsqlCommand(sql, conn);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(myAccessCommand);
var sqlUpdate = "INSERT INTO " + dataTable.TableName.ToLower()
+ " (\"datetime\", \"value\", \"flag\") VALUES (@datetime, @value, @flag)";
if (SupportsUpsert())// pg 9.5 or higher
{
sqlUpdate += " ON CONFLICT (\"datetime\") DO UPDATE SET "
+ "\"value\"= excluded.value, "
+ "\"flag\"= excluded.flag";
}
da.InsertCommand = new NpgsqlCommand(sqlUpdate, conn);
var p1 = new NpgsqlParameter("datetime", NpgsqlDbType.Timestamp, 0, "datetime");
var p2 = new NpgsqlParameter("value", NpgsqlDbType.Double, 0, "value");
var p3 = new NpgsqlParameter("flag", NpgsqlDbType.Varchar, 0, "flag");
da.InsertCommand.Parameters.Add(p1);
da.InsertCommand.Parameters.Add(p2);
da.InsertCommand.Parameters.Add(p3);
if (MapToLowerCase)
{
var map = da.TableMappings.Add(dataTable.TableName.ToLower(), dataTable.TableName);
for (int i = 0; i < dataTable.Columns.Count; i++)
{
var cn = dataTable.Columns[i].ColumnName;
map.ColumnMappings.Add(cn.ToLower(), cn);
}
}
Logger.WriteLine(sql);
int recordCount = 0;
try
{
conn.Open();
var dbTrans = conn.BeginTransaction();
recordCount = da.Update(dataTable);
dbTrans.Commit();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
string msg = "[" + dataTable.TableName + "] " + recordCount;
Logger.WriteLine(msg, "ui");
Console.WriteLine(msg);
return(recordCount);
}
public override int SaveTable(DataTable dataTable, string sql)
{
//Logger.WriteLine("Save Table with transaction");
Performance perf = new Performance();
Logger.WriteLine("Saving " + dataTable.TableName);
//Logger.WriteLine(sql);
DataSet myDataSet = new DataSet();
myDataSet.Tables.Add(dataTable.TableName);
NpgsqlConnection conn = new NpgsqlConnection(ConnectionString);
NpgsqlCommand myAccessCommand = new NpgsqlCommand(sql, conn);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(myAccessCommand);
NpgsqlCommandBuilder cb = new NpgsqlCommandBuilder(da);
da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
cb.SetAllValues = SetAllValuesInCommandBuilder;
cb.ConflictOption = ConflictOption.OverwriteChanges; // this fixes System.InvalidCastException : Specified cast is not valid.
// when reserved word (group) was a column name
if (MapToLowerCase)
{
var map = da.TableMappings.Add(dataTable.TableName.ToLower(), dataTable.TableName);
for (int i = 0; i < dataTable.Columns.Count; i++)
{
var cn = dataTable.Columns[i].ColumnName;
map.ColumnMappings.Add(cn.ToLower(), cn);
}
//PrintMapping(da);
}
SqlCommands.Add(sql);
int recordCount = 0;
//da.RowUpdating += myDataAdapter_RowUpdating;
try
{
conn.Open();
var dbTrans = conn.BeginTransaction();
da.Fill(myDataSet, dataTable.TableName);
recordCount = da.Update(dataTable);
dbTrans.Commit();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
Logger.WriteLine("Saved " + recordCount + " records in " + perf.ElapsedSeconds + "seconds", "ui");
if (SqlCommands.Count > 5000)
{
SqlCommands.Clear();
}
return(recordCount);
}