[2008] Inserting/updating multiple rows into a database's table

I was curious, how do other people insert/update multiple rows into a database's table?

If I got a loop and I'm enumerating some list that I need to push multiple Insert/Update commands to a database, in the past I've directly embedded values into SQL and run them as a single command:

Code:

---

        Dim strSQL As String = String.Empty

        For Each s As String In strList
            strSQL &= String.Format("INSERT INTO MyTable (ID, Name) VALUES ('{0}', '{1}'); ", strID, s)
        Next
        Dim cmd As New MySql.Data.MySqlClient.MySqlCommand(strSQL, con)
        cmd.ExecuteNonQuery()

---

Though it's easy, I don't like doing this for obvious reasons (single quotes come to mind) and would rather use parameters, but then I'd have to make a parameter for every line.

I could use multiple Command objects, but this would be extremely slow if I have a substantial amount of data to insert and I'd be inserting them one-by-one. I suppose I could use a DataAdapter, but building and pushing updates to DataAdapters always seems like a major pain in the rear (especially if I have a lot of fields)

How do you guys do it in this situation? Is there some trick I'm missing for pushing multiple inserts/updates? Should I just make a DataTable + DataAdapter and push multiple updates/inserts that way?

Here's how I do it:
1) Start the loop
2) Build the command object
3) Add the parameters
4) Execute the command
5) Repeat loop until done.

plain and simple. Just the way I like it.

-tg

Yea, but when I do it that way, performance stinks. What would take a few seconds can take more than a minute when I have to populate a large table.

You've knind of contradicted yourself there. First you ask:

Quote:

---

I was curious, how do other people insert/update multiple rows into a DataTable?

---

and then you ask about saving data to a database using methods that wouldn't involve a DataTable. Why would you use a DataTable at all if you're not going to use a DataAdapter or TableAdapter to save the data with an Update call? That's the way I'd save multiple records.

Sorry, I meant insert/update multiple rows into a database's table.

(updated first post and title)

I do it this way..
1>Update/Delete/Insert new rows in Datatable this way each row has it's own rowstate value
2>Create a Insert/Update/Delete SQLCommand Object with Parameter assign it to DataAdapter.
3>Call DataAdapter's Update method,this will update the Database table based i.e it will insert/delete/update rows based on each rows rowstate value.

Quote:

---

Originally Posted by riteshjain1982

I do it this way..
1>Update/Delete/Insert new rows in Datatable this way each row has it's own rowstate value
2>Create a Insert/Update/Delete SQLCommand Object with Parameter assign it to DataAdapter.
3>Call DataAdapter's Update method,this will update the Database table based i.e it will insert/delete/update rows based on each rows rowstate value.

---

Further to this, you can set the UpdateBatchSize of the DataAdapter to make the operation more efficient by sending multiple records to the database at a time.

What does UpdateBatchSize default to? I assume one command?

Quote:

---

Originally Posted by Jenner

What does UpdateBatchSize default to? I assume one command?

---

Why assume, or ask, when you can test? Create an adapter and test the value. That's about 20 seconds work and you have your answer.