public void TestSerializeDataOnFileSingleValue()
{
//string
#region arrange
string writeStringValue = "Basescu";
string fileName = "TestSerializeToFileSingleValue.txt";
SerializeDataOnFile sdf = new SerializeDataOnFile(fileName);
#endregion
#region act
sdf.SetValue("DummyKeyString", writeStringValue);
#endregion
#region assert
string readStringValue = (string)sdf.GetValue("DummyKeyString");
Assert.AreEqual(writeStringValue, readStringValue);
#endregion
//int
#region arrange
int writeIntValue = 12345;
#endregion
#region act
sdf.SetValue("DummyKeyInt", writeIntValue);
#endregion
#region assert
int readIntValue = (int)sdf.GetValue("DummyKeyInt");
Assert.AreEqual(writeIntValue, readIntValue);
#endregion
}
public async Task LoadData()
{
//Deserialize last received message
using (SerializeDataOnFile sdf = new SerializeDataOnFile(this.FileNameSerializeLastOffset))
{
lastRowValues = sdf.GetDictionary();
}
this.EnqueuedTimeUtc = (lastRowValues.ContainsKey("EnqueuedTimeUtc") ? (DateTime)lastRowValues["EnqueuedTimeUtc"] : new DateTime(1, 1, 1));
this.EnqueuedOffset = (lastRowValues.ContainsKey("EnqueuedOffset") ? (long)lastRowValues["EnqueuedOffset"] : 0);
//Load
var eventHubClient = EventHubClient.
CreateFromConnectionString(this.ConnectionString, this.Endpoint);
var d2cPartitions = eventHubClient.GetRuntimeInformation().PartitionIds;
foreach (string partition in d2cPartitions)
{
var receiver = eventHubClient.GetDefaultConsumerGroup().CreateReceiver(partition, this.EnqueuedTimeUtc);
ReceiveMessagesFromDeviceAsync(receiver);
}
//Serialize last received message
using (SerializeDataOnFile sdf = new SerializeDataOnFile(this.FileNameSerializeLastOffset))
{
lastRowValues["EnqueuedTimeUtc"] = this.EnqueuedTimeUtc;
lastRowValues["EnqueuedOffset"] = this.EnqueuedOffset;
sdf.SetDictionary(lastRowValues);
}
}
public async Task LoadData()
{
//Deserialize last received message
using (SerializeDataOnFile sdf = new SerializeDataOnFile(this.FileNameSerializeLastOffset))
{
Dictionary <string, object> lastRowValuesAsObjects = sdf.GetDictionary();
lastRowValues = new Dictionary <string, LastReceivedMessage>();
foreach (var item in lastRowValuesAsObjects)
{
LastReceivedMessage lastItem;
lastItem.EnqueuedOffset = Convert.ToInt64(((string)item.Value).Split(',')[0]);
lastItem.EnqueuedTimeUtc = Convert.ToDateTime(((string)item.Value).Split(',')[1]);
lastRowValues.Add(item.Key, lastItem);
}
}
//Load
receivedRows = new List <RowRead>();
var connectionStringBuilder = new EventHubsConnectionStringBuilder(this.ConnectionStringEventHubCompatible)
{
EntityPath = this.EntityEventHubCompatible
};
eventHubClient = EventHubClient.CreateFromConnectionString(connectionStringBuilder.ToString());
var runTimeInformation = await eventHubClient.GetRuntimeInformationAsync();
foreach (string partition in runTimeInformation.PartitionIds)
{
// It checks if lastRowValues contains for current partition last received message
if (!lastRowValues.ContainsKey(partition))
{
LastReceivedMessage lastReceivedMessage;
lastReceivedMessage.EnqueuedTimeUtc = this.StartTimeInHours == 0 ? DateTime.UtcNow.AddHours(-24) : DateTime.UtcNow.AddHours(this.StartTimeInHours);
lastReceivedMessage.EnqueuedOffset = -1; //Starting offset for EventHub. Note: For IoT Hub, starting offset is 0.
lastRowValues.Add(partition, lastReceivedMessage);
}
// Receiving
var receiver = eventHubClient.CreateReceiver(PartitionReceiver.DefaultConsumerGroupName, partition, lastRowValues[partition].EnqueuedOffset.ToString());
await ReceiveMessagesFromDeviceAsync(receiver);
}
valuesRead = receivedRows.ToArray();
//Serialize last received message
using (SerializeDataOnFile sdf = new SerializeDataOnFile(this.FileNameSerializeLastOffset))
{
Dictionary <string, object> lastRowValuesAsObjects = new Dictionary <string, object>();
foreach (var item in lastRowValues)
{
lastRowValuesAsObjects.Add(item.Key, $"{item.Value.EnqueuedOffset},{item.Value.EnqueuedTimeUtc}");
}
sdf.SetDictionary(lastRowValuesAsObjects);
}
}
public void TestSerializeDataOnFileDictionary()
{
//string
#region arrange
Dictionary <string, object> write = new Dictionary <string, object>();
write["PersonID"] = 1001;
write["FirstName"] = "John";
write["LastName"] = "Doe";
string fileName = "TestSerializeToFileDictionary.txt";
SerializeDataOnFile sdf = new SerializeDataOnFile(fileName);
#endregion
#region act
sdf.SetDictionary(write);
#endregion
#region assert
Dictionary <string, object> read = sdf.GetDictionary();
Assert.IsTrue(Utils.CompareDictionary(write, read));
#endregion
}
public async Task TestReiceverDBExecuteStoredProcedureWithParams()
{
//First call
#region arange
string connectionString = GetSqlServerConnectionString();
string commandText = "dbo.TestReiceverDBExecuteStoredProcedureWithParam";
const string fileNameSerilizeLastRow = "TestReiceverDBExecuteStoredProcedureWithParam_LastRow.txt";
string parameters = "@pid=PersonID;@p2=FirstName";
if (File.Exists(fileNameSerilizeLastRow))
{
File.Delete(fileNameSerilizeLastRow);
}
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "IF OBJECT_ID('dbo.TestingTestReiceverDBExecuteStoredProcedureWithParam') IS NOT NULL DROP TABLE dbo.TestingTestReiceverDBExecuteStoredProcedureWithParam; CREATE TABLE dbo.TestingTestReiceverDBExecuteStoredProcedureWithParam (PersonID INT NOT NULL PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50)); INSERT dbo.TestingTestReiceverDBExecuteStoredProcedureWithParam VALUES (1, 'John', 'Doe'), (11, 'Joanna', 'Doe');";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "IF OBJECT_ID('dbo.TestReiceverDBExecuteStoredProcedureWithParam') IS NOT NULL DROP PROCEDURE dbo.TestReiceverDBExecuteStoredProcedureWithParam;";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "CREATE PROCEDURE dbo.TestReiceverDBExecuteStoredProcedureWithParam (@pid INT = NULL, @p2 VARCHAR(50) = 'NULL') AS SELECT * FROM dbo.TestingTestReiceverDBExecuteStoredProcedureWithParam x WHERE x.PersonID > ISNULL(@pid,0) ORDER BY PersonID";
await cmd.ExecuteNonQueryAsync();
}
}
ReceiverStmtSqlServer rcvr = new ReceiverStmtSqlServer(connectionString, commandType, commandText, fileNameSerilizeLastRow, parameters);
#endregion
#region act
await rcvr.LoadData();
#endregion
#region assert
var results = rcvr.valuesRead;
//Same number of rows ?
Assert.AreEqual(2, results.Length);
//Same data ?
Assert.AreEqual(1, results[0].Values["PersonID"]);
Assert.AreEqual("John", results[0].Values["FirstName"]);
Assert.AreEqual("Doe", results[0].Values["LastName"]);
Assert.AreEqual(11, results[1].Values["PersonID"]);
Assert.AreEqual("Joanna", results[1].Values["FirstName"]);
Assert.AreEqual("Doe", results[1].Values["LastName"]);
//lastRow ?
Dictionary <string, object> lastRowRead;
using (SerializeDataOnFile sdf = new SerializeDataOnFile(fileNameSerilizeLastRow))
{
lastRowRead = sdf.GetDictionary();
}
//lastRow data (only columns mapped to SP parameters) ?
Assert.AreEqual(11, (long)lastRowRead["PersonID"]);
Assert.AreEqual("Joanna", lastRowRead["FirstName"]);
#endregion
//Second call (we just calling twice the same stored procedure)
#region arrage
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT dbo.TestingTestReiceverDBExecuteStoredProcedureWithParam VALUES (111, 'Ion', 'Ion'),(1111, 'Ioan', 'Ioan');";
await cmd.ExecuteNonQueryAsync();
}
}
#endregion
#region act
await rcvr.LoadData();
#endregion
#region assert
results = rcvr.valuesRead;
//Same number of rows ?
Assert.AreEqual(2, results.Length);
//Same data ?
Assert.AreEqual(111, results[0].Values["PersonID"]);
Assert.AreEqual("Ion", results[0].Values["FirstName"]);
Assert.AreEqual("Ion", results[0].Values["LastName"]);
Assert.AreEqual(1111, results[1].Values["PersonID"]);
Assert.AreEqual("Ioan", results[1].Values["FirstName"]);
Assert.AreEqual("Ioan", results[1].Values["LastName"]);
//lastRow ?
using (SerializeDataOnFile sdf = new SerializeDataOnFile(fileNameSerilizeLastRow))
{
lastRowRead = sdf.GetDictionary();
}
//lastRow data ?
Assert.AreEqual(1111, (long)lastRowRead["PersonID"]);
Assert.AreEqual("Ioan", lastRowRead["FirstName"]);
#endregion
}
public async Task TestReiceverDBExecuteStoredProcedureNoParams()
{
#region arange
string connectionString = GetSqlServerConnectionString();
string commandText = "dbo.TestReiceverDBExecuteStoredProcedureNoParams";
const string fileNameSerilizeLastRow = "TestReiceverDBExecuteStoredProcedureNoParams_LastRow.txt";
if (File.Exists(fileNameSerilizeLastRow))
{
File.Delete(fileNameSerilizeLastRow);
}
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "IF OBJECT_ID('dbo.TestReiceverDBExecuteStoredProcedureNoParams') IS NOT NULL DROP PROCEDURE dbo.TestReiceverDBExecuteStoredProcedureNoParams;";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "CREATE PROCEDURE dbo.TestReiceverDBExecuteStoredProcedureNoParams AS SELECT 1 AS PersonID, 'John' AS FirstName , 'Doe' AS LastName UNION ALL SELECT 11, 'Joanna', 'Doe' ORDER BY PersonID";
await cmd.ExecuteNonQueryAsync();
}
}
ReceiverStmtSqlServer rcvr = new ReceiverStmtSqlServer(connectionString, commandType, commandText, fileNameSerilizeLastRow);
#endregion
#region act
await rcvr.LoadData();
#endregion
#region assert
var results = rcvr.valuesRead;
//Same number of rows ?
Assert.AreEqual(2, results.Length);
//Same data
Assert.AreEqual(1, results[0].Values["PersonID"]);
Assert.AreEqual("John", results[0].Values["FirstName"]);
Assert.AreEqual("Doe", results[0].Values["LastName"]);
Assert.AreEqual(11, results[1].Values["PersonID"]);
Assert.AreEqual("Joanna", results[1].Values["FirstName"]);
Assert.AreEqual("Doe", results[1].Values["LastName"]);
//lastRow ?
Dictionary <string, object> lastRowRead;
using (SerializeDataOnFile sdf = new SerializeDataOnFile(fileNameSerilizeLastRow))
{
lastRowRead = sdf.GetDictionary();
}
//lastRow Count ?
Assert.AreEqual(3, lastRowRead.Count);
//lastRow data ?
Assert.AreEqual(11, (long)lastRowRead["PersonID"]);
Assert.AreEqual("Joanna", lastRowRead["FirstName"]);
Assert.AreEqual("Doe", lastRowRead["LastName"]);
#endregion
}
/// <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);
}
}
}
static string PBXJob()
{
var dir = AppContext.BaseDirectory;
var dirPBX = Path.Combine(dir, "PBX");
var serialize = new SerializeDataOnFile("a.txt");
IReceive r = new ReceiverFolderHierarchical(dirPBX, "*.log");
IFilter filterFiles = new FilterForFilesHierarchical();
#region filter for remove dates serialize
var filterDateTime = new FilterComparableGreat(typeof(DateTime), DateTime.MinValue, "LastWriteTimeUtc");
IFilter filterDateTimeSerializable = new FilterComparableFromSerializable(filterDateTime, serialize);
#endregion
IFilter removeFilesMaxWritten = new FilterRemovePropertyMaxMinDateTime("LastWriteTimeUtc", GroupingFunctions.Max);
ITransform transformLines = new TransformerFileToLines()
{
TrimEmptyLines = true
};
var trDateRegex = new TransformRowRegex(@"^Date:\ (?<datePBX>.{23}).*?$", "text");
var trKeyValueRegex = new TransformRowRegex(@"(^|\r|\n|\r\n)(?<tkey>(\$?[a-zA-Z0-9_[\]/ ]+))='?(?<tvalue>([a-zA-Z0-9_ ]+))'?(\r|\r\n|$)", "text");
var trToDate = new TransformerFieldStringToDate("datePBX", "NewDatePBX", "yyyy/MM/dd HH:mm:ss.fff");
var trAddDate = new TransformAddFieldDown("NewDatePBX");
var trAddKey = new TransformAddNewField("tkey");
var trAddValue = new TransformAddNewField("tvalue");
var trSimpleFields = new TransformRowRemainsProperties("NewDatePBX", "lineNr", "text", "FullName", "LastWriteTimeUtc", "tkey", "tvalue");
var data = new DBTableDataConnection <SqlConnection>(new SerializeDataInMemory());
data.ConnectionString = "#file:SqlServerConnectionString#";
data.Fields = new string[] { "NewDatePBX", "lineNr", "text", "FullName", "tkey", "tvalue" };
data.TableName = "PBXData";
var bulk = new SenderSqlServerBulkCopy(data);
var md = new MediaTransformMaxMin <DateTime>();
md.GroupFunction = GroupingFunctions.Max;
md.FieldName = "LastWriteTimeUtc";
var serializeMaxDate = new SenderMediaSerialize <DateTime>(serialize, "LastWriteTimeUtc", md);
var si = new SimpleJob();
si.Receivers.Add(0, r);
int iFilterNr = 0;
si.FiltersAndTransformers.Add(iFilterNr++, filterFiles);
si.FiltersAndTransformers.Add(iFilterNr++, filterDateTimeSerializable);
si.FiltersAndTransformers.Add(iFilterNr++, removeFilesMaxWritten);
si.FiltersAndTransformers.Add(iFilterNr++, transformLines);
si.FiltersAndTransformers.Add(iFilterNr++, trDateRegex);
si.FiltersAndTransformers.Add(iFilterNr++, trKeyValueRegex);
si.FiltersAndTransformers.Add(iFilterNr++, trToDate);
si.FiltersAndTransformers.Add(iFilterNr++, trAddDate);
si.FiltersAndTransformers.Add(iFilterNr++, trAddKey);
si.FiltersAndTransformers.Add(iFilterNr++, trAddValue);
si.FiltersAndTransformers.Add(iFilterNr++, trSimpleFields);
//TODO: add transformer to add a field down for all fields
//TODO: add transformer regex for splitting Key=Value
//TODO: add field to separate Conn(1)Type(Any)User(InternalTask) CDL Request:RSVI(Get)
si.Senders.Add(0, bulk);
si.Senders.Add(1, serializeMaxDate);
return(si.SerializeMe());
}
public async Task TestPBXData()
{
#region arrange
string connectionString = GetSqlServerConnectionString();
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"
IF OBJECT_ID('dbo.PBXData', 'U') IS NOT NULL
DROP TABLE dbo.PBXData;";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = @"CREATE TABLE [PBXData](
[NewDatePBX] [datetime] NOT NULL,
[lineNr] [int] NOT NULL,
[text] [nvarchar](500) NOT NULL,
[FullName] [nvarchar](500) NOT NULL
)";
await cmd.ExecuteNonQueryAsync();
}
await Task.Delay(2000);
var dir = AppContext.BaseDirectory;
var dirPBX = Path.Combine(dir, "PBX");
File.AppendAllText(Path.Combine(dirPBX, "PBXRemove.log"), "aaaa");
#endregion
#region act
var serialize = new SerializeDataOnFile("a.txt");
IReceive r = new ReceiverFolderHierarchical(dirPBX, "*.log");
IFilter filterFiles = new FilterForFilesHierarchical();
#region filter for remove dates serialize
var filterDateTime = new FilterComparableGreat(typeof(DateTime), DateTime.MinValue, "LastWriteTimeUtc");
IFilter filterDateTimeSerializable = new FilterComparableFromSerializable(filterDateTime, serialize);
#endregion
IFilter removeFilesMaxWritten = new FilterRemovePropertyMaxMinDateTime("LastWriteTimeUtc", GroupingFunctions.Max);
ITransform transformLines = new TransformerFileToLines()
{
TrimEmptyLines = true
};
var trDateRegex = new TransformRowRegex(@"^Date:\ (?<datePBX>.{23}).*?$", "text");
var trToDate = new TransformerFieldStringToDate("datePBX", "NewDatePBX", "yyyy/MM/dd HH:mm:ss.fff");
var trAddDate = new TransformAddFieldDown("NewDatePBX");
var trSimpleFields = new TransformRowRemainsProperties("NewDatePBX", "lineNr", "text", "FullName", "LastWriteTimeUtc");
var data = new DBTableDataConnection <SqlConnection>(new SerializeDataInMemory());
data.ConnectionString = GetSqlServerConnectionString();
data.Fields = new string[] { "NewDatePBX", "lineNr", "text", "FullName" };
data.TableName = "PBXData";
var bulk = new SenderSqlServerBulkCopy(data);
var md = new MediaTransformMaxMin <DateTime>();
md.GroupFunction = GroupingFunctions.Max;
md.FieldName = "LastWriteTimeUtc";
var serializeMaxDate = new SenderMediaSerialize <DateTime>(serialize, "LastWriteTimeUtc", md);
var si = new SimpleJob();
si.Receivers.Add(0, r);
int iFilterNr = 0;
si.FiltersAndTransformers.Add(iFilterNr++, filterFiles);
si.FiltersAndTransformers.Add(iFilterNr++, filterDateTimeSerializable);
si.FiltersAndTransformers.Add(iFilterNr++, removeFilesMaxWritten);
si.FiltersAndTransformers.Add(iFilterNr++, transformLines);
si.FiltersAndTransformers.Add(iFilterNr++, trDateRegex);
si.FiltersAndTransformers.Add(iFilterNr++, trToDate);
si.FiltersAndTransformers.Add(iFilterNr++, trAddDate);
si.FiltersAndTransformers.Add(iFilterNr++, trSimpleFields);
//TODO: add transformer to add a field down for all fields
//TODO: add transformer regex for splitting Key=Value
//TODO: add field to separate Conn(1)Type(Any)User(InternalTask) CDL Request:RSVI(Get)
si.Senders.Add(0, bulk);
si.Senders.Add(1, serializeMaxDate);
await si.Execute();
#endregion
#region assert
filterFiles.valuesTransformed.Length.ShouldBe(3, "three files after first filter");
removeFilesMaxWritten.valuesTransformed.Length.ShouldBe(2, "last one file written dismissed");
transformLines.valuesTransformed.Length.ShouldBe(77251);
var d = transformLines.valuesTransformed.Select(it => it.Values["FullName"]).Distinct().ToArray();
d.Length.ShouldBe(2, "two files after reading contents");
//transformGroupingFiles.valuesTransformed.Length.ShouldBe(2);
//trDateRegex.valuesTransformed.Count(it => it.Values.ContainsKey("datePBX")).ShouldBeGreaterThan(0,"datePBX");
//trToDate.valuesTransformed.Count(it => it.Values.ContainsKey("NewDatePBX")).ShouldBeGreaterThan(0,"NewDatePBX");
foreach (var item in trAddDate.valuesTransformed)
{
item.Values.ShouldContainKey("NewDatePBX");
}
foreach (var item in trSimpleFields.valuesTransformed)
{
item.Values.Keys.Count.ShouldBe(5);
}
using (var con = new SqlConnection(connectionString))
{
await con.OpenAsync();
using (var cmd = con.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select count(*) from PBXData";
var val = await cmd.ExecuteScalarAsync();
val.ShouldBe(77251);
}
}
md.Result.Year.ShouldBe(DateTime.Now.Year);
#endregion
#region arange to read again
r = new ReceiverFolderHierarchical(dirPBX, "*.log");
filterFiles = new FilterForFilesHierarchical();
#region filter for remove dates serialize
filterDateTime = new FilterComparableGreat(typeof(DateTime), DateTime.MinValue, "LastWriteTimeUtc");
filterDateTimeSerializable = new FilterComparableFromSerializable(filterDateTime, serialize);
#endregion
#endregion
#region act
si = new SimpleJob();
si.Receivers.Add(0, r);
iFilterNr = 0;
si.FiltersAndTransformers.Add(iFilterNr++, filterFiles);
si.FiltersAndTransformers.Add(iFilterNr++, filterDateTimeSerializable);
await si.Execute();
#endregion
#region assert
filterDateTime.valuesTransformed?.Length.ShouldBe(1, "next time 1 file read - the added one");
#endregion
}
}