none
typed dataset - batch updates and deletes? RRS feed

  • Question

  • Hello,
    Using VS 2008 SP1, .net 3.5: I'm using typed datasets for a project. I have the basic CRUD operations working with the auto-generated methods as well as a few custom parameterized methods added. How do I handle batch updates/deletes? For example, a user creates a list of ProductID's to be deleted from the database by populating a listbox. I don't want to loop through this list and make a separate DELETE call for each one, that sounds very inefficient.

    How do implement a:
    DELETE FROM ProductsTable
    WHERE ProductID IN (id1, id2, id3,id4, id4, id6, id7, id8);

    or

    DELETE FROM ProductsTable
    WHERE ProductID = id1 OR ProductID = id2 OR ProductID = id3 etc...

    Also, I'm assuming this needs to be some kind of dynamic sql (but please correct me if I'm wrong), what about protecting against sql injection?
    Tuesday, March 16, 2010 12:17 PM

Answers

  • 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();
                    }
                }
            }

     

     

    • Marked as answer by c0pe Monday, March 22, 2010 2:46 PM
    Saturday, March 20, 2010 2:22 PM

All replies

  • Are you specifically trying to avoid using data adapter / table adapter to submit the deletes once you have marked the rows for delete within the DataSet through the DataRow.Delete method?

    The adapter has an UpdateBatchSize which can be set to optimize the number of batches sent to the database server.  (If you are not using SQL Server, your database driver might not provide this kind of batch mode.)

    http://msdn.microsoft.com/en-us/library/kbbwt18a(VS.80).aspx

    Wednesday, March 17, 2010 2:34 AM
  • actually, no, not trying to avoid using data adapter / table adapter, I think I just don't know exactly what I'm doing yet with these typed datasets.

    basically, my user will be creating a list of ID's by populating a listbox. To keep it general, because I'm pretty sure I'll need to use this pattern frequently in this app, assume the question applies to insert/update/deletes, rather than one specific one.

    how should I go about doing the insert/update/deletes for all the items in the list? I don't want a separate database trip for each one, and most of the tables I'll be working with are too large to just pull all the records down to work with in-memory.

    I've seen examples that first call a method that returns a datatable with all records, then loops through each record, works on whatever rows are needed, then sends the datatable object as a parameter to the tableadapters's update method. So it does use one trip to the database for the update, but it first queries for all records of the table. I'm doing this for a handheld device with limited memory, I can't be pulling down tons of data at once.

    I have a parameterized method that returns a datatable like GetItemsByID(string ID), but I assume multiple calls to this within a loop will make one trip to the database for each call...

    not sure how to handle, but again, once I learn the appropriate pattern it is going to be used frequently throughout this app.

    Wednesday, March 17, 2010 3:13 PM
  • Are you saying that you do not have the rows in a DataTable but (somehow) you know the exact set of IDs you want to delete?

    (If you already had them in a DataTable, then you could call Delete() to mark the rows and pass that DataTable to an adapter which will execute the deletes.  Assuming batch mode is set up on that adapter, the commands can be sent in batches instead of one-for-one.)

    The problem where you just want to use the IDs directly is solvable by various technique (involves "parameterized SQL" to address the security matter).  I can provide more info, but want to get your requirements nailed down first.

    Wednesday, March 17, 2010 11:18 PM
  • that is correct, I know the exact IDs that need to be deleted (or in some cases updated), but I don't have them in a datatable, at least not yet, thats where I'm not sure how to handle. How can I first bring down just those rows into a datatable? if that is even the right approach, I don't want to lead you down a specific path if it's not the right way anyway. I have a paramaterized method like GetItemByID(theID) which would get the one specific item into a datatable, but before I just put that in a loop and did for each ID I wanted to check if that is the right thing to do... I'm assuming a could do something like that, then somehow merge all those datatables into one so I can make the one call to the adapters update method passing in the one datatable so it gets batched?

    what about something like this:
    make a method that instead of returning a datatable returns that typed datarow. Then instantiate the typed datatable object outside of and before the loop starts, in each iteration of the loop return one datarow and add it to that datatable, then when the loop completes I will have a datatable with all the rows that need to be deleted (or updated in other cases) and I pass that datatable into the dataadapters update method?

    I'm not sure exactly, but I would love to hear how you would handle it. The user populates a listbox in the gui with the ID's I need to work with. I'm looking for the appropriate way to work with them, taking into consideration these couple things: minimize trips to the db, and the program will be running on a smartdevice with limited RAM.

    your help is very much appreciated.
    thanks!
    Thursday, March 18, 2010 2:56 PM
  • still following this thread BinaryCoder? I hope so :)
    Saturday, March 20, 2010 2:01 PM
  • 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();
                    }
                }
            }

     

     

    • Marked as answer by c0pe Monday, March 22, 2010 2:46 PM
    Saturday, March 20, 2010 2:22 PM
  • thanks BC, I thought it may come to something like that, but I hadn't thought of using the actual ado.net parameter structure within what is essentially a dynamic sql pattern, which would protect against sql injection, right? If I understand correctly, very cool, if not, please correct me.

    Thanks!

    Monday, March 22, 2010 2:46 PM