public static string GetSqlSelect(TraceColumns columns)
{
List <TraceColumns> list = ToArray(columns);
StringBuilder ret = new StringBuilder();
foreach (TraceColumns field in list)
{
TraceFieldInfo info = Get(field);
ret.Append(ret.Length > 0 ? ", " : "").Append(info.Select);
}
return(ret.ToString());
}
public TraceGroupsReport <TKey> ReadGroupsReport <TKey>(TraceColumns groupingField)
{
if ((_columns & groupingField) == 0)
{
throw new ArgumentException("Axis field " + groupingField + " is not included in trace", "groupingField");
}
TraceFieldInfo info = TraceFieldInfo.Get(groupingField);
string sql = string.Format(
SQL_SELECT_GROUPS,
info.GroupExpression);
using (SqlConnection con = new SqlConnection(_connectionString))
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.Add("@file", SqlDbType.NVarChar).Value = _traceFile + ".trc";
using (SqlDataReader rdr = cmd.ExecuteReader())
{
TraceGroupsReport <TKey> ret = new TraceGroupsReport <TKey>();
while (rdr.Read())
{
object rawKey = rdr.IsDBNull(0) ? null : rdr.GetValue(0);
int count = rdr.GetInt32(1);
SqlCounters counters = ReadCounters(rdr, 2);
if (counters != null)
{
counters.Requests = count;
TKey key = (TKey)rawKey;
SqlGroupCounters <TKey> group =
new SqlGroupCounters <TKey>()
{
Group = key, Count = count, Counters = counters
};
ret.Add(key, group);
}
}
return(ret);
}
}
}
}
public static List <TraceColumns> ToArray(TraceColumns columns)
{
List <TraceColumns> ret = new List <TraceColumns>();
foreach (TraceColumns item in allColumns)
{
if (0 != (item & columns))
{
ret.Add(item);
}
}
return(ret);
}
public static TraceFieldInfo Get(TraceColumns field)
{
if (field == TraceColumns.Application)
{
return(new TraceFieldInfo(10, "ApplicationName"));
}
if (field == TraceColumns.ClientHost)
{
return(new TraceFieldInfo(8, "HostName"));
}
if (field == TraceColumns.ClientProcess)
{
return(new TraceFieldInfo(9, "ClientProcessID"));
}
if (field == TraceColumns.Database)
{
return(new TraceFieldInfo(35, "DatabaseName"));
}
if (field == TraceColumns.Login)
{
return(new TraceFieldInfo(11, "LoginName"));
}
if (field == TraceColumns.ServerProcess)
{
return(new TraceFieldInfo(12, "SPID"));
}
if (field == TraceColumns.Sql)
{
return(new TraceFieldInfo(
1,
"CASE WHEN EventClass = 10 THEN ObjectName ELSE NULL END, TextData",
"Cast((CASE WHEN EventClass = 10 THEN ObjectName ELSE TextData END) as NVARCHAR(MAX))"));
}
throw new ArgumentException(
"Unknown TraceColumn " + field,
"field");
}
public void Start(string connectionString, string tracePath, TraceColumns columns, params TraceRowFilter[] rowFilters)
{
_connectionString = connectionString;
_traceFile = Path.Combine(tracePath, "trace_" + Guid.NewGuid().ToString("N"));
_columns = columns;
// For non-local sql server it doesn't work
if (_PrevCreatedDirectory != tracePath)
{
if (!Directory.Exists(tracePath))
{
Directory.CreateDirectory(Path.GetDirectoryName(_traceFile));
}
_PrevCreatedDirectory = tracePath;
}
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
StringBuilder sqlSetFields = new StringBuilder();
foreach (TraceColumns field in TraceFieldInfo.ToArray(columns))
{
TraceFieldInfo info = TraceFieldInfo.Get(field);
sqlSetFields.AppendFormat(SQL_SET_TRACE_COLUMN, info.SqlId, field);
}
List <SqlParameter> parameters = new List <SqlParameter>();
List <TraceColumns> rowFilterColumns = new List <TraceColumns>();
TraceRowFilter[] distinctRowFilter = TraceRowFilter.GetDistinct(rowFilters);
foreach (TraceRowFilter rowFilter in distinctRowFilter)
{
TraceFieldInfo info = TraceFieldInfo.Get(rowFilter.Column);
string pName = "@filter_" + info.GroupExpression + "_" + parameters.Count;
string comparisonOperator = rowFilter.StrictEquality ? "0" : "6";
object comparisonValue = rowFilter.Value;
if (!rowFilter.StrictEquality && comparisonValue != null)
{
comparisonValue = "%" + Convert.ToString(comparisonValue).Replace("'", "''") + "%";
}
sqlSetFields.AppendFormat("exec sp_trace_setfilter @TRACE, {0}, 0, {1}, {2} -- {3}",
info.SqlId,
comparisonOperator,
pName,
info.GroupExpression);
sqlSetFields.AppendLine();
SqlParameter p = new SqlParameter(pName, comparisonValue);
parameters.Add(p);
if (rowFilterColumns.Contains(rowFilter.Column))
{
throw new ArgumentException("Duplicate row filter column " + rowFilter.Column, "rowFilters");
}
rowFilterColumns.Add(rowFilter.Column);
}
string sqlCmd = SQL_START1_TRACE + sqlSetFields + SQL_START2_TRACE;
// Console.WriteLine($"TRACE ON {connectionString}");
using (SqlCommand cmd = new SqlCommand(sqlCmd, con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@file", SqlDbType.NVarChar).Value = _traceFile;
var maxFileSize = Math.Max(8, MaxFileSize);
cmd.Parameters.Add("@MaxFileSize", SqlDbType.BigInt).Value = maxFileSize;
cmd.Parameters.AddRange(parameters.ToArray());
_traceId = (int)cmd.ExecuteScalar();
// PInvoke.DeleteFileOnReboot(_traceFile + ".trc");
}
}
_NeedStop = true;
}
public TraceRowFilter(TraceColumns column, object value, bool strictEquality)
{
_column = column;
_value = value;
_strictEquality = strictEquality;
}
public TraceRowFilter(TraceColumns column, object value)
{
_column = column;
_value = value;
}
public TraceDetailsReport(TraceColumns includedColumns, IList <SqlStatementCounters> list) : base(list)
{
IncludedColumns = includedColumns;
}
