DataTable批量更新中，执行到dr.SetModified()跳出问题。
我是想用ListBox调整好菜单顺序，后将index作为排序，批量更新到数据库中。
本来想着用法应该没什么问题，调试发现dr.SetModified()一执行就跳出。
datatable数据段(数据来自ListBox):
DataTable dt = new DataTable("T_Nav_1");
dt.Columns.Add(new DataColumn("Nav_1_id", typeof(Int32)));
//dt.Columns.Add(new DataColumn("Nav_1_Name", typeof(System.String)));
dt.Columns.Add(new DataColumn("NavOrder", typeof(Int32)));
for (int i=0; i < ListBox1.Items.Count; i++)
{
dt.Rows.Add(new object[] { ListBox1.Items[i].Value, i });//ListBox1.Items[i].Text,
}
int n = Update(dt);
public int Update(DataTable dt)
{
int res = 0;
dt.PrimaryKey = new DataColumn[] { dt.Columns["Nav_1_id"] };
using (SqlConnection sqlconn = new SqlConnection(DBUtility.PubConstant.ConnectionString))
{
sqlconn.Open();
//事务
SqlTransaction tran = sqlconn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
foreach (DataRow dr in dt.Rows)
{
//所有行设为修改状态
dr.SetModified();
}
//为Adapter定位目标表
SqlCommand cmd = new SqlCommand("select * from [T_Nav_1] where 1=0", sqlconn, tran);
SqlDataAdapter da = new SqlDataAdapter(cmd);
SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(da);
da.AcceptChangesDuringUpdate = false;
SqlCommand updatecmd = new SqlCommand("UPDATE [T_Nav_1] SET [NavOrder] = @NavOrder where Nav_1_id=@Nav_1_id");
//不修改源DataTable
updatecmd.UpdatedRowSource = UpdateRowSource.None;
da.UpdateCommand = updatecmd;
da.UpdateCommand.Parameters.Add("@NavOrder", SqlDbType.Int, 4, "NavOrder");
da.UpdateCommand.Parameters.Add("@Nav_1_id", SqlDbType.Int, 4, "Nav_1_id");
da.UpdateBatchSize = 1000;
res = da.Update(dt);
dt.AcceptChanges();
tran.Commit();
sqlconn.Close();
}
catch
{
tran.Rollback();
return -1;
}
}
return res;
}
