Print parameterized SQL

[kockhwie]

Hi,

I want to debug a very complicated sql query, i am using parameterized style, to easier for me to debug, i want to print out the FULL query before the query has been executed which we can super easy done this in classic ASP by just using :

Response.Write (MySQLStatement)
Response.End

But how about in .NET, i want a simple & faster way like the classic ASP has. Anyway, is it possible to print it out with FULL(+Parameter values) in .NET? Sometimes i have to debug it thru FTP file. SO i cant use VisualStudio to do breakPoint.

Example:

string userName = "MyName";
string cmdString = "Select user_id from users where user_name='" + userName + "'";

I want to print out the result as: Select user_id from users where user_name='MyName';


Thanks.

[Ocean10000]

I actually made a function which does most of what you want. THIS IS DESIGNED FOR DEBUGING ONLY. And it is only to give a reasonable approximation of how it might be submitted to sql server.


/// <summary>
/// Used to generate the sql statement for any giving non null Sql Command object.
/// </summary>
/// <param name="cmd">Command Object which you wish to generate a sql statement from.</param>
/// <returns>Sql string which represents the store procedure and variables passed into it.</returns>
/// <remarks>
/// This is only used for debugging reasons and is not to be used to actually run the stored procedure from since it
/// doesn't generate the same exact sql code as when the cmd object gets executed directly on a connection. Its just meant to
/// display what values are actually being passed into the different parameters of the stored procedure.
/// </remarks>
public static string AssembleSql(System.Data.SqlClient.SqlCommand cmd)
{
System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.Append("exec " + cmd.CommandText + " ");
for(int a = 0 ;a !=cmd.Parameters.Count ;a++)
{
string iValue, iName;
iValue ="";
iName="";
if (cmd.Parameters[a] != null )
{
if (cmd.Parameters[a].ToString() != null)
{
iName = cmd.Parameters[a] .ToString();
}
//this is to catch when no actual Value was set for the parameter value
try
{
iValue = "";
if (cmd.Parameters[a].Value.ToString() !=null)
{
iValue = cmd.Parameters[a] .Value.ToString();
}
else
{
iValue = "";
}
}
catch{}
if (
cmd.Parameters[a] .SqlDbType ==System.Data.SqlDbType.VarChar ||
cmd.Parameters[a] .SqlDbType ==System.Data.SqlDbType.Char ||
cmd.Parameters[a] .SqlDbType ==System.Data.SqlDbType.NChar ||
cmd.Parameters[a] .SqlDbType ==System.Data.SqlDbType.NText ||
cmd.Parameters[a] .SqlDbType ==System.Data.SqlDbType.SmallDateTime ||
cmd.Parameters[a] .SqlDbType ==System.Data.SqlDbType.DateTime ||
cmd.Parameters[a] .SqlDbType ==System.Data.SqlDbType.Text
)
{
if (a > 0)
{
sql.Append(", " + iName + " = '" + CleanText(iValue) + "' ");
}
else
{
sql.Append(iName + " = '" + CleanText(iValue) + "' ");
}
}
else
{
if (iValue=="")
{
iValue="null";
}
if (a > 0)
{
sql.Append(", " + iName + " = " + iValue + " ");
}
else
{
sql.Append(iName + " = " + iValue + " ");
}
}
sql.Append("\r\n");
}
}
return sql.ToString();
}
/// <summary>
/// clean text replaces ' with sql quoting which make it easier to use the raw text in sql basically using it for escaping in sql procedures
/// </summary>
/// <param name="item">This is the sql string which is going to be made so single quotes do no mess them up.</param>
/// <returns>A formatted Sql String which should be protected from Sql String Insertion attacks</returns>
/// <remarks>This is only used by AssembleSql to format the data it receives into a semi-working fashion</remarks>
static private string CleanText(string item)
{
string quote = "'";
if( item.Length > 0)
{
item = item.Trim();
return item.Replace(quote, quote + quote);
}
else
{
return "";
}
}

[haggul]

For that kind of debugging I'll often put a literal control on the page somewhere where it won't get in the way (right at the end) and then pop the string into that at run time - when finished just remark out the code that puts the string in there and / or make the literal visible=false