public void StartLoad(BackgroundWorker worker)
{
var useParams = false;
var badParams = new List <string>();
foreach (var theKey in _paramMappings.Keys)
{
if ((_paramMappings[theKey] == null) || (_paramMappings[theKey].Length == 0))
{
badParams.Add(theKey);
}
}
foreach (var theKey in badParams)
{
_paramMappings.Remove(theKey);
}
//Need some parameters?
if (_paramMappings.Count > 0)
{
ParamServer.Initialize(_paramQuery, _paramConnectionString, _paramMappings);
useParams = true;
}
//Initialize the connection pool
var conn = new SqlConnection(_connectionString);
//TODO: use this or not??
SqlConnection.ClearPool(conn);
conn.Open();
conn.Dispose();
//make sure the run cancelled flag is not set
QueryInput.RunCancelled = false;
//Spin up the load threads
for (var i = 0; i < _threads; i++)
{
conn = new SqlConnection(_connectionString);
//TODO: Figure out how to make this option work (maybe)
//conn.FireInfoMessageEventOnUserErrors = true;
SqlCommand statsComm = null;
var queryComm = new SqlCommand {
CommandTimeout = _commandTimeout, Connection = conn, CommandText = _query
};
if (useParams)
{
queryComm.Parameters.AddRange(ParamServer.GetParams());
}
var setStatistics = (_collectIoStats ? @"SET STATISTICS IO ON;" : "") + (_collectTimeStats ? @"SET STATISTICS TIME ON;" : "");
if (setStatistics.Length > 0)
{
statsComm = new SqlCommand {
CommandTimeout = _commandTimeout, Connection = conn, CommandText = setStatistics
};
}
//Queue<queryOutput> queryOutInfo = new Queue<queryOutput>();
var input = new QueryInput(statsComm, queryComm,
// this.queryOutInfo,
_iterations, _forceDataRetrieval);
var theThread = new Thread(input.StartLoadThread)
{
Priority = ThreadPriority.BelowNormal
};
_threadPool.Add(theThread);
_commandPool.Add(queryComm);
//queryOutInfoPool.Add(queryOutInfo);
}
//Start the load threads
for (var i = 0; i < _threads; i++)
{
_threadPool[i].Start();
}
//Start reading the queue...
var finishedThreads = 0;
var cancelled = false;
while (finishedThreads < _threads)
{
// for (int i = 0; i < threads; i++)
// {
// try
// {
QueryOutput theOut = null;
//lock (queryOutInfoPool[i])
lock (QueryOutInfo)
{
//if (queryOutInfoPool[i].Count > 0)
//theOut = (queryOutput)queryOutInfoPool[i].Dequeue();
if (QueryOutInfo.Count > 0)
{
theOut = QueryOutInfo.Dequeue();
}
else
{
Monitor.Wait(QueryOutInfo);
}
}
if (theOut != null)
{
//Report output to the UI
worker.ReportProgress((int)(finishedThreads / (decimal)_threads * 100), theOut);
//TODO: Make this actually remove the queue from the pool so that it's not checked again -- maintain this with a bitmap, perhaps?
if (theOut.Finished)
{
finishedThreads++;
}
}
/* }
* catch (InvalidOperationException e)
* {
* }
*/
/*
* if (theOut != null)
* Thread.Sleep(200);
* else
* Thread.Sleep(10);
*/
// }
//TODO: Remove this ?
GC.Collect();
if (worker.CancellationPending && !cancelled)
{
QueryInput.RunCancelled = true;
//First, kill connections as fast as possible
SqlConnection.ClearAllPools();
//for each 20 threads, create a new thread dedicated
//to killing them
var threadNum = _threadPool.Count;
var killerThreads = new List <Thread>();
while (threadNum > 0)
{
var i = threadNum <= 20 ? 0 : threadNum - 20;
var killThreads = new Thread[threadNum - i < 1 ? threadNum : threadNum - i];
var killCommands = new SqlCommand[threadNum - i < 1 ? threadNum : threadNum - i];
_threadPool.CopyTo(i, killThreads, 0, killThreads.Length);
_commandPool.CopyTo(i, killCommands, 0, killCommands.Length);
for (var j = threadNum - 1; j >= i; j--)
{
_threadPool.RemoveAt(j);
_commandPool.RemoveAt(j);
}
var kill = new ThreadKiller(killThreads, killCommands);
var killer = new Thread(kill.KillEm);
killer.Start();
Thread.Sleep(0);
killerThreads.Add(killer);
threadNum = i;
}
//wait for the kill threads to return
//before exiting...
foreach (var theThread in killerThreads)
{
theThread.Join();
}
cancelled = true;
}
}
//clear any remaining messages -- these are almost certainly
//execeptions due to thread cancellation
//queryOutInfo.Clear();
}
public void StartLoadThread()
{
try
{
//do the work
using (var conn = _queryComm.Connection)
{
SqlInfoMessageEventHandler handler = GetInfoMessages;
for (var i = 0; i < _iterations; i++)
{
if (_runCancelled)
{
throw new Exception();
}
Exception outException = null;
try
{
//initialize the outInfo structure
_outInfo = new QueryOutput();
conn.Open();
//set up the statistics gathering
if (_statsComm != null)
{
_statsComm.ExecuteNonQuery();
Thread.Sleep(0);
conn.InfoMessage += handler;
}
//Params are assigned only once -- after that, their values are dynamically retrieved
if (_queryComm.Parameters.Count > 0)
{
ParamServer.GetNextRow_Values(_queryComm.Parameters);
}
_sw.Start();
//TODO: This could be made better
if (_forceDataRetrieval)
{
var reader = _queryComm.ExecuteReader();
Thread.Sleep(0);
do
{
Thread.Sleep(0);
while (reader.Read())
{
//grab the first column to force the row down the pipe
var x = reader[0];
Thread.Sleep(0);
}
} while (reader.NextResult());
}
else
{
_queryComm.ExecuteNonQuery();
Thread.Sleep(0);
}
_sw.Stop();
}
catch (Exception e)
{
if (_runCancelled)
{
throw;
}
else
{
outException = e;
}
if (_sw.IsRunning)
{
_sw.Stop();
}
}
finally
{
//Clean up the connection
if (_statsComm != null)
{
conn.InfoMessage -= handler;
}
conn.Close();
}
var finished = i == _iterations - 1;
//List<string> infoMessages = null;
//infoMessages = (stats_comm != null) ? theInfoMessages[connectionHashCode] : null;
/*
* queryOutput theout = new queryOutput(
* outException,
* sw.Elapsed,
* finished,
* (infoMessages == null || infoMessages.Count == 0) ? null : infoMessages.ToArray());
*/
_outInfo.E = outException;
_outInfo.Time = _sw.Elapsed;
_outInfo.Finished = finished;
lock (QueryOutInfo)
{
QueryOutInfo.Enqueue(_outInfo);
Monitor.Pulse(QueryOutInfo);
}
//Prep the collection for the next round
//if (infoMessages != null && infoMessages.Count > 0)
// infoMessages.Clear();
_sw.Reset();
}
}
}
catch
{
if (_runCancelled)
{
//queryOutput theout = new queryOutput(null, new TimeSpan(0), true, null);
_outInfo.Time = new TimeSpan(0);
_outInfo.Finished = true;
lock (QueryOutInfo)
{
QueryOutInfo.Enqueue(_outInfo);
}
}
else
{
throw;
}
}
}
public void StartLoad(BackgroundWorker worker)
{
bool useParams = false;
List <string> badParams = new List <string>();
foreach (string theKey in paramMappings.Keys)
{
if ((paramMappings[theKey] == null) ||
(paramMappings[theKey].Length == 0))
{
badParams.Add(theKey);
}
}
foreach (string theKey in badParams)
{
paramMappings.Remove(theKey);
}
//Need some parameters?
if (paramMappings.Count > 0)
{
ParamServer.Initialize(paramQuery, paramConnectionString, paramMappings);
useParams = true;
}
//Initialize the connection pool
SqlConnection conn = new SqlConnection(this.connectionString);
//TODO: use this or not??
SqlConnection.ClearPool(conn);
conn.Open();
conn.Dispose();
//make sure the run cancelled flag is not set
queryInput.RunCancelled = false;
//Spin up the load threads
for (int i = 0; i < threads; i++)
{
conn = new SqlConnection(this.connectionString);
//TODO: Figure out how to make this option work (maybe)
//conn.FireInfoMessageEventOnUserErrors = true;
SqlCommand stats_comm = null;
SqlCommand query_comm = new SqlCommand();
query_comm.CommandTimeout = this.commandTimeout;
query_comm.Connection = conn;
query_comm.CommandText = this.query;
if (useParams)
{
query_comm.Parameters.AddRange(ParamServer.GetParams());
}
string setStatistics =
((collectIOStats) ? (@"SET STATISTICS IO ON;") : ("")) +
((collectTimeStats) ? (@"SET STATISTICS TIME ON;") : (""));
if (setStatistics.Length > 0)
{
stats_comm = new SqlCommand();
stats_comm.CommandTimeout = this.commandTimeout;
stats_comm.Connection = conn;
stats_comm.CommandText = setStatistics;
}
//Queue<queryOutput> queryOutInfo = new Queue<queryOutput>();
queryInput input = new queryInput(
stats_comm,
query_comm,
// this.queryOutInfo,
this.iterations,
this.forceDataRetrieval);
Thread theThread = new Thread(new ThreadStart(input.startLoadThread));
theThread.Priority = ThreadPriority.BelowNormal;
threadPool.Add(theThread);
commandPool.Add(query_comm);
//queryOutInfoPool.Add(queryOutInfo);
}
//Start the load threads
for (int i = 0; i < threads; i++)
{
threadPool[i].Start();
}
//Start reading the queue...
int finishedThreads = 0;
bool cancelled = false;
while (finishedThreads < threads)
{
// for (int i = 0; i < threads; i++)
// {
// try
// {
queryOutput theOut = null;
//lock (queryOutInfoPool[i])
lock (queryOutInfo)
{
//if (queryOutInfoPool[i].Count > 0)
//theOut = (queryOutput)queryOutInfoPool[i].Dequeue();
if (queryOutInfo.Count > 0)
{
theOut = queryOutInfo.Dequeue();
}
else
{
Monitor.Wait(queryOutInfo);
}
}
if (theOut != null)
{
//Report output to the UI
worker.ReportProgress((int)(((decimal)finishedThreads / (decimal)threads) * 100), theOut);
//TODO: Make this actually remove the queue from the pool so that it's not checked again -- maintain this with a bitmap, perhaps?
if (theOut.finished)
{
finishedThreads++;
}
}
/* }
* catch (InvalidOperationException e)
* {
* }
*/
/*
* if (theOut != null)
* Thread.Sleep(200);
* else
* Thread.Sleep(10);
*/
// }
//TODO: Remove this ?
GC.Collect();
if (worker.CancellationPending && (!cancelled))
{
queryInput.RunCancelled = true;
//First, kill connections as fast as possible
SqlConnection.ClearAllPools();
//for each 20 threads, create a new thread dedicated
//to killing them
int threadNum = threadPool.Count;
List <Thread> killerThreads = new List <Thread>();
while (threadNum > 0)
{
int i = (threadNum <= 20) ? 0 : (threadNum - 20);
Thread[] killThreads = new Thread[((threadNum - i) < 1) ? threadNum : (threadNum - i)];
SqlCommand[] killCommands = new SqlCommand[((threadNum - i) < 1) ? threadNum : (threadNum - i)];
threadPool.CopyTo(
i, killThreads, 0, killThreads.Length);
commandPool.CopyTo(
i, killCommands, 0, killCommands.Length);
for (int j = (threadNum - 1); j >= i; j--)
{
threadPool.RemoveAt(j);
commandPool.RemoveAt(j);
}
ThreadKiller kill = new ThreadKiller(
killThreads,
killCommands);
Thread killer = new Thread(new ThreadStart(kill.KillEm));
killer.Start();
Thread.Sleep(0);
killerThreads.Add(killer);
threadNum = i;
}
//wait for the kill threads to return
//before exiting...
foreach (Thread theThread in killerThreads)
{
theThread.Join();
}
cancelled = true;
}
}
//clear any remaining messages -- these are almost certainly
//execeptions due to thread cancellation
//queryOutInfo.Clear();
}
public void startLoadThread()
{
try
{
//do the work
using (SqlConnection conn = query_comm.Connection)
{
int connectionHashCode = conn.GetHashCode();
SqlInfoMessageEventHandler handler = new SqlInfoMessageEventHandler(queryInput.GetInfoMessages);
for (int i = 0; i < iterations; i++)
{
if (runCancelled)
{
throw new Exception();
}
Exception outException = null;
try
{
//initialize the outInfo structure
queryInput.outInfo = new queryOutput();
conn.Open();
//set up the statistics gathering
if (stats_comm != null)
{
stats_comm.ExecuteNonQuery();
Thread.Sleep(0);
conn.InfoMessage += handler;
}
//Params are assigned only once -- after that, their values are dynamically retrieved
if (query_comm.Parameters.Count > 0)
{
ParamServer.GetNextRow_Values(query_comm.Parameters);
}
sw.Start();
if (forceDataRetrieval)
{
SqlDataReader reader = query_comm.ExecuteReader();
Thread.Sleep(0);
do
{
Thread.Sleep(0);
while (reader.Read())
{
//grab the first column to force the row down the pipe
object x = reader[0];
Thread.Sleep(0);
}
} while (reader.NextResult());
}
else
{
query_comm.ExecuteNonQuery();
Thread.Sleep(0);
}
sw.Stop();
}
catch (Exception e)
{
if (runCancelled)
{
throw;
}
else
{
outException = e;
}
if (sw.IsRunning)
{
sw.Stop();
}
}
finally
{
//Clean up the connection
if (stats_comm != null)
{
conn.InfoMessage -= handler;
}
conn.Close();
}
bool finished = (i == (iterations - 1)) ? true : false;
//List<string> infoMessages = null;
//infoMessages = (stats_comm != null) ? theInfoMessages[connectionHashCode] : null;
/*
* queryOutput theout = new queryOutput(
* outException,
* sw.Elapsed,
* finished,
* (infoMessages == null || infoMessages.Count == 0) ? null : infoMessages.ToArray());
*/
outInfo.e = outException;
outInfo.time = sw.Elapsed;
outInfo.finished = finished;
lock (LoadEngine.queryOutInfo)
{
LoadEngine.queryOutInfo.Enqueue(outInfo);
Monitor.Pulse(LoadEngine.queryOutInfo);
}
//Prep the collection for the next round
//if (infoMessages != null && infoMessages.Count > 0)
// infoMessages.Clear();
sw.Reset();
}
}
}
catch
{
if (runCancelled)
{
//queryOutput theout = new queryOutput(null, new TimeSpan(0), true, null);
outInfo.time = new TimeSpan(0);
outInfo.finished = true;
lock (LoadEngine.queryOutInfo)
{
LoadEngine.queryOutInfo.Enqueue(outInfo);
}
}
else
{
throw;
}
}
}
private void StartLoad(BackgroundWorker worker)
{
var useParams = false;
var badParams = new List <string>();
foreach (var theKey in _paramMappings.Keys)
{
if ((_paramMappings[theKey] == null) || (_paramMappings[theKey].Length == 0))
{
badParams.Add(theKey);
}
}
foreach (var theKey in badParams)
{
_paramMappings.Remove(theKey);
}
//Need some parameters?
if (_paramMappings.Count > 0)
{
ParamServer.Initialize(_paramQuery, _paramConnectionString, _paramMappings);
useParams = true;
}
//Initialize the connection pool
var conn = new SqlConnection(_connectionString);
//TODO: use this or not??
SqlConnection.ClearPool(conn);
conn.Open();
conn.Dispose();
//Spin up the load threads
for (var i = 0; i < _threads; i++)
{
conn = new SqlConnection(_connectionString);
//TODO: Figure out how to make this option work (maybe)
//conn.FireInfoMessageEventOnUserErrors = true;
SqlCommand statsComm = null;
var queryComm = new SqlCommand {
CommandTimeout = _commandTimeout, Connection = conn, CommandText = _query
};
if (useParams)
{
queryComm.Parameters.AddRange(ParamServer.GetParams());
}
var setStatistics = (_collectIoStats ? @"SET STATISTICS IO ON;" : string.Empty) + (_collectTimeStats ? @"SET STATISTICS TIME ON;" : string.Empty);
if (setStatistics.Length > 0)
{
statsComm = new SqlCommand {
CommandTimeout = _commandTimeout, Connection = conn, CommandText = setStatistics
};
}
//Queue<queryOutput> queryOutInfo = new Queue<queryOutput>();
using var input = new QueryInput(statsComm, queryComm,
// this.queryOutInfo,
_iterations, _forceDataRetrieval, _queryDelay, worker, _killQueriesOnCancel, _threads);
var theThread = new Thread(input.StartLoadThread)
{
Priority = ThreadPriority.BelowNormal, IsBackground = true
};
theThread.Name = "thread: " + i;
_threadPool.Add(theThread);
_commandPool.Add(queryComm);
//queryOutInfoPool.Add(queryOutInfo);
}
// create a token source for the workers to be able to listen to a cancel event
using var workerCTS = new CancellationTokenSource();
_finishedThreads = 0;
for (var i = 0; i < _threads; i++)
{
_threadPool[i].Start(workerCTS.Token);
}
//Start reading the queue...
var cancelled = false;
while (!cancelled)
{
QueryOutput theOut = null;
try
{
// wait for OutInfo items in the queue or a user cancel event
theOut = QueryOutInfo.Take(_backgroundWorkerCTS.Token);
}
catch (Exception ex)
{
// The exception is InvalidOperationException if the threads are done
// and OperationCanceledException if the user clicked cancel.
// If it's OperationCanceledException, we need to cancel
// the worker threads and wait for them to exit
if (ex is OperationCanceledException)
{
workerCTS.Cancel();
foreach (var theThread in _threadPool)
{
// give the thread max 5 seconds to cancel nicely
if (!theThread.Join(5000))
{
theThread.Interrupt();
}
}
}
SqlConnection.ClearAllPools();
cancelled = true;
}
if (theOut != null)
{
//Report output to the UI
int finishedThreads = Interlocked.CompareExchange(ref _finishedThreads, 0, 0);
theOut.ActiveThreads = _threads - finishedThreads;
worker.ReportProgress((int)(_finishedThreads / (decimal)_threads * 100), theOut);
}
GC.Collect();
}
}
public void StartLoadThread(Object token)
{
bool runCancelled = false;
CancellationToken ctsToken = (CancellationToken)token;
try
{
ctsToken.Register(() =>
{
// Cancellation on the token will interrupt and cancel the thread
runCancelled = true;
_statsComm.Cancel();
_queryComm.Cancel();
});
//do the work
using (var conn = _queryComm.Connection)
{
SqlInfoMessageEventHandler handler = GetInfoMessages;
for (var i = 0; i < _iterations && !runCancelled; i++)
{
Exception outException = null;
try
{
//initialize the outInfo structure
_outInfo = new QueryOutput();
if (conn != null)
{
conn.Open();
//set up the statistics gathering
if (_statsComm != null)
{
_statsComm.ExecuteNonQuery();
Thread.Sleep(0);
conn.InfoMessage += handler;
}
}
//Params are assigned only once -- after that, their values are dynamically retrieved
if (_queryComm.Parameters.Count > 0)
{
ParamServer.GetNextRow_Values(_queryComm.Parameters);
}
_sw.Start();
//TODO: This could be made better
if (_forceDataRetrieval)
{
var reader = _queryComm.ExecuteReader();
Thread.Sleep(0);
do
{
Thread.Sleep(0);
while (!runCancelled && reader.Read())
{
//grab the first column to force the row down the pipe
// ReSharper disable once UnusedVariable
var x = reader[0];
Thread.Sleep(0);
}
} while (!runCancelled && reader.NextResult());
}
else
{
_queryComm.ExecuteNonQuery();
Thread.Sleep(0);
}
_sw.Stop();
}
catch (Exception ex)
{
if (!runCancelled)
{
outException = ex;
}
if (_sw.IsRunning)
{
_sw.Stop();
}
}
finally
{
//Clean up the connection
if (conn != null)
{
if (_statsComm != null)
{
conn.InfoMessage -= handler;
}
conn.Close();
}
}
var finished = i == _iterations - 1;
//List<string> infoMessages = null;
//infoMessages = (stats_comm != null) ? theInfoMessages[connectionHashCode] : null;
/*
* queryOutput theout = new queryOutput(
* outException,
* sw.Elapsed,
* finished,
* (infoMessages == null || infoMessages.Count == 0) ? null : infoMessages.ToArray());
*/
_outInfo.E = outException;
_outInfo.Time = _sw.Elapsed;
_outInfo.Finished = finished;
QueryOutInfo.Add(_outInfo);
//Prep the collection for the next round
//if (infoMessages != null && infoMessages.Count > 0)
// infoMessages.Clear();
_sw.Reset();
if (!runCancelled)
{
try
{
if (_queryDelay > 0)
{
Task.Delay(_queryDelay, ctsToken).Wait();
}
}
catch (AggregateException ae)
{
ae.Handle((x) =>
{
if (x is TaskCanceledException)
{
runCancelled = true;
return(true);
}
// if we get here, the exception wasn't a cancel
// so don't swallow it
return(false);
});
}
}
}
}
}
catch (Exception)
{
if (runCancelled)
{
//queryOutput theout = new queryOutput(null, new TimeSpan(0), true, null);
_outInfo.Time = new TimeSpan(0);
_outInfo.Finished = true;
QueryOutInfo.Add(_outInfo);
}
}
Interlocked.Increment(ref _finishedThreads);
if (_finishedThreads == _numWorkerThreads)
{
// once all of the threads have exited, tell the other side that we're done adding items to the collection
QueryOutInfo.CompleteAdding();
}
}
