Gallery Server Pro - An ASP.NET Gallery for Sharing Photos, Video, Audio and Other Media

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

	}
}