none
insert row in table ....using dataadepter update method.... RRS feed

  • Question

  • hi...

    I'm reading row from datatable and then inserting this row in database row by row .I'm handeling rowupdatedevent of the dataadepter .

    but i got some strange result like...

    1 row in datatable rowupdated event runs 1 time..

    2 row in datatable rowupdated event runs 2 times..

    3 row in datatable rowupdated event runs 3 times..

    if there is 1000 row then rowupdated event runs 1000 times...

    why this is happening ?

    here is the code

     using (SqlDataAdapter sda = new SqlDataAdapter(@"select * from " + destinationtablename, con1))
                            {  
                                sda.FillSchema(test, SchemaType.Mapped);
                                SqlCommandBuilder ocb = new SqlCommandBuilder(sda);
                                sda.InsertCommand = ocb.GetInsertCommand();
                                sda.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdating);
                                totalrecords = dt.Rows.Count;
                                foreach (DataRow dr in dt.Rows)
                                {
    
                                    j++;
                                    try
                                    {
                                        DataRow newrow = dr;
                                        test.Rows.Add(newrow.ItemArray);
                                        sda.Update(test); 
                                       
                                    }
                                    catch (Exception ex)
                                    {
                                       
                                    }
                                }
                                
                            } 
    
     private static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs e)
            {
                
                    if (e.Status == UpdateStatus.ErrorsOccurred)
                    {
                        k++;
                        Console.WriteLine("k {0}", k);
                        e.Status = UpdateStatus.SkipCurrentRow;                  
                            
                        
                    }
                   
            }

       
    • Edited by .netaholic Sunday, August 26, 2012 8:23 AM
    • Moved by Val MazurModerator Tuesday, September 11, 2012 5:02 PM (From:ADO.NET DataSet)
    Sunday, August 26, 2012 8:12 AM

Answers

  • OK, so the above steps that you've outlined are what you're doing to test the RowUpdated event handler, correct?

    So, your first test is inserting X number of rows, and it all went fine. The RowUpdated event fired X number of times and there were no Status errors reported.

    Next, your second test, insert those same X rows. Now, the RowUpdated event fires X times and each time reports an error for each row and you skip the update of that row.

    Now, for your third test, you said:

    then i delete first 50 rows and and again i re-insert ...then delete rows was not insert but I think it should be insert since i have deleted them...

    So, this appears to be the only problem that you're having with your testing right now, correct? My question for you is how did you delete these rows? In SQL Server itself with SSMS (SQL Server Management Studio)? Or did you do this in code also? If so, how did you do it?


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, August 26, 2012 10:17 PM

All replies

  • Why does this surprise you? You're updating 1000 rows, one at a time, the RowUpdated event will fire 1000 times, once for each row updated.

    This is obviously just a test, but I'm wondering what you were actually planning to do by handling the RowUpdated event?


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, August 26, 2012 3:46 PM
  • hi...

    I'm reading row from the datatable and want to insert in database ...

    but what happens if there is primary key aur candidate key then it gives me error while inserting row to database ...

    to test that I have inser all rows first time ...that has done without any problem...

    then I re-insert all and the all rows get rejected due to constraint....

    then i delete first 50 rows and and again i re-insert ...then delete rows was not insert but I think it should be insert since i have deleted them...

    so this is the sinario so I plan to handll the RowUpdated event ...and write the code like SkipCurrentRow in case if there is error to move to nest row...

     if (e.Status == UpdateStatus.ErrorsOccurred)
                    {
                        k++;
                        Console.WriteLine("k {0}", k);
                        e.Status = UpdateStatus.SkipCurrentRow;                  
                            
                        
                    }

    Sunday, August 26, 2012 6:15 PM
  • OK, so the above steps that you've outlined are what you're doing to test the RowUpdated event handler, correct?

    So, your first test is inserting X number of rows, and it all went fine. The RowUpdated event fired X number of times and there were no Status errors reported.

    Next, your second test, insert those same X rows. Now, the RowUpdated event fires X times and each time reports an error for each row and you skip the update of that row.

    Now, for your third test, you said:

    then i delete first 50 rows and and again i re-insert ...then delete rows was not insert but I think it should be insert since i have deleted them...

    So, this appears to be the only problem that you're having with your testing right now, correct? My question for you is how did you delete these rows? In SQL Server itself with SSMS (SQL Server Management Studio)? Or did you do this in code also? If so, how did you do it?


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, August 26, 2012 10:17 PM
  • i'm deleting from the SSMS...

    I think the problem is with the sda.Update() method that I'm doing row by row int the foreach loop ...

    foreach (DataRow dr in dt.Rows)
                                {
    
                                    j++;
                                    try
                                    {
    
                                        DataRow newrow = dr;
                                        test.Rows.Add(newrow.ItemArray);
                                        sda.Update(test);               //here it is 
                                        Console.WriteLine("j {0}", j);
                                    }
                                    catch (Exception ex)
                                    {
                                        using (System.IO.StreamWriter file = new System.IO.StreamWriter(logfilepath, true))
                                        {
    
                                            file.WriteLine("{0}--{1}", j, ex.Message);
                                            file.WriteLine("");
                                        }
                                        Console.WriteLine("{0}...{1}", j, ex.Message);
                                    }
                                }

    I have have write sda.Update(test); out side the foreach loop and it has worked find .....

    but I don't understatnd what was wrong ?

    Monday, August 27, 2012 5:49 AM
  • Hi .netaholic,

    to test that I have inser all rows first time ...that has done without any problem...

    then I re-insert all and the all rows get rejected due to constraint....

    This issue occurs because the primary key should be unique, the table can't contains two rows have a same primary key. The RowUpdated event fired X time because, the former insert failed and the dataset think there is X time of changed have not submitted. You can get how many rows will going to update by checking DataTable.GetChange().Rows.Count.

    then i delete first 50 rows and and again i re-insert ...then delete rows was not insert but I think it should be insert since i have deleted them...

    This is weird. If you have delete them from the SSMS, the code should insert them correctly. It may failed because it encounter other error message, could you please post the error message when the adapter trying to update the deleted row?

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, August 29, 2012 6:09 AM
    Moderator