Visual Basic 2005: A Developer's Notebook

Batch DataAdapter Commands for Better Performance

Many databases are able to execute commands in batches, reducing the total number of calls you need to make. For example, if you submit 10 update commands in a single batch, your code only needs to make 1 trip to the server (instead of 10). Cutting down the number of round-trips can increase performance, particularly on networks that have a high degree of latency. In .NET 2.0, the SqlDataAdapter is enhanced to use batching for updating, inserting, and deleting records.

Note

If you need an easy way to optimize DataSet updates, ADO. NET 's new batching can help you out.

How do I do that?

In previous versions of .NET, you could batch direct commands by concatenating them in a single string, and separating each with a semicolon. This syntax requires support from the database provider, but it works perfectly well with SQL Server. Here's an example that inserts two rows into a table:

Dim TwoInserts As String ="INSERT INTO Shippers" &_
  "(CompanyName, Phone) VALUES "ACME", "212-111-1111;" & _
  "INSERT INTO Shippers (CompanyName, Phone)" &_
  VALUES "Grey Matter", "416-123-4567"
    
Dim cmd As New SqlCommand(TwoInsert)
cmd.ExecuteNonQuery( )

As useful as this feature is, previous versions of .NET didn't provide any way to batch commands to one of the most important ADO.NET provider objects—the data adapter. The data-adapter object scans a DataSet, and executes insert, delete, and update commands whenever it finds a new, removed, or changed row. Each ...