批量编辑使用复选框更新ASP.NET gridview中的多行 [英] Bulk edit update multiple rows in ASP.NET gridview using checkboxes

查看:95
本文介绍了批量编辑使用复选框更新ASP.NET gridview中的多行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在下面的HTML标记我有一个简单的ASP.Net GridView有3列。



第一列包含CheckBox,第二列包含显示ID的标签和文件夹的Access Path分别和第三列包含Label和DropDownList,分别用于显示和编辑文件夹的状态。



这里我使用GridView进行分页显示数据,数据大约是数百万行。 当我点击提交按钮更新复选框选中的行时,执行时间过长会更新表中的行。



For示例:对于5000行= 26分钟。

任何人都可以帮我解决此问题并缩短执行时间,以便在几秒钟内更新所有记录。



我尝试过:



请参阅代码:



在这里输入代码

< asp:GridView ID =gvACLReportrunat =serverAutoGenerateColumns =FalseCssClass =mgridEmptyDataText =No Records Exists ... DataKeyNames =ACLIdShowFooter =TrueHorizo​​ntalAlign =CenterWidth =100%AllowPaging =TrueEnableSortingAndPagingCallback =TruePageSize =500AllowSorting =TrueVisible =Falseonpageindexchanging =gvACLReport_PageIndexChanging EnableSortingAndPagingCallbacks =True>

< AlternatingRowStyle CssClass =mgridalt/& gt;

< PagerSettings PageButtonCount =10000/>

< PagerStyle CssClass =gridviewHorizo​​ntalAlign =Center>

< columns>

< asp:templatefield>

< headertemplate>

< asp:CheckBox ID =chkAllACLReport runat =serverAutoPostBack =trueOnCheckedChanged =OnCheckedChanged/>



< itemtemplate>

< asp: CheckBox ID =chkACLReportrunat =serverAutoPostBack =trueOnCheckedChanged =OnCheckedChanged/>



< ItemStyle Width =20px/> ;





< asp:TemplateField HeaderText =ACL Id>

< itemtemplate>

< asp:标签ID =lblACLIdrunat =serverText ='<%#Eval(ACLId)%>'>



< ItemStyle Width = 20px/>





< asp:TemplateField HeaderText =访问路径>

< itemtemplate>

< asp:TextBox ID =lblAccessPathACLrunat =serverRows =3Width =400pxText ='<%#Eval(AccessPath) %>'ReadOnly =TrueTextMode =MultiLineBorderStyle =NoneBorderWidth =0pxBackColor =Transparent>



< ItemStyle宽度=150px/>





< asp:TemplateField HeaderText =目录名称>

< itemtemplate>

< asp:TextBox ID =lblDirectoryNamerunat =serverRows =3Width =400pxText ='<%#Eval(导向器yName)%>'ReadOnly =TrueTextMode =MultiLineBorderStyle =NoneBorderWidth =0pxBackColor =Transparent>



< ItemStyle Width =150px/>







< asp:TemplateField HeaderText =用户组>

< itemtemplate>

< asp:Label ID = lblUserGrouprunat =serverText ='<%#Eval(UserGroup)%>'>



< ItemStyle Width =150px/>





< HeaderStyle CssClass =mgridheader/>

< RowStyle CssClass =mgriditem/>





//请检查此表包含下拉列表以更新状态并提交并清除按钮





状态:

< asp:DropDownList ID =ddlChangeStatusAutoPostBack =True AppendDataBoundItems =True

runat =serverWidth =200pxDataSourceID =SDSChangeStatusDataTextField =Status

DataValueField =StatusId>

< asp:ListItem Text = - 选择 - 值=0>



< asp:SqlDataSource ID = SDSChangeStatusrunat =serverConnectionString =<%$ ConnectionStrings:gtsgeneralconn%>

SelectCommand =VT_getStatusListSelectCommandType =StoredProcedure>

< asp:RequiredFieldValidator ID =RequiredFieldValidator4runat =serverControlToValidate =ddlChangeStatus

Display =DynamicErrorMessage =Select StatusInitialValue =0SetFocusOnError =True> *

< asp:Button ID =btnChangeStatusrunat =serverText =SubmitCausesValidation =False

onclick =btnChangeStatus_Click

/>

< asp:Button ID =btnChangeClearrunat =serverText =Clear

CausesValidation =Falseonclick =btnChangeClear_Click

/>





Code Behind:



protected void ChangeStatusGlobalSensitiveNonSensitiveReport()

{

int rowsAffected = 0;

foreach(gvGlobalSensitive.Rows中的GridViewRow行)

{

if(row.RowType == DataControlRowType.DataRow)

{

bool isChecked = row.Cells [0]。 Controls.OfType< CheckBox()。FirstOrDefault()。Checked;

if(isChecked)

{

using(SqlConnection con = new SqlConnection) (cs))

{

cmd =新的SqlCommand(VT_ACLReportChangeStatus,con);

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.CommandTimeout = 3600;

cmd.Parameters.AddWithValue(@ ChangeStatus,ddlChangeStatus .SelectedItem.Text.ToString());

cmd.Parameters.AddWithValue(@ ACLId,row.Cells [1] .Controls.OfType()。FirstOrDefault()。Text);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

rowsAffected ++ ;

}

}

}

lblUpdatedRowsMsg.Text = rowsAffected +Rows updated !!;

lblUpdateMsg.Text =详细保存成功!!;

gvGlobalSensitive.Visible = false;

tableChangeStatus.Visible = false;

divReport.Visible = false;

}

if(rowsAffected == 0)

{

lblUpdateMsg.Text =请选中复选框以更新状态!! ;

lblUpdatedRowsMsg.Text = rowsAffected +行更新!!;

}

}



//请检查存储过程:

ALTER PROCEDURE [dbo]。[VT_ACLReportChangeStatus]



@ChangeStatus nvarchar (50)= null,

@ACLId int



AS

//执行VT_ACLReportChangeStatus'完成' ,34

BEGIN

UPDATE VT_ACLReport SET Status = @ChangeStatus WHERE ACLId = @ACLId

结束

解决方案

ConnectionStrings:gtsgeneralconn%>

SelectCommand =VT_getStatusListSelectCommandType =StoredProcedure>

< asp:RequiredFieldValidator ID =RequiredFieldValidator4runat =serverControlToValidate =ddlChangeStatus

Display =DynamicErrorMessage =Select StatusInitialValue =0SetFocusOnError =真> *

< asp:Button ID =btnChangeStatusrunat = serverText =提交CausesValidation =False

onclick =btnChangeStatus_Click

/>

< asp:Button ID =btnChangeClearrunat =serverText =Clear

CausesValidation =Falseonclick =btnChangeClear_Click

/>





代码背后:



protected void ChangeStatusGlobalSensitiveNonSensitiveReport()

{

int rowsAffected = 0;

foreach(gvGlobalSensitive.Rows中的GridViewRow行)

{

if(row.RowType == DataControlRowType.DataRow)
{

bool isChecked = row.Cells [0] .Controls.OfType< CheckBox()。FirstOrDefault()。Checked;

if( isChecked)

{

使用(SqlConnection con = new SqlConnection(cs))

{

cmd = new SqlCommand(VT_ACLReportChangeStatus,con);

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.CommandTimeout = 3600;

cmd.Parameters.AddWithValue(@ ChangeStatus,ddlChangeStatus.SelectedItem.Text.ToString());

cmd.Parameters.AddWithValue(@ ACLId,row.Cells [1] .Controls .OfType()。FirstOrDefault()。Text);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

rowsAffected ++;

}

}

}

lblUpdatedRowsMsg.Text = rowsAffected +Rows updated !!;

lblUpdateMsg.Text =详细保存成功!!;

gvGlobalSensitive.Visible = false;

tableChangeStatus.Visible = false;

divReport.Visible = false;

}

if(rowsAffected == 0)

{

lblUpdateMsg.Text =请选中复选框以更新状态!! ;

lblUpdatedRowsMsg.Text = rowsAffected +行更新!!;

}

}



//请检查存储过程:

ALTER PROCEDURE [dbo]。[VT_ACLReportChangeStatus]



@ChangeStatus nvarchar (50)= null,

@ACLId int



AS

//执行VT_ACLReportChangeStatus'完成' ,34

BEGIN

UPDATE VT_ACLReport SET Status = @ChangeStatus WHERE ACLId = @ACLId


In the below HTML Markup I have a simple ASP.Net GridView 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 click on submit button to update the checkbox selected rows, execution time is taking too much to update the rows in table.

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.

What I have tried:

Please see the code:

enter code here
<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">
<columns>
<asp:templatefield>
<headertemplate>
<asp:CheckBox ID="chkAllACLReport" runat="server" AutoPostBack="true" OnCheckedChanged="OnCheckedChanged" />

<itemtemplate>
<asp:CheckBox ID="chkACLReport" runat="server" AutoPostBack="true" OnCheckedChanged="OnCheckedChanged"/>

<ItemStyle Width="20px" />


<asp:TemplateField HeaderText="ACL Id">
<itemtemplate>
<asp:Label ID="lblACLId" runat="server" Text='<%# Eval("ACLId") %>'>

<ItemStyle Width="20px" />


<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">

<ItemStyle Width="150px" />


<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">

<ItemStyle Width="150px" />



<asp:TemplateField HeaderText="User Group">
<itemtemplate>
<asp:Label ID="lblUserGroup" runat="server" Text='<%# Eval("UserGroup") %>'>

<ItemStyle Width="150px" />


<HeaderStyle CssClass="mgridheader" />
<RowStyle CssClass="mgriditem" />


//Please check this the table contain the drop down list to update the status and submit and clear button


Status:
<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:SqlDataSource ID="SDSChangeStatus" runat="server" ConnectionString="<%$ ConnectionStrings:gtsgeneralconn %>"
SelectCommand="VT_getStatusList" SelectCommandType="StoredProcedure">
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="ddlChangeStatus"
Display="Dynamic" ErrorMessage="Select Status" InitialValue="0" SetFocusOnError="True">*
<asp:Button ID="btnChangeStatus" runat="server" Text="Submit" CausesValidation="False"
onclick="btnChangeStatus_Click"
/>
<asp:Button ID="btnChangeClear" runat="server" Text="Clear"
CausesValidation="False" onclick="btnChangeClear_Click"
/>


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_ACLReportChangeStatus", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 3600;
cmd.Parameters.AddWithValue("@ChangeStatus", ddlChangeStatus.SelectedItem.Text.ToString());
cmd.Parameters.AddWithValue("@ACLId", row.Cells[1].Controls.OfType().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!!";
}
}

// Please check Stored Procedure:
ALTER PROCEDURE [dbo].[VT_ACLReportChangeStatus]
(
@ChangeStatus nvarchar(50)=null,
@ACLId int
)
AS
// Exec VT_ACLReportChangeStatus 'Complete',34
BEGIN
UPDATE VT_ACLReport SET Status = @ChangeStatus WHERE ACLId = @ACLId
End

解决方案

ConnectionStrings:gtsgeneralconn %>"
SelectCommand="VT_getStatusList" SelectCommandType="StoredProcedure">
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="ddlChangeStatus"
Display="Dynamic" ErrorMessage="Select Status" InitialValue="0" SetFocusOnError="True">*
<asp:Button ID="btnChangeStatus" runat="server" Text="Submit" CausesValidation="False"
onclick="btnChangeStatus_Click"
/>
<asp:Button ID="btnChangeClear" runat="server" Text="Clear"
CausesValidation="False" onclick="btnChangeClear_Click"
/>


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_ACLReportChangeStatus", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 3600;
cmd.Parameters.AddWithValue("@ChangeStatus", ddlChangeStatus.SelectedItem.Text.ToString());
cmd.Parameters.AddWithValue("@ACLId", row.Cells[1].Controls.OfType().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!!";
}
}

// Please check Stored Procedure:
ALTER PROCEDURE [dbo].[VT_ACLReportChangeStatus]
(
@ChangeStatus nvarchar(50)=null,
@ACLId int
)
AS
// Exec VT_ACLReportChangeStatus 'Complete',34
BEGIN
UPDATE VT_ACLReport SET Status = @ChangeStatus WHERE ACLId = @ACLId
End


这篇关于批量编辑使用复选框更新ASP.NET gridview中的多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆