SQL aspnet_profile

Question

知道如何使用 SQL 从基于 UserID 的 aspnet_profile 表中获取用户名字和姓氏，因为我想在 Telerik Reporting 中使用作为用户参数。

示例行（名字是 George，姓氏是 Test）：

UserID: 06b24b5c-9aa1-426e-b7e4-0771c5f85e85

PropertyName: MobilePhone:S:0:0:Initials:S:0:1:City:S:1:14:FirstName:S:15:6:PostalCode:S:21:7:‌​WorkPhone:S:28:12:LastName:S:40:5:Address1:S:45:17:Address2:S:62:0:Province:S:62:‌​2:Organization:S:64:4:ClinicId:S:68:1:Country:S:69:6:Fax:S:75:0:MSPNumber:S:75:0:‌​ 

PropertyValuesString: HEast HustonEASGeorgeT7D 1N8604-111-2222Test5555 Beddtvue AveDCHCNL2Canada

PropertyValuesBinary: <Binary data>

LastUpdateDate: 2010-01-02 22:22:03.947

Answer 1

如果你坚持使用 SQL，我确信有大量 SUBSTRINGs 和 PATINDEXes 可以帮助您实现这一目标，但它不是一个干净的解决方案。

更新： user373721 找到了一个很棒的资源并发表了评论，但它可以很容易地错过了，所以我决定也将其添加到答案中 - 如何获取 asp.net使用 T-SQL 从 MS SQL 数据库获取配置文件值？

内置 dbo.aspnet_Profile_GetProperties 存储过程返回稍后在 ParseDataFromDB 中解析的 PropertyValuesString 值
功能。

private void GetPropertyValuesFromDatabase(string userName, SettingsPropertyValueCollection svc)
{
    if (HostingEnvironment.IsHosted && EtwTrace.IsTraceEnabled(4, 8))
    {
        EtwTrace.Trace(EtwTraceType.ETW_TYPE_PROFILE_BEGIN, HttpContext.Current.WorkerRequest);
    }
    HttpContext current = HttpContext.Current;
    string[] names = null;
    string values = null;
    byte[] buffer = null;
    if (current != null)
    {
        if (!current.Request.IsAuthenticated)
        {
            string anonymousID = current.Request.AnonymousID;
        }
        else
        {
            string name = current.User.Identity.Name;
        }
    }
    try
    {
        SqlConnectionHolder connection = null;
        SqlDataReader reader = null;
        try
        {
            connection = SqlConnectionHelper.GetConnection(this._sqlConnectionString, true);
            this.CheckSchemaVersion(connection.Connection);
            SqlCommand command = new SqlCommand("dbo.aspnet_Profile_GetProperties", connection.Connection) {
                CommandTimeout = this.CommandTimeout,
                CommandType = CommandType.StoredProcedure
            };
            command.Parameters.Add(this.CreateInputParam("@ApplicationName", SqlDbType.NVarChar, this.ApplicationName));
            command.Parameters.Add(this.CreateInputParam("@UserName", SqlDbType.NVarChar, userName));
            command.Parameters.Add(this.CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));
            reader = command.ExecuteReader(CommandBehavior.SingleRow);
            if (reader.Read())
            {
                names = reader.GetString(0).Split(new char[] { ':' });
                values = reader.GetString(1);
                int length = (int) reader.GetBytes(2, 0L, null, 0, 0);
                buffer = new byte[length];
                reader.GetBytes(2, 0L, buffer, 0, length);
            }
        }
        finally
        {
            if (connection != null)
            {
                connection.Close();
                connection = null;
            }
            if (reader != null)
            {
                reader.Close();
            }
        }
        ProfileModule.ParseDataFromDB(names, values, buffer, svc);
        if (HostingEnvironment.IsHosted && EtwTrace.IsTraceEnabled(4, 8))
        {
            EtwTrace.Trace(EtwTraceType.ETW_TYPE_PROFILE_END, HttpContext.Current.WorkerRequest, userName);
        }
    }
    catch
    {
        throw;
    }
}

 

internal static void ParseDataFromDB(string[] names, string values, byte[] buf, SettingsPropertyValueCollection properties)
{
    if (((names != null) && (values != null)) && ((buf != null) && (properties != null)))
    {
        try
        {
            for (int i = 0; i < (names.Length / 4); i++)
            {
                string str = names[i * 4];
                SettingsPropertyValue value2 = properties[str];
                if (value2 != null)
                {
                    int startIndex = int.Parse(names[(i * 4) + 2], CultureInfo.InvariantCulture);
                    int length = int.Parse(names[(i * 4) + 3], CultureInfo.InvariantCulture);
                    if ((length == -1) && !value2.Property.PropertyType.IsValueType)
                    {
                        value2.PropertyValue = null;
                        value2.IsDirty = false;
                        value2.Deserialized = true;
                    }
                    if (((names[(i * 4) + 1] == "S") && (startIndex >= 0)) && ((length > 0) && (values.Length >= (startIndex + length))))
                    {
                        value2.SerializedValue = values.Substring(startIndex, length);
                    }
                    if (((names[(i * 4) + 1] == "B") && (startIndex >= 0)) && ((length > 0) && (buf.Length >= (startIndex + length))))
                    {
                        byte[] dst = new byte[length];
                        Buffer.BlockCopy(buf, startIndex, dst, 0, length);
                        value2.SerializedValue = dst;
                    }
                }
            }
        }
        catch
        {
        }
    }
}

Answer 2

http://www .karpach.com/Get-asp-net-profile-value-MS-SQL-database-using-T-SQL.htm

这对我帮助很大！

按照该链接中的步骤，我可以从 aspnet_profile 表中获取 PropertyValueString 中的任何特定数据。

复制和粘贴-
第一个功能：

CREATE FUNCTION dbo.fn_GetElement
(
@ord AS INT,
@str AS VARCHAR(8000),
@delim AS VARCHAR(1) )

RETURNS INT
AS
BEGIN
  -- If input is invalid, return null.
  IF @str IS NULL
      OR LEN(@str) = 0
      OR @ord IS NULL
      OR @ord < 1
      -- @ord > [is the] expression that calculates the number of elements.
      OR @ord > LEN(@str) - LEN(REPLACE(@str, @delim, '')) + 1
    RETURN NULL
  DECLARE @pos AS INT, @curord AS INT
  SELECT @pos = 1, @curord = 1
  -- Find next element's start position and increment index.
  WHILE @curord < @ord
    SELECT
      @pos    = CHARINDEX(@delim, @str, @pos) + 1,
      @curord = @curord + 1
  RETURN
  CAST(SUBSTRING(@str, @pos, CHARINDEX(@delim, @str + @delim, @pos) - @pos) AS INT)
END

第二个功能：

CREATE 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

SQL 查询可以根据您的需要进行修改

SELECT dbo.fn_GetProfileElement('FirstName',PropertyNames,PropertyValuesString)
     , dbo.fn_GetProfileElement('LastName',PropertyNames,PropertyValuesString)
FROM aspnet_Profile

Answer 3

对于那些仍在寻找使用纯 SQL 解析 aspnet_Profile 表的方法的人。这是我使用的：

首先你需要一个理货表。如果您不知道这是什么，请阅读 Jeff Moden 的这篇文章： http:// www.sqlservercentral.com/articles/T-SQL/62867/

要生成统计表，请使用以下脚本：

SELECT TOP 11000 IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.dbo.SysColumns sc1,  Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Let the public use it
GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC

现在开始解析 ProfileData：
在对我的特定数据进行大量测试后，我发现以下过程是最快的方法。我已经测试过一次性解析整个表，但这比使用下面的函数并使用 CROSS APPLY 一次解析一个用户要慢。

因此，要调用该函数，请使用以下内容：

SELECT bla, bla
FROM aspnet_Users u CROSS APPY dbo.ProfileProperties(u.UserID)

您唯一需要做的就是更新 3 个内容以包含您使用的配置文件属性：
1）返回表
2) PIVOT 语句，以及
3) insert 语句将数据从 PIVOT 复制到返回表

这是该函数，享受吧！

/** =============================================
** Author:      Francois Grobler
** Create date: 2013-04-25
** Description: This function extracts all 
**    Profile Properties for a given UserId, 
**    and returns them as a table
** Change History: 
** Date:  Author:  Change:  
** 
** ============================================= **/
CREATE FUNCTION dbo.ProfileProperties
(
    @UserID UNIQUEIDENTIFIER
)
RETURNS @returnTable TABLE(
            FirstName nvarchar(200)
            , LastName nvarchar(200)
            , PassportNumber nvarchar(100)
            , PositionCode int
            , CellNumber nvarchar(20)
            , Telephone nvarchar(30)
            , FaxNumber nvarchar(20)
            , Email nvarchar(200)
            , PersalNumber nvarchar(10)
            , SouthAfricanIdentityNumber nchar(13)
            , ContractNumber nvarchar(20)
            , DepartmentName nvarchar(200)
            , SiteName nvarchar(200)
            , DepartmentCode int
            , SiteCode int
            , UserAccessCode int
            , ApproverCode int
                        )
WITH SCHEMABINDING
AS
BEGIN
    WITH Properties(PropertyNo, PropertyType, UserId, Value)
    AS
    (
            SELECT (ROW_NUMBER() OVER(ORDER BY UserId) - 1) / 4 PropertyNo
            , (ROW_NUMBER() OVER(PARTITION BY p.UserId ORDER BY UserId) - 1) % 4 PropertyType
            , p.UserId
            , SUBSTRING(':' + CONVERT(nvarchar(4000), p.PropertyNames), n + 1, CHARINDEX(':', ':' + CONVERT(nvarchar(4000), p.PropertyNames), n + 1) - n - 1) Value
            FROM dbo.Tally, dbo.aspnet_Profile p
            WHERE n < LEN(':' + CONVERT(nvarchar(4000), p.PropertyNames))
                and SUBSTRING(':' + CONVERT(nvarchar(4000), p.PropertyNames), n, 1) = ':'
                and p.UserId = @UserID
    )
    , FlatProperties(UserId, Property, ValueType, StartIndex, ValueLength)
    AS
    (
        SELECT UserId
        , MAX(CASE WHEN PropertyType = 0 THEN Value ELSE '' END) Property
        , MAX(CASE WHEN PropertyType = 1 THEN Value ELSE '' END) ValueType
        , MAX(CASE WHEN PropertyType = 2 THEN CONVERT(int, Value) + 1 ELSE 0 END) StartIndex
        , MAX(CASE WHEN PropertyType = 3 THEN CONVERT(int, Value) ELSE 0 END) ValueLength
        FROM
            Properties
        GROUP BY UserID, PropertyNo
    )
    , PropertyValues(UserID, PropertyName, PropertyValue)
    AS
    (
        SELECT p.UserID, fp.Property
        , CASE fp.ValueType
            WHEN 'S' THEN SUBSTRING(p.PropertyValuesString, fp.StartIndex, fp.ValueLength)
            ELSE SUBSTRING(p.PropertyValuesBinary, fp.StartIndex, fp.ValueLength) END Value
        FROM dbo.aspnet_Profile p INNER JOIN flatProperties fp ON p.UserId = fp.UserId
        WHERE p.UserId = @UserID
    )
    , PropertyTable
    AS
    (
        SELECT 
            UserID
            , pvt.[FirstName]
            , pvt.[LastName]
            , pvt.[PassportNumber]
            , pvt.[PositionCode]
            , pvt.[CellNumber]
            , pvt.[Telephone]
            , pvt.[FaxNumber]
            , pvt.[Email]
            , pvt.[PersalNumber]
            , pvt.[SouthAfricanIdentityNumber]
            , pvt.[ContractNumber]
            , pvt.[DepartmentName]
            , pvt.[SiteName]
            , pvt.[DepartmentCode]
            , pvt.[SiteCode]
            , pvt.[UserCode] UserAccessCode
            , pvt.[ApproverCode]
        FROM PropertyValues
         PIVOT (
         MAX(PropertyValue) FOR PropertyName IN ([FirstName],[LastName],[PassportNumber],[PositionCode],[CellNumber],[Telephone],[FaxNumber],[Email],[PersalNumber],[SouthAfricanIdentityNumber],[ContractNumber],[DepartmentName],[SiteName],[DepartmentCode],[SiteCode],[UserCode],[ApproverCode]) 
             ) AS pvt
    )
    INSERT INTO @returnTable
    (
        FirstName
        , LastName
        , PassportNumber
        , PositionCode
        , CellNumber
        , Telephone
        , FaxNumber
        , Email
        , PersalNumber
        , SouthAfricanIdentityNumber
        , ContractNumber
        , DepartmentName
        , SiteName
        , DepartmentCode
        , SiteCode
        , UserAccessCode
        , ApproverCode
    )
    SELECT TOP 1
        FirstName
        , LastName
        , PassportNumber
        , PositionCode
        , CellNumber
        , Telephone
        , FaxNumber
        , Email
        , PersalNumber
        , SouthAfricanIdentityNumber
        , ContractNumber
        , DepartmentName
        , SiteName
        , DepartmentCode
        , SiteCode
        , UserAccessCode
        , ApproverCode
    FROM PropertyTable;

    RETURN;
END
GO