Click here to Skip to main content
Click here to Skip to main content
Articles » Web Development » ASP.NET » Samples » Downloads
 
Add your own
alternative version

Building a Web Message Board using Visual Studio 2008, Part I - The Basic Message Board

, 30 Dec 2007
This article builds a web based message board and uses several new technologies introduced with Visual Studio 2008 such as LINQ, WCF Web Programming, WCF Syndication, ASP.NET ListView, ASP.NET DataPager etc.
MessageBoard.zip
MessageBoard
MessageBoard.Core
MessageBoard.snk
Properties
MessageBoard.DataAccess.Linq
MessageBoard.snk
Properties
Settings.settings
MessageBoard.DataAccess.NonLinq
MessageBoard.snk
Properties
MessageBoard.Web
MessageBoard.snk
Properties
Service References
MessageBoard
App_Code
App_Data
App_Themes
Default
Floating
MessageBoard.png
newpost.jpg
post.jpg
Outlook
controlBarGradient.jpg
headerGradient.jpg
headerGradient2.jpg
ClassDiagram1.cd
Feed.svc
Global.asax
images
rss16.png
Install
Application Files
MessageBoard.Word_1_0_0_0
MessageBoard.dotx
MessageBoard.Word.dll.config.deploy
MessageBoard.Word.dll.deploy
MessageBoard.Word.dll.manifest
MessageBoard.Word.vsto
MessageBoard.dotx
MessageBoard.Word.vsto
setup.exe
MessageBoardService.svc
WebSite
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Globalization;

namespace MessageBoard.DataAccess.NonLinq
{
    public class NonLinqMessageProvider : IMessageProvider
    {
        #region IMessageSource Members

        const string GETRECENTMESSAGESSQL = @"WITH OrderedMessages AS
            (
                SELECT id, subject, text, postedBy, postedById, DatePosted,  
		            ROW_NUMBER() OVER (ORDER BY DatePosted Desc) AS 'RowNumber'
                FROM Messages WHERE Id >= @id
            ) 
            SELECT * FROM OrderedMessages
            WHERE RowNumber BETWEEN @start and @start + @count - 1";



        public IEnumerable<Message> GetRecentMessages(int lastId, int start, int count)
        {
            List<Message> messages = new List<Message>();

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            using (SqlCommand cmd = new SqlCommand(GETRECENTMESSAGESSQL, conn))
            {
                conn.Open();

                cmd.Parameters.AddWithValue("@id", lastId);
                cmd.Parameters.AddWithValue("@start", start);
                cmd.Parameters.AddWithValue("@count", count);

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        string subject = reader.GetString(1);
                        string text = reader.GetString(2);
                        string postedBy = reader.GetString(3);
                        string postedById = reader.GetString(4);
                        DateTime postedDate = reader.GetDateTime(5);

                        Message m = new Message(id, subject, text, postedBy, postedById, postedDate);
                        messages.Add(m);
                    }
                }
            }

            return messages;
        }

        private static string ConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
            }
        }

        const string GETMESSAGECOUNTSQL = @"SELECT COUNT(1) FROM Messages";

        public int GetMessageCount()
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            using (SqlCommand cmd = new SqlCommand(GETMESSAGECOUNTSQL, conn))
            {
                conn.Open();
                return (int)cmd.ExecuteScalar();
            }

        }

        const string ADDMESSAGESQL = @"INSERT [Messages] ([Subject], [Text], [PostedBy], [PostedById], [DatePosted]) 
                        VALUES (@subject, @text, @postedBy, @postedById, @postedDate)
                        SELECT SCOPE_IDENTITY()";


        public int AddMessage(string subject, string text, string postedBy, string postedById, DateTime datePosted)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            using (SqlCommand cmd = new SqlCommand(ADDMESSAGESQL, conn))
            {
                conn.Open();

                cmd.Parameters.AddWithValue("@subject", subject);
                cmd.Parameters.AddWithValue("@text", text);
                cmd.Parameters.AddWithValue("@postedBy", postedBy);
                cmd.Parameters.AddWithValue("@postedById", postedById);
                cmd.Parameters.AddWithValue("@postedDate", datePosted);

                return Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.InvariantCulture);
            }
        }

        const string GETMESSAGESQL = @"SELECT Id, Subject, Text, PostedBy, PostedById, DatePosted
                FROM  Messages
                WHERE (Id = @Id)";

        public IEnumerable<Message> GetMessageById(int id)
        {
            List<Message> messages = new List<Message>();

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            using (SqlCommand cmd = new SqlCommand(GETMESSAGESQL, conn))
            {
                conn.Open();

                cmd.Parameters.AddWithValue("@id", id);

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    //There is only one message anyway so using reader.Read
                    //in a while loop
                    while (reader.Read())
                    {
                        string subject = reader.GetString(1);
                        string text = reader.GetString(2);
                        string postedBy = reader.GetString(3);
                        string postedById = reader.GetString(4);
                        DateTime postedDate = reader.GetDateTime(5);

                        Message m = new Message(id, subject, text, postedBy, postedById, postedDate);
                        messages.Add(m);
                    }
                }
            }

            return messages;
        }

        #endregion
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Rama Krishna Vavilala
Architect
United States United States
No Biography provided

| Advertise | Privacy | Mobile
Web01 | 2.8.140718.1 | Last Updated 30 Dec 2007
Article Copyright 2007 by Rama Krishna Vavilala
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid