Persisting Preferences on the Server

Parent Previous Next

When you save preferences, where are the preferences persisted to??


The grid has a robust persistence preference mechanism that "just works" out of the box, but the preferences are stored on the client machine's localstorage by default. This enables the preference persistence mechanism to work without any additional coding on the part of the developers utilizing the library. While this may be sufficient for most people, this will not work in scenarios when the same user uses multiple machines to access your application, or if multiple users access the application using the same machine. For this, you will need to store preferences in the backend, and this post aims to show you how this is done. Attached is a simple example that you can plug into the demo console you got when you downloaded a trial (or purchased a license).


This depends on preferencePersistenceMode. String value "server" or "client". When this property is set to client(default), the grid uses a local storage on the client to store preference settings. When it is set to server, the grid fires an event, preferencesChanged, which contains a string representation of the preference values. This can then be persisted on the back-end, tied to a specific user id.


While you are at it, also review preferencePersistenceKey:

String value that uniquely identifies this grid across the application. If you have multiple grids' with enablePreferencePersistence, and they happen to share the same value for the id field, e.g. id="grid1" they might overwrite each others' preferences. To combat this situation, we provide a property, which defaults to the id of the grid, but you can override to provide a globally unique key.



Saving preferences on the server:


If you look at ServerPreferences.js it shows the JS code require to enable server based persistence of preferences: Please note the declaration of the grid - we wire up 3 events, loadPreferences, persistPreferences, and clearPreferences. Each of these events call a service method, which we will talk about in a bit. But the idea is simple, on persistPreferences, we send up the preferences string, the name of the grid, and in your service method, you figure out who the logged in user is (on basis of your security implementation) and store a record in the database with UserID, gridName, and preferences (Make this a large text field (nvarchar(max) in SQL Server Lingo), it could get pretty verbose). the loadPreferences will call your service method to load the preferences you persisted, and set the grids preferences property. Once you set the property, the grid will parse everything and get back to the state it was when the preferences were stored. And then the final method is the clearPreferences, which will basically just wipe out the preferences that were previously stored. Sound like a lot, but really pretty straight forward. In this example, we just store the preferences in a global variable instead of the database.


Lets take a look at the code that does this:


var myCompanyNameSpace = {};


myCompanyNameSpace.Preferences = {}

myCompanyNameSpace.Preferences.save = function (str, fn) {

   $.post('Preferences/set', { str: str }, function (o) {

       fn(o);

   });

}


myCompanyNameSpace.Preferences.load = function (fn) {

   $.get('Preferences/get', function (o) {

       fn(o);

   });

}


export default class App extends React.Component {


   constructor() {

       super();

   }


   serverPreferences_CreationComplete(event) {

       var grid = event.target;

       grid.setDataProvider([

           { "id": "5001", "type": "None" },

           { "id": "5002", "type": "Glazed" },

           { "id": "5005", "type": "Sugar" },

           { "id": "5007", "type": "Powdered Sugar" },

           { "id": "5006", "type": "Chocolate with Sprinkles" },

           { "id": "5003", "type": "Chocolate" },

           { "id": "5004", "type": "Maple" }

       ]);

       grid.addEventListener(this, flexiciousNmsp.FlexDataGrid.EVENT_LOADPREFERENCES,

       //Called when the grid needs preferences (on creation complete)

           function () {//onLoadPreferences()

               //here you will call a server method to load preferences, for this demo, we are going to mock this.

               flexiciousNmsp.UIUtils.showMessage("Loading Preferences!");

               myCompanyNameSpace.Preferences.load(function (o) {

                   if (o) {

                       flexiciousNmsp.UIUtils.showMessage("Preferences found, applying!");

                       grid.setPreferences(o);

                   } else flexiciousNmsp.UIUtils.showMessage("No preferences found, please save preferences!");


               });


           }

       );

       grid.addEventListener(this, flexiciousNmsp.FlexDataGrid.EVENT_PERSISTPREFERENCES,

       //Called when the user clicks on "save preferences" in the preferences dialog box

           function () {//onPersistPreferences()

               //here you will call a server method to save preferences, for this demo, we are going to mock this.

               flexiciousNmsp.UIUtils.showMessage("Saving Preferences!");

               myCompanyNameSpace.Preferences.save(grid.getPreferences(), function (o) {

                   flexiciousNmsp.UIUtils.showMessage("Preferences Saved!");

               })


           }

       );


       //Called when the user clicks on "clear preferences" in the preferences dialog box

       grid.addEventListener(this, flexiciousNmsp.FlexDataGrid.EVENT_CLEARPREFERENCES,

           function () {//onClearPreferences()

               //here you will call a server method to clear preferences, for this demo, we are going to mock this.

               flexiciousNmsp.UIUtils.showMessage("Clearing Preferences!");

               myCompanyNameSpace.Preferences.save("", function (o) {

                   flexiciousNmsp.UIUtils.showMessage("Preferences Cleared!");

               })


           }

       );

       grid.loadPreferences();

   };


   render() {

       return (  

           <ReactDataGrid ref={(grid) => {this.grid = grid}} creationComplete={this.serverPreferences_CreationComplete.bind(this)} enablePrint={true}

               preferencePersistenceMode={server} enablePreferencePersistence={true} enableExport={true} forcePagerRow={true} pageSize={50}

               enableFilters={true} enableFooters={true} >

               <ReactDataGridColumnLevel>

                   <ReactDataGridColumn dataField="id" headerText="ID" />

                   <ReactDataGridColumn dataField="type" headerText="Type"/>

                   <ReactDataGridColumn dataField="" headerText=""/>

               </ReactDataGridColumnLevel>

           </ReactDataGrid>

       );

   }

}


$(document).ready(function () {

   render(<App />, document.getElementById("gridContainer"));

});



Basically, what we do here is that we wire up three event listeners:



Each of these event handlers, we go back to the server, passing in the relevant information and loading the preferences from the server. The data structure to store preferences looks like this


(MY-SQL):

delimiter $$


CREATE TABLE `gridpreference` (

 `idGridPreference` int(11) NOT NULL,

 `gridName` varchar(254) COLLATE utf8_unicode_ci DEFAULT NULL,

 `idUser` varchar(254) COLLATE utf8_unicode_ci DEFAULT NULL,

 `createdBy` varchar(254) COLLATE utf8_unicode_ci DEFAULT NULL,

 `createdDate` datetime DEFAULT NULL,

 `updatedBy` varchar(254) COLLATE utf8_unicode_ci DEFAULT NULL,

 `updatedDate` datetime DEFAULT NULL,

 `preferenceString` text COLLATE utf8_unicode_ci,

 PRIMARY KEY (`idGridPreference`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci$$



SQL Server:


USE [Flexicious]

GO


/****** Object:  Table [dbo].[Preferences]    Script Date: 11/27/2014 19:11:57 ******/

SET ANSI_NULLS ON

GO


SET QUOTED_IDENTIFIER ON

GO


CREATE TABLE [dbo].[Preferences](

       [PreferenceId] [int] NOT NULL,

       [GridName] [nvarchar](50) NOT NULL,

       [PreferenceString] [nvarchar](max) NOT NULL,

       [UserId] [nvarchar](255) NOT NULL

) ON [PRIMARY]


GO



And basically on the back-end,we have code that persists the preferences to the server.


Sample C# Code:

       /// <summary>

       /// Returns previously saved preferences for the provided

       /// Grid Name. This name corresponds to the preferencePersistenceKey

       /// from Flex

       /// </summary>

       /// <param name="gridName">The Grid To get preferences for</param>

       /// <returns>The persisted preference values</returns>

       public ActionResult GetSavedPreferences(string gridName)

       {

           //Check the database if we have previously peristed this preference.

           Preferences pref = entites.Preferences.Where(p => p.GridName.Equals(gridName) && p.UserId.Equals(SESSION_USER_ID)).FirstOrDefault();

           if (pref != null)

               return Content(pref.PreferenceString); //if we have preferences. return them

           else

               return Content("");//else return a blank string

       }

       /// <summary>

       /// Persists the preferences for the provided grid name

       /// </summary>

       /// <param name="gridName">Grid to persist the preferences for</param>

       /// <param name="prefXml">The preferences to persist</param>

       /// <returns>Success code.</returns>

       [ValidateInput(false)]

       public ActionResult PersistPreferences(string gridName, string prefXml)

       {

           //Check the database if we have previously peristed this preference.

           Preferences pref = entites.Preferences.Where(p => p.GridName.Equals(gridName) && p.UserId.Equals(SESSION_USER_ID)).FirstOrDefault();

           //If no, then create a new one..

           if (pref == null)

           {

               pref = new Preferences();

               entites.AddToPreferences(pref);

           }

           //Set the preference information

           pref.GridName = gridName;

           pref.PreferenceString = prefXml;

           //Here we're tying the preferences to your IP, but in your code,

           //you would tie it to your USER ID

           pref.UserID = SESSION_USER_ID;


           //save the preferences to the database

           

           

           entites.SaveChanges();

           return Content("Preferences Persisted!");

       }

       /// <summary>

       /// Clears previously saved preferences for the provided

       /// Grid Name. This name corresponds to the preferencePersistenceKey

       /// from Flex

       /// </summary>

       /// <param name="gridName">The Grid To clear preferences for</param>

       /// <returns>The persisted preference values</returns>

       public ActionResult ClearPreferences(string gridName)

       {

           //Check the database if we have previously peristed this preference.

           Preferences pref = entites.Preferences.Where(p => p.GridName.Equals(gridName) && p.UserId.Equals(SESSION_USER_ID)).FirstOrDefault();

           if (pref != null)

           {

               pref.PreferenceString = string.Empty;//we could alternatively delete the record too..

               entites.AddToPreferences(pref);

               entites.SaveChanges();

           }

           return Content("Preferences Removed!");

       }



Java Code:


   public static void createGridPreference(String gridName, String prefXml, String idUser)

           throws OSException {

       

       Connection c = null;

       PreparedStatement ps = null;

       

       int id = -1;

       long now = System.currentTimeMillis();

       


       try {


           c = createDBConnection();

           ps = prepareStatement(c, "INSERT INTO GridPreference (idGridPreference, gridName, idUser, createdBy, createdDate, updatedBy, updatedDate, preferenceString) values (?,?,?,?,?,?,?,?)");


           int i = 1;


           ps.setInt(i++, SQLSequencer.getNextValue(SQLSequencer.GRID_PREFERENCE_TABLE_CODE));

           ps.setString(i++, gridName);

           ps.setString(i++, GridPreference.ALL_USERS);

           ps.setString(i++, idUser);

           ps.setTimestamp(i++, new java.sql.Timestamp(SettingManager.offsetTime(now)));

           ps.setString(i++, idUser);

           ps.setTimestamp(i++, new java.sql.Timestamp(SettingManager.offsetTime(now)));

           ps.setString(i++, prefXml);




           ps.executeUpdate();


       } catch (SQLException x) {

           throw new OSException("Unable to create new grid preference.",

                   x);

       } finally {

           try {ps.close();} catch (Exception ignored) {}

           try {c.close();} catch (Exception ignored) {}

       }


   }

   public static void updateGridPreferences(String gridName, String prefXml, String idUser)

           throws OSException {

       

       Connection c = null;

       PreparedStatement ps = null;

       

       int id = -1;

       long now = System.currentTimeMillis();

       


       try {


           c = createDBConnection();


           ps = prepareStatement(c, "UPDATE GridPreference SET updatedBy=?, updatedDate=?, preferenceString=? WHERE idUser=? AND gridName=? ");

           int i = 1;

           ps.setString(i++, idUser);

           ps.setTimestamp(i++, new java.sql.Timestamp(SettingManager.offsetTime(now)));

           ps.setString(i++, prefXml);

           ps.setString(i++, GridPreference.ALL_USERS);

           ps.setString(i++, gridName);


           int numberUpdated = ps.executeUpdate();


           if (numberUpdated < 1) {

               throw new OSException("No grid preference was updated!  name: " + gridName + " does not exist under user: " + idUser + ".");

           } else if (numberUpdated > 1) {

               throw new OSException("Too many grid preferences update!  name: " + gridName + " belongs to more than 1 grid.");

           }


       } catch (SQLException x) {

           throw new OSException("Unable to update grid preferences.",

                   x);

       } finally {

           try {ps.close();} catch (Exception ignored) {}

           try {c.close();} catch (Exception ignored) {}

       }


   }


For a running demo, please refer http://blog.htmltreegrid.com/post/Adding-Preferences-Support.aspx