What SQL is being sent from a SqlCommand object

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.



Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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);
        builder.AppendLine("Sql command: " + command.CommandText);
    if (command.Parameters.Count > 0)
        builder.AppendLine("With the following parameters.");
    foreach (IDataParameter param in command.Parameters)
            "     Paramater {0}: {1}",
            (param.Value == null ? 
            "NULL" : param.Value.ToString())).AppendLine();
    return builder.ToString();

Method 2

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.

Method 3

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("'", "''") + "'";

    case SqlDbType.Bit:
     retval = (sp.Value.ToBooleanOrDefault(false)) ? "1" : "0";

     retval = sp.Value.ToString().Replace("'", "''");

   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());

        sql.AppendLine(sp.ParameterName + " = " + sp.ParameterName + " output");

     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 + ");");
    case CommandType.Text:

   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);

Method 4

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

Get the generated SQL statement from a SqlCommand object?

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x