Get the generated SQL statement from a SqlCommand object?











up vote
156
down vote

favorite
35












I have the following code:



Using cmd As SqlCommand = Connection.CreateCommand
cmd.CommandText = "UPDATE someTable SET Value = @Value"
cmd.CommandText &= " 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 & paste it to the Enterprise Manager for testing purposes










share|improve this question




















  • 1




    Why you marked answer stackoverflow.com/a/265261/206730 if not distinguish between different datatypes, Sql Injection, parameters names similar (replace problem)... ?
    – Kiquenet
    Jun 28 '16 at 10:47










  • @Kiquenet I could have sworn, that I tried that but it did'nt let me. Now it works. Thank you for this.
    – dummy
    Jun 28 '16 at 12:37










  • If you want to generate accurately the SQL that would be run then take a look at TdsParser.TdsExecuteRPC (github.com/Microsoft/referencesource/blob/master/System.Data/…) and be a little afraid.
    – Rory
    Nov 13 '16 at 22:02















up vote
156
down vote

favorite
35












I have the following code:



Using cmd As SqlCommand = Connection.CreateCommand
cmd.CommandText = "UPDATE someTable SET Value = @Value"
cmd.CommandText &= " 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 & paste it to the Enterprise Manager for testing purposes










share|improve this question




















  • 1




    Why you marked answer stackoverflow.com/a/265261/206730 if not distinguish between different datatypes, Sql Injection, parameters names similar (replace problem)... ?
    – Kiquenet
    Jun 28 '16 at 10:47










  • @Kiquenet I could have sworn, that I tried that but it did'nt let me. Now it works. Thank you for this.
    – dummy
    Jun 28 '16 at 12:37










  • If you want to generate accurately the SQL that would be run then take a look at TdsParser.TdsExecuteRPC (github.com/Microsoft/referencesource/blob/master/System.Data/…) and be a little afraid.
    – Rory
    Nov 13 '16 at 22:02













up vote
156
down vote

favorite
35









up vote
156
down vote

favorite
35






35





I have the following code:



Using cmd As SqlCommand = Connection.CreateCommand
cmd.CommandText = "UPDATE someTable SET Value = @Value"
cmd.CommandText &= " 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 & paste it to the Enterprise Manager for testing purposes










share|improve this question















I have the following code:



Using cmd As SqlCommand = Connection.CreateCommand
cmd.CommandText = "UPDATE someTable SET Value = @Value"
cmd.CommandText &= " 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 & paste it to the Enterprise Manager for testing purposes







c# vb.net ado.net






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited May 24 '16 at 7:33









dakab

3,26972746




3,26972746










asked Nov 5 '08 at 14:01









dummy

2,62932034




2,62932034








  • 1




    Why you marked answer stackoverflow.com/a/265261/206730 if not distinguish between different datatypes, Sql Injection, parameters names similar (replace problem)... ?
    – Kiquenet
    Jun 28 '16 at 10:47










  • @Kiquenet I could have sworn, that I tried that but it did'nt let me. Now it works. Thank you for this.
    – dummy
    Jun 28 '16 at 12:37










  • If you want to generate accurately the SQL that would be run then take a look at TdsParser.TdsExecuteRPC (github.com/Microsoft/referencesource/blob/master/System.Data/…) and be a little afraid.
    – Rory
    Nov 13 '16 at 22:02














  • 1




    Why you marked answer stackoverflow.com/a/265261/206730 if not distinguish between different datatypes, Sql Injection, parameters names similar (replace problem)... ?
    – Kiquenet
    Jun 28 '16 at 10:47










  • @Kiquenet I could have sworn, that I tried that but it did'nt let me. Now it works. Thank you for this.
    – dummy
    Jun 28 '16 at 12:37










  • If you want to generate accurately the SQL that would be run then take a look at TdsParser.TdsExecuteRPC (github.com/Microsoft/referencesource/blob/master/System.Data/…) and be a little afraid.
    – Rory
    Nov 13 '16 at 22:02








1




1




Why you marked answer stackoverflow.com/a/265261/206730 if not distinguish between different datatypes, Sql Injection, parameters names similar (replace problem)... ?
– Kiquenet
Jun 28 '16 at 10:47




Why you marked answer stackoverflow.com/a/265261/206730 if not distinguish between different datatypes, Sql Injection, parameters names similar (replace problem)... ?
– Kiquenet
Jun 28 '16 at 10:47












@Kiquenet I could have sworn, that I tried that but it did'nt let me. Now it works. Thank you for this.
– dummy
Jun 28 '16 at 12:37




@Kiquenet I could have sworn, that I tried that but it did'nt let me. Now it works. Thank you for this.
– dummy
Jun 28 '16 at 12:37












If you want to generate accurately the SQL that would be run then take a look at TdsParser.TdsExecuteRPC (github.com/Microsoft/referencesource/blob/master/System.Data/…) and be a little afraid.
– Rory
Nov 13 '16 at 22:02




If you want to generate accurately the SQL that would be run then take a look at TdsParser.TdsExecuteRPC (github.com/Microsoft/referencesource/blob/master/System.Data/…) and be a little afraid.
– Rory
Nov 13 '16 at 22:02












19 Answers
19






active

oldest

votes

















up vote
87
down vote



accepted










Whilst not perfect, here's something I knocked up 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);





share|improve this answer



















  • 7




    Nice job actually trying to tackle the problem here, up-voted for effort alone.
    – Adam Tolley
    Apr 5 '11 at 15:36






  • 3




    What would be your "ToBooleanOrDefault(false)" method?
    – Benoittr
    Oct 23 '13 at 16:41






  • 4




    @Benoittr, you can see an implementation of ToBooleanOrDefault here: Question #3244850
    – Alexandre Marcondes
    Nov 4 '13 at 13:33












  • @flapper what of a blob field or byte array
    – Smith
    Jul 25 '16 at 17:09






  • 1




    Made some minor adjustments and added table value parameters. It's all up on GitHub and a .Net Standard 2.0 Nuget package github.com/jphellemons/CommandAsSql Thank you Flapper! Can I add you as collaborator?
    – JP Hellemons
    Aug 25 '17 at 14:27


















up vote
108
down vote













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





share|improve this answer























  • If I do that, I'll have to distinguish between different datatypes. Then I could skip the parameterized query all together and execute that.
    – dummy
    Nov 5 '08 at 14:37






  • 1




    dummy: not really. if you execute a prepared statement, you are at risk for sql injection attack. +1 for the answer.
    – Sunny Milenov
    Nov 5 '08 at 15:05






  • 11




    Theres a gotcha Here. If I have "Param", and "differentParam" as parameters, it renders the differentParam useless as it replaces it to "ValueParam". assuming Param=Value.
    – Alok
    Aug 30 '12 at 4:47








  • 4




    The question doesn't deal with defensive coding techniques, therefore null reference checks are not part of the answer. The fact that it should be implemented is implied, therefore I don't see this as a constructive comment.
    – Kon
    Mar 11 '13 at 14:49






  • 2




    a slightly better approach to eliminate the problem with similar param names pointed out by @Alok might be to use to use query = Regex.Replace(query, @"b" + p.ParameterName + @"b", p.Value.ToString()); for replacing the params in the string. This will replace the 'whole word'. It mightn't be an universal solution though as the b marks a position between a word character and a non-word character so in case your parameters names start with @, you should use p.ParameterName + @"b" to replace the param in the query string.
    – stambikk
    Aug 17 '15 at 13:01


















up vote
45
down vote













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.






share|improve this answer

















  • 2




    SQL is generated - look in Profiler - that's the text I'd like to have for logging purposes
    – kpkpkp
    Jul 20 '17 at 21:17










  • apart from SQL Profiler (which is being deprecated for newer SQL Server if I understood some MS comment correctly) can also use Activity Monitor according to other answer here
    – George Birbilis
    Sep 19 at 16:40


















up vote
20
down vote













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 < 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 < 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 < 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 < 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 < dataTable.Rows.Count; rowNo++)
{
sbCommandText.Append("INSERT INTO ");
sbCommandText.Append(param.ParameterName);
sbCommandText.Append(" VALUES (");

bool hasPrev = true;
for (int colNo = 0; colNo < 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 -> 1, False -> 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 (>= 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 < 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
&& typeOriginal.GetGenericTypeDefinition() == typeof(Nullable<>))
{
// 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;
}
}
}





share|improve this answer























  • Worked for what I needed it for, thanks
    – Fiona - myaccessible.website
    Aug 23 '13 at 19:27










  • Thanks for this, it's pretty comprehensive! :-)
    – Alastair Maw
    May 30 '14 at 11:21










  • Exactly what i was looking for, Thanks.
    – Xilmiki
    Aug 14 '15 at 11:09










  • I used this as a starting point for a version of it that used sp_executesql to handle the parameters in a single statement rather than declaring variables separately. This code really took care of all the tedious work and I just had to rearrange the pieces. Big thanks!
    – pettys
    May 19 '16 at 15:02






  • 1




    Doesn't this require an "N" prefix for the SQL string literals? Otherwise you might get many "?"s. Silently. Bad. (At least with SQL Server 2005 - haven't checked with less ancient versions.)
    – Paul Groke
    Aug 10 '16 at 20:05


















up vote
5
down vote













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.






share|improve this answer




























    up vote
    5
    down vote













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





    share|improve this answer






























      up vote
      2
      down vote













      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.






      share|improve this answer




























        up vote
        2
        down vote













        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("rn", "").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;
        }





        share|improve this answer























        • Your 'solution' doesn't work. You replaced r and n with "" when you should have used " ". Further, it doesn't work if you have more than 9 parameters since replacing '@p1' replaces both '@p1' and '@p10' with all kinds of crazy results. Copying the parameter list and reversing it was a quick fix for what I'm doing.
          – B H
          May 2 at 18:57










        • Also, your code will not work for an update command because of the 'is null' replacement.
          – B H
          May 2 at 19:03










        • indeed Flapper's code doesn't handle DBNull, there is an issue here for the CommandAsSQL library that is based on it: github.com/jphellemons/CommandAsSql/issues/1
          – George Birbilis
          Sep 19 at 17:01


















        up vote
        1
        down vote













        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.






        share|improve this answer




























          up vote
          1
          down vote













          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.






          share|improve this answer




























            up vote
            1
            down vote













            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) => 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#.






            share|improve this answer




























              up vote
              1
              down vote













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





              share|improve this answer






























                up vote
                1
                down vote













                My Solution:



                public static class DbHelper
                {
                public static string ToString(this DbParameterCollection parameters, string sqlQuery)
                {
                return parameters.Cast<DbParameter>().Aggregate(sqlQuery, (current, p) => current.Replace(p.ParameterName, p.Value.ToString()));
                }
                }





                share|improve this answer






























                  up vote
                  1
                  down vote













                  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<string> paramlst = new List<string>();
                  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();
                  }





                  share|improve this answer




























                    up vote
                    0
                    down vote













                    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





                    share|improve this answer




























                      up vote
                      0
                      down vote













                      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" & vbNewLine)
                      query = query.Replace("WHERE", vbNewLine & "WHERE")
                      query = query.Replace("GROUP BY", vbNewLine & "GROUP BY")
                      query = query.Replace("ORDER BY", vbNewLine & "ORDER BY")
                      query = query.Replace("INNER JOIN", vbNewLine & "INNER JOIN")
                      query = query.Replace("LEFT JOIN", vbNewLine & "LEFT JOIN")
                      query = query.Replace("RIGHT JOIN", vbNewLine & "RIGHT JOIN")
                      If query.Contains("UNION ALL") Then
                      query = query.Replace("UNION ALL", vbNewLine & "UNION ALL" & vbNewLine)
                      ElseIf query.Contains("UNION DISTINCT") Then
                      query = query.Replace("UNION DISTINCT", vbNewLine & "UNION DISTINCT" & vbNewLine)
                      Else
                      query = query.Replace("UNION", vbNewLine & "UNION" & 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 & "b", "NULL")
                      ElseIf TypeOf par.Value Is Date Then
                      query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & Format(par.Value, "yyyy-MM-dd HH:mm:ss") & "'")
                      ElseIf TypeOf par.Value Is TimeSpan Then
                      query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & par.Value.ToString & "'")
                      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 & "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 & "b", par.Value.ToString)
                      Else
                      query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & MySqlHelper.EscapeString(CStr(par.Value)) & "'")
                      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'





                      share|improve this answer






























                        up vote
                        0
                        down vote













                        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<string>();
                        var parameterValues = new List<string>();
                        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 && 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 > 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;
                        }





                        share|improve this answer






























                          up vote
                          0
                          down vote













                          One liner:



                          string.Join(",", from SqlParameter p in cmd.Parameters select p.ToString()) 





                          share|improve this answer





















                          • Missing the values...
                            – dipi evil
                            Oct 11 '17 at 17:41


















                          up vote
                          0
                          down vote













                          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.






                          share|improve this answer























                          • btw, other promising piece of code is at github.com/jeroenpot/SqlHelper/blob/master/Source/… (mentioned at an answer in this thread). Probably could merge code from SQLCommand and SqlGenerator if you find something not working at one or the other
                            – George Birbilis
                            Sep 19 at 15:59










                          • ...meant to say CommandAsSQL library instead of SQLCommand in the last comment
                            – George Birbilis
                            Sep 19 at 16:48











                          Your Answer






                          StackExchange.ifUsing("editor", function () {
                          StackExchange.using("externalEditor", function () {
                          StackExchange.using("snippets", function () {
                          StackExchange.snippets.init();
                          });
                          });
                          }, "code-snippets");

                          StackExchange.ready(function() {
                          var channelOptions = {
                          tags: "".split(" "),
                          id: "1"
                          };
                          initTagRenderer("".split(" "), "".split(" "), channelOptions);

                          StackExchange.using("externalEditor", function() {
                          // Have to fire editor after snippets, if snippets enabled
                          if (StackExchange.settings.snippets.snippetsEnabled) {
                          StackExchange.using("snippets", function() {
                          createEditor();
                          });
                          }
                          else {
                          createEditor();
                          }
                          });

                          function createEditor() {
                          StackExchange.prepareEditor({
                          heartbeatType: 'answer',
                          convertImagesToLinks: true,
                          noModals: true,
                          showLowRepImageUploadWarning: true,
                          reputationToPostImages: 10,
                          bindNavPrevention: true,
                          postfix: "",
                          imageUploader: {
                          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
                          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
                          allowUrls: true
                          },
                          onDemand: true,
                          discardSelector: ".discard-answer"
                          ,immediatelyShowMarkdownHelp:true
                          });


                          }
                          });














                           

                          draft saved


                          draft discarded


















                          StackExchange.ready(
                          function () {
                          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f265192%2fget-the-generated-sql-statement-from-a-sqlcommand-object%23new-answer', 'question_page');
                          }
                          );

                          Post as a guest















                          Required, but never shown

























                          19 Answers
                          19






                          active

                          oldest

                          votes








                          19 Answers
                          19






                          active

                          oldest

                          votes









                          active

                          oldest

                          votes






                          active

                          oldest

                          votes








                          up vote
                          87
                          down vote



                          accepted










                          Whilst not perfect, here's something I knocked up 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);





                          share|improve this answer



















                          • 7




                            Nice job actually trying to tackle the problem here, up-voted for effort alone.
                            – Adam Tolley
                            Apr 5 '11 at 15:36






                          • 3




                            What would be your "ToBooleanOrDefault(false)" method?
                            – Benoittr
                            Oct 23 '13 at 16:41






                          • 4




                            @Benoittr, you can see an implementation of ToBooleanOrDefault here: Question #3244850
                            – Alexandre Marcondes
                            Nov 4 '13 at 13:33












                          • @flapper what of a blob field or byte array
                            – Smith
                            Jul 25 '16 at 17:09






                          • 1




                            Made some minor adjustments and added table value parameters. It's all up on GitHub and a .Net Standard 2.0 Nuget package github.com/jphellemons/CommandAsSql Thank you Flapper! Can I add you as collaborator?
                            – JP Hellemons
                            Aug 25 '17 at 14:27















                          up vote
                          87
                          down vote



                          accepted










                          Whilst not perfect, here's something I knocked up 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);





                          share|improve this answer



















                          • 7




                            Nice job actually trying to tackle the problem here, up-voted for effort alone.
                            – Adam Tolley
                            Apr 5 '11 at 15:36






                          • 3




                            What would be your "ToBooleanOrDefault(false)" method?
                            – Benoittr
                            Oct 23 '13 at 16:41






                          • 4




                            @Benoittr, you can see an implementation of ToBooleanOrDefault here: Question #3244850
                            – Alexandre Marcondes
                            Nov 4 '13 at 13:33












                          • @flapper what of a blob field or byte array
                            – Smith
                            Jul 25 '16 at 17:09






                          • 1




                            Made some minor adjustments and added table value parameters. It's all up on GitHub and a .Net Standard 2.0 Nuget package github.com/jphellemons/CommandAsSql Thank you Flapper! Can I add you as collaborator?
                            – JP Hellemons
                            Aug 25 '17 at 14:27













                          up vote
                          87
                          down vote



                          accepted







                          up vote
                          87
                          down vote



                          accepted






                          Whilst not perfect, here's something I knocked up 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);





                          share|improve this answer














                          Whilst not perfect, here's something I knocked up 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);






                          share|improve this answer














                          share|improve this answer



                          share|improve this answer








                          edited Apr 20 at 15:05

























                          answered Nov 10 '10 at 16:22









                          Flapper

                          1,9911514




                          1,9911514








                          • 7




                            Nice job actually trying to tackle the problem here, up-voted for effort alone.
                            – Adam Tolley
                            Apr 5 '11 at 15:36






                          • 3




                            What would be your "ToBooleanOrDefault(false)" method?
                            – Benoittr
                            Oct 23 '13 at 16:41






                          • 4




                            @Benoittr, you can see an implementation of ToBooleanOrDefault here: Question #3244850
                            – Alexandre Marcondes
                            Nov 4 '13 at 13:33












                          • @flapper what of a blob field or byte array
                            – Smith
                            Jul 25 '16 at 17:09






                          • 1




                            Made some minor adjustments and added table value parameters. It's all up on GitHub and a .Net Standard 2.0 Nuget package github.com/jphellemons/CommandAsSql Thank you Flapper! Can I add you as collaborator?
                            – JP Hellemons
                            Aug 25 '17 at 14:27














                          • 7




                            Nice job actually trying to tackle the problem here, up-voted for effort alone.
                            – Adam Tolley
                            Apr 5 '11 at 15:36






                          • 3




                            What would be your "ToBooleanOrDefault(false)" method?
                            – Benoittr
                            Oct 23 '13 at 16:41






                          • 4




                            @Benoittr, you can see an implementation of ToBooleanOrDefault here: Question #3244850
                            – Alexandre Marcondes
                            Nov 4 '13 at 13:33












                          • @flapper what of a blob field or byte array
                            – Smith
                            Jul 25 '16 at 17:09






                          • 1




                            Made some minor adjustments and added table value parameters. It's all up on GitHub and a .Net Standard 2.0 Nuget package github.com/jphellemons/CommandAsSql Thank you Flapper! Can I add you as collaborator?
                            – JP Hellemons
                            Aug 25 '17 at 14:27








                          7




                          7




                          Nice job actually trying to tackle the problem here, up-voted for effort alone.
                          – Adam Tolley
                          Apr 5 '11 at 15:36




                          Nice job actually trying to tackle the problem here, up-voted for effort alone.
                          – Adam Tolley
                          Apr 5 '11 at 15:36




                          3




                          3




                          What would be your "ToBooleanOrDefault(false)" method?
                          – Benoittr
                          Oct 23 '13 at 16:41




                          What would be your "ToBooleanOrDefault(false)" method?
                          – Benoittr
                          Oct 23 '13 at 16:41




                          4




                          4




                          @Benoittr, you can see an implementation of ToBooleanOrDefault here: Question #3244850
                          – Alexandre Marcondes
                          Nov 4 '13 at 13:33






                          @Benoittr, you can see an implementation of ToBooleanOrDefault here: Question #3244850
                          – Alexandre Marcondes
                          Nov 4 '13 at 13:33














                          @flapper what of a blob field or byte array
                          – Smith
                          Jul 25 '16 at 17:09




                          @flapper what of a blob field or byte array
                          – Smith
                          Jul 25 '16 at 17:09




                          1




                          1




                          Made some minor adjustments and added table value parameters. It's all up on GitHub and a .Net Standard 2.0 Nuget package github.com/jphellemons/CommandAsSql Thank you Flapper! Can I add you as collaborator?
                          – JP Hellemons
                          Aug 25 '17 at 14:27




                          Made some minor adjustments and added table value parameters. It's all up on GitHub and a .Net Standard 2.0 Nuget package github.com/jphellemons/CommandAsSql Thank you Flapper! Can I add you as collaborator?
                          – JP Hellemons
                          Aug 25 '17 at 14:27












                          up vote
                          108
                          down vote













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





                          share|improve this answer























                          • If I do that, I'll have to distinguish between different datatypes. Then I could skip the parameterized query all together and execute that.
                            – dummy
                            Nov 5 '08 at 14:37






                          • 1




                            dummy: not really. if you execute a prepared statement, you are at risk for sql injection attack. +1 for the answer.
                            – Sunny Milenov
                            Nov 5 '08 at 15:05






                          • 11




                            Theres a gotcha Here. If I have "Param", and "differentParam" as parameters, it renders the differentParam useless as it replaces it to "ValueParam". assuming Param=Value.
                            – Alok
                            Aug 30 '12 at 4:47








                          • 4




                            The question doesn't deal with defensive coding techniques, therefore null reference checks are not part of the answer. The fact that it should be implemented is implied, therefore I don't see this as a constructive comment.
                            – Kon
                            Mar 11 '13 at 14:49






                          • 2




                            a slightly better approach to eliminate the problem with similar param names pointed out by @Alok might be to use to use query = Regex.Replace(query, @"b" + p.ParameterName + @"b", p.Value.ToString()); for replacing the params in the string. This will replace the 'whole word'. It mightn't be an universal solution though as the b marks a position between a word character and a non-word character so in case your parameters names start with @, you should use p.ParameterName + @"b" to replace the param in the query string.
                            – stambikk
                            Aug 17 '15 at 13:01















                          up vote
                          108
                          down vote













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





                          share|improve this answer























                          • If I do that, I'll have to distinguish between different datatypes. Then I could skip the parameterized query all together and execute that.
                            – dummy
                            Nov 5 '08 at 14:37






                          • 1




                            dummy: not really. if you execute a prepared statement, you are at risk for sql injection attack. +1 for the answer.
                            – Sunny Milenov
                            Nov 5 '08 at 15:05






                          • 11




                            Theres a gotcha Here. If I have "Param", and "differentParam" as parameters, it renders the differentParam useless as it replaces it to "ValueParam". assuming Param=Value.
                            – Alok
                            Aug 30 '12 at 4:47








                          • 4




                            The question doesn't deal with defensive coding techniques, therefore null reference checks are not part of the answer. The fact that it should be implemented is implied, therefore I don't see this as a constructive comment.
                            – Kon
                            Mar 11 '13 at 14:49






                          • 2




                            a slightly better approach to eliminate the problem with similar param names pointed out by @Alok might be to use to use query = Regex.Replace(query, @"b" + p.ParameterName + @"b", p.Value.ToString()); for replacing the params in the string. This will replace the 'whole word'. It mightn't be an universal solution though as the b marks a position between a word character and a non-word character so in case your parameters names start with @, you should use p.ParameterName + @"b" to replace the param in the query string.
                            – stambikk
                            Aug 17 '15 at 13:01













                          up vote
                          108
                          down vote










                          up vote
                          108
                          down vote









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





                          share|improve this 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());
                          }






                          share|improve this answer














                          share|improve this answer



                          share|improve this answer








                          edited Nov 7 at 20:25









                          user3071284

                          3,82562643




                          3,82562643










                          answered Nov 5 '08 at 14:24









                          Kon

                          22.2k95182




                          22.2k95182












                          • If I do that, I'll have to distinguish between different datatypes. Then I could skip the parameterized query all together and execute that.
                            – dummy
                            Nov 5 '08 at 14:37






                          • 1




                            dummy: not really. if you execute a prepared statement, you are at risk for sql injection attack. +1 for the answer.
                            – Sunny Milenov
                            Nov 5 '08 at 15:05






                          • 11




                            Theres a gotcha Here. If I have "Param", and "differentParam" as parameters, it renders the differentParam useless as it replaces it to "ValueParam". assuming Param=Value.
                            – Alok
                            Aug 30 '12 at 4:47








                          • 4




                            The question doesn't deal with defensive coding techniques, therefore null reference checks are not part of the answer. The fact that it should be implemented is implied, therefore I don't see this as a constructive comment.
                            – Kon
                            Mar 11 '13 at 14:49






                          • 2




                            a slightly better approach to eliminate the problem with similar param names pointed out by @Alok might be to use to use query = Regex.Replace(query, @"b" + p.ParameterName + @"b", p.Value.ToString()); for replacing the params in the string. This will replace the 'whole word'. It mightn't be an universal solution though as the b marks a position between a word character and a non-word character so in case your parameters names start with @, you should use p.ParameterName + @"b" to replace the param in the query string.
                            – stambikk
                            Aug 17 '15 at 13:01


















                          • If I do that, I'll have to distinguish between different datatypes. Then I could skip the parameterized query all together and execute that.
                            – dummy
                            Nov 5 '08 at 14:37






                          • 1




                            dummy: not really. if you execute a prepared statement, you are at risk for sql injection attack. +1 for the answer.
                            – Sunny Milenov
                            Nov 5 '08 at 15:05






                          • 11




                            Theres a gotcha Here. If I have "Param", and "differentParam" as parameters, it renders the differentParam useless as it replaces it to "ValueParam". assuming Param=Value.
                            – Alok
                            Aug 30 '12 at 4:47








                          • 4




                            The question doesn't deal with defensive coding techniques, therefore null reference checks are not part of the answer. The fact that it should be implemented is implied, therefore I don't see this as a constructive comment.
                            – Kon
                            Mar 11 '13 at 14:49






                          • 2




                            a slightly better approach to eliminate the problem with similar param names pointed out by @Alok might be to use to use query = Regex.Replace(query, @"b" + p.ParameterName + @"b", p.Value.ToString()); for replacing the params in the string. This will replace the 'whole word'. It mightn't be an universal solution though as the b marks a position between a word character and a non-word character so in case your parameters names start with @, you should use p.ParameterName + @"b" to replace the param in the query string.
                            – stambikk
                            Aug 17 '15 at 13:01
















                          If I do that, I'll have to distinguish between different datatypes. Then I could skip the parameterized query all together and execute that.
                          – dummy
                          Nov 5 '08 at 14:37




                          If I do that, I'll have to distinguish between different datatypes. Then I could skip the parameterized query all together and execute that.
                          – dummy
                          Nov 5 '08 at 14:37




                          1




                          1




                          dummy: not really. if you execute a prepared statement, you are at risk for sql injection attack. +1 for the answer.
                          – Sunny Milenov
                          Nov 5 '08 at 15:05




                          dummy: not really. if you execute a prepared statement, you are at risk for sql injection attack. +1 for the answer.
                          – Sunny Milenov
                          Nov 5 '08 at 15:05




                          11




                          11




                          Theres a gotcha Here. If I have "Param", and "differentParam" as parameters, it renders the differentParam useless as it replaces it to "ValueParam". assuming Param=Value.
                          – Alok
                          Aug 30 '12 at 4:47






                          Theres a gotcha Here. If I have "Param", and "differentParam" as parameters, it renders the differentParam useless as it replaces it to "ValueParam". assuming Param=Value.
                          – Alok
                          Aug 30 '12 at 4:47






                          4




                          4




                          The question doesn't deal with defensive coding techniques, therefore null reference checks are not part of the answer. The fact that it should be implemented is implied, therefore I don't see this as a constructive comment.
                          – Kon
                          Mar 11 '13 at 14:49




                          The question doesn't deal with defensive coding techniques, therefore null reference checks are not part of the answer. The fact that it should be implemented is implied, therefore I don't see this as a constructive comment.
                          – Kon
                          Mar 11 '13 at 14:49




                          2




                          2




                          a slightly better approach to eliminate the problem with similar param names pointed out by @Alok might be to use to use query = Regex.Replace(query, @"b" + p.ParameterName + @"b", p.Value.ToString()); for replacing the params in the string. This will replace the 'whole word'. It mightn't be an universal solution though as the b marks a position between a word character and a non-word character so in case your parameters names start with @, you should use p.ParameterName + @"b" to replace the param in the query string.
                          – stambikk
                          Aug 17 '15 at 13:01




                          a slightly better approach to eliminate the problem with similar param names pointed out by @Alok might be to use to use query = Regex.Replace(query, @"b" + p.ParameterName + @"b", p.Value.ToString()); for replacing the params in the string. This will replace the 'whole word'. It mightn't be an universal solution though as the b marks a position between a word character and a non-word character so in case your parameters names start with @, you should use p.ParameterName + @"b" to replace the param in the query string.
                          – stambikk
                          Aug 17 '15 at 13:01










                          up vote
                          45
                          down vote













                          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.






                          share|improve this answer

















                          • 2




                            SQL is generated - look in Profiler - that's the text I'd like to have for logging purposes
                            – kpkpkp
                            Jul 20 '17 at 21:17










                          • apart from SQL Profiler (which is being deprecated for newer SQL Server if I understood some MS comment correctly) can also use Activity Monitor according to other answer here
                            – George Birbilis
                            Sep 19 at 16:40















                          up vote
                          45
                          down vote













                          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.






                          share|improve this answer

















                          • 2




                            SQL is generated - look in Profiler - that's the text I'd like to have for logging purposes
                            – kpkpkp
                            Jul 20 '17 at 21:17










                          • apart from SQL Profiler (which is being deprecated for newer SQL Server if I understood some MS comment correctly) can also use Activity Monitor according to other answer here
                            – George Birbilis
                            Sep 19 at 16:40













                          up vote
                          45
                          down vote










                          up vote
                          45
                          down vote









                          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.






                          share|improve this 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.







                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Nov 5 '08 at 14:21









                          Tomalak

                          255k51422538




                          255k51422538








                          • 2




                            SQL is generated - look in Profiler - that's the text I'd like to have for logging purposes
                            – kpkpkp
                            Jul 20 '17 at 21:17










                          • apart from SQL Profiler (which is being deprecated for newer SQL Server if I understood some MS comment correctly) can also use Activity Monitor according to other answer here
                            – George Birbilis
                            Sep 19 at 16:40














                          • 2




                            SQL is generated - look in Profiler - that's the text I'd like to have for logging purposes
                            – kpkpkp
                            Jul 20 '17 at 21:17










                          • apart from SQL Profiler (which is being deprecated for newer SQL Server if I understood some MS comment correctly) can also use Activity Monitor according to other answer here
                            – George Birbilis
                            Sep 19 at 16:40








                          2




                          2




                          SQL is generated - look in Profiler - that's the text I'd like to have for logging purposes
                          – kpkpkp
                          Jul 20 '17 at 21:17




                          SQL is generated - look in Profiler - that's the text I'd like to have for logging purposes
                          – kpkpkp
                          Jul 20 '17 at 21:17












                          apart from SQL Profiler (which is being deprecated for newer SQL Server if I understood some MS comment correctly) can also use Activity Monitor according to other answer here
                          – George Birbilis
                          Sep 19 at 16:40




                          apart from SQL Profiler (which is being deprecated for newer SQL Server if I understood some MS comment correctly) can also use Activity Monitor according to other answer here
                          – George Birbilis
                          Sep 19 at 16:40










                          up vote
                          20
                          down vote













                          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 < 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 < 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 < 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 < 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 < dataTable.Rows.Count; rowNo++)
                          {
                          sbCommandText.Append("INSERT INTO ");
                          sbCommandText.Append(param.ParameterName);
                          sbCommandText.Append(" VALUES (");

                          bool hasPrev = true;
                          for (int colNo = 0; colNo < 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 -> 1, False -> 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 (>= 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 < 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
                          && typeOriginal.GetGenericTypeDefinition() == typeof(Nullable<>))
                          {
                          // 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;
                          }
                          }
                          }





                          share|improve this answer























                          • Worked for what I needed it for, thanks
                            – Fiona - myaccessible.website
                            Aug 23 '13 at 19:27










                          • Thanks for this, it's pretty comprehensive! :-)
                            – Alastair Maw
                            May 30 '14 at 11:21










                          • Exactly what i was looking for, Thanks.
                            – Xilmiki
                            Aug 14 '15 at 11:09










                          • I used this as a starting point for a version of it that used sp_executesql to handle the parameters in a single statement rather than declaring variables separately. This code really took care of all the tedious work and I just had to rearrange the pieces. Big thanks!
                            – pettys
                            May 19 '16 at 15:02






                          • 1




                            Doesn't this require an "N" prefix for the SQL string literals? Otherwise you might get many "?"s. Silently. Bad. (At least with SQL Server 2005 - haven't checked with less ancient versions.)
                            – Paul Groke
                            Aug 10 '16 at 20:05















                          up vote
                          20
                          down vote













                          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 < 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 < 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 < 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 < 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 < dataTable.Rows.Count; rowNo++)
                          {
                          sbCommandText.Append("INSERT INTO ");
                          sbCommandText.Append(param.ParameterName);
                          sbCommandText.Append(" VALUES (");

                          bool hasPrev = true;
                          for (int colNo = 0; colNo < 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 -> 1, False -> 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 (>= 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 < 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
                          && typeOriginal.GetGenericTypeDefinition() == typeof(Nullable<>))
                          {
                          // 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;
                          }
                          }
                          }





                          share|improve this answer























                          • Worked for what I needed it for, thanks
                            – Fiona - myaccessible.website
                            Aug 23 '13 at 19:27










                          • Thanks for this, it's pretty comprehensive! :-)
                            – Alastair Maw
                            May 30 '14 at 11:21










                          • Exactly what i was looking for, Thanks.
                            – Xilmiki
                            Aug 14 '15 at 11:09










                          • I used this as a starting point for a version of it that used sp_executesql to handle the parameters in a single statement rather than declaring variables separately. This code really took care of all the tedious work and I just had to rearrange the pieces. Big thanks!
                            – pettys
                            May 19 '16 at 15:02






                          • 1




                            Doesn't this require an "N" prefix for the SQL string literals? Otherwise you might get many "?"s. Silently. Bad. (At least with SQL Server 2005 - haven't checked with less ancient versions.)
                            – Paul Groke
                            Aug 10 '16 at 20:05













                          up vote
                          20
                          down vote










                          up vote
                          20
                          down vote









                          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 < 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 < 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 < 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 < 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 < dataTable.Rows.Count; rowNo++)
                          {
                          sbCommandText.Append("INSERT INTO ");
                          sbCommandText.Append(param.ParameterName);
                          sbCommandText.Append(" VALUES (");

                          bool hasPrev = true;
                          for (int colNo = 0; colNo < 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 -> 1, False -> 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 (>= 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 < 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
                          && typeOriginal.GetGenericTypeDefinition() == typeof(Nullable<>))
                          {
                          // 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;
                          }
                          }
                          }





                          share|improve this 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 < 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 < 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 < 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 < 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 < dataTable.Rows.Count; rowNo++)
                          {
                          sbCommandText.Append("INSERT INTO ");
                          sbCommandText.Append(param.ParameterName);
                          sbCommandText.Append(" VALUES (");

                          bool hasPrev = true;
                          for (int colNo = 0; colNo < 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 -> 1, False -> 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 (>= 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 < 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
                          && typeOriginal.GetGenericTypeDefinition() == typeof(Nullable<>))
                          {
                          // 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;
                          }
                          }
                          }






                          share|improve this answer














                          share|improve this answer



                          share|improve this answer








                          edited Aug 11 '16 at 0:26

























                          answered Jul 21 '13 at 21:34









                          Mitch

                          14.5k33662




                          14.5k33662












                          • Worked for what I needed it for, thanks
                            – Fiona - myaccessible.website
                            Aug 23 '13 at 19:27










                          • Thanks for this, it's pretty comprehensive! :-)
                            – Alastair Maw
                            May 30 '14 at 11:21










                          • Exactly what i was looking for, Thanks.
                            – Xilmiki
                            Aug 14 '15 at 11:09










                          • I used this as a starting point for a version of it that used sp_executesql to handle the parameters in a single statement rather than declaring variables separately. This code really took care of all the tedious work and I just had to rearrange the pieces. Big thanks!
                            – pettys
                            May 19 '16 at 15:02






                          • 1




                            Doesn't this require an "N" prefix for the SQL string literals? Otherwise you might get many "?"s. Silently. Bad. (At least with SQL Server 2005 - haven't checked with less ancient versions.)
                            – Paul Groke
                            Aug 10 '16 at 20:05


















                          • Worked for what I needed it for, thanks
                            – Fiona - myaccessible.website
                            Aug 23 '13 at 19:27










                          • Thanks for this, it's pretty comprehensive! :-)
                            – Alastair Maw
                            May 30 '14 at 11:21










                          • Exactly what i was looking for, Thanks.
                            – Xilmiki
                            Aug 14 '15 at 11:09










                          • I used this as a starting point for a version of it that used sp_executesql to handle the parameters in a single statement rather than declaring variables separately. This code really took care of all the tedious work and I just had to rearrange the pieces. Big thanks!
                            – pettys
                            May 19 '16 at 15:02






                          • 1




                            Doesn't this require an "N" prefix for the SQL string literals? Otherwise you might get many "?"s. Silently. Bad. (At least with SQL Server 2005 - haven't checked with less ancient versions.)
                            – Paul Groke
                            Aug 10 '16 at 20:05
















                          Worked for what I needed it for, thanks
                          – Fiona - myaccessible.website
                          Aug 23 '13 at 19:27




                          Worked for what I needed it for, thanks
                          – Fiona - myaccessible.website
                          Aug 23 '13 at 19:27












                          Thanks for this, it's pretty comprehensive! :-)
                          – Alastair Maw
                          May 30 '14 at 11:21




                          Thanks for this, it's pretty comprehensive! :-)
                          – Alastair Maw
                          May 30 '14 at 11:21












                          Exactly what i was looking for, Thanks.
                          – Xilmiki
                          Aug 14 '15 at 11:09




                          Exactly what i was looking for, Thanks.
                          – Xilmiki
                          Aug 14 '15 at 11:09












                          I used this as a starting point for a version of it that used sp_executesql to handle the parameters in a single statement rather than declaring variables separately. This code really took care of all the tedious work and I just had to rearrange the pieces. Big thanks!
                          – pettys
                          May 19 '16 at 15:02




                          I used this as a starting point for a version of it that used sp_executesql to handle the parameters in a single statement rather than declaring variables separately. This code really took care of all the tedious work and I just had to rearrange the pieces. Big thanks!
                          – pettys
                          May 19 '16 at 15:02




                          1




                          1




                          Doesn't this require an "N" prefix for the SQL string literals? Otherwise you might get many "?"s. Silently. Bad. (At least with SQL Server 2005 - haven't checked with less ancient versions.)
                          – Paul Groke
                          Aug 10 '16 at 20:05




                          Doesn't this require an "N" prefix for the SQL string literals? Otherwise you might get many "?"s. Silently. Bad. (At least with SQL Server 2005 - haven't checked with less ancient versions.)
                          – Paul Groke
                          Aug 10 '16 at 20:05










                          up vote
                          5
                          down vote













                          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.






                          share|improve this answer

























                            up vote
                            5
                            down vote













                            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.






                            share|improve this answer























                              up vote
                              5
                              down vote










                              up vote
                              5
                              down vote









                              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.






                              share|improve this 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.







                              share|improve this answer












                              share|improve this answer



                              share|improve this answer










                              answered Nov 5 '08 at 14:18









                              Rockcoder

                              5,51622742




                              5,51622742






















                                  up vote
                                  5
                                  down vote













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





                                  share|improve this answer



























                                    up vote
                                    5
                                    down vote













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





                                    share|improve this answer

























                                      up vote
                                      5
                                      down vote










                                      up vote
                                      5
                                      down vote









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





                                      share|improve this 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);






                                      share|improve this answer














                                      share|improve this answer



                                      share|improve this answer








                                      edited Sep 9 '15 at 9:33

























                                      answered Sep 2 '15 at 11:27









                                      Jeroen Pot

                                      174314




                                      174314






















                                          up vote
                                          2
                                          down vote













                                          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.






                                          share|improve this answer

























                                            up vote
                                            2
                                            down vote













                                            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.






                                            share|improve this answer























                                              up vote
                                              2
                                              down vote










                                              up vote
                                              2
                                              down vote









                                              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.






                                              share|improve this 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.







                                              share|improve this answer












                                              share|improve this answer



                                              share|improve this answer










                                              answered Nov 5 '08 at 14:23









                                              Ed Guiness

                                              28.9k1590135




                                              28.9k1590135






















                                                  up vote
                                                  2
                                                  down vote













                                                  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("rn", "").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;
                                                  }





                                                  share|improve this answer























                                                  • Your 'solution' doesn't work. You replaced r and n with "" when you should have used " ". Further, it doesn't work if you have more than 9 parameters since replacing '@p1' replaces both '@p1' and '@p10' with all kinds of crazy results. Copying the parameter list and reversing it was a quick fix for what I'm doing.
                                                    – B H
                                                    May 2 at 18:57










                                                  • Also, your code will not work for an update command because of the 'is null' replacement.
                                                    – B H
                                                    May 2 at 19:03










                                                  • indeed Flapper's code doesn't handle DBNull, there is an issue here for the CommandAsSQL library that is based on it: github.com/jphellemons/CommandAsSql/issues/1
                                                    – George Birbilis
                                                    Sep 19 at 17:01















                                                  up vote
                                                  2
                                                  down vote













                                                  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("rn", "").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;
                                                  }





                                                  share|improve this answer























                                                  • Your 'solution' doesn't work. You replaced r and n with "" when you should have used " ". Further, it doesn't work if you have more than 9 parameters since replacing '@p1' replaces both '@p1' and '@p10' with all kinds of crazy results. Copying the parameter list and reversing it was a quick fix for what I'm doing.
                                                    – B H
                                                    May 2 at 18:57










                                                  • Also, your code will not work for an update command because of the 'is null' replacement.
                                                    – B H
                                                    May 2 at 19:03










                                                  • indeed Flapper's code doesn't handle DBNull, there is an issue here for the CommandAsSQL library that is based on it: github.com/jphellemons/CommandAsSql/issues/1
                                                    – George Birbilis
                                                    Sep 19 at 17:01













                                                  up vote
                                                  2
                                                  down vote










                                                  up vote
                                                  2
                                                  down vote









                                                  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("rn", "").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;
                                                  }





                                                  share|improve this 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("rn", "").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;
                                                  }






                                                  share|improve this answer














                                                  share|improve this answer



                                                  share|improve this answer








                                                  edited May 23 '17 at 12:26









                                                  Community

                                                  11




                                                  11










                                                  answered Jun 3 '15 at 21:14









                                                  Barry-Dean

                                                  291




                                                  291












                                                  • Your 'solution' doesn't work. You replaced r and n with "" when you should have used " ". Further, it doesn't work if you have more than 9 parameters since replacing '@p1' replaces both '@p1' and '@p10' with all kinds of crazy results. Copying the parameter list and reversing it was a quick fix for what I'm doing.
                                                    – B H
                                                    May 2 at 18:57










                                                  • Also, your code will not work for an update command because of the 'is null' replacement.
                                                    – B H
                                                    May 2 at 19:03










                                                  • indeed Flapper's code doesn't handle DBNull, there is an issue here for the CommandAsSQL library that is based on it: github.com/jphellemons/CommandAsSql/issues/1
                                                    – George Birbilis
                                                    Sep 19 at 17:01


















                                                  • Your 'solution' doesn't work. You replaced r and n with "" when you should have used " ". Further, it doesn't work if you have more than 9 parameters since replacing '@p1' replaces both '@p1' and '@p10' with all kinds of crazy results. Copying the parameter list and reversing it was a quick fix for what I'm doing.
                                                    – B H
                                                    May 2 at 18:57










                                                  • Also, your code will not work for an update command because of the 'is null' replacement.
                                                    – B H
                                                    May 2 at 19:03










                                                  • indeed Flapper's code doesn't handle DBNull, there is an issue here for the CommandAsSQL library that is based on it: github.com/jphellemons/CommandAsSql/issues/1
                                                    – George Birbilis
                                                    Sep 19 at 17:01
















                                                  Your 'solution' doesn't work. You replaced r and n with "" when you should have used " ". Further, it doesn't work if you have more than 9 parameters since replacing '@p1' replaces both '@p1' and '@p10' with all kinds of crazy results. Copying the parameter list and reversing it was a quick fix for what I'm doing.
                                                  – B H
                                                  May 2 at 18:57




                                                  Your 'solution' doesn't work. You replaced r and n with "" when you should have used " ". Further, it doesn't work if you have more than 9 parameters since replacing '@p1' replaces both '@p1' and '@p10' with all kinds of crazy results. Copying the parameter list and reversing it was a quick fix for what I'm doing.
                                                  – B H
                                                  May 2 at 18:57












                                                  Also, your code will not work for an update command because of the 'is null' replacement.
                                                  – B H
                                                  May 2 at 19:03




                                                  Also, your code will not work for an update command because of the 'is null' replacement.
                                                  – B H
                                                  May 2 at 19:03












                                                  indeed Flapper's code doesn't handle DBNull, there is an issue here for the CommandAsSQL library that is based on it: github.com/jphellemons/CommandAsSql/issues/1
                                                  – George Birbilis
                                                  Sep 19 at 17:01




                                                  indeed Flapper's code doesn't handle DBNull, there is an issue here for the CommandAsSQL library that is based on it: github.com/jphellemons/CommandAsSql/issues/1
                                                  – George Birbilis
                                                  Sep 19 at 17:01










                                                  up vote
                                                  1
                                                  down vote













                                                  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.






                                                  share|improve this answer

























                                                    up vote
                                                    1
                                                    down vote













                                                    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.






                                                    share|improve this answer























                                                      up vote
                                                      1
                                                      down vote










                                                      up vote
                                                      1
                                                      down vote









                                                      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.






                                                      share|improve this 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.







                                                      share|improve this answer












                                                      share|improve this answer



                                                      share|improve this answer










                                                      answered Jan 16 '14 at 15:49









                                                      MPelletier

                                                      11.4k1171117




                                                      11.4k1171117






















                                                          up vote
                                                          1
                                                          down vote













                                                          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.






                                                          share|improve this answer

























                                                            up vote
                                                            1
                                                            down vote













                                                            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.






                                                            share|improve this answer























                                                              up vote
                                                              1
                                                              down vote










                                                              up vote
                                                              1
                                                              down vote









                                                              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.






                                                              share|improve this 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.







                                                              share|improve this answer












                                                              share|improve this answer



                                                              share|improve this answer










                                                              answered Feb 21 '14 at 15:47









                                                              alan

                                                              4,12052859




                                                              4,12052859






















                                                                  up vote
                                                                  1
                                                                  down vote













                                                                  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) => 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#.






                                                                  share|improve this answer

























                                                                    up vote
                                                                    1
                                                                    down vote













                                                                    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) => 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#.






                                                                    share|improve this answer























                                                                      up vote
                                                                      1
                                                                      down vote










                                                                      up vote
                                                                      1
                                                                      down vote









                                                                      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) => 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#.






                                                                      share|improve this 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) => 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#.







                                                                      share|improve this answer












                                                                      share|improve this answer



                                                                      share|improve this answer










                                                                      answered Sep 25 '14 at 16:45









                                                                      nocarrier

                                                                      1,19822853




                                                                      1,19822853






















                                                                          up vote
                                                                          1
                                                                          down vote













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





                                                                          share|improve this answer



























                                                                            up vote
                                                                            1
                                                                            down vote













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





                                                                            share|improve this answer

























                                                                              up vote
                                                                              1
                                                                              down vote










                                                                              up vote
                                                                              1
                                                                              down vote









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





                                                                              share|improve this 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;
                                                                              }






                                                                              share|improve this answer














                                                                              share|improve this answer



                                                                              share|improve this answer








                                                                              edited May 23 '17 at 12:34









                                                                              Community

                                                                              11




                                                                              11










                                                                              answered Mar 16 '15 at 20:52









                                                                              HouseCat

                                                                              556614




                                                                              556614






















                                                                                  up vote
                                                                                  1
                                                                                  down vote













                                                                                  My Solution:



                                                                                  public static class DbHelper
                                                                                  {
                                                                                  public static string ToString(this DbParameterCollection parameters, string sqlQuery)
                                                                                  {
                                                                                  return parameters.Cast<DbParameter>().Aggregate(sqlQuery, (current, p) => current.Replace(p.ParameterName, p.Value.ToString()));
                                                                                  }
                                                                                  }





                                                                                  share|improve this answer



























                                                                                    up vote
                                                                                    1
                                                                                    down vote













                                                                                    My Solution:



                                                                                    public static class DbHelper
                                                                                    {
                                                                                    public static string ToString(this DbParameterCollection parameters, string sqlQuery)
                                                                                    {
                                                                                    return parameters.Cast<DbParameter>().Aggregate(sqlQuery, (current, p) => current.Replace(p.ParameterName, p.Value.ToString()));
                                                                                    }
                                                                                    }





                                                                                    share|improve this answer

























                                                                                      up vote
                                                                                      1
                                                                                      down vote










                                                                                      up vote
                                                                                      1
                                                                                      down vote









                                                                                      My Solution:



                                                                                      public static class DbHelper
                                                                                      {
                                                                                      public static string ToString(this DbParameterCollection parameters, string sqlQuery)
                                                                                      {
                                                                                      return parameters.Cast<DbParameter>().Aggregate(sqlQuery, (current, p) => current.Replace(p.ParameterName, p.Value.ToString()));
                                                                                      }
                                                                                      }





                                                                                      share|improve this answer














                                                                                      My Solution:



                                                                                      public static class DbHelper
                                                                                      {
                                                                                      public static string ToString(this DbParameterCollection parameters, string sqlQuery)
                                                                                      {
                                                                                      return parameters.Cast<DbParameter>().Aggregate(sqlQuery, (current, p) => current.Replace(p.ParameterName, p.Value.ToString()));
                                                                                      }
                                                                                      }






                                                                                      share|improve this answer














                                                                                      share|improve this answer



                                                                                      share|improve this answer








                                                                                      edited Feb 8 '17 at 19:23









                                                                                      Arithmomaniac

                                                                                      2,73412449




                                                                                      2,73412449










                                                                                      answered Jun 15 '16 at 18:12









                                                                                      Martin.Martinsson

                                                                                      400710




                                                                                      400710






















                                                                                          up vote
                                                                                          1
                                                                                          down vote













                                                                                          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<string> paramlst = new List<string>();
                                                                                          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();
                                                                                          }





                                                                                          share|improve this answer

























                                                                                            up vote
                                                                                            1
                                                                                            down vote













                                                                                            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<string> paramlst = new List<string>();
                                                                                            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();
                                                                                            }





                                                                                            share|improve this answer























                                                                                              up vote
                                                                                              1
                                                                                              down vote










                                                                                              up vote
                                                                                              1
                                                                                              down vote









                                                                                              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<string> paramlst = new List<string>();
                                                                                              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();
                                                                                              }





                                                                                              share|improve this 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<string> paramlst = new List<string>();
                                                                                              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();
                                                                                              }






                                                                                              share|improve this answer












                                                                                              share|improve this answer



                                                                                              share|improve this answer










                                                                                              answered Jul 28 '17 at 16:20









                                                                                              Daghan

                                                                                              294




                                                                                              294






















                                                                                                  up vote
                                                                                                  0
                                                                                                  down vote













                                                                                                  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





                                                                                                  share|improve this answer

























                                                                                                    up vote
                                                                                                    0
                                                                                                    down vote













                                                                                                    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





                                                                                                    share|improve this answer























                                                                                                      up vote
                                                                                                      0
                                                                                                      down vote










                                                                                                      up vote
                                                                                                      0
                                                                                                      down vote









                                                                                                      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





                                                                                                      share|improve this 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






                                                                                                      share|improve this answer












                                                                                                      share|improve this answer



                                                                                                      share|improve this answer










                                                                                                      answered Apr 6 '11 at 8:45









                                                                                                      dummy

                                                                                                      2,62932034




                                                                                                      2,62932034






















                                                                                                          up vote
                                                                                                          0
                                                                                                          down vote













                                                                                                          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" & vbNewLine)
                                                                                                          query = query.Replace("WHERE", vbNewLine & "WHERE")
                                                                                                          query = query.Replace("GROUP BY", vbNewLine & "GROUP BY")
                                                                                                          query = query.Replace("ORDER BY", vbNewLine & "ORDER BY")
                                                                                                          query = query.Replace("INNER JOIN", vbNewLine & "INNER JOIN")
                                                                                                          query = query.Replace("LEFT JOIN", vbNewLine & "LEFT JOIN")
                                                                                                          query = query.Replace("RIGHT JOIN", vbNewLine & "RIGHT JOIN")
                                                                                                          If query.Contains("UNION ALL") Then
                                                                                                          query = query.Replace("UNION ALL", vbNewLine & "UNION ALL" & vbNewLine)
                                                                                                          ElseIf query.Contains("UNION DISTINCT") Then
                                                                                                          query = query.Replace("UNION DISTINCT", vbNewLine & "UNION DISTINCT" & vbNewLine)
                                                                                                          Else
                                                                                                          query = query.Replace("UNION", vbNewLine & "UNION" & 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 & "b", "NULL")
                                                                                                          ElseIf TypeOf par.Value Is Date Then
                                                                                                          query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & Format(par.Value, "yyyy-MM-dd HH:mm:ss") & "'")
                                                                                                          ElseIf TypeOf par.Value Is TimeSpan Then
                                                                                                          query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & par.Value.ToString & "'")
                                                                                                          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 & "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 & "b", par.Value.ToString)
                                                                                                          Else
                                                                                                          query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & MySqlHelper.EscapeString(CStr(par.Value)) & "'")
                                                                                                          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'





                                                                                                          share|improve this answer



























                                                                                                            up vote
                                                                                                            0
                                                                                                            down vote













                                                                                                            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" & vbNewLine)
                                                                                                            query = query.Replace("WHERE", vbNewLine & "WHERE")
                                                                                                            query = query.Replace("GROUP BY", vbNewLine & "GROUP BY")
                                                                                                            query = query.Replace("ORDER BY", vbNewLine & "ORDER BY")
                                                                                                            query = query.Replace("INNER JOIN", vbNewLine & "INNER JOIN")
                                                                                                            query = query.Replace("LEFT JOIN", vbNewLine & "LEFT JOIN")
                                                                                                            query = query.Replace("RIGHT JOIN", vbNewLine & "RIGHT JOIN")
                                                                                                            If query.Contains("UNION ALL") Then
                                                                                                            query = query.Replace("UNION ALL", vbNewLine & "UNION ALL" & vbNewLine)
                                                                                                            ElseIf query.Contains("UNION DISTINCT") Then
                                                                                                            query = query.Replace("UNION DISTINCT", vbNewLine & "UNION DISTINCT" & vbNewLine)
                                                                                                            Else
                                                                                                            query = query.Replace("UNION", vbNewLine & "UNION" & 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 & "b", "NULL")
                                                                                                            ElseIf TypeOf par.Value Is Date Then
                                                                                                            query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & Format(par.Value, "yyyy-MM-dd HH:mm:ss") & "'")
                                                                                                            ElseIf TypeOf par.Value Is TimeSpan Then
                                                                                                            query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & par.Value.ToString & "'")
                                                                                                            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 & "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 & "b", par.Value.ToString)
                                                                                                            Else
                                                                                                            query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & MySqlHelper.EscapeString(CStr(par.Value)) & "'")
                                                                                                            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'





                                                                                                            share|improve this answer

























                                                                                                              up vote
                                                                                                              0
                                                                                                              down vote










                                                                                                              up vote
                                                                                                              0
                                                                                                              down vote









                                                                                                              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" & vbNewLine)
                                                                                                              query = query.Replace("WHERE", vbNewLine & "WHERE")
                                                                                                              query = query.Replace("GROUP BY", vbNewLine & "GROUP BY")
                                                                                                              query = query.Replace("ORDER BY", vbNewLine & "ORDER BY")
                                                                                                              query = query.Replace("INNER JOIN", vbNewLine & "INNER JOIN")
                                                                                                              query = query.Replace("LEFT JOIN", vbNewLine & "LEFT JOIN")
                                                                                                              query = query.Replace("RIGHT JOIN", vbNewLine & "RIGHT JOIN")
                                                                                                              If query.Contains("UNION ALL") Then
                                                                                                              query = query.Replace("UNION ALL", vbNewLine & "UNION ALL" & vbNewLine)
                                                                                                              ElseIf query.Contains("UNION DISTINCT") Then
                                                                                                              query = query.Replace("UNION DISTINCT", vbNewLine & "UNION DISTINCT" & vbNewLine)
                                                                                                              Else
                                                                                                              query = query.Replace("UNION", vbNewLine & "UNION" & 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 & "b", "NULL")
                                                                                                              ElseIf TypeOf par.Value Is Date Then
                                                                                                              query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & Format(par.Value, "yyyy-MM-dd HH:mm:ss") & "'")
                                                                                                              ElseIf TypeOf par.Value Is TimeSpan Then
                                                                                                              query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & par.Value.ToString & "'")
                                                                                                              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 & "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 & "b", par.Value.ToString)
                                                                                                              Else
                                                                                                              query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & MySqlHelper.EscapeString(CStr(par.Value)) & "'")
                                                                                                              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'





                                                                                                              share|improve this 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" & vbNewLine)
                                                                                                              query = query.Replace("WHERE", vbNewLine & "WHERE")
                                                                                                              query = query.Replace("GROUP BY", vbNewLine & "GROUP BY")
                                                                                                              query = query.Replace("ORDER BY", vbNewLine & "ORDER BY")
                                                                                                              query = query.Replace("INNER JOIN", vbNewLine & "INNER JOIN")
                                                                                                              query = query.Replace("LEFT JOIN", vbNewLine & "LEFT JOIN")
                                                                                                              query = query.Replace("RIGHT JOIN", vbNewLine & "RIGHT JOIN")
                                                                                                              If query.Contains("UNION ALL") Then
                                                                                                              query = query.Replace("UNION ALL", vbNewLine & "UNION ALL" & vbNewLine)
                                                                                                              ElseIf query.Contains("UNION DISTINCT") Then
                                                                                                              query = query.Replace("UNION DISTINCT", vbNewLine & "UNION DISTINCT" & vbNewLine)
                                                                                                              Else
                                                                                                              query = query.Replace("UNION", vbNewLine & "UNION" & 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 & "b", "NULL")
                                                                                                              ElseIf TypeOf par.Value Is Date Then
                                                                                                              query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & Format(par.Value, "yyyy-MM-dd HH:mm:ss") & "'")
                                                                                                              ElseIf TypeOf par.Value Is TimeSpan Then
                                                                                                              query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & par.Value.ToString & "'")
                                                                                                              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 & "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 & "b", par.Value.ToString)
                                                                                                              Else
                                                                                                              query = RegularExpressions.Regex.Replace(query, par.ParameterName & "b", "'" & MySqlHelper.EscapeString(CStr(par.Value)) & "'")
                                                                                                              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'






                                                                                                              share|improve this answer














                                                                                                              share|improve this answer



                                                                                                              share|improve this answer








                                                                                                              edited Jul 15 '15 at 13:13

























                                                                                                              answered Jul 15 '15 at 13:02









                                                                                                              JotaSantana

                                                                                                              2316




                                                                                                              2316






















                                                                                                                  up vote
                                                                                                                  0
                                                                                                                  down vote













                                                                                                                  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<string>();
                                                                                                                  var parameterValues = new List<string>();
                                                                                                                  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 && 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 > 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;
                                                                                                                  }





                                                                                                                  share|improve this answer



























                                                                                                                    up vote
                                                                                                                    0
                                                                                                                    down vote













                                                                                                                    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<string>();
                                                                                                                    var parameterValues = new List<string>();
                                                                                                                    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 && 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 > 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;
                                                                                                                    }





                                                                                                                    share|improve this answer

























                                                                                                                      up vote
                                                                                                                      0
                                                                                                                      down vote










                                                                                                                      up vote
                                                                                                                      0
                                                                                                                      down vote









                                                                                                                      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<string>();
                                                                                                                      var parameterValues = new List<string>();
                                                                                                                      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 && 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 > 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;
                                                                                                                      }





                                                                                                                      share|improve this 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<string>();
                                                                                                                      var parameterValues = new List<string>();
                                                                                                                      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 && 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 > 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;
                                                                                                                      }






                                                                                                                      share|improve this answer














                                                                                                                      share|improve this answer



                                                                                                                      share|improve this answer








                                                                                                                      edited Dec 6 '16 at 11:05

























                                                                                                                      answered Dec 6 '16 at 10:43









                                                                                                                      o_link

                                                                                                                      11




                                                                                                                      11






















                                                                                                                          up vote
                                                                                                                          0
                                                                                                                          down vote













                                                                                                                          One liner:



                                                                                                                          string.Join(",", from SqlParameter p in cmd.Parameters select p.ToString()) 





                                                                                                                          share|improve this answer





















                                                                                                                          • Missing the values...
                                                                                                                            – dipi evil
                                                                                                                            Oct 11 '17 at 17:41















                                                                                                                          up vote
                                                                                                                          0
                                                                                                                          down vote













                                                                                                                          One liner:



                                                                                                                          string.Join(",", from SqlParameter p in cmd.Parameters select p.ToString()) 





                                                                                                                          share|improve this answer





















                                                                                                                          • Missing the values...
                                                                                                                            – dipi evil
                                                                                                                            Oct 11 '17 at 17:41













                                                                                                                          up vote
                                                                                                                          0
                                                                                                                          down vote










                                                                                                                          up vote
                                                                                                                          0
                                                                                                                          down vote









                                                                                                                          One liner:



                                                                                                                          string.Join(",", from SqlParameter p in cmd.Parameters select p.ToString()) 





                                                                                                                          share|improve this answer












                                                                                                                          One liner:



                                                                                                                          string.Join(",", from SqlParameter p in cmd.Parameters select p.ToString()) 






                                                                                                                          share|improve this answer












                                                                                                                          share|improve this answer



                                                                                                                          share|improve this answer










                                                                                                                          answered Feb 22 '17 at 17:34









                                                                                                                          CheesusCrust

                                                                                                                          612




                                                                                                                          612












                                                                                                                          • Missing the values...
                                                                                                                            – dipi evil
                                                                                                                            Oct 11 '17 at 17:41


















                                                                                                                          • Missing the values...
                                                                                                                            – dipi evil
                                                                                                                            Oct 11 '17 at 17:41
















                                                                                                                          Missing the values...
                                                                                                                          – dipi evil
                                                                                                                          Oct 11 '17 at 17:41




                                                                                                                          Missing the values...
                                                                                                                          – dipi evil
                                                                                                                          Oct 11 '17 at 17:41










                                                                                                                          up vote
                                                                                                                          0
                                                                                                                          down vote













                                                                                                                          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.






                                                                                                                          share|improve this answer























                                                                                                                          • btw, other promising piece of code is at github.com/jeroenpot/SqlHelper/blob/master/Source/… (mentioned at an answer in this thread). Probably could merge code from SQLCommand and SqlGenerator if you find something not working at one or the other
                                                                                                                            – George Birbilis
                                                                                                                            Sep 19 at 15:59










                                                                                                                          • ...meant to say CommandAsSQL library instead of SQLCommand in the last comment
                                                                                                                            – George Birbilis
                                                                                                                            Sep 19 at 16:48















                                                                                                                          up vote
                                                                                                                          0
                                                                                                                          down vote













                                                                                                                          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.






                                                                                                                          share|improve this answer























                                                                                                                          • btw, other promising piece of code is at github.com/jeroenpot/SqlHelper/blob/master/Source/… (mentioned at an answer in this thread). Probably could merge code from SQLCommand and SqlGenerator if you find something not working at one or the other
                                                                                                                            – George Birbilis
                                                                                                                            Sep 19 at 15:59










                                                                                                                          • ...meant to say CommandAsSQL library instead of SQLCommand in the last comment
                                                                                                                            – George Birbilis
                                                                                                                            Sep 19 at 16:48













                                                                                                                          up vote
                                                                                                                          0
                                                                                                                          down vote










                                                                                                                          up vote
                                                                                                                          0
                                                                                                                          down vote









                                                                                                                          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.






                                                                                                                          share|improve this 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.







                                                                                                                          share|improve this answer














                                                                                                                          share|improve this answer



                                                                                                                          share|improve this answer








                                                                                                                          edited Sep 19 at 16:47

























                                                                                                                          answered Sep 19 at 15:58









                                                                                                                          George Birbilis

                                                                                                                          1,47811225




                                                                                                                          1,47811225












                                                                                                                          • btw, other promising piece of code is at github.com/jeroenpot/SqlHelper/blob/master/Source/… (mentioned at an answer in this thread). Probably could merge code from SQLCommand and SqlGenerator if you find something not working at one or the other
                                                                                                                            – George Birbilis
                                                                                                                            Sep 19 at 15:59










                                                                                                                          • ...meant to say CommandAsSQL library instead of SQLCommand in the last comment
                                                                                                                            – George Birbilis
                                                                                                                            Sep 19 at 16:48


















                                                                                                                          • btw, other promising piece of code is at github.com/jeroenpot/SqlHelper/blob/master/Source/… (mentioned at an answer in this thread). Probably could merge code from SQLCommand and SqlGenerator if you find something not working at one or the other
                                                                                                                            – George Birbilis
                                                                                                                            Sep 19 at 15:59










                                                                                                                          • ...meant to say CommandAsSQL library instead of SQLCommand in the last comment
                                                                                                                            – George Birbilis
                                                                                                                            Sep 19 at 16:48
















                                                                                                                          btw, other promising piece of code is at github.com/jeroenpot/SqlHelper/blob/master/Source/… (mentioned at an answer in this thread). Probably could merge code from SQLCommand and SqlGenerator if you find something not working at one or the other
                                                                                                                          – George Birbilis
                                                                                                                          Sep 19 at 15:59




                                                                                                                          btw, other promising piece of code is at github.com/jeroenpot/SqlHelper/blob/master/Source/… (mentioned at an answer in this thread). Probably could merge code from SQLCommand and SqlGenerator if you find something not working at one or the other
                                                                                                                          – George Birbilis
                                                                                                                          Sep 19 at 15:59












                                                                                                                          ...meant to say CommandAsSQL library instead of SQLCommand in the last comment
                                                                                                                          – George Birbilis
                                                                                                                          Sep 19 at 16:48




                                                                                                                          ...meant to say CommandAsSQL library instead of SQLCommand in the last comment
                                                                                                                          – George Birbilis
                                                                                                                          Sep 19 at 16:48


















                                                                                                                           

                                                                                                                          draft saved


                                                                                                                          draft discarded



















































                                                                                                                           


                                                                                                                          draft saved


                                                                                                                          draft discarded














                                                                                                                          StackExchange.ready(
                                                                                                                          function () {
                                                                                                                          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f265192%2fget-the-generated-sql-statement-from-a-sqlcommand-object%23new-answer', 'question_page');
                                                                                                                          }
                                                                                                                          );

                                                                                                                          Post as a guest















                                                                                                                          Required, but never shown





















































                                                                                                                          Required, but never shown














                                                                                                                          Required, but never shown












                                                                                                                          Required, but never shown







                                                                                                                          Required, but never shown

































                                                                                                                          Required, but never shown














                                                                                                                          Required, but never shown












                                                                                                                          Required, but never shown







                                                                                                                          Required, but never shown







                                                                                                                          這個網誌中的熱門文章

                                                                                                                          Xamarin.form Move up view when keyboard appear

                                                                                                                          Post-Redirect-Get with Spring WebFlux and Thymeleaf

                                                                                                                          Anylogic : not able to use stopDelay()