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);
            }
        }
示例#2
0
        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);
        }
示例#3
0
        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();
                }
            }
        }
示例#4
0
        public void FromSameConnectionSequentially()
        {
            IEnumerable <Tuple <DataTable, Func <string, string> > > tuples = new List <Tuple <DataTable, Func <string, string> > >
            {
                new Tuple <DataTable, Func <string, string> >(new DataTable(), schemaName => String.Format("select * from {0}.{1};", schemaName, TestConfig.Table1)),
                new Tuple <DataTable, Func <string, string> >(new DataTable(), schemaName => String.Format("select * from {0}.{1};", schemaName, TestConfig.Table2)),
            };

            using (var conn = new VerticaConnection(TestConfig.ConnectionString))
            {
                conn.Open();
                using (var tx = conn.BeginTransaction())
                {
                    foreach (var tuple in tuples)
                    {
                        using (var command = conn.CreateCommand())
                        {
                            command.CommandText = tuple.Item2(TestConfig.SchemaName);
                            var verticaDataReader = command.ExecuteReader();
                            tuple.Item1.Load(verticaDataReader);
                        }
                    }
                    tx.Commit();
                }
            }

            Assert.True(tuples.All(t => t.Item1.Rows.Count > 0), "Data should be loaded from all rows");
        }
示例#5
0
        public VerticaClient(string host, string database, string user, string password)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder {
                Host     = host,
                Database = database,
                User     = user,
                Password = password
            };

            connection = new VerticaConnection(builder.ToString());
            IsConnectionOpened();
        }
        public void SetDomainName(string domain)
        {
            DomainName = domain;
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();

            builder.Database = "mydb";
            builder.User     = "******";
            builder.Password = "******";
            builder.Host     = "10.58.44.163";
            builder.Pooling  = true;
            client           = new VerticaConnection(builder.ToString());
            client.Open();
        }
示例#7
0
 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;
 }
        public VerticaClient(string host, string database, string user, string password, BackgroundWorker bw = null)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder {
                Host     = host,
                Database = database,
                User     = user,
                Password = password
            };

            this.bw    = bw;
            connection = new VerticaConnection(builder.ToString());
            IsConnectionOpened();
        }
示例#9
0
        // NOTE: this may need work when Vertica is used as master agent
        // currently this is not used by any workflow
        public List <TColumn> GetFieldList(string dbName, string table, string schema)
        {
            var cols = new List <TColumn>();

            using (var con = new VerticaConnection(buildConnString())) {
                con.Open();
                var t = con.GetSchema("Columns", new string[] { dbName, schema, table, null });
                foreach (DataRow row in t.Rows)
                {
                    cols.Add(new TColumn(row.Field <string>("COLUMN_NAME"), false, null, true));
                }
            }
            return(cols);
        }
示例#10
0
        public static VerticaConnection ConnectDb(DbCredentials pCredentials)
        {
            var vcsb = new VerticaConnectionStringBuilder
            {
                Database = pCredentials.DataBase,
                User = pCredentials.User,
                Password = pCredentials.Password,
                Host = pCredentials.ServerIp
            };

            var vConnection = new VerticaConnection(vcsb.ConnectionString);

            vConnection.Open();
            return vConnection;
        }
示例#11
0
        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" });
        }
示例#12
0
        public IList <long> BulkInsert(string fullFilePath, string tableName)
        {
            IList <long> rejectedRowNumbers = null;

            using (var verticaConnection = new VerticaConnection(_connectionString))
            {
                try
                {
                    verticaConnection.Open();
                    using (var verticaTransaction = verticaConnection.BeginTransaction())
                    {
                        try
                        {
                            FileStream inputFileStream = File.OpenRead(fullFilePath);
                            string     copy            = @"COPY extr_manual." + tableName + @" FROM STDIN RECORD TERMINATOR E'\r\n' DELIMITER E'|' ENFORCELENGTH NO COMMIT";

                            VerticaCopyStream stream = new VerticaCopyStream(verticaConnection, copy);
                            stream.Start();
                            stream.AddStream(inputFileStream, false);
                            stream.Execute();
                            long insertedRowsCount = stream.Finish();
                            rejectedRowNumbers = stream.Rejects;

                            if (rejectedRowNumbers.Count == 0)
                            {
                                verticaTransaction.Commit();
                            }
                            else
                            {
                                verticaTransaction.Rollback();
                            }
                        }
                        catch (Exception ex)
                        {
                            verticaTransaction.Rollback();
                            // TASK : BulkInsert Exception details may be logged for further analysis
                            throw;
                        }
                    }
                }
                finally
                {
                    verticaConnection.Close();
                }
            }
            return(rejectedRowNumbers);
        }
示例#13
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();
            }
        }
示例#14
0
        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();
            }
        }
示例#15
0
        /// <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);
        }
示例#16
0
        // 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);
        }
示例#17
0
        public IEnumerable <TTable> GetTables(string dbName)
        {
            var tables = new List <TTable>();

            using (var con = new VerticaConnection(buildConnString())) {
                con.Open();
                var t = con.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
                foreach (DataRow row in t.Rows)
                {
                    string tableName = row.Field <string>("TABLE_NAME");
                    // NOTE: interestingly, "TABLE_SCHEM" shall be used instead of "TABLE_SCHEMA"
                    // in case you think this is a typo
                    string schema = row.Field <string>("TABLE_SCHEM");
                    tables.Add(new TTable(tableName, schema));
                }
            }
            return(tables);
        }
示例#18
0
        /// <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]);
            }
        }
示例#19
0
        public void ResultLargerThanBufferWhenReadShouldFail()
        {
            var result = new DataTable();

            using (var conn = new VerticaConnection(TestConfig.ConnectionString))
            {
                conn.Open();
                using (var tx = conn.BeginTransaction())
                    using (var command = conn.CreateCommand())
                    {
                        command.CommandText = String.Format("Select * from {0}.{1} limit 2000;",
                                                            TestConfig.SchemaName, TestConfig.LargeTable);

                        var verticaDataReader = command.ExecuteReader();
                        result.Load(verticaDataReader);
                        tx.Commit();
                    }
            }

            Assert.That(result.Rows.Count, Is.EqualTo(2000));
        }
示例#20
0
        public static DataTable Get(int id)
        {
            var dataTable = new DataTable();

            try
            {
                Console.WriteLine("[ID:{0}] using connection string :{1}", id, TestConfig.ConnectionString);
                using (var conn = new VerticaConnection(TestConfig.ConnectionString))
                {
                    conn.Open();
                    using (var tx = conn.BeginTransaction())
                        using (var command = conn.CreateCommand())
                        {
                            Console.WriteLine("[ID:{0}] thread id is :{1}", id,
                                              Thread.CurrentThread.ManagedThreadId);

                            command.Transaction = tx;
                            command.CommandText = String.Format("Select * from dual;");
                            var reader = command.ExecuteReader();
                            Console.WriteLine("[ID:{0}] Start reading ", id);
                            dataTable.Load(reader);
                            Console.WriteLine("[ID:{0}] Finished reading", id);
                            if (id % 2 == 0)
                            {
                                Console.WriteLine("[ID:{0}] going to sleep", id);
                                Thread.Sleep(1000 * 10);
                                Console.WriteLine("[ID:{0}] Waking Up", id);
                            }
                            Console.WriteLine("[ID:{0}] Finished executing", id);
                            tx.Commit();
                        }
                }
                return(dataTable);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.StackTrace);
                throw;
            }
        }
示例#21
0
        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);
        }