SqlCommandBuilder

SqlCommandBuilder is a helper class in ADO.NET that automatically generates SQL statements for reconciling changes made through a data adapter with a SQL Server database. It derives the InsertCommand, UpdateCommand, and DeleteCommand from the SelectCommand of a SqlDataAdapter and creates parameterized SQL that matches the schema of the underlying table.

The class operates best with simple, single-table queries. It requires that the target table has a primary key (or another suitable unique key). Generated commands use the primary key to identify rows and often include original values for concurrency control. The resulting commands can be accessed through the data adapter’s InsertCommand, UpdateCommand, and DeleteCommand properties, or by calling GetInsertCommand, GetUpdateCommand, and GetDeleteCommand on the builder after it is associated with the adapter.

SqlCommandBuilder also handles quoting of identifiers to accommodate reserved words or special characters via the QuotePrefix and QuoteSuffix properties, which are typically set to [ and ] for SQL Server.

Usage typically involves creating a SqlDataAdapter with a SELECT statement, constructing a SqlCommandBuilder with that adapter, and then using the adapter to update a DataSet or DataTable. Once created, the builder populates the adapter’s command properties automatically, enabling adapter.Update to propagate changes back to the database.

Limitations include its unsuitability for complex queries, multi-table joins, views, or queries without a primary key. If the SELECT statement does not yield a suitable key or if the table schema changes, the generated commands may be invalid or require manual adjustment. The class is available in System.Data.SqlClient (and in compatible data providers in Microsoft.Data.SqlClient).