public static List<MemberSettings> PopulateObject(IDataReader dr)
{
List<MemberSettings> arr = new List<MemberSettings>();
MemberSettings obj;
while (dr.Read())
{
obj = new MemberSettings();
obj._memberSettingsID = (int)dr["MemberSettingsID"];
obj._memberID = (int)dr["MemberID"];
obj._notifyOnNewMessage = (bool)dr["NotifyOnNewMessage"];
obj._notifyOnAAFComment = (bool)dr["NotifyOnAAFComment"];
obj._notifyOnFriendRequest = (bool)dr["NotifyOnFriendRequest"];
obj._notifyOnSubscriberEvent = (bool)dr["NotifyOnSubscriberEvent"];
obj._notifyOnNewsLetter = (bool)dr["NotifyOnNewsLetter"];
obj._notifyNewProfileComment = (bool)dr["NotifyNewProfileComment"];
obj._notifyNewPhotoComment = (bool)dr["NotifyNewPhotoComment"];
obj._notifyNewVideoComment = (bool)dr["NotifyNewVideoComment"];
obj._notifyOnNewBlog = (bool)dr["NotifyOnNewBlog"];
obj._notifyOnThreadReply = (bool)dr["NotifyOnThreadReply"];
obj._notifyOnNewVideo = (bool)dr["NotifyOnNewVideo"];
arr.Add(obj);
}
dr.Close();
return arr;
}
/// <summary>
/// Calls the database and gets all the MemberSettings objects for this Member
/// </summary>
public static MemberSettings GetMemberSettingsByMemberID(int MemberID, SqlConnection conn)
{
SqlCommand command = new SqlCommand("AG_GetMemberSettingsByMemberID", conn);
SqlParameter param = new SqlParameter("@MemberID", MemberID);
param.DbType = DbType.Int32;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(param);
List <MemberSettings> arr = null;
// Populate the datareader
using (IDataReader dr = command.ExecuteReader())
{
// Call the PopulateObject method passing the datareader to return the object array
arr = MemberSettings.PopulateObject(dr);
dr.Close();
}
if (arr.Count > 0)
{
return(arr[0]);
}
else
{
return(null);
}
}
private static void TriggerHandler(SqlConnection connection, ContentType contentType, DataSet ds)
{
//Populate data from the inserted row
DataRow insertedRow = ds.Tables[0].Rows[0];
DataColumnCollection insertedColumns = ds.Tables[0].Columns;
int OwnerMemberID = (int)insertedRow["MemberID"];
string WebVideoID = (string)insertedRow["WebVideoID"];
DataSet VideoDS = GetVideoByWebVideoIDWithJoin(WebVideoID, connection);
DataRow videoDSRow = VideoDS.Tables[0].Rows[0];
DataColumnCollection videoDSColumns = VideoDS.Tables[0].Columns;
Member ownerMember = Member.GetMemberByMemberID(OwnerMemberID, connection);
MemberSettings ms = Member.GetMemberSettingsByMemberID(OwnerMemberID, connection);
// In case we don't find any settings
if (ms == null)
{
return;
}
string TargetEmailAddress = ownerMember.Email;
string subject = Templates.NewStreamSelfSubject;
string body = Templates.NewStreamSelfBody;
subject = MergeHelper.GenericMerge(subject, insertedColumns, insertedRow);
body = MergeHelper.GenericMerge(body, insertedColumns, insertedRow);
subject = MergeHelper.GenericMerge(subject, videoDSColumns, videoDSRow);
body = MergeHelper.GenericMerge(body, videoDSColumns, videoDSRow);
subject = MergeHelper.MergeOtherMemberInfo(subject, ownerMember);
body = MergeHelper.MergeOtherMemberInfo(body, ownerMember);
try
{
MailHelper.SendEmail(TargetEmailAddress, subject, body);
}
catch { }
}
private static void TriggerHandler(SqlConnection connection, ContentType contentType, DataSet ds)
{
//Populate data from the inserted row
DataRow insertedRow = ds.Tables[0].Rows[0];
DataColumnCollection insertedColumns = ds.Tables[0].Columns;
int OwnerMemberID = (int)insertedRow["MemberID"];
string WebPhotoID = (string)insertedRow["WebPhotoID"];
DataSet PhotoDS = GetPhotoByWebPhotoIDWithJoin(WebPhotoID, connection);
DataRow PhotoDSRow = PhotoDS.Tables[0].Rows[0];
DataColumnCollection PhotoDSColumns = PhotoDS.Tables[0].Columns;
Member ownerMember = Member.GetMemberByMemberID(OwnerMemberID, connection);
MemberSettings ms = Member.GetMemberSettingsByMemberID(OwnerMemberID, connection);
// In case we don't find any settings
if (ms == null)
{
return;
}
string TargetEmailAddress = ownerMember.Email;
string subject = Templates.NewPhotoSelfSubject;
string body = Templates.NewPhotoSelfBody;
subject = MergeHelper.GenericMerge(subject, insertedColumns, insertedRow);
body = MergeHelper.GenericMerge(body, insertedColumns, insertedRow);
subject = MergeHelper.GenericMerge(subject, PhotoDSColumns, PhotoDSRow);
body = MergeHelper.GenericMerge(body, PhotoDSColumns, PhotoDSRow);
subject = MergeHelper.MergeMemberInfo(subject, ownerMember);
body = MergeHelper.MergeMemberInfo(body, ownerMember);
try
{
MailHelper.SendEmail(TargetEmailAddress, subject, body, @"\\www\Live\user\" + ownerMember.NickName + @"\pmed\" + PhotoDSRow["PhotoResourceFileFileName"]);
}
catch { }
}
public static void NewAAFComment()
{
SqlCommand command;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlDataAdapter dataAdapter = null;
DataSet ds = new DataSet();
try
{
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
dataAdapter = new SqlDataAdapter(command);
ds = new DataSet();
dataAdapter.Fill(ds);
//Populate data from the inserted row
DataRow insertedRow = ds.Tables[0].Rows[0];
DataColumnCollection insertedColumns = ds.Tables[0].Columns;
// In AskAFriend comment the MemberID is actually the from member id
int MemberIDFrom = (int)insertedRow["MemberID"];
// Get the AskAFriendID against the inserted Row
int AskAFriendID = (int)insertedRow["AskAFriendID"];
// Get AskAFriend Information against the inserted AskAFriend Comment
ds = AskAFriend.GetAskAFriendInformation(AskAFriendID, connection);
// If there is no AskAFriend row found
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return;
}
DataRow AAFRow = ds.Tables[0].Rows[0];
DataColumnCollection AAFColumns = ds.Tables[0].Columns;
int MemberID = (int)AAFRow["MemberID"];
MemberSettings ms = Member.GetMemberSettingsByMemberID(MemberID, connection);
// In case we don't find any settings
if (ms == null)
{
return;
}
// If the user doesn't want to be notified return
if (!ms.NotifyOnAAFComment)
{
return;
}
Member targetMember = Member.GetMemberByMemberID(MemberID, connection);
Member sendingMember = Member.GetMemberByMemberID(MemberIDFrom, connection);
string TargetEmailAddress = targetMember.Email;
string subject = Templates.NewAAFCommentSubject;
string body = Templates.NewAAFCommentBody;
subject = MergeHelper.GenericMerge(subject, insertedColumns, insertedRow);
body = MergeHelper.GenericMerge(body, insertedColumns, insertedRow);
subject = MergeHelper.GenericMerge(subject, AAFColumns, AAFRow);
body = MergeHelper.GenericMerge(body, AAFColumns, AAFRow);
subject = MergeHelper.MergeMemberInfo(subject, targetMember);
body = MergeHelper.MergeMemberInfo(body, targetMember);
subject = MergeHelper.MergeOtherMemberInfo(subject, sendingMember);
body = MergeHelper.MergeOtherMemberInfo(body, sendingMember);
MailHelper.SendEmail(TargetEmailAddress, subject, body);
}
break;
}
}
catch { }
}
private static void TriggerHandler(SqlConnection connection, ContentType contentType, DataSet ds)
{
DataSet friendMembersDS = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
//Populate data from the inserted row
DataRow insertedRow = ds.Tables[0].Rows[0];
DataColumnCollection insertedColumns = ds.Tables[0].Columns;
int OwnerMemberID = (int)insertedRow["MemberID"];
Member ownerMember = Member.GetMemberByMemberID(OwnerMemberID, connection);
friendMembersDS = GetFriendMembers(OwnerMemberID, connection);
DataTable friendMembers = friendMembersDS.Tables[0];
foreach (DataRow row in friendMembers.Rows)
{
MemberSettings ms = Member.GetMemberSettingsByMemberID((int)row["MemberMemberID"], connection);
// In case we don't find any settings
if (ms == null)
{
continue;
}
// If the user doesn't want to be notified return
if (contentType == ContentType.Video && !ms.NotifyOnNewVideo)
{
continue;
}
if (contentType == ContentType.Blog && !ms.NotifyOnNewBlog)
{
continue;
}
string TargetEmailAddress = (string)row["MemberEmail"];
string subject = Templates.NewFriendContentSubject;
string body = Templates.NewFriendContentBody;
subject = MergeHelper.GenericMerge(subject, insertedColumns, insertedRow);
body = MergeHelper.GenericMerge(body, insertedColumns, insertedRow);
subject = MergeHelper.GenericMerge(subject, row.Table.Columns, row);
body = MergeHelper.GenericMerge(body, row.Table.Columns, row);
subject = MergeHelper.MergeOtherMemberInfo(subject, ownerMember);
body = MergeHelper.MergeOtherMemberInfo(body, ownerMember);
subject = PopulateContentType(subject, contentType);
body = PopulateContentType(body, contentType);
body = PopulateLink(body, contentType, insertedRow, ownerMember);
try
{
MailHelper.SendEmail(TargetEmailAddress, subject, body);
}
catch {}
}
}
catch { }
finally
{
try
{ if (ds != null)
{
ds.Dispose();
ds = null;
}
if (friendMembersDS != null)
{
friendMembersDS.Dispose();
friendMembersDS = null;
}
}
catch { }
}
}
public static void NewMessage()
{
SqlCommand command = null;
DataSet ds = null;
SqlDataAdapter dataAdapter = null;
try
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
dataAdapter = new SqlDataAdapter(command);
ds = new DataSet();
dataAdapter.Fill(ds);
//Populate data from the inserted row
DataRow insertedRow = ds.Tables[0].Rows[0];
DataColumnCollection insertedColumns = ds.Tables[0].Columns;
int MemberIDTo = (int)insertedRow["MemberIDTo"];
int MemberIDFrom = (int)insertedRow["MemberIDFrom"];
string ExternalEmailTo = (string)insertedRow["ExternalEmailTo"];
if (MemberIDTo != -1)
{
MemberSettings ms = Member.GetMemberSettingsByMemberID(MemberIDTo, connection);
// In case we don't find any settings
if (ms == null)
{
return;
}
// If the user doesn't want to be notified return
if (!ms.NotifyOnNewMessage)
{
return;
}
}
Member targetMember = null;
if (MemberIDTo != -1)
{
targetMember = Member.GetMemberByMemberID(MemberIDTo, connection);
}
else
{
targetMember = new Member();
targetMember.Email = ExternalEmailTo;
}
Member sendingMember = Member.GetMemberByMemberID(MemberIDFrom, connection);
string TargetEmailAddress = targetMember.Email;
string subject = string.Empty;
string body = string.Empty;
if (MemberIDTo == -1)
{
subject = Templates.NewExternalMessageSubject;
body = Templates.NewExternalMessageBody;
}
else
{
subject = Templates.NewMessageSubject;
body = Templates.NewMessageBody;
}
subject = MergeHelper.GenericMerge(subject, insertedColumns, insertedRow);
body = MergeHelper.GenericMerge(body, insertedColumns, insertedRow);
subject = MergeHelper.MergeMemberInfo(subject, targetMember);
body = MergeHelper.MergeMemberInfo(body, targetMember);
subject = MergeHelper.MergeOtherMemberInfo(subject, sendingMember);
body = MergeHelper.MergeOtherMemberInfo(body, sendingMember);
MailHelper.SendEmail(TargetEmailAddress, subject, body);
}
break;
}
}
catch { }
finally
{
try
{
if (command != null)
{
command.Dispose();
}
if (ds != null)
{
ds.Dispose();
}
if (dataAdapter != null)
{
dataAdapter.Dispose();
}
}
catch { }
}
}
public static void NewMemberComment()
{
SqlCommand command = null;
DataSet ds = null;
SqlDataAdapter dataAdapter = null;
try
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
dataAdapter = new SqlDataAdapter(command);
ds = new DataSet();
dataAdapter.Fill(ds);
DataTable table = ds.Tables[0];
DataRow row = table.Rows[0];
DataColumnCollection columns = table.Columns;
int MemberID = (int)row["MemberID"];
int MemberIDFrom = (int)row["MemberIDFrom"];
MemberSettings ms = null;
ms = Member.GetMemberSettingsByMemberID(MemberID, connection);
// In case we don't find any settings
if (ms == null)
{
return;
}
if (!ms.NotifyNewProfileComment)
{
return;
}
Member targetMember = Member.GetMemberByMemberID(MemberID, connection);
Member sendingMember = Member.GetMemberByMemberID(MemberIDFrom, connection);
string TargetEmailAddress = targetMember.Email;
string subject = Templates.NewMemberCommentSubject;
string body = Templates.NewMemberCommentBody;
subject = MergeHelper.GenericMerge(subject, columns, row);
body = MergeHelper.GenericMerge(body, columns, row);
subject = MergeHelper.MergeMemberInfo(subject, targetMember);
body = MergeHelper.MergeMemberInfo(body, targetMember);
subject = MergeHelper.MergeOtherMemberInfo(subject, sendingMember);
body = MergeHelper.MergeOtherMemberInfo(body, sendingMember);
MailHelper.SendEmail(TargetEmailAddress, subject, body);
}
break;
}
}
catch { }
finally
{
try
{
if (command != null)
{
command.Dispose();
}
if (ds != null)
{
ds.Dispose();
}
if (dataAdapter != null)
{
dataAdapter.Dispose();
}
}
catch { }
}
}
public static void AcceptedFriendRequest()
{
SqlCommand command = null;
SqlCommand command2 = null;
SqlDataAdapter dataAdapter = null;
SqlDataAdapter dataAdapter2 = null;
DataSet ds = null;
DataSet dsDel = null;
try
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
switch (triggContext.TriggerAction)
{
case TriggerAction.Update:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
dataAdapter = new SqlDataAdapter(command);
ds = new DataSet();
dataAdapter.Fill(ds);
//Populate data from the inserted row
DataRow updatedRow = ds.Tables[0].Rows[0];
DataColumnCollection updatedColumns = ds.Tables[0].Columns;
command2 = new SqlCommand(@"SELECT * FROM DELETED;",
connection);
dataAdapter2 = new SqlDataAdapter(command2);
dsDel = new DataSet();
dataAdapter2.Fill(dsDel);
//Populate data from the inserted row
DataRow deletedRow = dsDel.Tables[0].Rows[0];
DataColumnCollection deletedColumns = dsDel.Tables[0].Columns;
// If the old response value was zero and the new response value is ONE
// only then its a request accept
// Other wise return
if (!((int)deletedRow["Status"] == 0 && (int)updatedRow["Status"] == 1))
{
return;
}
// If the status has been updated to ONE but
// response is still ZERO
// means the friend request was rejected
// Don't need to send the email on friend request reject
if (((int)updatedRow["Response"] == 0 && (int)updatedRow["Status"] == 1))
{
return;
}
int MemberIDTo = (int)updatedRow["FriendMemberID"];
int MemberIDFrom = (int)updatedRow["MemberID"];
MemberSettings ms = Member.GetMemberSettingsByMemberID(MemberIDTo, connection);
// In case we don't find any settings
if (ms == null)
{
return;
}
// If the user doesn't want to be notified return
if (!ms.NotifyOnFriendRequest)
{
return;
}
Member targetMember = Member.GetMemberByMemberID(MemberIDTo, connection);
Member sendingMember = Member.GetMemberByMemberID(MemberIDFrom, connection);
string TargetEmailAddress = targetMember.Email;
string subject = Templates.AcceptedFriendRequestSubject;
string body = Templates.AcceptedFriendRequestBody;
subject = MergeHelper.GenericMerge(subject, updatedColumns, updatedRow);
body = MergeHelper.GenericMerge(body, updatedColumns, updatedRow);
subject = MergeHelper.MergeMemberInfo(subject, targetMember);
body = MergeHelper.MergeMemberInfo(body, targetMember);
subject = MergeHelper.MergeOtherMemberInfo(subject, sendingMember);
body = MergeHelper.MergeOtherMemberInfo(body, sendingMember);
MailHelper.SendEmail(TargetEmailAddress, subject, body);
}
break;
}
}
catch { }
finally
{
try
{
if (command != null)
{
command.Dispose();
}
if (command2 != null)
{
command2.Dispose();
}
if (ds != null)
{
ds.Dispose();
}
if (dsDel != null)
{
dsDel.Dispose();
}
if (dataAdapter != null)
{
dataAdapter.Dispose();
}
if (dataAdapter2 != null)
{
dataAdapter2.Dispose();
}
}
catch { }
}
}
