Converts a .NET SqlCommand object to an executable SQL string. This is useful for debugging queries or to examine the stored procedure parameters. Output returned from calling SqlCommandToQueryString.Convert(...) will be a fully valid SQL command that can be copy/pasted into SSMS.

SqlCommandToQueryString.cs

    /// <summary>Converts a SqlCommand object to the equivelant TSQL Query. This can be useful for examining the parameters or debugging generated statements using SSMS.</summary>
    /// <remarks>From https://stackoverflow.com/a/4146573/1363780 with style modifications and misc fixes</remarks>
    public static class SqlCommandToQueryString
    {
        /// <summary> Converts the SqlCommand to the equivelant TSQL query string</summary>
        /// <param name="cmd">The SqlCommand object to convert</param>
        /// <returns>A string representation of the SqlCommand. This will be valid TSQL and can be copy/pasted into SSMS and executed or debugged.</returns>
        public static string Convert(SqlCommand cmd)
        {
            StringBuilder sqlString = new StringBuilder();
            bool firstParameter = true;

            if (cmd.Connection != null)
            {
                sqlString.AppendLine("use " + cmd.Connection.Database + ";");
            }
            else
            {
                sqlString.AppendLine("/*Connection not specified. Unable to generate use [database] statement.*/");
            }

            switch (cmd.CommandType)
            {
                case CommandType.StoredProcedure:
                    sqlString.AppendLine("declare @return_value int;");

                    foreach (SqlParameter sp in cmd.Parameters)
                    {
                        if (sp.Direction == ParameterDirection.InputOutput || sp.Direction == ParameterDirection.Output)
                        {
                            sqlString.Append("declare " + FixParameterForm(sp) + "\t" + sp.SqlDbType + "\t= ");
                            sqlString.AppendLine(sp.Direction == ParameterDirection.Output ? "null;" : GetParameterValueAsSqlString(sp) + ";");
                        }
                    }

                    var storedProcedureName = cmd.CommandText.Replace("[", string.Empty).Replace("]", string.Empty);
                    sqlString.AppendLine("exec [" + storedProcedureName + "]");

                    foreach (SqlParameter parameter in cmd.Parameters)
                    {
                        if (parameter.Direction != ParameterDirection.ReturnValue)
                        {
                            sqlString.Append(firstParameter ? "\t" : "\t, ");

                            if (firstParameter)
                            {
                                firstParameter = false;
                            }

                            if (parameter.Direction == ParameterDirection.Input)
                            {
                                sqlString.AppendLine(FixParameterForm(parameter) + " = " + GetParameterValueAsSqlString(parameter));
                            }
                            else
                            {
                                sqlString.AppendLine(FixParameterForm(parameter) + " = " + FixParameterForm(parameter) + " output");
                            }
                        }
                    }

                    sqlString.AppendLine(";");
                    sqlString.AppendLine("select 'Return Value' = convert(varchar, @return_value);");

                    foreach (SqlParameter parameter in cmd.Parameters)
                    {
                        if (parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Output)
                        {
                            sqlString.AppendLine("select '" + parameter.ParameterName + "' = convert(varchar, " + FixParameterForm(parameter) + ");");
                        }
                    }
                    break;
                case CommandType.Text:
                    sqlString.AppendLine(cmd.CommandText);
                    break;
            }

            return sqlString.ToString();
        }

        /// <summary>Applies the @ symbol to the front of the parameter name if required</summary>
        private static string FixParameterForm(SqlParameter parameter)
        {
            if (parameter.ParameterName.StartsWith("@"))
            {
                return parameter.ParameterName;
            }

            return "@" + parameter.ParameterName;
        }

        private static string GetParameterValueAsSqlString(SqlParameter parameter)
        {
            string paramVal;

            if (parameter.Value == null)
            {
                return "NULL";
            }

            switch (parameter.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:
                    paramVal = "'" + parameter.Value.ToString().Replace("'", "''") + "'";
                    break;

                case SqlDbType.Bit:
                    paramVal = ConvertToBool(parameter.Value,false) ? "1" : "0";
                    break;

                default:
                    paramVal = parameter.Value.ToString().Replace("'", "''");
                    break;
            }

            return paramVal;
        }

        ///https://stackoverflow.com/q/3244850/1363780
        private static bool ConvertToBool(object o, bool Default)
        {
            bool value = Default;

            if (o == null)
            {
                return value;
            }

            try
            {
                switch (o.ToString().ToLower())
                {
                    case "yes":
                    case "true":
                    case "ok":
                    case "y":
                        value = true;
                        break;
                    case "no":
                    case "false":
                    case "n":
                        value = false;
                        break;
                    default:
                        value = bool.Parse(o.ToString());
                        break;
                }
            }
            catch
            {
                // ignored, default will be used
            }
            return value;
        }
    }