sql server - From .NET can I get the full SQL string generated by a SqlCommand object (with SQL Parameters)?

Question

From the .NET environment can I get access to the full SQL string that is generated by a SqlCommand object?

Note: The full SQL string shows up in Intellisense hover, in VisualStudio, while in debug mode.

I'm willing to use reflection techniques if I must. I'm sure somebody here knows a way to get at it.

---

Update 1:
I'm calling a stored procedure having parameters with cmd.CommandType = CommandType.StoredProcedure and am trying to acquire the full SQL generated and run. I wonder if the cmd.Prepare() method might not prove useful in this circumstance, if it might store the full string in a state field or something like that.

---

Update 2:

In light of answers below (and referenced) that indicate no complete SQL string is generated internally during preparation or execution, I did a bit of poking around using .NET Reflector. Even the internal connection classes seem to pass objects rather than boiling them down to strings, for example:

internal abstract void AddPreparedCommand(SqlCommand cmd);
Declaring Type: System.Data.SqlClient.SqlInternalConnection
Assembly: System.Data, Version=2.0.0.0

---

In general, thanks to everybody for the level of detail you got into to prove what can be done and show what's actually happening. Much appreciated. I like thorough explanations; they add surety and lend credence to the answers.

Answer 1

A simple loop replacing all the parameter names with their values will provide you with something similar to what the end result is, but there are several problems.

1. Since the SQL is never actually rebuilt using the parameter values, things like newlines and quotes don't need to be considered
2. Parameter names in comments are never actually processed for their value, but left as-is

With those in place, and taking into account parameter names that starts with the same characters, like @NAME and @NAME_FULL, we can replace all the parameter names with the value that would be in the place of that parameter:

string query = cmd.CommandText;
foreach (SqlParameter p in cmd.Parameters.OrderByDescending(p => p.ParameterName.Length))
{
    query = query.Replace(p.ParameterName, p.Value.ToString());
}

there is one problem left with this, however, and that is if a parameter is a string, then the SQL that initially looks like this:

SELECT * FROM yourtable WHERE table_code = @CODE

will look like this:

SELECT * FROM yourtable WHERE table_code = SOME CODE WITH SPACES

This is clearly not legal SQL, so we need to account for some parameter-types as well:

DbType[] quotedParameterTypes = new DbType[] {
    DbType.AnsiString, DbType.Date,
    DbType.DateTime, DbType.Guid, DbType.String,
    DbType.AnsiStringFixedLength, DbType.StringFixedLength
};
string query = cmd.CommandText;

var arrParams = new SqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(arrParams, 0);

foreach (SqlParameter p in arrParams.OrderByDescending(p => p.ParameterName.Length))
{
    string value = p.Value.ToString();
    if (quotedParameterTypes.Contains(p.DbType))
        value = "'" + value + "'";
    query = query.Replace(p.ParameterName, value);
}