Howt o get the t-sql statements being used to Update a DataSet

Question

I've a c# DataSet object with one table in it, and put some data in it and i've made some changes to that dataset (by code).

Is there a way to get the actual t-sql queries this dataset will perform on the sql server when I update the dataset to the database with code that looks something like this:

var dataAdapter = new SqlDataAdapter(cmdText, connection);
var affected = dataAdapter.Update(updatedDataSet);

I want to know what queries this dataset will fire to the database so I can log these changes to a logfile in my c# program.

Answer 1

ask out the SqlCommand objects of the SqlDataAdapter

Answer 2

You can subscribe to the RowUpdating and RowUpdated events of the SqlDataAdapter. They will tell you the SqlCommand that's about to be executed or which has just executed.

You can affect the current and future updating rows by setting the Status property of the SqlRowUpdatingEventArgs supplied with the event. It is of type UpdateStatus, so you can tell it to skip the current or all future rows.

Answer 3

You could build a wrapper around the System.Data.SqlClient Provider (Ex. the provider registered in the config file as... providerName="System.Data.SqlClient"). Essentially an intercept proxy you would have access to all the information passing through the Provider and could siphon-off what you need, intercept, modify, aggregate and/or enrich it. This is a bit more advanced but opens the door to capture a whole range of information and could be inserted/replaced/removed as a separate layer of concern.