>private void GetProfileDataFromTable(SettingsPropertyCollection properties, SettingsPropertyValueCollection svc, string username, SqlConnection conn) { > List<ProfileColumnData> columnData = new List<ProfileColumnData>(properties.Count); > StringBuilder commandText = new StringBuilder("SELECT u.UserID"); > SqlCommand cmd = new SqlCommand(String.Empty, conn); > > int columnCount = 0; > foreach (SettingsProperty prop in properties) { > SettingsPropertyValue value = new SettingsPropertyValue(prop); > svc.Add(value); > > string persistenceData = prop.Attributes["CustomProviderData"] as string; > // If we can't find the table/column info we will ignore this data > if (String.IsNullOrEmpty(persistenceData)) { > // REVIEW: Perhaps we should throw instead? > continue; > } > string[] chunk = persistenceData.Split(new char[] { ';' }); > if (chunk.Length != 2) { > // REVIEW: Perhaps we should throw instead? > continue; > } > string columnName = chunk[0]; > // REVIEW: Should we ignore case? > SqlDbType datatype = (SqlDbType)Enum.Parse(typeof(SqlDbType), chunk[1], true); > > columnData.Add(new ProfileColumnData(columnName, value, null /* not needed for get */, datatype)); > commandText.Append(", "); > commandText.Append("t."+columnName); > ++columnCount; > } > //Added Nolock for speed Ephraim > commandText.Append(" FROM "+_table+" AS t WITH (NOLOCK), vw_aspnet_Users u WHERE u.ApplicationId = '").Append(AppId); > commandText.Append("' AND u.UserName = LOWER(@Username) AND t.UserID = u.UserID"); > cmd.CommandText = commandText.ToString(); > cmd.CommandType = CommandType.Text; > cmd.Parameters.AddWithValue("@Username", username); > SqlDataReader reader = null; > > try { > reader = cmd.ExecuteReader(); > //If no row exists in the database, then the default Profile values > //from configuration are used. > if (reader.Read()) { > Guid userId = reader.GetGuid(0); > for (int i = 0; i < columnData.Count; ++i) { > object val = reader.GetValue(i+1); > ProfileColumnData colData = columnData[i]; > SettingsPropertyValue propValue = colData.PropertyValue; > > //Only initialize a SettingsPropertyValue for non-null values > if (!(val is DBNull || val == null)) > { > propValue.PropertyValue = val; > propValue.IsDirty = false; > propValue.Deserialized = true; > } > } > > // need to close reader before we try to update the user > if (reader != null) { > reader.Close(); > reader = null; > } > > UpdateLastActivityDate(conn, userId); > > } > } > finally { > if (reader != null) { > reader.Close(); > } > } > } > > private static void UpdateLastActivityDate(SqlConnection conn, Guid userId) { > SqlCommand cmd = new SqlCommand("UPDATE aspnet_Users SET LastActivityDate = @LastUpdatedDate WHERE UserId = '" + userId + "'", conn); > cmd.CommandType = CommandType.Text; > cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow); > try { > cmd.ExecuteNonQuery(); > } > finally { > cmd.Dispose(); > } > } >>