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.Utils;
using SiberTek.Xenta.Data.Entities.Collections;
using SiberTek.Xenta.Enums;

namespace SiberTek.Xenta.Data.Providers
{
    public class UserDataProvider : DataProviderBase, IUserDataProvider
    {
        #region Methods
        public bool InsertUser(Guid guid, string username, string firstName, string lastName, Gender gender, DateTime? birthDate, string email, string comment, string passwordHash, string passwordSalt, int languageID, int currencyID, int timeZoneID, int countryID, bool isActive, DateTime createdOn, DateTime updatedOn, out int userID)
        {
            bool res = false;
            userID = 0;

            using(DbCommand cmd = DataBase.GetStoredProcCommand("Users_Insert"))
            {
                DbCommandHelper.AddGuid(DataBase, cmd, "Guid", guid);
                DbCommandHelper.AddString(DataBase, cmd, "Username", username);
                DbCommandHelper.AddString(DataBase, cmd, "FirstName", firstName);
                DbCommandHelper.AddString(DataBase, cmd, "LastName", lastName);
                DbCommandHelper.AddInt32(DataBase, cmd, "Gender", (int)gender);
                if(birthDate.HasValue)
                {
                    DbCommandHelper.AddDateTime(DataBase, cmd, "BirthDate", birthDate.Value);
                }
                DbCommandHelper.AddString(DataBase, cmd, "Email", email);
                DbCommandHelper.AddString(DataBase, cmd, "Comment", comment);
                DbCommandHelper.AddString(DataBase, cmd, "PasswordHash", passwordHash);
                DbCommandHelper.AddString(DataBase, cmd, "PasswordSalt", passwordSalt);
                DbCommandHelper.AddInt32(DataBase, cmd, "LanguageID", languageID);
                DbCommandHelper.AddInt32(DataBase, cmd, "CountryID", countryID);
                DbCommandHelper.AddInt32(DataBase, cmd, "TimeZoneID", timeZoneID);
                DbCommandHelper.AddInt32(DataBase, cmd, "CurrencyID", currencyID);
                DbCommandHelper.AddBoolean(DataBase, cmd, "IsActive", isActive);
                DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOn", createdOn);
                DbCommandHelper.AddDateTime(DataBase, cmd, "UpdatedOn", updatedOn);
                DbCommandHelper.AddOutInt32(DataBase, cmd, "UserID");

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

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

            return res;
        }

        public UserData GetUser(int userID)
        {
            UserData data = null;

            if(userID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("Users_Select"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "UserID", userID);

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

            return data;
        }

        public UserData GetUserByEmail(string email)
        {
            UserData data = null;

            if(!String.IsNullOrEmpty(email))
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("Users_SelectByEmail"))
                {
                    DbCommandHelper.AddString(DataBase, cmd, "Email", email);

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

            return data;
        }

        public UserData GetUserByUsername(string username)
        {
            UserData data = null;

            if(!String.IsNullOrEmpty(username))
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("Users_SelectByUsername"))
                {
                    DbCommandHelper.AddString(DataBase, cmd, "Username", username);

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

            return data;
        }

        public UserData GetUserByGuid(Guid guid)
        {
            UserData data = null;

            using(DbCommand cmd = DataBase.GetStoredProcCommand("Users_SelectByGuid"))
            {
                DbCommandHelper.AddGuid(DataBase, cmd, "Guid", guid);

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

            return data;
        }

        public UserDataCollection GetAllUsers(string searchTerm, int? roleID, int? countryID, int? languageID, int? currencyID, int? timeZoneID, DateTime? createdOnStart, DateTime? createdOnEnd, bool showHidden, int startIndex, int count, out int totalCount)
        {
            UserDataCollection dataCollection = new UserDataCollection();

            using(DbCommand cmd = DataBase.GetStoredProcCommand("Users_SelectAll"))
            {
                DbCommandHelper.AddString(DataBase, cmd, "SearchTerm", searchTerm);
                if(roleID.HasValue)
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "RoleID", roleID.Value);
                }
                if(countryID.HasValue)
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "CountryID", countryID.Value);
                }
                if(languageID.HasValue)
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "LanguageID", languageID.Value);
                }
                if(currencyID.HasValue)
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "CurrencyID", currencyID.Value);
                }
                if(timeZoneID.HasValue)
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "TimeZoneID", timeZoneID.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.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 UpdateUser(int userID, Guid guid, string username, string firstName, string lastName, Gender gender, DateTime? birthDate, string email, string comment, string passwordHash, string passwordSalt, int languageID, int currencyID, int timeZoneID, int countryID, bool isActive, DateTime createdOn, DateTime updatedOn)
        {
            bool res = false;

            if(userID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("Users_Update"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "UserID", userID);
                    DbCommandHelper.AddGuid(DataBase, cmd, "Guid", guid);
                    DbCommandHelper.AddString(DataBase, cmd, "Username", username);
                    DbCommandHelper.AddString(DataBase, cmd, "FirstName", firstName);
                    DbCommandHelper.AddString(DataBase, cmd, "LastName", lastName);
                    DbCommandHelper.AddInt32(DataBase, cmd, "Gender", (int)gender);
                    if(birthDate.HasValue)
                    {
                        DbCommandHelper.AddDateTime(DataBase, cmd, "BirthDate", birthDate.Value);
                    }
                    DbCommandHelper.AddString(DataBase, cmd, "Email", email);
                    DbCommandHelper.AddString(DataBase, cmd, "Comment", comment);
                    DbCommandHelper.AddString(DataBase, cmd, "PasswordHash", passwordHash);
                    DbCommandHelper.AddString(DataBase, cmd, "PasswordSalt", passwordSalt);
                    DbCommandHelper.AddInt32(DataBase, cmd, "LanguageID", languageID);
                    DbCommandHelper.AddInt32(DataBase, cmd, "CountryID", countryID);
                    DbCommandHelper.AddInt32(DataBase, cmd, "TimeZoneID", timeZoneID);
                    DbCommandHelper.AddInt32(DataBase, cmd, "CurrencyID", currencyID);
                    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 DeleteUser(int userID)
        {
            bool res = false;

            if(userID > 0)
            {
                using(DbCommand cmd = DataBase.GetStoredProcCommand("Users_Delete"))
                {
                    DbCommandHelper.AddInt32(DataBase, cmd, "UserID", userID);

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

            return res;
        }
        #endregion

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

            data.UserID = DataReaderHelper.ReadInt32(reader, "UserID");
            data.Guid = DataReaderHelper.ReadGuid(reader, "Guid");
            data.Username = DataReaderHelper.ReadString(reader, "Username");
            data.Email = DataReaderHelper.ReadString(reader, "Email");
            data.FirstName = DataReaderHelper.ReadString(reader, "FirstName");
            data.LastName = DataReaderHelper.ReadString(reader, "LastName");
            data.Gender = (Gender)DataReaderHelper.ReadInt32(reader, "Gender");
            data.BirthDate = DataReaderHelper.ReadNullableDateTime(reader, "BirthDate");
            data.Comment = DataReaderHelper.ReadString(reader, "Comment");
            data.PasswordHash = DataReaderHelper.ReadString(reader, "PasswordHash");
            data.PasswordSalt = DataReaderHelper.ReadString(reader, "PasswordSalt");
            data.LanguageID = DataReaderHelper.ReadInt32(reader, "LanguageID");
            data.CountryID = DataReaderHelper.ReadInt32(reader, "CountryID");
            data.TimeZoneID = DataReaderHelper.ReadInt32(reader, "TimeZoneID");
            data.CurrencyID = DataReaderHelper.ReadInt32(reader, "CurrencyID");
            data.IsActive = DataReaderHelper.ReadBoolean(reader, "IsActive");
            data.CreatedOn = DataReaderHelper.ReadDateTime(reader, "CreatedOn");
            data.UpdatedOn = DataReaderHelper.ReadDateTime(reader, "UpdatedOn");

            return data;
        }
        #endregion
    }
}