In the below HTML Markup I have a simple ASP.Net Grid View with 3 columns. First column containing the CheckBox, second column containing a Label for display ID and Access Path of the folder respectively and the third column containing a Label and DropDownList for display and edit status of the folder respectively Here I am using GridView with paging to display the data, and the data is about millions of rows. When i Execute or click on submit button, execution time is taking too much.
For Example : for 5000 rows = 26 min.
Can anyone help me to resolve this issue and reduce the execution time to update all the records within few seconds.
<asp:GridView ID="gvACLReport" runat="server" AutoGenerateColumns="False" CssClass="mgrid"
EmptyDataText="No Records Exists..." DataKeyNames="ACLId" ShowFooter="True" HorizontalAlign="Center"
Width="100%" AllowPaging="True" EnableSortingAndPagingCallback="True" PageSize="500"
AllowSorting="True" Visible="False" OnPageIndexChanging="gvACLReport_PageIndexChanging"
EnableSortingAndPagingCallbacks="True">
<AlternatingRowStyle CssClass="mgridalt" />
<PagerSettings PageButtonCount="10000" />
<PagerStyle CssClass="gridview" HorizontalAlign="Center"></PagerStyle>
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="chkAllACLReport" runat="server" AutoPostBack="true" OnCheckedChanged="OnCheckedChanged" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="chkACLReport" runat="server" AutoPostBack="true" OnCheckedChanged="OnCheckedChanged" />
</ItemTemplate>
<ItemStyle Width="20px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="ACL Id">
<ItemTemplate>
<asp:Label ID="lblACLId" runat="server" Text='<%# Eval("ACLId") %>'></asp:Label>
</ItemTemplate>
<ItemStyle Width="20px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Access Path">
<ItemTemplate>
<asp:TextBox ID="lblAccessPathACL" runat="server" Rows="3" Width="400px" Text='<%# Eval("AccessPath") %>'
ReadOnly="True" TextMode="MultiLine" BorderStyle="None" BorderWidth="0px" BackColor="Transparent"></asp:TextBox>
</ItemTemplate>
<ItemStyle Width="150px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Directory Name">
<ItemTemplate>
<asp:TextBox ID="lblDirectoryName" runat="server" Rows="3" Width="400px" Text='<%# Eval("DirectoryName") %>'
ReadOnly="True" TextMode="MultiLine" BorderStyle="None" BorderWidth="0px" BackColor="Transparent"></asp:TextBox>
</ItemTemplate>
<ItemStyle Width="150px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Object Type">
<ItemTemplate>
<asp:DropDownList ID="ddlChangeStatus" AutoPostBack="True" AppendDataBoundItems="True"
runat="server" Width="200px" DataSourceID="SDSChangeStatus" DataTextField="Status"
DataValueField="StatusId">
<asp:ListItem Text="--Select--" Value="0"></asp:ListItem>
<asp:ListItem Text="Work In progress" Value="0"></asp:ListItem>
<asp:ListItem Text="Complete" Value="0"></asp:ListItem>
</asp:DropDownList>
</ItemTemplate>
<ItemStyle Width="100px" />
</asp:TemplateField>
</Columns>
<HeaderStyle CssClass="mgridheader" />
<RowStyle CssClass="mgriditem" />
</asp:GridView>
Code Behind:
protected void ChangeStatusGlobalSensitiveNonSensitiveReport()
{
int rowsAffected = 0;
foreach (GridViewRow row in gvGlobalSensitive.Rows)
{
if (row.RowType == DataControlRowType.DataRow)
{
bool isChecked = row.Cells[0].Controls.OfType < CheckBox().FirstOrDefault().Checked;
if (isChecked)
{
using (SqlConnection con = new SqlConnection(cs))
{
cmd = new SqlCommand("VT_GlobalSensitiveReportChangeStatus", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 3600;
cmd.Parameters.AddWithValue("@ChangeStatus", ddlChangeStatus.SelectedItem.Text.ToString());
cmd.Parameters.AddWithValue("@SensitiveNonSensitiveId", row.Cells[1].Controls.OfType<Label>().FirstOrDefault().Text);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
rowsAffected++;
}
}
}
lblUpdatedRowsMsg.Text = rowsAffected + " Rows updated!!";
lblUpdateMsg.Text = "Detail Saved Successfully!!";
gvGlobalSensitive.Visible = false;
tableChangeStatus.Visible = false;
divReport.Visible = false;
}
if (rowsAffected == 0)
{
lblUpdateMsg.Text = "Please select the check box to update the status!!";
lblUpdatedRowsMsg.Text = rowsAffected + " Rows updated!!";
}
}