如何修改ASP.NET成员资格存储过程 [英] how to modify ASP.NET MEmbership stored procedures

问题描述

我在web应用中使用ASP.NET成员资格和我用GetAllUsers方法为用户上市，但此方法返回列表中用户名下令我想订购再见CrateDate I基金存储过程在我的SQL，但我不知道如何修改或(
这是我GetAllUsers的code存储过程：

 使用[MoftakiDB]
走
/ ******对象：StoredProcedure的[DBO] [aspnet_Membership_GetAllUsers]脚本日期：04月26日14点24分14秒****** /
SET ANSI_NULLS ON
走
SET QUOTED_IDENTIFIER OFF
走
ALTER PROCEDURE [DBO]。[aspnet_Membership_GetAllUsers]
    @ApplicationName nvarchar的（256），
    @PageIndex INT，
    @PageSize INT
如
开始
    DECLARE @ApplicationId唯一标识符
    选择@ApplicationId = NULL
    选择@ApplicationId =的applicationID从dbo.aspnet_Applications WHERE LOWER（@ApplicationName）= LoweredApplicationName
    IF（@ApplicationId IS NULL）
        返回0
     - 设置页面边界
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords INT
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize  -  1 + @PageLowerBound     - 创建一个临时表来存储选择结果
    CREATE TABLE #PageIndexForUsers
    （
        IndexID为INT IDENTITY（0,1）NOT NULL，
        用户ID唯一标识符
    ）     - 将进入我们的临时表
    INSERT INTO #PageIndexForUsers（用户ID）
    SELECT u.UserId
    从dbo.aspnet_Membership男，dbo.aspnet_Usersū
    WHERE u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
    ORDER BY u.UserName    SELECT @TotalRecords = @@ ROWCOUNT    选择u.UserName，m.Email，m.PasswordQuestion，m.Comment，m.IsApproved，
            m.CreateDate，
            m.LastLoginDate，
            u.LastActivityDate，
            m.LastPasswordChangedDate，
            u.UserId，m.IsLockedOut，
            m.LastLockoutDate
    从dbo.aspnet_Membership男，dbo.aspnet_Users U，#PageIndexForUsers p
    WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId＆GT; = @PageLowerBound和p.IndexId＆LT; = @PageUpperBound
    ORDER BY u.UserName
    RETURN @TotalRecords
结束
 

解决方案

这是一个普通的存储过程，这样你就可以用 ALTER PROCEDURE 就像任何其他SP修改

不过，我会建议反对。这是SP的成员资格提供程序的一部分，你有没有在code所做的假设的想法。更改排序可能破坏会员系统的一些关键部分。

这是更好地让所有的用户和使用LINQ 排序依据运算符重新排序在内存中的返回序列。

  VAR用户= Membership.GetAllUsers（）演员LT;＆的MembershipUser GT;（）
            .OrderBy（亩=＆GT; mu.CreationDate）;
 

因为集合（ MembershipUserCollection ）仅实现了非通用的IEnumerable 接口演员是必需的。

i used ASP.NET Membership in my webapplication and i used GetAllUsers Method for listing of users, but this method returns list ordered by UserName i want to order it bye CrateDate i fund the stored procedure in my sql but i dont know how to modify or edit it :( this is the code of my GetAllUsers stored procedure :

    USE [MoftakiDB]
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_GetAllUsers]    Script Date: 04/26/2012 14:24:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[aspnet_Membership_GetAllUsers]
    @ApplicationName       nvarchar(256),
    @PageIndex             int,
    @PageSize              int
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0


    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
    SELECT u.UserId
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u
    WHERE  u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
    ORDER BY u.UserName

    SELECT @TotalRecords = @@ROWCOUNT

    SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY u.UserName
    RETURN @TotalRecords
END

解决方案

It's an ordinary stored procedure, so you can modify it with ALTER PROCEDURE just as with any other SP.

However I would advise against it. That SP is part of the membership provider and you have no idea of the assumptions made by the code. Changing the ordering might break some critical part of the membership system.

It is better to get all the users and the use the linq OrderBy operator to reorder the returned sequence in memory.

var users = Membership.GetAllUsers().Cast<MembershipUser>()
            .OrderBy(mu => mu.CreationDate);

The cast is required because the collection (MembershipUserCollection) only implement the non-generic IEnumerable interface.

这篇关于如何修改ASP.NET成员资格存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！