/// <summary>
/// Gets the sendID's internal ID from the database. If the record doesn't exist
/// then it will be created.
/// </summary>
/// <param name="sendID">The SendID to get the internal ID for.</param>
/// <returns></returns>
public static async Task <Send> CreateAndGetInternalSendIDAsync(string sendID)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
BEGIN TRANSACTION
MERGE man_mta_send WITH (HOLDLOCK) AS target
USING (SELECT @sndID) AS source(mta_send_id)
ON (target.mta_send_id = source.mta_send_id)
WHEN NOT MATCHED THEN
INSERT (mta_send_id, mta_sendStatus_id, mta_send_internalId, mta_send_createdTimestamp)
VALUES (@sndID, @activeStatusID, ISNULL((SELECT MAX(mta_send_internalID) + 1 FROM man_mta_send), 1), GETUTCDATE());
COMMIT TRANSACTION
SELECT *
FROM man_mta_send WITH(nolock)
WHERE mta_send_id = @sndID";
cmd.Parameters.AddWithValue("@sndID", sendID);
cmd.Parameters.AddWithValue("@activeStatusID", (int)SendStatus.Active);
return(await DataRetrieval.GetSingleObjectFromDatabaseAsync <Send>(cmd, CreateAndFillSendFromRecord).ConfigureAwait(false));
}
}
/// <summary>
/// Saves the MantaEvent to the database.
/// </summary>
/// <param name="evn">The Manta Event to save.</param>
/// <returns>ID of the MantaEvent.</returns>
public static async Task <int> SaveAsync(MantaEvent evn)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
IF EXISTS (SELECT 1 FROM man_evn_event WHERE evn_event_id = @eventID)
BEGIN
UPDATE man_evn_event
SET evn_type_id = @eventType,
evn_event_timestamp = @timestamp,
evn_event_emailAddress = @emailAddress,
snd_send_id = @sendId,
evn_event_forwarded = @forwarded
WHERE evn_event_id = @eventID
END
ELSE
BEGIN
INSERT INTO man_evn_event(evn_type_id, evn_event_timestamp, evn_event_emailAddress, snd_send_id, evn_event_forwarded)
VALUES(@eventType, @timestamp, @emailAddress, @sendId, @forwarded)
SET @eventID = @@IDENTITY
END
";
cmd.Parameters.AddWithValue("@eventID", evn.ID);
cmd.Parameters.AddWithValue("@eventType", (int)evn.EventType);
cmd.Parameters.AddWithValue("@timestamp", evn.EventTime);
cmd.Parameters.AddWithValue("@emailAddress", evn.EmailAddress);
cmd.Parameters.AddWithValue("@sendId", evn.SendID);
cmd.Parameters.AddWithValue("@forwarded", evn.Forwarded);
if (evn is MantaBounceEvent)
{
cmd.CommandText += @"
IF EXISTS (SELECT 1 FROM man_evn_bounceEvent WHERE evn_event_id = @eventId)
UPDATE man_evn_bounceEvent
SET evn_bounceCode_id = @bounceCode,
evn_bounceEvent_message = @message,
evn_bounceType_id = @bounceType
WHERE evn_event_id = @eventId
ELSE
INSERT INTO man_evn_bounceEvent(evn_event_id, evn_bounceCode_id, evn_bounceEvent_message, evn_bounceType_id)
VALUES(@eventId, @bounceCode, @message, @bounceType)
";
cmd.Parameters.AddWithValue("@bounceCode", (int)(evn as MantaBounceEvent).BounceInfo.BounceCode);
cmd.Parameters.AddWithValue("@message", (evn as MantaBounceEvent).Message);
cmd.Parameters.AddWithValue("@bounceType", (int)(evn as MantaBounceEvent).BounceInfo.BounceType);
}
cmd.CommandText += @"SELECT @eventID
";
await conn.OpenAsync();
return(Convert.ToInt32(await cmd.ExecuteScalarAsync()));
}
}
/// <summary>
/// Logs an MTA Transaction to the database.
/// </summary>
public static async Task <bool> LogTransactionAsync(MtaMessage msg, TransactionStatus status, string svrResponse, VirtualMTA ipAddress, MXRecord mxRecord)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
BEGIN TRANSACTION
INSERT INTO man_mta_transaction (mta_msg_id, ip_ipAddress_id, mta_transaction_timestamp, mta_transactionStatus_id, mta_transaction_serverResponse, mta_transaction_serverHostname)
VALUES(@msgID, @ipAddressID, GETUTCDATE(), @status, @serverResponse, @serverHostname)";
switch (status)
{
case TransactionStatus.Discarded:
case TransactionStatus.Failed:
case TransactionStatus.TimedOut:
cmd.CommandText += @"UPDATE man_mta_send
SET mta_send_rejected = mta_send_rejected + 1
WHERE mta_send_internalID = @sendInternalID" ;
break;
case TransactionStatus.Success:
cmd.CommandText += @"UPDATE man_mta_send
SET mta_send_accepted = mta_send_accepted + 1
WHERE mta_send_internalID = @sendInternalID" ;
break;
}
cmd.CommandText += " COMMIT TRANSACTION";
cmd.Parameters.AddWithValue("@sendInternalID", msg.InternalSendID);
cmd.Parameters.AddWithValue("@msgID", msg.ID);
if (ipAddress != null)
{
cmd.Parameters.AddWithValue("@ipAddressID", ipAddress.ID);
}
else
{
cmd.Parameters.AddWithValue("@ipAddressID", DBNull.Value);
}
if (mxRecord != null)
{
cmd.Parameters.AddWithValue("@serverHostname", mxRecord.Host);
}
else
{
cmd.Parameters.AddWithValue("@serverHostname", DBNull.Value);
}
cmd.Parameters.AddWithValue("@status", (int)status);
cmd.Parameters.AddWithValue("@serverResponse", svrResponse);
await conn.OpenAsync();
await cmd.ExecuteNonQueryAsync();
return(true);
}
}
/// <summary>
/// Deletes all of the local domains from the database.
/// </summary>
public static void ClearLocalDomains()
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"DELETE FROM man_cfg_localDomain";
conn.Open();
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Gets all of the Virtual MTA Groups from the database; doesn't include Virtual MTA objects.
/// </summary>
/// <returns></returns>
public static IList <VirtualMtaGroup> GetVirtualMtaGroups()
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT *
FROM man_ip_group";
return(DataRetrieval.GetCollectionFromDatabase <VirtualMtaGroup>(cmd, CreateAndFillVirtualMtaGroup));
}
}
/// <summary>
/// Gets all of the MantaEvents from the database.
/// </summary>
/// <returns>Collection of MantaEvent objects.</returns>
public static IList <MantaEvent> GetEvents()
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT [evt].*, [bnc].evn_bounceCode_id, [bnc].evn_bounceEvent_message, [bnc].evn_bounceType_id
FROM man_evn_event AS [evt]
LEFT JOIN man_evn_bounceEvent AS [bnc] ON [evt].evn_event_id = [bnc].evn_event_id";
return(DataRetrieval.GetCollectionFromDatabase <MantaEvent> (cmd, CreateAndFillMantaEventFromRecord));
}
}
/// <summary>
/// Gets an array of the local domains from the database.
/// All domains are toLowered!
/// </summary>
/// <returns></returns>
public static IList <LocalDomain> GetLocalDomainsArray()
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT *
FROM man_cfg_localDomain";
return(DataRetrieval.GetCollectionFromDatabase <LocalDomain> (cmd, CreateAndFillLocalDomainFromRecord));
}
}
/// <summary>
/// ExecuteScalar getting value of colName in man_cfg_para
/// </summary>
/// <param name="colName"></param>
/// <returns></returns>
private static object GetColumnValue(string colName)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT " + colName + @"
FROM man_cfg_para";
conn.Open();
return(cmd.ExecuteScalar());
}
}
/// <summary>
/// Gets a collection of the Virtual MTAs that belong to a Virtual MTA Group from the database.
/// </summary>
/// <param name="groupID">ID of the Virtual MTA Group to get Virtual MTAs for.</param>
/// <returns></returns>
public static IList <VirtualMTA> GetVirtualMtasInVirtualMtaGroup(int groupID)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT *
FROM man_ip_ipAddress as [ip]
WHERE [ip].ip_ipAddress_id IN (SELECT [grp].ip_ipAddress_id FROM man_ip_groupMembership as [grp] WHERE [grp].ip_group_id = @groupID) ";
cmd.Parameters.AddWithValue("@groupID", groupID);
return(DataRetrieval.GetCollectionFromDatabase <VirtualMTA>(cmd, CreateAndFillVirtualMtaFromRecord));
}
}
/// <summary>
/// Get the Outbound Rules from the database.
/// </summary>
/// <returns></returns>
public static IList <OutboundRule> GetOutboundRules()
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT *
FROM man_rle_rule";
return(DataRetrieval.GetCollectionFromDatabase <OutboundRule>(cmd, CreateAndFillOutboundRule));
}
}
/// <summary>
/// Gets the return path domain.
/// </summary>
public static string GetReturnPathDomain()
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT [dmn].cfg_localDomain_domain
FROM man_cfg_localDomain as [dmn]
WHERE [dmn].cfg_localDomain_id = (SELECT TOP 1 [para].cfg_para_returnPathDomain_id FROM man_cfg_para as [para])";
conn.Open();
return(cmd.ExecuteScalar().ToString());
}
}
/// <summary>
/// Gets the specified send.
/// </summary>
/// <param name="internalSendID">Internal ID of the Send to get.</param>
/// <returns>The specified Send or NULL if none with the ID exist.</returns>
public static async Task <Send> GetSendAsync(int internalSendID)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT *
FROM man_mta_send WITH(NOLOCK)
WHERE mta_send_internalId = @internalSndID";
cmd.Parameters.AddWithValue("@internalSndID", internalSendID);
return(await DataRetrieval.GetSingleObjectFromDatabaseAsync <Send>(cmd, CreateAndFillSendFromRecord));
}
}
/// <summary>
/// Gets a single MTA IP Addresses from the Database.
/// </summary>
/// <returns></returns>
public static VirtualMTA GetVirtualMta(int id)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT *
FROM man_ip_ipAddress
WHERE ip_ipAddress_id = @id";
cmd.Parameters.AddWithValue("@id", id);
return(DataRetrieval.GetSingleObjectFromDatabase <VirtualMTA>(cmd, CreateAndFillVirtualMtaFromRecord));
}
}
/// <summary>
/// Saves the specified value to the column in the config table.
/// </summary>
/// <param name="colName">Name of the column to set.</param>
/// <param name="value">Value to set.</param>
private static void SetColumnValue(string colName, object value)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
UPDATE man_cfg_para
SET " + colName + @" = @value";
conn.Open();
cmd.Parameters.AddWithValue("@value", value);
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Get the Outbound MX Patterns from the database.
/// </summary>
/// <returns></returns>
public static IList <OutboundMxPattern> GetOutboundRulePatterns()
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT *
FROM man_rle_mxPattern
ORDER BY rle_mxPattern_id DESC"; // Order descending so default -1 is always at the bottom!
return(DataRetrieval.GetCollectionFromDatabase <OutboundMxPattern>(cmd, CreateAndFillOutboundMxPattern));
}
}
/// <summary>
/// Gets a MantaEvent from the database.
/// </summary>
/// <returns>The event from the database of NULL if one wasn't found with the ID</returns>
public static MantaEvent GetEvent(int ID)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT [evt].*, [bnc].evn_bounceCode_id, [bnc].evn_bounceEvent_message, [bnc].evn_bounceType_id
FROM man_evn_event AS [evt]
LEFT JOIN man_evn_bounceEvent AS [bnc] ON [evt].evn_event_id = [bnc].evn_event_id
WHERE [evt].evn_event_id = @eventId";
cmd.Parameters.AddWithValue("@eventId", ID);
return(DataRetrieval.GetSingleObjectFromDatabase <MantaEvent>(cmd, CreateAndFillMantaEventFromRecord));
}
}
/// <summary>
/// Gets a Virtual MTA Group from the database; doesn't include Virtual MTA objects.
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public static VirtualMtaGroup GetVirtualMtaGroup(int id)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT *
FROM man_ip_group as [grp]
WHERE [grp].ip_group_id = @groupID";
cmd.Parameters.AddWithValue("@groupID", id);
return(DataRetrieval.GetSingleObjectFromDatabase <VirtualMtaGroup>(cmd, CreateAndFillVirtualMtaGroup));
}
}
/// <summary>
/// Saves the virtual mta group to the database.
/// </summary>
/// <param name="grp">Group to save.</param>
public static void Save(VirtualMtaGroup grp)
{
StringBuilder groupMembershipInserts = new StringBuilder();
foreach (VirtualMTA vmta in grp.VirtualMtaCollection)
{
groupMembershipInserts.AppendFormat(@"{1}INSERT INTO man_ip_groupMembership(ip_group_id, ip_ipAddress_id)
VALUES(@id,{0}){1}", vmta.ID, Environment.NewLine);
}
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
BEGIN TRANSACTION
IF EXISTS(SELECT 1 FROM man_ip_group WHERE ip_group_id = @id)
UPDATE man_ip_group
SET ip_group_name = @name,
ip_group_description = @description
WHERE ip_group_id = @id
ELSE
BEGIN
INSERT INTO man_ip_group(ip_group_name, ip_group_description)
VALUES(@name, @description)
SELECT @id = @@IDENTITY
END
DELETE
FROM man_ip_groupMembership
WHERE ip_group_id = @id
" + groupMembershipInserts.ToString() + @"
COMMIT TRANSACTION";
cmd.Parameters.AddWithValue("@id", grp.ID);
cmd.Parameters.AddWithValue("@name", grp.Name);
if (grp.Description == null)
{
cmd.Parameters.AddWithValue("@description", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@description", grp.Description);
}
conn.Open();
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Gets <param name="maxEventsToGet"/> amount of Events that need forwarding from the database.
/// </summary>
public static IList <MantaEvent> GetEventsForForwarding(int maxEventsToGet)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT TOP " + maxEventsToGet + @" [evt].*, [bnc].evn_bounceCode_id, [bnc].evn_bounceEvent_message, [bnc].evn_bounceType_id
FROM man_evn_event AS [evt]
LEFT JOIN man_evn_bounceEvent AS [bnc] ON [evt].evn_event_id = [bnc].evn_event_id
WHERE evn_event_forwarded = 0
ORDER BY evn_event_id ASC";
return(DataRetrieval.GetCollectionFromDatabase <MantaEvent>(cmd, CreateAndFillMantaEventFromRecord));
}
}
/// <summary>
/// Saves the array of IP Address that are allowed to relay messages through MantaMTA.
/// Overwrites the existing addresses.
/// </summary>
/// <param name="addresses">IP Addresses to allow relaying for.</param>
public static void SetRelayingPermittedIPAddresses(IPAddress[] addresses)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"DELETE FROM man_cfg_relayingPermittedIp";
foreach (IPAddress addr in addresses)
{
cmd.CommandText += System.Environment.NewLine + "INSERT INTO man_cfg_relayingPermittedIp(cfg_relayingPermittedIp_ip) VALUES ( '" + addr.ToString() + "' )";
}
conn.Open();
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Sets the status of the specified send to the specified status.
/// </summary>
/// <param name="sendID">ID of the send to set the staus of.</param>
/// <param name="status">The status to set the send to.</param>
public static void SetSendStatus(string sendID, SendStatus status)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
UPDATE man_mta_send
SET mta_sendStatus_id = @sendStatus
WHERE mta_send_id = @sendID";
cmd.Parameters.AddWithValue("@sendID", sendID);
cmd.Parameters.AddWithValue("@sendStatus", (int)status);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public static async Task <bool> HasBeenHandledAsync(Guid messageID)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"IF EXISTS(SELECT 1
FROM man_mta_transaction WITH(readuncommitted)
WHERE man_mta_transaction.mta_msg_id = @msgID
AND man_mta_transaction.mta_transactionStatus_id IN (2,3,4,6))
SELECT 1
ELSE
SELECT 0" ;
cmd.Parameters.AddWithValue("@msgID", messageID);
await conn.OpenAsync();
return(Convert.ToBoolean(await cmd.ExecuteScalarAsync()));
}
}
/// <summary>
/// Deletes the specified Virtual MTA group.
/// </summary>
/// <param name="id">ID of the virtual mta group to delete.</param>
public static void Delete(int id)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
DELETE
FROM man_ip_group
WHERE ip_group_id = @id
DELETE
FROM man_ip_groupMembership
WHERE ip_group_id = @id";
cmd.Parameters.AddWithValue("@id", id);
conn.Open();
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Saves a local domain to the database.
/// </summary>
/// <param name="domain">Domain to add. Does nothing if domain already exists.</param>
public static void Save(LocalDomain localDomain)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
IF EXISTS (SELECT 1 FROM man_cfg_localDomain WHERE cfg_localDomain_id = @id)
UPDATE man_cfg_localDomain
SET cfg_localDomain_domain = @domain,
cfg_localDomain_name = @name,
cfg_localDomain_description = @description
WHERE cfg_localDomain_domain = @id
ELSE
BEGIN
IF(@id > 0)
BEGIN
SET IDENTITY_INSERT man_cfg_localDomain ON
INSERT INTO man_cfg_localDomain (cfg_localDomain_id, cfg_localDomain_domain, cfg_localDomain_name, cfg_localDomain_description)
VALUES(@id, @domain, @name, @description)
SET IDENTITY_INSERT man_cfg_localDomain OFF
END
ELSE
INSERT INTO man_cfg_localDomain (cfg_localDomain_domain, cfg_localDomain_name, cfg_localDomain_description)
VALUES(@domain, @name, @description)
END" ;
cmd.Parameters.AddWithValue("@id", localDomain.ID);
cmd.Parameters.AddWithValue("@domain", localDomain.Hostname);
cmd.Parameters.AddWithValue("@name", localDomain.Name);
if (localDomain.Description == null)
{
cmd.Parameters.AddWithValue("@description", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@description", localDomain.Description);
}
conn.Open();
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Gets an array of the IP addresses that are permitted to use this server for relaying from the database.
/// </summary>
/// <returns></returns>
public static string[] GetRelayingPermittedIPAddresses()
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT cfg_relayingPermittedIp_ip
FROM man_cfg_relayingPermittedIp";
conn.Open();
ArrayList results = new ArrayList();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
results.Add(reader.GetString("cfg_relayingPermittedIp_ip"));
}
return((string[])results.ToArray(typeof(string)));
}
}
public static async Task <string> GetMailFrom(Guid messageId)
{
using (var conn = MantaDB.GetSqlConnection())
{
var cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT TOP 1 mta_msg_mailFrom
FROM man_mta_msg
WHERE mta_msg_id = @msgId";
cmd.Parameters.AddWithValue("@msgId", messageId);
await conn.OpenAsync().ConfigureAwait(false);
var result = await cmd.ExecuteScalarAsync().ConfigureAwait(false);
if (result == null)
{
return(string.Empty);
}
return(result.ToString());
}
}
/// <summary>
/// Checks an address to see if it appears in the list of feedback loop addresses.
/// </summary>
/// <param name="address">Address to check.</param>
/// <returns>TRUE if exists, FALSE if not.</returns>
public static bool IsFeedbackLoopEmailAddress(string address)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT 1
FROM man_mta_fblAddress
WHERE mta_fblAddress_address = @address";
cmd.Parameters.AddWithValue("@address", address);
conn.Open();
object result = cmd.ExecuteScalar();
if (result == null)
{
return(false);
}
return(true);
}
}
