0

I tried to query the attributes of profiles table in asp.net website according to link. first, i defined a CLR function:

using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Linq;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

/// <summary>
/// Summary description for UserDefinedFunctions
/// </summary>
public class UserDefinedFunctions
{
    private static readonly Regex ProfileRegex = new Regex(@"([a-zA-Z]+):[A-Z]:(\d+):(\d+)");

[SqlFunction(FillRowMethodName = "FillProfileRow", TableDefinition = "Property nvarchar(250), Value nvarchar(2000)")]
public static IEnumerable ParseProfileString(SqlString names, SqlString values)
{
    var dict = ProfileRegex
        .Matches(names.Value)
        .Cast<Match>()
        .ToDictionary(
            x => x.Groups[1].Value,
            x => values.Value.Substring(int.Parse(x.Groups[2].Value), int.Parse(x.Groups[3].Value)));

    return dict;
}

public static void FillProfileRow(object obj, out string Property, out string Value)
{
    var x = (KeyValuePair<string, string>)obj;
    Property = x.Key;
    Value = x.Value;
}
}

but when I tried to create a view I got an error that the CLR function (ParseProfileString) is an unresolved reference.

CREATE VIEW UsersView
AS
SELECT *
FROM (
    SELECT u.UserId
        ,u.Username
        ,m.Email
        ,f.Property
        ,f.Value
     FROM aspnet_Profile p
    INNER JOIN aspnet_Users u ON p.UserId = u.UserId
    INNER JOIN aspnet_Membership m ON m.UserId = u.Userid
    INNER JOIN aspnet_Applications a ON a.ApplicationId = m.ApplicationId
    CROSS APPLY ParseProfileString(p.PropertyNames, p.PropertyValuesString) f
    WHERE a.ApplicationName = 'MyApplication'
    ) src
pivot(min(value) FOR property IN (
        -- list your profile property names here
        FirstName, LastName, BirthDate
        )) pvt
4
  • This doesn't sound like it has anything to do with the C# code. What is the unresolved reference? When you try to execute this view code manually as a query, what happens? Commented Jun 30, 2016 at 12:13
  • Have you deployed the ParseProfileString method to your SQL DB? Commented Jun 30, 2016 at 12:35
  • @TZHX How can I deploy it to my DB? i am almost a beginner. I compiled 'ParseProfileString' and I,ve added the 'dll' file to DB assemblies. should I do anything more? Commented Jun 30, 2016 at 13:46
  • @David when I executed it as a query, it resulted in error: Invalid object name 'ParseProfileString' Commented Jun 30, 2016 at 14:28

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.