100分问题！！C# DataTable 批量更新数据问题 大家帮帮忙

[zwenli001]

错误描述：如果分配给命令的连接位于本地挂起事务中，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

[D56233577]

写一大堆垃圾代码，真难看啊。不知道你的目的是什么

[bdmh]

两个command（查询和提交）对事务处理有问题
http://www.cnblogs.com/zhangzhu/archive/2013/08/22/3276307.html

[枫0子K]

好像是你没把事物tran赋值给Command，记得Command里面有个Transaction之类的属性。

[zwenli001]

版主正解 ok 搞定 结贴给分!