如果你坚持使用 SQL,我确信大量子串 http://msdn.microsoft.com/en-us/library/ms187748.aspxs and PATINDEX http://msdn.microsoft.com/en-us/library/ms188395.aspxes 会带你到达那里,但它不会是一个干净的解决方案。
Update: 用户373721 https://stackoverflow.com/users/373721/user373721找到了一个很棒的资源并发布了关于它的评论,但它很容易被错过,所以我决定也将其添加到答案中 -如何使用 T-SQL 从 MS SQL 数据库获取 asp.net 配置文件值? http://www.karpach.com/Get-asp-net-profile-value-MS-SQL-database-using-T-SQL.htm
内置的dbo.aspnet_Profile_GetProperties存储过程 https://techwriter.me/samples/database/aspnetdb/aspnetdb.html#PR-ASPNETDB-aspnet_Profile_GetProperties返回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
{
}
}
}