我有以下代码:
Using cmd As SqlCommand = Connection.CreateCommand
cmd.CommandText = "UPDATE someTable SET Value = @Value"
cmd.CommandText &= " WHERE Id = @Id"
cmd.Parameters.AddWithValue("@Id", 1234)
cmd.Parameters.AddWithValue("@Value", "myValue")
cmd.ExecuteNonQuery
End Using
我想知道是否有任何方法来获得最终的SQL语句作为字符串，它应该看起来像这样:
UPDATE someTable SET Value = "myValue" WHERE Id = 1234
如果有人想知道我为什么要这么做
用于记录(失败的)语句 可以将其复制粘贴到企业管理器中进行测试
当前回答
如果你要转换命令文本:
Private Function ConvToNonParm(ByRef Cmd As SqlClient.SqlCommand) As String
For myCnt As Int16 = 1 To Cmd.Parameters.Count
Dim myVal As String = Cmd.Parameters(myCnt - 1).Value
Select Case Cmd.Parameters(myCnt - 1).SqlDbType
Case SqlDbType.Char, SqlDbType.NChar, SqlDbType.VarChar, SqlDbType.NChar, SqlDbType.NVarChar 'and so on
myVal = "'" & myVal & "'"
'Case "others...."
Case Else
'please assing
End Select
Cmd.CommandText = Replace(Cmd.CommandText, Cmd.Parameters(myCnt - 1).ToString, myVal)
Next
Cmd.Parameters.Clear()
Return Cmd.CommandText
End Function
现在你可以像下面这样获得非参数commandtext:
myCmd.CommandText = "UPDATE someTable SET Value = @Value"
myCmd.CommandText &= " WHERE Id = @Id"
myCmd.Parameters.AddWithValue("@Id", 1234)
myCmd.Parameters.AddWithValue("@Value", "myValue")
myCmd.CommandText = ConvToNonParm(myCmd)
结果是“更新someTable SET值= 'myValue' WHERE Id = 1234”，不再带参数
其他回答
这是我用来将存储过程的参数列表输出到调试控制台的:
string query = (from SqlParameter p in sqlCmd.Parameters where p != null where p.Value != null select string.Format("Param: {0} = {1}, ", p.ParameterName, p.Value.ToString())).Aggregate(sqlCmd.CommandText, (current, parameter) => current + parameter);
Debug.WriteLine(query);
这将生成一个类似于下面的控制台输出:
Customer.prGetCustomerDetails: @Offset = 1, Param: @Fetch = 10, Param: @CategoryLevel1ID = 3, Param: @VehicleLineID = 9, Param: @SalesCode1 = bce,
我把这段代码直接放在我希望调试的任何过程的下面，它类似于sql分析器会话，但使用c#。
扩展Kon的代码来帮助调试存储过程:
private void ExtractSqlCommandForDebugging(SqlCommand cmd)
{
string sql = "exec " + cmd.CommandText;
bool first = true;
foreach (SqlParameter p in cmd.Parameters)
{
string value = ((p.Value == DBNull.Value) ? "null"
: (p.Value is string) ? "'" + p.Value + "'"
: p.Value.ToString());
if (first)
{
sql += string.Format(" {0}={1}", p.ParameterName, value);
first = false;
}
else
{
sql += string.Format("\n , {0}={1}", p.ParameterName, value);
}
}
sql += "\nGO";
Debug.WriteLine(sql);
}
在我的第一个测试用例中，它生成:
exec dbo.MyStoredProcName @SnailMail=False
, @Email=True
, @AcceptSnailMail=False
, @AcceptEmail=False
, @DistanceMiles=-1
, @DistanceLocationList=''
, @ExcludeDissatisfied=True
, @ExcludeCodeRed=True
, @MinAge=null
, @MaxAge=18
, @GenderTypeID=-1
, @NewThisYear=-1
, @RegisteredThisYear=-1
, @FormersTermGroupList=''
, @RegistrationStartDate=null
, @RegistrationEndDate=null
, @DivisionList='25'
, @LocationList='29,30'
, @OneOnOneOPL=-1
, @JumpStart=-1
, @SmallGroup=-1
, @PurchasedEAP=-1
, @RedeemedEAP=-1
, @ReturnPlanYes=False
, @MinNetPromoter=-1
, @MinSurveyScore=-1
, @VIPExclusionTypes='-2'
, @FieldSelectionMask=65011584
, @DisplayType=0
GO
你可能需要添加一些有条件的“..is…”类型的赋值，例如日期和时间。
我需要一个类似于字符串转换器的命令来允许更详细的日志记录，所以我写了这个命令。它将生成在新会话中重新执行命令所需的文本，包括输出参数和结构化参数。它经过了轻微的检验，但买者自负。
例子:
SqlCommand cmd = new SqlCommand("GetEntity", con);
cmd.Parameters.AddWithValue("@foobar", 1);
cmd.Parameters.Add(new SqlParameter(){
ParameterName = "@outParam",
Direction = ParameterDirection.Output,
SqlDbType = System.Data.SqlDbType.Int
});
cmd.Parameters.Add(new SqlParameter(){
Direction = ParameterDirection.ReturnValue
});
cmd.CommandType = CommandType.StoredProcedure;
会产生:
-- BEGIN COMMAND
DECLARE @foobar INT = 1;
DECLARE @outParam INT = NULL;
DECLARE @returnValue INT;
-- END PARAMS
EXEC @returnValue = GetEntity @foobar = @foobar, @outParam = @outParam OUTPUT
-- RESULTS
SELECT 1 as Executed, @returnValue as ReturnValue, @outParam as [@outParam];
-- END COMMAND
实现:
public class SqlCommandDumper
{
public static string GetCommandText(SqlCommand sqc)
{
StringBuilder sbCommandText = new StringBuilder();
sbCommandText.AppendLine("-- BEGIN COMMAND");
// params
for (int i = 0; i < sqc.Parameters.Count; i++)
logParameterToSqlBatch(sqc.Parameters[i], sbCommandText);
sbCommandText.AppendLine("-- END PARAMS");
// command
if (sqc.CommandType == CommandType.StoredProcedure)
{
sbCommandText.Append("EXEC ");
bool hasReturnValue = false;
for (int i = 0; i < sqc.Parameters.Count; i++)
{
if (sqc.Parameters[i].Direction == ParameterDirection.ReturnValue)
hasReturnValue = true;
}
if (hasReturnValue)
{
sbCommandText.Append("@returnValue = ");
}
sbCommandText.Append(sqc.CommandText);
bool hasPrev = false;
for (int i = 0; i < sqc.Parameters.Count; i++)
{
var cParam = sqc.Parameters[i];
if (cParam.Direction != ParameterDirection.ReturnValue)
{
if (hasPrev)
sbCommandText.Append(",");
sbCommandText.Append(" ");
sbCommandText.Append(cParam.ParameterName);
sbCommandText.Append(" = ");
sbCommandText.Append(cParam.ParameterName);
if (cParam.Direction.HasFlag(ParameterDirection.Output))
sbCommandText.Append(" OUTPUT");
hasPrev = true;
}
}
}
else
{
sbCommandText.AppendLine(sqc.CommandText);
}
sbCommandText.AppendLine("-- RESULTS");
sbCommandText.Append("SELECT 1 as Executed");
for (int i = 0; i < sqc.Parameters.Count; i++)
{
var cParam = sqc.Parameters[i];
if (cParam.Direction == ParameterDirection.ReturnValue)
{
sbCommandText.Append(", @returnValue as ReturnValue");
}
else if (cParam.Direction.HasFlag(ParameterDirection.Output))
{
sbCommandText.Append(", ");
sbCommandText.Append(cParam.ParameterName);
sbCommandText.Append(" as [");
sbCommandText.Append(cParam.ParameterName);
sbCommandText.Append(']');
}
}
sbCommandText.AppendLine(";");
sbCommandText.AppendLine("-- END COMMAND");
return sbCommandText.ToString();
}
private static void logParameterToSqlBatch(SqlParameter param, StringBuilder sbCommandText)
{
sbCommandText.Append("DECLARE ");
if (param.Direction == ParameterDirection.ReturnValue)
{
sbCommandText.AppendLine("@returnValue INT;");
}
else
{
sbCommandText.Append(param.ParameterName);
sbCommandText.Append(' ');
if (param.SqlDbType != SqlDbType.Structured)
{
logParameterType(param, sbCommandText);
sbCommandText.Append(" = ");
logQuotedParameterValue(param.Value, sbCommandText);
sbCommandText.AppendLine(";");
}
else
{
logStructuredParameter(param, sbCommandText);
}
}
}
private static void logStructuredParameter(SqlParameter param, StringBuilder sbCommandText)
{
sbCommandText.AppendLine(" {List Type};");
var dataTable = (DataTable)param.Value;
for (int rowNo = 0; rowNo < dataTable.Rows.Count; rowNo++)
{
sbCommandText.Append("INSERT INTO ");
sbCommandText.Append(param.ParameterName);
sbCommandText.Append(" VALUES (");
bool hasPrev = false;
for (int colNo = 0; colNo < dataTable.Columns.Count; colNo++)
{
if (hasPrev)
{
sbCommandText.Append(", ");
}
logQuotedParameterValue(dataTable.Rows[rowNo].ItemArray[colNo], sbCommandText);
hasPrev = true;
}
sbCommandText.AppendLine(");");
}
}
const string DATETIME_FORMAT_ROUNDTRIP = "o";
private static void logQuotedParameterValue(object value, StringBuilder sbCommandText)
{
try
{
if (value == null)
{
sbCommandText.Append("NULL");
}
else
{
value = unboxNullable(value);
if (value is string
|| value is char
|| value is char[]
|| value is System.Xml.Linq.XElement
|| value is System.Xml.Linq.XDocument)
{
sbCommandText.Append("N'");
sbCommandText.Append(value.ToString().Replace("'", "''"));
sbCommandText.Append('\'');
}
else if (value is bool)
{
// True -> 1, False -> 0
sbCommandText.Append(Convert.ToInt32(value));
}
else if (value is sbyte
|| value is byte
|| value is short
|| value is ushort
|| value is int
|| value is uint
|| value is long
|| value is ulong
|| value is float
|| value is double
|| value is decimal)
{
sbCommandText.Append(value.ToString());
}
else if (value is DateTime)
{
// SQL Server only supports ISO8601 with 3 digit precision on datetime,
// datetime2 (>= SQL Server 2008) parses the .net format, and will
// implicitly cast down to datetime.
// Alternatively, use the format string "yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fffK"
// to match SQL server parsing
sbCommandText.Append("CAST('");
sbCommandText.Append(((DateTime)value).ToString(DATETIME_FORMAT_ROUNDTRIP));
sbCommandText.Append("' as datetime2)");
}
else if (value is DateTimeOffset)
{
sbCommandText.Append('\'');
sbCommandText.Append(((DateTimeOffset)value).ToString(DATETIME_FORMAT_ROUNDTRIP));
sbCommandText.Append('\'');
}
else if (value is Guid)
{
sbCommandText.Append('\'');
sbCommandText.Append(((Guid)value).ToString());
sbCommandText.Append('\'');
}
else if (value is byte[])
{
var data = (byte[])value;
if (data.Length == 0)
{
sbCommandText.Append("NULL");
}
else
{
sbCommandText.Append("0x");
for (int i = 0; i < data.Length; i++)
{
sbCommandText.Append(data[i].ToString("x"));
}
}
}
else
{
sbCommandText.Append("/* UNKNOWN DATATYPE: ");
sbCommandText.Append(value.GetType().ToString());
sbCommandText.Append(" *" + "/ N'");
sbCommandText.Append(value.ToString());
sbCommandText.Append('\'');
}
}
}
catch (Exception ex)
{
sbCommandText.AppendLine("/* Exception occurred while converting parameter: ");
sbCommandText.AppendLine(ex.ToString());
sbCommandText.AppendLine("*/");
}
}
private static object unboxNullable(object value)
{
var typeOriginal = value.GetType();
if (typeOriginal.IsGenericType
&& typeOriginal.GetGenericTypeDefinition() == typeof(Nullable<>))
{
// generic value, unboxing needed
return typeOriginal.InvokeMember("GetValueOrDefault",
System.Reflection.BindingFlags.Public |
System.Reflection.BindingFlags.Instance |
System.Reflection.BindingFlags.InvokeMethod,
null, value, null);
}
else
{
return value;
}
}
private static void logParameterType(SqlParameter param, StringBuilder sbCommandText)
{
switch (param.SqlDbType)
{
// variable length
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.Binary:
{
sbCommandText.Append(param.SqlDbType.ToString().ToUpper());
sbCommandText.Append('(');
sbCommandText.Append(param.Size);
sbCommandText.Append(')');
}
break;
case SqlDbType.VarBinary:
case SqlDbType.Image:
{
sbCommandText.Append("VARBINARY");
sbCommandText.Append("(MAX /* Specified as ");
sbCommandText.Append(param.Size);
sbCommandText.Append(" */)");
}
break;
case SqlDbType.VarChar:
case SqlDbType.Text:
{
sbCommandText.Append("VARCHAR");
sbCommandText.Append("(MAX /* Specified as ");
sbCommandText.Append(param.Size);
sbCommandText.Append(" */)");
}
break;
case SqlDbType.NVarChar:
case SqlDbType.NText:
{
sbCommandText.Append("NVARCHAR");
sbCommandText.Append("(MAX /* Specified as ");
sbCommandText.Append(param.Size);
sbCommandText.Append(" */)");
}
break;
// fixed length
case SqlDbType.Bit:
case SqlDbType.TinyInt:
case SqlDbType.SmallInt:
case SqlDbType.Int:
case SqlDbType.BigInt:
case SqlDbType.SmallMoney:
case SqlDbType.Money:
case SqlDbType.Decimal:
case SqlDbType.Real:
case SqlDbType.Float:
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
case SqlDbType.UniqueIdentifier:
{
sbCommandText.Append(param.SqlDbType.ToString().ToUpper());
}
break;
// Unknown
case SqlDbType.Timestamp:
default:
{
sbCommandText.Append("/* UNKNOWN DATATYPE: ");
sbCommandText.Append(param.SqlDbType.ToString().ToUpper());
sbCommandText.Append(" *" + "/ ");
sbCommandText.Append(param.SqlDbType.ToString().ToUpper());
}
break;
}
}
}
sql命令查询将通过exec sp_executesql执行，所以这里有另一种方法来获取语句作为字符串(SqlCommand扩展方法):
public static string ToSqlStatement(this SqlCommand cmd)
{
return $@"EXECUTE sp_executesql N'{cmd.CommandText.Replace("'", "''")}'{cmd.Parameters.ToSqlParameters()}";
}
private static string ToSqlParameters(this SqlParameterCollection col)
{
if (col.Count == 0)
return string.Empty;
var parameters = new List<string>();
var parameterValues = new List<string>();
foreach (SqlParameter param in col)
{
parameters.Add($"{param.ParameterName}{param.ToSqlParameterType()}");
parameterValues.Add($"{param.ParameterName} = {param.ToSqlParameterValue()}");
}
return $",N\'{string.Join(",", parameters)}\',{string.Join(",", parameterValues)}";
}
private static object ToSqlParameterType(this SqlParameter param)
{
var paramDbType = param.SqlDbType.ToString().ToLower();
if (param.Precision != 0 && param.Scale != 0)
return $"{paramDbType}({param.Precision},{param.Scale})";
if (param.Precision != 0)
return $"{paramDbType}({param.Precision})";
switch (param.SqlDbType)
{
case SqlDbType.VarChar:
case SqlDbType.NVarChar:
string s = param.SqlValue?.ToString() ?? string.Empty;
return paramDbType + (s.Length > 0 ? $"({s.Length})" : string.Empty);
default:
return paramDbType;
}
}
private static string ToSqlParameterValue(this SqlParameter param)
{
switch (param.SqlDbType)
{
case SqlDbType.Char:
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.Time:
case SqlDbType.VarChar:
case SqlDbType.Xml:
return $"\'{param.SqlValue.ToString().Replace("'", "''")}\'";
case SqlDbType.Bit:
return param.SqlValue.ToBooleanOrDefault() ? "1" : "0";
default:
return param.SqlValue.ToString().Replace("'", "''");
}
}
public static bool ToBooleanOrDefault(this object o, bool defaultValue = false)
{
if (o == null)
return defaultValue;
string value = o.ToString().ToLower();
switch (value)
{
case "yes":
case "true":
case "ok":
case "y":
return true;
case "no":
case "false":
case "n":
return false;
default:
bool b;
if (bool.TryParse(o.ToString(), out b))
return b;
break;
}
return defaultValue;
}
如果你的数据库是Oracle, sql文本包含动态变量，如:1，:2，…然后你可以使用:
string query = cmd.CommandText;
int i = 1;
foreach (OracleParameter p in cmd.Parameters)
{
query = query.Replace(":"+i.ToString(),((p.Value==null)?"":p.Value.ToString()));
i++;
}
