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