C#从一个SqlCommand对象生成可执行的SQL语句

  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 + "	" + sp.SqlDbType.ToString() + "	= ");
 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) ? "	" : "	, ");
 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 }