1 using System; 2 using System.Data; 3 using System.Data.SqlClient; 4 using System.Text; 5 using System.Text.RegularExpressions; 6 7 namespace CustomExtensions 8 { 9 public static class sqlExtensions 10 { 11 public static String ParameterValueForSQL(this SqlParameter sp) 12 { 13 String retval = ""; 14 15 switch (sp.SqlDbType) 16 { 17 case SqlDbType.Char: 18 case SqlDbType.NChar: 19 case SqlDbType.NText: 20 case SqlDbType.NVarChar: 21 case SqlDbType.Text: 22 case SqlDbType.Time: 23 case SqlDbType.VarChar: 24 case SqlDbType.Xml: 25 case SqlDbType.Date: 26 case SqlDbType.DateTime: 27 case SqlDbType.DateTime2: 28 case SqlDbType.DateTimeOffset: 29 retval = "'" + sp.Value.ToString().Replace("'", "''") + "'"; 30 break; 31 32 case SqlDbType.Bit: 33 retval = bool.Parse(sp.Value.ToString()) ? "1" : "0"; 34 break; 35 36 default: 37 retval = sp.Value.ToString().Replace("'", "''"); 38 break; 39 } 40 41 return retval; 42 } 43 44 public static String CommandAsSql(this SqlCommand sc) 45 { 46 StringBuilder sql = new StringBuilder(); 47 Boolean FirstParam = true; 48 49 sql.AppendLine("use " + sc.Connection.Database + ";"); 50 switch (sc.CommandType) 51 { 52 case CommandType.StoredProcedure: 53 sql.AppendLine("declare @return_value int;"); 54 55 foreach (SqlParameter sp in sc.Parameters) 56 { 57 if ((sp.Direction == ParameterDirection.InputOutput) || (sp.Direction == ParameterDirection.Output)) 58 { 59 sql.Append("declare " + sp.ParameterName + "\t" + sp.SqlDbType.ToString() + "\t= "); 60 61 sql.AppendLine(((sp.Direction == ParameterDirection.Output) ? "null" : sp.ParameterValueForSQL()) + ";"); 62 63 } 64 } 65 66 sql.AppendLine("exec [" + sc.CommandText + "]"); 67 68 foreach (SqlParameter sp in sc.Parameters) 69 { 70 if (sp.Direction != ParameterDirection.ReturnValue) 71 { 72 sql.Append((FirstParam) ? "\t" : "\t, "); 73 74 if (FirstParam) FirstParam = false; 75 76 if (sp.Direction == ParameterDirection.Input) 77 sql.AppendLine(sp.ParameterName + " = " + sp.ParameterValueForSQL()); 78 else 79 80 sql.AppendLine(sp.ParameterName + " = " + sp.ParameterName + " output"); 81 } 82 } 83 sql.AppendLine(";"); 84 85 sql.AppendLine("select 'Return Value' = convert(varchar, @return_value);"); 86 87 foreach (SqlParameter sp in sc.Parameters) 88 { 89 if ((sp.Direction == ParameterDirection.InputOutput) || (sp.Direction == ParameterDirection.Output)) 90 { 91 sql.AppendLine("select '" + sp.ParameterName + "' = convert(varchar, " + sp.ParameterName + ");"); 92 } 93 } 94 break; 95 case CommandType.Text: 96 string query = sc.CommandText; 97 foreach (SqlParameter sp in sc.Parameters) 98 { 99 query = Regex.Replace(query, sp.ParameterName+ @"([^\w]|$)", sp.ParameterValueForSQL()+"$1"); 100 } 101 sql.AppendLine(query); 102 break; 103 } 104 105 return sql.ToString(); 106 } 107 } 108 }