Save grid state (column visibility, filtering, sorting) in database

Hatem Muhsen

Asked On June 30, 2019 01:10 AM UTC

I'd like to allow the user to save the state of the grid (after they have sorted, filtered, etc) so when the next time they visit the app the grid is saved in the same state as they left it. I understand there is the option for local save but I want the user to have the ability to save their own view permanently as well as a "reset" button to reset the view back to default.

I've seen an example you guys did but it was for the EJ model. I'm using EJS. Here's my code:

View

@(Html.EJS().Grid<ProjectMvcModel>("Grid")

.DataSource(DataManager => { DataManager.Url("/Proj/UrlDataSource").CrudUrl("/Proj/CrudUpdate").Adaptor("UrlAdaptor"); })

//.DataSource(DataManager => { DataManager.Url("/PMRS/UrlDataSource").InsertUrl("/PMRS/Insert").Adaptor("UrlAdaptor"); })

.Width("100%")

.AllowResizing(true)

.AllowFiltering()

.FilterSettings(filter => filter.Type(Syncfusion.EJ2.Grids.FilterType.Excel))

.AllowSelection()

.AllowGrouping()

//.AllowMultiSorting()

//.AllowTextWrap()

.AllowSorting(true)

.AllowReordering()

.ShowColumnChooser(true)

.ShowColumnMenu(true)

.AllowExcelExport(true)

.EnablePersistence(true)

.ToolbarClick("toolbarClick")

.GroupSettings(group => { group.ShowDropArea(false).ShowUngroupButton(true); })

//.ActionBegin("onActionBegin")

.Columns(col =>

{

col.Field(p => p.Id).Width("60").IsPrimaryKey(true).IsIdentity(true).Type("number").Width("120").Add();

col.HeaderText("Project Details").Visible(true).Template(" <a rel='nofollow' href='ProjectDetailsMaster?id=${Id} '>View</a> ").Width("140").AllowEditing(false).Add();

//col.Field(p => p.Id).Width("75").IsPrimaryKey(true).Type("number").Add();

col.Field(p => p.ProjName).AllowSorting(true).Width("175").Visible(false).Add();

col.Field(p => p.GroupName).EditType("dropdownedit").Width("175").ValidationRules(new { required = "true" }).Add();

col.Field(p => p.ProjDesc).ValidationRules(new { required = "true" }).Width("325").Add(); //allow editing(true)

col.Field(p => p.PortLead).ValidationRules(new { required = "true" })/*.EditType("dropdownedit")*/.Width("180").Add();

col.Field(p => p.ProjLead).ValidationRules(new { required = "true" })/*.EditType("dropdownedit")*/.Width("180").Add();

col.Field(p => p.ProgramName).ValidationRules(new { required = "true" }).EditType("dropdownedit").Width("180").Add();

col.Field(p => p.AssignedDate).ValidationRules(new { required = "true" }).Format("yMd").EditType("datepickeredit").Width("175").Add(); //Date

col.Field(p => p.StatusName)/*.Template("#statusTemplate")*/.EditType("dropdownedit").Width("175").Template("#statusTemplate").ValidationRules(new { required = "true" }).Add();

col.Field(p => p.DecisionTypeName).EditType("dropdownedit").Width("180").Add();

col.Field(p => p.JRCDate).Format("yMd").EditType("datepickeredit").Width("175").Add(); //Date

col.Field(p => p.PriorityName).EditType("dropdownedit").Width("175").ValidationRules(new { required = "true" }).Add();

col.Field(p => p.CompletionDate).Format("yMd").EditType("datepickeredit").Width("200").Visible(false).Add();

col.Field(p => p.CreatedOn).Format("yMd").Width("200").Visible(false).Add();

col.Field(p => p.CreatedBy).Width("200").Visible(false).Add();

col.Field(p => p.ModifiedOn).Format("yMd").Width("200").Visible(false).Add();

col.Field(p => p.ModifiedBy).Width("180").Visible(false).Add();

col.Field(p => p.KSNLink).Width("180").Visible(false).Add();

//col.Field(p => p.ProjSched).Width("180").Visible(false).Add();

//col.Field(p => p.Budget).Width("100").Add();

})

//.Load("load")

//.ActionBegin("actionBegin")

//.BatchAdd("insert")

//.CellSave("insert")

.ActionComplete("actionComplete")

.AllowPaging()

.QueryCellInfo("querycellinfo")

//.TextWrapSettings(text => { text.WrapMode(Syncfusion.EJ2.Grids.WrapMode.Header); })

.PageSettings(page => page.PageCount(5))

.EditSettings(edit => { edit.AllowAdding(true).AllowEditing(true).AllowDeleting(true).ShowDeleteConfirmDialog(true).Mode(Syncfusion.EJ2.Grids.EditMode.Normal); })

.Toolbar(new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel", "ColumnChooser", "ExcelExport", "Search" })

.PageSettings(page =>

{

//page.PageSizes(true);

page.PageSize(25);

//page.PageSizes(new List<int>() { 10, 25, 50, 100 });

page.PageSizes(new List<string>() { "10", "25", "50", "100", "All" });

//page.PageSizes(new List<string>() { "All" });

})

//.Created("onGridCreate")

//.RowSelected("rowSelected")

//.Load("load")

//.ActionComplete("complete")

//.ActionBegin("Begin")

//.Height("600")

//.EnableVirtualization(true)

//.SearchSettings()

.Render()

)

</div>

Also, do I need a separate table in my db?

Any help would be appreciated. Thank you.

Reply | Reply with quote

Thavasianand Sankaranarayanan [Syncfusion]

Replied On July 1, 2019 01:18 PM UTC

Hi Hatem,

Thanks for contacting Syncfusion support.

From your query we found that you want to store and retrieve the Grid state in DB. To store the Grid state, we suggest you to use the ajax to send the Grid state to server and store it in your DB. You can use Grid getPersistData method to collect the current persist data in Grid.

For the retrieve action you need to collect the stored Grid state from DB and provide it to Grid through the setProperties method. Please refer the below code snippet,

Store button click:

function store(e) { // button click

var grid = document.getElementById("Grid").ej2_instances[0];

var persistData = JSON.stringify({ persistData: grid.getPersistData() }); // Grid persistData

var ajax = new ej.base.Ajax({ // used our ajax to send the stored persistData to server

url: "/Home/StorePersistData",

type: "POST",

contentType: "application/json; charset=utf-8",

datatype: "json",

data: persistData

});

ajax.send();

}

function reset(e) { // button click

var ajax = new ej.base.Ajax({ // used our ajax to send the retrive the persistData from server

url: "/Home/restore",

type: "POST",

contentType: "application/json; charset=utf-8"

});

ajax.send();

ajax.onSuccess = function (result) {

var grid = document.getElementById("Grid").ej2_instances[0];

var state = JSON.parse(result);

grid.setProperties({ // provide the retrived state to Grid through the setProperties method

filterSettings: state.filterSettings,

sortSettings: { columns: state.sortSettings.columns } // provide the retrieved values to GRid

}, false);

grid.setProperties({

columns: state.columns

})

}

</script>

Controller:

public void StorePersistData(string persistData)

{

gridState = persistData;

// please do the DB save action here

}

public string restore()

{

// please do the retrieve action here

return gridState;

}

Note: In this code we have used local variable to save and retrieve the state in server for the sample purpose. Please modified this codes with your requirement.

We have prepared the sample with this requirement and you can find that sample from the below link,

Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/EJ2Grid-214726025.zip

Regards,

Thavasianand S.

Reply | Reply with quote

Hatem Muhsen

Replied On July 9, 2019 05:41 PM UTC

Thank you for getting back to me. This solution worked but with a few minor bugs. I have a Template column with "View" links that take users to the record's detail page. The "View" link disappears when the user clicks the Apply State button (in green). I have attached two pictures, one with the original view and the other view when the user reverts back to the saved state. The other issue is that the column names return as their database names, not the display names I want them to be (refer to both screen shots).

Here is my razor code:

@(Html.EJS().Grid<ProjectMvcModel>("Grid")

.DataSource(DataManager => { DataManager.Url("/Proj/UrlDataSource").CrudUrl("/Proj/CrudUpdate").Adaptor("UrlAdaptor"); })

//.DataSource(DataManager => { DataManager.Url("/PMRS/UrlDataSource").InsertUrl("/PMRS/Insert").Adaptor("UrlAdaptor"); })

.Width("100%")

.AllowResizing(true)

.AllowFiltering()

.FilterSettings(filter => filter.Type(Syncfusion.EJ2.Grids.FilterType.Excel))

.AllowSelection()

.AllowGrouping()

//.AllowMultiSorting()

//.AllowTextWrap()

.AllowSorting(true)

.AllowReordering()

.ShowColumnChooser(true)

.ShowColumnMenu(true)

.AllowExcelExport(true)

.EnablePersistence(true)

.ToolbarClick("toolbarClick")

.GroupSettings(group => { group.ShowDropArea(false).ShowUngroupButton(true).ShowGroupedColumn(true).ShowToggleButton(true); })