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);
}
}
}
