Have Data, Will Travel > Forums > Bi4ce Open Source DotNetNuke Skins: DNN Modules: DNN Consulting

Subject: Have Data, Will Travel

You are not authorized to post a reply.

Author

Messages

kevinmschreiner
Please Wait...

Posts:749

09/13/2005 2:14 PM

Alert

So, you have a fancy new install of DNN 3 and want to migrate your user profile information from a 3rd party source? The new membership model makes this exercise a bit daunting, but don't fret, a one-time import, or recursive integration is possible.

DotNetNuke has shifted their membership model to fall in line with the new provision in the ASP.NET 2.0 Framework which is called the Scalable Hosting Model. In a sense, the model provides the ability to manage and maintain user information securely within a database, and expose the ability to attach more attributes to a profile without the need to modify the database. The security model is a bit in depth, and at this point it is easy to assume that you already are aware of the structure - so how do you get those pesky PropertyNames and PropertyValues columns to populate easily within the aspnet_profile table?

The PropertyNames provides a roadmap of the name of the profile attribute, the starting position of the attribute as it is defined within the PropertyValues and the length of that defined attribute within the PropertyValues - so you need to simply populate this information without hosing up the start and stop positions of the values. We provide a simple (it’s a bit long, but all just repetitive) function for getting at each one of these values - allowing you to pass in the attributes as parameters and returning the PropertyName and Property Value functions. With this procedure, you can add all your users and populate the profile information from another data source easily.

CREATE   Function GetProfileNames(
@Website            varchar(50),
@TimeZone            varchar(5),
@PreferredLocale        varchar(25),
@Region            varchar(50),
@FirstName            varchar(25),
@PostalCode            varchar(15),
@Street            varchar(100),
@LastName            varchar(25),
@Unit                varchar(100),
@Telephone            varchar(25),
@Country            varchar(50),
@IM               varchar(50),
@Fax                varchar(25),
@City                varchar(50),
@Cell                varchar(25))
RETURNS varchar(2000)
AS
BEGIN

Declare @CONST varchar(2000)
Select @CONST = 'Website:S:#1s#:#1e#:TimeZone:S:#2s#:#2e#:PreferredLocale:S:#3s#:#3e#:Region:S:#4s#:#4e#:FirstName:S:#5s#:#5e#:PostalCode:S:#6s#:#6e#:Street:S:#7s#:#7e#:LastName:S:#8s#:#8e#:Unit:S:#9s#:#9e#:Telephone:S:#10s#:#10e#:Country:S:#11s#:#11e#:IM:S:#12s#:#12e#:Fax:S:#13s#:#13e#:City:S:#14s#:#14e#:Cell:S:#15s#:#15E#:'declare @Position int, @Index int, @Length int, @PropertyNames varchar(2000), @PropertyValues varchar(2000)

SELECT @Position = 0, @Index=0, @PropertyNames = @CONST, @PropertyValues = ''

--WEBSITE
select @Index = @Index+ 1
select @Length = len(isnull(@Website,''))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 's#',CAST(@Position as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues + isnull(@Website,'')
if @Length > 0
    select @Position = @Position + </SPAN>@Length

--TIMEZONE
select @Index = @Index + 1
select @Length = len(isnull(@TimeZone,''))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 's#',CAST(@Position as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues + isnull(@TimeZone,'')
if @Length > 0
    select @Position = @Position + @Length

--PREFERREDLOCALE
select @Index = @Index + 1
select @Length = len(isnull(@PreferredLocale,''))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Indexas varchar) + 's#',CAST(@Position as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues+ isnull(@PreferredLocale,'')
if @Length > 0
    select @Position = @Position + @Length

--REGION
select @Index = @Index + 1
select @Length = len(isnull(@Region,''))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 's#',CAST(@Position as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues + isnull(@Region,'')
if @Length > 0
    select @Position = @Position + @Length

--FIRSTNAME
select @Index = @Index + 1
select @Length = len(isnull(@FirstName,''))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 's#',CAST(@Position as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as lt;/SPAN>varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues + isnull(@FirstName,'')
if @Length > 0
    select @Position = @Position + @Length

--POSTALCODE
select @Index = @Index <SPAN style="COLOR: #808080">+ 1
select @Length = len(isnull(@PostalCode,''))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 's#',CAST(@Position as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues + isnull(@PostalCode,'')
if @Length > 0
    select @Position = @Position + @Length

--STREET
select @Index = @Index + 1
select @Length = len(isnull(@Street,''))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 's#',CAST(@Position as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues + isnull(@Street,'')
if @Length > 0
    select @Position = @Position + @Length

--LASTNAME
select @Index = @Index + 1
select @Length = len(isnull(@LastName,''))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 's#',CAST(@Position as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues + isnull(@LastName,'')
if @Length > 0
    select @Position = @Position + @Length

--UNIT
select @Index = @Index + 1
select @Length = len(isnull(@Unit,''))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 's#',CAST(@Position as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues + isnull(@Unit,'')
if @Length > 0
    select @Position = @Position + @Length

--TELEPHONE
select @Index = @Index + 1
select @Length = len(isnull(@Telephone,''))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 's#',CAST(@Position as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues + isnull(@Telephone,'')
if @Length > 0
    select @Position = @Position + @Length

--COUNTRY
select @Index = @Indexlt;SPAN style="COLOR: #000000"> + 1
select @Length = len(isnull(@Country,''))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 's#',CAST(@Position as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues + isnull(@Country,'')
if @Length > 0
    select @Position = @Position + @Length

--IM
select @Index = @Index + 1
select @Length = len(isnull(@IM,''))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 's#',CAST(@Postion as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues + isnull(@IM,'')
if @Length ></SPAN> 0
    select @Position = @Position + @Length

--FAX
select @Index = @Index + 1
select @Length = len(isnull(@Fax,''))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index &l;/SPAN>as varchar) + 's#',CAST(@Position as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues+ isnull(@Fax,'')
if @Length > 0
    select @Position = @Position + @Length
--CITY
select @Index = @Index + 1
select @Length = len(isnull(@City,''))
Select @PropertyNames = REPLAE(@PropertyNames,'#' + CAST(@Index as varchar) + 's#',CAST(@Position as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues + isnull(@City,'')
if @Length > 0
    select @Position = @Position + @Length

--CELL
select @Index = @Index + 1
select @Length = len(isnull(@Cell,''))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 's#',CAST(@Position as varchar))
Select @PropertyNames = REPLACE(@PropertyNames,'#' + CAST(@Index as varchar) + 'e#',CAST(@Length as varchar))
Select @PropertyValues = @PropertyValues + isnull(@Cell,'')
if @Length > 0
    select @Position = @Position + @Length

return @PropertyNames

END

CREATE   Function GetProfileValues(
@Website            varchar(50),
@TimeZone            varchar(5),
@PreferredLocale        varchar(25),
@Region            varchar(50),
@FirstName            varchar(25),
@PostalCode            varchar(15),
@Street            varchar(100),
@LastName            varchar(25),
lt;SPAN style="COLOR: #008000">@Unit                varchar(100),
@Telephone            varchar(25),
@Country            varchar(50),
@IM                varchar(50),
@Fax                varchar(25),
@City                varchar(50),
@Cell                varchar(25))
RETURNS varchar(2000)
AS
BEGIN

Declare @CONST varchar(2000)
Select @CONST =