让DotNetNuke 4.3.5使用SQL Full-text index 查找用户信息

2021-10-18 01:06:05

近日在开发DotNetNuke系统时，要实现一个查找站内注册用户信息的功能，我首先想到的就是系统自带的用户管理页面，因为系统自带的管理页面，在功能上，已经实现了大部份我要的功能，比如按用户名查找用户，按用户的其他Profile信息查找。

但有一个问题，那就是DotNetNuke自带的用户管理的查找功能很弱，对于稍稍不常见的中文查找就会出现找不到的情况，特别是查找公司名称，如果查找“北京三一重工有限公司”，如果用户只输入“三一”是不会返回任何结果的。那么能不能搭一搭SQL的Full-text index search这个顺风车呢？

经过一番研究(当中几千字日后再写.....)，简单的解决方案如下：

在SQL数据库中找到如下的存储过程：

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  PROCEDURE dbo.[GetUsersByProfileProperty]
    @PortalId        int,
    @PropertyName   nvarchar(256),
    @PropertyValue  nvarchar(256),
    @PageIndex      int,
    @PageSize       int
AS
BEGIN
    -- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound 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 int
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
        SELECT U.UserId
        FROM   ProfilePropertyDefinition P
            INNER JOIN UserProfile UP ON P.PropertyDefinitionID = UP.PropertyDefinitionID
            INNER JOIN Users U ON UP.UserID = U.UserID
        WHERE (PropertyName = @PropertyName) AND (PropertyValue LIKE @PropertyValue OR PropertyText LIKE @PropertyValue )
            AND (P.Portalid = @PortalId OR (P.PortalId Is Null AND @PortalId is null ))
        ORDER BY U.DisplayName

    SELECT  *
    FROM    vw_Users u,
            #PageIndexForUsers p
    WHERE  u.UserId = p.UserId
            AND ( PortalId = @PortalId OR (PortalId Is Null AND @PortalId is null ))
            AND p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
        ORDER BY U.DisplayName

    SELECT  TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

1、在企业管理器中，为UserProfile表的PropertyValue和PropertyText建立全文检索。

2、在SQL企业管理器中，点击“工具->查询分析器(Query Analyer)" 运行以下代码：

use DotNetNuke(请更换为你使用的数据库名称)
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetUsersByProfileProperty]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetUsersByProfileProperty]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.[GetUsersByProfileProperty]
    @PortalId        int,
    @PropertyName   nvarchar(256),
    @PropertyValue  nvarchar(256),
    @PageIndex      int,
    @PageSize       int
AS
BEGIN
    -- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound 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 int
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
        SELECT U.UserId
        FROM   ProfilePropertyDefinition P
            INNER JOIN UserProfile UP ON P.PropertyDefinitionID = UP.PropertyDefinitionID
            INNER JOIN Users U ON UP.UserID = U.UserID
        WHERE (PropertyName = @PropertyName) AND (contains(PropertyValue,@PropertyValue) OR contains(PropertyText,@PropertyValue ))
            AND (P.Portalid = @PortalId OR (P.PortalId Is Null AND @PortalId is null ))
        ORDER BY U.DisplayName

    SELECT  *
    FROM    vw_Users u,
            #PageIndexForUsers p
    WHERE  u.UserId = p.UserId
            AND ( PortalId = @PortalId OR (PortalId Is Null AND @PortalId is null ))
            AND p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
        ORDER BY U.DisplayName

    SELECT  TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

因为时间紧张，先写到这里，还有很多要补充的，并且这个解决方案直接改到了DotNetNuke系统的内核程序，不能向后兼容，写出来只是提供参考，解一时之急，正确的做法应该是写一个新的Provider，这个有时间再说吧。

码农公寓

相关文章