Open Source Extensible Enterprise n-tier Application Framework with Multilayered Architecture

using System.Data;
using System.Data.Common;
using SiberTek.Xenta.Data.Entities;
using SiberTek.Xenta.Data.Entities.Collections;
using SiberTek.Xenta.Data.Utils;
using System;

namespace SiberTek.Xenta.Data.Providers
{
    public class ForumPrivateMessageDataProvider : DataProviderBase, IForumPrivateMessageDataProvider
    {
        #region Methods
        public bool InsertForumPrivateMessage(int authorID, int recipientID, string subject, string text, bool isRead, bool isDeletedByAuthor, bool isDeletedByRecipient, bool isActive, DateTime createdOn, DateTime updatedOn, out int privateMessageID)
        {
            bool res = false;
            privateMessageID = 0;

            using(DbCommand cmd = DataBase.GetStoredProcCommand("ForumPrivateMessages_Insert"))
            {
                DbCommandHelper.AddInt32(DataBase, cmd, "AuthorID", authorID);
                DbCommandHelper.AddInt32(DataBase, cmd, "RecipientID", recipientID);
                DbCommandHelper.AddString(DataBase, cmd, "Subject", subject);
                DbCommandHelper.AddString(DataBase, cmd, "Text", text);
                DbCommandHelper.AddBoolean(DataBase, cmd, "IsRead", isRead);
                DbCommandHelper.AddBoolean(DataBase, cmd, "IsDeletedByAuthor", isDeletedByAuthor);
                DbCommandHelper.AddBoolean(DataBase, cmd, "IsDeletedByRecipient", isDeletedByRecipient);
                DbCommandHelper.AddBoolean(DataBase, cmd, "IsActive", isActive);
                DbCommandHelper.AddOutInt32(DataBase, cmd, "PrivateMessageID");
                DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOn", createdOn);
                DbCommandHelper.AddDateTime(DataBase, cmd, "UpdatedOn", updatedOn);

                res = DataBase.ExecuteNonQuery(cmd) > 0;

                if(res)
                {
                    privateMessageID = DbCommandHelper.GeInt32(DataBase, cmd, "@PrivateMessageID");
                }
            }

            return res;
        }

        public ForumPrivateMessageData GetForumPrivateMessage(int privateMessageID)
        {
            ForumPrivateMessageData data = null;

            if(privateMessageID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("ForumPrivateMessages_Select"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "PrivateMessageID", privateMessageID);

                    using(IDataReader reader = DataBase.ExecuteReader(cmd))
                    {
                        if(reader.Read())
                        {
                            data = ReadData(reader);
                        }
                    }
                }
            }

            return data;
        }

        public ForumPrivateMessageDataCollection GetAllForumPrivateMessages(string searchTerm, int? authorID, int? recipientID, DateTime? createdOnStart, DateTime? createdOnEnd, bool showHidden, bool showDeletedByAuthor, bool showDeletedByRecipient, int startIndex, int count, out int totalCount)
        {
            ForumPrivateMessageDataCollection dataCollection = new ForumPrivateMessageDataCollection();
            totalCount = 0;

            using(DbCommand cmd = DataBase.GetStoredProcCommand("ForumPrivateMessages_SelectAll"))
            {
                DbCommandHelper.AddString(DataBase, cmd, "SearchTerm", searchTerm);
                if(authorID.HasValue)
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "AuthorID", authorID.Value);
                }
                if(recipientID.HasValue)
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "RecipientID", recipientID.Value);
                }
                if(createdOnStart.HasValue)
                {
                    DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOnStart", createdOnStart.Value);
                }
                if(createdOnEnd.HasValue)
                {
                    DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOnEnd", createdOnEnd.Value);
                }
                DbCommandHelper.AddBoolean(DataBase, cmd, "ShowHidden", showHidden);
                DbCommandHelper.AddBoolean(DataBase, cmd, "ShowDeletedByAuthor", showDeletedByAuthor);
                DbCommandHelper.AddBoolean(DataBase, cmd, "ShowDeletedByRecipient", showDeletedByRecipient);
                DbCommandHelper.AddInt32(DataBase, cmd, "StartIndex", startIndex);
                DbCommandHelper.AddInt32(DataBase, cmd, "Count", count);
                DbCommandHelper.AddOutInt32(DataBase, cmd, "TotalCount");

                using(IDataReader reader = DataBase.ExecuteReader(cmd))
                {
                    while(reader.Read())
                    {
                        dataCollection.Add(ReadData(reader));
                    }
                }

                totalCount = DbCommandHelper.GeInt32(DataBase, cmd, "@TotalCount");
            }

            return dataCollection;
        }

        public bool UpdateForumPrivateMessage(int privateMessageID, int authorID, int recipientID, string subject, string text, bool isRead, bool isDeletedByAuthor, bool isDeletedByRecipient, bool isActive, DateTime createdOn, DateTime updatedOn)
        {
            bool res = false;

            if(privateMessageID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("ForumPrivateMessages_Update"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "PrivateMessageID", privateMessageID);
                    DbCommandHelper.AddInt32(DataBase, cmd, "AuthorID", authorID);
                    DbCommandHelper.AddInt32(DataBase, cmd, "RecipientID", recipientID);
                    DbCommandHelper.AddString(DataBase, cmd, "Subject", subject);
                    DbCommandHelper.AddString(DataBase, cmd, "Text", text);
                    DbCommandHelper.AddBoolean(DataBase, cmd, "IsRead", isRead);
                    DbCommandHelper.AddBoolean(DataBase, cmd, "IsDeletedByAuthor", isDeletedByAuthor);
                    DbCommandHelper.AddBoolean(DataBase, cmd, "IsDeletedByRecipient", isDeletedByRecipient);
                    DbCommandHelper.AddBoolean(DataBase, cmd, "IsActive", isActive);
                    DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOn", createdOn);
                    DbCommandHelper.AddDateTime(DataBase, cmd, "UpdatedOn", updatedOn);

                    res = DataBase.ExecuteNonQuery(cmd) > 0;
                }
            }

            return res;
        }

        public bool DeleteForumPrivateMessage(int privateMessageID)
        {
            bool res = false;

            if(privateMessageID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("ForumPrivateMessages_Delete"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "PrivateMessageID", privateMessageID);

                    res = DataBase.ExecuteNonQuery(cmd) > 0;
                }
            }

            return res;
        }
        #endregion

        #region Utilities 
        private static ForumPrivateMessageData ReadData(IDataReader reader)
        {
            ForumPrivateMessageData data = new ForumPrivateMessageData();

            data.PrivateMessageID = DataReaderHelper.ReadInt32(reader, "PrivateMessageID");
            data.AuthorID = DataReaderHelper.ReadInt32(reader, "AuthorID");
            data.RecipientID = DataReaderHelper.ReadInt32(reader, "RecipientID");
            data.Subject = DataReaderHelper.ReadString(reader, "Subject");
            data.Text = DataReaderHelper.ReadString(reader, "Text");
            data.IsRead = DataReaderHelper.ReadBoolean(reader, "IsRead");
            data.IsDeletedByAuthor = DataReaderHelper.ReadBoolean(reader, "IsDeletedByAuthor");
            data.IsDeletedByRecipient = DataReaderHelper.ReadBoolean(reader, "IsDeletedByRecipient");
            data.IsActive = DataReaderHelper.ReadBoolean(reader, "IsActive");
            data.CreatedOn = DataReaderHelper.ReadDateTime(reader, "CreatedOn");
            data.UpdatedOn = DataReaderHelper.ReadDateTime(reader, "UpdatedOn");

            return data;
        }
        #endregion
    }
}