SqlDataAdapter.UpdateBatchSize seems only commit 1 row each time

Question

       SqlDataAdapter adapter = new SqlDataAdapter();            
       string update = "update tableA set path = @path where Id = @ID";            
       string select = "select ... from tableA";
       SqlCommand cmd = new SqlCommand(update, connection);
       cmd.Parameters.Add("@ID", SqlDbType.NVarChar, 50, "ID");
       cmd.Parameters.Add("@path", SqlDbType.NVarChar, 4000, "Path");          
       cmd.UpdatedRowSource = UpdateRowSource.None;
       adapter.SelectCommand = new SqlCommand(select, connection);            
       adapter.UpdateCommand = cmd;
       adapter.UpdateCommand.CommandTimeout = 0;
       adapter.SelectCommand.CommandTimeout = 0;
       adapter.UpdateBatchSize = 200;
            ...
       adapter.Update(dataset);     

      To make sure this code should commit every 200 rows I keep on running this SQL to monitor the table:
 
         select COUNT(*) from pdt_tableA where path is null 

        Suppose originally there are 1000 rows with path is null, I expect the result value should be like 800, 600,
 400, 200, 0. But in fact I get the result like 991, 986, 982 and so on.  So I think the code only commit one row every time.

         My question is why UpdateBatchSize = 200 doesn't work? 

Answer 1

Hi Roger Zhaos,

Welcome to MSDN Forum.

Could you please tell me where do you execute the select query? I think it may because the batch process has not completed when you execute the select statement.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi Roger,

Have you solved the issue?

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 3

No, I still didn't solve this issue. I run this select count(*) sql in the Microsoft SQL Server Management Studio to watch the result in the same time when I am running this code in Visual Studio 2010. And I have set Transaction Isolation Level = Read Committed in the MSSMS

By this way I should ONLY see the data committed to the database, that's why I think UpdateBatchSize doesn't work.

Answer 4

Hi Roger,

i think there may be confusion on what you understand by committed data:

I suspect the mechanism behind the UpdateBatchSize is like the 'go' statement in sql; it gets the statements to the server, and then runs them, avoiding roundtrips to the server for every statement.

This has nothing to do with records being committed as in 'Read Committed', because committed here means it only reads records that have been 'Transaction' committed, i.e. you first need to make (start) a transaction, update some records, and then commit that transaction.

I don't see any transactions in your code, so it would be normal to see the progress of the updates as you do.

---

Regards, Nico

Answer 5

Nico,

       You are right, I misunderstood the behind mechanism of UpdateBatchSize. It just did a batch update not a transaction process so I should see the progress of the updates.  

 

Thanks for your help.