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); }
/// <summary> /// Build the apply command /// </summary> /// <param name="table">Table to apply changes to</param> /// <param name="schema">Vertica schema to apply changes to</param> /// <param name="CTDBName">CT database name, which is actually Vertica CT schema name</param> /// <param name="CTID">Change tracking ID</param> /// <returns>InsertDelete object representing the apply command</returns> private InsertDelete BuildApplyCommand(TableConf table, string schema, string CTDBName, long CTID) { // NOTE: Vertica does not like the first alias P in the following command: // DELETE FROM a.b P WHERE EXISTS (SELECT 1 FROM c.d CT WHERE P.id = CT.id) // instead, the first alias has to be removed, thus: // DELETE FROM a.b WHERE EXISTS (SELECT 1 FROM c.d CT WHERE a.b.id = CT.id) // and in the case of multi-column primary key: // DELETE FROM a.b WHERE EXISTS (SELECT 1 FROM c.d CT WHERE a.b.id1 = CT.id1 AND a.b.id2 = CT.id2) string verticaTableName = string.Format("{0}.{1}", schema, table.Name); string delete = string.Format( @"DELETE FROM {0} WHERE EXISTS (SELECT 1 FROM {1}.{2} CT WHERE {3});", verticaTableName, CTDBName, table.ToCTName(CTID), table.getNoAliasPkList(verticaTableName)); // since Vertica does not have the reserved words issue // we are using table.SimpleColumnList string insert = string.Format( @"INSERT INTO {0} ({1}) SELECT {1} FROM {2}.{3} CT WHERE NOT EXISTS (SELECT 1 FROM {0} P WHERE {4}) AND CT.sys_change_operation IN ( 'I', 'U' );", verticaTableName, table.SimpleColumnList, CTDBName, table.ToCTName(CTID), table.PkList); var deleteCmd = new VerticaCommand(delete); var insertCmd = new VerticaCommand(insert); return(new InsertDelete(insertCmd, deleteCmd)); }
public void ReadStaticC(int v, TimeSpan ts) { VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder(); builder.Database = "mydb"; builder.User = "******"; builder.Password = "******"; builder.Host = "10.58.44.163"; VerticaConnection con = new VerticaConnection(builder.ToString()); con.Open(); StringBuilder c = new StringBuilder(); string content = PrepareReadMultiple(v, ts, c); VerticaCommand com = new VerticaCommand(content); com.Connection = con; while (ReadTest.Running) { VerticaDataReader r = com.ExecuteReader(); if (!r.HasRows) { System.Console.WriteLine("Error: Vertica ReadMultiple received empty"); } r.Close(); System.Threading.Thread.Sleep(100); } }
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(); } } }
/// <summary> /// Check if the datatype of the column [column] in [dbName].[table] in Vertica DB /// matches the given data type /// We treat dbName as the schema in Vertica /// </summary> /// <param name="dbName">Database name</param> /// <param name="schema">Schema name</param> /// <param name="table">Table name</param> /// <param name="column">Column name</param> /// <param name="dataType">Data type to match</param> /// <returns>Boolean representing whether the column exists</returns> private bool ColumnDatatypeMatches(string dbName, string schema, string table, string column, string dataType) { // NOTE: for our scenario (MSSQL relay, Vertica slave) // MSSQL database name becomes Vertica schema name // and MSSQL schema name is ignored // NOTE: not sure why this same method is public in NetezzaDataUtils class // where the method is not used anywhere outside the class. We are keeping // this method private here, as is the case in MSSQLDataUtils and MySQLDataUtils string sql = string.Format( @"SELECT data_type FROM v_catalog.columns WHERE UPPER(table_schema) = UPPER('{0}') AND UPPER(table_name) = UPPER('{1}') AND UPPER(column_name) = UPPER('{2}')", dbName, table, column); var cmd = new VerticaCommand(sql); try { string dataTypeString = SqlQueryToScalar <string>(cmd); return(dataTypeString.Equals(dataType, StringComparison.OrdinalIgnoreCase)); } catch (Exception e) { logger.Log("Exception while matching data type for column [" + dbName + "].[" + table + "].[" + column + "]. " + e.Message, LogLevel.Debug); return(false); } }
private void mCheckInsert() { if (checkcom != null) { return; } checkcom = new VerticaCommand("select value from mydata where variable_id = 'testinstance_" + max_var + "' AND timestamp = '" + ts_max.ToString("yyyy-MM-dd hh:mm:ss.ffffff", dtfi) + "'"); checkcom.Connection = client; }
/// <summary> /// Parse a SQL query, substituting parameters for their values. /// </summary> /// <param name="cmd">VerticaCommand to parse</param> /// <returns>The parsed query</returns> private string ParseCommand(VerticaCommand cmd) { string query = cmd.CommandText; foreach (VerticaParameter p in cmd.Parameters) { query = query.Replace(p.ParameterName, "'" + p.Value.ToString() + "'"); } return(query); }
public bool DropTableIfExists(string dbName, string table, string schema) { // here we are still calling the public method defined by the interface if (CheckTableExists(dbName, table, schema)) { // for Vertica, the source database name becomes the schema name string drop = string.Format("DROP TABLE {0}.{1};", dbName, table); var cmd = new VerticaCommand(drop); return(SqlNonQuery(cmd) > 0); } return(false); }
/// <summary> /// Writes data from the given data file to the destination Vertica database /// </summary> /// <param name="fileName">Name of data file to copy from</param> /// <param name="dbName">Database name</param> /// <param name="table">Table name to write to</param> /// <param name="timeout">Timeout</param> public void BulkCopy(string fileName, string dbName, string table, int timeout) { // reference: http://goo.gl/8R6UXJ string copyStatement = string.Format( "COPY {0}.{1} FROM '{2}' DELIMITER '|' NULL '' ENCLOSED BY '' RECORD TERMINATOR E'\r\n' ABORT ON ERROR DIRECT STREAM NAME 'Tesla' NO COMMIT", dbName, table, fileName); VerticaCommand cmd = new VerticaCommand(copyStatement); SqlNonQueryWithTransaction(cmd); }
public static IEnumerable<SampleTable> SelectSampleTable(VerticaConnection pConnection) { var vResult = new List<SampleTable>(); using (var dt = new DataTable()) using (var comm = new VerticaCommand { CommandText = Queries.SelectSampleTable, Connection = pConnection}) { dt.Load(comm.ExecuteReader()); vResult.AddRange(Enumerable.Select(dt.AsEnumerable(), dataRow => new SampleTable(dataRow))); } return vResult; }
/// <summary> /// Runs a sql query and returns first column and row from results as specified type /// </summary> /// <param name="cmd">VerticaCommand to run</param> /// <param name="timeout">Query timeout</param> /// <returns>The value in the first column and row, as the specified type</returns> private T SqlQueryToScalar <T>(VerticaCommand cmd, int?timeout = null) { DataTable result = SqlQuery(cmd, timeout); // return result in first column and first row as specified type T toRet; try { toRet = (T)result.Rows[0][0]; } catch (InvalidCastException) { throw new Exception("Unable to cast value " + result.Rows[0][0].ToString() + " to type " + typeof(T) + " when running query: " + ParseCommand(cmd)); } return(toRet); }
public ActionResult <IEnumerable <string> > Get() { using (var db = new VerticaConnection("Host=localhost:37878;Database=docker;User=dbadmin;Password=1234;")) { db.Open(); using (var comm = new VerticaCommand { CommandText = "Select * from TestTable;", Connection = db }) { var dt = new DataTable(); dt.Load(comm.ExecuteReader()); } db.Close(); } return(new string[] { "value1", "value2" }); }
public void PrepareReadMultipleLatest(int v, TimeSpan ts) { StringBuilder con = new StringBuilder(); con.Append("select * from mydata where variable_id in ("); for (int i = 1; i <= v; i++) { con.Append("'testinstance_" + i + "'"); if (i != v) { con.Append(","); } } con.Append(") and timestamp >= now() - interval '" + ts.TotalSeconds + "'"); readmulcom = new VerticaCommand(con.ToString()); readmulcom.Connection = client; }
public static int UpdateSampleTable(SampleTable pData, VerticaConnection pConnection) { using (var comm = new VerticaCommand()) { comm.Connection = pConnection; comm.CommandText = Queries.UpdateSampleTable; comm.Parameters.AddRange(new[] { new VerticaParameter("@pID", VerticaType.Numeric, pData.Id), new VerticaParameter("@pText", VerticaType.VarChar, pData.Text) } ); return comm.ExecuteNonQuery(); } }
protected bool CheckTableExists(string schema, string table) { VerticaCommand cmd = new VerticaCommand(); // NOTE: Vertica is data case-sensitive, and command case-insensitive // which means: // for data, "SELECT ... WHERE col = 'A'" is not the same as "SELECT ... WHERE col = 'a'", // for command, "CREATE TABLE t ..." will not work if a table [T] already exists cmd.CommandText = "SELECT table_schema, table_name FROM v_catalog.tables " + "WHERE UPPER(table_schema) = UPPER(@TABLE_SCHEMA) " + "AND UPPER(table_name) = UPPER(@TABLE_NAME)"; cmd.Parameters.Add(new VerticaParameter("TABLE_SCHEMA", VerticaType.VarChar, schema)); cmd.Parameters.Add(new VerticaParameter("TABLE_NAME", VerticaType.VarChar, table)); var res = SqlQuery(cmd); return(res.Rows.Count > 0); }
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); }
// TASK : exception handling for method GetLastUpdateDateOfTable private DateTime GetLastUpdateDateOfTable(string tableName, VerticaConnection verticaConnection) { DateTime result = DateTime.MinValue; string verticaTableLastUpdateQuery = string.Format("SELECT MAX(insert_date) AS LastUpdateDate FROM extr_manual.{0}", tableName); using (var verticaTableLastUpdateCommand = new VerticaCommand(verticaTableLastUpdateQuery, verticaConnection)) { var verticaTableLastUpdateReader = verticaTableLastUpdateCommand.ExecuteReader(); while (verticaTableLastUpdateReader.Read()) { DateTime.TryParse(verticaTableLastUpdateReader[0].ToString(), out result); } } return(result); }
// The refresh command configured will execute if Config.RefreshViews has a configuration for the dbName tableName pair private void RefreshViews(string dbName, string tableName) { var refresh = Config.RefreshViews.Where(r => r.Db.ToLower() == dbName.ToLower() && r.TableName.ToLower() == tableName.ToLower()).FirstOrDefault(); if (refresh == null) { logger.Log("No refresh view config is available for [" + dbName + "].[" + tableName + "]. Abort refreshing views.", LogLevel.Debug); return; } string sql = refresh.Command; var cmd = new VerticaCommand(sql); try { SqlNonQuery(cmd); } catch (Exception) { throw new Exception("Please check any pending schema changes to be applied on Vertica before refreshing the view::" + dbName + ".." + refresh.ViewName); } }
public void RenameColumn(TableConf t, string dbName, string columnName, string newColumnName, string historyDB) { // rename the column if it exists if (CheckColumnExists(dbName, t.SchemaName, t.Name, columnName)) { string sql = string.Format( @"ALTER TABLE {0}.{1} RENAME COLUMN {2} TO {3};", dbName, t.Name, columnName, newColumnName); var cmd = new VerticaCommand(sql); SqlNonQuery(cmd); if (ShallRefreshViews(t, dbName, newColumnName, action: "RenameColumn", columnShallExist: true)) { RefreshViews(dbName, t.Name); } } }
public void CopyTableDefinition(string sourceDB, string sourceTableName, string schema, string destDB, string destTableName, string originalTableName = null) { var cols = GetColumns(sourceDB, sourceTableName, schema, originalTableName ?? sourceTableName); // get the table config object var table = Config.TableByName(originalTableName); List <TableConf> tableConfs = new List <TableConf>() { table }; Dictionary <TableConf, IList <TColumn> > allColumnsByTable = sourceDataUtils.GetAllFields(sourceDB, tableConfs.ToDictionary(t => t, t => t.Name)); string pkList = string.Join(",", cols.Where(c => c.isPk).Select(c => c.name)); if (String.IsNullOrEmpty(pkList)) { string err = "Primary Key information is required for Vertica, but absent"; logger.Log(err, LogLevel.Error); throw new Exception("Copy table definition error: " + err); } string vCreate = string.Format( @"CREATE TABLE {0}.{1} ( {2} NOT NULL, PRIMARY KEY ({3}) ) ORDER BY {3} SEGMENTED BY HASH({3}) ALL NODES KSAFE {4};", destDB, // for Vertica, the "database" becomes the "schema" destTableName, string.Join(",", cols), pkList, Config.VerticaKsafe); logger.Log(vCreate, LogLevel.Trace); destDataUtils.DropTableIfExists(destDB, destTableName, schema); var cmd = new VerticaCommand(vCreate); destDataUtils.SqlNonQuery(cmd); }
public void DropColumn(TableConf t, string dbName, string columnName, string historyDB) { // NOTE: Reserved word should not be a problem for Vertica // in case we found it is some point in the future, enable mapping // columnName = MapReservedWord(columnName); if (CheckColumnExists(dbName, t.SchemaName, t.Name, columnName)) { string sql = string.Format( @"ALTER TABLE {0}.{1} DROP COLUMN {2} RESTRICT;", dbName, t.Name, columnName); var cmd = new VerticaCommand(sql); SqlNonQuery(cmd); if (ShallRefreshViews(t, dbName, columnName, action: "DropColumn", columnShallExist: false)) { RefreshViews(dbName, t.Name); } } }
public void PrepareWriteData(int v) { if (v > variables) { checkcom = null; } variables = v; coms.Clear(); System.Random r = new Random(); StringBuilder con = new StringBuilder(); con.Append("insert /*+ AUTO */ into mydata\n\t"); VerticaCommand command; for (int i = 1; i <= variables; i++) { if (i >= 1000 && (i % 1000 == 0)) { command = client.CreateCommand(); command.CommandText = con.ToString(); coms.Add(command); con.Clear(); con.Append("insert /*+ AUTO */ into mydata\n\t"); } DateTime ts = DateTime.UtcNow; int upVal = r.Next(1, 1000000); if (i == max_var) { ts_max = ts; } con.Append(" select 'testinstance_" + i + "','OK', TIMESTAMP '" + ts.ToString("yyyy-MM-dd hh:mm:ss.ffffff", dtfi) + "', " + upVal.ToString()); if (i != variables && ((i + 1) % 1000 != 0)) { con.Append(" UNION "); } } command = client.CreateCommand(); command.CommandText = con.ToString(); command.Prepare(); coms.Add(command); }
/// <summary> /// Runs a sql query and returns results as requested type /// </summary> /// <param name="cmd">VerticaCommand to run</param> /// <param name="timeout">Query timeout</param> /// <returns>DataTable object representing the result</returns> internal DataTable SqlQuery(VerticaCommand cmd, int?timeout = null) { int commandTimeout = timeout ?? Config.QueryTimeout; string connStr = buildConnString(); using (VerticaConnection conn = new VerticaConnection(connStr)) { conn.Open(); cmd.Connection = conn; cmd.CommandTimeout = commandTimeout; LogCommand(cmd); DataSet ds = new DataSet(); VerticaDataAdapter da = new VerticaDataAdapter(cmd); //this is where the query is run da.Fill(ds); //return the result, which is the first DataTable in the DataSet return(ds.Tables[0]); } }
/// <summary> /// Check if a column [column] exists in [dbName].[table] in Vertica DB /// We treat dbName as the schema in Vertica /// </summary> /// <param name="dbName">Database name</param> /// <param name="schema">Schema name</param> /// <param name="table">Table name</param> /// <param name="column">Column name</param> /// <returns>Boolean representing whether the column exists</returns> private bool CheckColumnExists(string dbName, string schema, string table, string column) { // NOTE: for our scenario (MSSQL relay, Vertica slave) // MSSQL database name becomes Vertica schema name // and MSSQL schema name is ignored // NOTE: not sure why this same method is public in NetezzaDataUtils class // where the method is not used anywhere outside the class. We are keeping // this method private here, as is the case in MSSQLDataUtils and MySQLDataUtils string sql = string.Format( @"SELECT 1 FROM v_catalog.columns WHERE UPPER(table_schema) = UPPER('{0}') AND UPPER(table_name) = UPPER('{1}') AND UPPER(column_name) = UPPER('{2}')", dbName, table, column); var cmd = new VerticaCommand(sql); var res = SqlQuery(cmd); return(res.Rows.Count > 0); }
public void AddColumn(TableConf t, string dbName, string columnName, DataType dataType, string historyDB) { // NOTE: Reserved word should not be a problem for Vertica // in case we found it is some point in the future, enable mapping // columnName = MapReservedWord(columnName); if (!CheckColumnExists(dbName, t.SchemaName, t.Name, columnName)) { string destDataType = MapColumnTypeName(Config.RelayType, dataType, t.getColumnModifier(columnName)); string sql = string.Format( @"ALTER TABLE {0}.{1} ADD {2} {3};", dbName, t.Name, columnName, destDataType); var cmd = new VerticaCommand(sql); SqlNonQuery(cmd); if (ShallRefreshViews(t, dbName, columnName, action: "AddColumn", columnShallExist: true)) { RefreshViews(dbName, t.Name); } } }
public DataTable GetDataTable(string query, Dictionary <string, object> parameters = null) { DataTable dataTable = new DataTable(); if (!IsConnectionOpened()) { return(dataTable); } try { using (VerticaCommand command = new VerticaCommand(query, connection)) { if (parameters != null && parameters.Count > 0) { foreach (KeyValuePair <string, object> parameter in parameters) { command.Parameters.Add(new VerticaParameter(parameter.Key, parameter.Value)); } } using (VerticaDataAdapter fbDataAdapter = new VerticaDataAdapter(command)) fbDataAdapter.Fill(dataTable); } } 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(); if (bw != null) { bw.ReportProgress(0, subject + " " + body); } } return(dataTable); }
public void ModifyColumn(TableConf t, string dbName, string columnName, DataType dataType, string historyDB) { // modify the column if it exists if (CheckColumnExists(dbName, t.SchemaName, t.Name, columnName)) { string destDataType = MapColumnTypeName(Config.RelayType, dataType, t.getColumnModifier(columnName)); if (!ColumnDatatypeMatches(dbName, t.SchemaName, t.Name, columnName, destDataType)) { // do not modify if the destination column already has the right data type string sql = string.Format( @"ALTER TABLE {0}.{1} ALTER COLUMN {2} SET DATA TYPE {3};", dbName, t.Name, columnName, destDataType); var cmd = new VerticaCommand(sql); SqlNonQuery(cmd); if (ShallRefreshViews(t, dbName, columnName, action: "ModifyColumn", columnShallExist: true, dataType: destDataType)) { RefreshViews(dbName, t.Name); } } } }
public InsertDelete(VerticaCommand insert, VerticaCommand delete) { this.insert = insert; this.delete = delete; }
public IEnumerable <WmsPerformanceTableDto> GetWmsPerformanceTableInfos( IEnumerable <string> ignoredColumnTypes = null) { // TASK : Remove dummy item requirement. var queryResult = new[] { new { TableName = string.Empty, ColumnName = string.Empty, DataType = string.Empty, IsNullable = true, LastUpdateDate = DateTime.MinValue } }.ToList(); var queryLastDateResult = new[] { new { TableName = string.Empty, LastUpdateDate = DateTime.MinValue } }.ToList(); const string verticaQuery = "SELECT table_name, column_name, data_type, is_nullable FROM columns WHERE table_schema = 'extr_manual' ORDER BY ordinal_position"; using (var verticaConnection = new VerticaConnection(_connectionString)) { try { // verticaConnection Open failures may be handled with catch block and may be logged for further analysis // TASK : catch and handle verticaConnection Open failures verticaConnection.Open(); using (var verticaCommand = new VerticaCommand(verticaQuery, verticaConnection)) { var reader = verticaCommand.ExecuteReader(); while (reader.Read()) { // verticaQuery modifications may break the code below. var columnType = reader[2].ToString(); // These filtering below may be done with appending into verticaQuery // TASK : filter column type with appending into verticaQuery. Ref : Efficiency //Skip the ignored column types. if (ignoredColumnTypes != null && ignoredColumnTypes.Contains(columnType)) { continue; } // At first tableName finding, add its LastUpdateDate to queryLastDateResult. // These operation is differs from the methods responsibility (GetWmsPerformanceTableInfos). LastUpdateDate calculation per table can be done in grouping section below. Also after closing current verticaConnection.. // TASK : Refactor LastUpdateDate calculation by taking these functionality into another class or change the execution place to grouping section. Ref : Single Responsibility if (!queryLastDateResult.Any(x => x.TableName == reader[0].ToString())) { queryLastDateResult.Add(new { TableName = reader[0].ToString(), LastUpdateDate = GetLastUpdateDateOfTable(reader[0].ToString(), verticaConnection) }); } queryResult.Add(new { TableName = reader[0].ToString(), ColumnName = reader[1].ToString(), DataType = columnType, IsNullable = Convert.ToBoolean(reader[3]), LastUpdateDate = queryLastDateResult.SingleOrDefault(x => x.TableName == reader[0].ToString()).LastUpdateDate }); } } } finally { verticaConnection.Close(); } } //Remove the initial dummy item. queryResult.RemoveAt(0); // TASK : Preparing the result type may be handled in the reader while loop, for efficiency //Prepare the result by using anonymous query result type. var tables = queryResult.GroupBy(e => e.TableName).Select(e => new WmsPerformanceTableDto { TableName = e.Key, LastUpdateDate = e.FirstOrDefault(c => c.TableName == e.Key).LastUpdateDate, Columns = e.Select(c => new WmsPerformanceColumnDto { ColumnName = c.ColumnName, DataType = c.DataType, IsNullable = c.IsNullable, }).ToList() }).ToList(); return(tables); }
/// <summary> /// Log a command /// </summary> /// <param name="cmd">VerticaCommand to log</param> private void LogCommand(VerticaCommand cmd) { logger.Log("Executing query: " + ParseCommand(cmd), LogLevel.Debug); }
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); }
public void CopyTableDefinition(string sourceDB, string sourceTableName, string schema, string destDB, string destTableName, string originalTableName = null) { var cols = GetColumns(sourceDB, sourceTableName, schema, originalTableName ?? sourceTableName); // get the table config object var table = Config.TableByName(originalTableName); List<TableConf> tableConfs = new List<TableConf>() { table }; Dictionary<TableConf, IList<TColumn>> allColumnsByTable = sourceDataUtils.GetAllFields(sourceDB, tableConfs.ToDictionary(t => t, t => t.Name)); string pkList = string.Join(",", cols.Where(c => c.isPk).Select(c => c.name)); if (String.IsNullOrEmpty(pkList)) { string err = "Primary Key information is required for Vertica, but absent"; logger.Log(err, LogLevel.Error); throw new Exception("Copy table definition error: " + err); } string vCreate = string.Format( @"CREATE TABLE {0}.{1} ( {2} NOT NULL, PRIMARY KEY ({3}) ) ORDER BY {3} SEGMENTED BY HASH({3}) ALL NODES KSAFE {4};", destDB, // for Vertica, the "database" becomes the "schema" destTableName, string.Join(",", cols), pkList, Config.VerticaKsafe); logger.Log(vCreate, LogLevel.Trace); destDataUtils.DropTableIfExists(destDB, destTableName, schema); var cmd = new VerticaCommand(vCreate); destDataUtils.SqlNonQuery(cmd); }
public void PrepareReadOne(TimeSpan ts) { readonecom = new VerticaCommand("select * from mydata where variable_id = 'testinstance_1' and timestamp >= timestamp '" + Now + "' - interval '" + ts.TotalSeconds + "'"); readonecom.Connection = client; }