private void LogCommandAsError(Exception exception, ExecuteType type)
{
var formatter = new SqlServerFormatter();
SqlTiming timing = new SqlTiming(this, type, null);
exception.Data["SQL"] = formatter.FormatSql(timing);
}
public List<SqlTiming> GetSqlTimings(string transactionId, string timingId)
{
List<SqlTiming> result = new List<SqlTiming>();
var profilerDataContext = new ProfilerDbDataContext();
var sqlResults = profilerDataContext.GetSqlTimings(Guid.Parse(timingId), Guid.Parse(transactionId));
foreach (var sqlResult in sqlResults)
{
var preservedSqlResult = result.Find(x => x.Id == sqlResult.SqlTimingId.ToString());
if ((preservedSqlResult == null) == true)
{
preservedSqlResult = new SqlTiming();
preservedSqlResult.CommandString = sqlResult.CommandString ?? UNKNOWN;
preservedSqlResult.DurationMilliseconds = sqlResult.DurationMilliseconds.GetValueOrDefault();
preservedSqlResult.ExecuteType = sqlResult.ExecuteType ?? UNKNOWN;
preservedSqlResult.FirstFetchDurationMilliseconds = sqlResult.FirstFetchDurationMilliseconds.GetValueOrDefault();
preservedSqlResult.Id = sqlResult.SqlTimingId.ToString();
preservedSqlResult.Order = sqlResult.ProfiledOrder.GetValueOrDefault();
preservedSqlResult.StartMilliseconds = sqlResult.StartMilliseconds.GetValueOrDefault();
preservedSqlResult.TimingId = sqlResult.TimingId.ToString();
result.Add(preservedSqlResult);
}
BuildSqlParameter(sqlResult, preservedSqlResult);
}
return result;
}
public void OnError(IDbCommand profiledDbCommand, SqlExecuteType executeType, Exception e)
{
var formatter = new Profiling.SqlFormatters.SqlServerFormatter();
var parameters = SqlTiming.GetCommandParameters(profiledDbCommand);
e.Data["SQL"] = formatter.FormatSql(profiledDbCommand.CommandText, parameters);
}
/// <summary>
/// Formats the SQL in a SQL-Server friendly way, with DECLARE statements for the parameters up top.
/// </summary>
/// <param name="timing">The <c>SqlTiming</c> to format</param>
/// <returns>A formatted SQL string</returns>
public string FormatSql(SqlTiming timing)
{
if (timing.Parameters == null || timing.Parameters.Count == 0)
{
return timing.CommandString;
}
var buffer = new StringBuilder();
foreach (var p in timing.Parameters)
{
string paramFormat = "DECLARE {0} {1} = {2};";
DbType parsed;
string resolvedType = null;
if (!Enum.TryParse(p.DbType, out parsed))
{
resolvedType = p.DbType;
}
string val = p.Value;
if (resolvedType == null)
{
Func<SqlTimingParameter, string> translator;
if (ParamTranslator.TryGetValue(parsed, out translator))
{
resolvedType = translator(p);
}
resolvedType = resolvedType ?? p.DbType;
val = PrepareValue(p);
}
else
{
paramFormat = "DECLARE {0} {1};";
val = GetJsonVal(p);
if (val != null && val.Length > 0)
{
paramFormat += "\nINSERT INTO {0} VALUES {2};";
}
}
var niceName = p.Name;
if (!niceName.StartsWith("@"))
{
niceName = "@" + niceName;
}
buffer.AppendFormat(paramFormat, niceName, resolvedType, val);
buffer.AppendLine();
}
return buffer
.AppendLine()
.AppendLine()
.Append(timing.CommandString)
.ToString();
}
/// <summary>
/// Formats the SQL in a SQL-Server friendly way, with DECLARE statements for the parameters up top.
/// </summary>
/// <param name="timing">The <c>SqlTiming</c> to format</param>
/// <returns>A formatted SQL string</returns>
public string FormatSql(SqlTiming timing)
{
if (timing.Parameters == null || timing.Parameters.Count == 0)
{
return timing.CommandString;
}
var buffer = new StringBuilder("DECLARE ");
var first = true;
foreach (var p in timing.Parameters)
{
if (first)
{
first = false;
}
else
{
buffer.AppendLine(",").Append(new string(' ', 8));
}
DbType parsed;
string resolvedType = null;
if (!ExtensionMethods.TryParse(p.DbType, out parsed))
try
{
parsed = (DbType)Enum.Parse(typeof(DbType), p.DbType);
}
catch (Exception)
{
resolvedType = p.DbType;
}
if (resolvedType == null)
{
Func<SqlTimingParameter, string> translator;
if (ParamTranslator.TryGetValue(parsed, out translator))
{
resolvedType = translator(p);
}
resolvedType = resolvedType ?? p.DbType;
}
var niceName = p.Name;
if (!niceName.StartsWith("@"))
{
niceName = "@" + niceName;
}
buffer.Append(niceName).Append(" ").Append(resolvedType).Append(" = ").Append(PrepareValue(p));
}
return buffer
.Append(";")
.AppendLine()
.AppendLine()
.Append(timing.CommandString)
.ToString();
}
/// <summary>
/// Formats the SQL in a SQL-Server friendly way, with DECLARE statements for the parameters up top.
/// </summary>
/// <param name="timing">The SqlTiming to format</param>
/// <returns>A formatted SQL string</returns>
public string FormatSql(SqlTiming timing)
{
if (timing.Parameters == null || timing.Parameters.Count == 0)
{
return(timing.CommandString);
}
StringBuilder buffer = new StringBuilder();
buffer.Append("DECLARE ");
bool first = true;
foreach (var p in timing.Parameters)
{
if (first)
{
first = false;
}
else
{
buffer.AppendLine(",").Append(new string(' ', 8));
}
DbType parsed;
string resolvedType = null;
if (!EnumTryParse <DbType>(p.DbType, false, out parsed))
{
resolvedType = p.DbType;
}
if (resolvedType == null)
{
Func <SqlTimingParameter, string> translator;
if (paramTranslator.TryGetValue(parsed, out translator))
{
resolvedType = translator(p);
}
resolvedType = resolvedType ?? p.DbType;
}
var niceName = p.Name;
if (!niceName.StartsWith("@"))
{
niceName = "@" + niceName;
}
buffer.Append(niceName).Append(" ").Append(resolvedType).Append(" = ").Append(PrepareValue(p));
}
return(buffer
.AppendLine()
.AppendLine()
.Append(timing.CommandString)
.ToString());
}
public string FormatSql(SqlTiming timing)
{
var sqlFormatter = new SqlServerFormatter();
var sqlFormat = sqlFormatter.FormatSql(timing);
var poorMansFormatter = new TSqlStandardFormatter();
var fullFormatter = new SqlFormattingManager(poorMansFormatter);
return(fullFormatter.Format(sqlFormat));
}
/// <summary>
/// Does NOTHING, implement me!
/// </summary>
public string FormatSql(SqlTiming timing)
{
// It would be nice to have an oracle formatter, if anyone feel up to the challange a patch would be awesome
throw new NotImplementedException();
if (timing.Parameters == null || timing.Parameters.Count == 0)
{
return(timing.CommandString);
}
StringBuilder buffer = new StringBuilder();
buffer.Append("DECLARE ");
bool first = true;
foreach (var p in timing.Parameters)
{
if (first)
{
first = false;
}
else
{
buffer.AppendLine(",").Append(new string(' ', 8));
}
DbType parsed;
string resolvedType = null;
if (!Enum.TryParse <DbType>(p.DbType, out parsed))
{
resolvedType = p.DbType;
}
if (resolvedType == null)
{
Func <SqlTimingParameter, string> translator;
if (paramTranslator.TryGetValue(parsed, out translator))
{
resolvedType = translator(p);
}
resolvedType = resolvedType ?? p.DbType;
}
var niceName = p.Name;
buffer.Append(niceName).Append(" ").Append(resolvedType).Append(" = ").Append(PrepareValue(p));
}
return(buffer
.AppendLine()
.AppendLine()
.Append(timing.CommandString)
.ToString());
}
/// <summary>
/// Does NOTHING, implement me!
/// </summary>
public string FormatSql(SqlTiming timing)
{
// It would be nice to have an oracle formatter, if anyone feel up to the challange a patch would be awesome
throw new NotImplementedException();
if (timing.Parameters == null || timing.Parameters.Count == 0)
{
return timing.CommandString;
}
StringBuilder buffer = new StringBuilder();
buffer.Append("DECLARE ");
bool first = true;
foreach (var p in timing.Parameters)
{
if (first)
{
first = false;
}
else
{
buffer.AppendLine(",").Append(new string(' ', 8));
}
DbType parsed;
string resolvedType = null;
if (!Enum.TryParse<DbType>(p.DbType, out parsed))
{
resolvedType = p.DbType;
}
if (resolvedType == null)
{
Func<SqlTimingParameter, string> translator;
if (paramTranslator.TryGetValue(parsed, out translator))
{
resolvedType = translator(p);
}
resolvedType = resolvedType ?? p.DbType;
}
var niceName = p.Name;
buffer.Append(niceName).Append(" ").Append(resolvedType).Append(" = ").Append(PrepareValue(p));
}
return buffer
.AppendLine()
.AppendLine()
.Append(timing.CommandString)
.ToString();
}
/// <summary>
/// Saves parameter Timing to the sqltimingparams collection.
/// </summary>
private void SaveSqlTimingParameters(MiniProfiler profiler, SqlTiming s)
{
foreach (var p in s.Parameters)
{
var sqltimingParamPoco = new SqlTimingParameterPoco
{
MiniProfilerId = profiler.Id.ToString(),
ParentSqlTimingId = s.Id.ToString(),
Name = Truncate(p.Name, 150),
DbType = Truncate(p.DbType, 50),
Size = p.Size,
Value = p.Value
};
SqlTimingParams.Insert(sqltimingParamPoco);
}
}
/// <summary>
/// Saves parameter SqlTiming to the dbo.MiniProfilerSqlTimings table.
/// </summary>
protected virtual void SaveSqlTiming(DbConnection conn, Profiler profiler, SqlTiming st)
{
const string sql =
@"insert into MiniProfilerSqlTimings
(Id,
MiniProfilerId,
ParentTimingId,
ExecuteType,
StartMilliseconds,
DurationMilliseconds,
FirstFetchDurationMilliseconds,
IsDuplicate,
StackTraceSnippet,
CommandString)
values (@Id,
@MiniProfilerId,
@ParentTimingId,
@ExecuteType,
@StartMilliseconds,
@DurationMilliseconds,
@FirstFetchDurationMilliseconds,
@IsDuplicate,
@StackTraceSnippet,
@CommandString)";
conn.Execute(sql, new
{
Id = st.Id,
MiniProfilerId = profiler.Id,
ParentTimingId = st.ParentTiming.Id,
ExecuteType = st.ExecuteType,
StartMilliseconds = st.StartMilliseconds,
DurationMilliseconds = st.DurationMilliseconds,
FirstFetchDurationMilliseconds = st.FirstFetchDurationMilliseconds,
IsDuplicate = st.IsDuplicate,
StackTraceSnippet = st.StackTraceSnippet.Truncate(200),
CommandString = st.CommandString
});
if (st.Parameters != null && st.Parameters.Count > 0)
{
SaveSqlTimingParameters(conn, profiler, st);
}
}
/// <summary>
/// Formats the SQL in a generic frieldly format, including the parameter type information in a comment if it was specified in the InlineFormatter constructor
/// </summary>
/// <param name="timing">The SqlTiming to format</param>
/// <returns>A formatted SQL string</returns>
public string FormatSql(SqlTiming timing)
{
var sql = timing.CommandString;
if (timing.Parameters == null || timing.Parameters.Count == 0)
{
return sql;
}
foreach(var p in timing.Parameters)
{
// If the parameter doesn't have a prefix (@,:,etc), append one
var name = _paramPrefixes.IsMatch(p.Name) ? p.Name : Regex.Match(sql, "([@:?])" + p.Name).Value;
var value = GetParameterValue(p);
sql = Regex.Replace(sql, name, m => value, RegexOptions.IgnoreCase);
}
return sql;
}
/// <summary>
/// Formats the SQL in a generic frieldly format, including the parameter type information in a comment if it was specified in the InlineFormatter constructor
/// </summary>
/// <param name="timing">The SqlTiming to format</param>
/// <returns>A formatted SQL string</returns>
public string FormatSql(SqlTiming timing)
{
var sql = timing.CommandString;
if (timing.Parameters == null || timing.Parameters.Count == 0)
{
return(sql);
}
foreach (var p in timing.Parameters)
{
// If the parameter doesn't have a prefix (@,:,etc), append one
var name = _paramPrefixes.IsMatch(p.Name) ? p.Name : Regex.Match(sql, "([@:?])" + p.Name).Value;
var value = GetParameterValue(p);
sql = Regex.Replace(sql, name, m => value, RegexOptions.IgnoreCase);
}
return(sql);
}
/// <summary>
/// Saves parameter Timing to the sqltimings collection.
/// </summary>
private void SaveSqlTiming(MiniProfiler profiler, SqlTiming s)
{
var sqlTimingPoco = new SqlTimingPoco
{
Id = s.Id.ToString(),
MiniProfilerId = profiler.Id.ToString(),
ParentTimingId = s.ParentTiming.Id.ToString(),
ExecuteType = s.ExecuteType,
StartMilliseconds = (double)s.StartMilliseconds,
DurationMilliseconds = (double)s.DurationMilliseconds,
FirstFetchDurationMilliseconds = (double)s.FirstFetchDurationMilliseconds,
IsDuplicate = s.IsDuplicate,
StackTraceSnippet = Truncate(s.StackTraceSnippet, 200),
CommandString = s.CommandString
};
SqlTimings.Insert(sqlTimingPoco);
if (s.Parameters != null && s.Parameters.Count > 0)
{
SaveSqlTimingParameters(profiler, s);
}
}
/// <summary>
/// Formats the SQL in a SQL-Server friendly way, with DECLARE statements for the parameters up top.
/// </summary>
/// <param name="timing">The SqlTiming to format</param>
/// <returns>A formatted SQL string</returns>
public string FormatSql(SqlTiming timing)
{
if (timing.Parameters == null || timing.Parameters.Count == 0)
{
return(timing.CommandString);
}
StringBuilder buffer = StringBuilderCache.Allocate();
buffer.Append("DECLARE ");
bool first = true;
foreach (var p in timing.Parameters)
{
if (first)
{
first = false;
}
else
{
buffer.AppendLine(",").Append(new string(' ', 8));
}
var parsed = DbType.AnsiString;
string resolvedType = null;
var validEnum = false;
try
{
parsed = p.DbType.ToEnum <DbType>();
validEnum = true;
}
catch {}
if (!validEnum)
{
resolvedType = p.DbType;
}
if (resolvedType == null)
{
Func <SqlTimingParameter, string> translator;
if (paramTranslator.TryGetValue(parsed, out translator))
{
resolvedType = translator(p);
}
resolvedType = resolvedType ?? p.DbType;
}
var niceName = p.Name;
if (!niceName.StartsWith("@"))
{
niceName = "@" + niceName;
}
buffer.Append(niceName).Append(" ").Append(resolvedType).Append(" = ").Append(PrepareValue(p));
}
return(StringBuilderCache.ReturnAndFree(buffer
.AppendLine()
.AppendLine()
.Append(timing.CommandString)));
}
/// <summary>
/// The assert SQL parameters exist.
/// </summary>
/// <param name="sqlTiming">The SQL Timing.</param>
/// <param name="count">The count.</param>
private void AssertSqlParametersExist(SqlTiming sqlTiming, int count)
{
Assert.That(_conn.Query<int>("select count(*) from MiniProfilerSqlTimingParameters where ParentSqlTimingId = @Id ", new { sqlTiming.Id }).Single() == count);
}
private string GenerateOutput()
{
var sqlParameters = SqlTiming.GetCommandParameters(_dbCommand);
return(_formatter.GetFormattedSql(_commandText, sqlParameters, _dbCommand));
}
/// <summary>
/// Saves parameter SqlTiming to the dbo.MiniProfilerSqlTimings table.
/// </summary>
protected virtual void SaveSqlTiming(DbConnection conn, MiniProfiler profiler, SqlTiming st)
{
const string sql =
@"insert into MiniProfilerSqlTimings
(Id,
MiniProfilerId,
ParentTimingId,
ExecuteType,
StartMilliseconds,
DurationMilliseconds,
FirstFetchDurationMilliseconds,
IsDuplicate,
StackTraceSnippet,
CommandString)
values (@Id,
@MiniProfilerId,
@ParentTimingId,
@ExecuteType,
@StartMilliseconds,
@DurationMilliseconds,
@FirstFetchDurationMilliseconds,
@IsDuplicate,
@StackTraceSnippet,
@CommandString)";
conn.Execute(sql, new
{
Id = st.Id,
MiniProfilerId = profiler.Id,
ParentTimingId = st.ParentTiming.Id,
ExecuteType = st.ExecuteType,
StartMilliseconds = st.StartMilliseconds,
DurationMilliseconds = st.DurationMilliseconds,
FirstFetchDurationMilliseconds = st.FirstFetchDurationMilliseconds,
IsDuplicate = st.IsDuplicate,
StackTraceSnippet = st.StackTraceSnippet.Truncate(200),
CommandString = st.CommandString
});
if (st.Parameters != null && st.Parameters.Count > 0)
{
SaveSqlTimingParameters(conn, profiler, st);
}
}
/// <summary>
/// Saves parameter Timing to the sqltimings collection.
/// </summary>
private void SaveSqlTiming(MiniProfiler profiler, SqlTiming s)
{
var sqlTimingPoco = new SqlTimingPoco
{
Id = s.Id.ToString(),
MiniProfilerId = profiler.Id.ToString(),
ParentTimingId = s.ParentTiming.Id.ToString(),
ExecuteType = s.ExecuteType,
StartMilliseconds = (double)s.StartMilliseconds,
DurationMilliseconds = (double)s.DurationMilliseconds,
FirstFetchDurationMilliseconds = (double)s.FirstFetchDurationMilliseconds,
IsDuplicate = s.IsDuplicate,
StackTraceSnippet = Truncate(s.StackTraceSnippet, 200),
CommandString = s.CommandString
};
SqlTimings.Insert(sqlTimingPoco);
if (s.Parameters != null && s.Parameters.Count > 0)
{
SaveSqlTimingParameters(profiler, s);
}
}
public void OnError(IDbCommand profiledDbCommand, SqlExecuteType executeType, Exception exception)
{
var formatter = new SqlServerFormatter();
exception.Data["SQL"] = formatter.FormatSql(profiledDbCommand.CommandText, SqlTiming.GetCommandParameters(profiledDbCommand));
_wrapped.OnError(profiledDbCommand, executeType, exception);
}
private static void BuildSqlParameter(spGetSqlTimingsResult sqlResult, SqlTiming preservedSqlResult)
{
var sqlParamter = new SqlTimingParameter();
sqlParamter.DbType = sqlResult.DbType ?? UNKNOWN;
sqlParamter.Name = sqlResult.Name ?? UNKNOWN;
sqlParamter.Size = sqlResult.Size.GetValueOrDefault();
sqlParamter.SqlTimingId = preservedSqlResult.Id;
sqlParamter.Value = sqlResult.Value ?? UNKNOWN;
preservedSqlResult.Parameters.Add(sqlParamter);
}
/// <summary>
/// Does NOTHING, implement me!
/// </summary>
public string FormatSql(SqlTiming timing)
{
// It would be nice to have an oracle formatter, if anyone feel up to the challange a patch would be awesome
throw new NotImplementedException();
}
/// <summary>
/// Saves any SqlTimingParameters used in the profiled SqlTiming to the dbo.MiniProfilerSqlTimingParameters table.
/// </summary>
protected virtual void SaveSqlTimingParameters(DbConnection conn, MiniProfiler profiler, SqlTiming st)
{
const string sql =
@"insert into MiniProfilerSqlTimingParameters
(MiniProfilerId,
ParentSqlTimingId,
Name,
DbType,
Size,
Value)
values (@MiniProfilerId,
@ParentSqlTimingId,
@Name,
@DbType,
@Size,
@Value)";
foreach (var p in st.Parameters)
{
conn.Execute(sql, new
{
MiniProfilerId = profiler.Id,
ParentSqlTimingId = st.Id,
Name = p.Name.Truncate(130),
DbType = p.DbType.Truncate(50),
Size = p.Size,
Value = p.Value
});
}
}
/// <summary>
/// Saves any SqlTimingParameters used in the profiled SqlTiming to the dbo.MiniProfilerSqlTimingParameters table.
/// </summary>
protected virtual void SaveSqlTimingParameters(DbConnection conn, Profiler profiler, SqlTiming st)
{
const string sql =
@"insert into MiniProfilerSqlTimingParameters
(MiniProfilerId,
ParentSqlTimingId,
Name,
DbType,
Size,
Value)
values (@MiniProfilerId,
@ParentSqlTimingId,
@Name,
@DbType,
@Size,
@Value)";
foreach (var p in st.Parameters)
{
conn.Execute(sql, new
{
MiniProfilerId = profiler.Id,
ParentSqlTimingId = st.Id,
Name = p.Name.Truncate(130),
DbType = p.DbType.Truncate(50),
Size = p.Size,
Value = p.Value
});
}
}
/// <summary>
/// Does NOTHING, implement me!
/// </summary>
public string FormatSql(SqlTiming timing)
{
// It would be nice to have an oracle formatter, if anyone feel up to the challange a patch would be awesome
throw new NotImplementedException();
}
private void AssertSqlParametersExist(SqlTiming s, int count)
{
Assert.That(_conn.Query <int>("select count(*) from MiniProfilerSqlTimingParameters where ParentSqlTimingId = @Id ", new { s.Id }).Single() == count);
}
/// <summary>
/// Saves parameter Timing to the sqltimingparams collection.
/// </summary>
private void SaveSqlTimingParameters(MiniProfiler profiler, SqlTiming s)
{
foreach (var p in s.Parameters)
{
var sqltimingParamPoco = new SqlTimingParameterPoco
{
MiniProfilerId = profiler.Id.ToString(),
ParentSqlTimingId = s.Id.ToString(),
Name = Truncate(p.Name, 150),
DbType = Truncate(p.DbType, 50),
Size = p.Size,
Value = p.Value
};
SqlTimingParams.Insert(sqltimingParamPoco);
}
}
/// <summary>
/// Formats the SQL in a SQL-Server friendly way, with DECLARE statements for the parameters up top.
/// </summary>
/// <param name="timing">The SqlTiming to format</param>
/// <returns>A formatted SQL string</returns>
public string FormatSql(SqlTiming timing)
{
if (timing.Parameters == null || timing.Parameters.Count == 0)
{
return timing.CommandString;
}
StringBuilder buffer = new StringBuilder();
buffer.Append("DECLARE ");
bool first = true;
foreach (var p in timing.Parameters)
{
if (first)
{
first = false;
}
else
{
buffer.AppendLine(",").Append(new string(' ', 8));
}
var parsed = DbType.AnsiString;
string resolvedType = null;
var validEnum = false;
try
{
parsed = p.DbType.ToEnum<DbType>();
validEnum = true;
}
catch {}
if (!validEnum)
{
resolvedType = p.DbType;
}
if (resolvedType == null)
{
Func<SqlTimingParameter, string> translator;
if (paramTranslator.TryGetValue(parsed, out translator))
{
resolvedType = translator(p);
}
resolvedType = resolvedType ?? p.DbType;
}
var niceName = p.Name;
if (!niceName.StartsWith("@"))
{
niceName = "@" + niceName;
}
buffer.Append(niceName).Append(" ").Append(resolvedType).Append(" = ").Append(PrepareValue(p));
}
return buffer
.AppendLine()
.AppendLine()
.Append(timing.CommandString)
.ToString();
}
