近日在开发DotNetNuke系统时，要实现一个查找站内注册用户信息的功能，我首先想到的就是系统自带的用户管理页面，因为系统自带的管理页面，在功能上，已经实现了大部份我要的功能，比如按用户名查找用户，按用户的其他Profile信息查找。
但有一个问题，那就是DotNetNuke自带的用户管理的查找功能很弱，对于稍稍不常见的中文查找就会出现找不到的情况，特别是查找公司名称，如果查找“北京三一重工有限公司”，如果用户只输入“三一”是不会返回任何结果的。那么能不能搭一搭SQL的Full-text index search这个顺风车呢？
经过一番研究(当中几千字日后再写.....)，简单的解决方案如下：
在SQL数据库中找到如下的存储过程：
1、在企业管理器中，为UserProfile表的PropertyValue和PropertyText建立全文检索。
2、在SQL企业管理器中，点击“工具->查询分析器(Query Analyer)" 运行以下代码：
因为时间紧张，先写到这里，还有很多要补充的，并且这个解决方案直接改到了DotNetNuke系统的内核程序，不能向后兼容，写出来只是提供参考，解一时之急，正确的做法应该是写一个新的Provider，这个有时间再说吧。
但有一个问题，那就是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
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
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，这个有时间再说吧。
