/////////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////////
        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();
                    }
                }
            }
        }
Esempio n. 5
0
        /////////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////////
        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");
     }
 }
Esempio n. 10
0
 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();
                    }
                }
            }
        }
Esempio n. 12
0
        /////////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////////
        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);
            }
        }
Esempio n. 14
0
        /////////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////////
        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();
                    }
                }
            }
        }
Esempio n. 16
0
        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        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);
            }
        }