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:
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.
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());
}
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.
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 = false;
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;
}
}
}
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.
One liner:
string.Join(",", from SqlParameter p in cmd.Parameters select p.ToString())
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#.
If you will convert the commandtext:
Private Function ConvToNonParm(ByRef Cmd As SqlClient.SqlCommand) As String
For myCnt As Int16 = 1 To Cmd.Parameters.Count
Dim myVal As String = Cmd.Parameters(myCnt - 1).Value
Select Case Cmd.Parameters(myCnt - 1).SqlDbType
Case SqlDbType.Char, SqlDbType.NChar, SqlDbType.VarChar, SqlDbType.NChar, SqlDbType.NVarChar 'and so on
myVal = "'" & myVal & "'"
'Case "others...."
Case Else
'please assing
End Select
Cmd.CommandText = Replace(Cmd.CommandText, Cmd.Parameters(myCnt - 1).ToString, myVal)
Next
Cmd.Parameters.Clear()
Return Cmd.CommandText
End Function
Now you can get the non parameter commandtext as follows:
myCmd.CommandText = "UPDATE someTable SET Value = @Value"
myCmd.CommandText &= " WHERE Id = @Id"
myCmd.Parameters.AddWithValue("@Id", 1234)
myCmd.Parameters.AddWithValue("@Value", "myValue")
myCmd.CommandText = ConvToNonParm(myCmd)
and the Result is "UPDATE someTable SET Value = 'myValue' WHERE Id = 1234" without parameter anymore
If your database was Oracle and the sql text contains dynamic variables named like :1,:2 ,... then you can use:
string query = cmd.CommandText;
int i = 1;
foreach (OracleParameter p in cmd.Parameters)
{
query = query.Replace(":"+i.ToString(),((p.Value==null)?"":p.Value.ToString()));
i++;
}
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()));
}
}
Extended Kon's code to help debug a stored procedure:
private void ExtractSqlCommandForDebugging(SqlCommand cmd)
{
string sql = "exec " + cmd.CommandText;
bool first = true;
foreach (SqlParameter p in cmd.Parameters)
{
string value = ((p.Value == DBNull.Value) ? "null"
: (p.Value is string) ? "'" + p.Value + "'"
: p.Value.ToString());
if (first)
{
sql += string.Format(" {0}={1}", p.ParameterName, value);
first = false;
}
else
{
sql += string.Format("\n , {0}={1}", p.ParameterName, value);
}
}
sql += "\nGO";
Debug.WriteLine(sql);
}
In my first test case, it generated:
exec dbo.MyStoredProcName @SnailMail=False
, @Email=True
, @AcceptSnailMail=False
, @AcceptEmail=False
, @DistanceMiles=-1
, @DistanceLocationList=''
, @ExcludeDissatisfied=True
, @ExcludeCodeRed=True
, @MinAge=null
, @MaxAge=18
, @GenderTypeID=-1
, @NewThisYear=-1
, @RegisteredThisYear=-1
, @FormersTermGroupList=''
, @RegistrationStartDate=null
, @RegistrationEndDate=null
, @DivisionList='25'
, @LocationList='29,30'
, @OneOnOneOPL=-1
, @JumpStart=-1
, @SmallGroup=-1
, @PurchasedEAP=-1
, @RedeemedEAP=-1
, @ReturnPlanYes=False
, @MinNetPromoter=-1
, @MinSurveyScore=-1
, @VIPExclusionTypes='-2'
, @FieldSelectionMask=65011584
, @DisplayType=0
GO
You will probably need to add some more conditional "..is..." type assignments, e.g. for dates and times.
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()));
}
}
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'
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("\r\n", "").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;
}
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);
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#.
If your database was Oracle and the sql text contains dynamic variables named like :1,:2 ,... then you can use:
string query = cmd.CommandText;
int i = 1;
foreach (OracleParameter p in cmd.Parameters)
{
query = query.Replace(":"+i.ToString(),((p.Value==null)?"":p.Value.ToString()));
i++;
}
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;
}
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());
}
From parameter command to non parameter command, You Can change this one
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
To
Private sub Update( byval myID as Int32, byval myVal as String)
Using cmd As SqlCommand = Connection.CreateCommand
cmd.CommandText = "UPDATE someTable SET Value = '" & myVaL & "'" & _
" WHERE Id = " & myID
cmd.ExecuteNonQuery
End Using
End sub
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;
}
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.
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.
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
If you will convert the commandtext:
Private Function ConvToNonParm(ByRef Cmd As SqlClient.SqlCommand) As String
For myCnt As Int16 = 1 To Cmd.Parameters.Count
Dim myVal As String = Cmd.Parameters(myCnt - 1).Value
Select Case Cmd.Parameters(myCnt - 1).SqlDbType
Case SqlDbType.Char, SqlDbType.NChar, SqlDbType.VarChar, SqlDbType.NChar, SqlDbType.NVarChar 'and so on
myVal = "'" & myVal & "'"
'Case "others...."
Case Else
'please assing
End Select
Cmd.CommandText = Replace(Cmd.CommandText, Cmd.Parameters(myCnt - 1).ToString, myVal)
Next
Cmd.Parameters.Clear()
Return Cmd.CommandText
End Function
Now you can get the non parameter commandtext as follows:
myCmd.CommandText = "UPDATE someTable SET Value = @Value"
myCmd.CommandText &= " WHERE Id = @Id"
myCmd.Parameters.AddWithValue("@Id", 1234)
myCmd.Parameters.AddWithValue("@Value", "myValue")
myCmd.CommandText = ConvToNonParm(myCmd)
and the Result is "UPDATE someTable SET Value = 'myValue' WHERE Id = 1234" without parameter anymore
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.
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.
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.
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());
}
From parameter command to non parameter command, You Can change this one
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
To
Private sub Update( byval myID as Int32, byval myVal as String)
Using cmd As SqlCommand = Connection.CreateCommand
cmd.CommandText = "UPDATE someTable SET Value = '" & myVaL & "'" & _
" WHERE Id = " & myID
cmd.ExecuteNonQuery
End Using
End sub
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("\r\n", "").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;
}
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.
Late answer, I know but I too wanted this so I could log the SQL. The following is short and meets my needs.
The following produces SQL you can copy/paste in SSMS (it replaces the parameters with the values properly). You can add more types but this meets all I use in this case.
private static void LogSQL(SqlCommand cmd)
{
string query = cmd.CommandText;
foreach (SqlParameter prm in cmd.Parameters)
{
switch (prm.SqlDbType)
{
case SqlDbType.Bit:
int boolToInt = (bool)prm.Value ? 1 : 0;
query = query.Replace(prm.ParameterName, string.Format("{0}", (bool)prm.Value ? 1 : 0));
break;
case SqlDbType.Int:
query = query.Replace(prm.ParameterName, string.Format("{0}", prm.Value));
break;
case SqlDbType.VarChar:
query = query.Replace(prm.ParameterName, string.Format("'{0}'", prm.Value));
break;
default:
query = query.Replace(prm.ParameterName, string.Format("'{0}'", prm.Value));
break;
}
}
// the following is my how I write to my log - your use will vary
logger.Debug("{0}", query);
return;
}
Now I can log the SQL just before I execute it:
LogSQL(queryCmd)
queryCmd.ExecuteNonQuery()
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.
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'
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;
}
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.
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.
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.
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.
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();
}
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;
}
Late answer, I know but I too wanted this so I could log the SQL. The following is short and meets my needs.
The following produces SQL you can copy/paste in SSMS (it replaces the parameters with the values properly). You can add more types but this meets all I use in this case.
private static void LogSQL(SqlCommand cmd)
{
string query = cmd.CommandText;
foreach (SqlParameter prm in cmd.Parameters)
{
switch (prm.SqlDbType)
{
case SqlDbType.Bit:
int boolToInt = (bool)prm.Value ? 1 : 0;
query = query.Replace(prm.ParameterName, string.Format("{0}", (bool)prm.Value ? 1 : 0));
break;
case SqlDbType.Int:
query = query.Replace(prm.ParameterName, string.Format("{0}", prm.Value));
break;
case SqlDbType.VarChar:
query = query.Replace(prm.ParameterName, string.Format("'{0}'", prm.Value));
break;
default:
query = query.Replace(prm.ParameterName, string.Format("'{0}'", prm.Value));
break;
}
}
// the following is my how I write to my log - your use will vary
logger.Debug("{0}", query);
return;
}
Now I can log the SQL just before I execute it:
LogSQL(queryCmd)
queryCmd.ExecuteNonQuery()
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);
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());
}
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.
One liner:
string.Join(",", from SqlParameter p in cmd.Parameters select p.ToString())
Extended Kon's code to help debug a stored procedure:
private void ExtractSqlCommandForDebugging(SqlCommand cmd)
{
string sql = "exec " + cmd.CommandText;
bool first = true;
foreach (SqlParameter p in cmd.Parameters)
{
string value = ((p.Value == DBNull.Value) ? "null"
: (p.Value is string) ? "'" + p.Value + "'"
: p.Value.ToString());
if (first)
{
sql += string.Format(" {0}={1}", p.ParameterName, value);
first = false;
}
else
{
sql += string.Format("\n , {0}={1}", p.ParameterName, value);
}
}
sql += "\nGO";
Debug.WriteLine(sql);
}
In my first test case, it generated:
exec dbo.MyStoredProcName @SnailMail=False
, @Email=True
, @AcceptSnailMail=False
, @AcceptEmail=False
, @DistanceMiles=-1
, @DistanceLocationList=''
, @ExcludeDissatisfied=True
, @ExcludeCodeRed=True
, @MinAge=null
, @MaxAge=18
, @GenderTypeID=-1
, @NewThisYear=-1
, @RegisteredThisYear=-1
, @FormersTermGroupList=''
, @RegistrationStartDate=null
, @RegistrationEndDate=null
, @DivisionList='25'
, @LocationList='29,30'
, @OneOnOneOPL=-1
, @JumpStart=-1
, @SmallGroup=-1
, @PurchasedEAP=-1
, @RedeemedEAP=-1
, @ReturnPlanYes=False
, @MinNetPromoter=-1
, @MinSurveyScore=-1
, @VIPExclusionTypes='-2'
, @FieldSelectionMask=65011584
, @DisplayType=0
GO
You will probably need to add some more conditional "..is..." type assignments, e.g. for dates and times.
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 = false;
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;
}
}
}
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
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.
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();
}
Source: Stackoverflow.com