public void ReadMultiple(int v, TimeSpan ts)
{
VerticaDataReader r = readmulcom.ExecuteReader();
if (!r.HasRows)
{
System.Console.WriteLine("Error: Vertica ReadMultiple received empty");
}
r.Close();
}
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 ReadOne(TimeSpan ts)
{
VerticaDataReader r = readonecom.ExecuteReader();
if (!r.HasRows)
{
System.Console.WriteLine("Error: Vertica ReadOne received empty");
}
r.Close();
}
public bool CheckInsert()
{
mCheckInsert();
VerticaDataReader r = checkcom.ExecuteReader();
if (r.HasRows)
{
r.Close(); return(true);
}
r.Close();
return(false);
}
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 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" });
}
// 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);
}
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);
}