none
Update a datatable as if using an sql update statement RRS feed

  • Question

  • I have a datatable and i am using it in a for loop

    foreach(datarow row in dt.rows)

    {

        do stuff....

       here i would like to update values in the dt table, would that affect the foreach?

    also how woud i do an update like..

    UPDATE dt SET amnt = BalAmnt WHERE fno = BPFno AND i_date <= BPIDate

     

     

    }

     

    I know i can do a  filter but wondering if theres a better way?

    Wednesday, May 5, 2010 8:12 PM

Answers

  • Hiii

    If you insert any rows in the Datatable inside the FOR..EACH loop, then it will affect the No Of Iterations.

    If you want to Update the DataTable like the T-SQL Statement you have 2 choices.

    1. Select the DataTable Rows using the Condition you have provided and Update Each row using Begin Edit and End Edit() Like this. 

    For each (Datarow row in dt.Rows)
    {
    
          DataRow[] RowCollection;
    
          RowCollection = Dt.Select("Fno = " & BPFno & " AND i_date <= " & BPIDate);
    
          foreach(datarow dr in RowCollection )
          {
            dr.beginedit();
            dr.item("amnt") = BalAmount;
            dr.endnedit();
          }
    
    }

    2. Use Linq on the DataTable Object to do the Updates like in TSQL Syntax. Please visit : http://msdn.microsoft.com/en-us/vbasic/bb688085.aspx

    Hope this would thrown some lights on your question.

    Thanks


    Best Regards,
    Gopi V

    If you have found this post helpful, please click the Vote as Helpful link (the green triangle and number on the top-left).

    If this post answers your question, click the Mark As Answered link below. It helps others who experience the same issue in future to find the solution.

    Thursday, May 6, 2010 6:45 AM
  • I went with :

     

     private static void UpdateTemp2(string filter, DataTable temp, string updateColumn , string updateValue)
     {
            DataView dv = new DataView(temp);
     
            dv.RowFilter = filter;

            foreach (DataRowView drv in dv)
                drv[updateColumn] = updateValue;
     
          //  dv.RowFilter = string.Empty;
            dv = null;
    }

    Friday, May 7, 2010 7:53 PM

All replies

  • Hiii

    If you insert any rows in the Datatable inside the FOR..EACH loop, then it will affect the No Of Iterations.

    If you want to Update the DataTable like the T-SQL Statement you have 2 choices.

    1. Select the DataTable Rows using the Condition you have provided and Update Each row using Begin Edit and End Edit() Like this. 

    For each (Datarow row in dt.Rows)
    {
    
          DataRow[] RowCollection;
    
          RowCollection = Dt.Select("Fno = " & BPFno & " AND i_date <= " & BPIDate);
    
          foreach(datarow dr in RowCollection )
          {
            dr.beginedit();
            dr.item("amnt") = BalAmount;
            dr.endnedit();
          }
    
    }

    2. Use Linq on the DataTable Object to do the Updates like in TSQL Syntax. Please visit : http://msdn.microsoft.com/en-us/vbasic/bb688085.aspx

    Hope this would thrown some lights on your question.

    Thanks


    Best Regards,
    Gopi V

    If you have found this post helpful, please click the Vote as Helpful link (the green triangle and number on the top-left).

    If this post answers your question, click the Mark As Answered link below. It helps others who experience the same issue in future to find the solution.

    Thursday, May 6, 2010 6:45 AM
  • I went with :

     

     private static void UpdateTemp2(string filter, DataTable temp, string updateColumn , string updateValue)
     {
            DataView dv = new DataView(temp);
     
            dv.RowFilter = filter;

            foreach (DataRowView drv in dv)
                drv[updateColumn] = updateValue;
     
          //  dv.RowFilter = string.Empty;
            dv = null;
    }

    Friday, May 7, 2010 7:53 PM