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.