////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
// customProviderData = "Varname;SqlDbType;size"
private void GetProfileDataFromSproc(SettingsPropertyCollection properties, SettingsPropertyValueCollection svc, string username, SqlConnection conn, bool userIsAuthenticated)
{
SqlCommand cmd = CreateSprocSqlCommand(_readSproc, conn, username, userIsAuthenticated);
try {
cmd.Parameters.RemoveAt("@IsUserAnonymous"); //anonymous flag not needed on get
List <ProfileColumnData> columnData = new List <ProfileColumnData>(properties.Count);
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 != 3)
{
// REVIEW: Perhaps we should throw instead?
continue;
}
string varname = chunk[0];
// REVIEW: Should we ignore case?
SqlDbType datatype = (SqlDbType)Enum.Parse(typeof(SqlDbType), chunk[1], true);
int size = 0;
if (!Int32.TryParse(chunk[2], out size))
{
throw new ArgumentException("Unable to parse as integer: " + chunk[2]);
}
columnData.Add(new ProfileColumnData(varname, value, null /* not needed for get */, datatype));
cmd.Parameters.Add(CreateOutputParam(varname, datatype, size));
}
cmd.ExecuteNonQuery();
for (int i = 0; i < columnData.Count; ++i)
{
ProfileColumnData colData = columnData[i];
object val = cmd.Parameters[colData.VariableName].Value;
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;
}
}
}
finally {
cmd.Dispose();
}
}
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.MEMBER_ID");
SqlCommand cmd = new SqlCommand(String.Empty, conn);
int columnCount = 0;
foreach (SettingsProperty prop in properties)
{
SettingsPropertyValue value = new SettingsPropertyValue(prop);
if (prop.PropertyType == typeof(List <SnitzLink>))
{
prop.ThrowOnErrorDeserializing = true;
prop.SerializeAs = SettingsSerializeAs.Xml;
value.Deserialized = false;
}
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, prop, null /* not needed for get */, datatype));
commandText.Append(", ");
commandText.Append("t." + columnName);
++columnCount;
}
commandText.AppendFormat(" FROM {0} t, {1}MEMBERS u WHERE ", TableName, Config.MemberTablePrefix).AppendLine();
commandText.Append("u.M_NAME = @Username AND t.UserID = u.MEMBER_ID");
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())
{
svc.Clear();
int userId = reader.GetInt32(0);
for (int i = 0; i < columnData.Count; ++i)
{
object val = reader.GetValue(i + 1);
ProfileColumnData colData = columnData[i];
SettingsPropertyValue propValue = new SettingsPropertyValue(colData.PropertyValue);
//Only initialize a SettingsPropertyValue for non-null values
//if (!(val is DBNull || val == null))
//{
propValue.IsDirty = false;
if (propValue.Property.SerializeAs == SettingsSerializeAs.Xml)
{
propValue.Deserialized = false;
object test = "";
if (!val.Equals(test))
{
propValue.SerializedValue = val;
}
}
else
{
propValue.PropertyValue = val;
propValue.Deserialized = true;
}
svc.Add(propValue);
//}
}
// need to close reader before we try to update the user
if (reader != null)
{
reader.Close();
reader = null;
}
//UpdateLastActivityDate(conn, userId);
}
else
{
object val = GetBookMarkModValues(username);
ProfileColumnData colData = columnData.Find(c => c.ColumnName == "BookMarks");
SettingsPropertyValue propValue = new SettingsPropertyValue(colData.PropertyValue);
propValue.IsDirty = false;
if (propValue.Property.SerializeAs == SettingsSerializeAs.Xml)
{
if (propValue.Name == "BookMarks")
{
svc.Remove("BookMarks");
propValue.Deserialized = false;
object test = "";
if (!val.Equals(test))
{
propValue.SerializedValue = val;
}
svc.Add(propValue);
}
}
}
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
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;
}
commandText.Append(" FROM " + _table + " t, 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;
}
}
}
finally {
if (reader != null)
{
reader.Close();
}
}
}
private SqlCommand CreateApplicationSqlCommand(SqlCommand cmd, ProfileColumnData data)
{
cmd.Parameters.AddWithValue(data.VariableName, data.Value);
cmd.Parameters[data.VariableName].SqlDbType = data.DataType;
return cmd;
}
