public void ReplaceParameterPlaceholders()
{
var sut = new CommandSanitizer();
var parameter = new CommandParameterMetadata
{
Name = "@SomeParameter",
Type = typeof(int).Name,
Value = 1234
};
var command = string.Format("SELECT * FROM Table WHERE Id = {0}", parameter.Name);
var result = sut.Process(command, new[] { parameter });
var expected = string.Format(CommandParameterParser.UnquotedFormat, parameter.Value, parameter.Name);
Assert.Contains(expected, result);
}
public void WrapStringsInQuotes()
{
var sut = new CommandSanitizer();
var parameter = new CommandParameterMetadata
{
Name = "@StringParameter",
Type = typeof(string).Name,
Value = "This is the parameter value."
};
var command = string.Format("SELECT * FROM Table WHERE StringColumn = {0}", parameter.Name);
var result = sut.Process(command, new[] { parameter });
var expected = string.Format(CommandParameterParser.QuotedFormat, parameter.Value, parameter.Name);
Assert.Contains(expected, result);
}
public void NotReplacePartialMatches()
{
var sut = new CommandSanitizer();
var parameters = Enumerable.Range(1, 30).Select(x => new CommandParameterMetadata
{
Name = "@Id" + x,
Type = typeof(int).Name,
Value = x
}).ToList();
var parameterList = string.Join(", ", parameters.Select(x => x.Name));
var command = string.Format("SELECT * FROM Table WHERE Id IN ({0})", parameterList);
var result = sut.Process(command, parameters);
foreach (var parameter in parameters)
{
var expected = string.Format(CommandParameterParser.UnquotedFormat, parameter.Value, parameter.Name);
Assert.Contains(expected, result);
}
}
public override object GetData(ITabContext context)
{
var sanitizer = new CommandSanitizer();
var messages = context.GetMessages<AdoMessage>().ToList();
var aggregator = new MessageAggregator(messages);
var queryMetadata = aggregator.Aggregate();
if (queryMetadata == null)
{
return null;
}
var connections = new List<object[]> { new object[] { "Commands per Connection", "Duration" } };
foreach (var connection in queryMetadata.Connections.Values)
{
if (connection.Commands.Count == 0 && connection.Transactions.Count == 0)
{
continue;
}
var commands = new List<object[]> { new object[] { "Transaction Start", "Ordinal", "Command", "Parameters", "Records", "Duration", "Offset", "Async", "Transaction End", "Errors" } };
var commandCount = 1;
foreach (var command in connection.Commands.Values)
{
// Transaction Start
List<object[]> headTransaction = null;
if (command.HeadTransaction != null)
{
headTransaction = new List<object[]> { new object[] { "\t▼ Transaction - Started", "Isolation Level - " + command.HeadTransaction.IsolationLevel } };
if (!command.HeadTransaction.Committed.HasValue)
{
headTransaction.Add(new object[] { string.Empty, "Transaction was never completed", "error" });
}
}
// Transaction Finish
List<object[]> tailTransaction = null;
if (command.TailTransaction != null)
{
tailTransaction = new List<object[]> { new object[] { "\t▲ Transaction - Finished", "Status - " + (command.TailTransaction.Committed.GetValueOrDefault() ? "Committed" : "Rollbacked") } };
}
// Parameters
List<object[]> parameters = null;
if (command.Parameters.Count > 0)
{
parameters = new List<object[]> { new object[] { "Name", "Value", "Type", "Size" } };
foreach (var parameter in command.Parameters)
{
parameters.Add(new[] { parameter.Name, parameter.Value, parameter.Type, parameter.Size });
}
}
else if (command.BatchParameters.Count > 0)
{
parameters = new List<object[]>(command.BatchParameters.Count + 1);
parameters.Add(new object[] { "#", "Name", "Value", "Type", "Size" });
for(int i = 0; i < command.BatchParameters.Count; i++)
{
foreach(var parameter in command.BatchParameters[i])
{
parameters.Add(new object[] { i, parameter.Name, parameter.Value, parameter.Type, parameter.Size });
}
}
}
// Exception
List<object[]> errors = null;
if (command.Exception != null)
{
var exception = command.Exception.GetBaseException();
var exceptionName = command.Exception != exception ? command.Exception.Message + ": " + exception.Message : exception.Message;
errors = new List<object[]> { new object[] { "Error", "Stack" }, new object[] { exceptionName, exception.StackTrace } };
}
// Commands
var records = command.RecordsAffected == null || command.RecordsAffected < 0 ? command.TotalRecords : command.RecordsAffected;
var status = errors != null ? "error" : (command.IsDuplicate ? "warn" : string.Empty);
commands.Add(new object[] { headTransaction, string.Format("{0}{1}", command.HasTransaction ? "\t\t\t" : string.Empty, commandCount++), sanitizer.Process(command.Command, command.Parameters), parameters, records, command.Duration, command.Offset, command.IsAsync, tailTransaction, errors, status });
}
connections.Add(new[] { commands, connection.Duration.HasValue ? (object)connection.Duration.Value : null });
}
if (connections.Count > 1)
{
SqlStatistics sqlStatistics = SqlStatisticsCalculator.Caluculate(queryMetadata);
return new Dictionary<string, object>
{
{ "SQL Statistics", new object[] { new { sqlStatistics.ConnectionCount, sqlStatistics.QueryCount, sqlStatistics.TransactionCount, sqlStatistics.QueryExecutionTime, sqlStatistics.ConnectionOpenTime } } },
{ "Queries", connections }
};
}
return null;
}
public override object GetData(ITabContext context)
{
var sanitizer = new CommandSanitizer();
var messages = context.GetMessages<AdoMessage>().ToList();
var aggregator = new MessageAggregator(messages);
var queryMetadata = aggregator.Aggregate();
if (queryMetadata == null)
return null;
var connections = new List<object[]> { new[] { "Commands per Connection", "Duration" } };
foreach (var connection in queryMetadata.Connections.Values)
{
if (connection.Commands.Count == 0 && connection.Transactions.Count == 0)
continue;
var commands = new List<object[]> { new[] { "Transaction Start", "Ordinal", "Command", "Parameters", "Records", "Duration", "Offset", "Transaction End", "Errors" } };
var commandCount = 1;
foreach (var command in connection.Commands.Values)
{
//Transaction Start
List<object[]> headTransaction = null;
if (command.HeadTransaction != null)
{
headTransaction = new List<object[]> { new[] { "\t▼ Transaction - Started", "Isolation Level - " + command.HeadTransaction.IsolationLevel } };
if (!command.HeadTransaction.Committed.HasValue)
{
headTransaction.Add(new[] { "", "Transaction was never completed", "error" });
}
}
//Transaction Finish
List<object[]> tailTransaction = null;
if (command.TailTransaction != null)
{
tailTransaction = new List<object[]> { new[] { "\t▲ Transaction - Finished", "Status - " + (command.TailTransaction.Committed.GetValueOrDefault() ? "Committed" : "Rollbacked") } };
}
//Parameters
List<object[]> parameters = null;
if (command.Parameters.Count > 0)
{
parameters = new List<object[]> { new[] { "Name", "Value", "Type", "Size" } };
foreach (var parameter in command.Parameters)
{
parameters.Add(new[] { parameter.Name, parameter.Value, parameter.Type, parameter.Size });
}
}
//Exception
List<object[]> errors = null;
if (command.Exception != null)
{
var exception = command.Exception.GetBaseException();
var exceptionName = command.Exception != exception ? command.Exception.Message + ": " + exception.Message : exception.Message;
errors = new List<object[]> { new[] { "Error", "Stack" }, new[] { exceptionName, exception.StackTrace } };
}
//Commands
var records = command.RecordsAffected == null || command.RecordsAffected < 0 ? command.TotalRecords : command.RecordsAffected;
commands.Add(new object[] { headTransaction, string.Format("{0}{1}", command.HasTransaction ? "\t\t\t" : "", commandCount++), sanitizer.Process(command.Command, command.Parameters), parameters, records, command.Duration, command.Offset, tailTransaction, errors, errors != null ? "error" : "" });
}
connections.Add(new [] { commands, connection.Duration.HasValue ? (object)connection.Duration.Value : null });
}
return connections.Count > 1 ? connections : null;
}