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