Save Grid State in Database

[Sharmila]

I have a requirement where I have to save my Grid state in the database. 

A user will come to the grid page and sort, filter and group the columns as he wishes. 

He should be able to save that grid state in the database.

He should be able to keep that as a default setting for the grid when he enters grid page later.

He should be able to save different grid states and wish to select one from the saved settings using a dropdown list.

Below is my code for my ASP.Net web forms grid control where I bind the data from database dynamically to the GRID in aspx.cs file so the columns are auto generated.

.aspx

<ej:Grid ID="FlatGrid" runat="server" OnServerExcelExporting="FlatGrid_ServerExcelExporting" AllowPaging="True"

AllowFiltering="True" AllowSorting="True" AllowGrouping="True" AllowResizing="True" AllowResizeToFit="True" AllowScrolling="true"
AllowReordering="True" CssClass="" DataSourceCachingMode="None"      EnableLoadOnDemand="False"      MinWidth="0"   Width="1000px" 

SelectionType ="Multiple" >
                            <ClientSideEvents DataBound="bound" />
                            <ResizeSettings ResizeMode="NextColumn" ></ResizeSettings>
                            <PageSettings EnableQueryString="true" PageSize="10" />
                           <ToolbarSettings ShowToolbar="true" ToolbarItems="excelExport"></ToolbarSettings>
                            <FilterSettings FilterType="Menu"></FilterSettings>

</ej:Grid>

.aspx.cs

FlatGrid.DataSource = SampleBO;
FlatGrid.DataBind();

Note: I came across one implementation with ASP.Net MVC. I need something for ASP.Net web forms.

[Vignesh Natarajan]

Hi Sharmila, 

 

Thanks for contacting Syncfusion support.  

 

We already have a knowledge base documentation regarding your query. Please refer the below link for the KB documentation. 

 

KB link: https://www.syncfusion.com/kb/6312/how-to-store-retrieve-grid-model-values-into-from-database 

 

In the KB we have stored the groupedcolumns. Similarly you can store the grid state after performing any operation. For example we have stored the grid state after the filtering operation is performed in the below sample. Please refer the below link for the sample. 

 

Sample: http://www.syncfusion.com/downloads/support/forum/142362/7z/Grid-1635432418 

 

Please refer the below code example. 

 

 

<input type="text" id="list" /> 

   

<div id="StateList"> 

    <ul></ul> 

</div> 

 

<input type="button" id="save" value="Save State" /> 

<input type="button" id="apply" value="Apply State" /> 

 

 

 

  <div> 

        <ej:Grid ID="FlatGrid" runat="server" AllowPaging="True" AllowFiltering="true" OnServerExcelExporting="FlatGrid_ServerExcelExporting"> 

            <ToolbarSettings ShowToolbar="true" ToolbarItems="excelExport,wordExport,pdfExport"></ToolbarSettings> 

            <FilterSettings FilterType="Excel"></FilterSettings> 

            <Columns> 

                 <ej:Column Field="OrderID" HeaderText="Order ID" IsPrimaryKey="True" TextAlign="Right" Width="75" /> 

                <ej:Column Field="CustomerID" HeaderText="Customer ID" Width="80" /> 

                <ej:Column Field="EmployeeID" HeaderText="Employee ID" TextAlign="Right" Width="75" Priority="4" /> 

            </Columns> 

        </ej:Grid> 

    </div> 

 

 

   <script type="text/javascript"> 

       $(function () { 

            //Render dropdown 

    $('#list').ejDropDownList({ 

        targetID: "StateList" 

    }); 

    //render buttons 

    $("#save").ejButton({ click: "saveState" });//for saving Grid's state 

    $("#apply").ejButton({ click: "applyState" });//for applying the Grid's state 

   

       })   

    

</script> 

 

 

    <script type="text/javascript"> 

   

    function saveState(args) { 

        var gridObj = $(".e-grid").ejGrid("instance"); 

        var dropDownObj = $('#list').ejDropDownList("instance"); 

   

        //saving the Current value of groupedColumns 

        var state = { 

            cols: gridObj.model.columns.slice(), 

            filteredcol: gridObj.model.filterSettings.filteredColumns.slice() 

        } 

        var object = JSON.stringify(state);//converting object to string 

    

        $.ajax({ 

            type: "POST", 

            url: "/Default.aspx/Query", 

            data:JSON.stringify({ "gridObj": object }),//posting the grid object as string 

            datatype: "json",  

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

            success: function (data, status, xhr) { 

                //On Success save the data which is the time 

                //based on the time saving of Grid object in the db takes place 

                var TempData = []; 

                var obj = $('#list').ejDropDownList("instance"); 

   

                if (!ej.isNullOrUndefined(obj.model.dataSource)) 

                    TempData = obj.model.dataSource; 

   

                TempData.push({ dataTime: data.d, text: data.d }); 

   

                $('#list').ejDropDownList("destroy"); 

                //destroy and update the dropdownlist's dataSouce 

                $('#list').ejDropDownList({ dataSource: TempData }) 

            }, 

        }); 

    } 

    function applyState(args) { 

        var gridObj = $(".e-grid").ejGrid("instance"); 

        var obj = $('#list').ejDropDownList("instance"); 

        var value = obj.model.value; 

   

        //Post the saved (in ejDropDownlist) time  

        //To retrieve the Grid objects 

        $.ajax({ 

            type: "POST", 

            url: "/Default.aspx/Restate", 

            data:JSON.stringify({ "dropObject": value }), 

            datatype: "json",  

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

            success: function (data) { 

                var obj = JSON.parse(data.d); 

                gridObj.model.filterSettings.filteredColumns = obj.filteredcol; 

                if (obj.cols) 

                    gridObj.columns(obj.cols); 

                else 

                    gridObj.refreshContent();//Refresh the Grid to apply the saved settings 

                //if you are using anyother Grid methods to refresh other functionlities  

                //like columns() 

                //there is no need of refreshContent() 

            }, 

        }); 

    } 

</script> 

 

[aspx.cs] 

 

[WebMethod] 

        [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 

        public static string  Query(string gridObj) 

        { 

            con.Open(); 

            var time = DateTime.Now.TimeOfDay.ToString(); 

            //Inserting Grid object into StateStore Table with respective time 

            //States and CurrentTime are two columns in table 

            //States have the corresponding object whereas the CurrentTime as time 

            SqlCommand insert = new SqlCommand("INSERT INTO StateStore (States,CurrentTime ) VALUES('" + gridObj + "','" + time + "')", con); 

            insert.ExecuteNonQuery(); 

            con.Close(); 

            return time;//can be saved in ejDropDownList 

        } 

        [WebMethod] 

        [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 

        public static string Restate(string dropObject) 

        { 

            if (con.State != System.Data.ConnectionState.Open) 

                con.Open(); 

            //Retrieving the Grid object from the StateStore Table based on the time 

            SqlCommand take = new SqlCommand("SELECT States FROM StateStore WHERE CurrentTime ='" + dropObject + "'", con); 

            SqlDataAdapter da = new SqlDataAdapter(take); 

            DataTable dt = new DataTable(); 

            da.Fill(dt); 

            con.Close(); 

            return dt.Rows[0]["States"].ToString(); 

        } 

 

Please get back to us if you have further queries. 

 

Regards, 

Vignesh Natarajan