/// <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> /// 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 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 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> /// 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> /// Retrieves all BounceRules from the database. /// </summary> /// <returns>A BounceRulesCollection of all the Rules.</returns> public static BounceRulesCollection GetBounceRules() { using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString)) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = @" SELECT * FROM man_evn_bounceRule ORDER BY evn_bounceRule_executionOrder ASC"; return(new BounceRulesCollection(DataRetrieval.GetCollectionFromDatabase <BounceRule>(cmd, CreateAndFillBounceRuleFromRecord))); } }
/// <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 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> /// 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> /// 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)); } }