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