public static void NewLiveBroadcast()
{
try
{
SqlCommand command;
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);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
DataSet 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"];
Member targetMember = Member.GetMemberByMemberID(MemberIDTo, connection);
Member sendingMember = Member.GetMemberByMemberID(MemberIDFrom, connection);
string TargetEmailAddress = targetMember.Email;
string subject = Templates.NewLiveBroadcastSubject;
string body = Templates.NewLiveBroadcastBody;
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 { }
}
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 { }
}
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 NewComment()
{
SqlCommand command = null;
DataSet ds = null;
SqlDataAdapter dataAdapter = null;
string TargetEmailAddress = null;
string OwnerEmailAddress = null;
int OwnerMemberID = -1;
string subject = null;
string body = 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);
DataRow insertedRow = ds.Tables[0].Rows[0];
int ObjectID = (int)insertedRow["ObjectID"];
int MemberIDFrom = (int)insertedRow["MemberIDFrom"];
ContentType commentType = (ContentType)((int)insertedRow["CommentType"]);
int ThreadNo = (int)insertedRow["ThreadNo"];
string Path = (string)insertedRow["Path"];
// The object on which the comment was made along with the owner information
// Can be a Video, Photo, Nspot, Member etc...
DataSet objectDS = Utility.GetObjectByObjectIDWithJoin(ObjectID, commentType, connection);
OwnerMemberID = (int)objectDS.Tables[0].Rows[0]["MemberID"];
if (commentType == ContentType.Wall)
{
OwnerEmailAddress = (string)objectDS.Tables[0].Rows[0]["Email"];
}
else
{
OwnerEmailAddress = (string)objectDS.Tables[0].Rows[0]["MemberEmail"];
}
// The target members to which the notification email should be sent
// The owner of the object is not included in this list.
DataSet targetMembers = GetCommentTargetMembers(ObjectID, commentType, ThreadNo, connection);
Member sendingMember = Member.GetMemberByMemberID(MemberIDFrom, connection);
string unMergedSubject = GetSubject(Path == "/", commentType);
string unMergedBody = GetBody(Path == "/", commentType);
unMergedBody = Utility.PopulateLink(unMergedBody, commentType, objectDS.Tables[0].Rows[0]);
// Send notification email to all members participating in the thread
foreach (DataRow row in targetMembers.Tables[0].Rows)
{
// if the posting member is also found in target member.skip
if ((int)row["MemberID"] == MemberIDFrom)
{
continue;
}
TargetEmailAddress = (string)row["Email"];
subject = unMergedSubject;
body = unMergedBody;
if (OwnerEmailAddress == TargetEmailAddress)
{
continue;
}
if (!Notify(row, ContentType.ThreadReply))
{
continue;
}
subject = MergeHelper.GenericMerge(subject, insertedRow.Table.Columns, insertedRow);
body = MergeHelper.GenericMerge(body, insertedRow.Table.Columns, insertedRow);
subject = MergeHelper.GenericMerge(subject, row.Table.Columns, row);
body = MergeHelper.GenericMerge(body, row.Table.Columns, row);
subject = MergeHelper.MergeOtherMemberInfo(subject, sendingMember);
body = MergeHelper.MergeOtherMemberInfo(body, sendingMember);
//body = MergeHelper.MergeBanner(body, "New " + commentType.ToString() + " comment",connection);
MailHelper.SendEmail(TargetEmailAddress, subject, body);
}
if (MemberIDFrom != OwnerMemberID
&&
Notify(
GetMemberSettingsByMemberID(OwnerMemberID, connection).Tables[0].Rows[0], commentType)
)
{
subject = GetSubject(true, commentType);
body = GetBody(true, commentType);
subject = MergeHelper.GenericMerge(subject, insertedRow.Table.Columns, insertedRow);
body = MergeHelper.GenericMerge(body, insertedRow.Table.Columns, insertedRow);
subject = MergeHelper.GenericMerge(subject, objectDS.Tables[0].Columns, objectDS.Tables[0].Rows[0]);
body = MergeHelper.GenericMerge(body, objectDS.Tables[0].Columns, objectDS.Tables[0].Rows[0]);
subject = MergeHelper.MergeOtherMemberInfo(subject, sendingMember);
body = MergeHelper.MergeOtherMemberInfo(body, sendingMember);
//body = MergeHelper.MergeBanner(body, "New " + commentType.ToString() + " comment", connection);
MailHelper.SendEmail(OwnerEmailAddress, 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 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 { }
}
}