public async Task TestSendEmail() { string from = "*****@*****.**"; string to = "*****@*****.**"; string smtpServer = "smtp.mailtrap.io"; //https://mailtrap.io/ User:"******" Password:"******" without double quotes; Free plan = maximum 50 emails allowed int smtpPort = 2525; string subject = "SenderSMTP.test" + DateTime.Now.ToString("yyyy-MM-ddThh:mm:ss.fffffff"); bool requiresAuthentication = true; string user = "******"; string password = "******"; string pop3Server = "mailtrap.io"; int pop3Port = 9950; // 1100 or 9950 #region arrange //Prepare source data: 2 rows {ID, FirstName, LastName} 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); } var sender = new SenderToSMTP(from, to, string.Empty, string.Empty, subject, string.Empty, false, smtpServer, smtpPort, false, requiresAuthentication, user, password); sender.valuesToBeSent = rows.ToArray(); #endregion #region act //Send message await sender.Send(); #endregion #region assert //Read message and check Body (plain text) bool emailFound = false; 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) { emailFound = true; string[] csvRows = message.TextBody.Split('\n'); string[] csvValues0 = csvRows[0].Split(','); string[] csvValues1 = csvRows[1].Split(','); string[] csvValues2 = csvRows[2].Split(','); Assert.AreEqual("PersonID", csvValues0[0]); Assert.AreEqual("FirstName", csvValues0[1]); Assert.AreEqual("LastName", csvValues0[2]); Assert.AreEqual("0", csvValues1[0]); Assert.AreEqual("John 0", csvValues1[1]); Assert.AreEqual("Doe 0", csvValues1[2]); Assert.AreEqual("1", csvValues2[0]); Assert.AreEqual("John 1", csvValues2[1]); Assert.AreEqual("Doe 1", csvValues2[2]); Assert.AreEqual("", csvRows[3]); client.DeleteMessage(i); } } client.Disconnect(true); } Assert.IsTrue(emailFound); #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 }