How to see the SQL executing when updating a DataSet via DataAdapter.Update()
A fairly common question: How can I see the SQL that will be executed when updating a DataSet using a DataAdapter?
The first thing most people try is to get hold of is the Insert/Update commands for the DataAdapter.
This is typically done like so (using the trusted Northwind) with CommandBuilders:
using (SqlConnection con = new SqlConnection(cs))
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM Shippers";
SqlDataAdapter da = new SqlDataAdapter(cmd);
SqlCommandBuilder scb = new SqlCommandBuilder(da);
// Display insert and update commands
Console.WriteLine("Insert: \n{0}\n",scb.GetInsertCommand().CommandText);
Console.WriteLine("\nUpdate: \n{0}\n", scb.GetUpdateCommand().CommandText);
con.Close();
}
This will give the output:
Insert:
INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@p1, @p2)
Update:
UPDATE [Shippers] SET [CompanyName] = @p1, [Phone] = @p2 WHERE (([ShipperID] = @p3)
AND ([CompanyName] = @p4) AND ((@p5 = 1 AND [Phone] IS NULL) OR ([Phone] = @p6)))
This will however only display what the ‘template’ for the command contains.
We all know that when you call update/insert on a DataAdapter the .Net runtime will go through all rows in the dataset/datatable and check for modified rows.
If the row is modified, then the values of the columns are used in order to fill the SQL statements.
So then, how can we see what the parameters contain?
One way to do this is to use the DataAdapters RowUpdatingevent. This can be done like so, I hope this is self-explanatory:
class Program
{
static void Main(string[] args)
{
string cs = @"Data Source=<your server>;Integrated Security=SSPI;Initial Catalog=Northwind";
using (SqlConnection con = new SqlConnection(cs))
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT ShipperId, CompanyName, Phone from Shippers";
SqlDataAdapter da = new SqlDataAdapter(cmd);
SqlCommandBuilder sb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
// Update existing row
ds.Tables[0].Rows[0]["CompanyName"] = "My Company";
ds.Tables[0].Rows[0]["Phone"] = "(555) 123-654";
//// Insert a row
DataRow newRow = ds.Tables[0].NewRow();
newRow["ShipperId"] = 100;
newRow["CompanyName"] = "NewShipper";
newRow["Phone"] = "555-123-456";
ds.Tables[0].Rows.Add(newRow);
da.RowUpdating += new SqlRowUpdatingEventHandler(da_RowUpdating);
da.Update(ds);
con.Close();
}
}
static void da_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
{
if (e.Command != null)
{
Console.WriteLine("\nRow Updating...");
Console.WriteLine("Command type: -> {0}", e.StatementType);
Console.WriteLine("Command text: \n{0}", e.Command.CommandText);
Console.WriteLine("\nParameters:");
foreach (SqlParameter p in e.Command.Parameters)
{
Console.WriteLine("\t{0} - {1}", p.ParameterName, p.Value);
}
}
}
}
The output should most certainly be self-explanatory:
Row Updating...
Command type: -> Update
Command text:
UPDATE [Shippers] SET [CompanyName] = @p1, [Phone] = @p2 WHERE (([ShipperId] = @p3) AND ([CompanyName] = @p4) AND ((@p5 = 1
AND [Phone] IS NULL) OR ([Phone] = @p6)))
Parameters:
@p1 - My Company
@p2 - (555) 123-654
@p3 - 1
@p4 - Speedy Express
@p5 - 0
@p6 - (503) 555-9831
Row Updating...
Command type: -> Insert
Command text:
INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@p1, @p2)
Parameters:
@p1 - NewShipper
@p2 - 555-123-456