Get the generated SQL statement from a SqlCommand object

Question

I have the following code   Using cmd As SqlCommand   Connection CreateCommand     cmd CommandText    UPDATE someTable SET Value    Value      cmd CommandText  amp     WHERE Id    Id      cmd Parameters AddWithValue   Id   1234      cmd Parameters AddWithValue   Value    myValue       cmd ExecuteNonQuery End Using   I wonder if there is any way to get the final SQL statment as a String  which should look like this   UPDATE someTable SET Value    myValue  WHERE Id   1234   If anyone wonders why I would do this    for logging  failed  statements for having the possibility to copy  amp  paste it to the Enterprise Manager for testing purposes

User · Answer

If you will convert the commandtext   Private Function ConvToNonParm ByRef Cmd As SqlClient SqlCommand  As String     For myCnt As Int16   1 To Cmd Parameters Count         Dim myVal As String   Cmd Parameters myCnt - 1  Value         Select Case Cmd Parameters myCnt - 1  SqlDbType             Case SqlDbType Char  SqlDbType NChar  SqlDbType VarChar  SqlDbType NChar  SqlDbType NVarChar  and so on                 myVal        amp  myVal  amp                       Case  others                   Case Else                  please assing         End Select         Cmd CommandText   Replace Cmd CommandText  Cmd Parameters myCnt - 1  ToString  myVal      Next     Cmd Parameters Clear       Return Cmd CommandText End Function   Now you can get the non parameter commandtext as follows       myCmd CommandText    UPDATE someTable SET Value    Value      myCmd CommandText  amp     WHERE Id    Id      myCmd Parameters AddWithValue   Id   1234      myCmd Parameters AddWithValue   Value    myValue        myCmd CommandText   ConvToNonParm myCmd    and the Result is  UPDATE someTable SET Value    myValue  WHERE Id   1234  without parameter anymore

User · Answer

My Solution   public static class DbHelper       public static string ToString this DbParameterCollection parameters  string sqlQuery                return parameters Cast lt DbParameter gt    Aggregate sqlQuery   current  p    gt  current Replace p ParameterName  p Value ToString

User · Answer

needed to cover non-Stored procedures too so I augmented CommandAsSql library  see comments under  Flapper s answer above  with this logic       private static void CommandAsSql Text this SqlCommand command  System Text StringBuilder sql                string query   command CommandText           foreach  SqlParameter p in command Parameters              query   Regex Replace query     B    p ParameterName      b   p ParameterValueForSQL       the first one is  B  the 2nd one is  b  since ParameterName starts with   which is a non-word character in RegEx  see https   stackoverflow com a 2544661           sql AppendLine query           the pull request is at  https   github com jphellemons CommandAsSql pull 3 commits 527d696dc6055c5bcf858b9700b83dc863f04896  the Regex idea was based on  stambikk s and EvZ s comments above and the  Update   section of https   stackoverflow com a 2544661 903783 that mentions  negative look-behind assertion   The use of  B instead of  b for word boundary detection at the start of the regular expression is because the p parameterName will always start with a     which is not a word character   note that ParameterValueForSQL   is an extension method defined at the CommandAsSql library to handle issues like single-quoting string parameter values etc

User · Answer

If it s only to check how a parameter is formatted in the result query  most DBMS s will allow querying literals from nothing  Thus   Using cmd As SqlCommand   Connection CreateCommand     cmd CommandText    SELECT  Value      cmd Parameters AddWithValue   Value    myValue       Return cmd ExecuteScalar End Using   That way you can see if quotes are doubled  etc

User · Answer

Used part of Flapper s code for my solution  which returns the entire SQL string including parameter values to run in MS SQL SMS   public string ParameterValueForSQL SqlParameter sp                string retval                switch  sp SqlDbType                        case SqlDbType Char              case SqlDbType NChar              case SqlDbType NText              case SqlDbType NVarChar              case SqlDbType Text              case SqlDbType Time              case SqlDbType VarChar              case SqlDbType Xml              case SqlDbType Date              case SqlDbType DateTime              case SqlDbType DateTime2              case SqlDbType DateTimeOffset                  if  sp Value    DBNull Value                                        retval    NULL                                     else                                       retval         sp Value ToString   Replace                                                     break               case SqlDbType Bit                  if  sp Value    DBNull Value                                        retval    NULL                                     else                                       retval     bool sp Value    false     0     1                                     break               default                  if  sp Value    DBNull Value                                        retval    NULL                                     else                                       retval   sp Value ToString   Replace                                               break                     return retval              public string CommandAsSql SqlCommand sc                string sql   sc CommandText           sql   sql Replace   r n       Replace   r       Replace   n                sql   System Text RegularExpressions Regex Replace sql     s                   foreach  SqlParameter sp in sc Parameters                        string spName   sp ParameterName              string spValue   ParameterValueForSQL sp               sql   sql Replace spName  spValue                      sql   sql Replace    NULL    IS NULL            sql   sql Replace     NULL    IS NOT NULL            return sql

User · Answer

If your database was Oracle and the sql text contains dynamic variables named like  1  2      then you can use  string query   cmd CommandText  int i   1  foreach  OracleParameter p in cmd Parameters          query   query Replace  quot   quot  i ToString     p Value  null   quot  quot  p Value ToString          i

User · Answer

the sql command queries will be executed with exec sp executesql  so here s another way to get the statement as a string  SqlCommand extension method    public static string ToSqlStatement this SqlCommand cmd        return    EXECUTE sp executesql N  cmd CommandText Replace              cmd Parameters ToSqlParameters         private static string ToSqlParameters this SqlParameterCollection col        if  col Count    0          return string Empty      var parameters   new List lt string gt         var parameterValues   new List lt string gt         foreach  SqlParameter param in col                parameters Add    param ParameterName  param ToSqlParameterType               parameterValues Add    param ParameterName     param ToSqlParameterValue                 return    N   string Join      parameters      string Join      parameterValues        private static object ToSqlParameterType this SqlParameter param        var paramDbType   param SqlDbType ToString   ToLower        if  param Precision    0  amp  amp  param Scale    0          return    paramDbType   param Precision   param Scale         if  param Precision    0          return    paramDbType   param Precision         switch  param SqlDbType                case SqlDbType VarChar          case SqlDbType NVarChar              string s   param SqlValue  ToString      string Empty              return paramDbType    s Length  gt  0       s Length      string Empty           default              return paramDbType           private static string ToSqlParameterValue this SqlParameter param        switch  param SqlDbType                case SqlDbType Char          case SqlDbType Date          case SqlDbType DateTime          case SqlDbType DateTime2          case SqlDbType DateTimeOffset          case SqlDbType NChar          case SqlDbType NText          case SqlDbType NVarChar          case SqlDbType Text          case SqlDbType Time          case SqlDbType VarChar          case SqlDbType Xml              return      param SqlValue ToString   Replace                         case SqlDbType Bit              return param SqlValue ToBooleanOrDefault      1     0           default              return param SqlValue ToString   Replace                      public static bool ToBooleanOrDefault this object o  bool defaultValue   false        if  o    null          return defaultValue      string value   o ToString   ToLower        switch  value                case  yes           case  true           case  ok           case  y               return true          case  no           case  false           case  n               return false          default              bool b              if  bool TryParse o ToString    out b                   return b              break            return defaultValue

User · Answer

Profiler is hands-down your best option     You might need to copy a set of statements from profiler due to the prepare   execute steps involved

User · Answer

If it s only to check how a parameter is formatted in the result query  most DBMS s will allow querying literals from nothing  Thus   Using cmd As SqlCommand   Connection CreateCommand     cmd CommandText    SELECT  Value      cmd Parameters AddWithValue   Value    myValue       Return cmd ExecuteScalar End Using   That way you can see if quotes are doubled  etc

User · Answer

From parameter command to non parameter command  You Can change this one  Using cmd As SqlCommand   Connection CreateCommand     cmd CommandText    UPDATE someTable SET Value    Value      cmd CommandText  amp     WHERE Id    Id      cmd Parameters AddWithValue   Id   1234      cmd Parameters AddWithValue   Value    myValue       cmd ExecuteNonQuery End Using   To  Private sub Update  byval myID as Int32  byval myVal as String      Using cmd As SqlCommand   Connection CreateCommand         cmd CommandText    UPDATE someTable SET Value       amp  myVaL  amp       amp                                WHERE Id      amp  myID           cmd ExecuteNonQuery     End Using End sub

User · Answer

For logging purposes  I m afraid there s no nicer way of doing this but to construct the string yourself   string query   cmd CommandText   foreach  SqlParameter p in cmd Parameters        query   query Replace p ParameterName  p Value ToString

User · Answer

needed to cover non-Stored procedures too so I augmented CommandAsSql library  see comments under  Flapper s answer above  with this logic       private static void CommandAsSql Text this SqlCommand command  System Text StringBuilder sql                string query   command CommandText           foreach  SqlParameter p in command Parameters              query   Regex Replace query     B    p ParameterName      b   p ParameterValueForSQL       the first one is  B  the 2nd one is  b  since ParameterName starts with   which is a non-word character in RegEx  see https   stackoverflow com a 2544661           sql AppendLine query           the pull request is at  https   github com jphellemons CommandAsSql pull 3 commits 527d696dc6055c5bcf858b9700b83dc863f04896  the Regex idea was based on  stambikk s and EvZ s comments above and the  Update   section of https   stackoverflow com a 2544661 903783 that mentions  negative look-behind assertion   The use of  B instead of  b for word boundary detection at the start of the regular expression is because the p parameterName will always start with a     which is not a word character   note that ParameterValueForSQL   is an extension method defined at the CommandAsSql library to handle issues like single-quoting string parameter values etc

User · Answer

One liner   string Join      from SqlParameter p in cmd Parameters select p ToString

User · Answer

This is what I use to output parameter lists for a stored procedure into the debug console   string query    from SqlParameter p in sqlCmd Parameters where p    null where p Value    null select string Format  Param   0     1       p ParameterName  p Value ToString     Aggregate sqlCmd CommandText   current  parameter    gt  current   parameter   Debug WriteLine query     This will generate a console outputt simlar to this   Customer prGetCustomerDetails   Offset   1   Param   Fetch   10   Param   CategoryLevel1ID   3   Param   VehicleLineID   9   Param   SalesCode1   bce      I place this code directly below any procedure I wish to debug and is similar to a sql profiler session but in C

User · Answer

From parameter command to non parameter command  You Can change this one  Using cmd As SqlCommand   Connection CreateCommand     cmd CommandText    UPDATE someTable SET Value    Value      cmd CommandText  amp     WHERE Id    Id      cmd Parameters AddWithValue   Id   1234      cmd Parameters AddWithValue   Value    myValue       cmd ExecuteNonQuery End Using   To  Private sub Update  byval myID as Int32  byval myVal as String      Using cmd As SqlCommand   Connection CreateCommand         cmd CommandText    UPDATE someTable SET Value       amp  myVaL  amp       amp                                WHERE Id      amp  myID           cmd ExecuteNonQuery     End Using End sub

User · Answer

Modified version of Kon s answer as it only partially works with similar named parameters   The down side of using String Replace function   Other than that  I give him full credit on the solution   private string GetActualQuery SqlCommand sqlcmd        string query   sqlcmd CommandText      string parameters           string   strArray   System Text RegularExpressions Regex Split query    VALUES            Reconstructs the second half of the SQL Command     parameters             int count   0      foreach  SqlParameter p in sqlcmd Parameters                if  count     sqlcmd Parameters Count - 1                         parameters    p Value ToString                      else                       parameters    p Value ToString                             count               parameters                Returns the string recombined      return strArray 0      VALUES     parameters

User · Answer

I also had this issue where some parameterized queries or sp s would give me a SqlException  mostly the string or binary data would be truncated   and the statements where hard to debug  As far as i know there currently is no sql-profiler support for SQL Azure   I see a lot of simular code in reactions here  I ended up putting my solution in a Sql-Library project for future use    The generator is available here  https   github com jeroenpot SqlHelper blob master Source Mirabeau MsSql Library SqlGenerator cs  It supports both CommandType Text and CommandType StoredProcedure  And if you install the nuget-package you can generate it with this statement   SqlDebugHelper CreateExecutableSqlStatement sql  parameters

User · Answer

This is what I use to output parameter lists for a stored procedure into the debug console   string query    from SqlParameter p in sqlCmd Parameters where p    null where p Value    null select string Format  Param   0     1       p ParameterName  p Value ToString     Aggregate sqlCmd CommandText   current  parameter    gt  current   parameter   Debug WriteLine query     This will generate a console outputt simlar to this   Customer prGetCustomerDetails   Offset   1   Param   Fetch   10   Param   CategoryLevel1ID   3   Param   VehicleLineID   9   Param   SalesCode1   bce      I place this code directly below any procedure I wish to debug and is similar to a sql profiler session but in C

User · Answer

If you re using SQL Server  you could use SQL Server Profiler  if you have it  to view the command string that is actually executed  That would be useful for copy paste testing purpuses but not for logging I m afraid

User · Answer

As  pkExec and  Alok mentioned  use Replace does not work in 100  of cases  This is the solution I ve used in our DAL that uses RegExp to  match whole word  only and format the datatypes correctly  Thus the SQL generated can be tested directly in MySQL Workbench  or SQLSMS  etc           Replace the MySQLHelper EscapeString   function according to the DBMS used    Dim query As String   cmd CommandText query   query Replace  SET    SET   amp  vbNewLine  query   query Replace  WHERE   vbNewLine  amp   WHERE   query   query Replace  GROUP BY   vbNewLine  amp   GROUP BY   query   query Replace  ORDER BY   vbNewLine  amp   ORDER BY   query   query Replace  INNER JOIN   vbNewLine  amp   INNER JOIN   query   query Replace  LEFT JOIN   vbNewLine  amp   LEFT JOIN   query   query Replace  RIGHT JOIN   vbNewLine  amp   RIGHT JOIN   If query Contains  UNION ALL   Then     query   query Replace  UNION ALL   vbNewLine  amp   UNION ALL   amp  vbNewLine  ElseIf query Contains  UNION DISTINCT   Then     query   query Replace  UNION DISTINCT   vbNewLine  amp   UNION DISTINCT   amp  vbNewLine  Else     query   query Replace  UNION   vbNewLine  amp   UNION   amp  vbNewLine  End If  For Each par In cmd Parameters     If par Value Is Nothing OrElse IsDBNull par Value  Then         query   RegularExpressions Regex Replace query  par ParameterName  amp    b    NULL       ElseIf TypeOf par Value Is Date Then         query   RegularExpressions Regex Replace query  par ParameterName  amp    b        amp  Format par Value   yyyy-MM-dd HH mm ss    amp           ElseIf TypeOf par Value Is TimeSpan Then         query   RegularExpressions Regex Replace query  par ParameterName  amp    b        amp  par Value ToString  amp           ElseIf TypeOf par Value Is Double Or TypeOf par Value Is Decimal Or TypeOf par Value Is Single Then         query   RegularExpressions Regex Replace query  par ParameterName  amp    b   Replace par Value ToString                 ElseIf TypeOf par Value Is Integer Or TypeOf par Value Is UInteger Or TypeOf par Value Is Long Or TypeOf par Value Is ULong Then         query   RegularExpressions Regex Replace query  par ParameterName  amp    b   par Value ToString      Else         query   RegularExpressions Regex Replace query  par ParameterName  amp    b        amp  MySqlHelper EscapeString CStr par Value    amp           End If Next   Example    SELECT   FROM order WHERE order status    order status AND order date    order date   Will be generated   SELECT   FROM order WHERE order status    C  AND order date    2015-01-01 00 00 00

User · Answer

Used part of Flapper s code for my solution  which returns the entire SQL string including parameter values to run in MS SQL SMS   public string ParameterValueForSQL SqlParameter sp                string retval                switch  sp SqlDbType                        case SqlDbType Char              case SqlDbType NChar              case SqlDbType NText              case SqlDbType NVarChar              case SqlDbType Text              case SqlDbType Time              case SqlDbType VarChar              case SqlDbType Xml              case SqlDbType Date              case SqlDbType DateTime              case SqlDbType DateTime2              case SqlDbType DateTimeOffset                  if  sp Value    DBNull Value                                        retval    NULL                                     else                                       retval         sp Value ToString   Replace                                                     break               case SqlDbType Bit                  if  sp Value    DBNull Value                                        retval    NULL                                     else                                       retval     bool sp Value    false     0     1                                     break               default                  if  sp Value    DBNull Value                                        retval    NULL                                     else                                       retval   sp Value ToString   Replace                                               break                     return retval              public string CommandAsSql SqlCommand sc                string sql   sc CommandText           sql   sql Replace   r n       Replace   r       Replace   n                sql   System Text RegularExpressions Regex Replace sql     s                   foreach  SqlParameter sp in sc Parameters                        string spName   sp ParameterName              string spValue   ParameterValueForSQL sp               sql   sql Replace spName  spValue                      sql   sql Replace    NULL    IS NULL            sql   sql Replace     NULL    IS NOT NULL            return sql

User · Answer

I needed a similar command to string transformer to allow for more verbose logging  so I wrote this one   It will produce the text needed to re-execute the command in a new session including output parameters and structured parameters   It is lightly tested  but caveat emptor   Example   SqlCommand cmd   new SqlCommand  GetEntity   con   cmd Parameters AddWithValue   foobar   1   cmd Parameters Add new SqlParameter        ParameterName     outParam       Direction   ParameterDirection Output      SqlDbType   System Data SqlDbType Int     cmd Parameters Add new SqlParameter        Direction   ParameterDirection ReturnValue     cmd CommandType   CommandType StoredProcedure    Will produce   -- BEGIN COMMAND DECLARE  foobar INT   1  DECLARE  outParam INT   NULL  DECLARE  returnValue INT  -- END PARAMS EXEC  returnValue   GetEntity  foobar    foobar   outParam    outParam OUTPUT -- RESULTS SELECT 1 as Executed   returnValue as ReturnValue   outParam as   outParam   -- END COMMAND   Implementation   public class SqlCommandDumper       public static string GetCommandText SqlCommand sqc                StringBuilder sbCommandText   new StringBuilder             sbCommandText AppendLine  -- BEGIN COMMAND                params         for  int i   0  i  lt  sqc Parameters Count  i                logParameterToSqlBatch sqc Parameters i   sbCommandText           sbCommandText AppendLine  -- END PARAMS                command         if  sqc CommandType    CommandType StoredProcedure                        sbCommandText Append  EXEC                  bool hasReturnValue   false              for  int i   0  i  lt  sqc Parameters Count  i                                  if  sqc Parameters i  Direction    ParameterDirection ReturnValue                      hasReturnValue   true                            if  hasReturnValue                                sbCommandText Append   returnValue                                  sbCommandText Append sqc CommandText                bool hasPrev   false              for  int i   0  i  lt  sqc Parameters Count  i                                  var cParam   sqc Parameters i                   if  cParam Direction    ParameterDirection ReturnValue                                        if  hasPrev                          sbCommandText Append                             sbCommandText Append cParam ParameterName                       sbCommandText Append                             sbCommandText Append cParam ParameterName                        if  cParam Direction HasFlag ParameterDirection Output                           sbCommandText Append   OUTPUT                         hasPrev   true                                                    else                       sbCommandText AppendLine sqc CommandText                      sbCommandText AppendLine  -- RESULTS            sbCommandText Append  SELECT 1 as Executed            for  int i   0  i  lt  sqc Parameters Count  i                          var cParam   sqc Parameters i                if  cParam Direction    ParameterDirection ReturnValue                                sbCommandText Append     returnValue as ReturnValue                              else if  cParam Direction HasFlag ParameterDirection Output                                 sbCommandText Append                        sbCommandText Append cParam ParameterName                   sbCommandText Append   as                      sbCommandText Append cParam ParameterName                   sbCommandText Append                                       sbCommandText AppendLine                sbCommandText AppendLine  -- END COMMAND            return sbCommandText ToString               private static void logParameterToSqlBatch SqlParameter param  StringBuilder sbCommandText                sbCommandText Append  DECLARE             if  param Direction    ParameterDirection ReturnValue                        sbCommandText AppendLine   returnValue INT                       else                       sbCommandText Append param ParameterName                sbCommandText Append                   if  param SqlDbType    SqlDbType Structured                                logParameterType param  sbCommandText                   sbCommandText Append                         logQuotedParameterValue param Value  sbCommandText                    sbCommandText AppendLine                                 else                               logStructuredParameter param  sbCommandText                                      private static void logStructuredParameter SqlParameter param  StringBuilder sbCommandText                sbCommandText AppendLine    List Type              var dataTable    DataTable param Value           for  int rowNo   0  rowNo  lt  dataTable Rows Count  rowNo                          sbCommandText Append  INSERT INTO                 sbCommandText Append param ParameterName               sbCommandText Append   VALUES                   bool hasPrev   false              for  int colNo   0  colNo  lt  dataTable Columns Count  colNo                                  if  hasPrev                                        sbCommandText Append                                          logQuotedParameterValue dataTable Rows rowNo  ItemArray colNo   sbCommandText                   hasPrev   true                            sbCommandText AppendLine                             const string DATETIME FORMAT ROUNDTRIP    o       private static void logQuotedParameterValue object value  StringBuilder sbCommandText                try                       if  value    null                                sbCommandText Append  NULL                              else                               value   unboxNullable value                    if  value is string                        value is char                        value is char                          value is System Xml Linq XElement                        value is System Xml Linq XDocument                                        sbCommandText Append  N                         sbCommandText Append value ToString   Replace                                  sbCommandText Append                                          else if  value is bool                                           True - gt  1  False - gt  0                     sbCommandText Append Convert ToInt32 value                                      else if  value is sbyte                        value is byte                        value is short                        value is ushort                        value is int                        value is uint                        value is long                        value is ulong                        value is float                        value is double                        value is decimal                                        sbCommandText Append value ToString                                       else if  value is DateTime                                           SQL Server only supports ISO8601 with 3 digit precision on datetime                         datetime2   gt   SQL Server 2008  parses the  net format  and will                         implicitly cast down to datetime                         Alternatively  use the format string  yyyy - MM - dd T HH   mm   ss   fffK                         to match SQL server parsing                     sbCommandText Append  CAST                          sbCommandText Append   DateTime value  ToString DATETIME FORMAT ROUNDTRIP                        sbCommandText Append    as datetime2                                       else if  value is DateTimeOffset                                        sbCommandText Append                            sbCommandText Append   DateTimeOffset value  ToString DATETIME FORMAT ROUNDTRIP                        sbCommandText Append                                          else if  value is Guid                                        sbCommandText Append                            sbCommandText Append   Guid value  ToString                         sbCommandText Append                                          else if  value is byte                                          var data    byte   value                      if  data Length    0                                                sbCommandText Append  NULL                                              else                                               sbCommandText Append  0x                            for  int i   0  i  lt  data Length  i                                                          sbCommandText Append data i  ToString  h2                                                                                       else                                       sbCommandText Append     UNKNOWN DATATYPE                          sbCommandText Append value GetType   ToString                         sbCommandText Append           N                         sbCommandText Append value ToString                         sbCommandText Append                                                           catch  Exception ex                        sbCommandText AppendLine     Exception occurred while converting parameter                  sbCommandText AppendLine ex ToString                 sbCommandText AppendLine                             private static object unboxNullable object value                var typeOriginal   value GetType            if  typeOriginal IsGenericType              amp  amp  typeOriginal GetGenericTypeDefinition      typeof Nullable lt  gt                             generic value  unboxing needed             return typeOriginal InvokeMember  GetValueOrDefault                   System Reflection BindingFlags Public                   System Reflection BindingFlags Instance                   System Reflection BindingFlags InvokeMethod                  null  value  null                     else                       return value                       private static void logParameterType SqlParameter param  StringBuilder sbCommandText                switch  param SqlDbType                           variable length             case SqlDbType Char              case SqlDbType NChar              case SqlDbType Binary                                        sbCommandText Append param SqlDbType ToString   ToUpper                         sbCommandText Append                           sbCommandText Append param Size                       sbCommandText Append                                         break              case SqlDbType VarChar              case SqlDbType NVarChar              case SqlDbType VarBinary                                        sbCommandText Append param SqlDbType ToString   ToUpper                         sbCommandText Append   MAX    Specified as                         sbCommandText Append param Size                       sbCommandText Append                                            break                 fixed length             case SqlDbType Text              case SqlDbType NText              case SqlDbType Bit              case SqlDbType TinyInt              case SqlDbType SmallInt              case SqlDbType Int              case SqlDbType BigInt              case SqlDbType SmallMoney              case SqlDbType Money              case SqlDbType Decimal              case SqlDbType Real              case SqlDbType Float              case SqlDbType Date              case SqlDbType DateTime              case SqlDbType DateTime2              case SqlDbType DateTimeOffset              case SqlDbType UniqueIdentifier              case SqlDbType Image                                        sbCommandText Append param SqlDbType ToString   ToUpper                                       break                 Unknown             case SqlDbType Timestamp              default                                        sbCommandText Append     UNKNOWN DATATYPE                          sbCommandText Append param SqlDbType ToString   ToUpper                         sbCommandText Append                                   sbCommandText Append param SqlDbType ToString   ToUpper                                       break

User · Answer

You can t  because it does not generate any SQL   The parameterized query  the one in CommandText  is sent to the SQL Server as the equivalent of a prepared statement  When you execute the command  the parameters and the query text are treated separately  At no point in time a complete SQL string is generated   You can use SQL Profiler to take a look behind the scenes

User · Answer

Whilst not perfect  here s something I knocked up for TSQL - could be easily tweaked for other flavors    If nothing else it will give you a start point for your own improvements     This does an OK job on data types and output parameters etc similar to using  execute stored procedure  in SSMS  We mostly used SPs so the  text  command doesn t account for parameters etc      public static String ParameterValueForSQL this SqlParameter sp                String retval                switch  sp SqlDbType                        case SqlDbType Char              case SqlDbType NChar              case SqlDbType NText              case SqlDbType NVarChar              case SqlDbType Text              case SqlDbType Time              case SqlDbType VarChar              case SqlDbType Xml              case SqlDbType Date              case SqlDbType DateTime              case SqlDbType DateTime2              case SqlDbType DateTimeOffset                  retval         sp Value ToString   Replace                                   break               case SqlDbType Bit                  retval    sp Value ToBooleanOrDefault false      1     0                   break               default                  retval   sp Value ToString   Replace                             break                     return retval             public static String CommandAsSql this SqlCommand sc                StringBuilder sql   new StringBuilder            Boolean FirstParam   true           sql AppendLine  use     sc Connection Database                 switch  sc CommandType                        case CommandType StoredProcedure                  sql AppendLine  declare  return value int                      foreach  SqlParameter sp in sc Parameters                                        if   sp Direction    ParameterDirection InputOutput      sp Direction    ParameterDirection Output                                                 sql Append  declare     sp ParameterName     t    sp SqlDbType ToString       t                               sql AppendLine   sp Direction    ParameterDirection Output     null    sp ParameterValueForSQL                                                                      sql AppendLine  exec      sc CommandText                          foreach  SqlParameter sp in sc Parameters                                        if  sp Direction    ParameterDirection ReturnValue                                                sql Append  FirstParam      t      t                               if  FirstParam  FirstParam   false                           if  sp Direction    ParameterDirection Input                              sql AppendLine sp ParameterName           sp ParameterValueForSQL                             else                              sql AppendLine sp ParameterName           sp ParameterName     output                                                            sql AppendLine                        sql AppendLine  select  Return Value    convert varchar   return value                       foreach  SqlParameter sp in sc Parameters                                        if   sp Direction    ParameterDirection InputOutput      sp Direction    ParameterDirection Output                                                 sql AppendLine  select      sp ParameterName        convert varchar      sp ParameterName                                                                  break              case CommandType Text                  sql AppendLine sc CommandText                   break                     return sql ToString            this generates output along these lines     use dbMyDatabase  declare  return value int  declare  OutTotalRows   BigInt    null  exec  spMyStoredProc       InEmployeeID   1000686        InPageSize   20        InPage   1        OutTotalRows    OutTotalRows output   select  Return Value    convert varchar   return value   select   OutTotalRows    convert varchar   OutTotalRows

User · Answer

Profiler is hands-down your best option     You might need to copy a set of statements from profiler due to the prepare   execute steps involved

User · Answer

You can t  because it does not generate any SQL   The parameterized query  the one in CommandText  is sent to the SQL Server as the equivalent of a prepared statement  When you execute the command  the parameters and the query text are treated separately  At no point in time a complete SQL string is generated   You can use SQL Profiler to take a look behind the scenes

User · Answer

I also had this issue where some parameterized queries or sp s would give me a SqlException  mostly the string or binary data would be truncated   and the statements where hard to debug  As far as i know there currently is no sql-profiler support for SQL Azure   I see a lot of simular code in reactions here  I ended up putting my solution in a Sql-Library project for future use    The generator is available here  https   github com jeroenpot SqlHelper blob master Source Mirabeau MsSql Library SqlGenerator cs  It supports both CommandType Text and CommandType StoredProcedure  And if you install the nuget-package you can generate it with this statement   SqlDebugHelper CreateExecutableSqlStatement sql  parameters

User · Answer

I had the same exact question and after reading these responses mistakenly decided it wasn t possible to get the exact resulting query   I was wrong     Solution  Open Activity Monitor in SQL Server Management Studio  narrow the processes section to the login username  database or application name that your application is using in the connection string   When the call is made to the db refresh Activity Monitor   When you see the process  right click on it and View Details   Note  this may not be a viable option for a busy db   But you should be able to narrow the result considerably using these steps

User · Answer

If you re using SQL Server  you could use SQL Server Profiler  if you have it  to view the command string that is actually executed  That would be useful for copy paste testing purpuses but not for logging I m afraid

User · Answer

For logging purposes  I m afraid there s no nicer way of doing this but to construct the string yourself   string query   cmd CommandText   foreach  SqlParameter p in cmd Parameters        query   query Replace p ParameterName  p Value ToString

User · Answer

Modified version of Kon s answer as it only partially works with similar named parameters   The down side of using String Replace function   Other than that  I give him full credit on the solution   private string GetActualQuery SqlCommand sqlcmd        string query   sqlcmd CommandText      string parameters           string   strArray   System Text RegularExpressions Regex Split query    VALUES            Reconstructs the second half of the SQL Command     parameters             int count   0      foreach  SqlParameter p in sqlcmd Parameters                if  count     sqlcmd Parameters Count - 1                         parameters    p Value ToString                      else                       parameters    p Value ToString                             count               parameters                Returns the string recombined      return strArray 0      VALUES     parameters

User · Answer

If you re using SQL Server  you could use SQL Server Profiler  if you have it  to view the command string that is actually executed  That would be useful for copy paste testing purpuses but not for logging I m afraid

User · Answer

If you re using SQL Server  you could use SQL Server Profiler  if you have it  to view the command string that is actually executed  That would be useful for copy paste testing purpuses but not for logging I m afraid

User · Answer

My Solution   public static class DbHelper       public static string ToString this DbParameterCollection parameters  string sqlQuery                return parameters Cast lt DbParameter gt    Aggregate sqlQuery   current  p    gt  current Replace p ParameterName  p Value ToString

User · Answer

If you will convert the commandtext   Private Function ConvToNonParm ByRef Cmd As SqlClient SqlCommand  As String     For myCnt As Int16   1 To Cmd Parameters Count         Dim myVal As String   Cmd Parameters myCnt - 1  Value         Select Case Cmd Parameters myCnt - 1  SqlDbType             Case SqlDbType Char  SqlDbType NChar  SqlDbType VarChar  SqlDbType NChar  SqlDbType NVarChar  and so on                 myVal        amp  myVal  amp                       Case  others                   Case Else                  please assing         End Select         Cmd CommandText   Replace Cmd CommandText  Cmd Parameters myCnt - 1  ToString  myVal      Next     Cmd Parameters Clear       Return Cmd CommandText End Function   Now you can get the non parameter commandtext as follows       myCmd CommandText    UPDATE someTable SET Value    Value      myCmd CommandText  amp     WHERE Id    Id      myCmd Parameters AddWithValue   Id   1234      myCmd Parameters AddWithValue   Value    myValue        myCmd CommandText   ConvToNonParm myCmd    and the Result is  UPDATE someTable SET Value    myValue  WHERE Id   1234  without parameter anymore

User · Answer

I wrote this method for me  I use some part of Bruno Ratnieks s code  Maybe it is useful to someone    public static string getQueryFromCommand SqlCommand cmd                StringBuilder CommandTxt   new StringBuilder            CommandTxt Append  DECLARE             List lt string gt  paramlst   new List lt string gt             foreach  SqlParameter parms in cmd Parameters                        paramlst Add parms ParameterName               CommandTxt Append parms ParameterName     AS                 CommandTxt Append parms SqlDbType ToString                 CommandTxt Append                          if  CommandTxt ToString   Substring CommandTxt Length-1  1                      CommandTxt Remove CommandTxt Length-1  1           CommandTxt AppendLine            int rownr   0          foreach  SqlParameter parms in cmd Parameters                        string val   String Empty              if  parms DbType Equals DbType String     parms DbType Equals DbType DateTime                   val         Convert ToString parms Value  Replace              Replace                                if  parms DbType Equals DbType Int16     parms DbType Equals DbType Int32     parms DbType Equals DbType Int64     parms DbType Equals DbType Decimal     parms DbType Equals DbType Double                   val   Convert ToString parms Value                CommandTxt AppendLine                CommandTxt Append  SET     paramlst rownr  ToString             val ToString                 rownr    1                    CommandTxt AppendLine            CommandTxt AppendLine            CommandTxt Append cmd CommandText           return CommandTxt ToString

User · Answer

As  pkExec and  Alok mentioned  use Replace does not work in 100  of cases  This is the solution I ve used in our DAL that uses RegExp to  match whole word  only and format the datatypes correctly  Thus the SQL generated can be tested directly in MySQL Workbench  or SQLSMS  etc           Replace the MySQLHelper EscapeString   function according to the DBMS used    Dim query As String   cmd CommandText query   query Replace  SET    SET   amp  vbNewLine  query   query Replace  WHERE   vbNewLine  amp   WHERE   query   query Replace  GROUP BY   vbNewLine  amp   GROUP BY   query   query Replace  ORDER BY   vbNewLine  amp   ORDER BY   query   query Replace  INNER JOIN   vbNewLine  amp   INNER JOIN   query   query Replace  LEFT JOIN   vbNewLine  amp   LEFT JOIN   query   query Replace  RIGHT JOIN   vbNewLine  amp   RIGHT JOIN   If query Contains  UNION ALL   Then     query   query Replace  UNION ALL   vbNewLine  amp   UNION ALL   amp  vbNewLine  ElseIf query Contains  UNION DISTINCT   Then     query   query Replace  UNION DISTINCT   vbNewLine  amp   UNION DISTINCT   amp  vbNewLine  Else     query   query Replace  UNION   vbNewLine  amp   UNION   amp  vbNewLine  End If  For Each par In cmd Parameters     If par Value Is Nothing OrElse IsDBNull par Value  Then         query   RegularExpressions Regex Replace query  par ParameterName  amp    b    NULL       ElseIf TypeOf par Value Is Date Then         query   RegularExpressions Regex Replace query  par ParameterName  amp    b        amp  Format par Value   yyyy-MM-dd HH mm ss    amp           ElseIf TypeOf par Value Is TimeSpan Then         query   RegularExpressions Regex Replace query  par ParameterName  amp    b        amp  par Value ToString  amp           ElseIf TypeOf par Value Is Double Or TypeOf par Value Is Decimal Or TypeOf par Value Is Single Then         query   RegularExpressions Regex Replace query  par ParameterName  amp    b   Replace par Value ToString                 ElseIf TypeOf par Value Is Integer Or TypeOf par Value Is UInteger Or TypeOf par Value Is Long Or TypeOf par Value Is ULong Then         query   RegularExpressions Regex Replace query  par ParameterName  amp    b   par Value ToString      Else         query   RegularExpressions Regex Replace query  par ParameterName  amp    b        amp  MySqlHelper EscapeString CStr par Value    amp           End If Next   Example    SELECT   FROM order WHERE order status    order status AND order date    order date   Will be generated   SELECT   FROM order WHERE order status    C  AND order date    2015-01-01 00 00 00

User · Answer

One liner   string Join      from SqlParameter p in cmd Parameters select p ToString

User · Answer

I needed a similar command to string transformer to allow for more verbose logging  so I wrote this one   It will produce the text needed to re-execute the command in a new session including output parameters and structured parameters   It is lightly tested  but caveat emptor   Example   SqlCommand cmd   new SqlCommand  GetEntity   con   cmd Parameters AddWithValue   foobar   1   cmd Parameters Add new SqlParameter        ParameterName     outParam       Direction   ParameterDirection Output      SqlDbType   System Data SqlDbType Int     cmd Parameters Add new SqlParameter        Direction   ParameterDirection ReturnValue     cmd CommandType   CommandType StoredProcedure    Will produce   -- BEGIN COMMAND DECLARE  foobar INT   1  DECLARE  outParam INT   NULL  DECLARE  returnValue INT  -- END PARAMS EXEC  returnValue   GetEntity  foobar    foobar   outParam    outParam OUTPUT -- RESULTS SELECT 1 as Executed   returnValue as ReturnValue   outParam as   outParam   -- END COMMAND   Implementation   public class SqlCommandDumper       public static string GetCommandText SqlCommand sqc                StringBuilder sbCommandText   new StringBuilder             sbCommandText AppendLine  -- BEGIN COMMAND                params         for  int i   0  i  lt  sqc Parameters Count  i                logParameterToSqlBatch sqc Parameters i   sbCommandText           sbCommandText AppendLine  -- END PARAMS                command         if  sqc CommandType    CommandType StoredProcedure                        sbCommandText Append  EXEC                  bool hasReturnValue   false              for  int i   0  i  lt  sqc Parameters Count  i                                  if  sqc Parameters i  Direction    ParameterDirection ReturnValue                      hasReturnValue   true                            if  hasReturnValue                                sbCommandText Append   returnValue                                  sbCommandText Append sqc CommandText                bool hasPrev   false              for  int i   0  i  lt  sqc Parameters Count  i                                  var cParam   sqc Parameters i                   if  cParam Direction    ParameterDirection ReturnValue                                        if  hasPrev                          sbCommandText Append                             sbCommandText Append cParam ParameterName                       sbCommandText Append                             sbCommandText Append cParam ParameterName                        if  cParam Direction HasFlag ParameterDirection Output                           sbCommandText Append   OUTPUT                         hasPrev   true                                                    else                       sbCommandText AppendLine sqc CommandText                      sbCommandText AppendLine  -- RESULTS            sbCommandText Append  SELECT 1 as Executed            for  int i   0  i  lt  sqc Parameters Count  i                          var cParam   sqc Parameters i                if  cParam Direction    ParameterDirection ReturnValue                                sbCommandText Append     returnValue as ReturnValue                              else if  cParam Direction HasFlag ParameterDirection Output                                 sbCommandText Append                        sbCommandText Append cParam ParameterName                   sbCommandText Append   as                      sbCommandText Append cParam ParameterName                   sbCommandText Append                                       sbCommandText AppendLine                sbCommandText AppendLine  -- END COMMAND            return sbCommandText ToString               private static void logParameterToSqlBatch SqlParameter param  StringBuilder sbCommandText                sbCommandText Append  DECLARE             if  param Direction    ParameterDirection ReturnValue                        sbCommandText AppendLine   returnValue INT                       else                       sbCommandText Append param ParameterName                sbCommandText Append                   if  param SqlDbType    SqlDbType Structured                                logParameterType param  sbCommandText                   sbCommandText Append                         logQuotedParameterValue param Value  sbCommandText                    sbCommandText AppendLine                                 else                               logStructuredParameter param  sbCommandText                                      private static void logStructuredParameter SqlParameter param  StringBuilder sbCommandText                sbCommandText AppendLine    List Type              var dataTable    DataTable param Value           for  int rowNo   0  rowNo  lt  dataTable Rows Count  rowNo                          sbCommandText Append  INSERT INTO                 sbCommandText Append param ParameterName               sbCommandText Append   VALUES                   bool hasPrev   false              for  int colNo   0  colNo  lt  dataTable Columns Count  colNo                                  if  hasPrev                                        sbCommandText Append                                          logQuotedParameterValue dataTable Rows rowNo  ItemArray colNo   sbCommandText                   hasPrev   true                            sbCommandText AppendLine                             const string DATETIME FORMAT ROUNDTRIP    o       private static void logQuotedParameterValue object value  StringBuilder sbCommandText                try                       if  value    null                                sbCommandText Append  NULL                              else                               value   unboxNullable value                    if  value is string                        value is char                        value is char                          value is System Xml Linq XElement                        value is System Xml Linq XDocument                                        sbCommandText Append  N                         sbCommandText Append value ToString   Replace                                  sbCommandText Append                                          else if  value is bool                                           True - gt  1  False - gt  0                     sbCommandText Append Convert ToInt32 value                                      else if  value is sbyte                        value is byte                        value is short                        value is ushort                        value is int                        value is uint                        value is long                        value is ulong                        value is float                        value is double                        value is decimal                                        sbCommandText Append value ToString                                       else if  value is DateTime                                           SQL Server only supports ISO8601 with 3 digit precision on datetime                         datetime2   gt   SQL Server 2008  parses the  net format  and will                         implicitly cast down to datetime                         Alternatively  use the format string  yyyy - MM - dd T HH   mm   ss   fffK                         to match SQL server parsing                     sbCommandText Append  CAST                          sbCommandText Append   DateTime value  ToString DATETIME FORMAT ROUNDTRIP                        sbCommandText Append    as datetime2                                       else if  value is DateTimeOffset                                        sbCommandText Append                            sbCommandText Append   DateTimeOffset value  ToString DATETIME FORMAT ROUNDTRIP                        sbCommandText Append                                          else if  value is Guid                                        sbCommandText Append                            sbCommandText Append   Guid value  ToString                         sbCommandText Append                                          else if  value is byte                                          var data    byte   value                      if  data Length    0                                                sbCommandText Append  NULL                                              else                                               sbCommandText Append  0x                            for  int i   0  i  lt  data Length  i                                                          sbCommandText Append data i  ToString  h2                                                                                       else                                       sbCommandText Append     UNKNOWN DATATYPE                          sbCommandText Append value GetType   ToString                         sbCommandText Append           N                         sbCommandText Append value ToString                         sbCommandText Append                                                           catch  Exception ex                        sbCommandText AppendLine     Exception occurred while converting parameter                  sbCommandText AppendLine ex ToString                 sbCommandText AppendLine                             private static object unboxNullable object value                var typeOriginal   value GetType            if  typeOriginal IsGenericType              amp  amp  typeOriginal GetGenericTypeDefinition      typeof Nullable lt  gt                             generic value  unboxing needed             return typeOriginal InvokeMember  GetValueOrDefault                   System Reflection BindingFlags Public                   System Reflection BindingFlags Instance                   System Reflection BindingFlags InvokeMethod                  null  value  null                     else                       return value                       private static void logParameterType SqlParameter param  StringBuilder sbCommandText                switch  param SqlDbType                           variable length             case SqlDbType Char              case SqlDbType NChar              case SqlDbType Binary                                        sbCommandText Append param SqlDbType ToString   ToUpper                         sbCommandText Append                           sbCommandText Append param Size                       sbCommandText Append                                         break              case SqlDbType VarChar              case SqlDbType NVarChar              case SqlDbType VarBinary                                        sbCommandText Append param SqlDbType ToString   ToUpper                         sbCommandText Append   MAX    Specified as                         sbCommandText Append param Size                       sbCommandText Append                                            break                 fixed length             case SqlDbType Text              case SqlDbType NText              case SqlDbType Bit              case SqlDbType TinyInt              case SqlDbType SmallInt              case SqlDbType Int              case SqlDbType BigInt              case SqlDbType SmallMoney              case SqlDbType Money              case SqlDbType Decimal              case SqlDbType Real              case SqlDbType Float              case SqlDbType Date              case SqlDbType DateTime              case SqlDbType DateTime2              case SqlDbType DateTimeOffset              case SqlDbType UniqueIdentifier              case SqlDbType Image                                        sbCommandText Append param SqlDbType ToString   ToUpper                                       break                 Unknown             case SqlDbType Timestamp              default                                        sbCommandText Append     UNKNOWN DATATYPE                          sbCommandText Append param SqlDbType ToString   ToUpper                         sbCommandText Append                                   sbCommandText Append param SqlDbType ToString   ToUpper                                       break

User · Answer

If your database was Oracle and the sql text contains dynamic variables named like  1  2      then you can use  string query   cmd CommandText  int i   1  foreach  OracleParameter p in cmd Parameters          query   query Replace  quot   quot  i ToString     p Value  null   quot  quot  p Value ToString          i

User · Answer

For logging purposes  I m afraid there s no nicer way of doing this but to construct the string yourself   string query   cmd CommandText   foreach  SqlParameter p in cmd Parameters        query   query Replace p ParameterName  p Value ToString

User · Answer

This solution works for me right now  Maybe it is usefull to someone  Please excuse all the redundancy        Public Shared Function SqlString ByVal cmd As SqlCommand  As String     Dim sbRetVal As New System Text StringBuilder       For Each item As SqlParameter In cmd Parameters         Select Case item DbType             Case DbType String                 sbRetVal AppendFormat  DECLARE  0  AS VARCHAR 255    item ParameterName                  sbRetVal AppendLine                   sbRetVal AppendFormat  SET  0      1     item ParameterName  item Value                  sbRetVal AppendLine                Case DbType DateTime                 sbRetVal AppendFormat  DECLARE  0  AS DATETIME   item ParameterName                  sbRetVal AppendLine                   sbRetVal AppendFormat  SET  0      1     item ParameterName  item Value                  sbRetVal AppendLine                Case DbType Guid                 sbRetVal AppendFormat  DECLARE  0  AS UNIQUEIDENTIFIER   item ParameterName                  sbRetVal AppendLine                   sbRetVal AppendFormat  SET  0      1     item ParameterName  item Value                  sbRetVal AppendLine                Case DbType Int32                 sbRetVal AppendFormat  DECLARE  0  AS int   item ParameterName                  sbRetVal AppendLine                   sbRetVal AppendFormat  SET  0     1    item ParameterName  item Value                  sbRetVal AppendLine                Case Else                 Stop          End Select     Next      sbRetVal AppendLine         sbRetVal AppendLine cmd CommandText       Return sbRetVal ToString   End Function

User · Answer

This solution works for me right now  Maybe it is usefull to someone  Please excuse all the redundancy        Public Shared Function SqlString ByVal cmd As SqlCommand  As String     Dim sbRetVal As New System Text StringBuilder       For Each item As SqlParameter In cmd Parameters         Select Case item DbType             Case DbType String                 sbRetVal AppendFormat  DECLARE  0  AS VARCHAR 255    item ParameterName                  sbRetVal AppendLine                   sbRetVal AppendFormat  SET  0      1     item ParameterName  item Value                  sbRetVal AppendLine                Case DbType DateTime                 sbRetVal AppendFormat  DECLARE  0  AS DATETIME   item ParameterName                  sbRetVal AppendLine                   sbRetVal AppendFormat  SET  0      1     item ParameterName  item Value                  sbRetVal AppendLine                Case DbType Guid                 sbRetVal AppendFormat  DECLARE  0  AS UNIQUEIDENTIFIER   item ParameterName                  sbRetVal AppendLine                   sbRetVal AppendFormat  SET  0      1     item ParameterName  item Value                  sbRetVal AppendLine                Case DbType Int32                 sbRetVal AppendFormat  DECLARE  0  AS int   item ParameterName                  sbRetVal AppendLine                   sbRetVal AppendFormat  SET  0     1    item ParameterName  item Value                  sbRetVal AppendLine                Case Else                 Stop          End Select     Next      sbRetVal AppendLine         sbRetVal AppendLine cmd CommandText       Return sbRetVal ToString   End Function

User · Answer

You can t  because it does not generate any SQL   The parameterized query  the one in CommandText  is sent to the SQL Server as the equivalent of a prepared statement  When you execute the command  the parameters and the query text are treated separately  At no point in time a complete SQL string is generated   You can use SQL Profiler to take a look behind the scenes

User · Answer

Late answer  I know but I too wanted this so I could log the SQL  The following is short and meets my needs   The following produces SQL you can copy paste in SSMS  it replaces the parameters with the values properly   You can add more types but this meets all I use in this case       private static void LogSQL SqlCommand cmd                        string query   cmd CommandText               foreach  SqlParameter prm in cmd Parameters                                switch  prm SqlDbType                                        case SqlDbType Bit                          int boolToInt    bool prm Value   1   0                          query   query Replace prm ParameterName  string Format   0     bool prm Value   1   0                            break                      case SqlDbType Int                          query   query Replace prm ParameterName  string Format   0    prm Value                            break                      case SqlDbType VarChar                          query   query Replace prm ParameterName  string Format    0     prm Value                            break                      default                          query   query Replace prm ParameterName  string Format    0     prm Value                            break                                                  the following is my how I write to my log - your use will vary             logger Debug   0    query                return              Now I can log the SQL just before I execute it   LogSQL queryCmd  queryCmd ExecuteNonQuery

User · Answer

I wrote this method for me  I use some part of Bruno Ratnieks s code  Maybe it is useful to someone    public static string getQueryFromCommand SqlCommand cmd                StringBuilder CommandTxt   new StringBuilder            CommandTxt Append  DECLARE             List lt string gt  paramlst   new List lt string gt             foreach  SqlParameter parms in cmd Parameters                        paramlst Add parms ParameterName               CommandTxt Append parms ParameterName     AS                 CommandTxt Append parms SqlDbType ToString                 CommandTxt Append                          if  CommandTxt ToString   Substring CommandTxt Length-1  1                      CommandTxt Remove CommandTxt Length-1  1           CommandTxt AppendLine            int rownr   0          foreach  SqlParameter parms in cmd Parameters                        string val   String Empty              if  parms DbType Equals DbType String     parms DbType Equals DbType DateTime                   val         Convert ToString parms Value  Replace              Replace                                if  parms DbType Equals DbType Int16     parms DbType Equals DbType Int32     parms DbType Equals DbType Int64     parms DbType Equals DbType Decimal     parms DbType Equals DbType Double                   val   Convert ToString parms Value                CommandTxt AppendLine                CommandTxt Append  SET     paramlst rownr  ToString             val ToString                 rownr    1                    CommandTxt AppendLine            CommandTxt AppendLine            CommandTxt Append cmd CommandText           return CommandTxt ToString

User · Answer

You can t  because it does not generate any SQL   The parameterized query  the one in CommandText  is sent to the SQL Server as the equivalent of a prepared statement  When you execute the command  the parameters and the query text are treated separately  At no point in time a complete SQL string is generated   You can use SQL Profiler to take a look behind the scenes

User · Answer

Whilst not perfect  here s something I knocked up for TSQL - could be easily tweaked for other flavors    If nothing else it will give you a start point for your own improvements     This does an OK job on data types and output parameters etc similar to using  execute stored procedure  in SSMS  We mostly used SPs so the  text  command doesn t account for parameters etc      public static String ParameterValueForSQL this SqlParameter sp                String retval                switch  sp SqlDbType                        case SqlDbType Char              case SqlDbType NChar              case SqlDbType NText              case SqlDbType NVarChar              case SqlDbType Text              case SqlDbType Time              case SqlDbType VarChar              case SqlDbType Xml              case SqlDbType Date              case SqlDbType DateTime              case SqlDbType DateTime2              case SqlDbType DateTimeOffset                  retval         sp Value ToString   Replace                                   break               case SqlDbType Bit                  retval    sp Value ToBooleanOrDefault false      1     0                   break               default                  retval   sp Value ToString   Replace                             break                     return retval             public static String CommandAsSql this SqlCommand sc                StringBuilder sql   new StringBuilder            Boolean FirstParam   true           sql AppendLine  use     sc Connection Database                 switch  sc CommandType                        case CommandType StoredProcedure                  sql AppendLine  declare  return value int                      foreach  SqlParameter sp in sc Parameters                                        if   sp Direction    ParameterDirection InputOutput      sp Direction    ParameterDirection Output                                                 sql Append  declare     sp ParameterName     t    sp SqlDbType ToString       t                               sql AppendLine   sp Direction    ParameterDirection Output     null    sp ParameterValueForSQL                                                                      sql AppendLine  exec      sc CommandText                          foreach  SqlParameter sp in sc Parameters                                        if  sp Direction    ParameterDirection ReturnValue                                                sql Append  FirstParam      t      t                               if  FirstParam  FirstParam   false                           if  sp Direction    ParameterDirection Input                              sql AppendLine sp ParameterName           sp ParameterValueForSQL                             else                              sql AppendLine sp ParameterName           sp ParameterName     output                                                            sql AppendLine                        sql AppendLine  select  Return Value    convert varchar   return value                       foreach  SqlParameter sp in sc Parameters                                        if   sp Direction    ParameterDirection InputOutput      sp Direction    ParameterDirection Output                                                 sql AppendLine  select      sp ParameterName        convert varchar      sp ParameterName                                                                  break              case CommandType Text                  sql AppendLine sc CommandText                   break                     return sql ToString            this generates output along these lines     use dbMyDatabase  declare  return value int  declare  OutTotalRows   BigInt    null  exec  spMyStoredProc       InEmployeeID   1000686        InPageSize   20        InPage   1        OutTotalRows    OutTotalRows output   select  Return Value    convert varchar   return value   select   OutTotalRows    convert varchar   OutTotalRows

User · Answer

Profiler is hands-down your best option     You might need to copy a set of statements from profiler due to the prepare   execute steps involved

User · Answer

Late answer  I know but I too wanted this so I could log the SQL  The following is short and meets my needs   The following produces SQL you can copy paste in SSMS  it replaces the parameters with the values properly   You can add more types but this meets all I use in this case       private static void LogSQL SqlCommand cmd                        string query   cmd CommandText               foreach  SqlParameter prm in cmd Parameters                                switch  prm SqlDbType                                        case SqlDbType Bit                          int boolToInt    bool prm Value   1   0                          query   query Replace prm ParameterName  string Format   0     bool prm Value   1   0                            break                      case SqlDbType Int                          query   query Replace prm ParameterName  string Format   0    prm Value                            break                      case SqlDbType VarChar                          query   query Replace prm ParameterName  string Format    0     prm Value                            break                      default                          query   query Replace prm ParameterName  string Format    0     prm Value                            break                                                  the following is my how I write to my log - your use will vary             logger Debug   0    query                return              Now I can log the SQL just before I execute it   LogSQL queryCmd  queryCmd ExecuteNonQuery

User · Answer

For logging purposes  I m afraid there s no nicer way of doing this but to construct the string yourself   string query   cmd CommandText   foreach  SqlParameter p in cmd Parameters        query   query Replace p ParameterName  p Value ToString

User · Answer

the sql command queries will be executed with exec sp executesql  so here s another way to get the statement as a string  SqlCommand extension method    public static string ToSqlStatement this SqlCommand cmd        return    EXECUTE sp executesql N  cmd CommandText Replace              cmd Parameters ToSqlParameters         private static string ToSqlParameters this SqlParameterCollection col        if  col Count    0          return string Empty      var parameters   new List lt string gt         var parameterValues   new List lt string gt         foreach  SqlParameter param in col                parameters Add    param ParameterName  param ToSqlParameterType               parameterValues Add    param ParameterName     param ToSqlParameterValue                 return    N   string Join      parameters      string Join      parameterValues        private static object ToSqlParameterType this SqlParameter param        var paramDbType   param SqlDbType ToString   ToLower        if  param Precision    0  amp  amp  param Scale    0          return    paramDbType   param Precision   param Scale         if  param Precision    0          return    paramDbType   param Precision         switch  param SqlDbType                case SqlDbType VarChar          case SqlDbType NVarChar              string s   param SqlValue  ToString      string Empty              return paramDbType    s Length  gt  0       s Length      string Empty           default              return paramDbType           private static string ToSqlParameterValue this SqlParameter param        switch  param SqlDbType                case SqlDbType Char          case SqlDbType Date          case SqlDbType DateTime          case SqlDbType DateTime2          case SqlDbType DateTimeOffset          case SqlDbType NChar          case SqlDbType NText          case SqlDbType NVarChar          case SqlDbType Text          case SqlDbType Time          case SqlDbType VarChar          case SqlDbType Xml              return      param SqlValue ToString   Replace                         case SqlDbType Bit              return param SqlValue ToBooleanOrDefault      1     0           default              return param SqlValue ToString   Replace                      public static bool ToBooleanOrDefault this object o  bool defaultValue   false        if  o    null          return defaultValue      string value   o ToString   ToLower        switch  value                case  yes           case  true           case  ok           case  y               return true          case  no           case  false           case  n               return false          default              bool b              if  bool TryParse o ToString    out b                   return b              break            return defaultValue

User · Answer

I had the same exact question and after reading these responses mistakenly decided it wasn t possible to get the exact resulting query   I was wrong     Solution  Open Activity Monitor in SQL Server Management Studio  narrow the processes section to the login username  database or application name that your application is using in the connection string   When the call is made to the db refresh Activity Monitor   When you see the process  right click on it and View Details   Note  this may not be a viable option for a busy db   But you should be able to narrow the result considerably using these steps

User · Answer

Extended Kon s code to help debug a stored procedure       private void ExtractSqlCommandForDebugging SqlCommand cmd                string sql    exec     cmd CommandText          bool first   true          foreach  SqlParameter p in cmd Parameters                        string value     p Value    DBNull Value     null                                 p Value is string          p Value                                     p Value ToString                 if  first                                sql    string Format    0   1    p ParameterName  value                   first   false                            else                               sql    string Format   n    0   1    p ParameterName  value                                   sql      nGO           Debug WriteLine sql           In my first test case  it generated   exec dbo MyStoredProcName  SnailMail False     Email True     AcceptSnailMail False     AcceptEmail False     DistanceMiles -1     DistanceLocationList        ExcludeDissatisfied True     ExcludeCodeRed True     MinAge null     MaxAge 18     GenderTypeID -1     NewThisYear -1     RegisteredThisYear -1     FormersTermGroupList        RegistrationStartDate null     RegistrationEndDate null     DivisionList  25      LocationList  29 30      OneOnOneOPL -1     JumpStart -1     SmallGroup -1     PurchasedEAP -1     RedeemedEAP -1     ReturnPlanYes False     MinNetPromoter -1     MinSurveyScore -1     VIPExclusionTypes  -2      FieldSelectionMask 65011584     DisplayType 0 GO   You will probably need to add some more conditional    is     type assignments  e g  for dates and times

User · Answer

Extended Kon s code to help debug a stored procedure       private void ExtractSqlCommandForDebugging SqlCommand cmd                string sql    exec     cmd CommandText          bool first   true          foreach  SqlParameter p in cmd Parameters                        string value     p Value    DBNull Value     null                                 p Value is string          p Value                                     p Value ToString                 if  first                                sql    string Format    0   1    p ParameterName  value                   first   false                            else                               sql    string Format   n    0   1    p ParameterName  value                                   sql      nGO           Debug WriteLine sql           In my first test case  it generated   exec dbo MyStoredProcName  SnailMail False     Email True     AcceptSnailMail False     AcceptEmail False     DistanceMiles -1     DistanceLocationList        ExcludeDissatisfied True     ExcludeCodeRed True     MinAge null     MaxAge 18     GenderTypeID -1     NewThisYear -1     RegisteredThisYear -1     FormersTermGroupList        RegistrationStartDate null     RegistrationEndDate null     DivisionList  25      LocationList  29 30      OneOnOneOPL -1     JumpStart -1     SmallGroup -1     PurchasedEAP -1     RedeemedEAP -1     ReturnPlanYes False     MinNetPromoter -1     MinSurveyScore -1     VIPExclusionTypes  -2      FieldSelectionMask 65011584     DisplayType 0 GO   You will probably need to add some more conditional    is     type assignments  e g  for dates and times

User · Answer

Profiler is hands-down your best option     You might need to copy a set of statements from profiler due to the prepare   execute steps involved

[c#] Get the generated SQL statement from a SqlCommand object?

Examples related to c#

Examples related to vb.net

Examples related to ado.net