public static bool CreatePortalUser(UserInfo obj, out UserCreationStatus status, UserCreationMode mode)
        {
            string sp = "[dbo].[usp_sf_CreateUser]";
            try
            {
                List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
                ParamCollInput.Add(new KeyValuePair<string, object>("@ApplicationName", obj.ApplicationName));
                ParamCollInput.Add(new KeyValuePair<string, object>("@UserName", obj.UserName));
                ParamCollInput.Add(new KeyValuePair<string, object>("@FirstName", obj.FirstName));
                ParamCollInput.Add(new KeyValuePair<string, object>("@LastName", obj.LastName));
                ParamCollInput.Add(new KeyValuePair<string, object>("@Password", obj.Password));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PasswordSalt", obj.PasswordSalt));
                ParamCollInput.Add(new KeyValuePair<string, object>("@Email", obj.Email));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PasswordQuestion", obj.SecurityQuestion));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PasswordAnswer", obj.SecurityAnswer));
                ParamCollInput.Add(new KeyValuePair<string, object>("@IsApproved", obj.IsApproved));
                ParamCollInput.Add(new KeyValuePair<string, object>("@CurrentTimeUtc", obj.CurrentTimeUtc));
                ParamCollInput.Add(new KeyValuePair<string, object>("@CreateDate", obj.CreatedDate));
                ParamCollInput.Add(new KeyValuePair<string, object>("@UniqueEmail", obj.UniqueEmail));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PasswordFormat", obj.PasswordFormat));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", obj.PortalID));
                ParamCollInput.Add(new KeyValuePair<string, object>("@AddedOn", obj.AddedOn));
                ParamCollInput.Add(new KeyValuePair<string, object>("@AddedBy", obj.AddedBy));
                ParamCollInput.Add(new KeyValuePair<string, object>("@RoleNames", obj.RoleNames));
                ParamCollInput.Add(new KeyValuePair<string, object>("@StoreID", obj.StoreID)); 

                List<KeyValuePair<string, object>> ParamCollOutput = new List<KeyValuePair<string, object>>();
                ParamCollOutput.Add(new KeyValuePair<string, object>("@UserId", obj.UserID));
                ParamCollOutput.Add(new KeyValuePair<string, object>("@ErrorCode", 0));
                ParamCollOutput.Add(new KeyValuePair<string, object>("@CustomerID", obj.CustomerID));             

                SageFrameSQLHelper sagesql = new SageFrameSQLHelper();

                List<KeyValuePair<int, string>> OutputValColl = new List<KeyValuePair<int, string>>();
                OutputValColl = sagesql.ExecuteNonQueryWithMultipleOutput(sp, ParamCollInput, ParamCollOutput);
                int CustomerID = int.Parse(OutputValColl[2].Value);
                int ErrorCode = int.Parse(OutputValColl[1].Value);
                Guid UserID = new Guid(OutputValColl[0].Value.ToString());

                switch (ErrorCode)
                {
                    case 3:
                        status = UserCreationStatus.DUPLICATE_EMAIL;
                        break;
                    case 6:
                        status = UserCreationStatus.DUPLICATE_USER;
                        break;
                    default:
                        status = UserCreationStatus.SUCCESS;
                        break;
                }
                return true;
            }
            catch (Exception)
            {
                throw;
            }


        }
        public static bool ChangePassword(UserInfo obj)
        {
            string sp = "[dbo].[usp_sf_ResetPassword]";
            SageFrameSQLHelper sagesql = new SageFrameSQLHelper();

            try
            {
                List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
                ParamCollInput.Add(new KeyValuePair<string, object>("@UserID", obj.UserID));
                ParamCollInput.Add(new KeyValuePair<string, object>("@NewPassword", obj.Password));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PasswordSalt", obj.PasswordSalt));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PasswordFormat", obj.PasswordFormat));

                sagesql.ExecuteNonQuery(sp, ParamCollInput);
                return true;
            }
            catch (Exception)
            {

                throw;
            }
        }
        public static bool ChangeUserInRoles(string ApplicationName, Guid UserID, string RoleNamesUnselected, string RoleNamesSelected, int PortalID)
        {
            string sp = "[dbo].[usp_ChangeUserInRoles]";
            SageFrameSQLHelper sagesql = new SageFrameSQLHelper();
            try
            {
                List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
                ParamCollInput.Add(new KeyValuePair<string, object>("@ApplicationName", ApplicationName));
                ParamCollInput.Add(new KeyValuePair<string, object>("@UserID", UserID));
                ParamCollInput.Add(new KeyValuePair<string, object>("@RoleNamesUnselected", RoleNamesUnselected));
                ParamCollInput.Add(new KeyValuePair<string, object>("@RoleNamesSelected", RoleNamesSelected));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
                sagesql.ExecuteNonQuery(sp, ParamCollInput);

                return true;
            }
            catch (Exception)
            {

                throw;
            }

        }
        public static bool DeleteUserInRoles(UserInfo obj)
        {
            string sp = "[dbo].[usp_sf_UserInRolesDelete]";
            SageFrameSQLHelper sagesql = new SageFrameSQLHelper();
            try
            {
                List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
                ParamCollInput.Add(new KeyValuePair<string, object>("@ApplicationName", obj.ApplicationName));
                ParamCollInput.Add(new KeyValuePair<string, object>("@UserID", obj.UserID));
                ParamCollInput.Add(new KeyValuePair<string, object>("@RoleNames", obj.RoleNames));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", obj.PortalID));

                List<KeyValuePair<string, object>> ParamCollOutput = new List<KeyValuePair<string, object>>();
                ParamCollOutput.Add(new KeyValuePair<string, object>("@ErrorCode", 0));

                List<KeyValuePair<int, string>> OutPutColl = new List<KeyValuePair<int, string>>();
                OutPutColl = sagesql.ExecuteNonQueryWithMultipleOutput(sp, ParamCollInput, ParamCollOutput);

                return true;
            }
            catch (Exception)
            {

                throw;
            }

        }
        public static bool AddUserInRoles(UserInfo obj)
        {
            string sp = "[dbo].[usp_sf_UserInRolesAdd]";
            SageFrameSQLHelper sagesql = new SageFrameSQLHelper();
            try
            {
                List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
                ParamCollInput.Add(new KeyValuePair<string, object>("@ApplicationName", obj.ApplicationName));
                ParamCollInput.Add(new KeyValuePair<string, object>("@UserID", obj.UserID));
                ParamCollInput.Add(new KeyValuePair<string, object>("@RoleNames", obj.RoleNames));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", obj.PortalID));

                sagesql.ExecuteNonQuery(sp, ParamCollInput);


                return true;
            }
            catch (Exception)
            {

                throw;
            }

        }
        public static void CreateRole(RoleInfo obj, out RoleCreationStatus status)
        {
            List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
            ParamCollInput.Add(new KeyValuePair<string, object>("@ApplicationName", obj.ApplicationName));
            ParamCollInput.Add(new KeyValuePair<string, object>("@RoleName", obj.RoleName));
            ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", obj.PortalID));
            ParamCollInput.Add(new KeyValuePair<string, object>("@IsActive", obj.IsActive));
            ParamCollInput.Add(new KeyValuePair<string, object>("@AddedOn", obj.AddedOn));
            ParamCollInput.Add(new KeyValuePair<string, object>("@AddedBy", obj.AddedBy));

            List<KeyValuePair<string, object>> ParamCollOutput = new List<KeyValuePair<string, object>>();
            ParamCollOutput.Add(new KeyValuePair<string, object>("@ErrorCode", 0));

            try
            {
                SageFrameSQLHelper sagesql = new SageFrameSQLHelper();
                List<KeyValuePair<int, string>> OutputValColl = new List<KeyValuePair<int, string>>();
                OutputValColl = sagesql.ExecuteNonQueryWithMultipleOutput("[dbo].[usp_sf_CreateRole]", ParamCollInput, ParamCollOutput);
                int ErrorCode = int.Parse(OutputValColl[0].Value);

                switch (ErrorCode)
                {
                    case 1:
                        status = RoleCreationStatus.DUPLICATE_ROLE;
                        break;
                    default:
                        status = RoleCreationStatus.SUCCESS;
                        break;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }
        public static string ActivateUser(string ActivationCode, int PortalID, int StoreID)
        {
            string sp = "[usp_ActivateUserByUserId]";
            SageFrameSQLHelper sagesql = new SageFrameSQLHelper();

            List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
            ParamCollInput.Add(new KeyValuePair<string, object>("@ActivationCode", ActivationCode));
            ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
            ParamCollInput.Add(new KeyValuePair<string, object>("@StoreID", StoreID));

            List<KeyValuePair<string, object>> ParamCollOutput = new List<KeyValuePair<string, object>>();
            ParamCollOutput.Add(new KeyValuePair<string, object>("@UserName", "nouser"));

            try
            {
                List<KeyValuePair<int, string>> Output = new List<KeyValuePair<int, string>>();
                Output = sagesql.ExecuteNonQueryWithMultipleOutput(sp, ParamCollInput, ParamCollOutput);
                return Output[0].Value.ToString();
            }
            catch (Exception ex)
            {

                throw (ex);
            }
        }
        public static SageFrameUserCollection SearchUsers(string RoleID, string SearchText, int PortalID, string UserName)
        {
            string sp = "[dbo].[usp_SageFrameUserListSearch]";
            SageFrameSQLHelper sagesql = new SageFrameSQLHelper();

            SageFrameUserCollection userColl = new SageFrameUserCollection();
            List<UserInfo> lstUsers = new List<UserInfo>();

            List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
            ParamCollInput.Add(new KeyValuePair<string, object>("@RoleID", RoleID));
            ParamCollInput.Add(new KeyValuePair<string, object>("@SearchText", SearchText));
            ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
            ParamCollInput.Add(new KeyValuePair<string, object>("@Username", UserName));
            try
            {
                SqlDataReader reader;
                reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);
                while (reader.Read())
                {
                    UserInfo obj = new UserInfo();
                    obj.UserID = new Guid(reader["userid"].ToString());
                    obj.UserName = reader["username"].ToString();
                    obj.FirstName = reader["firstname"].ToString();
                    obj.LastName = reader["lastname"].ToString();
                    obj.Email = reader["email"].ToString();
                    obj.IsActive = bool.Parse(reader["IsActive"].ToString());
                    lstUsers.Add(obj);
                }
                reader.Close();
                userColl.UserList = lstUsers;
                return userColl;

            }
            catch (Exception ex)
            {

                throw (ex);
            }

        }
        public static bool UpdateUser(UserInfo obj, out UserUpdateStatus status)
        {
            string sp = "[dbo].[usp_sf_UsersUpdate]";
            try
            {
                List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
                ParamCollInput.Add(new KeyValuePair<string, object>("@ApplicationName", obj.ApplicationName));
                ParamCollInput.Add(new KeyValuePair<string, object>("@UserName", obj.UserName));
                ParamCollInput.Add(new KeyValuePair<string, object>("@UserID", obj.UserID));
                ParamCollInput.Add(new KeyValuePair<string, object>("@FirstName", obj.FirstName));
                ParamCollInput.Add(new KeyValuePair<string, object>("@LastName", obj.LastName));
                ParamCollInput.Add(new KeyValuePair<string, object>("@Email", obj.Email));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", obj.PortalID));
                ParamCollInput.Add(new KeyValuePair<string, object>("@IsApproved", obj.IsApproved));
                ParamCollInput.Add(new KeyValuePair<string, object>("@UpdatedBy", obj.UpdatedBy));
                ParamCollInput.Add(new KeyValuePair<string, object>("@StoreID", obj.StoreID));

                List<KeyValuePair<string, object>> ParamCollOutput = new List<KeyValuePair<string, object>>();
                ParamCollOutput.Add(new KeyValuePair<string, object>("@ErrorCode", 0));

                SageFrameSQLHelper sagesql = new SageFrameSQLHelper();

                List<KeyValuePair<int, string>> OutputValColl = new List<KeyValuePair<int, string>>();
                OutputValColl = sagesql.ExecuteNonQueryWithMultipleOutput(sp, ParamCollInput, ParamCollOutput);

                int ErrorCode = int.Parse(OutputValColl[0].Value);

                switch (ErrorCode)
                {
                    case 1:
                        status = UserUpdateStatus.DUPLICATE_EMAIL_NOT_ALLOWED;
                        break;
                    default:
                        status = UserUpdateStatus.USER_UPDATE_SUCCESSFUL;
                        break;
                }
                return true;
            }
            catch (Exception)
            {
                throw;
            }


        }