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 ForumBanDataProvider : DataProviderBase, IForumBanDataProvider
    {
        #region Methods
        public bool InsertForumBan(int userID, string description, DateTime createdOn, DateTime? expiredOn, DateTime updatedOn, out int banID)
        {
            bool res = false;
            banID = 0;

            using(DbCommand cmd = DataBase.GetStoredProcCommand("ForumBans_Insert"))
            {
                DbCommandHelper.AddInt32(DataBase, cmd, "UserID", userID);
                DbCommandHelper.AddString(DataBase, cmd, "Description", description);
                DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOn", createdOn);
                if(expiredOn.HasValue)
                {
                    DbCommandHelper.AddDateTime(DataBase, cmd, "ExpiredOn", expiredOn.Value);
                }
                DbCommandHelper.AddDateTime(DataBase, cmd, "UpdatedOn", updatedOn);
                DbCommandHelper.AddOutInt32(DataBase, cmd, "BanID");

                res = DataBase.ExecuteNonQuery(cmd) > 0;

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

            return res;
        }

        public ForumBanData GetForumBan(int banID)
        {
            ForumBanData data = null;

            if(banID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("ForumBans_Select"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "BanID", banID);

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

            return data;
        }

        public ForumBanDataCollection GetAllForumBans(int? userID, DateTime? createdOnStart, DateTime? createdOnEnd, DateTime? expiredOnStart, DateTime? expiredOnEnd, int startIndex, int count, out int totalCount)
        {
            ForumBanDataCollection dataCollection = new ForumBanDataCollection();

            using(DbCommand cmd = DataBase.GetStoredProcCommand("ForumBans_SelectAll"))
            {
                if(userID.HasValue)
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "UserID", userID.Value);
                }
                if(createdOnStart.HasValue)
                {
                    DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOnStart", createdOnStart.Value);
                }
                if(createdOnEnd.HasValue)
                {
                    DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOnEnd", createdOnEnd.Value);
                }
                if(expiredOnStart.HasValue)
                {
                    DbCommandHelper.AddDateTime(DataBase, cmd, "ExpiredOnStart", expiredOnStart.Value);
                }
                if(expiredOnEnd.HasValue)
                {
                    DbCommandHelper.AddDateTime(DataBase, cmd, "ExpiredOnEnd", expiredOnEnd.Value);
                }
                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 UpdateForumBan(int banID, int userID, string description, DateTime createdOn, DateTime? expiredOn, DateTime updatedOn)
        {
            bool res = false;

            if(banID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("ForumBans_Update"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "BanID", banID);
                    DbCommandHelper.AddInt32(DataBase, cmd, "UserID", userID);
                    DbCommandHelper.AddString(DataBase, cmd, "Description", description);
                    DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOn", createdOn);
                    if(expiredOn.HasValue)
                    {
                        DbCommandHelper.AddDateTime(DataBase, cmd, "ExpiredOn", expiredOn.Value);
                    }
                    DbCommandHelper.AddDateTime(DataBase, cmd, "UpdatedOn", updatedOn);

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

            return res;
        }

        public bool DeleteForumBan(int banID)
        {
            bool res = false;

            if(banID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("ForumBans_Delete"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "BanID", banID);

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

            return res;
        }
        #endregion 

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

            data.BanID = DataReaderHelper.ReadInt32(reader, "BanID");
            data.UserID = DataReaderHelper.ReadInt32(reader, "UserID");
            data.Description = DataReaderHelper.ReadString(reader, "Description");
            data.CreatedOn = DataReaderHelper.ReadDateTime(reader, "CreatedOn");
            data.ExpiredOn = DataReaderHelper.ReadNullableDateTime(reader, "ExpiredOn");
            data.UpdatedOn = DataReaderHelper.ReadDateTime(reader, "UpdatedOn");

            return data;
        }
        #endregion
    }
}