locked
Enable Comments on News/Announcement items RRS feed

  • Question

  • User742541801 posted

    Maybe this will help someone.

    I added a new table to hold comments on the posted News (Announcements) Items:

    CREATE TABLE [NewsComments](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[itemdate] [datetime] NOT NULL,
    	[CommentText] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[NewsItemId] [int] NOT NULL,
    	[postedBy] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[wantUpdates] [bit] NOT NULL
    ) ON [PRIMARY]
    GO
    

    Then  I added some login in the News_View.aspx to display and update the contents of that table.  The new file is here:

     
    <%@ Page Language="C#" MasterPageFile="~/Default.master" Title="Untitled Page"  Trace="false"%>
    
    <%@ Register TagPrefix="Club" Namespace="ClubSite" %>
    <%@ Register TagPrefix="Club" TagName="LoginBanner" Src="LoginBanner.ascx" %>
    <%@ Register TagPrefix="Club" TagName="ImageThumbnail" Src="ImageThumbnail.ascx" %>
    
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.SqlClient" %>
    
    <script runat="server">
    
        private class NextPrev
        {
            public int nextArticleId;
            public int prevArticleId;
            public NextPrev() { nextArticleId = INVALIDID; prevArticleId = INVALIDID; }
        }
        
        private NextPrev pointers;
        const int INVALIDID = -1;
    
        private bool IsAdmin;
        protected void Page_Load(object sender, System.EventArgs e)
        {
    
            IsAdmin = User.IsInRole("Administrators");
            AddCommentPanel.Visible = IsAdmin;
    
            if (!IsPostBack)
            {
                Trace.Write("Not Post Back");
                InitDatasources();
                EnableLinksIntelligently();
            }
        }
        
        protected void FormView1_DataBound(object sender, System.EventArgs e)
        {
            DataRowView view = (DataRowView)(FormView1.DataItem);
            if (view == null) Response.Redirect("Default.aspx");
            object o = view["staticURL"];
            if ((o != null) &&  (o != DBNull.Value))
            {
                string staticurl = (string)o;
                if (staticurl != "")
                {
                    Response.Redirect(staticurl);
                }
            }
        }
    
        protected void nextButton_Click(object sender, System.EventArgs e)
        {
            NextPrev np1 = GetNextPrev(ArticleID);
            if (np1.nextArticleId != INVALIDID)
            { 
                ArticleID = np1.nextArticleId;
                InitDatasources();
            }
            EnableLinksIntelligently();
        }
    
        protected void prevButton_Click(object sender, System.EventArgs e)
        {
            Trace.Write("Previous");
    
            NextPrev np1= GetNextPrev(ArticleID);
            if (np1.prevArticleId != INVALIDID)
            {
                ArticleID = np1.prevArticleId;
                InitDatasources();
            }
            EnableLinksIntelligently();
        }
    
        
        
        void SendNotificationMail()
        {
            SqlDataReader myReader = null;
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ClubSiteDB"].ConnectionString);
    
                // get the name and email for all users who want email notification on updates to this particular news item: 
                SqlCommand command = new SqlCommand(
                "SELECT DISTINCT nc.postedby, m.Email, n.Title  FROM  NewsComments nc INNER JOIN aspnet_Users u ON  nc.postedBy = u.UserName INNER JOIN aspnet_Membership m ON u.userId = m.userId INNER JOIN News n ON nc.NewsItemId = n.Id   WHERE  nc.NewsItemId = @NewsItemId and nc.wantUpdates = 1",
                connection);
    
                SqlParameter p1 = new SqlParameter("@NewsItemId", SqlDbType.Int);
                p1.Value = ArticleID;
                command.Parameters.Add(p1);
    
                connection.Open();
                 myReader = command.ExecuteReader();
                bool done = false;
                System.Net.Mail.SmtpClient SmtpClient = SharedRoutines.GetSmtpClient();
                do
                {
                    if (myReader.Read())
                    {
                        string UserName = myReader.GetString(0);
                        string EmailTo = myReader.GetString(1);
                        string NewsItemSubject = myReader.GetString(2);
                        Trace.Write(String.Format("SendNotificationMail(): mailing user ({0}, {1})...", UserName, EmailTo));
                        System.Net.Mail.MailMessage MailMessage = new System.Net.Mail.MailMessage();
                        MailMessage.To.Add(EmailTo);
                        MailMessage.From = new System.Net.Mail.MailAddress("Someone@YourOrg.net");
                        MailMessage.Subject = "YourClubWebSite News Item Comment";
                        if (UserName != User.Identity.Name)
                            MailMessage.Body = String.Format("Hello {0} . \n\nA comment has been made to the News Item  entitled '{1}'.  " +
                                "You have asked for email notification.  " +
                                "You can view the updated item at: http://www.YourUrlHere.net/news_view.aspx?ArticleID={2}.\n\n-YourClubWebSite Mgmt\n",
                                UserName, NewsItemSubject, ArticleID);
                        
                        else
                            MailMessage.Body = String.Format("Hello {0} . \n\nYour comment to the News Item  entitled '{1}' has been accepted.  " +
                                "You can view the updated item at: http://www.YourUrlHere.net/news_view.aspx?ArticleID={2}.\n\n-YourClubWebSite Mgmt\n",
                                UserName, NewsItemSubject, ArticleID);
    
                        SmtpClient.Send(MailMessage);
                    }
                    else done = true;
                } while (!done);
            }
            catch (Exception e1)
            {
                statusLabel.Text += "<br/><br/>But we failed to send email notification:  " + e1;
            }
            finally
            {
                if (myReader!=null) myReader.Close();
                if (connection!=null) connection.Close();
            }
        }
        
        
        void InitDatasources()
        {
            // if the panel that contains a databound control is not visible, the select is optimized away, even on "previous" or "next".  
            // so, we always set these panels to be visible, while updating the query.
            PhotoPanel.Visible = true; 
            CommentPanel.Visible = true; 
            SqlDataSource1.SelectParameters["id"].DefaultValue = System.Convert.ToString(ArticleID);
            SqlDataSource2.SelectParameters["id"].DefaultValue = System.Convert.ToString(ArticleID);
            SqlDataSource3.SelectParameters["NewsItemId"].DefaultValue = System.Convert.ToString(ArticleID);
        }
    
        void EnableLinksIntelligently()
        {
            pointers = GetNextPrev(ArticleID);
            Trace.Write(String.Format("New links: curr={0} next={1} prev={2}", ArticleID, pointers.nextArticleId, pointers.prevArticleId));
            LinkButton1.Enabled = //(pointers.nextArticleId != INVALIDID);
            LinkButton3.Enabled = (pointers.nextArticleId != INVALIDID);
            LinkButton2.Enabled = //(pointers.prevArticleId != INVALIDID);
            LinkButton4.Enabled = (pointers.prevArticleId != INVALIDID);
        }
    
        protected bool CanEdit(string postedBy)
        {
            MembershipUser user = Membership.GetUser();
            return (User.IsInRole("Administrators") && (postedBy == user.UserName));
        }
        
        int ArticleID
        {
            get
            {
                int m_articleID;
                object id = ViewState["ArticleId"];
                if (id != null)
                {
                    m_articleID = System.Convert.ToInt32(id);
                }
                else
                {
                    id = Request.QueryString["ArticleId"];
                    if (id != null)
                    {
                        m_articleID = System.Convert.ToInt32(id);
                    }
                    else
                    {
                        m_articleID = 1;
                    }
                    ViewState["ArticleId"] = m_articleID;
                }
                return m_articleID;
            }
            set
            {
                ViewState["ArticleId"] = value;
            }
        }
    
    
    
        NextPrev GetNextPrev (int ArticleID)
        {
            NextPrev np = new NextPrev();
            try
            {
                SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ClubSiteDB"].ConnectionString);
                SqlCommand command = new SqlCommand("NextPrevAnnouncement", connection);
            
                SqlParameter param0 = new SqlParameter("@id", ArticleID);
                SqlParameter param1 = new SqlParameter("@previd", INVALIDID);
                SqlParameter param2 = new SqlParameter("@nextid", INVALIDID);
                
                param1.Direction = ParameterDirection.InputOutput;
                param2.Direction = ParameterDirection.InputOutput;
                
                command.Parameters.Add(param0);
                command.Parameters.Add(param1);
                command.Parameters.Add(param2);
                
                command.CommandType = CommandType.StoredProcedure;
                connection.Open();
                command.ExecuteNonQuery();
                if (param1.Value != null && param1.Value != DBNull.Value)
                {
                    np.prevArticleId = Convert.ToInt32(param1.Value);
                }
                else
                {
                    np.prevArticleId = INVALIDID;
                }
                if (param2.Value != null && param2.Value != DBNull.Value)
                {
                    np.nextArticleId= Convert.ToInt32(param2.Value);
                }
                else
                {
                    np.nextArticleId = INVALIDID;
                }
                connection.Close();
            }
            catch (Exception e1)
            {
                Trace.Write(String.Format("Exception while getting next/prev news: {0}", e1.Message));
                np = new NextPrev();
            }
            return np;
        }
    
    
        protected void SqlDataSource2_Selected(object sender, System.Web.UI.WebControls.SqlDataSourceStatusEventArgs e)
        {
            PhotoPanel.Visible = (e.AffectedRows != 0);
        }
    
        protected void SqlDataSource3_Selected(object sender, System.Web.UI.WebControls.SqlDataSourceStatusEventArgs e)
        {
            CommentPanel.Visible = (e.AffectedRows != 0);
        }
    
        protected void Comment_Inserting(object sender, System.Web.UI.WebControls.FormViewInsertEventArgs e)
        {
            e.Values.Add("postedBy", User.Identity.Name);
            e.Values.Add("NewsItemId", ArticleID.ToString());
        }
        
        protected void Comment_Inserted(object sender, System.Web.UI.WebControls.FormViewInsertedEventArgs e)
        {        
            if (e.Exception != null)
            {
                Session["StatusMessage"] = "An error occured while entering your comment.<br/>" + e.Exception;
                e.ExceptionHandled = true;
            }
            else
            {
                Session["StatusMessage"] = "Ok, we added that comment. ";
                SendNotificationMail();
            }
            CommentList.DataBind();
    
        }
    
        private string CommentLabel(object postedBy, object stamp)
        {
            string p1= postedBy.ToString().Trim();
            string p2= "??";
            if (stamp is System.DateTime)
            {
                System.DateTime s1 = (System.DateTime)stamp;
                p2= s1.ToString("yyyy MMM dd hh:mmttt");
            }
            return " - " + p1 + " // " + p2; 
        }            
    </script>
    
    <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>"
            SelectCommand="SELECT id, itemdate, title, description, photo, albumid, staticURL, postedBy FROM News WHERE (id = @id)">
            <SelectParameters>
                <asp:Parameter Type="Int32" DefaultValue="1" Name="id"></asp:Parameter>
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ClubsiteDB %>"
            SelectCommand="SELECT images.id, images.album, images.title FROM images, News WHERE images.album=News.albumid AND News.id=@id"
            OnSelected="SqlDataSource2_Selected">
            <SelectParameters>
                <asp:Parameter Type="Int32" DefaultValue="1" Name="id"></asp:Parameter>
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>"
            SelectCommand="SELECT [id], dateadd(minute,180,itemdate) as itemdate, [CommentText], [NewsItemId], [postedBy] FROM [NewsComments]  WHERE ([NewsItemId] = @NewsItemId)  ORDER BY id ASC"
            InsertCommand="INSERT INTO NewsComments (itemdate, CommentText, NewsItemId, postedBy, wantUpdates) VALUES (GETDATE(), @CommentText, @NewsItemId, @postedBy, @wantUpdates)"
            OnSelected="SqlDataSource3_Selected"
            >
            <SelectParameters>
                <asp:Parameter Type="Int32" DefaultValue="1" Name="NewsItemId"></asp:Parameter>
            </SelectParameters>
    
            <InsertParameters>
                <asp:Parameter Name="CommentText"  Type="String" />
                <asp:Parameter Name="NewsItemId"   Type="Int32" />
                <asp:Parameter Name="postedBy"     Type="String" />
                <asp:Parameter Name="wantUpdates"  Type="Boolean" />
            </InsertParameters>
    
        </asp:SqlDataSource>
    
    
        <div id="body">
            <Club:LoginBanner ID="LoginBanner1" runat="server" />
            <!--
            
            Left column
            
            -->
            <div id="columnleft">
                <div class="leftblock">
                    <h2>
                        News </h2>
                    <p>
                        Here you'll find news items covering stuff that YourClubWebsite Manager finds noteworthy.<br />
                        <br />
                        <%= SharedRoutines.GetStatusMessage() %>
                        <asp:Label runat="server" ID="statusLabel" EnableViewState="false" />
                    </p>
                </div>
                <asp:Panel ID="PhotoPanel" runat="server" CssClass="leftblock">
                    <h2>
                        Associated Album photos</h2>
                    <asp:DataList ID="DataList2" runat="server" DataSourceID="SqlDataSource2" DataKeyField="id"
                        RepeatColumns="2" CellSpacing="2" SelectedIndex="0" ItemStyle-CssClass="unselected">
                        <ItemTemplate>
                            <a href='photoalbum_contents.aspx?Albumid=<%#Eval("album") %>'>
                                <asp:Image ID="Image1" ImageUrl='<%# "imagefetch.ashx?size=1&imageid=" + Convert.ToString(Eval("id")) %>'
                                    runat="server" /><br />
                                <asp:Label Text='<%# Eval("title") %>' runat="server" ID="titleLabel" />
                            </a>
                        </ItemTemplate>
                    </asp:DataList>
                </asp:Panel>
            </div>
            <!--
            
            Right column
            
            -->
            <div id="columnright">
                <div class="rightblock">
                    <a href="news_list.aspx">News Article List</a></div>
                <div class="rightblock">
                    <div class="nextlink">
                        <asp:LinkButton ID="LinkButton1" runat="server" OnClick="nextButton_Click">Next Article »</asp:LinkButton>
                    </div>
                    <asp:LinkButton ID="LinkButton2" runat="server" OnClick="prevButton_Click">« Previous article</asp:LinkButton>
                    <div class="dashedline">
                    </div>
                    <asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1" DataKeyNames="id"
                        Width="444px" OnDataBound="FormView1_DataBound">
                        <ItemTemplate>
                            <h2>
                                <asp:Label Text='<%# Eval("title") %>' runat="server" ID="titleLabel" />
                            </h2>
                            <div class="itemdetails">
                                <p>
                                    <asp:Label Text='<%# Eval("itemdate","{0:D}") %>' runat="server" ID="itemdateLabel" />
                                </p>
                            </div>
                            <Club:ImageThumbnail ID="thumb1" runat="server" ImageSize="large" PhotoID='<%# Eval("photo") %>' />
                            <p>
                                <asp:Label Text='<%# Eval("description") %>' runat="server" ID="descriptionLabel" />
                            </p>
                            <asp:Panel ID="panel3" runat="server" Visible='<%# Eval("postedBy").ToString()!="" %>'>
                                <p>
                                (last updated by 
                                    <asp:Label ID="postedByLabel" runat="server" Text='<%# SharedRoutines.truncate(Eval("postedBy").ToString()) %>' />)
                                </p>
                            </asp:Panel>
                            <asp:Panel runat="server" ID="panel1" CssClass="actionbuttons" Visible='<%# CanEdit(Eval("postedBy").ToString()) %>'>
                                <Club:RolloverLink ID="editbtn" runat="server" Text="Edit Article" NavigateURL='<%# "news_edit.aspx?ArticleID=" + Convert.ToString( ArticleID )%>' />
                            </asp:Panel>
                        </ItemTemplate>
                    </asp:FormView>
                    <div class="dashedline">
                    </div>
                    <asp:Panel runat="server" ID="CommentPanel">
                        <asp:Repeater ID="CommentList" runat="server" DataSourceID="SqlDataSource3">
                            <HeaderTemplate>
                                <h3>
                                    Comments on this item</h3>
                                <br />
                            </HeaderTemplate>
                            <ItemTemplate>
                                <div class="listitem">
                                    <div style="border: 1px solid #696969; margin-top: 2px; margin-bottom:2px; padding-top:2px; padding-left:4px; padding-bottom:2px; padding-right:4px;">
                                        <asp:Label Text='<%# Eval("CommentText") %>' runat="server" ID="commentLabel" Style="color: #483D8B;" />
                                        <br />
                                        <asp:Label ID="headerLabel" Style="color: #696969; margin-top: 4px;padding-bottom:0px; "
                                            runat="server" Text='<%# CommentLabel(Eval("postedBy"),Eval("itemdate")) %>' Font-Size="8pt"/>
                                        <br />
                                    </div>
                                </div>
                            </ItemTemplate>
                        </asp:Repeater>
                        <div class="dashedline">
                        </div>
                    </asp:Panel>
                    <asp:Panel runat="server" ID="AddCommentPanel" CssClass="editbuttons" Visible='<%#IsAdmin %>'>
                        <asp:FormView ID="FormView2" runat="server" DataSourceID="SqlDataSource3" DefaultMode="insert"
                            OnItemInserting="Comment_Inserting" OnItemInserted="Comment_Inserted">
                            <InsertItemTemplate>
                                <asp:TextBox ID="CommentAddText" runat="server" TextMode="MultiLine" Rows="3" Width="434"
                                    Columns="30" Text='<%# Bind("CommentText") %>' />
                                <asp:CheckBox ID="CommentAddWantEmailNotification" runat="server" Text="Send me email notification of updates"
                                    Checked='<%# Bind("wantUpdates") %>' />
                                <br />
                                <asp:Button ID="CommentAddInsertButton" Text="Add this Comment" CommandName="Insert"
                                CssClass="btnCssClass1"
                                    runat="server" />
                            </InsertItemTemplate>
                        </asp:FormView>
                    </asp:Panel>
                    <div class="nextlink">
                        <asp:LinkButton ID="LinkButton3" runat="server" OnClick="nextButton_Click">Next Article »</asp:LinkButton>
                    </div>
                    <asp:LinkButton ID="LinkButton4" runat="server" OnClick="prevButton_Click">« Previous Article</asp:LinkButton>
                </div>
            </div>
            <div class="clear2column">
            </div>
        </div>
    </asp:Content>
    

     

    Bugs:

    • I changed the tablename for Announcements to News. For anyone who has the original tablename, you'll need to change it back in the above code.
    • I also changed the schema of the News/Announcements table to include a postedBy column.  This is used in the FormView1 above, which displays the original news item.  If the person viewing the item is the original poster, they can edit it.  If not, they cannot edit it.  If you don't want this feature, change it back to however it was in the original Club Starter Kit. (I forget how it used to work).
    • I didn't use CSS classes for the comment display. 
    • The DataSource3 (for the NewsComments table) selects using a dateadd() SQL function, subtracting 180 minutes (3 hours) from the stored time.  This is a hack because my hoster is 3 hours off from my actual user base.  You may have to change this for your own purposes.  Or, better, provide a general solution.
    • Only Admins are allowed to post comments.  This is dumb and confusing.  Ideally I should create an additional role in the Club Web, to allow users to post comments.  I'll leave that up to you all.  
    • I reference a new method called SharedRoutines.GetStatusMessage(). it is included at the end of this message.  Essentially it extracts and then resets a session variable. This is useful for sending messages across pages. I found that the Club Starter Kit could fail silently (eg on overflow of text column widths in the database tables).  This GetStatusMessage() is my fix for that.  When there's a failure, I stuff it in a session variable and then display it on the next loaded page. code for this is below.   
    • The button for the "Add this Comment" form uses a new Css class called btnCssClass1, which you need to add to your clubsite.css file. Mine is right here:
        .btnCssClass1 { 
                 font-size: 9px;
                 margin: 0; 
                 height: 18px;  
                 padding: 0;
                 border: 1;
                 } 
    
    

     here's SharedRoutines.GetStatusMessage()  (put it in SharedRoutines.cs):

     
        public static string GetStatusMessage()
        {
            if (HttpContext.Current == null) return "";
            if (HttpContext.Current.Session["StatusMessage"] != null)
            {
                string result= HttpContext.Current.Session["StatusMessage"].ToString();
                HttpContext.Current.Session["StatusMessage"] = null;
                return result;
            }
            return "";
        }
    
     
    Wednesday, April 26, 2006 6:34 PM

All replies

  • User1275632693 posted

    Nice work there Cheeso, keep it up.

    I will most likely have to switch to the C# version of the Kit, I certainly don't want to end up with a Bilingual Kit (VB and C# in various pages)

     

     

     

     

    Wednesday, April 26, 2006 11:20 PM
  • User1853794821 posted

    The Select in     // get the name and email for all users who want email notification on updates to this particular news item:
                SqlCommand command = new SqlCommand(
                "SELECT DISTINCT nc.postedby, m.Email, n.Title  FROM  NewsComments nc INNER JOIN aspnet_Users u ON  nc.postedBy = u.UserName INNER JOIN aspnet_Membership m ON u.userId = m.userId INNER JOIN News n ON nc.NewsItemId = n.Id   WHERE  nc.NewsItemId = @NewsItemId and nc.wantUpdates = 1",
                connection);

    doesnt work with the out-of-the-box Starter Kit.  The code appears to assume that everything is in the same database, whereas the SK has two separate databases: ASPNETDB and Club.

     

    I would be interested if anyone can get the code in this example to work with the base SK.  I can't

    Saturday, August 5, 2006 12:14 PM