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，我肯定大量SUBSTRING和PATINDEX es 可以帮助您实现这一目标，但它不是一个干净的解决方案。

更新: user373721找到了一个很棒的资源并发布了关于它的评论，但它很容易被错过，所以我决定将它也添加到答案中 - How to get asp.net profile value from MS SQL database using T-SQL?

内置dbo.aspnet_Profile_GetProperties stored procedure返回稍后在 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
        {
        }
    }
}