How to create refresh statements for TableAdapter objects in Visual Studio?

Question

I am working on developing an ADO.NET data provider and an associated DDEX provider. I am unable to convince the Visual Studio TableAdapater Configuration Wizard to generate SQL statements to refresh the data table after inserts and updates. It generates the insert and delete statements but will not produce the select statements to do the refresh.

The functionality referred to can be accessed by dropping a table from the Server Explorer (inside Visual Studio) onto a DataSet (e.g., DataSet1.xsd). It creates a TableAdapter object and configures SELECT, UPDATE, DELETE, and INSERT statements. If you right click on the TableAdapter object, the context menu has a “Configure” option that starts the “TableAdapter Configuration Wizard”. The first dialog of that wizard has an Advanced Options button, which leads to an option titled “Refresh the data table”. When used with SQL Server tables, that option causes a statement of the form “select field1, field2, …” to be added on to the end of the commands for the TableAdapter’s InsertCommand and UpdateCommand.

Do you have any idea what type property or interface might need to be exposed from the DDEX provider (or maybe the ADO.NET data provider) in order to make Visual Studio add those refresh statements to the update/insert commands?

The MSDN documentation for the Advanced SQL Generation Options Dialog Box has a note stating, “Refreshing the data table is only supported on databases that support batching of SQL statements.” This seems to imply that a .NET data provider might need to expose some property indicating such behavior is supported. But I cannot find it. Any ideas?

Answer 1

The refresh functionality in TableAdapter means that the associated datatable for the tableadapter operation will have updated data after the operation is completed.

If you call insert method of the tableadapter and pass the datarow or the datatable for the insert operation, the tableadapter will refresh the datatable/datarow to reflect the latest values from the database after the insert operation. If your table in database has generate a unique-id or auto-number for the insert command for the given row to insert, the original datarow's p.key coloumn will reflect the auto-generate id value from the database. This refreshing will be taken care by the tableadapter provided you have set the refresh option to true in the wizard configuration.

Now to this 'refresh' tableadapter will execute 2 queries in single batch, first will be the Insert, then the select with the scope_identity.

EDIT

My default Insert command would fire this single SQL if Refresh is turned OFF on the TableAdapter

INSERT INTO Table (coloumns) VALUES (values);

But My default Insert command would fire these SQL statement batch if Refresh is turned ON on the TableAdapter. Datarow that is passed as argument to Insert() will also be updated after successful execution.

INSERT INTO Table (coloumns) VALUES (values);

SELECT coloumns FROM Table WHERE (PKeyID = SCOPE_IDENTITY())