public DataTable notifications(int userId) { DataTable dt = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda = new SqlDataAdapter("sp_notifications", sqlConn2); sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId; sda.Fill(ds); dt = ds.Tables[0]; DataTable dt2 = new DataTable(); DataRow dr2 = null; //define the columns dt2.Columns.Add(new DataColumn("NotificationId", typeof(string))); dt2.Columns.Add(new DataColumn("NotificationType", typeof(string))); dt2.Columns.Add(new DataColumn("Text", typeof(string))); dt2.Columns.Add(new DataColumn("Unread", typeof(string))); dt2.Columns.Add(new DataColumn("PassedTime", typeof(string))); dt2.Columns.Add(new DataColumn("Link", typeof(string))); Classes.Date d = new Classes.Date(); for (int i = 0; i < dt.Rows.Count; i++) { //create new row dr2 = dt2.NewRow(); //add values to each rows dr2["NotificationId"] = dt.Rows[i]["NotificationId"].ToString(); dr2["NotificationType"] = dt.Rows[i]["NotificationType"].ToString(); dr2["Unread"] = dt.Rows[i]["Unread"].ToString(); dr2["PassedTime"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString()); //link dr2["Link"] = notificationLink(Convert.ToInt32(dt.Rows[i]["NotificationType"].ToString()), Convert.ToInt64(dt.Rows[i]["ItemId"].ToString())); //text dr2["Text"] = notificationText(Convert.ToInt32(dt.Rows[i]["NotificationType"].ToString()), Convert.ToInt64(dt.Rows[i]["ItemId"].ToString())); //add the row to DataTable dt2.Rows.Add(dr2); } return(dt2); }
public DataTable notifications(int userId) { DataTable dt = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda = new SqlDataAdapter("sp_notifications", sqlConn2); sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId; sda.Fill(ds); dt = ds.Tables[0]; DataTable dt2 = new DataTable(); DataRow dr2 = null; //define the columns dt2.Columns.Add(new DataColumn("NotificationId", typeof(string))); dt2.Columns.Add(new DataColumn("NotificationType", typeof(string))); dt2.Columns.Add(new DataColumn("Text", typeof(string))); dt2.Columns.Add(new DataColumn("Unread", typeof(string))); dt2.Columns.Add(new DataColumn("PassedTime", typeof(string))); dt2.Columns.Add(new DataColumn("Link", typeof(string))); Classes.Date d = new Classes.Date(); for (int i = 0; i < dt.Rows.Count; i++) { //create new row dr2 = dt2.NewRow(); //add values to each rows dr2["NotificationId"] = dt.Rows[i]["NotificationId"].ToString(); dr2["NotificationType"] = dt.Rows[i]["NotificationType"].ToString(); dr2["Unread"] = dt.Rows[i]["Unread"].ToString(); dr2["PassedTime"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString()); //link dr2["Link"] = notificationLink(Convert.ToInt32(dt.Rows[i]["NotificationType"].ToString()), Convert.ToInt64(dt.Rows[i]["ItemId"].ToString())); //text dr2["Text"] = notificationText(Convert.ToInt32(dt.Rows[i]["NotificationType"].ToString()), Convert.ToInt64(dt.Rows[i]["ItemId"].ToString())); //add the row to DataTable dt2.Rows.Add(dr2); } return dt2; }
public DataTable eventBoardMessages(Int64 eventId, int ownerId) { DataTable dt = new DataTable(); DataTable dtCountries = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda = new SqlDataAdapter("sp_eventBoardMessages", sqlConn); sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.SelectCommand.Parameters.Add("@EventId", SqlDbType.BigInt).Value = eventId; //try //{ sda.Fill(ds); dt = ds.Tables[0]; //} //catch (Exception ex) //{ //} //finally //{ sqlConn.Close(); sda.Dispose(); sqlConn.Dispose(); //} if (dt.Rows.Count == 0)// no messages { return(dt); } else { DataTable dt2 = new DataTable(); DataRow dr2 = null; //define the columns dt2.Columns.Add(new DataColumn("MessageId", typeof(string))); dt2.Columns.Add(new DataColumn("IsOwner", typeof(string))); dt2.Columns.Add(new DataColumn("SenderName", typeof(string))); dt2.Columns.Add(new DataColumn("SenderId", typeof(string))); dt2.Columns.Add(new DataColumn("Message", typeof(string))); dt2.Columns.Add(new DataColumn("PassedDate", typeof(string))); dt2.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string))); string profilePicUrl; for (int i = 0; i < dt.Rows.Count; i++) { //create new row dr2 = dt2.NewRow(); //add values to each rows dr2["MessageId"] = dt.Rows[i]["MessageId"].ToString(); dr2["SenderName"] = dt.Rows[i]["FirstName"].ToString() + " " + dt.Rows[i]["LastName"].ToString(); dr2["SenderId"] = dt.Rows[i]["UserId"].ToString(); dr2["Message"] = dt.Rows[i]["Message"].ToString(); Classes.Date d = new Classes.Date(); dr2["PassedDate"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString()); if (ownerId == Convert.ToInt32(dt.Rows[i]["UserId"].ToString())) { dr2["IsOwner"] = "true"; } else { dr2["IsOwner"] = "false"; } // Show profile's photo if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString())) { profilePicUrl = "~/Files/ProfilesPhotos/" + dt.Rows[i]["UserId"].ToString() + "-100.jpg"; } else { profilePicUrl = "~/Images/nophoto.png"; } dr2["ProfilePicUrl"] = profilePicUrl; //add the row to DataTable dt2.Rows.Add(dr2); } return(dt2); } }
public DataTable eventRequestsList(int userId, Int64 eventId) { DataTable dt = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda = new SqlDataAdapter("sp_requestsInfo", sqlConn); sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId; sda.SelectCommand.Parameters.Add("@EventId", SqlDbType.BigInt).Value = eventId; try { sda.Fill(ds); dt = ds.Tables[0]; } catch (Exception ex) { } finally { sqlConn.Close(); sda.Dispose(); sqlConn.Dispose(); } if (dt.Rows.Count == 0) { return(dt); } else { DataTable dt2 = new DataTable(); DataRow dr = null; string profilePicUrl = ""; //define the columns dt2.Columns.Add(new DataColumn("RequestId", typeof(string))); dt2.Columns.Add(new DataColumn("EventId", typeof(string))); dt2.Columns.Add(new DataColumn("SenderId", typeof(string))); dt2.Columns.Add(new DataColumn("FullName", typeof(string))); dt2.Columns.Add(new DataColumn("Message", typeof(string))); dt2.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string))); dt2.Columns.Add(new DataColumn("PassedTime", typeof(string))); for (int i = 0; i < dt.Rows.Count; i++) { //create new row dr = dt2.NewRow(); //add values to each rows dr["RequestId"] = dt.Rows[i]["RequestId"].ToString(); dr["EventId"] = dt.Rows[i]["EventId"].ToString(); dr["SenderId"] = dt.Rows[i]["UserId"].ToString(); dr["FullName"] = dt.Rows[i]["FirstName"].ToString() + " " + dt.Rows[i]["LastName"].ToString(); dr["Message"] = dt.Rows[i]["Message"].ToString(); // time Classes.Date d = new Classes.Date(); dr["PassedTime"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString()); // Show profile's photo if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString())) { profilePicUrl = "~/Files/ProfilesPhotos/" + dt.Rows[i]["UserId"].ToString() + "-100.jpg"; } else { profilePicUrl = "~/Images/nophoto.png"; } dr["ProfilePicUrl"] = profilePicUrl; //add the row to DataTable dt2.Rows.Add(dr); } return(dt2); } }
public DataTable eventRequestsList(int userId, Int64 eventId) { DataTable dt = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda = new SqlDataAdapter("sp_requestsInfo", sqlConn); sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId; sda.SelectCommand.Parameters.Add("@EventId", SqlDbType.BigInt).Value = eventId; try { sda.Fill(ds); dt = ds.Tables[0]; } catch (Exception ex) { } finally { sqlConn.Close(); sda.Dispose(); sqlConn.Dispose(); } if (dt.Rows.Count == 0) { return dt; } else { DataTable dt2 = new DataTable(); DataRow dr = null; string profilePicUrl = ""; //define the columns dt2.Columns.Add(new DataColumn("RequestId", typeof(string))); dt2.Columns.Add(new DataColumn("EventId", typeof(string))); dt2.Columns.Add(new DataColumn("SenderId", typeof(string))); dt2.Columns.Add(new DataColumn("FullName", typeof(string))); dt2.Columns.Add(new DataColumn("Message", typeof(string))); dt2.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string))); dt2.Columns.Add(new DataColumn("PassedTime", typeof(string))); for (int i = 0; i < dt.Rows.Count; i++) { //create new row dr = dt2.NewRow(); //add values to each rows dr["RequestId"] = dt.Rows[i]["RequestId"].ToString(); dr["EventId"] = dt.Rows[i]["EventId"].ToString(); dr["SenderId"] = dt.Rows[i]["UserId"].ToString(); dr["FullName"] = dt.Rows[i]["FirstName"].ToString() + " " + dt.Rows[i]["LastName"].ToString(); dr["Message"] = dt.Rows[i]["Message"].ToString(); // time Classes.Date d = new Classes.Date(); dr["PassedTime"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString()); // Show profile's photo if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString())) { profilePicUrl = "~/Files/ProfilesPhotos/" + dt.Rows[i]["UserId"].ToString() + "-100.jpg"; } else { profilePicUrl = "~/Images/nophoto.png"; } dr["ProfilePicUrl"] = profilePicUrl; //add the row to DataTable dt2.Rows.Add(dr); } return dt2; } }
public MessageObjectAll getMessages(int userId, int otherId, Int64 messageId) { MessageObjectAll allObjects = new MessageObjectAll(); if (messageId == 0) { messageId = 999999999; } DataTable dt = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda1 = new SqlDataAdapter("sp_messages", sqlConn); sda1.SelectCommand.CommandType = CommandType.StoredProcedure; sda1.SelectCommand.Parameters.Add("@OwnerId", SqlDbType.Int).Value = userId; sda1.SelectCommand.Parameters.Add("@OtherId", SqlDbType.Int).Value = otherId; sda1.SelectCommand.Parameters.Add("@MessageId", SqlDbType.Int).Value = messageId; //try //{ sda1.Fill(ds); dt = ds.Tables[0]; //} //catch (Exception ex) //{ //} //finally //{ sqlConn.Close(); sda1.Dispose(); sqlConn.Dispose(); //} if (dt.Rows.Count == 0) { return(allObjects); } else { MessageObject object1 = new MessageObject(); MessageObject object2 = new MessageObject(); MessageObject object3 = new MessageObject(); MessageObject object4 = new MessageObject(); MessageObject object5 = new MessageObject(); MessageObject object6 = new MessageObject(); MessageObject object7 = new MessageObject(); MessageObject object8 = new MessageObject(); MessageObject object9 = new MessageObject(); MessageObject object10 = new MessageObject(); // profile pic url int userIdString = 0; Classes.Date d = new Classes.Date(); for (int i = 0; i < dt.Rows.Count; i++) { if (Convert.ToBoolean(dt.Rows[i]["Sender"].ToString())) { userIdString = Convert.ToInt32(dt.Rows[i]["OwnerId"].ToString()); } else { userIdString = Convert.ToInt32(dt.Rows[i]["OtherId"].ToString()); } MessageObject myMessageObject = new MessageObject( Convert.ToInt32(dt.Rows[i]["MessageId"].ToString()), Convert.ToBoolean(dt.Rows[i]["Sender"].ToString()), dt.Rows[i]["Message"].ToString(), d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString()), Convert.ToBoolean(dt.Rows[i]["Unread"].ToString()), userIdString); switch (i) { case 0: { object10 = myMessageObject; break; } case 1: { object9 = myMessageObject; break; } case 2: { object8 = myMessageObject; break; } case 3: { object7 = myMessageObject; break; } case 4: { object6 = myMessageObject; break; } case 5: { object5 = myMessageObject; break; } case 6: { object4 = myMessageObject; break; } case 7: { object3 = myMessageObject; break; } case 8: { object2 = myMessageObject; break; } case 9: { object1 = myMessageObject; break; } } allObjects = new MessageObjectAll( object1, object2, object3, object4, object5, object6, object7, object8, object9, object10); } return(allObjects); } }
public DataTable messageLists(int userId) { DataTable dt = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda = new SqlDataAdapter("sp_messageLists", sqlConn); sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId; //try //{ sda.Fill(ds); dt = ds.Tables[0]; //} //catch (Exception ex) //{ //} //finally //{ sqlConn.Close(); sda.Dispose(); sqlConn.Dispose(); //} DataTable dt2 = new DataTable(); DataRow dr2 = null; //define the columns dt2.Columns.Add(new DataColumn("MessageListId", typeof(string))); dt2.Columns.Add(new DataColumn("OtherId", typeof(string))); dt2.Columns.Add(new DataColumn("FirstName", typeof(string))); dt2.Columns.Add(new DataColumn("LastName", typeof(string))); dt2.Columns.Add(new DataColumn("PassedDate", typeof(string))); dt2.Columns.Add(new DataColumn("Brief", typeof(string))); dt2.Columns.Add(new DataColumn("Unread", typeof(string))); dt2.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string))); dt2.Columns.Add(new DataColumn("NewCount", typeof(string))); string profilePicUrl; for (int i = 0; i < dt.Rows.Count; i++) { //create new row dr2 = dt2.NewRow(); //add values to each rows dr2["MessageListId"] = dt.Rows[i]["MessageListId"].ToString(); dr2["OtherId"] = dt.Rows[i]["OtherId"].ToString(); dr2["FirstName"] = dt.Rows[i]["FirstName"].ToString(); dr2["LastName"] = dt.Rows[i]["LastName"].ToString(); dr2["Brief"] = dt.Rows[i]["Brief"].ToString(); dr2["Unread"] = dt.Rows[i]["Unread"].ToString(); dr2["NewCount"] = dt.Rows[i]["NewCount"].ToString(); Classes.Date d = new Classes.Date(); dr2["PassedDate"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString()); // Show profile's photo if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString())) { profilePicUrl = "~/Files/ProfilesPhotos/" + dt.Rows[i]["OtherId"].ToString() + "-100.jpg"; } else { profilePicUrl = "~/Images/nophoto.png"; } dr2["ProfilePicUrl"] = profilePicUrl; //add the row to DataTable dt2.Rows.Add(dr2); } return(dt2); }
public DataTable messageLists(int userId) { DataTable dt = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda = new SqlDataAdapter("sp_messageLists", sqlConn); sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId; try { sda.Fill(ds); dt = ds.Tables[0]; } catch (Exception ex) { } finally { sqlConn.Close(); sda.Dispose(); sqlConn.Dispose(); } DataTable dt2 = new DataTable(); DataRow dr2 = null; //define the columns dt2.Columns.Add(new DataColumn("MessageListId", typeof(string))); dt2.Columns.Add(new DataColumn("OtherId", typeof(string))); dt2.Columns.Add(new DataColumn("FirstName", typeof(string))); dt2.Columns.Add(new DataColumn("LastName", typeof(string))); dt2.Columns.Add(new DataColumn("PassedDate", typeof(string))); dt2.Columns.Add(new DataColumn("Brief", typeof(string))); dt2.Columns.Add(new DataColumn("Unread", typeof(string))); dt2.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string))); dt2.Columns.Add(new DataColumn("NewCount", typeof(string))); string profilePicUrl; for (int i = 0; i < dt.Rows.Count; i++) { //create new row dr2 = dt2.NewRow(); //add values to each rows dr2["MessageListId"] = dt.Rows[i]["MessageListId"].ToString(); dr2["OtherId"] = dt.Rows[i]["OtherId"].ToString(); dr2["FirstName"] = dt.Rows[i]["FirstName"].ToString(); dr2["LastName"] = dt.Rows[i]["LastName"].ToString(); dr2["Brief"] = dt.Rows[i]["Brief"].ToString(); dr2["Unread"] = dt.Rows[i]["Unread"].ToString(); dr2["NewCount"] = dt.Rows[i]["NewCount"].ToString(); Classes.Date d = new Classes.Date(); dr2["PassedDate"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString()); // Show profile's photo if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString())) { profilePicUrl = "~/Files/ProfilesPhotos/" + dt.Rows[i]["UserId"].ToString() + "-220.jpg"; } else { profilePicUrl = "~/Images/ProfilesPhotos/nophoto220.png"; } dr2["ProfilePicUrl"] = profilePicUrl; //add the row to DataTable dt2.Rows.Add(dr2); } return dt2; }
public Tuple<int, DataTable, DataTable, DataTable> showMessages(int userId, int otherId) { int status = 0; DataTable dtUserName = new DataTable(); DataTable dtOtherName = new DataTable(); DataTable dtMessages = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda = new SqlDataAdapter("sp_messagesSenderReceiverInfo", sqlConn); sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.SelectCommand.Parameters.Add("@OwnerId", SqlDbType.Int).Value = userId; sda.SelectCommand.Parameters.Add("@OtherId", SqlDbType.Int).Value = otherId; //try //{ sda.Fill(ds); dtUserName = ds.Tables[0]; dtOtherName = ds.Tables[1]; if (dtUserName.Rows.Count == 0 || dtOtherName.Rows.Count == 0) { status = -1; } //} //catch (Exception ex) //{ //} //finally //{ // sqlConn.Close(); // sda.Dispose(); // sqlConn.Dispose(); //} if (status != -1) { DataTable dt1 = new DataTable(); DataSet ds1 = new DataSet(); SqlConnection sqlConn3 = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda1 = new SqlDataAdapter("sp_messages", sqlConn3); sda1.SelectCommand.CommandType = CommandType.StoredProcedure; sda1.SelectCommand.Parameters.Add("@OwnerId", SqlDbType.Int).Value = userId; sda1.SelectCommand.Parameters.Add("@OtherId", SqlDbType.Int).Value = otherId; sda1.Fill(ds1); dt1 = ds1.Tables[0]; DataRow dr2 = null; //define the columns dtMessages.Columns.Add(new DataColumn("MessageId", typeof(string))); dtMessages.Columns.Add(new DataColumn("Sender", typeof(string))); dtMessages.Columns.Add(new DataColumn("Message", typeof(string))); dtMessages.Columns.Add(new DataColumn("PassedDate", typeof(string))); dtMessages.Columns.Add(new DataColumn("Unread", typeof(string))); dtMessages.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string))); string profilePicUrl; for (int i = 0; i < dt1.Rows.Count; i++) { //create new row dr2 = dtMessages.NewRow(); //add values to each rows dr2["MessageId"] = dt1.Rows[i]["MessageId"].ToString(); dr2["Sender"] = dt1.Rows[i]["Sender"].ToString(); dr2["Message"] = dt1.Rows[i]["Message"].ToString(); dr2["Unread"] = dt1.Rows[i]["Unread"].ToString(); Classes.Date d = new Classes.Date(); dr2["PassedDate"] = d.FormatPassedDate(dt1.Rows[i]["SubmitDate"].ToString()); // Show profile's photo //if (Convert.ToBoolean(dt1.Rows[i]["HasPhoto"].ToString())) //{ // profilePicUrl = "~/Files/ProfilesPhotos/" + dt1.Rows[i]["UserId"].ToString() + "-220.jpg"; //} //else //{ profilePicUrl = "~/Images/ProfilesPhotos/nophoto220.png"; //} dr2["ProfilePicUrl"] = profilePicUrl; //add the row to DataTable dtMessages.Rows.Add(dr2); } } return new Tuple<int, DataTable, DataTable, DataTable>(status, dtUserName, dtOtherName, dtMessages); }
public Tuple <int, DataTable, DataTable, DataTable> showMessages(int userId, int otherId) { int status = 0; DataTable dtUserName = new DataTable(); DataTable dtOtherName = new DataTable(); DataTable dtMessages = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda = new SqlDataAdapter("sp_messagesSenderReceiverInfo", sqlConn); sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.SelectCommand.Parameters.Add("@OwnerId", SqlDbType.Int).Value = userId; sda.SelectCommand.Parameters.Add("@OtherId", SqlDbType.Int).Value = otherId; //try //{ sda.Fill(ds); dtUserName = ds.Tables[0]; dtOtherName = ds.Tables[1]; if (dtUserName.Rows.Count == 0 || dtOtherName.Rows.Count == 0) { status = -1; } //} //catch (Exception ex) //{ //} //finally //{ // sqlConn.Close(); // sda.Dispose(); // sqlConn.Dispose(); //} if (status != -1) { DataTable dt1 = new DataTable(); DataSet ds1 = new DataSet(); SqlConnection sqlConn3 = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda1 = new SqlDataAdapter("sp_messages", sqlConn3); sda1.SelectCommand.CommandType = CommandType.StoredProcedure; sda1.SelectCommand.Parameters.Add("@OwnerId", SqlDbType.Int).Value = userId; sda1.SelectCommand.Parameters.Add("@OtherId", SqlDbType.Int).Value = otherId; sda1.Fill(ds1); dt1 = ds1.Tables[0]; DataRow dr2 = null; //define the columns dtMessages.Columns.Add(new DataColumn("MessageId", typeof(string))); dtMessages.Columns.Add(new DataColumn("Sender", typeof(string))); dtMessages.Columns.Add(new DataColumn("Message", typeof(string))); dtMessages.Columns.Add(new DataColumn("PassedDate", typeof(string))); dtMessages.Columns.Add(new DataColumn("Unread", typeof(string))); dtMessages.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string))); string profilePicUrl; for (int i = 0; i < dt1.Rows.Count; i++) { //create new row dr2 = dtMessages.NewRow(); //add values to each rows dr2["MessageId"] = dt1.Rows[i]["MessageId"].ToString(); dr2["Sender"] = dt1.Rows[i]["Sender"].ToString(); dr2["Message"] = dt1.Rows[i]["Message"].ToString(); dr2["Unread"] = dt1.Rows[i]["Unread"].ToString(); Classes.Date d = new Classes.Date(); dr2["PassedDate"] = d.FormatPassedDate(dt1.Rows[i]["SubmitDate"].ToString()); // Show profile's photo //if (Convert.ToBoolean(dt1.Rows[i]["HasPhoto"].ToString())) //{ // profilePicUrl = "~/Files/ProfilesPhotos/" + dt1.Rows[i]["UserId"].ToString() + "-220.jpg"; //} //else //{ profilePicUrl = "~/Images/ProfilesPhotos/nophoto220.png"; //} dr2["ProfilePicUrl"] = profilePicUrl; //add the row to DataTable dtMessages.Rows.Add(dr2); } } return(new Tuple <int, DataTable, DataTable, DataTable>(status, dtUserName, dtOtherName, dtMessages)); }
public DataTable eventBoardMessages(Int64 eventId, int ownerId) { DataTable dt = new DataTable(); DataTable dtCountries = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda = new SqlDataAdapter("sp_eventBoardMessages", sqlConn); sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.SelectCommand.Parameters.Add("@EventId", SqlDbType.BigInt).Value = eventId; //try //{ sda.Fill(ds); dt = ds.Tables[0]; //} //catch (Exception ex) //{ //} //finally //{ sqlConn.Close(); sda.Dispose(); sqlConn.Dispose(); //} if (dt.Rows.Count == 0)// no messages { return dt; } else { DataTable dt2 = new DataTable(); DataRow dr2 = null; //define the columns dt2.Columns.Add(new DataColumn("MessageId", typeof(string))); dt2.Columns.Add(new DataColumn("IsOwner", typeof(string))); dt2.Columns.Add(new DataColumn("SenderName", typeof(string))); dt2.Columns.Add(new DataColumn("SenderId", typeof(string))); dt2.Columns.Add(new DataColumn("Message", typeof(string))); dt2.Columns.Add(new DataColumn("PassedDate", typeof(string))); dt2.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string))); string profilePicUrl; for (int i = 0; i < dt.Rows.Count; i++) { //create new row dr2 = dt2.NewRow(); //add values to each rows dr2["MessageId"] = dt.Rows[i]["MessageId"].ToString(); dr2["SenderName"] = dt.Rows[i]["FirstName"].ToString() + " " + dt.Rows[i]["LastName"].ToString(); dr2["SenderId"] = dt.Rows[i]["UserId"].ToString(); dr2["Message"] = dt.Rows[i]["Message"].ToString(); Classes.Date d = new Classes.Date(); dr2["PassedDate"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString()); if (ownerId == Convert.ToInt32(dt.Rows[i]["UserId"].ToString())) { dr2["IsOwner"] = "true"; } else { dr2["IsOwner"] = "false"; } // Show profile's photo if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString())) { profilePicUrl = "~/Files/ProfilesPhotos/" + dt.Rows[i]["UserId"].ToString() + "-100.jpg"; } else { profilePicUrl = "~/Images/nophoto.png"; } dr2["ProfilePicUrl"] = profilePicUrl; //add the row to DataTable dt2.Rows.Add(dr2); } return dt2; } }
public MessageObjectAll getMessages(int userId, int otherId, Int64 messageId) { MessageObjectAll allObjects = new MessageObjectAll(); if (messageId == 0) { messageId = 999999999; } DataTable dt = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda1 = new SqlDataAdapter("sp_messages", sqlConn); sda1.SelectCommand.CommandType = CommandType.StoredProcedure; sda1.SelectCommand.Parameters.Add("@OwnerId", SqlDbType.Int).Value = userId; sda1.SelectCommand.Parameters.Add("@OtherId", SqlDbType.Int).Value = otherId; sda1.SelectCommand.Parameters.Add("@MessageId", SqlDbType.Int).Value = messageId; //try //{ sda1.Fill(ds); dt = ds.Tables[0]; //} //catch (Exception ex) //{ //} //finally //{ sqlConn.Close(); sda1.Dispose(); sqlConn.Dispose(); //} if (dt.Rows.Count == 0) { return allObjects; } else { MessageObject object1 = new MessageObject(); MessageObject object2 = new MessageObject(); MessageObject object3 = new MessageObject(); MessageObject object4 = new MessageObject(); MessageObject object5 = new MessageObject(); MessageObject object6 = new MessageObject(); MessageObject object7 = new MessageObject(); MessageObject object8 = new MessageObject(); MessageObject object9 = new MessageObject(); MessageObject object10 = new MessageObject(); // profile pic url int userIdString = 0; Classes.Date d = new Classes.Date(); for (int i = 0; i < dt.Rows.Count; i++) { if (Convert.ToBoolean(dt.Rows[i]["Sender"].ToString())) { userIdString = Convert.ToInt32(dt.Rows[i]["OwnerId"].ToString()); } else { userIdString = Convert.ToInt32(dt.Rows[i]["OtherId"].ToString()); } MessageObject myMessageObject = new MessageObject( Convert.ToInt32(dt.Rows[i]["MessageId"].ToString()), Convert.ToBoolean(dt.Rows[i]["Sender"].ToString()), dt.Rows[i]["Message"].ToString(), d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString()), Convert.ToBoolean(dt.Rows[i]["Unread"].ToString()), userIdString); switch (i) { case 0: { object10 = myMessageObject; break; } case 1: { object9 = myMessageObject; break; } case 2: { object8 = myMessageObject; break; } case 3: { object7 = myMessageObject; break; } case 4: { object6 = myMessageObject; break; } case 5: { object5 = myMessageObject; break; } case 6: { object4 = myMessageObject; break; } case 7: { object3 = myMessageObject; break; } case 8: { object2 = myMessageObject; break; } case 9: { object1 = myMessageObject; break; } } allObjects = new MessageObjectAll( object1, object2, object3, object4, object5, object6, object7, object8, object9, object10); } return allObjects; } }