NDP - ADO.NET 2.0
DbCommandBuilder

To use a DbCommandBuilder:

The DbCommandBuilder will not properly handle auto-generated id fields or timestamp fields. It doesn't know how to handle output parameters.

It will generate only those commands that have null value in the data adapter.

If you change the SelectCommand on the DataAdapter, be sure to call RefreshSchema on the DbCommandBuilder.

There are three ways DbCommandBuilder handles (optimistic) concurrency. These are controlled by the ConflictOption property, taking values from the ConflictOption enumeration:

Member name Description
CompareAllSearchableValues Update and delete statements will include all searchable columns from the table in the WHERE clause. This is equivalent to specifying CompareAllValuesUpdate | CompareAllValuesDelete.
CompareRowVersion If any Timestamp columns exist in the table, they are used in the WHERE clause for all generated update statements. This is equivalent to specifying CompareRowVersionUpdate | CompareRowVersionDelete.
OverwriteChanges All update and delete statements include only PrimaryKey columns in the WHERE clause. If no PrimaryKey is defined, all searchable columns are included in the WHERE clause. This is equivalent to OverwriteChangesUpdate | OverwriteChangesDelete.


Here is a small program to show the commands generated under each option.

        static void Main(string[] args)
        {
            Initialize();
            DoOption(ConflictOption.OverwriteChanges);
            DoOption(ConflictOption.CompareAllSearchableValues);
            DoOption(ConflictOption.CompareRowVersion);           
        }


        static DbDataAdapter m_da;
        static DbCommandBuilder m_builder;

        private static void Initialize()
        {
            m_da = CreateDataAdapter();

            DbCommand cmd = CreateDbCommand();
            cmd.CommandText = "select Id, FirstName, LastName, Age, Version from Student";
            m_da.SelectCommand = cmd;
            m_da.SelectCommand.Connection = CreateDbConnection();


            m_builder = CreateCommandBuilder();
            m_builder.DataAdapter = m_da;

         }



        private static void DoOption(ConflictOption conflictOption)
        {
            m_builder.ConflictOption = conflictOption;
            m_builder.RefreshSchema();
 
            ShowQueries(conflictOption.ToString());
        }


         private static void ShowQueries(string message)
        {
            Console.WriteLine("====================");
            Console.WriteLine("Showing command builder under option {0}", message);
            ShowQuery("Insert", m_builder.GetInsertCommand());
            ShowQuery("Delete", m_builder.GetDeleteCommand());
            ShowQuery("Update", m_builder.GetUpdateCommand());
            Pause();
        }

       private static void ShowQuery(string type, DbCommand dbCommand)
        {
            Console.WriteLine("===================");
            Console.WriteLine("Query type: {0}", type);
            Console.WriteLine("{0}", dbCommand.CommandText);
            foreach (DbParameter param in dbCommand.Parameters)

                Console.WriteLine("  {0}\t{1}\t{2}\t{3}",
                    param.ParameterName,
                    param.SourceColumn,
                    param.SourceVersion,
                    param.Direction);
    
        }


        private static void Pause()
        {
            Console.WriteLine("Hit enter to continue.");
            Console.ReadLine();
        }

        private static DbProviderFactory GetProviderFactory()
        {
            return DbProviderFactories.GetFactory(ConfigurationManager.AppSettings["provider"]);
        }

        private static DbConnection CreateDbConnection()
        {
            string connStr = ConfigurationManager.ConnectionStrings["SqlConnStr"].ConnectionString;
            DbConnection conn = GetProviderFactory().CreateConnection();
            conn.ConnectionString = connStr;
            return conn;
        }

        private static DbDataAdapter CreateDataAdapter()
        {
            return GetProviderFactory().CreateDataAdapter();
        }

        private static DbCommand CreateDbCommand()
        {
            return GetProviderFactory().CreateCommand();
        }

        private static DbCommandBuilder CreateCommandBuilder()
        {
            return GetProviderFactory().CreateCommandBuilder();
        }

And here is the output. Note that our tables all have Version column that is a timestamp.

====================
Showing command builder under option OverwriteChanges
===================
Query type: Insert
INSERT INTO [Student] ([FirstName], [LastName], [Age]) VALUES (@p1, @p2, @p3)
  @p1   FirstName       Current Input
  @p2   LastName        Current Input
  @p3   Age     Current Input
===================
Query type: Delete
DELETE FROM [Student] WHERE (([Id] = @p1))
  @p1   Id      Original        Input
===================
Query type: Update
UPDATE [Student] SET [FirstName] = @p1, [LastName] = @p2, [Age] = @p3 WHERE (([I
d] = @p4))
  @p1   FirstName       Current Input
  @p2   LastName        Current Input
  @p3   Age     Current Input
  @p4   Id      Original        Input
Hit enter to continue.

====================
Showing command builder under option CompareAllSearchableValues
===================
Query type: Insert
INSERT INTO [Student] ([FirstName], [LastName], [Age]) VALUES (@p1, @p2, @p3)
  @p1   FirstName       Current Input
  @p2   LastName        Current Input
  @p3   Age     Current Input
===================
Query type: Delete
DELETE FROM [Student] WHERE (([Id] = @p1) AND ((@p2 = 1 AND [FirstName] IS NULL)
 OR ([FirstName] = @p3)) AND ((@p4 = 1 AND [LastName] IS NULL) OR ([LastName] =
@p5)) AND ((@p6 = 1 AND [Age] IS NULL) OR ([Age] = @p7)))
  @p1   Id      Original        Input
  @p2   FirstName       Original        Input
  @p3   FirstName       Original        Input
  @p4   LastName        Original        Input
  @p5   LastName        Original        Input
  @p6   Age     Original        Input
  @p7   Age     Original        Input
===================
Query type: Update
UPDATE [Student] SET [FirstName] = @p1, [LastName] = @p2, [Age] = @p3 WHERE (([I
d] = @p4) AND ((@p5 = 1 AND [FirstName] IS NULL) OR ([FirstName] = @p6)) AND ((@
p7 = 1 AND [LastName] IS NULL) OR ([LastName] = @p8)) AND ((@p9 = 1 AND [Age] IS
 NULL) OR ([Age] = @p10)))
  @p1   FirstName       Current Input
  @p2   LastName        Current Input
  @p3   Age     Current Input
  @p4   Id      Original        Input
  @p5   FirstName       Original        Input
  @p6   FirstName       Original        Input
  @p7   LastName        Original        Input
  @p8   LastName        Original        Input
  @p9   Age     Original        Input
  @p10  Age     Original        Input
Hit enter to continue.

====================
Showing command builder under option CompareRowVersion
===================
Query type: Insert
INSERT INTO [Student] ([FirstName], [LastName], [Age]) VALUES (@p1, @p2, @p3)
  @p1   FirstName       Current Input
  @p2   LastName        Current Input
  @p3   Age     Current Input
===================
Query type: Delete
DELETE FROM [Student] WHERE (([Id] = @p1) AND ((@p2 = 1 AND [Version] IS NULL) O
R ([Version] = @p3)))
  @p1   Id      Original        Input
  @p2   Version Original        Input
  @p3   Version Original        Input
===================
Query type: Update
UPDATE [Student] SET [FirstName] = @p1, [LastName] = @p2, [Age] = @p3 WHERE (([I
d] = @p4) AND ((@p5 = 1 AND [Version] IS NULL) OR ([Version] = @p6)))
  @p1   FirstName       Current Input
  @p2   LastName        Current Input
  @p3   Age     Current Input
  @p4   Id      Original        Input
  @p5   Version Original        Input
  @p6   Version Original        Input
Hit enter to continue.