using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using ASPNET.StarterKit.BusinessLogicLayer;
using System.Runtime.Caching;
using System.IO;
using System.Collections;

namespace ASPNET.StarterKit.DataAccessLayer
{
    public class SQLDataAccess : DataAccess
    {
        
        /*** DELEGATE ***/

        private delegate void TGenerateListFromReader<T>(SqlDataReader returnData, ref List<T> tempList);
        private delegate void TGenerateFromReader<T>(SqlDataReader returnData, ref T obj);
        private delegate void TGenerateObjectFromReader<T>(SqlDataReader returnData, ref T obj,string colname);
        /*****************************  BASE CLASS IMPLEMENTATION *****************************/
        #region categories
        /***  CATEGORY ***/
        private const string SP_CATEGORY_CREATE = "aspnet_starterkits_CreateNewCategory";
        private const string SP_CATEGORY_DELETE = "aspnet_starterkits_DeleteCategory";
        private const string SP_CATEGORY_GETALLCATEGORIES = "aspnet_starterkits_GetAllCategories";
        private const string SP_CATEGORY_GETCATEGORYBYPROJECTID = "aspnet_starterkits_GetCategoriesByProjectId";
        private const string SP_CATEGORY_GETCATEGORYBYID = "aspnet_starterkits_GetCategoryById";
        private const string SP_CATEGORY_GETCATEGORYBYNAMEANDPROJECT = "aspnet_starterkits_GetCategoryByNameAndProjectId";
        private const string SP_CATEGORY_UPDATE = "aspnet_starterkits_UpdateCategories";



        public override int CreateNewCategory(Category newCategory)
        {
            if (newCategory == null)
                throw (new ArgumentNullException("newCategory"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@CategoryAbbreviation", SqlDbType.NText, 255, ParameterDirection.Input, newCategory.Abbreviation);
            AddParamToSQLCmd(sqlCmd, "@CategoryEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, newCategory.EstimateDuration);
            AddParamToSQLCmd(sqlCmd, "@CategoryName", SqlDbType.NText, 255, ParameterDirection.Input, newCategory.Name);
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, newCategory.ProjectId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_CREATE);
            ExecuteScalarCmd(sqlCmd);

            return ((int)sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override bool DeleteCategory(int categoryId)
        {
            if (categoryId <= DefaultValues.GetCategoryIdMinValue())
                throw (new ArgumentOutOfRangeException("categoryId"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@CategoryIdToDelete", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_DELETE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? true : false);
        }

        public override List<Category> GetAllCategories()
        {
            SqlCommand sqlCmd = new SqlCommand();

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETALLCATEGORIES);

            List<Category> categoryList = new List<Category>();

            TExecuteReaderCmd<Category>(sqlCmd, TGenerateCategoryListFromReader<Category>, ref categoryList);

            return categoryList;
        }

        public override Category GetCategoryByCategoryId(int Id)
        {
            if (Id <= DefaultValues.GetCategoryIdMinValue())
                throw (new ArgumentOutOfRangeException("Id"));


            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, Id);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYID);

            List<Category> categoryList = new List<Category>();

            TExecuteReaderCmd<Category>(sqlCmd, TGenerateCategoryListFromReader<Category>, ref categoryList);

            if (categoryList.Count > 0)
                return categoryList[0];
            else
                return null;

        }

        public override Category GetCategoryByCategoryNameandProjectId(string categoryName, int projectId)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
                throw (new ArgumentOutOfRangeException("Id"));


            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            AddParamToSQLCmd(sqlCmd, "@CategoryName", SqlDbType.NText, 255, ParameterDirection.Input, categoryName);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYNAMEANDPROJECT);

            List<Category> categoryList = new List<Category>();

            TExecuteReaderCmd<Category>(sqlCmd, TGenerateCategoryListFromReader<Category>, ref categoryList);

            if (categoryList.Count > 0)
                return categoryList[0];
            else
                return null;

        }



        public override List<Category> GetCategoriesByProjectId(int projectId)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
                throw (new ArgumentOutOfRangeException("projectId"));


            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYPROJECTID);

            List<Category> categoryList = new List<Category>();

            TExecuteReaderCmd<Category>(sqlCmd, TGenerateCategoryListFromReader<Category>, ref categoryList);


            return categoryList;
        }

        public override bool UpdateCategory(Category newCategory)
        {
            if (newCategory == null)
                throw (new ArgumentNullException("newCategory"));

            if (newCategory.Id <= DefaultValues.GetCategoryIdMinValue())
                throw (new ArgumentOutOfRangeException("newCategory.Id"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newCategory.Id);
            AddParamToSQLCmd(sqlCmd, "@CategoryAbbreviation", SqlDbType.NText, 255, ParameterDirection.Input, newCategory.Abbreviation);
            AddParamToSQLCmd(sqlCmd, "@CategoryEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, newCategory.EstimateDuration);
            AddParamToSQLCmd(sqlCmd, "@CategoryName", SqlDbType.NText, 255, ParameterDirection.Input, newCategory.Name);
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, newCategory.ProjectId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_UPDATE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? true : false);
        }
#endregion
        #region Projects
        /***  PROJECT ***/
        private const string SP_PROJECT_ADDUSERTOPROJECT = "aspnet_starterkits_AddUserToProject";
        private const string SP_PROJECT_CREATE = "aspnet_starterkits_CreateNewProject";
        private const string SP_PROJECT_DELETE = "aspnet_starterkits_DeleteProject";
        private const string SP_PROJECT_GETALLPROJECTS = "aspnet_starterkits_GetAllProjects";
        private const string SP_PROJECT_GETAPROJECTBYID = "aspnet_starterkits_GetProjectById";
        private const string SP_PROJECT_GETAPROJECTBYNUMBER = "aspnet_starterkits_GetProjectByNumber";
        private const string SP_PROJECT_GETAPROJECTBYNAME = "aspnet_starterkits_GetProjectByName";
        private const string SP_PROJECT_GETAPROJECTSBYMANAGERUSERNAME = "aspnet_starterkits_GetProjectByManagerUserName";
        private const string SP_PROJECT_GETPROJECTSBYYSERNAME = "aspnet_starterkits_GetProjectByUserName";
        private const string SP_PROJECT_GETPROJECTMEMBERS = "aspnet_starterkits_GetProjectMember";
        private const string SP_PROJECT_REMOVEUSERFROMPROJECT = "aspnet_starterkits_RemoveUserFromProject";
        private const string SP_PROJECT_UPDATE = "aspnet_starterkits_UpdateProject";

        public override bool AddUserToProject(int projectId, string userName)
        {
            if (userName == null || userName.Length == 0)
                throw (new ArgumentOutOfRangeException("userName"));

            if (projectId <= 0)
                throw (new ArgumentOutOfRangeException("projectId"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ResultValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParamToSQLCmd(sqlCmd, "@MemberUserName", SqlDbType.NText, 255, ParameterDirection.Input, userName);
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_ADDUSERTOPROJECT);
            ExecuteScalarCmd(sqlCmd);

            int resultValue = (int)sqlCmd.Parameters["@ResultValue"].Value;

            return (resultValue == 0 ? true : false);
        }


        public override int CreateNewProject(Project newProject)
        {
            if (newProject == null)
                throw (new ArgumentNullException("newProject"));


            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParamToSQLCmd(sqlCmd, "@ProjectCreatorUserName", SqlDbType.NText, 255, ParameterDirection.Input, newProject.CreatorUserName);
            AddParamToSQLCmd(sqlCmd, "@ProjectCompletionDate", SqlDbType.DateTime, 0, ParameterDirection.Input, newProject.CompletionDate);
            AddParamToSQLCmd(sqlCmd, "@ProjectDescription", SqlDbType.NText, 1000, ParameterDirection.Input, newProject.Description);
            AddParamToSQLCmd(sqlCmd, "@ProjectEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, newProject.EstimateDuration);
            AddParamToSQLCmd(sqlCmd, "@ProjectManagerUserName", SqlDbType.NText, 255, ParameterDirection.Input, newProject.ManagerUserName);
            AddParamToSQLCmd(sqlCmd, "@ProjectName", SqlDbType.NText, 255, ParameterDirection.Input, newProject.Name);
            AddParamToSQLCmd(sqlCmd, "@ProjectNumber", SqlDbType.NText, 255, ParameterDirection.Input, newProject.ProjectNumber);
            AddParamToSQLCmd(sqlCmd, "@InActive", SqlDbType.Bit, 0, ParameterDirection.Input, newProject.IsInActive);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_CREATE);
            ExecuteScalarCmd(sqlCmd);

            return ((int)sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override bool DeleteProject(int projectID)
        {
            if (projectID <= 0)
                throw (new ArgumentOutOfRangeException("projectID"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParamToSQLCmd(sqlCmd, "@ProjectIdToDelete", SqlDbType.Int, 0, ParameterDirection.Input, projectID);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_DELETE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;

            return (returnValue == 0 ? true : false);
        }

        public override List<Project> GetAllProjects()
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParamToSQLCmd(sqlCmd, "@StartDate", SqlDbType.DateTime, 0, ParameterDirection.Input, DBNull.Value );
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETALLPROJECTS);

            List<Project> prjList = new List<Project>();

            TExecuteReaderCmd<Project>(sqlCmd, TGenerateProjectListFromReader<Project>, ref prjList);

            return prjList;
        }
        public override List<Project> GetAllProjects(DateTime StartDate)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParamToSQLCmd(sqlCmd, "@StartDate", SqlDbType.DateTime, 0, ParameterDirection.Input, StartDate);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETALLPROJECTS);

            List<Project> prjList = new List<Project>();

            TExecuteReaderCmd<Project>(sqlCmd, TGenerateProjectListFromReader<Project>, ref prjList);

            return prjList;
        }
        
        public override Project GetProjectByName(string projectName,bool includeInactive)
        {
            if (string.IsNullOrEmpty(projectName))
                throw (new ArgumentOutOfRangeException("projectName"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ProjectName", SqlDbType.NVarChar, 255, ParameterDirection.Input, projectName);
            AddParamToSQLCmd(sqlCmd, "@IncludeInActive", SqlDbType.Bit, 255, ParameterDirection.Input, includeInactive);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETAPROJECTBYNAME);

            List<Project> prjList = new List<Project>();

            TExecuteReaderCmd<Project>(sqlCmd, TGenerateProjectListFromReader<Project>, ref prjList);

            if (prjList.Count > 0)
                return prjList[0];
            else
                return null;
        }
        public override Project GetProjectByNumber(string projectNbr, bool includeInactive)
        {
            if (string.IsNullOrEmpty(projectNbr))
                throw (new ArgumentOutOfRangeException("projectNbr"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ProjectNumber", SqlDbType.NVarChar, 255, ParameterDirection.Input, projectNbr);
            AddParamToSQLCmd(sqlCmd, "@IncludeInActive", SqlDbType.Bit, 255, ParameterDirection.Input, includeInactive);


            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETAPROJECTBYNUMBER);

            List<Project> prjList = new List<Project>();

            TExecuteReaderCmd<Project>(sqlCmd, TGenerateProjectListFromReader<Project>, ref prjList);

            if (prjList.Count > 0)
                return prjList[0];
            else
                return null;
        }
        public override Project GetProjectById(int projectId,bool includeInactive)
        {
            if (projectId <= 0)
                throw (new ArgumentOutOfRangeException("projectId"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            AddParamToSQLCmd(sqlCmd, "@IncludeInActive", SqlDbType.Bit, 255, ParameterDirection.Input, includeInactive);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETAPROJECTBYID);

            List<Project> prjList = new List<Project>();

            TExecuteReaderCmd<Project>(sqlCmd, TGenerateProjectListFromReader<Project>, ref prjList);

            if (prjList.Count > 0)
                return prjList[0];
            else
                return null;
        }

        public override List<Project> GetProjectsByManagerUserName(string userName)
        {
            if (userName == null || userName.Length == 0)
                throw (new ArgumentOutOfRangeException("userName"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ProjectManagerUserName", SqlDbType.NText, 256, ParameterDirection.Input, userName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETAPROJECTSBYMANAGERUSERNAME);

            List<Project> prjList = new List<Project>();

            TExecuteReaderCmd<Project>(sqlCmd, TGenerateProjectListFromReader<Project>, ref prjList);

            return prjList;


        }

        public override List<string> GetProjectMembers(int Id)
        {
            if (Id <= 0)
                throw (new ArgumentOutOfRangeException("Id"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, Id);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETPROJECTMEMBERS);

            List<string> userList = new List<string>();

            TExecuteReaderCmd<string>(sqlCmd, TGenerateUsertListFromReader<string>, ref userList);

            return userList;

        }

        public override List<Project> GetProjectsByUserName(string userName)
        {
            if (userName == null || userName.Length == 0)
                throw (new ArgumentOutOfRangeException("userName"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 256, ParameterDirection.Input, userName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETPROJECTSBYYSERNAME);

            List<Project> prjList = new List<Project>();

            TExecuteReaderCmd<Project>(sqlCmd, TGenerateProjectListFromReader<Project>, ref prjList);

            return prjList;


            //      return (new List<Project>());

        }


        public override bool RemoveUserFromProject(int projectId, string userName)
        {
            if (String.IsNullOrEmpty(userName))
                throw (new ArgumentOutOfRangeException("userName"));
            if (projectId <= 0)
                throw (new ArgumentOutOfRangeException("projectId"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ResultValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@userName", SqlDbType.NVarChar, 0, ParameterDirection.Input, userName);
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_REMOVEUSERFROMPROJECT);
            ExecuteScalarCmd(sqlCmd);

            int resultValue = (int)sqlCmd.Parameters["@ResultValue"].Value;

            return (resultValue == 0 ? true : false);
        }

        public override bool UpdateProject(Project projectToUpdate)
        {
            // validate input
            if (projectToUpdate == null)
                throw (new ArgumentNullException("projectToUpdate"));
            // validate input
            if (projectToUpdate.Id <= 0)
                throw (new ArgumentOutOfRangeException("projectToUpdate"));

            SqlCommand sqlCmd = new SqlCommand();
            // set the type of parameter to add a new project
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectToUpdate.Id);
            AddParamToSQLCmd(sqlCmd, "@ProjectCompletionDate", SqlDbType.DateTime, 0, ParameterDirection.Input, projectToUpdate.CompletionDate);
            AddParamToSQLCmd(sqlCmd, "@ProjectDescription", SqlDbType.NText, 1000, ParameterDirection.Input, projectToUpdate.Description);
            AddParamToSQLCmd(sqlCmd, "@ProjectEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, projectToUpdate.EstimateDuration);
            AddParamToSQLCmd(sqlCmd, "@ProjectManagerUserName", SqlDbType.NText, 256, ParameterDirection.Input, projectToUpdate.ManagerUserName);
            AddParamToSQLCmd(sqlCmd, "@ProjectName", SqlDbType.NText, 256, ParameterDirection.Input, projectToUpdate.Name);
            AddParamToSQLCmd(sqlCmd, "@ProjectNumber", SqlDbType.NText, 255, ParameterDirection.Input, projectToUpdate.ProjectNumber);
            AddParamToSQLCmd(sqlCmd, "@InActive", SqlDbType.Bit,0 , ParameterDirection.Input, projectToUpdate.IsInActive);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_UPDATE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;

            return (returnValue == 0 ? true : false);
        }
        #endregion
        #region Time Entry
        /*** TIME ENTRY ***/
        private string SP_TIMEENTRY_CREATE = "aspnet_starterkits_CreateNewTimeEntry";
        private string SP_TIMEENTRY_DELETE = "aspnet_starterkits_DeleteTimeEntry";
        private string SP_TIMEENTRY_GETALLTIMEENTRIES = "aspnet_starterkits_GetAllTimeEntries";
        private string SP_TIMEENTRY_GETALLTIMEENTRIESBYPROJECTID_USER = "aspnet_starterkits_GetAllTimeEntriesByProjectIdandUser";
        private string SP_TIMEENTRY_GETALLTIMEENTRIESBYPROJECTID_USER_DATE = "aspnet_starterkits_GetAllTimeEntriesByProjectIdandUserAndDate";
        private string SP_TIMEENTRY_GETALLTIMEENTRIESBYUSERNAMEANDDATE = "aspnet_starterkits_GetAllTimeEntriesByProjectIdandUserAndDate";
        private string SP_TIMEENTRY_UPDATE = "aspnet_starterkits_UpdateTimeEntry";
        private string SP_TIMEENTRY_GETTIMEENTRYBYID = "aspnet_starterkits_GetTimeEntryById";
        private string SP_TIMEENTRY_GETWEEKENDINGDATESBYID = "aspnet_starterkits_GetWeekEndingDatesForUserId";
        private string SP_TIMEENTRY_GETWEEKENDINGDATE = "aspnet_starterkits_GetWeekEndingDate";
        
        public override List<TimeEntry> GetAllUnSubmittedTimeEntries()
        {
            SqlCommand sqlCmd = new SqlCommand();

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETALLTIMEENTRIES);
            AddParamToSQLCmd(sqlCmd, "@IncludeSubmitted", SqlDbType.Bit, 255, ParameterDirection.Input, false);
            List<TimeEntry> timeEntryList = new List<TimeEntry>();

            TExecuteReaderCmd<TimeEntry>(sqlCmd, TGenerateTimeEntryListFromReader<TimeEntry>, ref timeEntryList);

            return timeEntryList;
        }
        public override List<WeekEnding> GetWeekEndingDates(string userName)
        {
            if (string.IsNullOrEmpty(userName))
                throw (new ArgumentNullException("userName"));
            SqlCommand sqlCmd = new SqlCommand();
            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 255, ParameterDirection.Input, userName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETWEEKENDINGDATESBYID);

            List<WeekEnding> timeEntryList = new List<WeekEnding>();

            TExecuteReaderCmd<WeekEnding>(sqlCmd, TGenerateWeekEndingListFromReader<WeekEnding>, ref timeEntryList);

            return timeEntryList;

        }
        public override int CreateNewTimeEntry(TimeEntry newTimeEntry)
        {

            if (newTimeEntry == null)
                throw (new ArgumentNullException("newTimeEntry"));


            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, newTimeEntry.ProjectId);
            AddParamToSQLCmd(sqlCmd, "@TimeEntryCreatorUserName", SqlDbType.NText, 255, ParameterDirection.Input, newTimeEntry.CreatorUserName);
            AddParamToSQLCmd(sqlCmd, "@TimeEntryDescription", SqlDbType.NText, 1000, ParameterDirection.Input, newTimeEntry.Description);
            AddParamToSQLCmd(sqlCmd, "@TimeEntryEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, newTimeEntry.Duration);
            AddParamToSQLCmd(sqlCmd, "@TimeEntryEnteredDate", SqlDbType.DateTime, 0, ParameterDirection.Input, newTimeEntry.ReportedDate);
            AddParamToSQLCmd(sqlCmd, "@TimeEntryUserName", SqlDbType.NText, 255, ParameterDirection.Input, newTimeEntry.UserName);
            AddParamToSQLCmd(sqlCmd, "@CreditName", SqlDbType.NText, 200, ParameterDirection.Input, newTimeEntry.CreditDepartment);
            AddParamToSQLCmd(sqlCmd, "@Rate", SqlDbType.Decimal, 0, ParameterDirection.Input, newTimeEntry.Rate);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_CREATE);

            ExecuteScalarCmd(sqlCmd);

            return ((int)sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override bool DeleteTimeEntry(int timeEntryId)
        {
            if (timeEntryId <= DefaultValues.GetTimeEntryIdMinValue())
                throw (new ArgumentOutOfRangeException("timeEntryId"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@TimeEntryIdToDelete", SqlDbType.Int, 0, ParameterDirection.Input, timeEntryId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_DELETE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;

            return (returnValue == 0 ? true : false);

        }

        public override List<TimeEntry> GetAllTimeEntries()
        {

            SqlCommand sqlCmd = new SqlCommand();

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETALLTIMEENTRIES);

            List<TimeEntry> timeEntryList = new List<TimeEntry>();

            TExecuteReaderCmd<TimeEntry>(sqlCmd, TGenerateTimeEntryListFromReader<TimeEntry>, ref timeEntryList);

            return timeEntryList;
        }

        public override List<TimeEntry> GetTimeEntries(int projectId, string userName)
        {
            if (projectId <= DefaultValues.GetTimeEntryIdMinValue())
                throw (new ArgumentOutOfRangeException("projectId"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            AddParamToSQLCmd(sqlCmd, "@TimeEntryUserName", SqlDbType.NText, 255, ParameterDirection.Input, userName);


            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETALLTIMEENTRIESBYPROJECTID_USER);

            List<TimeEntry> timeEntryList = new List<TimeEntry>();

            TExecuteReaderCmd<TimeEntry>(sqlCmd, TGenerateTimeEntryListFromReader<TimeEntry>, ref timeEntryList);

            return timeEntryList;
        }
        public override List<TimeEntry> GetTimeEntries( string userName,DateTime WeekStart)
        {
         

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@TimeEntryUserName", SqlDbType.NText, 255, ParameterDirection.Input, userName);
            AddParamToSQLCmd(sqlCmd, "@StartDate", SqlDbType.DateTime, 255, ParameterDirection.Input, WeekStart);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETALLTIMEENTRIESBYPROJECTID_USER_DATE);

            List<TimeEntry> timeEntryList = new List<TimeEntry>();

            TExecuteReaderCmd<TimeEntry>(sqlCmd, TGenerateTimeEntryListFromReader<TimeEntry>, ref timeEntryList);

            return timeEntryList;
        }
        public override List<DaysInPeriod> GetWeekEndingForStartDate(DateTime Start)
        {
            if (Start <= DefaultValues.GetDateTimeMinValue())
                throw (new ArgumentOutOfRangeException("StartDate"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@StartDate", SqlDbType.DateTime, 255, ParameterDirection.Input, Start);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETWEEKENDINGDATE);

            WeekEnding rs = new WeekEnding();

            TExecuteReaderCmd<WeekEnding>(sqlCmd, TGenerateWeekEndingFromReader<WeekEnding>, ref rs);
            if (rs == null)
                return new List<DaysInPeriod>();
            return rs.Days;
        }

        private void TExecuteReaderCmd<T>(SqlCommand sqlCmd, TGenerateFromReader<T> gcfr, ref T rs)
        {
            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            AddParamToSQLCmd(sqlCmd, "@ApplicationId", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, getApplicationId());


            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sqlCmd.Connection = cn;

                cn.Open();

                gcfr(sqlCmd.ExecuteReader(), ref rs);
            }

        }
        private void PExecuteReaderCmd<T>(SqlCommand sqlCmd, TGenerateListFromReader<T> gcfr, ref List<T> rs)
        {
            if (PrevuConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("PrevuConnectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            using (SqlConnection cn = new SqlConnection(this.PrevuConnectionString))
            {
                sqlCmd.Connection = cn;

                cn.Open();

                gcfr(sqlCmd.ExecuteReader(), ref rs);
            }

        }
        public override TimeEntry GetTimeEntryById(int timeEntryId)
        {
            if (timeEntryId <= 0)
                throw (new ArgumentOutOfRangeException("timeEntryId"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@TimeEntryId", SqlDbType.Int, 0, ParameterDirection.Input, timeEntryId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETTIMEENTRYBYID);

            List<TimeEntry> timeEntryList = new List<TimeEntry>();


            TExecuteReaderCmd<TimeEntry>(sqlCmd, TGenerateTimeEntryListFromReader<TimeEntry>, ref timeEntryList);

            if (timeEntryList.Count > 0)
                return timeEntryList[0];
            else
                return null;

        }
        public override List<TimeEntry> GetTimeEntriesByUserNameForDuration(string userName,
                                                                            DateTime startingDate)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            
            AddParamToSQLCmd(sqlCmd, "@StartDate", SqlDbType.DateTime, 0, ParameterDirection.Input, startingDate);
            AddParamToSQLCmd(sqlCmd, "@TimeEntryUserName", SqlDbType.NText, 255, ParameterDirection.Input, userName);


            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETALLTIMEENTRIESBYUSERNAMEANDDATE);

            List<TimeEntry> timeEntryList = new List<TimeEntry>();

            TExecuteReaderCmd<TimeEntry>(sqlCmd, TGenerateTimeEntryListFromReader<TimeEntry>, ref timeEntryList);

            return timeEntryList;
        }
        public override List<TimeEntry> GetTimeEntriesByUserNameAndDates(string userName,
                                                                          DateTime startingDate, DateTime endDate)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
           
            AddParamToSQLCmd(sqlCmd, "@StartDate", SqlDbType.DateTime, 0, ParameterDirection.Input, startingDate);
            AddParamToSQLCmd(sqlCmd, "@TimeEntryUserName", SqlDbType.NText, 255, ParameterDirection.Input, userName);


            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETALLTIMEENTRIESBYUSERNAMEANDDATE);

            List<TimeEntry> timeEntryList = new List<TimeEntry>();

            TExecuteReaderCmd<TimeEntry>(sqlCmd, TGenerateTimeEntryListFromReader<TimeEntry>, ref timeEntryList);

            return timeEntryList;

        }

        public override bool UpdateTimeEntry(TimeEntry timeEntry)
        {
            if (timeEntry == null)
                throw (new ArgumentNullException("timeEntry"));


            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);


            AddParamToSQLCmd(sqlCmd, "@TimeEntryId", SqlDbType.Int, 0, ParameterDirection.Input, timeEntry.Id);
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, timeEntry.ProjectId);
            AddParamToSQLCmd(sqlCmd, "@TimeEntryDescription", SqlDbType.NText, 1000, ParameterDirection.Input, timeEntry.Description);
            AddParamToSQLCmd(sqlCmd, "@TimeEntryEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, timeEntry.Duration);
            AddParamToSQLCmd(sqlCmd, "@TimeEntryEnteredDate", SqlDbType.DateTime, 0, ParameterDirection.Input, timeEntry.ReportedDate);
            AddParamToSQLCmd(sqlCmd, "@TimeEntryUserName", SqlDbType.NText, 1000, ParameterDirection.Input, timeEntry.UserName);
            AddParamToSQLCmd(sqlCmd, "@CreditName", SqlDbType.NText, 200, ParameterDirection.Input, timeEntry.CreditDepartment);
            AddParamToSQLCmd(sqlCmd, "@Rate", SqlDbType.Decimal, 0, ParameterDirection.Input, timeEntry.Rate);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_UPDATE);
            ExecuteScalarCmd(sqlCmd);
            int resultValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;

            return (resultValue == 0 ? true : false);
        }

        /***  USER REPORT ***/
        private string SP_TIMEENTRY_GETUSERREPORT = "aspnet_starterkits_GetTimeEntryUserReport";
        private string SP_TIMEENTRY_GETUSERREPORTBYCATEGORY = "aspnet_starterkits_GetTimeEntryUserReportByCategoryId";

        public override List<UserReport> GetUserReportsByProjectId(int projectId)
        {

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETUSERREPORT);

            List<UserReport> userReport = new List<UserReport>();

            TExecuteReaderCmd<UserReport>(sqlCmd, TGenerateUserReportListFromReader<UserReport>, ref userReport);

            return userReport;
        }

        public override List<UserReport> GetUserReportsByCategoryId(int categoryId)
        {

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETUSERREPORTBYCATEGORY);

            List<UserReport> userReport = new List<UserReport>();

            TExecuteReaderCmd<UserReport>(sqlCmd, TGenerateUserReportListFromReader<UserReport>, ref userReport);

            return userReport;
        }
        #endregion
        #region User Reports
        /***  USER TOTAL DURATION REPORT ***/
        private string SP_TIMEENTRY_GETUSERREPORTBYUSER = "aspnet_starterkits_GetTimeEntryUserReportByUser";

        public override List<UserTotalDurationReport> GetUserReportsByUserName(string userName)
        {

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 256, ParameterDirection.Input, userName);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETUSERREPORTBYUSER);

            List<UserTotalDurationReport> userReport = new List<UserTotalDurationReport>();

            TExecuteReaderCmd<UserTotalDurationReport>(sqlCmd, TGenerateUserReportListFromReader<UserTotalDurationReport>, ref userReport);
            return userReport;
        }


        /*****************************  SQL HELPER METHODS *****************************/
        private void AddParamToSQLCmd(SqlCommand sqlCmd,
                                      string paramId,
                                      SqlDbType sqlType,
                                      int paramSize,
                                      ParameterDirection paramDirection,
                                      object paramvalue)
        {

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));
            if (paramId == string.Empty)
                throw (new ArgumentOutOfRangeException("paramId"));

            SqlParameter newSqlParam = new SqlParameter();
            newSqlParam.ParameterName = paramId;
            newSqlParam.SqlDbType = sqlType;
            newSqlParam.Direction = paramDirection;

            if (paramSize > 0)
                newSqlParam.Size = paramSize;

            if (paramvalue != null)
                newSqlParam.Value = paramvalue;

            sqlCmd.Parameters.Add(newSqlParam);
        }

        private void ExecuteScalarCmd(SqlCommand sqlCmd)
        {

            AddParamToSQLCmd(sqlCmd, "@ApplicationId", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, getApplicationId());


            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));
         
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sqlCmd.Connection = cn;
                cn.Open();
                sqlCmd.ExecuteScalar();
            }
        }

        private void SetCommandType(SqlCommand sqlCmd, CommandType cmdType, string cmdText)
        {
            sqlCmd.CommandType = cmdType;
            sqlCmd.CommandText = cmdText;
        }

        private void TExecuteReaderCmd<T>(SqlCommand sqlCmd, TGenerateListFromReader<T> gcfr, ref List<T> List)
        {
            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));
            

            AddParamToSQLCmd(sqlCmd, "@ApplicationId", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, getApplicationId());
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sqlCmd.Connection = cn;

                cn.Open();

                gcfr(sqlCmd.ExecuteReader(), ref List);
            }
        }
        private void TExecuteReaderCmd<T>(SqlCommand sqlCmd, TGenerateObjectFromReader<T> gcfr, ref T val,string colname)
        {
            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));


            AddParamToSQLCmd(sqlCmd, "@ApplicationId", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, getApplicationId());
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sqlCmd.Connection = cn;

                cn.Open();

                gcfr(sqlCmd.ExecuteReader(), ref val, colname);
            }
        }
        #endregion
        /*****************************  GENARATE List HELPER METHODS  *****************************/

        private void TGenerateProjectListFromReader<T>(SqlDataReader returnData, ref List<Project> prjList)
        {
            while (returnData.Read())
            {
                decimal actualDuration = 0;
                if (returnData["ProjectActualDuration"] != DBNull.Value)
                    actualDuration = Convert.ToDecimal(returnData["ProjectActualDuration"]);

                Project project = new Project(actualDuration, (string)returnData["ProjectCreatorDisplayName"], (DateTime)returnData["ProjectCompletionDate"], (DateTime)returnData["ProjectCreationDate"], (string)returnData["ProjectDescription"],
                                         (Decimal)returnData["ProjectEstimateDuration"], (int)returnData["ProjectId"], (string)returnData["ProjectManagerDisplayName"], (string)returnData["ProjectName"], (string) returnData["ProjectNumber"]);
                prjList.Add(project);
            }
        }

        private void TGenerateCategoryListFromReader<T>(SqlDataReader returnData, ref List<Category> categoryList)
        {
            while (returnData.Read())
            {
                decimal actualDuration = 0;
                if (returnData["CategoryActualDuration"] != DBNull.Value)
                    actualDuration = Convert.ToDecimal(returnData["CategoryActualDuration"]);

                Category category = new Category((string)returnData["CategoryAbbreviation"], actualDuration, (int)returnData["CategoryId"], (decimal)returnData["CategoryEstimateDuration"], (string)returnData["CategoryName"], (int)returnData["ProjectId"]);
                categoryList.Add(category);
            }
        }

        private void TGenerateTimeEntryListFromReader<T>(SqlDataReader returnData, ref List<TimeEntry> timeEntryList)
        {
            while (returnData.Read())
            {
                TimeEntry timeEntry = new TimeEntry((string)returnData["TimeEntryCreatorDisplayName"], (int)returnData["ProjectId"], (DateTime)returnData["TimeEntryCreated"], (string)returnData["TimeEntryDescription"],
                                                (Decimal)returnData["TimeEntryDuration"], (int)returnData["TimeEntryId"], (DateTime)returnData["TimeEntryDate"], (string)returnData["TimeEntryUserName"],(string)returnData["CreditName"],(decimal)returnData["Rate"]);
                timeEntryList.Add(timeEntry);
            }
        }
        private void TGenerateWeekEndingFromReader<T>(SqlDataReader returnData, ref WeekEnding weekEnding)
        {
            while (returnData.Read())
            {
                weekEnding = getWeekEnding(returnData);
                return;
            }
        }
        private void TGenerateUsertListFromReader<T>(SqlDataReader returnData, ref List<string> userList)
        {
            while (returnData.Read())
            {
                string userName = (string)returnData["UserName"];
                userList.Add(userName);
            }
        }

        private void TGenerateUserReportListFromReader<T>(SqlDataReader returnData, ref List<UserReport> userReportList)
        {
            while (returnData.Read())
            {
                UserReport userReport = new UserReport((decimal)returnData["duration"], (int)returnData["CategoryId"], (string)returnData["UserName"]);
                userReportList.Add(userReport);
            }
        }

        private void TGenerateUserReportListFromReader<T>(SqlDataReader returnData, ref List<UserTotalDurationReport> userReportList)
        {
            while (returnData.Read())
            {
                decimal totalDuration = 0;
                if (returnData["TotalDuration"] != DBNull.Value)
                    totalDuration = (decimal)returnData["TotalDuration"];
                UserTotalDurationReport userReport = new UserTotalDurationReport(totalDuration, (string)returnData["UserName"]);
                userReportList.Add(userReport);
            }
        }
        private void TGenerateWeekEndingListFromReader<T>(SqlDataReader returnData, ref List<WeekEnding> WeekEndingList)
        {
            while (returnData.Read())
            {
                WeekEnding we=getWeekEnding(returnData);
                if (we != null)
                    WeekEndingList.Add(we);
            }
        }
        private WeekEnding getWeekEnding(SqlDataReader reader)
        {
            DateTime weekstart = (DateTime)reader["StartDate"];
            DateTime weekend = (DateTime)reader["EndDate"];
            DateTime dtTo = weekend;
            DateTime dtFrom = weekstart;
            List<DaysInPeriod> days = new List<DaysInPeriod>();
            while (dtTo >= dtFrom)
            {

                days.Add(new DaysInPeriod()
                {
                    Name = dtFrom.ToString("dddd"),
                    DayIndex = (int)dtFrom.DayOfWeek
                }
                    );
                dtFrom = dtFrom.AddDays(1);
            }
            WeekEnding  weekEnding = new WeekEnding()
            {
                WeekStart = weekstart,
                WeekEnd = weekend,
                Days = days
            };
            return weekEnding;
        }
        #region import projects
        private string PRV_SP_GETPROJSFORIMPORT = "GetProjectsForImport";
        private string PRV_SP_CACHEIMPORTDATA = "aspnet_Import_CacheImportData";

        public override List<ImportProject> ImportProjects()
        { 
            SqlCommand sqlCmd = new SqlCommand();

             
            SetCommandType(sqlCmd, CommandType.StoredProcedure, PRV_SP_GETPROJSFORIMPORT);

            List<ImportProject> importProjects = new List<ImportProject>();
            PExecuteReaderCmd<ImportProject>(sqlCmd, PGenerateImportProjectFromReader<ImportProject>, ref importProjects);
            if (importProjects == null)
                return new List<ImportProject>();
            return importProjects;
        }
        public override bool CacheImportFile(string filename, string type, int size, byte[] image,string username)
        {
            if (string.IsNullOrEmpty(filename))
                throw (new ArgumentNullException("setting"));

            if (string.IsNullOrEmpty(type))
                throw (new ArgumentNullException("type"));

            if (string.IsNullOrEmpty(username))
                throw (new ArgumentNullException("username"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParamToSQLCmd(sqlCmd, "@FileName", SqlDbType.NText, 255, ParameterDirection.Input, filename);
            AddParamToSQLCmd(sqlCmd, "@FileType", SqlDbType.NText, 255, ParameterDirection.Input, type);
            AddParamToSQLCmd(sqlCmd, "@size", SqlDbType.Int, 0, ParameterDirection.Input, size);
            AddParamToSQLCmd(sqlCmd, "@Image", SqlDbType.VarBinary, image.Length, ParameterDirection.Input, image);
            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 255, ParameterDirection.Input, username);


            SetCommandType(sqlCmd, CommandType.StoredProcedure, PRV_SP_CACHEIMPORTDATA);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue > 0 ? true : false);
        }


        private void PGenerateImportProjectFromReader<T>(SqlDataReader returnData, ref List<ImportProject> importProjects)
        {
            
            while (returnData.Read())
            {
              string projectName= returnData["Name"].ToString();
              string owner= returnData["ProjectOwner"].ToString();
                string projectNbr = returnData["ProjectNumber"].ToString();
                bool inActive = (bool)returnData["IsInActive"];
                ImportProject p=importProjects.Find(x => x.Name == projectName);
              if (p == null)
                {
                    p = new ImportProject(projectNbr,owner, owner, projectName, inActive)
                    {
                        Description = (string)returnData["Details"],
                        CompletionDate = (DateTime)returnData["Deadline"],
                                             };
                    p.AddUser(returnData["Email"].ToString());
                    importProjects.Add(p);
                }
                else
                {
                    p.AddUser(returnData["Email"].ToString());
                }
            }
        }
        #endregion
        #region Settings and profiles
        private string SP_PROFILE_CREATE = "aspnet_starterkits_CreateSetting";
        private string SP_PROFILE_DELETE = "aspnet_starterkits_DeleteSetting";
        private string SP_PROFILE_GETALLSETTINGS = "aspnet_starterkits_GetAllSettings";
        private string SP_PROFILE_GETSETTINGS_BY_SECTION = "aspnet_starterkits_GetSettingsBySection";
        private string SP_PROFILE_UPDATESETTING = "aspnet_starterkits_UpdateSetting";
        private string SP_USER_GETUSER_BYEMAIL = "aspnet_Membership_GetUserByEmail";
        public override List<Profiles> GetAllSecurityLevels()
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@Section", SqlDbType.NText, 100, ParameterDirection.Input, DefaultValues.GetSecurityLevelKey());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROFILE_GETSETTINGS_BY_SECTION);

            List<Profiles> settings = new List<Profiles>();

            TExecuteReaderCmd<Profiles>(sqlCmd, TGenerateSettingsListFromReader<Profiles>, ref settings);
            return settings;
        }
        public override List<Profiles> GetAllSettings()
        {
            SqlCommand sqlCmd = new SqlCommand();

            
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROFILE_GETALLSETTINGS);

            List<Profiles> settings = new List<Profiles>();

            TExecuteReaderCmd<Profiles>(sqlCmd, TGenerateSettingsListFromReader<Profiles>, ref settings);
            return settings;
        }
        public override bool UpdateSetting(string setting,string value,int settingId)
        {
            if (string.IsNullOrEmpty(setting))
                throw (new ArgumentNullException("setting"));

            if (settingId <= DefaultValues.GetProfileIdMinValue())
                throw (new ArgumentOutOfRangeException("settingId"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParamToSQLCmd(sqlCmd, "@SettingId", SqlDbType.Int, 0, ParameterDirection.Input, settingId);
            AddParamToSQLCmd(sqlCmd, "@Setting", SqlDbType.NText, 255, ParameterDirection.Input, setting);
            AddParamToSQLCmd(sqlCmd, "@Value", SqlDbType.NText, 255, ParameterDirection.Input, value);


            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROFILE_UPDATESETTING);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? true : false);
        }
        public override int AddSetting(Profiles newProfile)
        {

            if (newProfile == null)
                throw (new ArgumentNullException("newProfile"));

            if (newProfile.Id > DefaultValues.GetProfileIdMinValue())
                throw (new ArgumentOutOfRangeException("newProfile.Id"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

             
            AddParamToSQLCmd(sqlCmd, "@Setting", SqlDbType.NText, 255, ParameterDirection.Input, newProfile.Setting);
            AddParamToSQLCmd(sqlCmd, "@Value", SqlDbType.NText, 255, ParameterDirection.Input, newProfile.Value);
            AddParamToSQLCmd(sqlCmd, "@CanEdit", SqlDbType.Bit, 0, ParameterDirection.Input, newProfile.CanEdit);
            AddParamToSQLCmd(sqlCmd, "@Description", SqlDbType.NText, 255, ParameterDirection.Input, newProfile.SettingDescription);
            AddParamToSQLCmd(sqlCmd, "@SettingType", SqlDbType.NText, 255, ParameterDirection.Input, newProfile.SettingType);
            AddParamToSQLCmd(sqlCmd, "@Section", SqlDbType.NText, 255, ParameterDirection.Input, newProfile.SettingSection);


            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROFILE_CREATE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }
        public override bool DeleteProfile(int id)
        {

            if (id <= DefaultValues.GetProfileIdMinValue())
                throw (new ArgumentNullException("Setting.id"));
         
            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParamToSQLCmd(sqlCmd, "@SettingId", SqlDbType.Int, 0, ParameterDirection.Input, id);
           

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROFILE_DELETE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? true : false);
        }
        private void TGenerateSettingsListFromReader<T>(SqlDataReader returnData, ref List<Profiles> settingsList)
        {
            while (returnData.Read())
            {
                Profiles p = new Profiles((string)returnData["Setting"], (string)returnData["Value"], (int)returnData["Id"],(string)returnData["SettingDescription"],
                    (string)returnData["SettingType"],(bool)returnData["CanEdit"],(string)returnData["Section"]);
                settingsList.Add(p);
            }
        }


        #endregion
        #region Application Info

        private string SP_APPINFO_GETID = "aspnet_GetAppIdFromAppName";

        private Guid getApplicationId()
        {
            ObjectCache cache = MemoryCache.Default;

            string aID = cache["ApplicationId"] as string;
            if (aID == null)
            {
                string appName = cache["ApplicationName"] as string;
                if (string.IsNullOrEmpty(appName))
                    throw (new ArgumentOutOfRangeException("ApplicationID"));
                Guid appid = GetAppID(appName);
                cache["ApplicationId"] = appid.ToString();
                return appid;
            }
            return Guid.Parse(aID);
        }

        private Guid GetAppID(string name)
        {
            SqlCommand sqlCmd = new SqlCommand();


            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_APPINFO_GETID);
            AddParamToSQLCmd(sqlCmd, "@AppName", SqlDbType.NText, 255, ParameterDirection.Input, name);


            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sqlCmd.Connection = cn;

                cn.Open();

                SqlDataReader r = sqlCmd.ExecuteReader();
                while (r.Read())
                {
                    Guid AppId = (Guid)r["ApplicationId"];
                    return AppId;
                }

            }
            return Guid.Empty;
        }

        #endregion
        #region User profile
        private string SP_USER_GETUSER = "aspnet_Membership_GetUserByName";
        private string SP_USER_UPDATE = "aspnet_Membership_UpdateUser";
        private string SP_USER_GETUSER_BYID = "aspnet_Membership_GetUserByUserId";
        private string SP_USER_CHANGEPASSWORD = "aspnet_Membership_ChangePassword";
        private string SP_USER_CREATE = "aspnet_Membership_CreateUser";
        private string SP_USER_REGISTER= "aspnet_Membership_RegUser";
        private string SP_USER_DELETE = "aspnet_Membership_DeleteUser";
        private string SP_USER_GETALLUSERS = "aspnet_Membership_GetAllUsers";
        private string SP_USER_UPDATE_FAILURECOUNT = "aspnet_Membership_UpdateFailureCount";
        private string SP_USER_UNLOCK_USER = "aspnet_Membership_UnlockUser";
        private string SP_USER_CHANGE_PASSWORD_QUESTION = "aspnet_Membership_ChangePasswordQuestionAndAnswer";
        public override MembershipAppUser GetUser(string userName, bool online)
        {

            if (string.IsNullOrEmpty(userName))
                throw (new ArgumentOutOfRangeException("UserName"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 255, ParameterDirection.Input, userName);
            AddParamToSQLCmd(sqlCmd, "@CurrentTimeUtc", SqlDbType.DateTime, 255, ParameterDirection.Input, DateTime.Now);
            AddParamToSQLCmd(sqlCmd, "@UpdateLastActivity", SqlDbType.Bit, 0, ParameterDirection.Input, online);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_USER_GETUSER);

            List<MembershipAppUser> users = new List<MembershipAppUser>();

            TExecuteReaderCmd<MembershipAppUser>(sqlCmd, TGenerateUserListFromReader<MembershipAppUser>, ref users);
            if (users.Count > 0)
                return users[0];
            return null;
        }

       
        public override int updateUser(MembershipAppUser u )
        {
            if (u == null)
                throw (new ArgumentNullException("Users"));

            if (u.UserID  == Guid.Empty)
                throw (new ArgumentOutOfRangeException("UserID"));

            SqlCommand sqlCmd = new SqlCommand();
            //this flag tells the sproc to make sure we have a unique email address. 
            //this is important because resource updates that get pushed to prevu 
            // use email as the key that link the two.
            int uniqueEmail = 1;
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);


            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 255, ParameterDirection.Input, u.UserID);
            AddParamToSQLCmd(sqlCmd, "@Email", SqlDbType.NText, 256, ParameterDirection.Input, u.Email);
            AddParamToSQLCmd(sqlCmd, "@Comment", SqlDbType.NText, 500, ParameterDirection.Input, u.Comment);
            AddParamToSQLCmd(sqlCmd, "@IsApproved", SqlDbType.Bit, 0, ParameterDirection.Input, u.IsApproved);
            AddParamToSQLCmd(sqlCmd, "@LastLoginDate", SqlDbType.DateTime, 0, ParameterDirection.Input, u.LastLoginDate);
            AddParamToSQLCmd(sqlCmd, "@LastActivityDate", SqlDbType.DateTime, 0, ParameterDirection.Input, u.LastActivityDate);
            AddParamToSQLCmd(sqlCmd, "@UniqueEmail", SqlDbType.Int, 0, ParameterDirection.Input, uniqueEmail);
            AddParamToSQLCmd(sqlCmd, "@CurrentTimeUtc", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now);
            AddParamToSQLCmd(sqlCmd, "@SecurityLevel", SqlDbType.Int, 0, ParameterDirection.Input, u.SecurityLevel);
            AddParamToSQLCmd(sqlCmd, "@FirstName", SqlDbType.NText, 200, ParameterDirection.Input, u.FirstName);
            AddParamToSQLCmd(sqlCmd, "@LastName", SqlDbType.NText, 200, ParameterDirection.Input, u.LastName);
            AddParamToSQLCmd(sqlCmd, "@Title", SqlDbType.NText, 200, ParameterDirection.Input, u.Title);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_USER_UPDATE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;

        }
       

 
        public override MembershipAppUser GetUser(object providerUserKey, bool userIsOnline)
        {
            Guid UserID = Guid.Empty;
            if (providerUserKey is Guid)
                UserID =(Guid) providerUserKey;
            if (UserID == Guid.Empty )
                throw (new ArgumentOutOfRangeException("providerUserKey"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@UserId", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, UserID);
            AddParamToSQLCmd(sqlCmd, "@CurrentTimeUtc", SqlDbType.DateTime, 255, ParameterDirection.Input, DateTime.Now);
            AddParamToSQLCmd(sqlCmd, "@UpdateLastActivity", SqlDbType.Bit, 0, ParameterDirection.Input, userIsOnline);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_USER_GETUSER_BYID);

            List<MembershipAppUser> users = new List<MembershipAppUser>();

            TExecuteReaderCmd<MembershipAppUser>(sqlCmd, TGenerateUserListFromReader<MembershipAppUser>, ref users);
            if (users.Count > 0)
                return users[0];
            return null;
        }

        public override bool ChangePassword(string username, string password, string passwordAnswer)
        {
            if (string.IsNullOrEmpty(username))
                throw (new ArgumentNullException("username"));

            if (string.IsNullOrEmpty(password))
                throw (new ArgumentOutOfRangeException("password"));
            if (string.IsNullOrEmpty(passwordAnswer))
                throw (new ArgumentOutOfRangeException("passwordAnswer"));

            SqlCommand sqlCmd = new SqlCommand();
          
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 255, ParameterDirection.Input, username);
            AddParamToSQLCmd(sqlCmd, "@NewPassword", SqlDbType.NText, 256, ParameterDirection.Input, password);
            AddParamToSQLCmd(sqlCmd, "@PasswordAnswer", SqlDbType.NText, 500, ParameterDirection.Input, passwordAnswer);
            AddParamToSQLCmd(sqlCmd, "@CurrentTimeUtc", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_USER_CHANGEPASSWORD);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0);
        }

        public override int CreateUser(MembershipAppUser u)
        {
            if (u == null)
                throw (new ArgumentNullException("Users"));

            //if (u.UserID == Guid.Empty)
            //    throw (new ArgumentOutOfRangeException("UserID"));

            SqlCommand sqlCmd = new SqlCommand();
            //this flag tells the sproc to make sure we have a unique email address. 
            //this is important because resource updates that get pushed to prevu 
            // use email as the key that link the two.
            int uniqueEmail = 1;
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);


            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 255, ParameterDirection.Input, u.UserName);
            AddParamToSQLCmd(sqlCmd, "@Email", SqlDbType.NText, 256, ParameterDirection.Input, u.Email);
            AddParamToSQLCmd(sqlCmd, "@Password", SqlDbType.NText, 256, ParameterDirection.Input, u.Password);
            AddParamToSQLCmd(sqlCmd, "@PasswordSalt", SqlDbType.NText, 256, ParameterDirection.Input, u.PasswordSalt);
            AddParamToSQLCmd(sqlCmd, "@PasswordQuestion", SqlDbType.NText, 256, ParameterDirection.Input, u.PasswordQuestion);
            AddParamToSQLCmd(sqlCmd, "@PasswordAnswer", SqlDbType.NText, 256, ParameterDirection.Input, u.PasswordAnswer);
            AddParamToSQLCmd(sqlCmd, "@IsApproved", SqlDbType.Bit, 0, ParameterDirection.Input, u.IsApproved);
            AddParamToSQLCmd(sqlCmd, "@CurrentTimeUtc", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now);
            AddParamToSQLCmd(sqlCmd, "@CreateDate", SqlDbType.DateTime, 0, ParameterDirection.Input,u.CreationDate);
            AddParamToSQLCmd(sqlCmd, "@UniqueEmail", SqlDbType.Int, 0, ParameterDirection.Input, uniqueEmail);
            AddParamToSQLCmd(sqlCmd, "@PasswordFormat", SqlDbType.Int, 0, ParameterDirection.Input, u.PasswordFormatt);
            AddParamToSQLCmd(sqlCmd, "@FirstName", SqlDbType.NText, 200, ParameterDirection.Input, u.FirstName);
            AddParamToSQLCmd(sqlCmd, "@LastName", SqlDbType.NText, 200, ParameterDirection.Input, u.LastName);
            AddParamToSQLCmd(sqlCmd, "@Title", SqlDbType.NText, 200, ParameterDirection.Input, u.Title);
            AddParamToSQLCmd(sqlCmd, "@SecurityLevel", SqlDbType.Int, 0, ParameterDirection.Input, u.SecurityLevel);
            AddParamToSQLCmd(sqlCmd, "@UserId", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Output, Guid.NewGuid());
            AddParamToSQLCmd(sqlCmd, "@Rate", SqlDbType.Decimal, 0, ParameterDirection.Input, u.Rate);
            AddParamToSQLCmd(sqlCmd, "@CreditDepartment", SqlDbType.NText, 800, ParameterDirection.Input, u.CreditDepartment);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_USER_CREATE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public override bool Delete(string username, bool deleteAllRelatedData)
        {
            if (string.IsNullOrEmpty(username))
                throw (new ArgumentNullException("username"));

            
            SqlCommand sqlCmd = new SqlCommand();
            //this flag tells the sproc to make sure we have a unique email address. 
            //this is important because resource updates that get pushed to prevu 
            // use email as the key that link the two.
            int uniqueEmail = 1;
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);


            AddParamToSQLCmd(sqlCmd, " @UserName", SqlDbType.NText, 255, ParameterDirection.Input, username);
            AddParamToSQLCmd(sqlCmd, "@clearAllData", SqlDbType.Bit, 0, ParameterDirection.Input, deleteAllRelatedData);
           
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_USER_DELETE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0);
        }

        public override List<MembershipAppUser> GetAllUsers(int PageSize,int PageIndex)
        {
             
            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@PageIndex", SqlDbType.Int, 0, ParameterDirection.Input, PageIndex);
            AddParamToSQLCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, PageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_USER_GETALLUSERS);

            List<MembershipAppUser> users = new List<MembershipAppUser>();

            TExecuteReaderCmd<MembershipAppUser>(sqlCmd, TGenerateUserListFromReader<MembershipAppUser>, ref users);
          
            return users;
        }

        public override void UpdateFailureCount(string username, string type)
        {
            if (string.IsNullOrEmpty(username))
                throw (new ArgumentNullException("username"));
            if (string.IsNullOrEmpty(type))
                throw (new ArgumentNullException("type"));

            SqlCommand sqlCmd = new SqlCommand();
            AddParamToSQLCmd(sqlCmd, " @UserName", SqlDbType.NText, 255, ParameterDirection.Input, username);
            AddParamToSQLCmd(sqlCmd, "@PasswordFailureType", SqlDbType.NText, 200, ParameterDirection.Input, type);
            AddParamToSQLCmd(sqlCmd, "@CurrentTimeUtc", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_USER_UPDATE_FAILURECOUNT);
            ExecuteScalarCmd(sqlCmd);
        }

        public override string GetUserByEmail(string email)
        {
            if (string.IsNullOrEmpty(email))
                throw (new ArgumentOutOfRangeException("email"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@email", SqlDbType.NText, 255, ParameterDirection.Input, email);
           
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_USER_GETUSER_BYEMAIL);

            string username = string.Empty;

            TExecuteReaderCmd<string>(sqlCmd, TGetObjectFromReader<string>, ref username, "UserName");
            return username;
        }

        public override bool ResetPassword( string pwd)
        {
            throw new NotImplementedException();
        }

        public override bool UnlockUser(string userName)
        {
            

                if (string.IsNullOrEmpty(userName))
                throw (new ArgumentNullException("Users"));

        
            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);


            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 255, ParameterDirection.Input, userName);
           

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_USER_UNLOCK_USER);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue > 0);
        }



   

        public override bool RegisterUser(string UserName, string pwd, string salt,string question,string answer,bool isApproved,
            DateTime CreateDate, int PwdFormat,Guid UserId)
        {
            if (string.IsNullOrEmpty(UserName))
                throw (new ArgumentNullException("UserName"));

            if (UserId == Guid.Empty)
                throw (new ArgumentOutOfRangeException("UserId"));

            if (string.IsNullOrEmpty(pwd))
                throw (new ArgumentNullException("pwd"));

            if (string.IsNullOrEmpty(question))
                throw (new ArgumentNullException("question"));

            if (string.IsNullOrEmpty(answer))
                throw (new ArgumentNullException("answer"));

            if (salt == null)
                salt = "";


            SqlCommand sqlCmd = new SqlCommand();
            //this flag tells the sproc to make sure we have a unique email address. 
            //this is important because resource updates that get pushed to prevu 
            // use email as the key that link the two.
            int uniqueEmail = 1;
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);


            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 255, ParameterDirection.Input, UserName);
            AddParamToSQLCmd(sqlCmd, "@Password", SqlDbType.NText, 256, ParameterDirection.Input, pwd);
            AddParamToSQLCmd(sqlCmd, "@PasswordSalt", SqlDbType.NText, 256, ParameterDirection.Input, salt);
            AddParamToSQLCmd(sqlCmd, "@PasswordQuestion", SqlDbType.NText, 256, ParameterDirection.Input, question);
            AddParamToSQLCmd(sqlCmd, "@PasswordAnswer", SqlDbType.NText, 256, ParameterDirection.Input, answer);
            AddParamToSQLCmd(sqlCmd, "@IsApproved", SqlDbType.Bit, 0, ParameterDirection.Input, isApproved);
            AddParamToSQLCmd(sqlCmd, "@CurrentTimeUtc", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now);
            AddParamToSQLCmd(sqlCmd, "@CreateDate", SqlDbType.DateTime, 0, ParameterDirection.Input, CreateDate);
            AddParamToSQLCmd(sqlCmd, "@UniqueEmail", SqlDbType.Int, 0, ParameterDirection.Input, uniqueEmail);
            AddParamToSQLCmd(sqlCmd, "@PasswordFormat", SqlDbType.Int, 0, ParameterDirection.Input, PwdFormat);
           
            AddParamToSQLCmd(sqlCmd, "@UserId", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, UserId);
           
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_USER_REGISTER);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0);
        }

        public override bool ChangePasswordQuestionAndAnswer(string username, string newPasswordQuestion, string newPasswordAnswer)
        {

            if (string.IsNullOrEmpty(username))
                throw (new ArgumentNullException("username"));


            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);


            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 255, ParameterDirection.Input, username);
            AddParamToSQLCmd(sqlCmd, "@NewPasswordQuestion", SqlDbType.NText, 255, ParameterDirection.Input, newPasswordQuestion);
            AddParamToSQLCmd(sqlCmd, "@NewPasswordAnswer", SqlDbType.NText, 255, ParameterDirection.Input, newPasswordAnswer);


            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_USER_CHANGE_PASSWORD_QUESTION);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue > 0);
        }

        private void TGenerateUserListFromReader<T>(SqlDataReader returnData, ref List<MembershipAppUser> userList)
        {
            
            while (returnData.Read())
            {
                MembershipAppUser u = new MembershipAppUser( DefaultValues.GetProviderName(), returnData["UserName"] as string,
                  (object) returnData["UserId"], returnData["Email"] as string,
                  returnData["PasswordQuestion"] as string, returnData["Comment"] as string, (bool) returnData["IsApproved"],
                 (bool) returnData["IsLockedOut"], (DateTime) returnData["CreateDate"], (DateTime) returnData["LastLoginDate"],
                 ((returnData["LastActivityDate"] == DBNull.Value) ? DefaultValues.GetDefaultStartDate() : (DateTime) returnData["LastActivityDate"]),
                (DateTime) ((returnData["LastPasswordChangedDate"] == DBNull.Value) ? DefaultValues.GetDefaultStartDate() : returnData["LastPasswordChangedDate"]),
                (DateTime) ((returnData["LastLockoutDate"] == DBNull.Value) ? DefaultValues.GetDefaultStartDate() : returnData["LastLockoutDate"]),
                 returnData["FirstName"] as string, returnData["LastName"] as string,
                  (int) returnData["SecurityLevel"], returnData["PasswordAnswer"] as string,
                  returnData["Title"] as string, (Guid) returnData["UserId"], returnData["Password"] as string, returnData["PasswordSalt"] as string,
                  (int) returnData["PasswordFormat"], (decimal) returnData["rate"], returnData["CreditDepartment"] as string, returnData["SecurityLevelName"] as string);
                userList.Add(u);
            }
        }
        private void TGetObjectFromReader<T>(SqlDataReader returnData, ref T value, string colname)
        {

            while (returnData.Read())
            {
                value = (T) returnData[colname];
                break;
            }
            
        }
        #endregion
        #region teams
        private static string SP_TEAM_DELETE = "aspnet_Team_DeleteTeam";
        private static string SP_TEAM_CREATE = "aspnet_Team_CreateTeam";
        private static string SP_TEAM_UPDATE = "aspnet_Team_UpdateTeam";
        private static string SP_TEAM_ADDUSER = "aspnet_Team_AddUser";
        private static string SP_TEAM_GETALL = "aspnet_Team_GetAll";
        private static string SP_TEAM_GETBYID = "aspnet_Team_GetTeamById";
        private static string SP_TEAM_GETBYNAME = "aspnet_Team_GetTeamByName";
        private static string SP_TEAM_GETMEMBERS = "aspnet_Team_GetTeamMembers";
        private static string SP_TEAM_GETTEAMSBYMANNAME = "aspnet_Team_GetTeamsByManagerName";
        private static string SP_TEAM_REMOVEUSER = "aspnet_Team_RemoveUser";
        private static string SP_TEAM_GETMEMBERSBY_MANNAME = "aspnet_Team_GetTeamMembersByManagerName";
        public override bool DeleteTeam(int teamID)
        {
            if (teamID <= DefaultValues.GetPriorityIdMinValue())
                throw (new ArgumentNullException("teamID"));


            SqlCommand sqlCmd = new SqlCommand();
          
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, " @TeamId", SqlDbType.Int, 0, ParameterDirection.Input, teamID);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TEAM_DELETE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0);
        }
        public override int CreateNewTeam(Team t)
        {
            if (t == null)
                throw (new ArgumentNullException("Team"));

             
            SqlCommand sqlCmd = new SqlCommand();
            
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);


            AddParamToSQLCmd(sqlCmd, "@TeamName", SqlDbType.NText, 255, ParameterDirection.Input, t.Name);
            AddParamToSQLCmd(sqlCmd, "@ManagerName", SqlDbType.NText, 256, ParameterDirection.Input, t.ManagerUserName);
        
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TEAM_CREATE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public override bool UpdateTeam(Team t)
        {
            if (t == null)
                throw (new ArgumentNullException("Team"));


            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParamToSQLCmd(sqlCmd, "@TeamId", SqlDbType.Int, 0, ParameterDirection.Input, t.Id);
            AddParamToSQLCmd(sqlCmd, "@TeamName", SqlDbType.NText, 255, ParameterDirection.Input, t.Name);
            AddParamToSQLCmd(sqlCmd, "@ManagerName", SqlDbType.NText, 256, ParameterDirection.Input, t.ManagerUserId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TEAM_UPDATE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0);
        }

        public override bool AddUserToTeam(int teamId, string userName, int role)
        {
            if (teamId <= DefaultValues.GetMinTeamID())
                throw (new ArgumentNullException("TeamId"));
            if (string.IsNullOrEmpty(userName))
                throw (new ArgumentNullException("userName"));
            SqlCommand sqlCmd = new SqlCommand();
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@TeamId", SqlDbType.Int, 0, ParameterDirection.Input, teamId);
            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 255, ParameterDirection.Input, userName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TEAM_ADDUSER);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0);
        }

        public override List<Team> GetAllTeams()
        {
            SqlCommand sqlCmd = new SqlCommand();

             
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TEAM_GETALL);

            List<Team> teams = new List<Team>();

            TExecuteReaderCmd<Team>(sqlCmd, TGenerateTeamListFromReader<Team>, ref teams);

            return teams;
        }

        public override Team GetTeamById(int teamID)
        {

            if (teamID <= DefaultValues.GetPriorityIdMinValue())
                throw (new ArgumentNullException("teamID"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, " @TeamId", SqlDbType.Int, 0, ParameterDirection.Input, teamID);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TEAM_GETBYID);

            List<Team> teams = new List<Team>();

            TExecuteReaderCmd<Team>(sqlCmd, TGenerateTeamListFromReader<Team>, ref teams);
            if (teams.Count > 0)
                return teams[0];

            return null;
           
        }

        public override Team GetTeamByName(string teamName)
        {
            if (string.IsNullOrEmpty(teamName))
                throw (new ArgumentNullException("teamName"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@TeamName", SqlDbType.NText, 200, ParameterDirection.Input, teamName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TEAM_GETBYNAME);

            List<Team> teams = new List<Team>();

            TExecuteReaderCmd<Team>(sqlCmd, TGenerateTeamListFromReader<Team>, ref teams);
            if (teams.Count > 0)
                return teams[0];

            return null;
        }

        public override List<MembershipAppUser> GetTeamMembers(int teamId)
        {
            
            if (teamId <= DefaultValues.GetMinTeamID())
                throw (new ArgumentOutOfRangeException("teamId"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@TeamId", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, teamId);
            
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TEAM_GETMEMBERS);

            List<MembershipAppUser> users = new List<MembershipAppUser>();

            TExecuteReaderCmd<MembershipAppUser>(sqlCmd, TGenerateUserListFromReader<MembershipAppUser>, ref users);
            
            return users;
        }
        public override List<MembershipAppUser> GetTeamMembersByManager(string managerName)
        {
            if (string.IsNullOrEmpty(managerName))
                throw (new ArgumentOutOfRangeException("managerName"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ManagerName", SqlDbType.NText, 255, ParameterDirection.Input, managerName);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TEAM_GETMEMBERSBY_MANNAME);

            List<MembershipAppUser> users = new List<MembershipAppUser>();

            TExecuteReaderCmd<MembershipAppUser>(sqlCmd, TGenerateUserListFromReader<MembershipAppUser>, ref users);

            return users;
        }
        public override List<Team> GetTeamsByManagerUserName(string username)
        {
            if (string.IsNullOrEmpty(username))
                throw (new ArgumentNullException("username"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ManagerName", SqlDbType.NText, 200, ParameterDirection.Input, username);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TEAM_GETTEAMSBYMANNAME);

            List<Team> teams = new List<Team>();

            TExecuteReaderCmd<Team>(sqlCmd, TGenerateTeamListFromReader<Team>, ref teams);
            return teams;
        }

        public override bool RemoveUserFromTeam(int teamId, string UserName)
        {
            if (teamId <= DefaultValues.GetMinTeamID())
                throw (new ArgumentNullException("TeamId"));
            if (string.IsNullOrEmpty(UserName))
                throw (new ArgumentNullException("UserName"));
            SqlCommand sqlCmd = new SqlCommand();
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@TeamId", SqlDbType.Int, 0, ParameterDirection.Input, teamId);
            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 255, ParameterDirection.Input, UserName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TEAM_REMOVEUSER);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0);
        }
        private void TGenerateTeamListFromReader<T>(SqlDataReader returnData, ref List<Team> teamList)
        {
            while (returnData.Read())
            {
                List<MembershipAppUser> users=this.GetTeamMembers((int) returnData["TeamId"]);
                MembershipAppUser m = this.GetUser((Guid) returnData["ManagerId"], false);
                Team t = new Team(returnData["Name"] as string, m, users, (int) returnData["TeamId"]);
                teamList.Add(t);
            }
        }
        #endregion
        #region AppUser

        private static string SP_APPUSER_GETBYEMAIL = "aspnet_user_GetUserByEmail";
        private static string SP_APPUSER_GETALL = "aspnet_user_GetAllUsers";
        private static string SP_APPUSER_GETBYID= "aspnet_user_GetUserById";
        private static string SP_APPUSER_GETBYNAME = "aspnet_Users_GetUserByUserName";
        private static string SP_APPUSER_CREATE = "aspnet_Users_CreateUser";
        private static string SP_APPUSER_UPDATE = "aspnet_Users_UpdateUser";

        public override List<AppUser> GetAllAppUsers()
        {
            SqlCommand sqlCmd = new SqlCommand();

          

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_APPUSER_GETALL);

            List<AppUser> users = new List<AppUser>();

            TExecuteReaderCmd<AppUser>(sqlCmd, TGenerateAppUserListFromReader<AppUser>, ref users);
           
            return users;
        }

        public override AppUser GetAppUserByEmail(string email)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@Email", SqlDbType.NText, 200, ParameterDirection.Input, email);
        
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_APPUSER_GETBYEMAIL);

            List<AppUser> users = new List<AppUser>();

            TExecuteReaderCmd<AppUser>(sqlCmd, TGenerateAppUserListFromReader<AppUser>, ref users);
            if (users.Count > 0)
                return users[0];
            return null;
        }

        public override AppUser GetUserById(Guid userId)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@UserId", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, userId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_APPUSER_GETBYID);

            List<AppUser> users = new List<AppUser>();

            TExecuteReaderCmd<AppUser>(sqlCmd, TGenerateAppUserListFromReader<AppUser>, ref users);
            if (users.Count > 0)
                return users[0];
            return null;
        }

        public override AppUser GetUserByUserName(string userName)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 200, ParameterDirection.Input, userName);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_APPUSER_GETBYNAME);

            List<AppUser> users = new List<AppUser>();

            TExecuteReaderCmd<AppUser>(sqlCmd, TGenerateAppUserListFromReader<AppUser>, ref users);
            if (users.Count > 0)
                return users[0];
            return null;
        }

        public override bool UpdateAppUser(AppUser u)
        {
            if (u == null)
                throw (new ArgumentNullException("AppUser"));

            if (u.UserId == Guid.Empty)
                throw (new ArgumentOutOfRangeException("UserId"));

            SqlCommand sqlCmd = new SqlCommand();


            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@UserId", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, u.UserId);

            bool isAnonymous = false;

         
            AddParamToSQLCmd(sqlCmd, "@FirstName", SqlDbType.NText, 200, ParameterDirection.Input, u.FirstName);
            AddParamToSQLCmd(sqlCmd, "@LastName", SqlDbType.NText, 200, ParameterDirection.Input, u.LastName);
            AddParamToSQLCmd(sqlCmd, "@Title", SqlDbType.NText, 300, ParameterDirection.Input, u.Title);
            AddParamToSQLCmd(sqlCmd, "@SecurityLevel", SqlDbType.Int, 300, ParameterDirection.Input, u.SecurityLevel);
            AddParamToSQLCmd(sqlCmd, "@Rate", SqlDbType.Decimal, 0, ParameterDirection.Input, u.Rate);
            AddParamToSQLCmd(sqlCmd, "@CreditDepartment", SqlDbType.NText, 800, ParameterDirection.Input, u.CreditDepartment);
            AddParamToSQLCmd(sqlCmd, "@isLocked", SqlDbType.Bit, 0, ParameterDirection.Input,false);
            AddParamToSQLCmd(sqlCmd, "@isInActive", SqlDbType.Bit, 0, ParameterDirection.Input, u.InActive);
            AddParamToSQLCmd(sqlCmd, "@RegEmailSent", SqlDbType.Bit, 0, ParameterDirection.Input, u.RegEmailSent);


            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_APPUSER_UPDATE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0);
        }
        public override bool UpdateAppUser(AppUser u,bool isLocked)
        {
            if (u == null)
                throw (new ArgumentNullException("AppUser"));

            if (u.UserId == Guid.Empty)
                throw (new ArgumentOutOfRangeException("UserId"));

            SqlCommand sqlCmd = new SqlCommand();


            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@UserId", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, u.UserId);

            bool isAnonymous = false;

           
            AddParamToSQLCmd(sqlCmd, "@FirstName", SqlDbType.NText, 200, ParameterDirection.Input, u.FirstName);
            AddParamToSQLCmd(sqlCmd, "@LastName", SqlDbType.NText, 200, ParameterDirection.Input, u.LastName);
            AddParamToSQLCmd(sqlCmd, "@Title", SqlDbType.NText, 300, ParameterDirection.Input, u.Title);
            AddParamToSQLCmd(sqlCmd, "@SecurityLevel", SqlDbType.Int, 300, ParameterDirection.Input, u.SecurityLevel);
            AddParamToSQLCmd(sqlCmd, "@Rate", SqlDbType.Decimal, 0, ParameterDirection.Input, u.Rate);
            AddParamToSQLCmd(sqlCmd, "@CreditDepartment", SqlDbType.NText, 800, ParameterDirection.Input, u.CreditDepartment);
            AddParamToSQLCmd(sqlCmd, "@isLocked", SqlDbType.Bit, 0, ParameterDirection.Input, isLocked);
            AddParamToSQLCmd(sqlCmd, "@isInActive", SqlDbType.Bit, 0, ParameterDirection.Input, u.InActive);
            AddParamToSQLCmd(sqlCmd, "@RegEmailSent", SqlDbType.Bit, 0, ParameterDirection.Input, u.RegEmailSent);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_APPUSER_UPDATE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0);
        }
        public override Guid AddAppUser(AppUser u)
        {
            if (u == null)
                throw (new ArgumentNullException("AppUser"));

            if (u.UserId != Guid.Empty)
                throw (new ArgumentOutOfRangeException("UserId"));

            SqlCommand sqlCmd = new SqlCommand();
 
            
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@UserId", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Output, Guid.NewGuid());
           
            bool isAnonymous = false;

            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 255, ParameterDirection.Input, u.UserName);
            AddParamToSQLCmd(sqlCmd, "@IsUserAnonymous", SqlDbType.Bit, 0, ParameterDirection.Input, isAnonymous);
            AddParamToSQLCmd(sqlCmd, "@LastActivityDate", SqlDbType.DateTime, 0, ParameterDirection.Input, DefaultValues.GetDefaultStartDate());
            AddParamToSQLCmd(sqlCmd, "@FirstName", SqlDbType.NText, 200, ParameterDirection.Input, u.FirstName);
            AddParamToSQLCmd(sqlCmd, "@LastName", SqlDbType.NText, 200, ParameterDirection.Input, u.LastName);
            AddParamToSQLCmd(sqlCmd, "@Title", SqlDbType.NText, 300, ParameterDirection.Input, u.Title);
            AddParamToSQLCmd(sqlCmd, "@SecurityLevel", SqlDbType.Int, 300, ParameterDirection.Input, u.SecurityLevel);
            AddParamToSQLCmd(sqlCmd, "@Rate", SqlDbType.Decimal, 0, ParameterDirection.Input, u.Rate);
            AddParamToSQLCmd(sqlCmd, "@CreditDepartment", SqlDbType.NText, 800, ParameterDirection.Input, u.CreditDepartment);
            AddParamToSQLCmd(sqlCmd, "@RegEmailSent", SqlDbType.Bit, 0, ParameterDirection.Input, u.RegEmailSent);
          //  AddParamToSQLCmd(sqlCmd, "@RegEmailSent", SqlDbType.Bit, 0, ParameterDirection.Input, u.RegEmailSent);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_APPUSER_CREATE);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int) sqlCmd.Parameters["@ReturnValue"].Value;
            if (returnValue == 0)
            {
                Guid UserID = (Guid) sqlCmd.Parameters["@UserId"].Value;
                return UserID;
            }
            else
            {
                return Guid.Empty;
            }
        }
        private void TGenerateAppUserListFromReader<T>(SqlDataReader returnData, ref List<AppUser> userList)
        {

            while (returnData.Read())
            {
                AppUser u = new AppUser(returnData["UserName"] as string,
                  (Guid) returnData["UserId"], (int) returnData["SecurityLevel"], returnData["FirstName"] as string, returnData["LastName"] as string,
                  returnData["Title"] as string, returnData["CreditDepartment"] as string, (decimal) returnData["rate"], (bool) returnData["RegEmailSent"]);
                userList.Add(u);
            }
        }

        #endregion
        #region Page/control security
        public static string SP_PAGESECURITY_GETBYNAME = "aspnet_pageSecurity_GetByPageName";
        public override ControlSecurity GetPageSecurity(string name)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@PageName", SqlDbType.NText, 200, ParameterDirection.Input, name);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PAGESECURITY_GETBYNAME);

            List<ControlSecurity> controls = new List<ControlSecurity>();

            TExecuteReaderCmd<ControlSecurity>(sqlCmd, TGenerateControlListFromReader<ControlSecurity>, ref controls);
            if (controls.Count > 0)
                return controls[0];
            return null;
        }
        private void TGenerateControlListFromReader<T>(SqlDataReader returnData, ref List<ControlSecurity> cntlList)
        {
            Hashtable controls = new Hashtable();
            while (returnData.Read())
            {
                string controlName = returnData["PageName"] as string;
                ControlSecurity c = null;
                if (controls.ContainsKey(controlName))
                {
                    c = (ControlSecurity) controls[controlName];
                    ControlSecurity child = new ControlSecurity(returnData["Name"] as string, (int) returnData["MinLevel"], (int) returnData["MaxLevel"], new List<ControlSecurity>());
                    c.ChildControls.Add(child);
                }
                c = new ControlSecurity(controlName, (int) returnData["MinLevel"], (int) returnData["MaxLevel"],new List<ControlSecurity>());
                controls.Add(controlName,c);
            }
            foreach(ControlSecurity c in controls.Values)
            {
                cntlList.Add(c);
            }
        }
        
        #endregion
    }
}