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

[Jenner]

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:

        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?

[techgnome]

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

[Jenner]

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.

[jmcilhinney]

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

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.

[Jenner]

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

(updated first post and title)

[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.

[jmcilhinney]

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.

[Jenner]

What does UpdateBatchSize default to? I assume one command?

[jmcilhinney]

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.