using System;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Configuration;
using System.Configuration.Provider;
using System.Data;
using System.Data.SQLite;
using System.Globalization;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Web.Profile;
using System.Xml.Serialization;
namespace GalleryServerPro.Data.SQLite
{
public sealed class SQLiteProfileProvider : ProfileProvider
{
#region Private Fields
private static string _applicationName;
private static string _connectionString;
private static string _applicationId;
private static string _description;
private const string _httpTransactionId = "SQLiteTran";
private const string USER_TB_NAME = "[aspnet_Users]";
private const string PROFILE_TB_NAME = "[aspnet_Profile]";
private const string APP_TB_NAME = "[aspnet_Applications]";
private const int MAX_USERNAME_LENGTH = 256;
#endregion
#region Public Properties
public override string ApplicationName
{
get { return _applicationName; }
set
{
if (value.Length > 256)
throw new ProviderException("ApplicationName exceeds 256 characters.");
_applicationName = value;
_applicationId = GetApplicationId(_applicationName);
}
}
#endregion
#region Public Methods
public override void Initialize(string name, NameValueCollection config)
{
if (config == null)
throw new ArgumentNullException("config");
if (string.IsNullOrEmpty(name))
name = "SQLiteProfileProvider";
if (string.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "SQLite Profile Provider");
}
base.Initialize(name, config);
// Initialize SQLiteConnection.
ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];
if (ConnectionStringSettings == null || String.IsNullOrEmpty(ConnectionStringSettings.ConnectionString))
{
throw new ProviderException("Connection String is empty for SQLiteProfileProvider");
}
_connectionString = ConnectionStringSettings.ConnectionString;
// Get the Application Name from Config
string appName = config["applicationName"];
if (string.IsNullOrEmpty(appName))
appName = String.Empty;
if (appName.Length > 256)
throw new ProviderException("SQLiteMembershipProvider error: applicationName must be less than or equal to 256 characters.");
_applicationName = appName;
// Get the Description
_description = config["description"];
// Check for invalid parameters in the config //
config.Remove("connectionStringName");
config.Remove("applicationName");
config.Remove("description");
if (config.Count > 0)
{
string attribUnrecognized = config.GetKey(0);
if (!String.IsNullOrEmpty(attribUnrecognized))
throw new ProviderException("Unrecognized attribute: " + attribUnrecognized);
}
_applicationId = GetApplicationId(_applicationName);
VerifyApplication();
}
public override SettingsPropertyValueCollection GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties)
{
SettingsPropertyValueCollection svc = new SettingsPropertyValueCollection();
if (properties.Count < 1)
return svc;
string username = (string)sc["UserName"];
foreach (SettingsProperty prop in properties)
{
if (prop.SerializeAs == SettingsSerializeAs.ProviderSpecific)
if (prop.PropertyType.IsPrimitive || prop.PropertyType == typeof(string))
prop.SerializeAs = SettingsSerializeAs.String;
else
prop.SerializeAs = SettingsSerializeAs.Xml;
svc.Add(new SettingsPropertyValue(prop));
}
if (!String.IsNullOrEmpty(username))
{
GetPropertyValuesFromDatabase(username, svc);
}
return svc;
}
public override void SetPropertyValues(SettingsContext sc, SettingsPropertyValueCollection properties)
{
string username = (string)sc["UserName"];
bool userIsAuthenticated = (bool)sc["IsAuthenticated"];
if (string.IsNullOrEmpty(username) || properties.Count < 1)
return;
string names = String.Empty;
string values = String.Empty;
byte[] buf = null;
PrepareDataForSaving(ref names, ref values, ref buf, false, properties, userIsAuthenticated);
if (names.Length == 0)
return;
SQLiteTransaction tran = null;
SQLiteConnection cn = GetDBConnectionForProfile();
try
{
if (cn.State == ConnectionState.Closed)
cn.Open();
if (!IsTransactionInProgress())
tran = cn.BeginTransaction();
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT UserId FROM " + USER_TB_NAME + " WHERE LoweredUsername = $Username AND ApplicationId = $ApplicationId;";
cmd.Parameters.AddWithValue("$Username", username.ToLowerInvariant());
cmd.Parameters.AddWithValue("$ApplicationId", _applicationId);
string userId = cmd.ExecuteScalar() as string;
if ((userId == null) && (userIsAuthenticated))
return; // User is logged on but no record exists in user table. This should never happen, but if it doesn, just exit.
if (userId == null)
{
// User is anonymous and no record exists in user table. Add it.
userId = Guid.NewGuid().ToString();
CreateAnonymousUser(username, cn, tran, userId);
}
cmd.CommandText = "SELECT COUNT(*) FROM " + PROFILE_TB_NAME + " WHERE UserId = $UserId";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("$UserId", userId);
if (Convert.ToInt64(cmd.ExecuteScalar()) > 0)
{
cmd.CommandText = "UPDATE " + PROFILE_TB_NAME + " SET PropertyNames = $PropertyNames, PropertyValuesString = $PropertyValuesString, LastUpdatedDate = $LastUpdatedDate WHERE UserId = $UserId";
}
else
{
cmd.CommandText = "INSERT INTO " + PROFILE_TB_NAME + " (UserId, PropertyNames, PropertyValuesString, LastUpdatedDate) VALUES ($UserId, $PropertyNames, $PropertyValuesString, $LastUpdatedDate)";
}
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("$UserId", userId);
cmd.Parameters.AddWithValue("$PropertyNames", names);
cmd.Parameters.AddWithValue("$PropertyValuesString", values);
cmd.Parameters.AddWithValue("$LastUpdatedDate", DateTime.UtcNow);
cmd.ExecuteNonQuery();
// Update activity field
cmd.CommandText = "UPDATE " + USER_TB_NAME + " SET LastActivityDate = $LastActivityDate WHERE UserId = $UserId";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("$LastActivityDate", DateTime.UtcNow);
cmd.Parameters.AddWithValue("$UserId", userId);
cmd.ExecuteNonQuery();
if (tran != null)
tran.Commit();
}
}
catch
{
if (tran != null)
tran.Rollback();
throw;
}
finally
{
if (tran != null)
tran.Dispose();
if (!IsTransactionInProgress())
cn.Dispose();
}
}
public override int DeleteProfiles(ProfileInfoCollection profiles)
{
if (profiles == null)
throw new ArgumentNullException("profiles");
if (profiles.Count < 1)
throw new ArgumentException("Profiles collection is empty", "profiles");
int numDeleted = 0;
SQLiteTransaction tran = null;
SQLiteConnection cn = GetDBConnectionForProfile();
try
{
if (cn.State == ConnectionState.Closed)
cn.Open();
if (!IsTransactionInProgress())
tran = cn.BeginTransaction();
foreach (ProfileInfo profile in profiles)
{
if (DeleteProfile(cn, tran, profile.UserName.Trim()))
numDeleted++;
}
// Commit the transaction if it's the one we created in this method.
if (tran != null)
tran.Commit();
}
catch
{
if (tran != null)
tran.Rollback();
throw;
}
finally
{
if (tran != null)
tran.Dispose();
if (!IsTransactionInProgress())
cn.Dispose();
}
return numDeleted;
}
public override int DeleteProfiles(string[] usernames)
{
int numDeleted = 0;
SQLiteTransaction tran = null;
SQLiteConnection cn = GetDBConnectionForProfile();
try
{
if (cn.State == ConnectionState.Closed)
cn.Open();
if (!IsTransactionInProgress())
tran = cn.BeginTransaction();
foreach (string username in usernames)
{
if (DeleteProfile(cn, tran, username))
numDeleted++;
}
// Commit the transaction if it's the one we created in this method.
if (tran != null)
tran.Commit();
}
catch
{
if (tran != null)
tran.Rollback();
throw;
}
finally
{
if (tran != null)
tran.Dispose();
if (!IsTransactionInProgress())
cn.Dispose();
}
return numDeleted;
}
public override int DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
{
SQLiteConnection cn = GetDBConnectionForProfile();
try
{
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "DELETE FROM " + PROFILE_TB_NAME + " WHERE UserId IN (SELECT UserId FROM " + USER_TB_NAME
+ " WHERE ApplicationId = $ApplicationId AND LastActivityDate <= $LastActivityDate"
+ GetClauseForAuthenticationOptions(authenticationOption) + ")";
cmd.Parameters.AddWithValue("$ApplicationId", _applicationId);
cmd.Parameters.AddWithValue("$LastActivityDate", userInactiveSinceDate);
if (cn.State == ConnectionState.Closed)
cn.Open();
return cmd.ExecuteNonQuery();
}
}
finally
{
if (!IsTransactionInProgress())
cn.Dispose();
}
}
public override int GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
{
SQLiteConnection cn = GetDBConnectionForProfile();
try
{
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(*) FROM " + USER_TB_NAME + " u, " + PROFILE_TB_NAME + " p " +
"WHERE u.ApplicationId = $ApplicationId AND u.LastActivityDate <= $LastActivityDate AND u.UserId = p.UserId" + GetClauseForAuthenticationOptions(authenticationOption);
if (cn.State == ConnectionState.Closed)
cn.Open();
cmd.Parameters.AddWithValue("$ApplicationId", _applicationId);
cmd.Parameters.AddWithValue("$LastActivityDate", userInactiveSinceDate);
return cmd.ExecuteNonQuery();
}
}
finally
{
if (!IsTransactionInProgress())
cn.Dispose();
}
}
public override ProfileInfoCollection GetAllProfiles(ProfileAuthenticationOption authenticationOption, int pageIndex, int pageSize, out int totalRecords)
{
string sqlQuery = "SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate, LEN(p.PropertyNames) + LEN(p.PropertyValuesString) FROM "
+ USER_TB_NAME + " u, " + PROFILE_TB_NAME + " p WHERE u.ApplicationId = $ApplicationId AND u.UserId = p.UserId "
+ GetClauseForAuthenticationOptions(authenticationOption);
SQLiteParameter prm = new SQLiteParameter("$ApplicationId", DbType.String, 36);
prm.Value = _applicationId;
SQLiteParameter[] args = new SQLiteParameter[1];
args[0] = prm;
return GetProfilesForQuery(sqlQuery, args, pageIndex, pageSize, out totalRecords);
}
public override ProfileInfoCollection GetAllInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords)
{
string sqlQuery = "SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate, LEN(p.PropertyNames) + LEN(p.PropertyValuesString) FROM "
+ USER_TB_NAME + " u, " + PROFILE_TB_NAME + " p WHERE u.ApplicationId = $ApplicationId AND u.UserId = p.UserId AND u.LastActivityDate <= $LastActivityDate"
+ GetClauseForAuthenticationOptions(authenticationOption);
SQLiteParameter prm1 = new SQLiteParameter("$ApplicationId", DbType.String, 256);
prm1.Value = _applicationId;
SQLiteParameter prm2 = new SQLiteParameter("$LastActivityDate", DbType.DateTime);
prm2.Value = userInactiveSinceDate;
SQLiteParameter[] args = new SQLiteParameter[2];
args[0] = prm1;
args[1] = prm2;
return GetProfilesForQuery(sqlQuery, args, pageIndex, pageSize, out totalRecords);
}
public override ProfileInfoCollection FindProfilesByUserName(ProfileAuthenticationOption authenticationOption, string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
{
string sqlQuery = "SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate, LEN(p.PropertyNames) + LEN(p.PropertyValuesString) FROM "
+ USER_TB_NAME + " u, " + PROFILE_TB_NAME + " p WHERE u.ApplicationId = $ApplicationId AND u.UserId = p.UserId AND u.LoweredUserName LIKE $UserName"
+ GetClauseForAuthenticationOptions(authenticationOption);
SQLiteParameter prm1 = new SQLiteParameter("$ApplicationId", DbType.String, 256);
prm1.Value = _applicationId;
SQLiteParameter prm2 = new SQLiteParameter("$UserName", DbType.String, 256);
prm2.Value = usernameToMatch.ToLowerInvariant();
SQLiteParameter[] args = new SQLiteParameter[2];
args[0] = prm1;
args[1] = prm2;
return GetProfilesForQuery(sqlQuery, args, pageIndex, pageSize, out totalRecords);
}
public override ProfileInfoCollection FindInactiveProfilesByUserName(ProfileAuthenticationOption authenticationOption, string usernameToMatch, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords)
{
string sqlQuery = "SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate, LEN(p.PropertyNames) + LEN(p.PropertyValuesString) FROM "
+ USER_TB_NAME + " u, " + PROFILE_TB_NAME + " p WHERE u.ApplicationId = $ApplicationId AND u.UserId = p.UserId AND u.UserName LIKE $UserName AND u.LastActivityDate <= $LastActivityDate"
+ GetClauseForAuthenticationOptions(authenticationOption);
SQLiteParameter prm1 = new SQLiteParameter("$ApplicationId", DbType.String, 256);
prm1.Value = _applicationId;
SQLiteParameter prm2 = new SQLiteParameter("$UserName", DbType.String, 256);
prm2.Value = usernameToMatch.ToLowerInvariant();
SQLiteParameter prm3 = new SQLiteParameter("$LastActivityDate", DbType.DateTime);
prm3.Value = userInactiveSinceDate;
SQLiteParameter[] args = new SQLiteParameter[3];
args[0] = prm1;
args[1] = prm2;
args[2] = prm3;
return GetProfilesForQuery(sqlQuery, args, pageIndex, pageSize, out totalRecords);
}
#endregion
#region Private Methods
private static void CreateAnonymousUser(string username, SQLiteConnection cn, SQLiteTransaction tran, string userId)
{
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "INSERT INTO " + USER_TB_NAME
+ " (UserId, Username, LoweredUsername, ApplicationId, Email, LoweredEmail, Comment, Password,"
+ " PasswordFormat, PasswordSalt, PasswordQuestion,"
+ " PasswordAnswer, IsApproved, IsAnonymous,"
+ " CreateDate, LastPasswordChangedDate, LastActivityDate,"
+ " LastLoginDate, IsLockedOut, LastLockoutDate,"
+ " FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart,"
+ " FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart)"
+ " Values($UserId, $Username, $LoweredUsername, $ApplicationId, $Email, $LoweredEmail, $Comment, $Password,"
+ " $PasswordFormat, $PasswordSalt, $PasswordQuestion, $PasswordAnswer, $IsApproved, $IsAnonymous, $CreateDate, $LastPasswordChangedDate,"
+ " $LastActivityDate, $LastLoginDate, $IsLockedOut, $LastLockoutDate,"
+ " $FailedPasswordAttemptCount, $FailedPasswordAttemptWindowStart,"
+ " $FailedPasswordAnswerAttemptCount, $FailedPasswordAnswerAttemptWindowStart)";
cmd.Transaction = tran;
DateTime nullDate = DateTime.MinValue;
DateTime nowDate = DateTime.UtcNow;
cmd.Parameters.Add("$UserId", DbType.String).Value = userId;
cmd.Parameters.Add("$Username", DbType.String, 256).Value = username;
cmd.Parameters.Add("$LoweredUsername", DbType.String, 256).Value = username.ToLowerInvariant();
cmd.Parameters.Add("$ApplicationId", DbType.String, 256).Value = _applicationId;
cmd.Parameters.Add("$Email", DbType.String, 256).Value = String.Empty;
cmd.Parameters.Add("$LoweredEmail", DbType.String, 256).Value = String.Empty;
cmd.Parameters.Add("$Comment", DbType.String, 3000).Value = null;
cmd.Parameters.Add("$Password", DbType.String, 128).Value = Guid.NewGuid().ToString();
cmd.Parameters.Add("$PasswordFormat", DbType.String, 128).Value = System.Web.Security.Membership.Provider.PasswordFormat.ToString();
cmd.Parameters.Add("$PasswordSalt", DbType.String, 128).Value = String.Empty;
cmd.Parameters.Add("$PasswordQuestion", DbType.String, 256).Value = null;
cmd.Parameters.Add("$PasswordAnswer", DbType.String, 128).Value = null;
cmd.Parameters.Add("$IsApproved", DbType.Boolean).Value = true;
cmd.Parameters.Add("$IsAnonymous", DbType.Boolean).Value = true;
cmd.Parameters.Add("$CreateDate", DbType.DateTime).Value = nowDate;
cmd.Parameters.Add("$LastPasswordChangedDate", DbType.DateTime).Value = nullDate;
cmd.Parameters.Add("$LastActivityDate", DbType.DateTime).Value = nowDate;
cmd.Parameters.Add("$LastLoginDate", DbType.DateTime).Value = nullDate;
cmd.Parameters.Add("$IsLockedOut", DbType.Boolean).Value = false;
cmd.Parameters.Add("$LastLockoutDate", DbType.DateTime).Value = nullDate;
cmd.Parameters.Add("$FailedPasswordAttemptCount", DbType.Int32).Value = 0;
cmd.Parameters.Add("$FailedPasswordAttemptWindowStart", DbType.DateTime).Value = nullDate;
cmd.Parameters.Add("$FailedPasswordAnswerAttemptCount", DbType.Int32).Value = 0;
cmd.Parameters.Add("$FailedPasswordAnswerAttemptWindowStart", DbType.DateTime).Value = nullDate;
if (cn.State != ConnectionState.Open)
cn.Open();
cmd.ExecuteNonQuery();
}
}
private static void ParseDataFromDB(string[] names, string values, byte[] buf, SettingsPropertyValueCollection properties)
{
if (names == null || values == null || buf == null || properties == null)
return;
for (int iter = 0; iter < names.Length / 4; iter++)
{
string name = names[iter * 4];
SettingsPropertyValue pp = properties[name];
if (pp == null) // property not found
continue;
int startPos = Int32.Parse(names[iter * 4 + 2], CultureInfo.InvariantCulture);
int length = Int32.Parse(names[iter * 4 + 3], CultureInfo.InvariantCulture);
if (length == -1 && !pp.Property.PropertyType.IsValueType) // Null Value
{
pp.PropertyValue = null;
pp.IsDirty = false;
pp.Deserialized = true;
}
if (names[iter * 4 + 1] == "S" && startPos >= 0 && length > 0 && values.Length >= startPos + length)
{
pp.PropertyValue = Deserialize(pp, values.Substring(startPos, length));
}
if (names[iter * 4 + 1] == "B" && startPos >= 0 && length > 0 && buf.Length >= startPos + length)
{
byte[] buf2 = new byte[length];
Buffer.BlockCopy(buf, startPos, buf2, 0, length);
pp.PropertyValue = Deserialize(pp, buf2);
}
}
}
private static void GetPropertyValuesFromDatabase(string username, SettingsPropertyValueCollection svc)
{
string[] names = null;
string values = null;
SQLiteConnection cn = GetDBConnectionForProfile();
try
{
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT UserId FROM " + USER_TB_NAME + " WHERE LoweredUsername = $UserName AND ApplicationId = $ApplicationId";
cmd.Parameters.AddWithValue("$UserName", username.ToLowerInvariant());
cmd.Parameters.AddWithValue("$ApplicationId", _applicationId);
if (cn.State == ConnectionState.Closed)
cn.Open();
string userId = cmd.ExecuteScalar() as string;
if (userId != null)
{
// User exists?
cmd.CommandText = "SELECT PropertyNames, PropertyValuesString FROM " + PROFILE_TB_NAME + " WHERE UserId = $UserId";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("$UserId", userId);
using (SQLiteDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
names = dr.GetString(0).Split(':');
values = dr.GetString(1);
}
}
cmd.CommandText = "UPDATE " + USER_TB_NAME + " SET LastActivityDate = $LastActivityDate WHERE UserId = $UserId";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("$LastActivityDate", DateTime.UtcNow);
cmd.Parameters.AddWithValue("$UserId", userId);
cmd.ExecuteNonQuery();
}
}
}
finally
{
if (!IsTransactionInProgress())
cn.Dispose();
}
if (names != null && names.Length > 0)
{
ParseDataFromDB(names, values, new byte[0], svc);
}
}
private static string GetApplicationId(string appName)
{
SQLiteConnection cn = GetDBConnectionForProfile();
try
{
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT ApplicationId FROM aspnet_Applications WHERE ApplicationName = $AppName";
cmd.Parameters.AddWithValue("$AppName", appName);
if (cn.State == ConnectionState.Closed)
cn.Open();
return cmd.ExecuteScalar() as string;
}
}
finally
{
if (!IsTransactionInProgress())
cn.Dispose();
}
}
private static void VerifyApplication()
{
// Verify a record exists in the application table.
if (!String.IsNullOrEmpty(_applicationId))
return;
// No record exists in the application table. Create one now.
SQLiteConnection cn = GetDBConnectionForProfile();
try
{
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "INSERT INTO " + APP_TB_NAME + " (ApplicationId, ApplicationName, Description) VALUES ($ApplicationId, $ApplicationName, $Description)";
_applicationId = Guid.NewGuid().ToString();
cmd.Parameters.AddWithValue("$ApplicationId", _applicationId);
cmd.Parameters.AddWithValue("ApplicationName", _applicationName);
cmd.Parameters.AddWithValue("Description", String.Empty);
if (cn.State == ConnectionState.Closed)
cn.Open();
cmd.ExecuteNonQuery();
}
}
finally
{
if (!IsTransactionInProgress())
cn.Dispose();
}
}
private static ProfileInfoCollection GetProfilesForQuery(string sqlQuery, SQLiteParameter[] args, int pageIndex, int pageSize, out int totalRecords)
{
if (pageIndex < 0)
throw new ArgumentException("Page index must be non-negative", "pageIndex");
if (pageSize < 1)
throw new ArgumentException("Page size must be positive", "pageSize");
long lBound = (long)pageIndex * pageSize;
long uBound = lBound + pageSize - 1;
if (uBound > System.Int32.MaxValue)
{
throw new ArgumentException("pageIndex*pageSize too large");
}
SQLiteConnection cn = GetDBConnectionForProfile();
try
{
ProfileInfoCollection profiles = new ProfileInfoCollection();
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = sqlQuery;
for (int iter = 0; iter < args.Length; iter++)
{
cmd.Parameters.Add(args[iter]);
}
if (cn.State == ConnectionState.Closed)
cn.Open();
using (SQLiteDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
totalRecords = 0;
while (dr.Read())
{
totalRecords++;
if ((totalRecords - 1 < lBound) || (totalRecords - 1 > uBound))
continue;
string username = dr.GetString(0);
bool isAnon = dr.GetBoolean(1);
DateTime dtLastActivity = dr.GetDateTime(2);
DateTime dtLastUpdated = dr.GetDateTime(3);
int size = dr.GetInt32(4);
profiles.Add(new ProfileInfo(username, isAnon, dtLastActivity, dtLastUpdated, size));
}
return profiles;
}
}
}
finally
{
if (!IsTransactionInProgress())
cn.Dispose();
}
}
private static bool DeleteProfile(SQLiteConnection cn, SQLiteTransaction tran, string username)
{
bool deleteSuccessful = false;
if (cn.State != ConnectionState.Open)
cn.Open();
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT UserId FROM " + USER_TB_NAME + " WHERE LoweredUsername = $Username AND ApplicationId = $ApplicationId";
cmd.Parameters.AddWithValue("$Username", username.ToLowerInvariant());
cmd.Parameters.AddWithValue("$ApplicationId", _applicationId);
if (tran != null)
cmd.Transaction = tran;
string userId = cmd.ExecuteScalar() as string;
if (userId != null)
{
cmd.CommandText = "DELETE FROM " + PROFILE_TB_NAME + " WHERE UserId = $UserId";
cmd.Parameters.Clear();
cmd.Parameters.Add("$UserId", DbType.String, 36).Value = userId;
deleteSuccessful = (cmd.ExecuteNonQuery() != 0);
}
return (deleteSuccessful);
}
}
private static object Deserialize(SettingsPropertyValue prop, object obj)
{
object val = null;
//////////////////////////////////////////////
/// Step 1: Try creating from Serialized value
if (obj != null)
{
if (obj is string)
{
val = GetObjectFromString(prop.Property.PropertyType, prop.Property.SerializeAs, (string)obj);
}
else
{
MemoryStream ms = new System.IO.MemoryStream((byte[])obj);
try
{
val = (new BinaryFormatter()).Deserialize(ms);
}
finally
{
ms.Close();
}
}
if (val != null && !prop.Property.PropertyType.IsAssignableFrom(val.GetType())) // is it the correct type
val = null;
}
//////////////////////////////////////////////
/// Step 2: Try creating from default value
if (val == null)
{
if (prop.Property.DefaultValue == null || prop.Property.DefaultValue.ToString() == "[null]")
{
if (prop.Property.PropertyType.IsValueType)
return Activator.CreateInstance(prop.Property.PropertyType);
else
return null;
}
if (!(prop.Property.DefaultValue is string))
{
val = prop.Property.DefaultValue;
}
else
{
val = GetObjectFromString(prop.Property.PropertyType, prop.Property.SerializeAs, (string)prop.Property.DefaultValue);
}
if (val != null && !prop.Property.PropertyType.IsAssignableFrom(val.GetType())) // is it the correct type
throw new ArgumentException("Could not create from default value for property: " + prop.Property.Name);
}
//////////////////////////////////////////////
/// Step 3: Create a new one by calling the parameterless constructor
if (val == null)
{
if (prop.Property.PropertyType == typeof(string))
val = "";
else
val = Activator.CreateInstance(prop.Property.PropertyType);
}
return val;
}
private static void PrepareDataForSaving(ref string allNames, ref string allValues, ref byte[] buf, bool binarySupported, SettingsPropertyValueCollection properties, bool userIsAuthenticated)
{
if (binarySupported)
throw new NotSupportedException("SQLiteProfileProvider does not support storing binary data in user's profile.");
StringBuilder names = new StringBuilder();
StringBuilder values = new StringBuilder();
MemoryStream ms = (binarySupported ? new MemoryStream() : null);
try
{
bool anyItemsToSave = false;
foreach (SettingsPropertyValue pp in properties)
{
if (pp.IsDirty)
{
if (!userIsAuthenticated)
{
bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];
if (!allowAnonymous)
continue;
}
anyItemsToSave = true;
break;
}
}
if (!anyItemsToSave)
return;
foreach (SettingsPropertyValue pp in properties)
{
if (!userIsAuthenticated)
{
bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];
if (!allowAnonymous)
continue;
}
if (!pp.IsDirty && pp.UsingDefaultValue) // Not fetched from DB and not written to
continue;
int len, startPos = 0;
string propValue = null;
if (pp.Deserialized && pp.PropertyValue == null)
{ // is value null?
len = -1;
}
else
{
object sVal = SerializePropertyValue(pp);
if (sVal == null)
{
len = -1;
}
else
{
if (!(sVal is string) && !binarySupported)
{
sVal = Convert.ToBase64String((byte[])sVal);
}
if (sVal is string)
{
propValue = (string)sVal;
len = propValue.Length;
startPos = values.Length;
}
else
{
byte[] b2 = (byte[])sVal;
startPos = (int)ms.Position;
ms.Write(b2, 0, b2.Length);
ms.Position = startPos + b2.Length;
len = b2.Length;
}
}
}
names.Append(pp.Name + ":" + ((propValue != null) ? "S" : "B") +
":" + startPos.ToString(CultureInfo.InvariantCulture) + ":" + len.ToString(CultureInfo.InvariantCulture) + ":");
if (propValue != null)
values.Append(propValue);
}
if (binarySupported)
{
buf = ms.ToArray();
}
}
finally
{
if (ms != null)
ms.Close();
}
allNames = names.ToString();
allValues = values.ToString();
}
private static string ConvertObjectToString(object propValue, Type type, SettingsSerializeAs serializeAs, bool throwOnError)
{
if (serializeAs == SettingsSerializeAs.ProviderSpecific)
{
if (type == typeof(string) || type.IsPrimitive)
serializeAs = SettingsSerializeAs.String;
else
serializeAs = SettingsSerializeAs.Xml;
}
try
{
switch (serializeAs)
{
case SettingsSerializeAs.String:
TypeConverter converter = TypeDescriptor.GetConverter(type);
if (converter != null && converter.CanConvertTo(typeof(String)) && converter.CanConvertFrom(typeof(String)))
return converter.ConvertToString(propValue);
throw new ArgumentException("Unable to convert type " + type.ToString() + " to string", "type");
case SettingsSerializeAs.Binary:
MemoryStream ms = new System.IO.MemoryStream();
try
{
BinaryFormatter bf = new BinaryFormatter();
bf.Serialize(ms, propValue);
byte[] buffer = ms.ToArray();
return Convert.ToBase64String(buffer);
}
finally
{
ms.Close();
}
case SettingsSerializeAs.Xml:
XmlSerializer xs = new XmlSerializer(type);
StringWriter sw = new StringWriter(CultureInfo.InvariantCulture);
xs.Serialize(sw, propValue);
return sw.ToString();
}
}
catch (Exception)
{
if (throwOnError)
throw;
}
return null;
}
private static object SerializePropertyValue(SettingsPropertyValue prop)
{
object val = prop.PropertyValue;
if (val == null)
return null;
if (prop.Property.SerializeAs != SettingsSerializeAs.Binary)
return ConvertObjectToString(val, prop.Property.PropertyType, prop.Property.SerializeAs, prop.Property.ThrowOnErrorSerializing);
MemoryStream ms = new System.IO.MemoryStream();
try
{
BinaryFormatter bf = new BinaryFormatter();
bf.Serialize(ms, val);
return ms.ToArray();
}
finally
{
ms.Close();
}
}
private static object GetObjectFromString(Type type, SettingsSerializeAs serializeAs, string attValue)
{
// Deal with string types
if (type == typeof(string) && (string.IsNullOrEmpty(attValue) || serializeAs == SettingsSerializeAs.String))
return attValue;
// Return null if there is nothing to convert
if (string.IsNullOrEmpty(attValue))
return null;
// Convert based on the serialized type
switch (serializeAs)
{
case SettingsSerializeAs.Binary:
byte[] buf = Convert.FromBase64String(attValue);
MemoryStream ms = null;
try
{
ms = new System.IO.MemoryStream(buf);
return (new BinaryFormatter()).Deserialize(ms);
}
finally
{
if (ms != null)
ms.Close();
}
case SettingsSerializeAs.Xml:
StringReader sr = new StringReader(attValue);
XmlSerializer xs = new XmlSerializer(type);
return xs.Deserialize(sr);
case SettingsSerializeAs.String:
TypeConverter converter = TypeDescriptor.GetConverter(type);
if (converter != null && converter.CanConvertTo(typeof(String)) && converter.CanConvertFrom(typeof(String)))
return converter.ConvertFromString(attValue);
throw new ArgumentException("Unable to convert type: " + type.ToString() + " from string", "type");
default:
return null;
}
}
private static string GetClauseForAuthenticationOptions(ProfileAuthenticationOption authenticationOption)
{
switch (authenticationOption)
{
case ProfileAuthenticationOption.Anonymous:
return " AND IsAnonymous='1' ";
case ProfileAuthenticationOption.Authenticated:
return " AND IsAnonymous='0' ";
case ProfileAuthenticationOption.All:
return " ";
default: throw new InvalidEnumArgumentException(String.Format("Unknown ProfileAuthenticationOption value: {0}.", authenticationOption.ToString()));
}
}
/// <summary>
/// Get a reference to the database connection used for profile. If a transaction is currently in progress, and the
/// connection string of the transaction connection is the same as the connection string for the profile provider,
/// then the connection associated with the transaction is returned, and it will already be open. If no transaction is in progress,
/// a new <see cref="SQLiteConnection"/> is created and returned. It will be closed and must be opened by the caller
/// before using.
/// </summary>
/// <returns>A <see cref="SQLiteConnection"/> object.</returns>
/// <remarks>The transaction is stored in <see cref="System.Web.HttpContext.Current"/>. That means transaction support is limited
/// to web applications. For other types of applications, there is no transaction support unless this code is modified.</remarks>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1024:UsePropertiesWhereAppropriate")]
private static SQLiteConnection GetDBConnectionForProfile()
{
// Look in the HTTP context bag for a previously created connection and transaction. Return if found and its connection
// string matches that of the Profile connection string; otherwise return a fresh connection.
if (System.Web.HttpContext.Current != null)
{
SQLiteTransaction tran = (SQLiteTransaction)System.Web.HttpContext.Current.Items[_httpTransactionId];
if ((tran != null) && (String.Equals(tran.Connection.ConnectionString, _connectionString)))
return tran.Connection;
}
return new SQLiteConnection(_connectionString);
}
/// <summary>
/// Determines whether a database transaction is in progress for the Profile provider.
/// </summary>
/// <returns>
/// <c>true</c> if a database transaction is in progress; otherwise, <c>false</c>.
/// </returns>
/// <remarks>A transaction is considered in progress if an instance of <see cref="SQLiteTransaction"/> is found in the
/// <see cref="System.Web.HttpContext.Current"/> Items property and its connection string is equal to the Profile
/// provider's connection string. Note that this implementation of <see cref="SQLiteProfileProvider"/> never adds a
/// <see cref="SQLiteTransaction"/> to <see cref="System.Web.HttpContext.Current"/>, but it is possible that
/// another data provider in this application does. This may be because other data is also stored in this SQLite database,
/// and the application author wants to provide transaction support across the individual providers. If an instance of
/// <see cref="System.Web.HttpContext.Current"/> does not exist (for example, if the calling application is not a web application),
/// this method always returns false.</remarks>
private static bool IsTransactionInProgress()
{
if (System.Web.HttpContext.Current == null)
return false;
SQLiteTransaction tran = (SQLiteTransaction)System.Web.HttpContext.Current.Items[_httpTransactionId];
if ((tran != null) && (String.Equals(tran.Connection.ConnectionString, _connectionString)))
return true;
else
return false;
}
#endregion
}
}