private void AddBulkInsertData(BulkInsert bulkInsert, SqlConnection con)
{
using (DataTable dataTable = GetBulkInsertDataFromTable(bulkInsert, con))
{
for (var rowIndex = 0; rowIndex < bulkInsert.RowCount; rowIndex++)
{
var row = new List <object>();
for (var columnIndex = 0; columnIndex < bulkInsert.Columns.Count; columnIndex++)
{
row.Add(dataTable.Rows[rowIndex][columnIndex]);
}
bulkInsert.Rows.Add(row);
}
}
}
private DataTable GetBulkInsertDataFromTable(BulkInsert bulkInsert, SqlConnection con)
{
var dataTable = new DataTable();
var cmdText = $" select top(@count) {string.Join(',', bulkInsert.Columns.Select(c => c.Name).ToArray())} from {bulkInsert.Table}";
using (var cmd = new SqlCommand(cmdText, con))
{
cmd.Parameters.Add(new SqlParameter("@count", SqlDbType.Int)
{
Value = bulkInsert.RowCount
});
using (var dataAdapter = new SqlDataAdapter(cmd))
{
dataAdapter.Fill(dataTable);
}
}
return(dataTable);
}
internal async Task <Run> PreProcess(string[] fileNames, string connectionString, DateTimeOffset?cutoff)
{
var sessions = new ConcurrentDictionary <string, Session>();
using (var con = new SqlConnection(connectionString))
{
await con.OpenAsync();
foreach (string fileName in fileNames)
{
var xeStream = new XEFileEventStreamer(fileName);
await xeStream.ReadEventStream(xevent =>
{
if (xevent.Actions["database_name"].ToString() != con.Database)
{
return(Task.CompletedTask);
}
if (cutoff != null && xevent.Timestamp > cutoff)
{
return(Task.CompletedTask);
}
Event evt = null;
if (xevent.Name == "rpc_starting" &&
xevent.Fields["object_name"].ToString() != "sp_reset_connection" &&
!xevent.Fields["statement"].ToString().StartsWith("exec sp_unprepare "))
{
evt = new Rpc()
{
EventSequence = xevent.Actions["event_sequence"].ToString(),
TransactionId = xevent.Actions["transaction_id"].ToString(),
Statement = xevent.Fields["statement"].ToString(),
ObjectName = xevent.Fields["object_name"].ToString(),
Timestamp = xevent.Timestamp
};
//Build parameters so we can replay statements as ADO.NET CommandType.StoredProcedure calls in order to avoid extra compilations of raw statement
LoadParameters(con, (Rpc)evt);
}
else if (xevent.Name == "sql_transaction")
{
if (xevent.Fields["transaction_type"].ToString() == "User")
{
evt = new Transaction()
{
EventSequence = xevent.Actions["event_sequence"].ToString(),
TransactionId = xevent.Fields["transaction_id"].ToString(),
TransactionState = xevent.Fields["transaction_state"].ToString(),
Timestamp = xevent.Timestamp
};
}
}
else if (xevent.Name == "sql_batch_starting" &&
xevent.Fields["batch_text"].ToString().Contains("insert bulk"))
{
var bulkInsert = new BulkInsert()
{
EventSequence = xevent.Actions["event_sequence"].ToString(),
TransactionId = xevent.Actions["transaction_id"].ToString(),
BatchText = xevent.Fields["batch_text"].ToString(),
Timestamp = xevent.Timestamp
};
bulkInsert.Table = bulkInsert.BatchText.Split(' ')[2];
string[] columns = bulkInsert.BatchText.GetParenthesesContent().Split(", ");
foreach (string col in columns)
{
string[] columnInfo = col.Split(' ');
bulkInsert.Columns.Add(new Column {
Name = columnInfo[0], DataType = columnInfo[1]
});
}
if (bulkInsert.BatchText.Contains(" with ("))
{
string[] settings = bulkInsert.BatchText
.GetParenthesesContent(bulkInsert.BatchText.IndexOf(" with (") + 6).Split(", ");
foreach (string setting in settings)
{
if (setting == "FIRE_TRIGGERS")
{
bulkInsert.FireTriggers = true;
break;
}
}
}
evt = bulkInsert;
}
else if (xevent.Name == "sql_batch_completed" &&
xevent.Fields["batch_text"].ToString().Contains("insert bulk"))
{
if (!sessions.TryGetValue(xevent.Actions["session_id"].ToString(), out var session))
{
throw new Exception(
$"Could not find session ID {xevent.Actions["session_id"].ToString()} for bulk insert.");
}
else
{
var bulkInsert = (BulkInsert)session.Events
.FirstOrDefault(e =>
(e as BulkInsert)?.TransactionId ==
xevent.Actions["transaction_id"].ToString() &&
(e as BulkInsert)?.BatchText == xevent.Fields["batch_text"].ToString());
if (bulkInsert != null)
{
bulkInsert.Rows = int.Parse(xevent.Fields["row_count"].ToString());
}
}
}
if (evt != null)
{
string sessionId = xevent.Actions["session_id"].ToString();
Session session = sessions.GetOrAdd(sessionId, new Session()
{
SessionId = sessionId
});
session.Events.Add(evt);
}
return(Task.CompletedTask);
}, CancellationToken.None);
}
}
foreach (Session session in sessions.Values)
{
session.Events = session.Events.OrderBy(e => e.EventSequence).ToList();
}
var run = new Run()
{
Sessions = sessions.Values.ToArray().Where(s => s.Events.Count > 0).OrderBy(s => s.Events.First().EventSequence).ToList()
};
run.EventCaptureOrigin = run.Sessions.First().Events.First().Timestamp;
return(run);
}
internal async Task <Run> PreProcessAsync(string[] filePaths, string connectionString)
{
var sessionDictionary = new ConcurrentDictionary <string, Session>();
using (var con = new SqlConnection(connectionString))
{
await con.OpenAsync();
foreach (string filePath in filePaths)
{
if (!Regex.IsMatch(Path.GetFileName(filePath), @"^\w+\.xel$", RegexOptions.IgnoreCase))
{
//ignore any files that don't fit the pattern of an XE file
continue;
}
var xeStream = new XEFileEventStreamer(filePath);
await xeStream.ReadEventStream(xevent =>
{
if (xevent.Actions["database_name"].ToString() != con.Database)
{
return(Task.CompletedTask);
}
Event evt = null;
if (xevent.Name == "rpc_starting" &&
xevent.Fields["object_name"].ToString() != "sp_reset_connection" &&
!xevent.Fields["statement"].ToString().StartsWith("exec sp_unprepare "))
{
evt = new Rpc()
{
EventSequence = long.Parse(xevent.Actions["event_sequence"].ToString()),
TransactionId = xevent.Actions["transaction_id"].ToString(),
Statement = xevent.Fields["statement"].ToString(),
ObjectName = xevent.Fields["object_name"].ToString(),
Timestamp = xevent.Timestamp
};
//Build parameters so we can replay statements as ADO.NET CommandType.StoredProcedure calls in order to avoid extra compilations of raw statement
LoadParameters(con, (Rpc)evt);
}
else if (xevent.Name == "sql_transaction")
{
if (xevent.Fields["transaction_type"].ToString() == "User")
{
evt = new Transaction()
{
EventSequence = long.Parse(xevent.Actions["event_sequence"].ToString()),
TransactionId = xevent.Fields["transaction_id"].ToString(),
TransactionState = xevent.Fields["transaction_state"].ToString(),
Timestamp = xevent.Timestamp
};
}
}
else if (xevent.Name == "sql_batch_starting" &&
xevent.Fields["batch_text"].ToString().Contains("insert bulk"))
{
var bulkInsert = new BulkInsert()
{
EventSequence = long.Parse(xevent.Actions["event_sequence"].ToString()),
TransactionId = xevent.Actions["transaction_id"].ToString(),
BatchText = xevent.Fields["batch_text"].ToString(),
Timestamp = xevent.Timestamp
};
bulkInsert.Table = bulkInsert.BatchText.Split(' ')[2];
if (bulkInsert.Table.Contains('('))
{
bulkInsert.Table = bulkInsert.Table.Substring(0, bulkInsert.Table.IndexOf('('));
}
string[] columns = bulkInsert.BatchText.GetParenthesesContent().Split(", ");
foreach (string col in columns)
{
string[] columnInfo = col.Split(' ');
bulkInsert.Columns.Add(new Column {
Name = columnInfo[0], SqlDbType = GetSqlDbType(columnInfo[1])
});
}
if (bulkInsert.BatchText.Contains(" with ("))
{
string[] settings = bulkInsert.BatchText
.GetParenthesesContent(bulkInsert.BatchText.IndexOf(" with (") + 6).Split(", ");
foreach (string setting in settings)
{
if (setting == "CHECK_CONSTRAINTS")
{
bulkInsert.CheckConstraints = true;
}
else if (setting == "FIRE_TRIGGERS")
{
bulkInsert.FireTriggers = true;
break;
}
}
}
evt = bulkInsert;
}
else if (xevent.Name == "sql_batch_completed" &&
xevent.Fields["batch_text"].ToString().Contains("insert bulk"))
{
if (!sessionDictionary.TryGetValue(xevent.Actions["session_id"].ToString(), out var session))
{
throw new Exception(
$"Could not find session ID {xevent.Actions["session_id"].ToString()} for bulk insert.");
}
var bulkInsert = (BulkInsert)session.Events
.FirstOrDefault(e =>
(e as BulkInsert)?.TransactionId ==
xevent.Actions["transaction_id"].ToString() &&
(e as BulkInsert)?.BatchText == xevent.Fields["batch_text"].ToString());
if (bulkInsert != null)
{
bulkInsert.RowCount = int.Parse(xevent.Fields["row_count"].ToString());
AddBulkInsertData(bulkInsert, con);
}
}
if (evt != null)
{
string sessionId = xevent.Actions["session_id"].ToString();
Session session = sessionDictionary.GetOrAdd(sessionId, new Session()
{
SessionId = sessionId
});
session.Events.Add(evt);
}
return(Task.CompletedTask);
}, CancellationToken.None);
}
}
var sessions = sessionDictionary.Values.ToList();
//Remove any sessions with no events
sessions.RemoveAll(s => s.Events.Count == 0);
foreach (Session session in sessions)
{
//Remove any bulk inserts where we never found a corresponding sql_batch_completed
session.Events.RemoveAll(e => (e as BulkInsert)?.RowCount == 0);
session.Events = session.Events.OrderBy(e => e.EventSequence).ToList();
}
var run = new Run()
{
Sessions = sessions.Where(s => s.Events.Count > 0).OrderBy(s => s.Events.First().Timestamp).ToList()
};
run.EventCaptureOrigin = run.Sessions.First().Events.First().Timestamp;
return(run);
}
