Why SQL Server SHOWPLAN_XML via DbCommand(Parameterized) is not return results?

Question

I wish to collect Query plan via C# for improve development.

My approach is use DbCommand and SET SHOWPLAN_XML ON

But...

• Non-parameterized query will return query plan collectly.
• Parameterized query will return nothing!

Both SQL Server 2008 R2 and LocalDB(2012) have same problem.

How can I get plan of parameterized query?

Do you know why my parameterized query is not plannable?

---

［Sample: Steps to reproduce］

1. Create table.

CREATE TABLE Banana (
  BananaId             int IDENTITY(100,1),
  Title                nvarchar(512),
);
GO

ALTER TABLE Banana
    ADD CONSTRAINT Banana_PK PRIMARY KEY(BananaId);
GO

2. Execute queries

• Non-Parameterized query

  This wll be return plan:

  using (var conn = new SqlConnection( {Connection Strings} )) {
      conn.Open();
      DbCommand command = conn.CreateCommand();
  
      command.CommandText = "SET SHOWPLAN_XML ON;";
      command.CommandType = CommandType.Text;
      command.ExecuteNonQuery();
  
      command.CommandText = "SELECT BananaId, Title FROM Banana Where BananaId = 999";
      var plan = (string)command.ExecuteScalar();
  
      command.CommandText = "SET SHOWPLAN_XML OFF;";
      command.ExecuteNonQuery();
  
      Debug.WriteLine(plan); // <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" ...
  }
  

• Prameterized query

  This is not response plan:

  using (var conn = new SqlConnection( {Connection Strings} )) {
      conn.Open();
      DbCommand command = conn.CreateCommand();
  
      command.CommandText = "SET SHOWPLAN_XML ON;";
      command.CommandType = CommandType.Text;
      command.ExecuteNonQuery();
  
      command.CommandText = "SELECT BananaId, Title FROM Banana Where BananaId = @BananaId";
      var parameter = command.CreateParameter();
      parameter.ParameterName = "@BananaId";
      parameter.Value = 999;
      command.Parameters.Add(parameter);
      var plan = (string)command.ExecuteScalar();
  
      command.CommandText = "SET SHOWPLAN_XML OFF;";
      command.ExecuteNonQuery();
  
      Debug.WriteLine(plan); // (null)
  }
  

Answer 1

Instead of creating SqlParameters, create a bunch of T-SQL DECLARE statements and prefix your query. Then the plan will work as expected.

DECLARE @BananaId INT
SELECT BananaId, Title FROM Banana Where BananaId = @BananaId

Answer 2

Finally I found ugry and limited answer.
Why ugry? Because it must execute SQL!

So... I must exclude "NON SELECT" SQL to avoid duplicate data change.

I could get QueryPlan when following at the same time.

1. Use SET STATISTICS XML ON(OFF)
2. Use ExecuteReader()