protected async void ReadTableData(DbCommand cmd)
{
var reader = await cmd.ExecuteReaderAsync();
//TODO: replace with datatable
var nrFields = reader.FieldCount;
Dictionary <int, string> FieldNameToMarks = new Dictionary <int, string>();
for (int i = 0; i < nrFields; i++)
{
FieldNameToMarks.Add(i, reader.GetName(i));
}
var values = new List <RowRead>();
RowRead valLoop = null;
while (reader.Read())
{
valLoop = new RowRead();
for (int i = 0; i < nrFields; i++)
{
var val = reader.GetValue(i);
if (val != null && val == DBNull.Value)
{
val = null;
}
valLoop.Values.Add(FieldNameToMarks[i], val);
}
values.Add(valLoop);
}
valuesRead = values.ToArray();
}
protected void ReceiveMessagesFromDeviceAsync(EventHubReceiver receiver)
{
while (true)
{
var eventData = receiver.Receive(10, new TimeSpan(0, 0, 10));
if (eventData == null)
{
break;
}
List <RowRead> receivedRows = new List <RowRead>();
int count = 0;
foreach (var ed in eventData)
{
count++;
string data = Encoding.Unicode.GetString(ed.GetBytes());
RowRead row = new RowRead();
row.Values = JsonConvert.DeserializeObject <Dictionary <string, object> >(data);
receivedRows.Add(row);
this.EnqueuedTimeUtc = ed.EnqueuedTimeUtc;
this.EnqueuedOffset = long.Parse(ed.Offset);
}
if (count == 0)
{
break;
}
}
}
protected override async Task ProcessText(string text)
{
var rr = new RowRead();
rr.Values.Add("lineNr", line++);
rr.Values.Add("name", FileToRead);
rr.Values.Add("line", text);
values.Add(rr);
await Task.CompletedTask;
}
protected async Task ReceiveMessagesFromDeviceAsync(PartitionReceiver receiver)
{
while (true)
{
var eventData = await receiver.ReceiveAsync(5, new TimeSpan(0, 0, 5)); //Timeout for receiving messages = 30 seconds
int count = 0;
if (eventData != null)
{
if (eventData != null)
{
foreach (var item in eventData)
{
count++;
if (item.SystemProperties.EnqueuedTimeUtc >= lastRowValues[receiver.PartitionId].EnqueuedTimeUtc && Convert.ToInt64(item.SystemProperties.Offset) > lastRowValues[receiver.PartitionId].EnqueuedOffset)
{
if (item.Properties.ContainsKey("MessageType") && item.Properties.ContainsKey("UnicodeEncoding"))
{
if (this.MessageType.Equals(item.Properties["MessageType"]))
{
//Assert ? item.Properties["UnicodeEncoding"] == "UTF8"
string data = UnicodeEncoding.UTF8.GetString(item.Body.Array);
var jsonDeserializerSettings = new JsonSerializerSettings()
{
DateFormatHandling = DateFormatHandling.IsoDateFormat, DateParseHandling = DateParseHandling.DateTimeOffset, DateTimeZoneHandling = DateTimeZoneHandling.RoundtripKind
};
List <Dictionary <string, object> > results = JsonConvert.DeserializeObject <List <Dictionary <string, object> > >(data, jsonDeserializerSettings);
foreach (var r in results)
{
RowRead row = new RowRead();
row.Values = r;
receivedRows.Add(row);
}
}
}
LastReceivedMessage lastReceivedMessage;
lastReceivedMessage.EnqueuedTimeUtc = item.SystemProperties.EnqueuedTimeUtc;
lastReceivedMessage.EnqueuedOffset = Convert.ToInt64(item.SystemProperties.Offset);
lastRowValues[receiver.PartitionId] = lastReceivedMessage;
}
}
}
}
if (count == 0)
{
break;
}
}
await Task.CompletedTask;
}
/// <summary>
/// Split a line read from file into separate values and fills listOfData. <seealso cref="listOfData"/>
/// </summary>
/// <param name="text"></param>
/// <returns></returns>
protected override async Task ProcessText(string text)
{
if ((text?.Length ?? 0) == 0)
{
return;
}
foreach (var item in text.Split(new char[] { '\n' }, StringSplitOptions.RemoveEmptyEntries))
{
var textLine = item.Replace("\r", "");
var row = textLine.Split(new string[] { "," }, StringSplitOptions.None);
if (CSVHeaderLine != null && row.Length > CSVHeaderLine.Length)
{
if (textLine.Contains("\""))
{
if ((textLine.Replace("\"", "").Length - textLine.Length) % 2 == 0)
{
row = Regex.Split(textLine, ",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
}
}
if (textLine.Contains("'"))
{
if ((textLine.Replace("'", "").Length - textLine.Length) % 2 == 0)
{
row = Regex.Split(textLine, ",(?=(?:[^']*'[^']*')*[^']*$)");
}
}
}
if (CSVHeaderLine == null)
{
CSVHeaderLine = row;
continue;
}
RowRead obj = new RowRead();
for (int columns = 0; columns < Math.Min(row.Length, CSVHeaderLine.Length); columns++)
{
obj.Values.Add(CSVHeaderLine[columns], row[columns]);
}
listOfData.Add(obj);
}
return;
}
async Task <IRow[]> LoadFromFile(IRow item)
{
List <IRow> r = new List <IRow>();
if (!item.Values.ContainsKey("FullName"))
{
string message = "item not contain FullName";
//@class.Log(LogLevel.Information, 0, $"load from file: {message}", null, null);
message += "";
return(null);
}
var file = item.Values["FullName"]?.ToString();
if (!File.Exists(file))
{
string message = $"file dos not exists {file}";
//@class.Log(LogLevel.Information, 0, $"load from file: {message}", null, null);
message += "";
return(null);
}
int id = 1;
using (var stream = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))
using (var reader = new StreamReader(stream))
{
string line;
while ((line = await reader.ReadLineAsync()) != null)
{
if (TrimEmptyLines && string.IsNullOrWhiteSpace(line.Trim()))
{
continue;
}
var rr = new RowRead();
foreach (var val in item.Values)
{
rr.Values.Add(val.Key, val.Value);
}
rr.Values["text"] = line;
rr.Values["lineNr"] = id++;
r.Add(rr);
}
}
return(r.ToArray());
}
protected override async Task ProcessText(string text)
{
List <IRowReceive> returns = new List <IRowReceive>();
var doc = new HtmlDocument();
doc.LoadHtml(text);
var hrefs = doc.DocumentNode.SelectNodes("//a");
foreach (var item in hrefs)
{
var rr = new RowRead();
rr.Values.Add("href", item.Attributes["HREF"].Value);
rr.Values.Add("title", item.InnerText);
rr.Values.Add("ADD_DATE", item.Attributes["ADD_DATE"].Value);
returns.Add(rr);
}
valuesRead = returns.ToArray();
await Task.CompletedTask;
}
protected override async Task ProcessText(string text)
{
var ret = new List <IRowReceive>();
var doc = new HtmlDocument();
doc.LoadHtml(text);
var dn = doc.DocumentNode;
var list = new List <HtmlNodeCollection>();
foreach (var item in XPaths)
{
list.Add(dn.SelectNodes(item));
}
var nr = list.Min(it => it.Count);
var nrCols = AttributeNames.Length;
for (int i = 0; i < nr; i++)
{
var rr = new RowRead();
for (int col = 0; col < nrCols; col++)
{
string value;
//no attribute name means value
string attr = AttributeNames[col];
if (string.IsNullOrWhiteSpace(attr))
{
value = list[col][i].InnerText;
attr = "Value" + col;
}
else
{
value = list[col][i].Attributes[AttributeNames[col]].Value;
}
rr.Values.Add(attr, value);
}
ret.Add(rr);
}
valuesRead = ret.ToArray();
}
public override void ProcessTypeInfo(TypeInfo item, Assembly assembly)
{
string interfaces = "", baseTypes = "";
if (LoadInterfaces)
{
var i = item.ImplementedInterfaces.ToArray();
interfaces = string.Join("@", i.Select(it => it.FullName).ToArray());
}
if (LoadBaseClasses)
{
var bases = new List <string>();
var baseType = item.BaseType;
while (baseType != typeof(object))
{
bases.Add(baseType.FullName);
baseType = baseType.BaseType;
}
bases.Add(baseType.FullName);//object
baseTypes = string.Join("@", bases.ToArray());
}
var rr = new RowRead();
rr.Values.Add("Name", item.Name);
rr.Values.Add("FullName", item.FullName);
rr.Values.Add("AssemblyName", an.Name);
rr.Values.Add("AssemblyFullName", an.FullName);
if (LoadInterfaces)
{
rr.Values.Add("Interfaces", interfaces);
}
if (LoadBaseClasses)
{
rr.Values.Add("BaseTypes", baseTypes);
}
ret.Add(rr);
}
public async Task Run()
{
int nrValues = valuesRead?.Length ?? 0;
if (nrValues == 0)
{
return;
}
valuesTransformed = new RowRead[nrValues];
for (int i = 0; i < nrValues; i++)
{
var rr = new RowRead();
foreach (var item in valuesRead[i].Values)
{
if (item.Value == null)
{
continue;
}
if (string.IsNullOrWhiteSpace(item.Value.ToString()))
{
continue;
}
if (!rr.Values.ContainsKey("key"))
{
rr.Values["key"] = item.Value;
continue;
}
if (rr.Values.ContainsKey("value"))
{
throw new ArgumentException("more than 1 value for " + item.Key);
}
rr.Values.Add("value", item.Value);
}
valuesTransformed[i] = rr;
}
}
protected override async Task ProcessText(string text)
{
var ret = new List <IRowReceive>();
var doc = new HtmlDocument();
doc.LoadHtml(text);
var tables = doc.DocumentNode.SelectNodes("//table");
if ((tables?.Count ?? 0) == 0)
{
throw new ArgumentException("not found tables");
}
foreach (var table in tables)
{
string[] columnsNames = null;
var head = table.SelectSingleNode("thead");
if (head != null)
{
var columns = head.SelectNodes("tr/th");
if ((columns?.Count ?? 0) == 0)
{
columns = head.SelectNodes("tr/td");
}
if (columns != null)
{
columnsNames = new string[columns.Count];
for (int i = 0; i < columns.Count; i++)
{
var colName = columns[i].InnerText;
columnsNames[i] = MakePretty(colName);
}
}
}
HtmlNodeCollection rows;
var body = table.SelectSingleNode("tbody");
if (body != null)
{
rows = body.SelectNodes("tr");
}
else
{
rows = table.SelectNodes("tr");
}
foreach (var row in rows)
{
var cells = row.SelectNodes("td");
if ((cells?.Count ?? 0) == 0)
{
cells = row.SelectNodes("th");
}
if ((columnsNames?.Length ?? 0) == 0)
{
columnsNames = new string[cells.Count];
for (int i = 0; i < columnsNames.Length; i++)
{
var colName = cells[i].InnerText;
columnsNames[i] = MakePretty(cells[i].InnerText);
}
continue;
}
//cells = row.SelectNodes("td");
if ((cells?.Count ?? 0) == 0)
{
continue;
}
var rr = new RowRead();
ret.Add(rr);
for (int i = 0; i < cells.Count; i++)
{
var key = columnsNames[i];
if (string.IsNullOrWhiteSpace(key))
{
key = "!NoName";
}
if (rr.Values.ContainsKey(key))
{
rr.Values[key] += cells[i].InnerText;
}
else
{
rr.Values.Add(key, cells[i].InnerText);
}
}
}
}
valuesRead = ret.ToArray();
await Task.CompletedTask;
}
/// <summary>
/// Execute the receiver filling valuesRead with received data.
/// </summary>
/// <returns></returns>
public async Task LoadData()
{
//Initialization
Initialization();
//Deserialize last received row
if (this.SerializeLastRow)
{
using (SerializeDataOnFile sdf = new SerializeDataOnFile(this.FileNameSerializeLastRow))
{
lastRowValues = sdf.GetDictionary();
}
}
List <RowRead> receivedRows = new List <RowRead>();
using (var conn = new SqlConnection(this.ConnectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = this.CommandType;
cmd.CommandText = this.CommandText;
if (this.HasParameters)
{
foreach (var param in this.Parameters)
{
if (lastRowValues.ContainsKey(param.Value))
{
cmd.Parameters.AddWithValue(param.Key, lastRowValues[param.Value]);
}
}
}
var rdr = await cmd.ExecuteReaderAsync();
while (rdr.Read())
{
RowRead row = new RowRead();
for (var i = 0; i < rdr.FieldCount; i++)
{
string key = rdr.GetName(i);
object value = rdr[i];
row.Values.Add(key, value);
}
receivedRows.Add(row);
lastRowValues = row.Values; //For simplicity we are storing all column/values: now, we are not filtering only those columns from this.Parameters
}
}
}
valuesRead = receivedRows.ToArray();
//Serialize last received row
if (this.SerializeLastRow)
{
using (SerializeDataOnFile sdf = new SerializeDataOnFile(this.FileNameSerializeLastRow))
{
//Only columns from this.Parameters are going to be serialized
Dictionary <string, object> selectedLastRowValues = this.HasParameters ? (lastRowValues.Where(filter => this.Parameters.Values.Contains(filter.Key)).ToDictionary(kvp => kvp.Key, kvp => kvp.Value)) : lastRowValues;
sdf.SetDictionary(selectedLastRowValues);
}
}
}
public async Task MySqlTestLoadDataWithLatestValueInt()
{
#region Arrange
//ADD A DATABASE AND PUT nrPostsToAdd data into table
string sqlcommand;
string testName1 = "TEST";
bool equality = true;
RowRead valLoop = null;
ISerializeData sd = new SerializeDataInMemory();
IRowReceive[] valuesRead1 = new IRowReceive[100];
var values = new List <IRowReceive>();
var ConnString = @"server=localhost;Database=new_schema ;user id=george95; Pwd=1234;";
Dictionary <int, string> FieldNameToMarks1 = new Dictionary <int, string>();
DBTableData <int, MySqlConnection> tab2 = new DBTableData <int, MySqlConnection>(sd)
{
ConnectionString = ConnString,
FieldNameToMark = "id",
TableName = "employees_test;"
};
var mysql_obj = new ReceiverTableMySQLInt(tab2);
using (MySqlConnection connection = new MySqlConnection(ConnString))
{
sqlcommand = "CREATE TABLE Employees_test(ID INT(6), Name VARCHAR(50));";
connection.Open();
using (MySqlCommand command = new MySqlCommand(sqlcommand, connection))
{
command.ExecuteNonQuery();
}
for (int Emp_ID = 1; Emp_ID < 10; Emp_ID++)
{
sqlcommand = "INSERT INTO Employees_test (ID, Name) VALUES (" + Emp_ID + ", '" + testName1 + "');";
using (MySqlCommand command = new MySqlCommand(sqlcommand, connection))
{
command.ExecuteNonQuery();
}
}
#endregion
#region Act
sqlcommand = "SELECT * FROM Employees_test;";
using (MySqlCommand command = new MySqlCommand(sqlcommand, connection))
using (MySqlDataReader reader = (MySqlDataReader)command.ExecuteReader())
{
var nrFields1 = reader.FieldCount;
for (int i = 0; i < nrFields1; i++)
{
FieldNameToMarks1.Add(i, reader.GetName(i));
}
while (reader.Read())
{
valLoop = new RowRead();
for (int i = 0; i < nrFields1; i++)
{
object val;
val = reader.GetValue(i);
if (val != null && val == DBNull.Value)
{
val = null;
}
valLoop.Values.Add(FieldNameToMarks1[i], val);
}
values.Add(valLoop);
}
}
connection.Close();
}
await mysql_obj.LoadData();
using (MySqlConnection connection = new MySqlConnection(ConnString))
{
sqlcommand = "DROP TABLE Employees_test;";
connection.Open();
using (MySqlCommand command = new MySqlCommand(sqlcommand, connection))
{
command.ExecuteNonQuery();
}
connection.Close();
}
#endregion
#region Assert
var MySqlLoadResult = mysql_obj.valuesRead.Select(o => o.Values).ToList();
var AdoNetResult = values.Select(o => o.Values).ToList();
//AdoNetResult[7]["Name"] = 999;
for (int index = 0; index < MySqlLoadResult.Count; index++)
{
if (!MySqlLoadResult[index].SequenceEqual(AdoNetResult[index]))
{
equality = false;
}
}
Assert.IsTrue(equality);
#endregion
}
public async Task LoadData()
{
//LOG: before retrieving latest value
var valSaved = tableData.lastValue;
var cn = await tableData.GetConnection();
bool CompareValues = !String.IsNullOrWhiteSpace(tableData.FieldNameToMark);
using (var cmd = cn.CreateCommand())
{
string fields = String.Join(",", tableData.Fields);
string CommandText = $"select {fields} from {tableData.TableName} ";
if (CompareValues && !valSaved.Equals(default(T)))
{
CommandText += $" where {tableData.FieldNameToMark} > @lastValue";
var param = cmd.CreateParameter();
param.ParameterName = "@lastValue";
param.Value = tableData.lastValue;
cmd.Parameters.Add(param);
}
if (CompareValues)
{
CommandText += $" order by {tableData.FieldNameToMark} asc";
}
CommandText += MaxRowsToLoad();
cmd.CommandText = CommandText;
var reader = await cmd.ExecuteReaderAsync();
//TODO: replace with datatable
var nrFields = reader.FieldCount;
Dictionary <int, string> FieldNameToMarks = new Dictionary <int, string>();
for (int i = 0; i < nrFields; i++)
{
FieldNameToMarks.Add(i, reader.GetName(i));
}
var values = new List <RowRead>();
RowRead valLoop = null;
while (await reader.ReadAsync())
{
valLoop = new RowRead();
for (int i = 0; i < nrFields; i++)
{
var val = reader.GetValue(i);
//transform DBNull.Value into null
if (val != null && val == DBNull.Value)
{
val = null;
}
valLoop.Values.Add(FieldNameToMarks[i], val);
}
values.Add(valLoop);
}
valuesRead = values.ToArray();
if (values.Count > 0 && CompareValues)
{
var lastVal = valLoop.Values[tableData.FieldNameToMark];
if (lastVal != null && lastVal != DBNull.Value)
{
tableData.lastValue = (T)Convert.ChangeType(lastVal, typeof(T));
}
}
}
}
