반응형
SQLCommand 개체에서 생성된 SQL 문을 가져오시겠습니까?
다음 코드가 있습니다.
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 스테이트먼트를 String으로 얻을 수 있는 방법이 있는지 궁금합니다.
UPDATE someTable SET Value = "myValue" WHERE Id = 1234
내가 왜 이런 짓을 하는지 궁금해 하는 사람이 있다면:
- 로깅(필수) 문의 경우
- 테스트 목적으로 Enterprise Manager에 복사하여 붙여넣을 수 있습니다.
로깅을 위해 스트링을 직접 작성하는 것 외에 더 좋은 방법은 없습니다.
string query = cmd.CommandText;
foreach (SqlParameter p in cmd.Parameters)
{
query = query.Replace(p.ParameterName, p.Value.ToString());
}
완벽하지는 않지만 TSQL을 위해 생각해 낸 것이 있습니다.다른 맛에 맞게 쉽게 조정할 수 있습니다.그 외에는 아무것도 없는 경우, 고객 자신의 개선을 위한 출발점이 됩니다.
이는 SSMS에서 "저장된 프로시저 실행"을 사용하는 것과 유사한 데이터 유형 및 출력 파라미터 등에 대해 정상 작업을 수행합니다.우리는 주로 SP를 사용했기 때문에 "text" 명령어는 파라미터 등을 고려하지 않습니다.
public static String ParameterValueForSQL(this SqlParameter sp)
{
String retval = "";
switch (sp.SqlDbType)
{
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.Time:
case SqlDbType.VarChar:
case SqlDbType.Xml:
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
retval = "'" + sp.Value.ToString().Replace("'", "''") + "'";
break;
case SqlDbType.Bit:
retval = (sp.Value.ToBooleanOrDefault(false)) ? "1" : "0";
break;
default:
retval = sp.Value.ToString().Replace("'", "''");
break;
}
return retval;
}
public static String CommandAsSql(this SqlCommand sc)
{
StringBuilder sql = new StringBuilder();
Boolean FirstParam = 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((FirstParam) ? "\t" : "\t, ");
if (FirstParam) FirstParam = 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:
sql.AppendLine(sc.CommandText);
break;
}
return sql.ToString();
}
그러면 이러한 라인을 따라 출력이 생성됩니다.
use dbMyDatabase;
declare @return_value int;
declare @OutTotalRows BigInt = null;
exec [spMyStoredProc]
@InEmployeeID = 1000686
, @InPageSize = 20
, @InPage = 1
, @OutTotalRows = @OutTotalRows output
;
select 'Return Value' = convert(varchar, @return_value);
select '@OutTotalRows' = convert(varchar, @OutTotalRows);
SQL을 생성하지 않기 때문에 할 수 없습니다.
된 쿼리(「」의 CommandText된 스테이트먼트에 으로 SQL 에 는 준비된 스테이트먼트에 상당하는 것으로 SQL Server 에 송신됩니다.명령을 실행하면 매개 변수와 쿼리 텍스트가 별도로 처리됩니다.SQL을 사용합니다.
SQL Profiler를 사용하여 이면을 살펴볼 수 있습니다.
보다 상세한 로깅을 위해 스트링 트랜스포머와 유사한 명령어가 필요했기 때문에 이 명령어를 작성했습니다.출력 파라미터 및 구조화 파라미터를 포함하여 새 세션에서 명령어를 재실행하는 데 필요한 텍스트를 생성합니다.간단한 테스트를 거쳤지만 경고 비우기입니다.
예:
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;
}
}
}
또한 일부 파라미터화된 쿼리 또는 SP에서 SQLException(대부분 문자열 또는 바이너리 데이터가 잘립니다)과 디버깅이 어려운 문장이 표시되는 문제가 있었습니다(현재 SQL Azure에 대한 SQL 프로파일러 지원은 없는 것으로 알고 있습니다).
여기서 반응하는 많은 시뮬레이션 코드를 볼 수 있습니다.나중에 사용할 수 있도록 솔루션을 SQL-Library 프로젝트에 배치했습니다.
제너레이터는 https://github.com/jeroenpot/SqlHelper/blob/master/Source/Mirabeau.MsSql.Library/SqlGenerator.cs 에서 구할 수 있습니다.
두 가지 명령 유형을 모두 지원합니다.텍스트와 명령어 타입.스토어드 프로시저
nuget-package 를 인스톨 하면, 다음의 스테이트먼트를 사용해 nuget-package 를 생성할 수 있습니다.
SqlDebugHelper.CreateExecutableSqlStatement(sql, parameters);
SQL Server를 사용하는 경우 SQL Server Profiler(있는 경우)를 사용하여 실제로 실행된 명령 문자열을 볼 수 있습니다.복사/붙여넣기 테스트에는 유용하지만 로깅에는 유용하지 않습니다.
답변이 늦었다는 건 알지만 SQL을 기록할 수 있도록 저도 이걸 원했어요.다음은 저의 요구에 부응하는 짧은 내용입니다.
다음으로 SSMS에서 복사/붙여넣을 수 있는SQL이 생성됩니다(파라미터는 값으로 올바르게 치환됩니다).더 많은 유형을 추가할 수 있지만 이 경우 사용하는 모든 유형을 충족합니다.
private static void LogSQL(SqlCommand cmd)
{
string query = cmd.CommandText;
foreach (SqlParameter prm in cmd.Parameters)
{
switch (prm.SqlDbType)
{
case SqlDbType.Bit:
int boolToInt = (bool)prm.Value ? 1 : 0;
query = query.Replace(prm.ParameterName, string.Format("{0}", (bool)prm.Value ? 1 : 0));
break;
case SqlDbType.Int:
query = query.Replace(prm.ParameterName, string.Format("{0}", prm.Value));
break;
case SqlDbType.VarChar:
query = query.Replace(prm.ParameterName, string.Format("'{0}'", prm.Value));
break;
default:
query = query.Replace(prm.ParameterName, string.Format("'{0}'", prm.Value));
break;
}
}
// the following is my how I write to my log - your use will vary
logger.Debug("{0}", query);
return;
}
이제 SQL을 실행하기 직전에 기록할 수 있습니다.
LogSQL(queryCmd)
queryCmd.ExecuteNonQuery()
Profiler는 쉽게 선택할 수 있는 최선의 옵션입니다.
준비 + 실행 절차로 인해 프로파일러에서 일련의 스테이트먼트를 복사해야 할 수 있습니다.
저도 같은 질문을 받았는데, 이 답변을 읽은 후 정확한 답변을 얻을 수 없다고 판단했습니다.잘못했습니다.
솔루션:열다.Activity MonitorSQL Server Management Studio문자열에서 로그인 사용자 이름, 또는 refresh 에 되었을 때Activity Monitor되었을 때, 클릭해, 「 」를 View Details.
사용 중인 DB에는 이 옵션이 적합하지 않을 수 있습니다.그러나 이러한 단계를 통해 결과를 상당히 좁힐 수 있습니다.
MS SQL SMS에서 실행할 파라미터 값을 포함한 전체 SQL 문자열을 반환하는 솔루션에 Flapper 코드의 일부를 사용했습니다.
public string ParameterValueForSQL(SqlParameter sp)
{
string retval = "";
switch (sp.SqlDbType)
{
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.Time:
case SqlDbType.VarChar:
case SqlDbType.Xml:
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
if (sp.Value == DBNull.Value)
{
retval = "NULL";
}
else
{
retval = "'" + sp.Value.ToString().Replace("'", "''") + "'";
}
break;
case SqlDbType.Bit:
if (sp.Value == DBNull.Value)
{
retval = "NULL";
}
else
{
retval = ((bool)sp.Value == false) ? "0" : "1";
}
break;
default:
if (sp.Value == DBNull.Value)
{
retval = "NULL";
}
else
{
retval = sp.Value.ToString().Replace("'", "''");
}
break;
}
return retval;
}
public string CommandAsSql(SqlCommand sc)
{
string sql = sc.CommandText;
sql = sql.Replace("\r\n", "").Replace("\r", "").Replace("\n", "");
sql = System.Text.RegularExpressions.Regex.Replace(sql, @"\s+", " ");
foreach (SqlParameter sp in sc.Parameters)
{
string spName = sp.ParameterName;
string spValue = ParameterValueForSQL(sp);
sql = sql.Replace(spName, spValue);
}
sql = sql.Replace("= NULL", "IS NULL");
sql = sql.Replace("!= NULL", "IS NOT NULL");
return sql;
}
마이솔루션:
public static class DbHelper
{
public static string ToString(this DbParameterCollection parameters, string sqlQuery)
{
return parameters.Cast<DbParameter>().Aggregate(sqlQuery, (current, p) => current.Replace(p.ParameterName, p.Value.ToString()));
}
}
나는 나를 위해 이 방법을 썼다.브루노 라트니크스의 코드 일부를 사용했어누군가에게 도움이 될지도 몰라요.
public static string getQueryFromCommand(SqlCommand cmd)
{
StringBuilder CommandTxt = new StringBuilder();
CommandTxt.Append("DECLARE ");
List<string> paramlst = new List<string>();
foreach (SqlParameter parms in cmd.Parameters)
{
paramlst.Add(parms.ParameterName);
CommandTxt.Append(parms.ParameterName + " AS ");
CommandTxt.Append(parms.SqlDbType.ToString());
CommandTxt.Append(",");
}
if (CommandTxt.ToString().Substring(CommandTxt.Length-1, 1) == ",")
CommandTxt.Remove(CommandTxt.Length-1, 1);
CommandTxt.AppendLine();
int rownr = 0;
foreach (SqlParameter parms in cmd.Parameters)
{
string val = String.Empty;
if (parms.DbType.Equals(DbType.String) || parms.DbType.Equals(DbType.DateTime))
val = "'" + Convert.ToString(parms.Value).Replace(@"\", @"\\").Replace("'", @"\'") + "'";
if (parms.DbType.Equals(DbType.Int16) || parms.DbType.Equals(DbType.Int32) || parms.DbType.Equals(DbType.Int64) || parms.DbType.Equals(DbType.Decimal) || parms.DbType.Equals(DbType.Double))
val = Convert.ToString(parms.Value);
CommandTxt.AppendLine();
CommandTxt.Append("SET " + paramlst[rownr].ToString() + " = " + val.ToString());
rownr += 1;
}
CommandTxt.AppendLine();
CommandTxt.AppendLine();
CommandTxt.Append(cmd.CommandText);
return CommandTxt.ToString();
}
에 "Oracle"과 같은 되어 있는 :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++;
}
결과 쿼리에서 매개 변수가 어떻게 형식 지정되었는지 확인하는 경우에만 대부분의 DBMS에서 무에서 리터럴 조회가 허용됩니다.다음과 같이 됩니다.
Using cmd As SqlCommand = Connection.CreateCommand
cmd.CommandText = "SELECT @Value"
cmd.Parameters.AddWithValue("@Value", "myValue")
Return cmd.ExecuteScalar
End Using
이렇게 하면 따옴표가 두 배인지 등을 확인할 수 있습니다.
다음은 저장 프로시저의 파라미터 목록을 디버깅콘솔에 출력하기 위해 사용합니다.
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,
이 코드는 디버깅하는 절차 바로 아래에 배치하며 C#에 있는 sql profiler 세션과 비슷합니다.
유사한 명명된 매개 변수에서만 작동하므로 Kon의 답변 수정 버전입니다.String Replace 기능을 사용하는 경우의 단점.그것 말고는, 나는 그 해결책에 대해 전적으로 그를 신뢰한다.
private string GetActualQuery(SqlCommand sqlcmd)
{
string query = sqlcmd.CommandText;
string parameters = "";
string[] strArray = System.Text.RegularExpressions.Regex.Split(query, " VALUES ");
//Reconstructs the second half of the SQL Command
parameters = "(";
int count = 0;
foreach (SqlParameter p in sqlcmd.Parameters)
{
if (count == (sqlcmd.Parameters.Count - 1))
{
parameters += p.Value.ToString();
}
else
{
parameters += p.Value.ToString() + ", ";
}
count++;
}
parameters += ")";
//Returns the string recombined.
return strArray[0] + " VALUES " + parameters;
}
지금은 이 솔루션이 효과가 있습니다.아마도 그것은 누군가에게 쓸모가 있을 것이다.중복되는 점 양해 바랍니다.
Public Shared Function SqlString(ByVal cmd As SqlCommand) As String
Dim sbRetVal As New System.Text.StringBuilder()
For Each item As SqlParameter In cmd.Parameters
Select Case item.DbType
Case DbType.String
sbRetVal.AppendFormat("DECLARE {0} AS VARCHAR(255)", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value)
sbRetVal.AppendLine()
Case DbType.DateTime
sbRetVal.AppendFormat("DECLARE {0} AS DATETIME", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value)
sbRetVal.AppendLine()
Case DbType.Guid
sbRetVal.AppendFormat("DECLARE {0} AS UNIQUEIDENTIFIER", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value)
sbRetVal.AppendLine()
Case DbType.Int32
sbRetVal.AppendFormat("DECLARE {0} AS int", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = {1}", item.ParameterName, item.Value)
sbRetVal.AppendLine()
Case Else
Stop
End Select
Next
sbRetVal.AppendLine("")
sbRetVal.AppendLine(cmd.CommandText)
Return sbRetVal.ToString()
End Function
@pkExec 및 @Alok에서 설명한 바와 같이 100% Replace를 사용할 수 없습니다.이것은, DAL에서 사용하고 있는 솔루션으로서 RegExp 를 사용해 단어 전체를 조합해, 데이터 타입의 올바른 형식을 지정합니다.생성된 SQL은 MySQL Workbench(또는 SQLSMS 등)에서 직접 테스트할 수 있습니다.
(MySQLHelper를 교환해 주세요.EscapeString()은 사용되는 DBMS에 따라 기능합니다.)
Dim query As String = cmd.CommandText
query = query.Replace("SET", "SET" & vbNewLine)
query = query.Replace("WHERE", vbNewLine & "WHERE")
query = query.Replace("GROUP BY", vbNewLine & "GROUP BY")
query = query.Replace("ORDER BY", vbNewLine & "ORDER BY")
query = query.Replace("INNER JOIN", vbNewLine & "INNER JOIN")
query = query.Replace("LEFT JOIN", vbNewLine & "LEFT JOIN")
query = query.Replace("RIGHT JOIN", vbNewLine & "RIGHT JOIN")
If query.Contains("UNION ALL") Then
query = query.Replace("UNION ALL", vbNewLine & "UNION ALL" & vbNewLine)
ElseIf query.Contains("UNION DISTINCT") Then
query = query.Replace("UNION DISTINCT", vbNewLine & "UNION DISTINCT" & vbNewLine)
Else
query = query.Replace("UNION", vbNewLine & "UNION" & vbNewLine)
End If
For Each par In cmd.Parameters
If par.Value Is Nothing OrElse IsDBNull(par.Value) Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "NULL")
ElseIf TypeOf par.Value Is Date Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "'" & Format(par.Value, "yyyy-MM-dd HH:mm:ss") & "'")
ElseIf TypeOf par.Value Is TimeSpan Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "'" & par.Value.ToString & "'")
ElseIf TypeOf par.Value Is Double Or TypeOf par.Value Is Decimal Or TypeOf par.Value Is Single Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", Replace(par.Value.ToString, ",", "."))
ElseIf TypeOf par.Value Is Integer Or TypeOf par.Value Is UInteger Or TypeOf par.Value Is Long Or TypeOf par.Value Is ULong Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", par.Value.ToString)
Else
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "'" & MySqlHelper.EscapeString(CStr(par.Value)) & "'")
End If
Next
예:
SELECT * FROM order WHERE order_status = @order_status AND order_date = @order_date
생성:
SELECT * FROM order WHERE order_status = 'C' AND order_date = '2015-01-01 00:00:00'
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;
}
저장되지 않은 프로시저를 다룰 필요가 있기 때문에 다음 논리로 CommandAsSql 라이브러리(위의 @Flapper 답변 아래의 코멘트 참조)를 늘렸습니다.
private static void CommandAsSql_Text(this SqlCommand command, System.Text.StringBuilder sql)
{
string query = command.CommandText;
foreach (SqlParameter p in command.Parameters)
query = Regex.Replace(query, "\\B" + p.ParameterName + "\\b", p.ParameterValueForSQL()); //the first one is \B, the 2nd one is \b, since ParameterName starts with @ which is a non-word character in RegEx (see https://stackoverflow.com/a/2544661)
sql.AppendLine(query);
}
풀 요청은 https://github.com/jphellemons/CommandAsSql/pull/3/commits/527d696dc6055c5bcf858b9700b83dc863f04896에 있습니다.
Regex의 아이디어는 위의 @subbikk와 EvZ의 코멘트와 https://stackoverflow.com/a/2544661/903783의 "Update:" 섹션에서 "negative look-behind assertion"을 언급하고 있습니다.정규 표현 시작 시 단어 경계 검출에 \b 대신 \B를 사용하는 것은 p.parameterName이 항상 단어 문자가 아닌 "@"로 시작되기 때문입니다.
ParameterValueForSQL()은 단일 따옴표로 묶은 문자열 파라미터 값 등의 문제를 처리하기 위해 CommandAsSql 라이브러리에서 정의된 확장 메서드입니다.
명령 텍스트를 변환하는 경우:
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
이제 다음과 같이 비파라미터 명령어를 가져올 수 있습니다.
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)
결과는 파라미터 없이 "UPDATE someTable SET Value = 'myValue' WHERE ID = 1234"가 됩니다.
저장 프로시저를 디버깅하는 데 도움이 되는 확장 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..." 유형 할당을 추가해야 할 수 있습니다.
//For Oracle db in the sql text the parameters could be like :ID,:NAME etc.
//This is same as above Seyed Hossein Mirheydari, but slightly improved,
//this works with parameter names well.
//Further is straight simple one, you need to format for datatypes
//after query is generated.
private string GetFullOraQuery(OracleCommand cmd)
{
string query = cmd.CommandText;
foreach (OracleParameter p in cmd.Parameters)
{
query = query.Replace(":" + p.ParameterName, ((p.Value == null) ? "" : p.Value.ToString()));
}
return query;
}
라이너 1개:
string.Join(",", from SqlParameter p in cmd.Parameters select p.ToString())
parameter 명령에서 non parameter 명령으로 변경할 수 있습니다.
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
로.
Private sub Update( byval myID as Int32, byval myVal as String)
Using cmd As SqlCommand = Connection.CreateCommand
cmd.CommandText = "UPDATE someTable SET Value = '" & myVaL & "'" & _
" WHERE Id = " & myID
cmd.ExecuteNonQuery
End Using
End sub
언급URL : https://stackoverflow.com/questions/265192/get-the-generated-sql-statement-from-a-sqlcommand-object
반응형
'programing' 카테고리의 다른 글
| Visual Studio 기능에 대한 IntelliSense 코멘트를 받는 방법 (0) | 2023.04.07 |
|---|---|
| SQL Server:빠른 쿼리, 그러나 절차에서 느린 쿼리 (0) | 2023.04.07 |
| 를 사용하여 사람이 읽을 수 있는 파일사이즈(바이트 단위)를 얻으려면 어떻게 해야 하나요?인터넷? (0) | 2023.04.07 |
| ON [PRIMAY]는 무슨 뜻입니까? (0) | 2023.04.07 |
| SQL Server에서 데이터베이스 목록 가져오기 (0) | 2023.04.07 |