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

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
    }
}