/// <summary>
/// Counts the total amount of deferrals for a send.
/// </summary>
/// <param name="sendID">ID of the send to count bounces for.</param>
/// <returns>The amount of deferrals for the send.</returns>
public static int GetFailedCount(string sendID)
{
bool hasSendID = !string.IsNullOrWhiteSpace(sendID);
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = (hasSendID ? @"
declare @internalSendID int
SELECT @internalSendID = mta_send_internalId
FROM man_mta_send WITH(nolock)
WHERE mta_send_id = @sndID
" : string.Empty) + @"
SELECT COUNT(*)
FROM(
SELECT 1 as 'Col'
FROM man_mta_transaction as [tran] WITH(nolock)
JOIN man_mta_msg as [msg] WITH(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id
JOIN man_ip_ipAddress as [ip] ON [tran].ip_ipAddress_id = [ip].ip_ipAddress_id
WHERE mta_transactionStatus_id IN (2, 3, 6) --// Todo: Make this enum!
" + (hasSendID ? "AND [msg].mta_send_internalId = @internalSendID" : string.Empty) + @"
GROUP BY mta_transactionStatus_id, mta_transaction_serverResponse, mta_transaction_serverHostname,[ip].ip_ipAddress_hostname, [ip].ip_ipAddress_ipAddress
) as [sorted]" ;
if (hasSendID)
{
cmd.Parameters.AddWithValue("@sndID", sendID);
}
conn.Open();
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
}
/// <summary>
/// Gets the total deferred and rejected counts.
/// </summary>
/// <param name="deferred">Returns the deferred count.</param>
/// <param name="rejected">Returns the rejected count.</param>
public static void GetBounceDeferredAndRejected(out long deferred, out long rejected)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
declare @deferred bigint
declare @rejected bigint
SELECT @deferred = COUNT(*)
FROM man_mta_transaction WITH(nolock)
WHERE mta_transactionStatus_id = 1
SELECT @rejected = COUNT(*)
FROM man_mta_transaction WITH(nolock)
WHERE mta_transactionStatus_id IN (2, 3, 6)
SELECT @deferred as 'Deferred', @rejected as 'Rejected'";
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
deferred = Convert.ToInt64(reader["Deferred"]);
rejected = Convert.ToInt64(reader["Rejected"]);
reader.Close();
}
}
/// <summary>
/// Saves the OutboundRule to the database.
/// </summary>
/// <param name="outboundRule">The OutboundRule to save.</param>
public static void Save(OutboundRule outboundRule)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
IF EXISTS (SELECT 1 FROM man_rle_rule WHERE rle_mxPattern_id = @mxPatternID AND rle_ruleType_id = @type)
BEGIN
UPDATE man_rle_rule
SET rle_rule_value = @value
WHERE rle_mxPattern_id = @mxPatternID AND rle_ruleType_id = @type
END
ELSE
BEGIN
INSERT INTO man_rle_rule(rle_mxPattern_id, rle_ruleType_id, rle_rule_value)
VALUES(@mxPatternID, @type, @value)
END
";
cmd.Parameters.AddWithValue("@mxPatternID", outboundRule.OutboundMxPatternID);
cmd.Parameters.AddWithValue("@type", (int)outboundRule.Type);
cmd.Parameters.AddWithValue("@value", outboundRule.Value);
conn.Open();
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Gets information about VirtualMTA sends for the specified send.
/// </summary>
/// <param name="sendID">ID of the send to get information for.</param>
/// <returns>Information about the usage of each VirtualMTA in the send.</returns>
public static VirtualMtaSendInfo[] GetSendVirtualMTAStats(string sendID)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
--// Get the internal Send ID
DECLARE @internalSendId int
SELECT @internalSendId = [snd].mta_send_internalId
FROM man_mta_send as [snd] WITH(nolock)
WHERE [snd].mta_send_id = @sndID
DECLARE @usedIpAddressIds table(ip_ipAddress_id int)
--// Get the IP addresses used by the send
INSERT INTO @usedIpAddressIds
SELECT DISTINCT(ip_ipAddress_id)
FROM man_mta_transaction as [tran] WITH(nolock)
JOIN man_mta_msg as [msg] with(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id
WHERE [msg].mta_send_internalId = @internalSendId
--// Get the actual data
SELECT [ip].*,
(SELECT COUNT(*) FROM man_mta_transaction as [tran] with(nolock) JOIN man_mta_msg as [msg] with(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [tran].ip_ipAddress_id = [ip].ip_ipAddress_id AND [msg].mta_send_internalId = @internalSendId AND [tran].mta_transactionStatus_id = 4) AS 'Accepted',
(SELECT COUNT(*) FROM man_mta_transaction as [tran] with(nolock) JOIN man_mta_msg as [msg] with(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [tran].ip_ipAddress_id = [ip].ip_ipAddress_id AND [msg].mta_send_internalId = @internalSendId AND ([tran].mta_transactionStatus_id = 2 OR [tran].mta_transactionStatus_id = 3 OR [tran].mta_transactionStatus_id = 6)) AS 'Rejected',
(SELECT COUNT(*) FROM man_mta_transaction as [tran] with(nolock) JOIN man_mta_msg as [msg] with(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [tran].ip_ipAddress_id = [ip].ip_ipAddress_id AND [msg].mta_send_internalId = @internalSendId AND [tran].mta_transactionStatus_id = 5) AS 'Throttled',
(SELECT COUNT(*) FROM man_mta_transaction as [tran] with(nolock) JOIN man_mta_msg as [msg] with(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [tran].ip_ipAddress_id = [ip].ip_ipAddress_id AND [msg].mta_send_internalId = @internalSendId AND [tran].mta_transactionStatus_id = 1) AS 'Deferred'
FROM man_ip_ipAddress as [ip]
WHERE [ip].ip_ipAddress_id IN (SELECT * FROM @usedIpAddressIds)";
cmd.Parameters.AddWithValue("@sndID", sendID);
return(DataRetrieval.GetCollectionFromDatabase <VirtualMtaSendInfo>(cmd, CreateAndFillVirtualMtaSendInfo).ToArray());
}
}
/// <summary>
/// Gets a page sends information.
/// </summary>
/// <param name="pageSize">Size of the page to get.</param>
/// <param name="pageNum">The page to get.</param>
/// <returns>SendInfoCollection of the data page.</returns>
public static SendInfoCollection GetSends(int pageSize, int pageNum)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"DECLARE @sends table (RowNum int,
mta_send_internalId int)
INSERT INTO @sends
SELECT [sends].RowNumber, [sends].mta_send_internalId
FROM (SELECT (ROW_NUMBER() OVER(ORDER BY mta_send_createdTimestamp DESC)) as RowNumber, man_mta_send.mta_send_internalId
FROM man_mta_send with(nolock)) [sends]
WHERE [sends].RowNumber >= " + ((pageNum * pageSize) - pageSize + 1) + " AND [sends].RowNumber <= " + (pageSize * pageNum) + @"
SELECT [send].*,
mta_send_messages AS 'Messages',
mta_send_accepted AS 'Accepted',
mta_send_rejected AS 'Rejected',
([send].mta_send_messages - (mta_send_accepted + mta_send_rejected)) AS 'Waiting',
(SELECT COUNT(*) FROM man_mta_transaction as [tran] with(nolock) JOIN man_mta_msg as [msg] ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [msg].mta_send_internalId = [send].mta_send_internalId AND [tran].mta_transactionStatus_id = 5) AS 'Throttled',
(SELECT COUNT(*) FROM man_mta_transaction as [tran] with(nolock) JOIN man_mta_msg as [msg] ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [msg].mta_send_internalId = [send].mta_send_internalId AND [tran].mta_transactionStatus_id = 1) AS 'Deferred',
(SELECT MAX(mta_transaction_timestamp) FROM man_mta_transaction as [tran] with(nolock) JOIN man_mta_msg as [msg] ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [msg].mta_send_internalId = [send].mta_send_internalId) AS 'LastTransactionTimestamp'
FROM man_mta_send as [send] with(nolock)
WHERE [send].mta_send_internalId in (SELECT [s].mta_send_internalId FROM @sends as [s])
ORDER BY [send].mta_send_createdTimestamp DESC";
cmd.CommandTimeout = 90; // Query can take a while to run due to the size of the Transactions table.
return(new SendInfoCollection(DataRetrieval.GetCollectionFromDatabase <SendInfo>(cmd, CreateAndFillSendInfo)));
}
}
/// <summary>
/// Save the specified Virtual MTA to the Database.
/// </summary>
/// <param name="vmta"></param>
public static void Save(VirtualMTA vmta)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
IF EXISTS(SELECT 1 FROM man_ip_ipAddress WHERE ip_ipAddress_id = @id)
BEGIN
UPDATE man_ip_ipAddress
SET ip_ipAddress_ipAddress = @ipAddress,
ip_ipAddress_hostname = @hostname,
ip_ipAddress_isInbound = @isInbound,
ip_ipAddress_isOutbound = @isOutbound
WHERE ip_ipAddress_id = @id
END
ELSE
BEGIN
INSERT INTO man_ip_ipAddress(ip_ipAddress_ipAddress, ip_ipAddress_hostname, ip_ipAddress_isInbound, ip_ipAddress_isOutbound)
VALUES(@ipAddress, @hostname, @isInbound, @isOutbound)
END
";
cmd.Parameters.AddWithValue("@id", vmta.ID);
cmd.Parameters.AddWithValue("@ipAddress", vmta.IPAddress.ToString());
cmd.Parameters.AddWithValue("@hostname", vmta.Hostname);
cmd.Parameters.AddWithValue("@isInbound", vmta.IsSmtpInbound);
cmd.Parameters.AddWithValue("@isOutbound", vmta.IsSmtpOutbound);
conn.Open();
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Get a count of all the sends in the MantaMTA database.
/// </summary>
/// <returns>Count of all Sends.</returns>
public static long GetSendsCount()
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT COUNT(*) FROM man_mta_send";
conn.Open();
return(Convert.ToInt64(cmd.ExecuteScalar()));
}
}
/// <summary>
/// Gets a summary of the transactions made in the last one hour.
/// </summary>
/// <returns>Transaction Summary</returns>
public static SendTransactionSummaryCollection GetLastHourTransactionSummary()
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT [tran].mta_transactionStatus_id, COUNT(*) AS 'Count'
FROM man_mta_transaction as [tran] WITH(nolock)
WHERE [tran].mta_transaction_timestamp >= DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY [tran].mta_transactionStatus_id";
return(new SendTransactionSummaryCollection(DataRetrieval.GetCollectionFromDatabase <SendTransactionSummary>(cmd, CreateAndFillTransactionSummary)));
}
}
/// <summary>
/// Gets a summary of a virtual MTAs transaction history.
/// </summary>
/// <param name="ipAddressId"></param>
/// <returns></returns>
public static SendTransactionSummaryCollection GetSendSummaryForIpAddress(int ipAddressId)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT mta_transactionStatus_id, COUNT(*) AS 'Count'
FROM man_mta_transaction
WHERE ip_ipAddress_id = @ipAddressId
GROUP BY mta_transactionStatus_id";
cmd.Parameters.AddWithValue("@ipAddressId", ipAddressId);
return(new SendTransactionSummaryCollection(DataRetrieval.GetCollectionFromDatabase <SendTransactionSummary>(cmd, CreateAndFillSendTransactionSummaryFromRecord)));
}
}
/// <summary>
/// Deletes the specified Virtual MTA from the Database.
/// </summary>
/// <param name="id">ID of Virtual MTA to Delete.</param>
public static void Delete(int id)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
DELETE FROM man_ip_ipAddress WHERE ip_ipAddress_id = @id
DELETE FROM man_ip_groupMembership WHERE ip_ipAddress_id = @id";
cmd.Parameters.AddWithValue("@id", id);
conn.Open();
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Gets information about the speed of sending over the last one hour.
/// </summary>
/// <returns>SendSpeedInfo</returns>
public static SendSpeedInfo GetLastHourSendSpeedInfo()
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT COUNT(*) AS 'Count', [tran].mta_transactionStatus_id, CONVERT(smalldatetime, [tran].mta_transaction_timestamp) as 'mta_transaction_timestamp'
FROM man_mta_transaction as [tran] WITH (nolock)
WHERE [tran].mta_transaction_timestamp >= DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY [tran].mta_transactionStatus_id, CONVERT(smalldatetime, [tran].mta_transaction_timestamp)
ORDER BY CONVERT(smalldatetime, [tran].mta_transaction_timestamp)";
return(new SendSpeedInfo(DataRetrieval.GetCollectionFromDatabase <SendSpeedInfoItem>(cmd, CreateAndFillSendSpeedInfoItemFromRecord)));
}
}
/// <summary>
/// Saves the specified OutboundMxPattern to the database.
/// </summary>
/// <param name="mxPattern">The OutboundMxPattern to save.</param>
/// <returns>ID of the OutboundMxPattern.</returns>
public static int Save(OutboundMxPattern mxPattern)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
IF EXISTS (SELECT 1 FROM man_rle_mxPattern WHERE rle_mxPattern_id = @mxPatternID)
BEGIN
UPDATE man_rle_mxPattern
SET rle_mxPattern_name = @name,
rle_mxPattern_description = @description,
rle_patternType_id = @type,
rle_mxPattern_value = @value,
ip_ipAddress_id = @ipAddressID
WHERE rle_mxPattern_id = @mxPatternID
SELECT @mxPatternID
END
ELSE
BEGIN
INSERT INTO man_rle_mxPattern(rle_mxPattern_name, rle_mxPattern_description, rle_patternType_id, rle_mxPattern_value, ip_ipAddress_id)
VALUES(@name, @description, @type, @value, @ipAddressID)
SELECT @@IDENTITY
END
";
cmd.Parameters.AddWithValue("@mxPatternID", mxPattern.ID);
cmd.Parameters.AddWithValue("@name", mxPattern.Name);
if (mxPattern.Description == null)
{
cmd.Parameters.AddWithValue("@description", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@description", mxPattern.Description);
}
cmd.Parameters.AddWithValue("@type", (int)mxPattern.Type);
cmd.Parameters.AddWithValue("@value", mxPattern.Value);
if (mxPattern.LimitedToOutboundIpAddressID.HasValue)
{
cmd.Parameters.AddWithValue("@ipAddressID", mxPattern.LimitedToOutboundIpAddressID.Value);
}
else
{
cmd.Parameters.AddWithValue("@ipAddressID", DBNull.Value);
}
conn.Open();
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
}
/// <summary>
/// Deletes the MX Pattern and its rules from the database.
/// </summary>
/// <param name="patternID">ID of the pattern to delete.</param>
public static void Delete(int mxPatternID)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
IF(@mxPatternID <> " + MtaParameters.OUTBOUND_RULES_DEFAULT_PATTERN_ID + @")
BEGIN
DELETE FROM man_rle_mxPattern WHERE rle_mxPattern_id = @mxPatternID
DELETE FROM man_rle_rule WHERE rle_mxPattern_id = @mxPatternID
END
";
cmd.Parameters.AddWithValue("@mxPatternID", mxPatternID);
conn.Open();
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Runs a provided SQL query and returns a DataTable of the results.
/// </summary>
/// <param name="sqlQuery"></param>
/// <returns></returns>
protected static DataTable GetDataTable(string sqlQuery)
{
DataTable data = new DataTable();
using (SqlConnection connection = MantaDB.GetSqlConnection())
{
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sqlQuery;
connection.Open();
data.Load(command.ExecuteReader());
connection.Close();
}
return(data);
}
/// <summary>
/// Gets the amount of messages currently the queue with the specified statuses.
/// </summary>
/// <returns>Count of the messages waiting in the queue.</returns>
public static long GetQueueCount(SendStatus[] sendStatus)
{
return(0);
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT SUM(s.mta_send_messages) - SUM(s.mta_send_accepted + s.mta_send_rejected)
FROM man_mta_send as s WITH(nolock)
WHERE s.mta_sendStatus_id in (" + string.Join(",", Array.ConvertAll <SendStatus, int>(sendStatus, s => (int)s)) + ")";
conn.Open();
Int64 result = Convert.ToInt64(cmd.ExecuteScalar());
if (result < 0)
{
return(0);
}
return(result);
}
}
/// <summary>
/// Gets information about the speed of a send.
/// </summary>
/// <param name="sendID">ID of the send to get speed information about.</param>
/// <returns>SendSpeedInfo</returns>
public static SendSpeedInfo GetSendSpeedInfo(string sendID)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
DECLARE @internalSendID int
SELECT @internalSendID = mta_send_internalId
FROM man_mta_send
WHERE mta_send_id = @sndID
SELECT COUNT(*) AS 'Count', [tran].mta_transactionStatus_id, CONVERT(smalldatetime, [tran].mta_transaction_timestamp) as 'mta_transaction_timestamp'
FROM man_mta_transaction as [tran] with(nolock)
JOIN man_mta_msg AS [msg] with(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id
WHERE [msg].mta_send_internalId = @internalSendID
GROUP BY [tran].mta_transactionStatus_id, CONVERT(smalldatetime, [tran].mta_transaction_timestamp)
ORDER BY CONVERT(smalldatetime, [tran].mta_transaction_timestamp)";
cmd.Parameters.AddWithValue("@sndID", sendID);
return(new SendSpeedInfo(DataRetrieval.GetCollectionFromDatabase <SendSpeedInfoItem>(cmd, CreateAndFillSendSpeedInfoItemFromRecord)));
}
}
/// <summary>
/// Gets a data page about bounces from the transactions table for a send.
/// </summary>
/// <param name="sendID">Send to get data for.</param>
/// <param name="pageNum">The page to get.</param>
/// <param name="pageSize">The size of the data pages.</param>
/// <returns>An array of BounceInfo from the data page.</returns>
public static BounceInfo[] GetBounceInfo(string sendID, int pageNum, int pageSize)
{
bool hasSendID = !string.IsNullOrWhiteSpace(sendID);
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = (hasSendID ? @"
declare @internalSendID int
SELECT @internalSendID = mta_send_internalId
FROM man_mta_send WITH(nolock)
WHERE mta_send_id = @sndID
" : string.Empty) + @"
SELECT [sorted].*
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY count(*) DESC, mta_transaction_serverHostname) as 'Row',
mta_transactionStatus_id,
mta_transaction_serverResponse,
mta_transaction_serverHostname as 'mta_transaction_serverHostname',
[ip].ip_ipAddress_hostname,
[ip].ip_ipAddress_ipAddress, COUNT(*) as 'Count',
MAX(mta_transaction_timestamp) as 'LastOccurred'
FROM man_mta_transaction as [tran] with(nolock)
JOIN man_mta_msg as [msg] with(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id
JOIN man_ip_ipAddress as [ip] ON [tran].ip_ipAddress_id = [ip].ip_ipAddress_id
WHERE mta_transactionStatus_id IN (1, 2, 3, 6) --// Todo: Make this enum!
" + (hasSendID ? "AND [msg].mta_send_internalId = @internalSendID " : string.Empty) + @"
GROUP BY mta_transactionStatus_id, mta_transaction_serverResponse, mta_transaction_serverHostname,[ip].ip_ipAddress_hostname, [ip].ip_ipAddress_ipAddress
) as [sorted]
WHERE [Row] >= " + (((pageNum * pageSize) - pageSize) + 1) + " AND [Row] <= " + (pageNum * pageSize);
if (hasSendID)
{
cmd.Parameters.AddWithValue("@sndID", sendID);
}
return(DataRetrieval.GetCollectionFromDatabase <BounceInfo>(cmd, CreateAndFillBounceInfo).ToArray());
}
}
/// <summary>
/// Gets the most common bounces from the last hour.
/// </summary>
/// <param name="count">Amount of bounces to get.</param>
/// <returns>Information about the bounces</returns>
public static BounceInfo[] GetLastHourBounceInfo(int count)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT TOP " + count + @" ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, mta_transaction_serverHostname) as 'Row',
mta_transactionStatus_id,
mta_transaction_serverResponse,
mta_transaction_serverHostname as 'mta_transaction_serverHostname',
[ip].ip_ipAddress_hostname,
[ip].ip_ipAddress_ipAddress, COUNT(*) as 'Count',
MAX(mta_transaction_timestamp) as 'LastOccurred'
FROM man_mta_transaction as [tran] WITH (nolock)
JOIN man_mta_msg as [msg] WITH(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id
JOIN man_ip_ipAddress as [ip] ON [tran].ip_ipAddress_id = [ip].ip_ipAddress_id
WHERE [tran].mta_transaction_timestamp >= DATEADD(HOUR, -1, GETUTCDATE())
AND mta_transactionStatus_id IN (1, 2, 3, 6)
AND mta_transaction_serverHostname NOT LIKE ''
GROUP BY mta_transactionStatus_id, mta_transaction_serverResponse, mta_transaction_serverHostname,[ip].ip_ipAddress_hostname, [ip].ip_ipAddress_ipAddress
ORDER BY COUNT(*) DESC";
return(DataRetrieval.GetCollectionFromDatabase <BounceInfo>(cmd, CreateAndFillBounceInfo).ToArray());
}
}
/// <summary>
/// Does the actual bulk importing from RabbitMQ to SQL Server.
/// </summary>
private void DoSqlBulkInsertFromRabbitMQ()
{
// Keep going until Manta is stopping.
while (!_isStopping)
{
try
{
// Get queued messages for bulk importing.
IList <MtaMessage> recordsToImportToSql = RabbitMq.RabbitMqInboundQueueManager.Dequeue(100).Result;
// If there are no messages to import then sleep and try again.
if (recordsToImportToSql == null || recordsToImportToSql.Count == 0)
{
Thread.Sleep(RABBITMQ_EMPTY_QUEUE_SLEEP_TIME);
continue;
}
DataTable dt = new DataTable();
dt.Columns.Add("mta_msg_id", typeof(Guid));
dt.Columns.Add("mta_send_internalId", typeof(int));
dt.Columns.Add("mta_msg_rcptTo", typeof(string));
dt.Columns.Add("mta_msg_mailFrom", typeof(string));
foreach (MtaMessage msg in recordsToImportToSql)
{
dt.Rows.Add(new object[] { msg.ID, msg.InternalSendID, msg.RcptTo[0], msg.MailFrom });
}
try
{
// Create a record of the messages in SQL server.
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlBulkCopy bulk = new SqlBulkCopy(conn);
bulk.DestinationTableName = "man_mta_msg_staging";
foreach (DataColumn c in dt.Columns)
{
bulk.ColumnMappings.Add(c.ColumnName, c.ColumnName);
}
conn.Open();
bulk.WriteToServer(dt);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
BEGIN TRANSACTION
MERGE man_mta_msg AS target
USING (SELECT * FROM man_mta_msg_staging) AS source
ON (target.[mta_msg_id] = source.[mta_msg_id])
WHEN NOT MATCHED THEN
INSERT ([mta_msg_id], [mta_send_internalId], [mta_msg_rcptTo], [mta_msg_mailFrom])
VALUES (source.[mta_msg_id], source.[mta_send_internalId], source.[mta_msg_rcptTo], source.[mta_msg_mailFrom]);
DELETE FROM [man_mta_msg_staging]
COMMIT TRANSACTION";
cmd.ExecuteNonQuery();
}
RabbitMq.RabbitMqManager.Ack(RabbitMq.RabbitMqManager.RabbitMqQueue.Inbound, recordsToImportToSql.Max(r => r.RabbitMqDeliveryTag), true);
}
catch (Exception ex)
{
Logging.Warn("Server Queue Manager", ex);
}
}
catch (Exception)
{
//Logging.Error("Bulk Importer Error", ex);
}
}
_hasStopped = true;
}