How do I print the parameters and query that was called in the stored procedure?

Question

I have a set aspx.cs codes to call a stored procedure to populate a grid view in my front end website. However during the run gridview is not populated but there are no errors. So I would like to know how to print the SQL query that was executed with the parameters as well. Thanks

The reason is being when I run with the existing data it populates however if new data are added it causes the gridview to be not populated at all.

using (SqlConnection conn = new SqlConnection(dbConn))
            {
                using (SqlCommand cmd = new SqlCommand(spretrieve, conn))
                {
cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@param1", SqlDbType.VarChar).Value = selectedDATE;
                        cmd.Parameters.Add("@param2", SqlDbType.VarChar).Value = selectedLVL2RISK;
                        cmd.Parameters.Add("@param3", SqlDbType.VarChar).Value = selectedORSA;
                        cmd.Parameters.Add("@param4", SqlDbType.VarChar).Value = selectedDPT;
                        string query = cmd.CommandText;

                        //Populate ORSA_ASSESSMENTS grid view
                        conn.Open();
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        GRID.DataSource = ds.Tables[0];
                        GRID.DataBind();
              }
}

So you want to do this only for testing purpose? Use `SQL Profiler` then. It will show you the exact query fired in SSMS. — Rahul Singh, Oct 07 '15 at 11:51
Possible duplicate of [Get the generated SQL-Statement from a SqlCommand-Object?](http://stackoverflow.com/questions/265192/get-the-generated-sql-statement-from-a-sqlcommand-object) — juharr, Oct 07 '15 at 11:58

Kaushik Maheta · Answer 1 · 2015-10-08T04:42:17.730

using (SqlConnection conn = new SqlConnection(dbConn))
        {
            using (SqlCommand cmd = new SqlCommand(spretrieve, conn))
            {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@param1", SqlDbType.VarChar).Value = selectedDATE;
                    cmd.Parameters.Add("@param2", SqlDbType.VarChar).Value = selectedLVL2RISK;
                    cmd.Parameters.Add("@param3", SqlDbType.VarChar).Value = selectedORSA;
                    cmd.Parameters.Add("@param4", SqlDbType.VarChar).Value = selectedDPT;
                    string query = cmd.CommandText;

                    foreach (SqlParameter p in cmd.Parameters)
                    {
                         query = query.Replace(p.ParameterName, p.Value.ToString());
                    }

                    //query variable store sql statement with parameter value.       
                    //Populate ORSA_ASSESSMENTS grid view
                    conn.Open();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    GRID.DataSource = ds.Tables[0];
                    GRID.DataBind();
          }
}

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. — ryanyuyu, Oct 07 '15 at 16:30

How do I print the parameters and query that was called in the stored procedure?

1 Answers1