SQL GetProfileElement - migrating sqlmembership provider to identity 2.0

Question

My project is MVC5, I am trying to import users data from SqlMembership Provider to Identity 2.0. I am using:

dbo.fn_GetProfileElement('FirstName',Prfl.PropertyNames,Prfl.PropertyValuesString) FirstName,

I get the following error:

Cannot insert the value NULL into column 'FirstName', table 'bcrs_new.dbo.AspNetUsers'; column does not allow nulls. INSERT fails.

Here is the function:

ALTER FUNCTION [dbo].[fn_GetProfileElement]
(
@fieldName AS NVARCHAR(100),
@fields AS NVARCHAR(4000),
@values AS NVARCHAR(4000))

RETURNS NVARCHAR(4000)
AS
BEGIN
  -- If input is invalid, return null.
  IF @fieldName IS NULL
      OR LEN(@fieldName) = 0
      OR @fields IS NULL
      OR LEN(@fields) = 0
      OR @values IS NULL
      OR LEN(@values) = 0

    RETURN NULL

-- locate FieldName in Fields
DECLARE @fieldNameToken AS NVARCHAR(20)
DECLARE @fieldNameStart AS INTEGER,
@valueStart AS INTEGER,
@valueLength AS INTEGER

-- Only handle string type fields (:S:)
SET @fieldNameStart = CHARINDEX(@fieldName + ':S',@Fields,0)

-- If field is not found, return null
IF @fieldNameStart = 0 RETURN NULL
SET @fieldNameStart = @fieldNameStart + LEN(@fieldName) + 3

-- Get the field token which I've defined as the start of the
-- field offset to the end of the length
SET @fieldNameToken = SUBSTRING(@Fields,@fieldNameStart,LEN(@Fields)-@fieldNameStart)

-- Get the values for the offset and length
SET @valueStart = dbo.fn_getelement(1,@fieldNameToken,':')
SET @valueLength = dbo.fn_getelement(2,@fieldNameToken,':')

-- Check for sane values, 0 length means the profile item was
-- stored, just no data
IF @valueLength = 0 RETURN ''

-- Return the string
RETURN SUBSTRING(@values, @valueStart+1, @valueLength)

END

I would appreciate your suggestions on how to check if the FirstName is null insert "NA".

Answer 1

As trailmax noted, the problem is that you are trying to insert a null into a column that will not accept nulls. There are two possible solutions. One is to modify the table to allow nulls. If the table has data in it already, there may be issues with this. Plus you are probably the software developer, not the data architect. So it may not be feasible.

I like using the isnull() function in SQL, which is roughly equivalent to the null coallescing operator (??) in C#. isnull(parm1, parm2) will return parm1 if it is not null, otherwise it will return parm 2.

An Example:

declare @myVar int
set @myVar  = null
select isnull(@myVar, 1)    
-- Returns 1
set @myVar  = 2
select isnull(@myVar, 1)   
-- Returns 2 

So applying this to you code snippet:

isnull(dbo.fn_GetProfileElement('FirstName',Prfl.PropertyNames,Prfl.PropertyValuesString),'NA') FirstName

(Editted this after the fact for a better explanation)

Answer 2

As the error says, that column does not allow empty values but you are trying to insert null there. So you need to change that column to allow to insert nulls.

If this table was generated by EF for you, then in your model you need to remove [Required] from property FirstName. Then create a new migration that modifies the DB state and try your script again.

If that table was not created by EF for you, you need to modify the column to allow nulls:

ALTER TABLE bcrs_new.dbo.AspNetUsers
ALTER COLUMN FirstName nvarchar(max) NULL