SqlDebugHelper.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;
using System.Linq;
namespace R.SqlDebugHelper
{
    public static class SqlDebugHelper
    {
        /// <summary>
        /// Generate T-SQL script from SqlParameter array and output to Debug window
        /// </summary>
        /// <param name="spName">Stored Procedure Name</param>
        /// <param name="parameters">SqlParameter array</param>
        public static void Print(string spName, SqlParameter[] parameters)
        {
            GenerateAndLog(spName, parameters);
        }

        /// <summary>
        /// Generate T-SQL script from SqlCommand object and output to Debug window
        /// </summary>
        /// <param name="cmd">SqlCommand object</param>
        public static void Print(SqlCommand cmd)
        {
            GenerateAndLog(cmd.CommandText, cmd.Parameters.Cast<SqlParameter>().ToArray());
        }

        private static void GenerateAndLog(string spName, SqlParameter[] parameters)
        {
            try
            {
                var sb = new StringBuilder();
                sb.AppendLine("-- SP: " + spName);
                sb.AppendLine("-- Generated: " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                sb.AppendLine("DECLARE ");

                foreach (SqlParameter p in parameters)
                {
                    if (p.Direction == ParameterDirection.ReturnValue) continue;
                    
                    string paramName = p.ParameterName;
                    string sqlType = GetSqlType(p);
                    string sqlValue = GetSqlValue(p.Value);
                    
                    sb.AppendLine(paramName + " " + sqlType + " = " + sqlValue + ",");
                }

                sb.AppendLine("BEGIN");
                sb.Append("EXEC " + spName + " ");
                
                bool first = true;
                foreach (SqlParameter p in parameters)
                {
                    if (p.Direction == ParameterDirection.ReturnValue) continue;
                    if (first) { first = false; } else { sb.Append(","); }
                    sb.Append(p.ParameterName);
                }
                
                sb.AppendLine();
                sb.AppendLine("END");
                
                Debug.WriteLine(sb.ToString());
                Debug.WriteLine("--------------------------------------------------");
            }
            catch (Exception ex)
            {
                Debug.WriteLine("DebugHelper Error: " + ex.Message);
            }
        }

        private static string GetSqlType(SqlParameter p)
        {
            if (p.SqlDbType != SqlDbType.VarChar && p.SqlDbType != SqlDbType.Int)
            {
                return GetSqlTypeFromDbType(p.SqlDbType);
            }
            
            object val = p.Value;
            if (val == null || val == DBNull.Value) return "INT";
            if (val is bool) return "BIT";
            if (val is DateTime) return "DATETIME";
            if (val is string) return "NVARCHAR(MAX)";
            if (val is decimal) return "DECIMAL(18,4)";
            if (val is double) return "FLOAT";
            if (val is long) return "BIGINT";
            if (val is Guid) return "UNIQUEIDENTIFIER";
            return "INT";
        }

        private static string GetSqlTypeFromDbType(SqlDbType dbType)
        {
            switch (dbType)
            {
                case SqlDbType.Bit: return "BIT";
                case SqlDbType.DateTime:
                case SqlDbType.DateTime2: return "DATETIME";
                case SqlDbType.NVarChar:
                case SqlDbType.VarChar:
                case SqlDbType.NChar:
                case SqlDbType.Char: return "NVARCHAR(MAX)";
                case SqlDbType.Decimal: return "DECIMAL(18,4)";
                case SqlDbType.Float: return "FLOAT";
                case SqlDbType.BigInt: return "BIGINT";
                case SqlDbType.UniqueIdentifier: return "UNIQUEIDENTIFIER";
                case SqlDbType.Int: return "INT";
                case SqlDbType.SmallInt: return "SMALLINT";
                case SqlDbType.TinyInt: return "TINYINT";
                case SqlDbType.Money: return "MONEY";
                case SqlDbType.Date: return "DATE";
                case SqlDbType.Time: return "TIME";
                case SqlDbType.DateTimeOffset: return "DATETIMEOFFSET";
                case SqlDbType.Binary:
                case SqlDbType.VarBinary: return "VARBINARY(MAX)";
                case SqlDbType.Xml: return "XML";
                case SqlDbType.Text:
                case SqlDbType.NText: return "TEXT";
                default: return "INT";
            }
        }

        private static string GetSqlValue(object val)
        {
            if (val == null || val == DBNull.Value) return "NULL";
            
            if (val is bool)
            {
                return ((bool)val) ? "1" : "0";
            }
            
            if (val is DateTime)
            {
                return "'" + ((DateTime)val).ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            
            if (val is string)
            {
                string s = (string)val;
                return "'" + s.Replace("'", "''") + "'";
            }
            
            if (val is Guid)
            {
                return "'" + ((Guid)val).ToString() + "'";
            }
            
            return val.ToString();
        }
    }
}