/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
private string GetLastUserNameFromOffileStore()
{
//if (MustAssertForSql)
// (new PermissionSet(PermissionState.Unrestricted)).Assert();
if (_UsingFileSystemStore || _UsingIsolatedStore)
{
return(ClientDataManager.GetAppClientData(_UsingIsolatedStore).LastLoggedInUserName);
}
using (DbConnection conn = SqlHelper.GetConnection(null, _ConnectionString, _ConnectionStringProvider)) {
DbTransaction trans = null;
try
{
trans = conn.BeginTransaction();
DbCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "SELECT PropertyValue FROM ApplicationProperties WHERE PropertyName = N'LastLoggedInUserName'";
object o = cmd.ExecuteScalar();
return((o != null) ? o.ToString() : null);
} catch {
if (trans != null)
{
trans.Rollback();
trans = null;
}
throw;
} finally {
if (trans != null)
{
trans.Commit();
}
}
}
}
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
internal static void DeleteAllCookies(string username, string connectionString, string sqlProvider)
{
if (connectionString == _SQL_FILES_Tag || connectionString == _Isolated_Storage_Tag)
{
ClientDataManager.DeleteAllCookies(username, connectionString == _Isolated_Storage_Tag);
return;
}
using (DbConnection connection = GetConnection(username, connectionString, sqlProvider)) {
DbTransaction trans = null;
try {
trans = connection.BeginTransaction();
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = "DELETE FROM UserProperties WHERE PropertyName LIKE N'CookieName_%'";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
} catch {
if (trans != null)
{
trans.Rollback();
trans = null;
}
throw;
} finally {
if (trans != null)
{
trans.Commit();
}
}
}
}
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
private bool ValidateUserWithOfflineStore(string username, string password)
{
if (!_SavePasswordHash)
{
return(false);
}
string passwordHash = null;
string passwordSalt = null;
if (_UsingFileSystemStore || _UsingIsolatedStore)
{
ClientData cd = ClientDataManager.GetUserClientData(username, _UsingIsolatedStore);
passwordHash = cd.PasswordHash;
passwordSalt = cd.PasswordSalt;
}
else
{
// if (MustAssertForSql)
// (new PermissionSet(PermissionState.Unrestricted)).Assert();
DbTransaction trans = null;
using (DbConnection conn = SqlHelper.GetConnection(username, _ConnectionString, _ConnectionStringProvider)) {
try {
DbCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "SELECT PropertyValue FROM UserProperties WHERE PropertyName = @PasswordHashName";
SqlHelper.AddParameter(conn, cmd, "@PasswordHashName", "PasswordHash_" + username);
passwordHash = cmd.ExecuteScalar() as string;
cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "SELECT PropertyValue FROM UserProperties WHERE PropertyName = @PasswordSaltName";
SqlHelper.AddParameter(conn, cmd, "@PasswordSaltName", "PasswordSalt_" + username);
passwordSalt = cmd.ExecuteScalar() as string;
} catch {
if (trans != null)
{
trans.Rollback();
trans = null;
}
throw;
} finally {
if (trans != null)
{
trans.Commit();
}
}
}
}
if (string.IsNullOrEmpty(passwordHash) || string.IsNullOrEmpty(passwordSalt))
{
return(false);
}
byte [] buf = Convert.FromBase64String(passwordSalt);
return(passwordHash == EncodePassword(password, buf));
}
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
private void GetPropertyValuesFromSQL()
{
if (_UsingFileSystemStore || _UsingIsolatedStore)
{
ClientData cd = ClientDataManager.GetUserClientData(Thread.CurrentPrincipal.Identity.Name, _UsingIsolatedStore);
if (cd.SettingsNames == null || cd.SettingsValues == null)
{
return;
}
int len = cd.SettingsNames.Length;
if (cd.SettingsNames.Length != cd.SettingsStoredAs.Length || cd.SettingsValues.Length != cd.SettingsStoredAs.Length)
{
return; // Bad!
}
for (int iter = 0; iter < len; iter++)
{
AddProperty(cd.SettingsNames[iter], cd.SettingsStoredAs[iter], cd.SettingsValues[iter]);
}
return;
}
using (DbConnection conn = SqlHelper.GetConnection(Thread.CurrentPrincipal.Identity.Name,
GetConnectionString(), _ConnectionStringProvider))
{
DbTransaction trans = null;
try {
trans = conn.BeginTransaction();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT PropertyName, PropertyStoredAs, PropertyValue FROM Settings";
cmd.Transaction = trans;
using (DbDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
string name = reader.GetString(0);
string storedAs = reader.GetString(1);
string propVal = (reader.IsDBNull(2) ? null : reader.GetString(2));
AddProperty(name, storedAs, propVal);
}
}
} catch {
if (trans != null)
{
trans.Rollback();
trans = null;
}
throw;
} finally {
if (trans != null)
{
trans.Commit();
}
}
}
}
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
private void StoreRolesForCurrentUser()
{
if (_UsingFileSystemStore || _UsingIsolatedStore)
{
ClientData cd = ClientDataManager.GetUserClientData(_CurrentUser, _UsingIsolatedStore);
cd.Roles = _Roles;
cd.RolesCachedDateUtc = DateTime.UtcNow;
cd.Save();
return;
}
// if (MustAssertForSql)
// (new PermissionSet(PermissionState.Unrestricted)).Assert();
RemoveRolesFromDB(); // Remove all old roles
DbTransaction trans = null;
using (DbConnection conn = SqlHelper.GetConnection(_CurrentUser, _ConnectionString, _ConnectionStringProvider)) {
try {
trans = conn.BeginTransaction();
// Add the roles
DbCommand cmd = null;
foreach (string role in _Roles)
{
cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO Roles(UserName, RoleName) VALUES(@UserName, @RoleName)";
SqlHelper.AddParameter(conn, cmd, "@UserName", _CurrentUser);
SqlHelper.AddParameter(conn, cmd, "@RoleName", role);
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
}
cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO UserProperties (PropertyName, PropertyValue) VALUES(@RolesCachedDate, @Date)";
SqlHelper.AddParameter(conn, cmd, "@RolesCachedDate", "RolesCachedDate_" + _CurrentUser);
SqlHelper.AddParameter(conn, cmd, "@Date", DateTime.UtcNow.ToFileTimeUtc().ToString(CultureInfo.InvariantCulture));
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
} catch {
if (trans != null)
{
trans.Rollback();
trans = null;
}
throw;
} finally {
if (trans != null)
{
trans.Commit();
}
}
}
}
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
private void StoreLastUserNameInOffileStore(string username)
{
if (_UsingFileSystemStore || _UsingIsolatedStore)
{
ClientData cd = ClientDataManager.GetAppClientData(_UsingIsolatedStore);
cd.LastLoggedInUserName = username;
cd.LastLoggedInDateUtc = DateTime.UtcNow;
cd.Save();
return;
}
//if (MustAssertForSql)
// (new PermissionSet(PermissionState.Unrestricted)).Assert();
using (DbConnection conn = SqlHelper.GetConnection(null, _ConnectionString, _ConnectionStringProvider)) {
DbTransaction trans = null;
try
{
trans = conn.BeginTransaction();
DbCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "DELETE FROM ApplicationProperties WHERE PropertyName = N'LastLoggedInUserName'";
cmd.ExecuteNonQuery();
if (!string.IsNullOrEmpty(username))
{
cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO ApplicationProperties(PropertyName, PropertyValue) VALUES (N'LastLoggedInUserName', @UserName)";
SqlHelper.AddParameter(conn, cmd, "@UserName", username);
cmd.ExecuteNonQuery();
cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO ApplicationProperties(PropertyName, PropertyValue) VALUES (N'LastLoggedInDate', @Date)";
SqlHelper.AddParameter(conn, cmd, "@Date", DateTime.Now.ToFileTimeUtc().ToString(CultureInfo.InvariantCulture));
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
}
} catch {
if (trans != null)
{
trans.Rollback();
trans = null;
}
throw;
} finally {
if (trans != null)
{
trans.Commit();
}
}
}
}
private bool GetNeedToReset()
{
if (_UsingFileSystemStore || _UsingIsolatedStore)
{
ClientData cd = ClientDataManager.GetUserClientData(Thread.CurrentPrincipal.Identity.Name, _UsingIsolatedStore);
return(cd.SettingsNeedReset);
}
else
{
string temp = GetTagValue("NeeedToDoReset");
return(temp != null && temp == "1");
}
}
private void SetIsCacheMoreFresh(bool fSet)
{
if (_UsingFileSystemStore || _UsingIsolatedStore)
{
ClientData cd = ClientDataManager.GetUserClientData(Thread.CurrentPrincipal.Identity.Name, _UsingIsolatedStore);
cd.SettingsCacheIsMoreFresh = fSet;
cd.Save();
}
else
{
SetTagValue("IsCacheMoreFresh", fSet ? "1" : "0");
}
}
private bool GetIsCacheMoreFresh()
{
if (_UsingFileSystemStore || _UsingIsolatedStore)
{
ClientData cd = ClientDataManager.GetUserClientData(Thread.CurrentPrincipal.Identity.Name, _UsingIsolatedStore);
return(cd.SettingsCacheIsMoreFresh);
}
else
{
string temp = GetTagValue("IsCacheMoreFresh");
return(temp != null && temp == "1");
}
}
private void SetNeedToReset(bool fSet)
{
if (_UsingFileSystemStore || _UsingIsolatedStore)
{
ClientData cd = ClientDataManager.GetUserClientData(Thread.CurrentPrincipal.Identity.Name, _UsingIsolatedStore);
cd.SettingsNeedReset = fSet;
cd.Save();
}
else
{
SetTagValue("NeeedToDoReset", fSet ? "1" : "0");
}
}
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
internal static string StoreCookieInDB(string cookieName, string cookieValue, string username, string connectionString, string sqlProvider)
{
if (connectionString == _SQL_FILES_Tag)
{
return(ClientDataManager.StoreCookie(username, cookieName, cookieValue, false));
}
if (connectionString == _Isolated_Storage_Tag)
{
return(ClientDataManager.StoreCookie(username, cookieName, cookieValue, true));
}
string name = Guid.NewGuid().ToString("N");
using (DbConnection connection = GetConnection(username, connectionString, sqlProvider)) {
DbTransaction trans = null;
try {
trans = connection.BeginTransaction();
DbCommand cmd = connection.CreateCommand();
// delete old cookie
cmd.CommandText = "DELETE FROM UserProperties WHERE PropertyName LIKE N'CookieName_%' AND PropertyValue LIKE @PropValue";
cmd.Transaction = trans;
AddParameter(connection, cmd, "@PropValue", cookieName + "=%");
cmd.ExecuteNonQuery();
if (!string.IsNullOrEmpty(cookieValue))
{
cmd = connection.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO UserProperties (PropertyName, PropertyValue) VALUES (@PropName, @PropValue)";
AddParameter(connection, cmd, "@PropName", "CookieName_" + name);
AddParameter(connection, cmd, "@PropValue", cookieName + "=" + cookieValue);
cmd.ExecuteNonQuery();
return(name);
}
return(cookieName);
} catch {
if (trans != null)
{
trans.Rollback();
trans = null;
}
throw;
} finally {
if (trans != null)
{
trans.Commit();
}
}
}
}
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
private void RemoveRolesFromDB()
{
// if (MustAssertForSql)
// (new PermissionSet(PermissionState.Unrestricted)).Assert();
if (string.IsNullOrEmpty(_CurrentUser))
{
return;
}
if (_UsingFileSystemStore || _UsingIsolatedStore)
{
ClientData cd = ClientDataManager.GetUserClientData(_CurrentUser, _UsingIsolatedStore);
cd.Roles = null;
cd.Save();
return;
}
using (DbConnection conn = SqlHelper.GetConnection(_CurrentUser, _ConnectionString, _ConnectionStringProvider)) {
DbTransaction trans = null;
try {
trans = conn.BeginTransaction();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Roles WHERE UserName = @UserName";
SqlHelper.AddParameter(conn, cmd, "@UserName", _CurrentUser);
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM UserProperties WHERE PropertyName = @RolesCachedDate";
SqlHelper.AddParameter(conn, cmd, "@RolesCachedDate", "RolesCachedDate_" + _CurrentUser);
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
} catch {
if (trans != null)
{
trans.Rollback();
trans = null;
}
throw;
} finally {
if (trans != null)
{
trans.Commit();
}
}
}
}
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
internal static string GetCookieFromDB(string name, string username, string connectionString, string sqlProvider)
{
if (connectionString == _SQL_FILES_Tag)
{
return(ClientDataManager.GetCookie(username, name, false));
}
if (connectionString == _Isolated_Storage_Tag)
{
return(ClientDataManager.GetCookie(username, name, true));
}
using (DbConnection connection = GetConnection(username, connectionString, sqlProvider)) {
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT PropertyValue FROM UserProperties WHERE PropertyName = @PropName";
AddParameter(connection, cmd, "@PropName", "CookieName_" + name);
return(cmd.ExecuteScalar() as string);
}
}
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
private bool GetRolesFromDBForUser(string username)
{
_Roles = null;
_CacheExpiryDate = DateTime.UtcNow;
_CurrentUser = username;
// if (MustAssertForSql)
// (new PermissionSet(PermissionState.Unrestricted)).Assert();
if (_UsingFileSystemStore || _UsingIsolatedStore)
{
ClientData cd = ClientDataManager.GetUserClientData(username, _UsingIsolatedStore);
if (cd.Roles == null)
{
return(false);
}
_Roles = cd.Roles;
_CacheExpiryDate = cd.RolesCachedDateUtc.AddMinutes(_CacheTimeout);
if (!ConnectivityStatus.IsOffline && _CacheExpiryDate < DateTime.UtcNow) // expired roles
{
return(false);
}
return(true);
}
using (DbConnection conn = SqlHelper.GetConnection(_CurrentUser, _ConnectionString, _ConnectionStringProvider)) {
DbTransaction trans = null;
try {
trans = conn.BeginTransaction();
DbCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "SELECT PropertyValue FROM UserProperties WHERE PropertyName = @RolesCachedDate";
SqlHelper.AddParameter(conn, cmd, "@RolesCachedDate", "RolesCachedDate_" + _CurrentUser);
string date = cmd.ExecuteScalar() as string;
if (date == null) // not cached
{
return(false);
}
long filetime = long.Parse(date, CultureInfo.InvariantCulture);
_CacheExpiryDate = DateTime.FromFileTimeUtc(filetime).AddMinutes(_CacheTimeout);
if (!ConnectivityStatus.IsOffline && _CacheExpiryDate < DateTime.UtcNow) // expired roles
{
return(false);
}
cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "SELECT RoleName FROM Roles WHERE UserName = @UserName ORDER BY RoleName";
SqlHelper.AddParameter(conn, cmd, "@UserName", _CurrentUser);
ArrayList al = new ArrayList();
using (DbDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
al.Add(reader.GetString(0));
}
}
_Roles = new string[al.Count];
for (int iter = 0; iter < al.Count; iter++)
{
_Roles[iter] = (string)al[iter];
}
return(true);
} catch {
if (trans != null)
{
trans.Rollback();
trans = null;
}
throw;
} finally {
if (trans != null)
{
trans.Commit();
}
}
}
}
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
private void StoreHashedPasswordInDB(string username, string password)
{
if (!_SavePasswordHash)
{
return;
}
// if (MustAssertForSql)
// (new PermissionSet(PermissionState.Unrestricted)).Assert();
byte[] buf = new byte[16];
(new RNGCryptoServiceProvider()).GetBytes(buf);
string passwordSalt = Convert.ToBase64String(buf);
string passwordHash = EncodePassword(password, buf);
if (_UsingFileSystemStore || _UsingIsolatedStore)
{
ClientData cd = ClientDataManager.GetUserClientData(username, _UsingIsolatedStore);
cd.PasswordHash = passwordHash;
cd.PasswordSalt = passwordSalt;
cd.Save();
return;
}
using (DbConnection conn = SqlHelper.GetConnection(username, _ConnectionString, _ConnectionStringProvider)) {
DbTransaction trans = null;
DbCommand cmd = null;
try {
trans = conn.BeginTransaction();
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM UserProperties WHERE PropertyName = @PasswordHashName";
SqlHelper.AddParameter(conn, cmd, "@PasswordHashName", "PasswordHash_" + username);
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM UserProperties WHERE PropertyName = @PasswordSaltName";
SqlHelper.AddParameter(conn, cmd, "@PasswordSaltName", "PasswordSalt_" + username);
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO UserProperties(PropertyName, PropertyValue) VALUES (@PasswordHashName, @PasswordHashValue)";
SqlHelper.AddParameter(conn, cmd, "@PasswordHashName", "PasswordHash_" + username);
SqlHelper.AddParameter(conn, cmd, "@PasswordHashValue", passwordHash);
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO UserProperties(PropertyName, PropertyValue) VALUES (@PasswordSaltName, @PasswordSaltValue)";
SqlHelper.AddParameter(conn, cmd, "@PasswordSaltName", "PasswordSalt_" + username);
SqlHelper.AddParameter(conn, cmd, "@PasswordSaltValue", passwordSalt);
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
} catch {
if (trans != null)
{
trans.Rollback();
trans = null;
}
throw;
} finally {
if (trans != null)
{
trans.Commit();
}
}
}
}
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
private void SetPropertyValuesSQL(SettingsPropertyValueCollection values, bool updateSaveTime)
{
string username = Thread.CurrentPrincipal.Identity.Name;
if (_UsingFileSystemStore || _UsingIsolatedStore)
{
ClientData cd = ClientDataManager.GetUserClientData(username, _UsingIsolatedStore);
cd.SettingsNames = new string[values.Count];
cd.SettingsStoredAs = new string[values.Count];
cd.SettingsValues = new string[values.Count];
int iter = 0;
foreach (SettingsPropertyValue value in values)
{
cd.SettingsNames[iter] = value.Property.Name;
object val = value.SerializedValue;
if (val == null)
{
cd.SettingsStoredAs[iter] = "N";
}
else if (val is string)
{
cd.SettingsStoredAs[iter] = "S";
cd.SettingsValues[iter] = (string)val;
}
else
{
cd.SettingsStoredAs[iter] = "B";
cd.SettingsValues[iter] = Convert.ToBase64String((byte[])val);
}
iter++;
}
if (updateSaveTime)
{
cd.SettingsCacheIsMoreFresh = true;
}
cd.Save();
return;
}
using (DbConnection conn = SqlHelper.GetConnection(username, GetConnectionString(), _ConnectionStringProvider)) {
DbTransaction trans = null;
try {
trans = conn.BeginTransaction();
foreach (SettingsPropertyValue value in values)
{
DbCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "DELETE FROM Settings WHERE PropertyName = @PropName";
SqlHelper.AddParameter(conn, cmd, "@PropName", value.Property.Name);
cmd.ExecuteNonQuery();
cmd = conn.CreateCommand();
cmd.Transaction = trans;
object val = value.SerializedValue;
if (val == null)
{
cmd.CommandText = "INSERT INTO Settings (PropertyName, PropertyStoredAs, PropertyValue) VALUES (@PropName, 'N', '')";
SqlHelper.AddParameter(conn, cmd, "@PropName", value.Property.Name);
}
else if (val is string)
{
cmd.CommandText = "INSERT INTO Settings (PropertyName, PropertyStoredAs, PropertyValue) VALUES (@PropName, 'S', @PropVal)";
SqlHelper.AddParameter(conn, cmd, "@PropName", value.Property.Name);
SqlHelper.AddParameter(conn, cmd, "@PropVal", (string)val);
}
else
{
cmd.CommandText = "INSERT INTO Settings (PropertyName, PropertyStoredAs, PropertyValue) VALUES (@PropName, 'B', @PropVal)";
SqlHelper.AddParameter(conn, cmd, "@PropName", value.Property.Name);
SqlHelper.AddParameter(conn, cmd, "@PropVal", Convert.ToBase64String((byte[])val));
}
cmd.ExecuteNonQuery();
}
} catch {
if (trans != null)
{
trans.Rollback();
trans = null;
}
throw;
} finally {
if (trans != null)
{
trans.Commit();
}
}
}
if (updateSaveTime)
{
SetIsCacheMoreFresh(true);
}
}