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