public RowCounts ApplyTableChanges(TableConf table, TableConf archiveTable, string dbName, long CTID, string CTDBName, bool isConsolidated) { var cmds = new List <InsertDelete>(); cmds.Add(BuildApplyCommand(table, dbName, CTDBName, CTID)); if (archiveTable != null) { cmds.Add(BuildApplyCommand(archiveTable, dbName, CTDBName, CTID)); } var connStr = buildConnString(); var rowCounts = new RowCounts(0, 0); using (var conn = new VerticaConnection(connStr)) { conn.Open(); VerticaTransaction trans = conn.BeginTransaction(); foreach (var id in cmds) { id.delete.Connection = conn; id.delete.Transaction = trans; id.delete.CommandTimeout = Config.QueryTimeout; logger.Log(id.delete.CommandText, LogLevel.Trace); int deleted = id.delete.ExecuteNonQuery(); logger.Log(new { Table = table.Name, message = "Rows deleted: " + deleted }, LogLevel.Info); id.insert.Connection = conn; id.insert.Transaction = trans; id.insert.CommandTimeout = Config.QueryTimeout; logger.Log(id.insert.CommandText, LogLevel.Trace); int inserted = id.insert.ExecuteNonQuery(); logger.Log(new { Table = table.Name, message = "Rows inserted: " + inserted }, LogLevel.Info); rowCounts = new RowCounts(rowCounts.Inserted + inserted, rowCounts.Deleted + deleted); } trans.Commit(); } return(rowCounts); }
public void Send(ref System.Diagnostics.Stopwatch sw) { try { sw.Start(); VerticaTransaction txn = client.BeginTransaction(); foreach (VerticaCommand c in coms) { c.Transaction = txn; c.ExecuteNonQueryAsync(); } txn.Commit(); } catch (Exception e) { Console.WriteLine(e.Message); } }
/// <summary> /// Runs a query that does not return results (i.e. a write operation), with transaction /// </summary> /// <param name="cmd">VerticaCommand to run</param> /// <param name="timeout">Timeout (higher than selects since some writes can be large)</param> /// <returns>The number of rows affected</returns> internal int SqlNonQueryWithTransaction(VerticaCommand cmd, int?timeout = null) { int commandTimeout = timeout ?? Config.QueryTimeout; //build connection string based on server/db info passed in string connStr = buildConnString(); int numrows; //using block to avoid resource leaks using (VerticaConnection conn = new VerticaConnection(connStr)) { //open database connection conn.Open(); VerticaTransaction txn = conn.BeginTransaction(); cmd.Connection = conn; cmd.CommandTimeout = commandTimeout; LogCommand(cmd); numrows = cmd.ExecuteNonQuery(); txn.Commit(); } return(numrows); }
public bool ExecuteUpdateQuery( string query, bool isTreatmentsDetails, string fileInfo) { bool updatedCorrected = true; if (!IsConnectionOpened()) { return(updatedCorrected); } string now = DateTime.Now.ToString("yyyyMMddHHmmss"); using (VerticaTransaction transaction = connection.BeginTransaction()) { using (VerticaCommand update = new VerticaCommand(query, connection)) { if (isTreatmentsDetails) { DataTable dataTable = Program.FileContentTreatmentsDetails; if (dataTable == null) { return(false); } for (int i = 0; i < dataTable.Rows.Count; i++) { try { update.Parameters.Clear(); bool skip = false; foreach (Program.Header header in Program.headers) { if (Debugger.IsAttached) { if (header.DbField == "ordtid" && string.IsNullOrEmpty(dataTable.Rows[i][header.DbField].ToString())) { skip = true; break; } } update.Parameters.Add(new VerticaParameter(header.DbField, dataTable.Rows[i][header.DbField])); } if (skip) { continue; } update.Parameters.Add(new VerticaParameter("@etl_pipeline_id", "CleanedTreatmentsDetailsImport" + "_" + now)); update.Parameters.Add(new VerticaParameter("@file_info", fileInfo)); update.Parameters.Add(new VerticaParameter("@loadingUserName", Environment.UserName + "@" + Environment.MachineName)); if (dataTable.Columns.Contains("average_discount")) { update.Parameters.Add(new VerticaParameter("@average_discount", dataTable.Rows[i]["average_discount"])); } else { update.Parameters.Add(new VerticaParameter("@average_discount", null)); } if (dataTable.Columns.Contains("amount_total_with_average_discount")) { update.Parameters.Add(new VerticaParameter("@amount_total_with_average_discount", dataTable.Rows[i]["amount_total_with_average_discount"])); } else { update.Parameters.Add(new VerticaParameter("@amount_total_with_average_discount", null)); } if (update.ExecuteNonQuery() == 0) { updatedCorrected = false; } } catch (Exception e) { string subject = "Ошибка выполнения запроса к БД"; string body = e.Message + Environment.NewLine + e.StackTrace; SystemMail.SendMail(subject, body, Properties.Settings.Default.MailCopy); Logging.ToLog(subject + " " + body); if (bw != null) { bw.ReportProgress(0, subject + " " + body); } Logging.ToLog("---Исходные данные:"); foreach (Program.Header header in Program.headers) { Logging.ToLog(header.DbField + " | " + (dataTable.Rows[i][header.DbField] == null ? "null" : dataTable.Rows[i][header.DbField].ToString())); } transaction.Rollback(); connection.Close(); return(false); } } } else { foreach (ItemProfitAndLoss item in Program.FileContentProfitAndLoss) { try { update.Parameters.Clear(); update.Parameters.Add(new VerticaParameter("@object_name", item.ObjectName)); update.Parameters.Add(new VerticaParameter("@period_year", item.PeriodYear)); update.Parameters.Add(new VerticaParameter("@period_type", item.PeriodType)); update.Parameters.Add(new VerticaParameter("@group_name_level_1", item.GroupNameLevel1)); update.Parameters.Add(new VerticaParameter("@group_name_level_2", item.GroupNameLevel2)); update.Parameters.Add(new VerticaParameter("@group_name_level_3", item.GroupNameLevel3)); update.Parameters.Add(new VerticaParameter("@value", item.Value)); update.Parameters.Add(new VerticaParameter("@group_sorting_order", item.GroupSortingOrder)); update.Parameters.Add(new VerticaParameter("@object_sorting_order", item.ObjectSrotingOrder)); update.Parameters.Add(new VerticaParameter("@quarter", item.Quarter)); update.Parameters.Add(new VerticaParameter("@has_data", item.HasData)); update.Parameters.Add(new VerticaParameter("@etl_pipeline_id", "CleanedTreatmentsDetailsImport" + "_" + now)); update.Parameters.Add(new VerticaParameter("@file_info", fileInfo)); update.Parameters.Add(new VerticaParameter("@loadingUserName", Environment.UserName + "@" + Environment.MachineName)); if (update.ExecuteNonQuery() == 0) { updatedCorrected = false; } } catch (Exception e) { string subject = "Ошибка выполнения запроса к БД"; string body = e.Message + Environment.NewLine + e.StackTrace; if (e.InnerException != null) { body += Environment.NewLine + e.InnerException.Message + Environment.NewLine + e.InnerException.StackTrace; } SystemMail.SendMail(subject, body, Properties.Settings.Default.MailCopy); Logging.ToLog(subject + " " + body); if (bw != null) { bw.ReportProgress(0, subject + " " + body); } Logging.ToLog("---Исходные данные:"); Logging.ToLog(item.ToString()); transaction.Rollback(); connection.Close(); return(false); } } } } transaction.Commit(); } return(updatedCorrected); }