public async Task TestSenderDBExecuteStoredProcedureWithParams()
{
#region arrange
string connectionString = GetSqlServerConnectionString();
string commandText = "dbo.TestSenderDBExecuteStoredProcedureWithParams";
string parameters = "@p1=PersonID;@p2=FirstName;@p3=LastName";
//Prepare source data: 2 rows {ID, FirstName, LastName}
var rows = new List <IRow>();
int nrRows = 2;
for (int i = 0; i < nrRows; i++)
{
var row = new Mock <IRow>();
row.SetupProperty
(
obj => obj.Values,
new Dictionary <string, object>()
{
["PersonID"] = i,
["FirstName"] = "John " + i,
["LastName"] = "Doe " + i
}
);
rows.Add(row.Object);
}
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "IF OBJECT_ID('dbo.TestingTestSenderDBExecuteStoredProcedureWithParams') IS NOT NULL DROP TABLE dbo.TestingTestSenderDBExecuteStoredProcedureWithParams; CREATE TABLE dbo.TestingTestSenderDBExecuteStoredProcedureWithParams (PersonID INT NOT NULL PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50));";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "IF OBJECT_ID('dbo.TestSenderDBExecuteStoredProcedureWithParams') IS NOT NULL DROP PROCEDURE dbo.TestSenderDBExecuteStoredProcedureWithParams;";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "CREATE PROCEDURE dbo.TestSenderDBExecuteStoredProcedureWithParams (@p1 INT, @p2 VARCHAR(50), @p3 VARCHAR(50)) AS INSERT dbo.TestingTestSenderDBExecuteStoredProcedureWithParams (PersonID, FirstName, LastName) VALUES (@p1, @p2, @p3)";
await cmd.ExecuteNonQueryAsync();
}
}
var sender = new SenderToDBStmtSqlServer(connectionString, commandType, commandText, parameters);
sender.valuesToBeSent = rows.ToArray();
#endregion
#region act
await sender.Send();
#endregion
#region assert
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT COUNT(*) AS Cnt FROM TestingTestSenderDBExecuteStoredProcedureWithParams tbl FULL JOIN (VALUES (0, 'John 0', 'Doe 0'), (1, 'John 1', 'Doe 1')) chk(PersonID, FirstName, LastName) ON tbl.PersonID = chk.PersonID AND tbl.FirstName = chk.FirstName AND tbl.LastName = chk.LastName WHERE EXISTS(SELECT tbl.PersonID EXCEPT SELECT chk.PersonID) OR EXISTS(SELECT tbl.FirstName EXCEPT SELECT chk.FirstName) OR EXISTS(SELECT tbl.LastName EXCEPT SELECT chk.LastName);";
var cnt = (int)await cmd.ExecuteScalarAsync();
Assert.AreEqual(0, cnt);
}
}
#endregion
}
public async Task TestSenderWithFilterSQL2SQL2SMTP()
{
#region arrange
string commandText1 = "dbo.TestReiceverDBExecuteStoredProcedureNoParam2"; // Receiver SP (Source)
string fileNameSerilizeLastRow = string.Empty;
string parameters1 = string.Empty;
string commandText2 = "dbo.TestSenderDBExecuteStoredProcedureWithParams2"; // Sender SP (Destination)
string parameters2 = "@p1=PersonID;@p2=FirstName;@p3=LastName";
//Sender: SMTP Clean
using (var client = new Pop3Client())
{
client.Connect(pop3Server, pop3Port, false);
// Note: since we don't have an OAuth2 token, disable
// the XOAUTH2 authentication mechanism.
client.AuthenticationMechanisms.Remove("XOAUTH2");
client.Authenticate(user, password);
for (int i = 0; i < client.Count; i++)
{
var message = client.GetMessage(i);
if (message.Subject == subject)
{
client.DeleteMessage(i);
}
}
client.Disconnect(true);
}
//Receiver: SQL Server Source
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "IF OBJECT_ID('dbo.TestingTestReiceverDBExecuteStoredProcedureNoParam2') IS NOT NULL DROP TABLE dbo.TestingTestReiceverDBExecuteStoredProcedureNoParam2; CREATE TABLE dbo.TestingTestReiceverDBExecuteStoredProcedureNoParam2 (PersonID INT NOT NULL PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50)); INSERT dbo.TestingTestReiceverDBExecuteStoredProcedureNoParam2 VALUES (0, 'John 0', 'Doe 0'), (1, 'John 1', 'Doe 1'), (2, 'John 2', 'Doe 2');";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "IF OBJECT_ID('dbo.TestReiceverDBExecuteStoredProcedureNoParam2') IS NOT NULL DROP PROCEDURE dbo.TestReiceverDBExecuteStoredProcedureNoParam2;";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "CREATE PROCEDURE dbo.TestReiceverDBExecuteStoredProcedureNoParam2 AS SELECT * FROM dbo.TestingTestReiceverDBExecuteStoredProcedureNoParam2;";
await cmd.ExecuteNonQueryAsync();
}
}
//Sender: SQL Server Destination
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "IF OBJECT_ID('dbo.TestingTestSenderDBExecuteStoredProcedureWithParams2') IS NOT NULL DROP TABLE dbo.TestingTestSenderDBExecuteStoredProcedureWithParams2; CREATE TABLE dbo.TestingTestSenderDBExecuteStoredProcedureWithParams2 (PersonID INT NOT NULL PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50));";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "IF OBJECT_ID('dbo.TestSenderDBExecuteStoredProcedureWithParams2') IS NOT NULL DROP PROCEDURE dbo.TestSenderDBExecuteStoredProcedureWithParams2;";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "CREATE PROCEDURE dbo.TestSenderDBExecuteStoredProcedureWithParams2 (@p1 INT, @p2 VARCHAR(50), @p3 VARCHAR(50)) AS INSERT dbo.TestingTestSenderDBExecuteStoredProcedureWithParams2 (PersonID, FirstName, LastName) VALUES (@p1, @p2, @p3)";
await cmd.ExecuteNonQueryAsync();
}
}
//Job
var rcvr = new ReceiverStmtSqlServer(connectionString, commandType, commandText1, fileNameSerilizeLastRow, parameters1);
var sndAll = new SenderToDBStmtSqlServer(connectionString, commandType, commandText2, parameters2);
//It's not the real sender being used by sndSMTPFiltered
var sndSMTP = new SenderToSMTP(from, to, string.Empty, string.Empty, subject, string.Empty, false, smtpServer, smtpPort, false, requiresAuthentication, user, password);
//Real sender
FilterComparable fltPerson = new FilterComparableGreaterOrEqual(typeof(Int32), 1, "PersonID");
ISend sndSMTPFiltered = new SenderWithFilterComparable(fltPerson, sndSMTP);
var job = new SimpleJob();
job.Receivers.Add(0, rcvr);
job.Senders.Add(0, sndAll);
job.Senders.Add(1, sndSMTPFiltered);
/*
* var job = new SimpleJob();
* job.Receivers.Add(0, rcvr);
* job.Senders.Add(0, sndSMTP);
*
* var j = job.SerializeMe();
* File.WriteAllText("j.json", j);
* Process.Start("Notepad.exe", "j.json");
*/
#endregion
#region act
await job.Execute();
#endregion
#region assert
//Check destination table
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT COUNT(*) AS Cnt FROM TestingTestSenderDBExecuteStoredProcedureWithParams2 tbl FULL JOIN (VALUES (0, 'John 0', 'Doe 0'), (1, 'John 1', 'Doe 1'), (2, 'John 2', 'Doe 2')) chk(PersonID, FirstName, LastName) ON tbl.PersonID = chk.PersonID AND tbl.FirstName = chk.FirstName AND tbl.LastName = chk.LastName WHERE EXISTS(SELECT tbl.PersonID EXCEPT SELECT chk.PersonID) OR EXISTS(SELECT tbl.FirstName EXCEPT SELECT chk.FirstName) OR EXISTS(SELECT tbl.LastName EXCEPT SELECT chk.LastName);";
var cnt = (int)await cmd.ExecuteScalarAsync();
Assert.AreEqual(0, cnt);
}
}
//Check email
//Read message and check Body (plain text)
int numOfEmailFound = 0;
using (var client = new Pop3Client())
{
client.Connect(pop3Server, pop3Port, false);
// Note: since we don't have an OAuth2 token, disable
// the XOAUTH2 authentication mechanism.
client.AuthenticationMechanisms.Remove("XOAUTH2");
client.Authenticate(user, password);
for (int i = 0; i < client.Count; i++)
{
var message = client.GetMessage(i);
if (message.Subject == subject)
{
numOfEmailFound++;
}
}
client.Disconnect(true);
}
Assert.AreEqual(1, numOfEmailFound);
#endregion
}
public async Task TestSimpleJobReceiverToSenderDBExecuteSqlServerStoredProcedures()
{
#region arrange
string connectionString = GetSqlServerConnectionString();
string commandText1 = "dbo.TestReiceverDBExecuteStoredProcedureNoParam2"; // Receiver SP (Source)
string fileNameSerilizeLastRow = string.Empty;
string parameters1 = string.Empty;
string commandText2 = "dbo.TestSenderDBExecuteStoredProcedureWithParams2"; // Sender SP (Destination)
string parameters2 = "@p1=PersonID;@p2=FirstName;@p3=LastName";
//Source
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "IF OBJECT_ID('dbo.TestingTestReiceverDBExecuteStoredProcedureNoParam2') IS NOT NULL DROP TABLE dbo.TestingTestReiceverDBExecuteStoredProcedureNoParam2; CREATE TABLE dbo.TestingTestReiceverDBExecuteStoredProcedureNoParam2 (PersonID INT NOT NULL PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50)); INSERT dbo.TestingTestReiceverDBExecuteStoredProcedureNoParam2 VALUES (0, 'John 0', 'Doe 0'), (1, 'John 1', 'Doe 1');";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "IF OBJECT_ID('dbo.TestReiceverDBExecuteStoredProcedureNoParam2') IS NOT NULL DROP PROCEDURE dbo.TestReiceverDBExecuteStoredProcedureNoParam2;";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "CREATE PROCEDURE dbo.TestReiceverDBExecuteStoredProcedureNoParam2 AS SELECT * FROM dbo.TestingTestReiceverDBExecuteStoredProcedureNoParam2;";
await cmd.ExecuteNonQueryAsync();
}
}
var rcvr = new ReceiverStmtSqlServer(connectionString, commandType, commandText1, fileNameSerilizeLastRow, parameters1);
//Destination
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "IF OBJECT_ID('dbo.TestingTestSenderDBExecuteStoredProcedureWithParams2') IS NOT NULL DROP TABLE dbo.TestingTestSenderDBExecuteStoredProcedureWithParams2; CREATE TABLE dbo.TestingTestSenderDBExecuteStoredProcedureWithParams2 (PersonID INT NOT NULL PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50));";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "IF OBJECT_ID('dbo.TestSenderDBExecuteStoredProcedureWithParams2') IS NOT NULL DROP PROCEDURE dbo.TestSenderDBExecuteStoredProcedureWithParams2;";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "CREATE PROCEDURE dbo.TestSenderDBExecuteStoredProcedureWithParams2 (@p1 INT, @p2 VARCHAR(50), @p3 VARCHAR(50)) AS INSERT dbo.TestingTestSenderDBExecuteStoredProcedureWithParams2 (PersonID, FirstName, LastName) VALUES (@p1, @p2, @p3)";
await cmd.ExecuteNonQueryAsync();
}
}
var snd = new SenderToDBStmtSqlServer(connectionString, commandType, commandText2, parameters2);
//Job
ISimpleJob job = new SimpleJob();
job.Receivers.Add(0, rcvr);
job.Senders.Add(0, snd);
#endregion
#region act
job.Execute().Wait(-1);
#endregion
#region assert
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT COUNT(*) AS Cnt FROM TestingTestSenderDBExecuteStoredProcedureWithParams2 tbl FULL JOIN (VALUES (0, 'John 0', 'Doe 0'), (1, 'John 1', 'Doe 1')) chk(PersonID, FirstName, LastName) ON tbl.PersonID = chk.PersonID AND tbl.FirstName = chk.FirstName AND tbl.LastName = chk.LastName WHERE EXISTS(SELECT tbl.PersonID EXCEPT SELECT chk.PersonID) OR EXISTS(SELECT tbl.FirstName EXCEPT SELECT chk.FirstName) OR EXISTS(SELECT tbl.LastName EXCEPT SELECT chk.LastName);";
var cnt = (int)await cmd.ExecuteScalarAsync();
Assert.AreEqual(0, cnt);
}
}
#endregion
}
public async Task TestSimpleJobReceiverFromIoTHub2SenderToSQLServer()
{
#region arrange
//Send test message
string iotHubUri = "a";
string deviceId = "b";
string deviceKey = "c";
string messageType = "UnitTestSimpleJobReceiverFromIoTHub2SenderToSQLServer" + DateTime.Now.ToString();
var sndToIoTHub = new SenderToAzureIoTHub(iotHubUri, deviceId, deviceKey, messageType);
var m = new Mock <IRow>();
var rows = new List <IRow>();
int nrRows = 2;
for (int i = 0; i < nrRows; i++)
{
var row = new Mock <IRow>();
row.SetupProperty
(
obj => obj.Values,
new Dictionary <string, object>()
{
["PersonID"] = i,
["FirstName"] = "John " + i,
["LastName"] = "Doe " + i
}
);
rows.Add(row.Object);
}
sndToIoTHub.valuesToBeSent = rows.ToArray();
await sndToIoTHub.Send();
//End of Send test message
//Receiver settings
string iotHubConnectionStringEventHubCompatible = "a";
string iotHubMessageEntityEventHubCompatible = "b";
string fileNameLastRow = "TestReceiveAzureIoTHubSimple_LastRow.json";
//Receiver
string connectionString = GetSqlServerConnectionString();
string commandText2 = "dbo.TestReiceverDBExecuteStoredProcedureNoParam4"; // Sender SP (Destination)
string parameters2 = "@p1=PersonID;@p2=FirstName;@p3=LastName";
//Source
if (File.Exists(fileNameLastRow))
{
File.Delete(fileNameLastRow);
}
var rcv = new ReceiverFromAzureIoTHub(iotHubConnectionStringEventHubCompatible, iotHubMessageEntityEventHubCompatible, fileNameLastRow, messageType, -4);
//Destination
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "IF OBJECT_ID('dbo.TestingTestReiceverDBExecuteStoredProcedureNoParam4') IS NOT NULL DROP TABLE dbo.TestingTestReiceverDBExecuteStoredProcedureNoParam4; CREATE TABLE dbo.TestingTestReiceverDBExecuteStoredProcedureNoParam4 (PersonID INT NOT NULL /*PRIMARY KEY*/, FirstName VARCHAR(50), LastName VARCHAR(50));";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "IF OBJECT_ID('dbo.TestReiceverDBExecuteStoredProcedureNoParam4') IS NOT NULL DROP PROCEDURE dbo.TestReiceverDBExecuteStoredProcedureNoParam4;";
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "CREATE PROCEDURE dbo.TestReiceverDBExecuteStoredProcedureNoParam4 (@p1 INT, @p2 VARCHAR(50), @p3 VARCHAR(50)) AS INSERT dbo.TestingTestReiceverDBExecuteStoredProcedureNoParam4 (PersonID, FirstName, LastName) VALUES (@p1, @p2, @p3)";
await cmd.ExecuteNonQueryAsync();
}
}
var snd = new SenderToDBStmtSqlServer(connectionString, commandType, commandText2, parameters2);
//Job
ISimpleJob job = new SimpleJob();
job.Receivers.Add(0, rcv);
job.Senders.Add(0, snd);
//var j = job.SerializeMe();
//File.WriteAllText(@"E:\j2.json", j);
#endregion
#region act
await job.Execute();
#endregion
#region assert
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT COUNT(*) AS Cnt FROM TestingTestReiceverDBExecuteStoredProcedureNoParam4;";
var cnt = (int)await cmd.ExecuteScalarAsync();
Assert.IsTrue(cnt >= 2); // It requires a sender in arrange region
}
}
#endregion
}
