Articles in this section
Category / Section

How to store/retrieve grid model values into/from database in LightSwitch Grid?

3 mins read

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

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment