none
Update More Than 1 Datasets In A Batch RRS feed

  • Question

  • Hi....

    Can More Than 1 Dataset (Strongly Typed) be updated in a batch.

    By Batch I mean that, if  any thing any where goes wrong: the whole transaction will be cancelled.

    Thanks and Regards,

    IBelongToYou

     


    I Love Microsoft
    Tuesday, March 15, 2011 7:25 AM

Answers

  • Well, you already mentioned what you need to do, and that is to use Transactions. Here's one way to utilize Transactions. There are other methodologies also:

    // TransactionScope is in the System.Transactions namespace
    using (TransactionScope scope = new TransactionScope())
    {
      try
      {
        // code to do your DataSet updates, maybe something like this:
        UpdateMyDataSet(DataSet1);
        UpdateMyDataSet(DataSet2);
        scope.Complete(); // this commits the transaction
      }
      catch (Exception ex)
      {
        Console.WriteLine("Cannot complete transaction. Error: {0}", ex.Message);
      }
    }
    
    

    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, March 20, 2011 12:37 AM

All replies

  • Hello IBelongToYou,

     

    Welcome to the MSDN Forum and thanks for posting here.

    The following shows how to issue a single DELETE SQL command given the IDs.

    (Note that there is probably some break even point after which you should not add more parameters.  I don't know if this is in the hundreds/thousands/etc of parameters at once.  If concerned, split the set of IDs into reasonable sized subsets and call the method on each of these.)

        private static void DeleteRecords(IList<int> IDsToDelete)
        {
          if (IDsToDelete.Count == 0)
            return; // Nothing to do.
    
          using (SqlConnection conn = new SqlConnection("Database=tempdb;Integrated Security=SSPI"))
          {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
              StringBuilder commandText = new StringBuilder();
    
              commandText.Append("DELETE ProductsTable WHERE ProductID IN (");
              for (int i=0; i<IDsToDelete.Count; i++)
              {
                if (i != 0)
                  commandText.Append(", ");
                string paramName = "@ID" + Convert.ToString(IDsToDelete[i], System.Globalization.CultureInfo.InvariantCulture);
                commandText.Append(paramName);
                cmd.Parameters.AddWithValue(paramName, IDsToDelete[i]);
              }
              commandText.Append(")");
    
              cmd.Connection = conn;
              cmd.CommandText = commandText.ToString();
    
              cmd.ExecuteNonQuery();
            }
          }
        }
    
    For the Batch Update, I found these tutorials:
    I hope this can help and please feel free to tell me if I misunderstood you.
    Have a nice day,

    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 17, 2011 6:50 AM
    Moderator
  • Well, you already mentioned what you need to do, and that is to use Transactions. Here's one way to utilize Transactions. There are other methodologies also:

    // TransactionScope is in the System.Transactions namespace
    using (TransactionScope scope = new TransactionScope())
    {
      try
      {
        // code to do your DataSet updates, maybe something like this:
        UpdateMyDataSet(DataSet1);
        UpdateMyDataSet(DataSet2);
        scope.Complete(); // this commits the transaction
      }
      catch (Exception ex)
      {
        Console.WriteLine("Cannot complete transaction. Error: {0}", ex.Message);
      }
    }
    
    

    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, March 20, 2011 12:37 AM