ASP.NET Web Forms Filtering and Batch Updates

[Eugene]

Good afternoon,

Two issues to request advisement/help on here;

First, I'm having some issues in getting a Filter applied initially on load to the Grid. Currently, no or C# code is interacting with it yet.

The reason for this is the necessity to have all of the selectable items for a DropDownEdit in the table for them to be selectable - however those records I do not want visible from the user side as they will not be edited since they have no key in the main table and so nothing will match for the Update command to edit them and there needs to be at least one entry for each selectable option in the table.

Second, is there a way to use Batch update for the table, in using an as the DataSourceID for the Grid, or to at least not have the SQL data and page fully reload every time a record is pushed for Updating, without creating a massive JS/C# workaround and redesiging how the table pulls data?

Simple Sample code effectively matching how the current page is working is included below. The page is an embeded aspx with an overlaying Site.Master:

<%@ Page Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="True" CodeBehind="default.aspx.cs" Inherits="AppName.apps.subtype._default" %>

<%@ Register assembly="Syncfusion.EJ.Web, Version=19.2460.0.44 Culture=neutral, PublicKeyToken=... namespace="Syncfusion.JavaScript.Web" tagprefix="ej" %>

<%@ Register assembly="Syncfusion.EJ, Version=19.2460.0.44 Culture=neutral, PublicKeyToken=... namespace="Syncfusion.JavaScript.Models" tagprefix="ej" %>


<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">


<asp:SqlDataSource ID="AList" runat="server" ConnectionString="<% ConnetionStrings:AConnectionString %>" SelectCommand="SELECT DISTINCT Table1.a, Table1.b, Table1.c, Table2.d FROM Table1 INNER JOIN Table2 ON Table1.d = Table2.key UNION NULL AS 'a', NULL AS 'b', NULL AS 'c', Table2.d" UpdateCommand="UPDATE Table1 SET Table1.b = @b, Table1.c=@c, Table1.d = Table2.key FROM Table1 INNER JOIN Table2 ON Table2.d=@d WHERE Table1.a=@a">

<UpdateParameters>

<asp:Parameter name="a" Type="String" />


<asp:Parameter name="b" Type="String" />

<asp:Parameter name="c" Type="DateTime" />

<asp Parameter name="d" Type="String />

</UpdateParameters>

</asp:SqlDataSource>




<ej:Grid ID="Grid" runat="server" CSSClass="" AllowScrolling="True" DataSourceID="AList" ...>

<columns>

<ej:Column DataType="String" Field="a" IsPrimaryKey="True" Visible="False">

</ej:Column>


<ej:Column DataType="String" Field="b" AllowEditing="True" AllowEditing="True" Width="125" HeaderText="Name1">

</ej:Column>

<ej:Column DataType="date" Field="c" AllowEditing="True" Width="125" HeaderText="NameDate">

</ej:Column>

<ej:Column DataType="String" Field="d" AllowEditing="True" Width="125" HeaderText="DropDownReason" EditType="DropDownEdit">

</ej:column>


</columns>

<EditSettings AllowEditing="True" ShowConfirmDialog="False" AllowEditOnDblClick="True" EditMode="Normal" />

<FilterSettings ShowFilterBarStatus="True" FilterType="Excel" FilterBarMode="OnEnter" MaxFilterChoices="5000" />

<ToolbarSettings ShowToolbar="True" ToolbarItems="edit,update,cancel" />

</ej:Grid>


</asp:Content>

[Pon Selva Jeganathan]

Hi Eugene, 

 

Thanks for contacting syncfusion forum. 

 

Query: First, I'm having some issues in getting a Filter applied initially on load to the Grid. Currently, no or C# code is interacting with it yet. 

 

We suggest that you use FilteredColumn property of FilterSettings  to have an initial filter value in columns on page load as show in the below code example.  

  

<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="MainContent" ClientIDMode="Static">  

         <ej:Grid ID="OrdersGrid" runat="server" AllowPaging="true" AllowFiltering="true" >  

                  <FilterSettings FilterType="Menu" >  

                      <FilteredColumn>   

                       <ej:FilteredColumn Field="ShipCity" Operator="NotEquals"   Value="Reims" /> 

                      </FilteredColumn>   

         </FilterSettings>  

                      <Columns>  

                          ………….. . .  

                      </Columns>    

          </ej:Grid>  

</asp:Content>  

  

  

Please refer to the below screenshot, 

   

  

If the above solution does not meet your requirement, kindly get back to us with the below requested details, 

 

1. Video demo of requirement with exact scenario
2. Kindly share the detailed explanation of your requirement.

 

Query: Second, is there a way to use Batch update for the table, in using an as the DataSourceID for the Grid,  

 

Based on your query, we suggest you use the EditMode as ‘Batch’. Using Batch edit, you can bulk save (added, changed and deleted data in the single request) to data source by click on the toolbar’s update button. 

 

Please refer to the below sample, 

https://www.syncfusion.com/downloads/support/forum/168221/ze/webform_-sample1374229922 

 

Please refer to the below help documentation, 

https://help.syncfusion.com/aspnet/grid/editing#batch--excel-like 

 

Please refer to the below API documentation, 

https://help.syncfusion.com/api/js/ejgrid#members:editsettings-editmode 

 

Kindly get back to us for further assistance.                                                                                                                                         

 

Regards, 

Pon selva  

 

[Eugene]

For the First issue with the filtering, your sugestion worked. THank you very much for that.

For the second issue, Batch is not working as an update method with the objects from the page.

When set as

<EditSettings AllowEditing="True" ShowConfirmDialog="True" AllowEditOnDblClick="True" EditMode="Normal" />

The system is not giving a Confirm Dialog, but it is willing to write to the back end SQL as exected, and there is the issue of a delay with the form reloading. This reload time has been able to be cut down by using dynamic paging and the size of the query, but it is still between 1-2 seconds.

When the line is set as 

<EditSettings AllowEditing="True" ShowConfirmDialog="True" AllowEditOnDblClick="True" EditMode="Batch" />


The form will give a poup asking for confirmation of the edit, but after the confirmation it does not communicate with the back end and nothing is written to SQL. There is no delay from the webpage as it is not reloading and just utilizing the cached edits, but those edits are not writing.

[Pon Selva Jeganathan]

Hi Eugene, 

 

Thank you for the update. 

 

Query: First, I'm having some issues in getting a Filter applied initially on load to the Grid. Currently, no or C# code is interacting with it yet. 

 

 We are glad to hear your query has been resolved by our solution. 

 

Query: Second, is there a way to use Batch update for the table, in using an as the DataSourceID for the Grid,  

 

Based on your query, we suggest you use the Batch edit mode and OnServerBatchEditRow event of grid. Using the OnServerBatchEditRow event, save the batchChanges at server end . 

 

Please refer to the below code example, 

 

Default.aspx:-  

  

  

<ej: Grid  runat="server" AllowPaging="True" OnServerBatchEditRow="OrdersGrid_ServerBatchEditRow" ClientIDMode="Static" >  

   <Columns>  

      <ej:Column Field="OrderID" HeaderText="Order IsPrimaryKey="true"  TextAlign="Right" Width="80">                           

      </ej:Column>  

        

    </Columns>  

  <EditSettings AllowEditing="True" AllowAdding="True" AllowDeleting="True" EditMode="Batch"></EditSettings>  

  <ToolbarSettings ShowToolbar="True" ToolbarItems="add,edit,delete,update,cancel"></ToolbarSettings>  

      

</ej:Grid>  

  

  

Default.aspx.cs:-  

  

 

public void ServerBatchEditRow(object sender, GridEventArgs e)  

 

        { 

      ArrayList changed = ((ArrayList)((Dictionary<String, Object>)e.Arguments["batchChanges"])["changed"]); 

      if (changed.Count > 0) 

        GridAction("change", changed, e); 

      ArrayList deleted = ((ArrayList)((Dictionary<String, Object>)e.Arguments["batchChanges"])["deleted"]); 

      if (deleted.Count > 0) 

        GridAction("delete", deleted, e); 

      ArrayList added = ((ArrayList)((Dictionary<String, Object>)e.Arguments["batchChanges"])["added"]); 

      if (added.Count > 0) 

        GridAction("add", added, e); 

 

    } 

    protected void GridAction(string eventType, ArrayList changed,  Syncfusion.JavaScript.Web.GridEventArgs e) 

    { 

       

      SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ToString()); 

      dt = Session["SqlDataSource"] as DataTable; 

      //Dictionary<string, object> KeyVal = record as Dictionary<string, object>; 

      if (eventType == "change") 

      { 

        for (var i = 0; i <= changed.Count - 1; i++) 

        { 

          Dictionary<string, object> KeyVal = changed[0] as Dictionary<string, object>; 

          var Order = KeyVal.Values.ToArray();  

          foreach (DataRow dr in dt.Rows) 

          { 

            if (Convert.ToInt32(dr["OrderID"]) == Convert.ToInt32(Order[0])) 

            { 

              dr["Freight"] = Order[7]; 

              dr["EmployeeID"] = Order[2]; 

 

              dr["ShipCity"] = Order[10]; 

               

 

              dr.AcceptChanges(); 

            } 

          } 

        } 

      } 

 

 

      else if (eventType == "add") 

      { 

        Dictionary<string, object> KeyVal = changed[0] as Dictionary<string, object>; 

      var Order1 = KeyVal.Values.ToArray();  

       

      DataRow dr = dt.NewRow(); 

      dr["OrderID"] = Order1[0]; 

      dr["EmployeeID"] = Order1[1]; 

      dr["Freight"] = Order1[2]; 

      dr["ShipCity"] = Order1[3]; 

       

      dt.Rows.Add(dr); 

      } 

      else if (eventType == "delete") 

      { 

        Dictionary<string, object> KeyVal = changed[0] as Dictionary<string, object>; 

        var Order = KeyVal.Values.ToArray(); 

        if (Session["SqlDataSource"] != null) 

        { 

          DataRow[] rows = dt.Select("OrderID = " + Order[0]); 

 

          foreach (DataRow row in rows) 

            dt.Rows.Remove(row); 

 

        } 

      } 

 

      Session["SqlDataSource"] = dt; 

          this.EmployeesGrid2.DataSource = dt; 

          this.EmployeesGrid2.DataBind(); 

        } 

       

 

  

  

Please refer to the below sample, 

https://www.syncfusion.com/downloads/support/forum/168221/ze/webform-SQL-serverbatchedit-1790774185 

 

Please refer to the below help documentation, 

https://help.syncfusion.com/aspnet/grid/server-side-events#onserverbatcheditrow 

 

Kindly get back to us for further assistance. 

 

Regards, 

Pon selva