1. Home
  2. Forum
  3. ASP.NET Core - EJ 2
  4. Pivod grid from SQL database

Pivod grid from SQL database

Dear All,

If there is a better way to implement below; please feel free to give me an example:


I have a requirement to be able to read 3 columns from the database and present them as a editable pivot grid.
The DB table is as shown below. The value assigned is either 1 or 0.


Please find attached my code so far. What I am missing is:

- How to capture the values changed in the cell edit method so that I can update them into the DB?

- The value of the assigned field is always 1 or zero, can we have a field template to replace it by a checkbox which is checked or unchecked depending on the assigned value (checked when value is 1 and uncheked when value is 0)

- Lastly, would be great if it is batch editable, so instead of calling the method everytime when cell is edited; can we edit all cells and then save them with a button click?

Thanks a lot in advance,




















Attachment: PivotGrid_e868d92e.rar

4 Replies
SU Suriya Syncfusion Team October 1, 2018 12:51 PM UTC

Hi Prasad, 
 
  
Please find the response below. 

Query 
Response 
How to capture the values changed in the cell edit method so that I can update them into the DB? 
You can use the “CellEdit” event, to get the cell information and also call the server side method through AJAX on the same event (“CellEdit”). Please refer the code snippet below. 

Code Snippet: [ASPX] 
<ej:PivotGrid ID="PivotGrid1" runat="server" ClientIDMode="Static" EnableCellEditing="true"> 
//… 
<ClientSideEvents Load="onLoad" CellEdit="OnCellEdit"/> 
</ej:PivotGrid> 
 
Code Snippet: [JavaScript] 
function OnCellEdit(args) { 
        var test = "prasad"; 
        $.ajax({ 
            type: "POST", 
            url: "/Relational/LoadGridData", 
            data: JSON.stringify({ 
                Test: args 
            }), 
            contentType: 'application/json; charset=utf-8', 
            dataType: 'json', 
            async: false, 
            success: function (val) { 
                alert("Success"); 
            }, 
            error: function (msg) { 
                alert("Error"); 
            } 
        }); 
    } 

Code Snippet: [C#] 
[HttpPost] 
[System.Web.Http.ActionName("LoadGridData")] 
  public Dictionary<string, object> LoadGridData(Dictionary<string, object> Test) 
   { 
    return dict; // based on rowheader and column header values, you can update the cell values in DB. 
} 
 
The value of the assigned field is always 1 or zero, can we have a field template to replace it by a checkbox which is checked or unchecked depending on the assigned value (checked when value is 1 and uncheked when value is 0) 
You show the check boxes (checked when value is 1 and uncheked when value is 0) by using the “renderSuccess” event. Please refer the code snippet below. 

Code Snippet: [ASPX] 

<ej:PivotGrid ID="PivotGrid1" runat="server" ClientIDMode="Static" EnableCellEditing="true" EnableToolTip="false"> 
<ClientSideEvents Load="onLoad" CellEdit="OnCellEdit" RenderSuccess="renderSuccess"/> 
</ej:PivotGrid> 


CodeSnippet: [JavaScript] 
function renderSuccess(args) { 
         
         
   pivotGridElement = this.element.find("table td"); 
     for (var i = 0; i < pivotGridElement.length; i++) { 
        if (pivotGridElement[i].innerHTML == "") 
          { 
                checkBox1 = document.createElement("input") 
                checkBox1.className = 'check0'; 
                pivotGridElement[i].appendChild(checkBox1); 
                 
            } 
            else if (pivotGridElement[i].innerHTML == "1") { 
                checkBox2 = document.createElement("input") 
                checkBox2.className = 'check1'; 
                pivotGridElement[i].appendChild(checkBox2); 
               
            } 
             
        } 
        $('.check0').ejCheckBox({ checked: false }); 
        $('.check1').ejCheckBox({ checked: true }); 
    } 

Note: We have achieved your requirement as an work around solution and we are facing the following issues. Also, please confirm us whether this requirement satisfied. Hence, we will check on this further. 

Known issues: 1) While hover cells which contain checkboxes, script error thrown (Meanwhile, we have disabled the EnableToolTip API). 
                           2) While check (or) uncheck the checkboxes, script error thrown. 



Lastly, would be great if it is batch editable, so instead of calling the method everytime when cell is edited; can we edit all cells and then save them with a button click? 
You can maintain the cell values in an array through the “CellEdit” event. And, you can pass the values from client side to server side through AJAX method on button click. 

Code Snippet: [ASPX] 
<ClientSideEvents Load="onLoad" CellEdit="OnCellEdit"> 


Code Snippet: [JavaScript] 
<script type="text/javascript"> 
    var batchObject=[]; 
     
    function OnCellEdit(args) { 
         
        batchObject.push(args.editCellsInfo.JSONRecords); 
    } 
 
    function myFunction() { 
        var test = "prasad"; 
        $.ajax({ 
            type: "POST", 
            url: "/Relational/LoadGridData", 
            data: JSON.stringify({ 
                Test: batchObject 
            }), 
            contentType: 'application/json; charset=utf-8', 
            dataType: 'json', 
            async: false, 
            success: function (val) { 
                alert("Success"); 
            }, 
            error: function (msg) { 
                alert("Error"); 
            } 
        }); 
    } 
   
</script> 
 
Code Snippet: [C#] 
[HttpPost] 
[System.Web.Http.ActionName("LoadGridData")] 
  public Dictionary<string, object> LoadGridData(Dictionary<string, object> Test) 
   { 
    return dict; // based on rowheader and column header values, you can update the cell values in DB. 
} 
 
 


Meanwhile, we have prepared a sample for your convenience. Please find the sample in the below link. 

 
Regards, 
Suriya 


PG Prasad Gurla October 1, 2018 03:28 PM UTC

Thanks for the reply. I am still struggling reading the edited cell values on backend code. My JSON string looks like below.

How do I read the rowheader, columnHeader and Value (in this case, 5000 as seen below).

I know this is not a syncfusion related problem but still hoping you can help.

  • "editCellsInfo":{
    • "JSONRecords":[
      1. {
        • "Index":"1,2",
        • "CSS":"value",
        • "Value":"5000",
        • "State":0,
        • "RowSpan":1,
        • "ColSpan":1,
        • "Info":"",
        • "Span":"None",
        • "Expander":0,
        • "MCnt":1
        }
      ]      ,
    • "rowHeader":[
      1. "player1"
      ]      ,
    • "columnHeader":[
      1. "comp1#Flag"
      ]
    },
  • "type":"cellEdit",
  • "model":{

SU Suriya Syncfusion Team October 2, 2018 07:09 AM UTC

Hi Prasad, 

You can use the below code snippet to get “rowHeader” and “columnHeader” values. 

Code Snippet:[C#] 
public Dictionary<string, object> LoadGridData(Dictionary<string, object> Test) 
{ 
 String rowHeader, columnHeader; 
 dynamic data = serializer.Deserialize<dynamic>(Test["Test"].ToString()); 
 foreach (KeyValuePair<string, object> i in data["editCellsInfo"]) 
  { 
    if(i.Key == "rowHeader") 
       rowHeader = ((object[])i.Value)[0].ToString(); 
    else if(i.Key == "columnHeader") 
       columnHeader = ((object[])i.Value)[0].ToString(); 
  } 
return dict; 
} 
 

 
Regards, 
Suriya 


PG Prasad Gurla October 2, 2018 07:43 PM UTC

Thanks a lot! Works nicely.

Loader.
Up arrow icon