任何想法,因为我想在Telerik Reporting中用作用户参数,因此如何使用SQL从基于UserID的aspnet_profile表中获取用户的名字和姓氏。
示例行(名字是乔治,名字是测试):
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,我相信大量的SUBSTRING和PATINDEX会帮助您实现目标,但这并不是一个干净的解决方案。
更新: user373721找到了不错的资源并发表了评论,但很容易错过,因此我决定也将其添加到答案中-How to get asp.net profile value from MS SQL database using T-SQL?
内置的 dbo.aspnet_Profile_GetProperties
stored procedure返回PropertyValuesString
值,该值稍后会在ParseDataFromDB
中进行解析
功能。
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,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/7315284/