SQL aspnet_profile [英] SQL aspnet_profile

查看:76
本文介绍了SQL aspnet_profile的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

由于我想在Telerik Reporting中将其用作用户参数,因此我如何使用SQL从基于UserID的aspnet_profile表中获取用户的名字和姓氏.

any idea how I can get user FirstName and LastName from the aspnet_profile table based on UserID using SQL becasue I would like to use in Telerik Reporting as a user parameter.

示例行(名字是George,姓氏是Test):

Sample row (FirstName is George, LastName is 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

推荐答案

如果您坚持使用SQL,我敢肯定会有大量

If you insist on using SQL, I'm sure a large number of SUBSTRINGs and PATINDEXes will get you there but it won't be a clean solution.

更新: user373721 找到了不错的资源并发表了评论,但是容易被错过,所以我决定也将其添加到答案中-

Update: user373721 found a great resource and posted a comment about it, but it can be easily missed, so I decided to add it to the answer, too - How to get asp.net profile value from MS SQL database using T-SQL?

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

The built-in dbo.aspnet_Profile_GetProperties stored procedure returns the PropertyValuesString value that is later parsed in the ParseDataFromDB function.

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
        {
        }
    }
}

这篇关于SQL aspnet_profile的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆