public void TruncateWmsPerformanceTable(string tableName)
{
var verticaQuery = "DELETE FROM extr_manual." + tableName;
using (var verticaConnection = new VerticaConnection(_connectionString))
{
try
{
verticaConnection.Open();
using (var verticaTransaction = verticaConnection.BeginTransaction())
{
try
{
using (var verticaCommand = new VerticaCommand(verticaQuery, verticaConnection))
{
verticaCommand.ExecuteNonQuery();
verticaTransaction.Commit();
}
}
catch (Exception)
{
verticaTransaction.Rollback();
// TASK : TruncateWmsPerformanceTable Exception details may be logged for further analysis
throw;
}
}
}
finally
{
verticaConnection.Close();
}
}
}
public bool ExecuteUpdateQuery(string query, Dictionary <string, object> parameters)
{
bool updated = false;
if (!IsConnectionOpened())
{
return(updated);
}
try {
VerticaCommand update = new VerticaCommand(query, connection);
if (parameters.Count > 0)
{
foreach (KeyValuePair <string, object> parameter in parameters)
{
update.Parameters.Add(new VerticaParameter(parameter.Key, parameter.Value));
}
}
updated = update.ExecuteNonQuery() > 0 ? true : 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);
connection.Close();
}
return(updated);
}
public static int InsertSampleTable(SampleTable pData, VerticaConnection pConnection)
{
using (var comm = new VerticaCommand())
{
comm.Connection = pConnection;
comm.CommandText = Queries.InsertSampleTable;
comm.Parameters.AddRange(new[]
{
new VerticaParameter("@pID", VerticaType.BigInt, pData.Id),
new VerticaParameter("@pText", VerticaType.VarChar, pData.Text)
}
);
return comm.ExecuteNonQuery();
}
}
public static int DeleteSampleTableById(SampleTable pData, VerticaConnection pConnection)
{
using (var comm = new VerticaCommand())
{
comm.Connection = pConnection;
comm.CommandText = Queries.DeleteSampleTableById;
comm.Parameters.AddRange(new[]
{
new VerticaParameter("@pID", VerticaType.Numeric, pData.Id)
}
);
return comm.ExecuteNonQuery();
}
}
/// <summary>
/// Runs a query that does not return results (i.e. a write operation)
/// </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 SqlNonQuery(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();
cmd.Connection = conn;
cmd.CommandTimeout = commandTimeout;
LogCommand(cmd);
numrows = cmd.ExecuteNonQuery();
}
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);
}