How to store/retrieve grid model values into/from database
How to store/retrieve grid model values into/from database
We can retrieve/save Grid settings/model values from or into the SQL Table and apply them to Grid.
HTML
<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 id="Grid"></div>
JS
<script type="text/javascript"> $(function () { $("#PersistenceGrid").ejGrid({ dataSource: window.gridData, allowPaging: true, allowGrouping: true, showColumnChooser: true, columns: [ { field: "OrderID" }, { field: "CustomerID" }, { field: "EmployeeID" }, { field: "ShipName" }, { field: "ShipCity" } ] }); }); </script>
Razor
@(Html.EJ().Grid<object>("PersistenceGrid") .Datasource((IEnumerable<object>)ViewBag.dataSource) .AllowPaging() .AllowGrouping() .ShowColumnChooser() .Columns(col => { col.Field("OrderID").Add(); col.Field("CustomerID").Add(); col.Field("EmployeeID").Add(); col.Field("ShipName").Add(); col.Field("ShipCity").Add(); }) )
Controller
namespace Sample.Controllers { public class HomeController : Controller { static string cons = ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString; static SqlConnection con = new SqlConnection(cons); public ActionResult Index() { var dataSource = OrderRespositary.GetAllRecords(); ViewBag.dataSource = dataSource; return View(); } public 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 } public 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(); } } }
Render ejDropDownList to store the different time and two ejButtons for saving and applying the Grid states.
<script type="text/javascript"> //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>
On Clicking the save button, current state of Grid i.e. its model values will be save in the SQL dataSource which in turn saves the respective time in the ejDropDownList.
Based on the ejDropDownList’s value (time), click event of “apply” button will retrieve Grid objects from the StateStore table and apply the changes to the Grid.
<script type="text/javascript"> function saveState(args) { var gridObj = $("#PersistenceGrid").ejGrid("instance"); var dropDownObj = $('#list').ejDropDownList("instance"); //saving the Current value of groupedColumns var state = { cols: gridObj.model.columns.slice(), groupedCol: gridObj.model.groupSettings.groupedColumns.slice() } var object = JSON.stringify(state);//converting object to string $.ajax({ type: "POST", url: "/Home/Query", data: { "gridObj": object },//posting the grid object as string 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, text: data }); $('#list').ejDropDownList("destroy"); //destroy and update the dropdownlist's dataSouce $('#list').ejDropDownList({ dataSource: TempData }) }, }); } function applyState(args) { var gridObj = $("#PersistenceGrid").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: "/Home/Restate", data: { "dropObject": value }, success: function (data) { var obj = JSON.parse(data); gridObj.model.groupSettings.groupedColumns = obj.groupedCol; 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>
Likewise, follow the above steps to apply states for ASP.Net Grid.
The following screenshot show Persistence Grid with the dropdown and buttons for applying the Grid state.
Figure 1: Grid state from the dropdown