What SQL is being sent from a SqlCommand object

Question

I have a SqlCommand object on my c# based asp.net page. The SQL and the passed parameters are working the majority of the time. I have one case that is not working, I get the following error:

String or binary data would be truncated. The statement has been terminated.

I understand the error and but all the columns in the database should be long enough to hold everything being sent.

My questions,

Is there a way to see what the actual SQL being sent to the database is from SqlCommand object? I would like to be able to email the SQL when an error occurs.

Thanks, Justin

Answer 1

Whilst not perfect, here's something I knocked up something for TSQL - could be easily tweaked for other flavours... 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);

Answer 2

Check out this question it should provide what you are looking for.

Get the generated SQL statement from a SqlCommand object?

Answer 3

While you will not be able to plug is into something like Enterprise Manager to run it works for logging.

public static string ToReadableString(this IDbCommand command)
{
    StringBuilder builder = new StringBuilder();
    if (command.CommandType == CommandType.StoredProcedure)
        builder.AppendLine("Stored procedure: " + command.CommandText);
    else
        builder.AppendLine("Sql command: " + command.CommandText);
    if (command.Parameters.Count > 0)
        builder.AppendLine("With the following parameters.");
    foreach (IDataParameter param in command.Parameters)
    {
        builder.AppendFormat(
            "     Paramater {0}: {1}",
            param.ParameterName,
            (param.Value == null ? 
            "NULL" : param.Value.ToString())).AppendLine();
    }
    return builder.ToString();
}

Answer 4

You need to use the SQL Server Profiler to watch what comes from the application. I believe it can show you the SQL and the parameters, which you will need to see.