错误描述：如果分配给命令的连接位于本地挂起事务中，ExecuteNonQuery 要求命令拥有事务。命令的 Transaction 属性尚未初始化。
请各位大神帮帮忙，小弟在此谢过啦！
代码如下
protected void btnUpdate_Click(object sender, EventArgs e)
{
DataTable dt = CreateTable();
DataRow dr = dt.NewRow();
dr["tid"] = "1";
dr["tname"] = "xxx";
dr["tupdate"] = DateTime.Now.ToString();
dt.Rows.Add(dr);
UpdateDemo(dt);
Response.Redirect("/Update.aspx");
}
private DataTable CreateTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("tid", typeof(int));
dt.Columns.Add("tname", typeof(string));
dt.Columns.Add("tupdate", typeof(DateTime));
return dt;
}
private int UpdateDemo(DataTable dt)
{
string tableName = "testUpdate";
int res = 0;
using (SqlConnection sqlconn = new SqlConnection(strConn))
{
sqlconn.Open();
//事务
SqlTransaction tran = sqlconn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
dt.AcceptChanges();
foreach (DataRow dr in dt.Rows)
{
//所有行设为修改状态
dr.SetModified();
}
//为Adapter定位目标表
SqlCommand cmd = new SqlCommand("select * from " + tableName + " where 1=0", sqlconn, tran);
SqlDataAdapter da = new SqlDataAdapter(cmd);
SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(da);
da.AcceptChangesDuringUpdate = false;
string upSql = @"UPDATE {0} SET [tname]=@tname
,[tupdate]=@tupdate
where tid=@tid ";
upSql = string.Format(upSql, tableName);
SqlCommand updatecmd = new SqlCommand(upSql);
//不修改源DataTable
updatecmd.UpdatedRowSource = UpdateRowSource.None;
da.UpdateCommand = updatecmd;
da.UpdateCommand.Parameters.Add("@tname", SqlDbType.NVarChar, 50, "tname");
da.UpdateCommand.Parameters.Add("@tupdate", SqlDbType.DateTime, 8, "tupdate");
da.UpdateCommand.Parameters.Add("@tid", SqlDbType.Int, 4, "tid");//主键字段
da.UpdateBatchSize = 1000;
res = da.Update(dt);//报错位置
dt.AcceptChanges();
tran.Commit();
sqlconn.Close();
}
catch
{
tran.Rollback();
return -1;
}
}
return res;
}
数据表脚本如下
USE [TestUpdate] GO /****** Object: Table [dbo].[testUpdate] Script Date: 11/29/2014 17:32:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[testUpdate]( [tid] [int] NOT NULL, [tname] [nvarchar](50) NULL, [tupdate] [datetime] NULL, CONSTRAINT [PK_testUpdate] PRIMARY KEY CLUSTERED ( [tid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明C# DataTable 批量更新数据问题！
