qdr.fnd/Common/Data/Extensions/SqlCommandExt.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Crestyl.DMS.Console.Migration.Commands.Migration.Assets
{
    public static class SqlCommandExt
    {
        private static string ParameterValueForSQL(this SqlParameter sp)
        {
            var result = "";
            if (sp.Value == null || sp.Value == DBNull.Value)
                return "NULL";

            switch (sp.SqlDbType)
            {
                case SqlDbType.UniqueIdentifier:
                    result = "'" + ((Guid)sp.Value).ToString("B") + "'";
                    break;
                case SqlDbType.Char:
                case SqlDbType.NChar:
                case SqlDbType.NText:
                case SqlDbType.NVarChar:
                case SqlDbType.Text:
                case SqlDbType.Time:
                case SqlDbType.VarChar:
                case SqlDbType.Xml:
                    result = "'" + sp.Value.ToString().Replace("'", "''") + "'";
                    break;
                case SqlDbType.Date:
                case SqlDbType.DateTime:
                case SqlDbType.DateTime2:
                case SqlDbType.DateTimeOffset:
                    result = "'" + ((DateTime)sp.Value).ToString("s").Replace("'", "''") + "'";
                    break;

                case SqlDbType.Bit:
                    result = ((int)sp.Value == 1) ? "1" : "0";
                    break;

                default:
                    result = sp.Value.ToString().Replace("'", "''");
                    break;
            }

            return result;
        }

        public static string CommandAsSql(this SqlCommand sc)
        {
            StringBuilder sql = new StringBuilder();
            var isFirst = 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((isFirst) ? "\t" : "\t, ");

                            if (isFirst) isFirst = 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:
                    foreach (SqlParameter sp in sc.Parameters)
                    {
                        if (sp.Direction == ParameterDirection.Input)
                        {
                            sql.Append("declare " + sp.ParameterName + "\t" + sp.SqlDbType.ToString() + "\t= ");

                            sql.AppendLine(((sp.Direction == ParameterDirection.Output) ? "null" : sp.ParameterValueForSQL()) + ";");

                        }
                    }
                    sql.AppendLine(sc.CommandText);
                    break;
            }

            return sql.ToString();
        }
    }
}