将现有网站与Community Server2.0整合解决方案（原创)

网站需要快速搭建论坛与BOLG平台，突然想到CS2.0， 2天搞定用户整合部分！以下是解决方案！

解决老用户向新的论坛的导入问题
1 清空论坛内的数据，或直接导入空的数据库 CS2DataBase2.0.bak 恢复名称为 CommunityServer
2 在CommunityServer库中添加原TestDataBaseWeb库中的TestDataBaseWebAdmin用户
3 执行以下存储过程

USE CommunityServer
GO
/*
用户整合将指定的用户信息转入CS2.0中
2008-3-3
测试： user:liangzhimy66 pwd: 111111
EXEC dbo.cs_Auto_Create_User
@UserID   = 'liangzhimy66',
@Email    = 'liangzhimy66@yahoo.com.cn',
@CommonName = 'liangzhimy66',
@CommPassword   = 'ZPg9qqxyp2sWPM+a+juLTP72wKM=',
@CommPasswordSalt = 'dG2Q/DXNfUTXDr/mBH5Rgw=='
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Auto_Create_User]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Auto_Create_User]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- =============================================
-- Author: LIANGZHIMY
-- Create date: 2008-3-3
-- Description: 用户整合将指定的用户信息转入CS2.0中
-- =============================================
CREATE PROCEDURE dbo.cs_Auto_Create_User
@UserID     nvarchar(256),
@Email      nvarchar(128),
@CommonName nvarchar(128),
@CommPassword   nvarchar(128),
@CommPasswordSalt nvarchar(128)
AS
BEGIN
DECLARE @retval int
DECLARE @timezoneadj int
SET @timezoneadj = DATEDIFF(hh,GETUTCDATE(),GETDATE())

EXECUTE @retval = aspnet_Membership_GetUserByName
@ApplicationName    = 'dev',
@UserName           = @UserID,
@TimeZoneAdjustment = @timezoneadj,
@UpdateLastActivity = 1

IF (@retval = -1)
BEGIN
DECLARE @newuserid uniqueidentifier
DECLARE @guid uniqueidentifier
DECLARE @csuserid int
SET @guid = NEWID()

EXECUTE @retval = aspnet_Membership_CreateUser
@ApplicationName    = 'dev',
@UserName           = @UserID,
@Password           = @CommPassword,
@PasswordSalt       = @CommPasswordSalt,
@Email              = @Email,
@PasswordQuestion   = NULL,
@PasswordAnswer     = NULL,
@IsApproved         = 1,
@TimeZoneAdjustment = @timezoneadj,
@UniqueEmail        = 1,
@PasswordFormat     = 1,
@UserId             = @newuserid OUTPUT

EXECUTE @retval = aspnet_UsersInRoles_AddUsersToRoles
@ApplicationName    = 'dev',
@UserNames          = @UserID,
@RoleNames          = 'Everyone,Registered Users',
@TimeZoneAdjustment = @timezoneadj

EXECUTE @retval = cs_user_CreateUpdateDelete
@cs_UserID   = @csuserid OUTPUT,
@UserID             = @newuserid,
@Action             = 0,        -- CREATE
@SettingsID               = 1000,     -- CS
@TimeZone                           = 0,
@ModerationLevel                    = 1,        -- Not Moderated
@EnableThreadTracking               = 1,
@EnableDisplayUnreadThreadsOnly     = 1
/*
@Action=0,
@cs_UserID=@p2 output,
@UserID=@newuserid,
@UserAccountStatus=1,
@IsAnonymous=0,
@IsIgnored=0,
@ForceLogin=0,
@AppUserToken='',
@PropertyNames=default,
@PropertyValues=default,
@TimeZone=0,
@PostRank=default,
@PostSortOrder=0,
@IsAvatarApproved=1,
@ModerationLevel=0,
@EnableThreadTracking=0,
@EnableAvatar=0,
@EnableDisplayInMemberList=1,
@EnablePrivateMessages=0,
@EnableOnlineStatus=0,
@EnableEmail=1,
@EnableHtmlEmail=1,
@FavoritesShared=0,
@SettingsID=1000
*/
END

EXECUTE aspnet_Profile_GetProperties
@ApplicationName        = 'dev',
@UserName               = @UserID,
@TimeZoneAdjustment     = @timezoneadj
IF (@@ROWCOUNT = 0)
BEGIN

DECLARE @pn varchar(1000), @pvs varchar(1000)
SET @pvs = '-5'+@CommonName
SET @pn = 'timezone:S:0:2:commonName:S:2:' + CAST(LEN(@CommonName) AS varchar(5))

EXECUTE aspnet_Profile_SetProperties
@ApplicationName        = 'dev',
@PropertyNames          = @pn,
@PropertyValuesString   = @pvs,
@PropertyValuesBinary   = '',
@UserName               = @UserID,
@IsUserAnonymous        = 0,
@TimeZoneAdjustment     = @timezoneadj
END

END

-- 自动开通论坛
DECLARE @pUserName Varchar(256)
DECLARE @pUserID int
SET @pUserName = @UserID
print LEN(@pUserName)

DECLARE @pPropertyValues VARCHAR(256)

SET @pPropertyValues = N'' + Convert(varchar(128), @pUserName) + 'TrueTrueAbout ' + Convert(varchar(128), @pUserName)

DECLARE @pPropertyNames VARCHAR(1000)
--SET @pPropertyNames = N'SectionOwners:S:0:5:EnableAggBugs:S:5:4:IsCommunityAggregated:S:9:4:aboutTitle:S:13:11:'
SET @pPropertyNames = N'SectionOwners:S:0:' + Convert(varchar(10), LEN(@pUserName)) +
':EnableAggBugs:S:' + Convert(varchar(10), LEN(@pUserName)) + ':4:IsCommunityAggregated:S:'
+ Convert(varchar(10), LEN(@pUserName) + 4) + ':4:aboutTitle:S:' +
Convert(varchar(10), LEN(@pUserName) + 8)
+ ':11:'

SET @pPropertyNames = N'publicEmail:S:0:0:yahooIM:S:0:0:timezone:S:0:1:msnIM:S:1:0:commonName:S:1:0:birthdate:B:0:-1:gender:B:0:-1:fontsize:B:0:-1:bio:S:1:0:webLog:S:1:0:enablePostPreviewPopup:B:0:-1:location:S:1:0:interests:S:1:0:webGallery:B:0:-1:occupation:S:1:0:signature:S:1:0:icqIM:S:1:0:aolIM:S:1:0:signatureFormatted:S:1:0:webAddress:S:1:0:enableEmoticons:B:0:-1:'

SET @pPropertyValues = N'8'

print @pPropertyNames
SELECT @pUserID = u.UserID FROM dbo.cs_Users u
JOIN dbo.aspnet_Users m
ON m.UserId = u.MembershipID
Where UserName = @pUserName

declare @P1 int
SET @P1 = 0

declare @pGroupID int

SELECT TOP 1 @pGroupID = GroupID
FROM cs_Groups
WHERE (ApplicationType = 1)
ORDER BY SortOrder DESC

exec dbo.cs_Section_CreateUpdateDelete
@SectionID = @P1 output,
@Name = @pUserName,
@Url = default,
@Description = N'',
@ParentID = 0,
@GroupID = @pGroupID,
@IsModerated = 0,
@DisplayPostsOlderThan = 7,
@IsActive = 1,
@EnablePostStatistics = 1,
@EnablePostPoints = 1,
@EnableAutoDelete = 0,
@EnableAnonymousPosting = 0,
@AutoDeleteThreshold = 90,
@SortOrder = 0,
@IsSearchable = 1,
@ApplicationType = 1,
@ApplicationKey = @pUserName,
@ForumType = 0,
@PropertyNames = @pPropertyNames,
@PropertyValues = @pPropertyValues,
@SettingsID = 1000,
@UserID = @pUserID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[cs_Auto_Create_User] TO [TestDataBaseWebAdmin]
GO

USE TestDataBaseWeb
GO

/*
用户注册时自动注册到BBS中
*/
CREATE PROC dbo.TestDataBase_User_AutoRegBBS
@RegUserName nvarchar(256)
AS

DECLARE @pUserID     nvarchar(256),
@pEmail      nvarchar(128),
@pCommonName nvarchar(128),
@pCommPassword   nvarchar(128),
@pCommPasswordSalt nvarchar(128)

SELECT @pUserID = UserName, @pCommonName = loweredUserName, @pCommPassword = Password,
@pCommPasswordSalt = PasswordSalt, @pEmail = Email
FROM dbo.aspnet_Users u
JOIN dbo.aspnet_Membership m
ON u.UserID = m.UserID
where u.UserName = @RegUserName

EXEC CommunityServer.dbo.cs_Auto_Create_User
@UserID   = @pUserID,
@Email    = @pEmail,
@CommonName = @pCommonName,
@CommPassword   = @pCommPassword,
@CommPasswordSalt = @pCommPasswordSalt
GO

4 导入原网站内的用户
执行以下脚本

USE TestDataBaseWeb
GO

DECLARE @pUserID     nvarchar(256),
@pEmail      nvarchar(128),
@pCommonName nvarchar(128),
@pCommPassword   nvarchar(128),
@pCommPasswordSalt nvarchar(128)

DECLARE contact_cursor CURSOR FOR
SELECT UserName, loweredUserName, [Password], PasswordSalt, Email
FROM dbo.aspnet_Users u
JOIN dbo.aspnet_Membership m
ON u.UserID = m.UserID

OPEN contact_cursor

FETCH NEXT FROM contact_cursor
INTO @pUserID, @pCommonName, @pCommPassword, @pCommPasswordSalt, @pEmail

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'UserID: ' + @pUserID
-- 导入数据以CS2中
EXEC CommunityServer.dbo.cs_Auto_Create_User
@UserID   = @pUserID,
@Email    = @pEmail,
@CommonName = @pCommonName,
@CommPassword   = @pCommPassword,
@CommPasswordSalt = @pCommPasswordSalt
   FETCH NEXT FROM contact_cursor
   INTO @pUserID, @pCommonName, @pCommPassword, @pCommPasswordSalt, @pEmail
END
CLOSE contact_cursor
DEALLOCATE contact_cursor

GO

5 .NET webconfig中注意修改domain=".test.com"
完整解决方案

<authentication mode="Forms">
    <forms name=".SWSCOMMONDOMAINTestDataBaseCOM" domain=".test.com" protection="All" defaultUrl="Default.aspx" loginUrl="login.aspx" ></forms>
   </authentication>
   <machineKey
  validationKey= "C50B3C89CB21F4F1422FF158A5B42D0E8DB8CB5CDA1742572A487D9401E3400267682B202B746511891C1BAF47F8D25C07F6C39A104696DB51F17C529AD3CABE"
  decryptionKey= "8A9BE8FD67AF6979E7D20198CFEA50DD3D3799C77AF2B72F"
  validation="SHA1">
   </machineKey>

以下是解决过程中的问题总结

-----------------------------------------------------------------------------------------------------
// 自动建立论坛

// 1 自动注册后创建论坛的模块
public class AutoBlogCreate : ICSModule
// 2 调用的事件
CSEvents.AfterUser(createdUser,ObjectState.Create);

-----------------------------------------------------------------------------------------------------
// 自动开通论坛
DECLARE @pUserName Varchar(128)
DECLARE @pUserID int
SET @pUserName = N'liangzhimytest1'
print LEN(@pUserName)

DECLARE @pPropertyValues VARCHAR(256)

SET @pPropertyValues = N'' + Convert(varchar(128), @pUserName) + 'TrueTrueAbout ' + Convert(varchar(128), @pUserName)

DECLARE @pPropertyNames VARCHAR(1000)
--SET @pPropertyNames = N'SectionOwners:S:0:5:EnableAggBugs:S:5:4:IsCommunityAggregated:S:9:4:aboutTitle:S:13:11:'
SET @pPropertyNames = N'SectionOwners:S:0:' + Convert(varchar(10), LEN(@pUserName)) +
':EnableAggBugs:S:' + Convert(varchar(10), LEN(@pUserName)) + ':4:IsCommunityAggregated:S:'
+ Convert(varchar(10), LEN(@pUserName) + 4) + ':4:aboutTitle:S:' +
Convert(varchar(10), LEN(@pUserName) + 8)
+ ':11:'

print @pPropertyNames
SELECT @pUserID = u.UserID FROM dbo.cs_Users u
JOIN dbo.aspnet_Users m
ON m.UserId = u.MembershipID
Where UserName = @pUserName

declare @P1 int
SET @P1 = 0

declare @pGroupID int

SELECT TOP 1 @pGroupID = GroupID
FROM cs_Groups
WHERE (ApplicationType = 1)
ORDER BY SortOrder DESC

exec dbo.cs_Section_CreateUpdateDelete
@SectionID = @P1 output,
@Name = @pUserName,
@Url = default,
@Description = N'',
@ParentID = 0,
@GroupID = @pGroupID,
@IsModerated = 0,
@DisplayPostsOlderThan = 7,
@IsActive = 1,
@EnablePostStatistics = 1,
@EnablePostPoints = 1,
@EnableAutoDelete = 0,
@EnableAnonymousPosting = 0,
@AutoDeleteThreshold = 90,
@SortOrder = 0,
@IsSearchable = 1,
@ApplicationType = 1,
@ApplicationKey = @pUserName,
@ForumType = 0,
@PropertyNames = @pPropertyNames,
@PropertyValues = @pPropertyValues,
@SettingsID = 1000,
@UserID = @pUserID

GO

-----------------------------------------------------------------------------------------------------
/*
用户注册时自动注册到BBS中
*/
CREATE PROC dbo.Gupk_User_AutoRegBBS
@RegUserName nvarchar(256)
AS

DECLARE @pUserID     nvarchar(256),
@pEmail      nvarchar(128),
@pCommonName nvarchar(128),
@pCommPassword   nvarchar(128),
@pCommPasswordSalt nvarchar(128)

SELECT @pUserID = UserName, @pCommonName = loweredUserName, @pCommPassword = Password,
@pCommPasswordSalt = PasswordSalt, @pEmail = Email
FROM dbo.aspnet_Users u
JOIN dbo.aspnet_Membership m
ON u.UserID = m.UserID
where u.UserName = @RegUserName

EXEC CommunityServer.dbo.cs_Auto_Create_User
@UserID   = @pUserID,
@Email    = @pEmail,
@CommonName = @pCommonName,
@CommPassword   = @pCommPassword,
@CommPasswordSalt = @pCommPasswordSalt
GO

-----------------------------------------------------------------------------------------------------
/*
用户整合将指定的用户信息转入CS2.0中
2008-3-3
测试： user:liangzhimy66 pwd: 111111
EXEC dbo.cs_Auto_Create_User
@UserID   = 'liangzhimy66',
@Email    = 'liangzhimy66@yahoo.com.cn',
@CommonName = 'liangzhimy66',
@CommPassword   = 'ZPg9qqxyp2sWPM+a+juLTP72wKM=',
@CommPasswordSalt = 'dG2Q/DXNfUTXDr/mBH5Rgw=='
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Auto_Create_User]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Auto_Create_User]
GO