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 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(); }
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(); } }