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

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

namespace SiberTek.Xenta.Data.Providers
{
    public class TagDataProvider : DataProviderBase, ITagDataProvider
    {
        #region Methods
        public bool InsertTag(string name, DateTime createdOn, DateTime updatedOn, out int tagID)
        {
            bool res = false;
            tagID = 0;

            using(DbCommand cmd = DataBase.GetStoredProcCommand("Tags_Insert"))
            {
                DbCommandHelper.AddString(DataBase, cmd, "Name", name);
                DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOn", createdOn);
                DbCommandHelper.AddDateTime(DataBase, cmd, "UpdatedOn", updatedOn);
                DbCommandHelper.AddOutInt32(DataBase, cmd, "TagID");

                res = DataBase.ExecuteNonQuery(cmd) > 0;

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

            return res;
        }

        public bool InsertTagPublicationMapping(int tagID, int publicationID)
        {
            bool res = false;

            if(tagID > 0 && publicationID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("TagPublicationMap_Insert"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "TagID", tagID);
                    DbCommandHelper.AddInt32(DataBase, cmd, "PublicationID", publicationID);

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

            return res;
        }

        public TagData GetTag(int tagID)
        {
            TagData data = null;

            if(tagID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("Tags_Select"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "TagID", tagID);

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

            return data;
        }

        public TagData GetTagByName(string name)
        {
            TagData data = null;

            if(!String.IsNullOrEmpty(name))
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("Tags_SelectByName"))
                {
                    DbCommandHelper.AddString(DataBase, cmd, "Name", name);

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

            return data;
        }

        public TagDataCollection GetAllTags(int? publicationID, DateTime? createdOnStart, DateTime? createdOnEnd)
        {
            TagDataCollection dataCollection = new TagDataCollection();

            using(DbCommand cmd = DataBase.GetStoredProcCommand("Tags_SelectAll"))
            {
                if(publicationID.HasValue)
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "PublicationID", publicationID.Value);
                }
                if(createdOnStart.HasValue)
                {
                    DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOnStart", createdOnStart.Value);
                }
                if(createdOnEnd.HasValue)
                {
                    DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOnEnd", createdOnEnd.Value);
                }

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

            return dataCollection;
        }

        public bool TagPublicationMappingExists(int tagID, int publicationID)
        {
            bool res = false;

            if(tagID > 0 && publicationID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("TagPublicationMap_Contains"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "TagID", tagID);
                    DbCommandHelper.AddInt32(DataBase, cmd, "PublicationID", publicationID);
                    DbCommandHelper.AddOutBoolean(DataBase, cmd, "Result");

                    DataBase.ExecuteNonQuery(cmd);

                    res = DbCommandHelper.GetBoolean(DataBase, cmd, "@Result");
                }
            }

            return res;
        }

        public bool UpdateTag(int tagID, string name, DateTime createdOn, DateTime updatedOn)
        {
            bool res = false;

            if(tagID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("Tags_Update"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "TagID", tagID);
                    DbCommandHelper.AddString(DataBase, cmd, "Name", name);
                    DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOn", createdOn);
                    DbCommandHelper.AddDateTime(DataBase, cmd, "UpdatedOn", updatedOn);

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

            return true;
        }

        public bool DeleteTag(int tagID)
        {
            bool res = false;

            if(tagID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("Tags_Delete"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "TagID", tagID);

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

            return res;
        }

        public bool DeleteTagPublicationMapping(int tagID, int publicationID)
        {
            bool res = false;

            if(tagID > 0 && publicationID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("TagPublicationMap_DeleteAll"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "TagID", tagID);
                    DbCommandHelper.AddInt32(DataBase, cmd, "PublicationID", publicationID);

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

            return res;
        }
        #endregion

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

            data.TagID = DataReaderHelper.ReadInt32(reader, "TagID");
            data.Name = DataReaderHelper.ReadString(reader, "Name");
            data.Count = DataReaderHelper.ReadInt32(reader, "Count");
            data.CreatedOn = DataReaderHelper.ReadDateTime(reader, "CreatedOn");
            data.UpdatedOn = DataReaderHelper.ReadDateTime(reader, "UpdatedOn");

            return data;
        }
        #endregion
    }
}