none
Display progressbar and percentage (%) while querying a database

    Question

  • when querying a database, I wish to display the progressbar and its percentage (10% interval till 100%) of completion.

    How do I implement it? I refer to 

    http://www.c-sharpcorner.com/UploadFile/deepak.sharma00/how-to-show-progress-of-fetching-database-records-using-a-pr/

    but it seems to me that I should not base the progressbar1.Maximum on the total records.

    I think my code in the percentage progress is wrong too, what should it be based on?

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Windows;
    using System.Windows.Controls;
    using System.Windows.Data;
    using System.Windows.Documents;
    using System.Windows.Input;
    using System.Windows.Media;
    using System.Windows.Media.Imaging;
    using System.Windows.Navigation;
    using System.Windows.Shapes;
    using System.Data;
    using System.Globalization;
    using System.IO;
    using System.ComponentModel;
    using System.Windows.Threading;
    using Xceed.Wpf.Toolkit;
    
    namespace TestSystemUtilisation
    {
        /// <summary>
        /// Interaction logic for MainWindow.xaml
        /// </summary>
        /// 
    
        public partial class MainWindow : Window
        {
            string path1 = @"C:\DriveD\TESTS4WORK\TestSystemUtilisation\CostCenter\";
            string path = @"C:\DriveD\TESTS4WORK\TestSystemUtilisation\";
            string outputfilename = "Utilisation.txt";
            string[] CostCenterName = {"Analog", "Digital", "EMS", "INT1", "INT2", "INT3", "Photo", "QA", "SMT1", "SMT2", "US-PF", "US-PXS"};
            string[] CostCenterNumber = { "130300", "130400", "110100", "130500", "130600", "130650", "120200", "200801", "190234", "190235", "120300", "120301" };
    
            double START_AddHours1, END_AddHours1, START_AddHours2, END_AddHours2;
            int MAX_DateDifference = 180;
    
            private BackgroundWorker worker = new BackgroundWorker();
        
            public MainWindow()
            {
                InitializeComponent();
                worker.WorkerReportsProgress = true;
                worker.WorkerSupportsCancellation = true;
                worker.DoWork += new DoWorkEventHandler(bw_DoWork);
                worker.ProgressChanged += new ProgressChangedEventHandler(bw_ProgressChanged);
                worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bw_RunWorkerCompleted);           
            }
    
            private void Window_Loaded(object sender, RoutedEventArgs e)
            {
                string[] formats = { "dd/MM/yyyy" };
                var defaultDT_Start = DateTime.ParseExact("01/12/2013", formats, new CultureInfo("en-US"), DateTimeStyles.None);
                DateTimePicker_Start.Value = defaultDT_Start;
                var defaultDT_End = DateTime.ParseExact("05/12/2013", formats, new CultureInfo("en-US"), DateTimeStyles.None);
                DateTimePicker_End.Value = defaultDT_End;
                Update_ListBoxTestSystemsDefault();
                string[] hr = { "Whole Day", "Day Shift w/o OT", "Night Shift w/o OT", "Day Shift with OT", "Night Shift with OT",
                                "Day Shift w/o OT + Night Shift w/o OT", "Day Shift w/o OT + Night Shift with OT",
                                "Night Shift w/o OT + Day Shift with OT", "Day Shift with OT + Night Shift with OT"};
                comboBox_Base.ItemsSource = hr.ToList();
                comboBox_CostCenter.ItemsSource = CostCenterName.ToList();
            }
            
            private void Update_ListBoxTestSystemsDefault()
            {
                listBox_TestSystems.Items.Clear();
                string outputfilename = "Analog.txt";
                FileInfo file = new FileInfo(string.Concat(path1, outputfilename));
                StreamReader stRead = file.OpenText();
                while (!stRead.EndOfStream)
                {
                    listBox_TestSystems.Items.Add(stRead.ReadLine());
                }
                stRead.Close();
            }
    
            private void comboBox_CostCenter_SelectionChanged(object sender, SelectionChangedEventArgs e)
            {
                int selectedIndex = comboBox_CostCenter.SelectedIndex;
                listBox_TestSystems.Items.Clear();
                label_CostCenter.Content = CostCenterNumber[selectedIndex];
                FileInfo file = new FileInfo(string.Concat(path1, CostCenterName[selectedIndex],".txt"));
                StreamReader stRead = file.OpenText();
                while (!stRead.EndOfStream)
                {
                    listBox_TestSystems.Items.Add(stRead.ReadLine());
                }
                stRead.Close();
            }
    
            private void comboBox_Base_SelectionChanged(object sender, SelectionChangedEventArgs e)
            {
                int selectedIndex = comboBox_Base.SelectedIndex;
                string[] hr = { "24", "8.8", "8.5", "11.55", "11.5", "17.3", "20.3", "20.05", "23.05" };
                string[] duration = { "00:00 AM - 11:59 PM", "07:30 AM - 05:00 PM", "11:00 PM - 07:45 AM", "07:30 AM - 08:00 PM", "08:00 PM - 07:45 AM",
                                      "07:30 AM - 05:00 PM + 11:00 PM - 07:45 AM", "07:30 AM - 05:00 PM + 08:00 PM - 07:45 AM", 
                                      "11:00 PM - 07:45 AM + 07:30 AM - 08:00 PM", "07:30 AM - 08:00 PM + 08:00 PM - 07:45 AM" };
                
                double[] AddHours_START1 = { 0, 7.5, 23, 7.5, 20, 7.5, 7.5, 23, 7.5 };
                double[] AddHours_END1 = { 24, 9.5, 8.75, 12.5, 11.75, 9.5, 9.5, 8.75, 12.5 };
                double[] AddHours_START2 = { 0, 0, 0, 0, 0, 23, 20, 7.5, 20};
                double[] AddHours_END2 = { 0, 0, 0, 0, 0, 8.75, 11.75, 12.5, 11.75};
    
                if (comboBox_Base.SelectedIndex == selectedIndex)
                    label_Base.Content = hr[selectedIndex];
                    label_Duration.Content = duration[selectedIndex];
                    START_AddHours1 = AddHours_START1[selectedIndex];
                    END_AddHours1 = AddHours_END1[selectedIndex];
                    START_AddHours2 = AddHours_START2[selectedIndex];
                    END_AddHours2 = AddHours_END2[selectedIndex];
            }
    
            private void Update_ListBoxUtilisation()
            {  
                listBox_Utilisation.Items.Clear();
             
                FileInfo file = new FileInfo(string.Concat(path, outputfilename));
                StreamReader stRead = file.OpenText();
                while (!stRead.EndOfStream)
                {
                    listBox_Utilisation.Items.Add(stRead.ReadLine());
                }
                stRead.Close();
            }
    
            private void Query_Click(object sender, RoutedEventArgs e)
            {
                textBox_Status.Text = "";
    
                if (listBox_TestSystems.SelectedItems.Count == 0)
                {
                    Xceed.Wpf.Toolkit.MessageBox.Show("Please select at least one TestSystem.", "No TestSystem selected");
                    return;
                }
    
                string[] formats = { "dd/MM/yyyy" };
                var Start = DateTime.ParseExact(DateTimePicker_Start.Text.ToString(), formats, new CultureInfo("en-US"), DateTimeStyles.None);
                var End = DateTime.ParseExact(DateTimePicker_End.Text.ToString(), formats, new CultureInfo("en-US"), DateTimeStyles.None);
    
                TimeSpan ts = End - Start;
                int DifferenceInDays = ts.Days;
    
                if (DifferenceInDays > MAX_DateDifference)
                {
                    Xceed.Wpf.Toolkit.MessageBox.Show("Please select a shorter Date Difference of 180 days or less.", "Date Difference exceeds its limit");
                    return;
                }
    
                progressBar1.Maximum = 8;
                textBox_Status.Text = "In progress ...";
                listBox_Utilisation.Items.Clear();
    
                if (worker.IsBusy != true)
                {
                    worker.RunWorkerAsync();
                }            
            }
    
            private void bw_DoWork(object sender, DoWorkEventArgs e)
            {
                TS_UtilisationDataSetTableAdapters.PPL_TESTSYSTEMS_UTILISATIONTableAdapter adapter = new TS_UtilisationDataSetTableAdapters.PPL_TESTSYSTEMS_UTILISATIONTableAdapter();
                TS_UtilisationDataSet.PPL_TESTSYSTEMS_UTILISATIONDataTable table = adapter.GetData();
    
                StreamWriter sw = new StreamWriter(string.Concat(path, outputfilename));
                string header1 = "   Date     SystemType   Testtime    Waittime    Systemtime     Utilisation (%)          Utilisation (%)";
                string header2 = "                                                              (Test, System time)   (Test, Wait, System time)";
                string header3 = "==========  ==========   ========    ========    ==========   ===================   =========================";
                sw.WriteLine(header1);
                sw.WriteLine(header2);
                sw.WriteLine(header3);
    
                var SelectedTestSystems = new List<string>();
                var Testtime = new List<Int32>();
                var Waittime = new List<Int32>();
                var Systemtime = new List<Int32>();
                var Utilisation1 = new List<double>();
                var Utilisation2 = new List<double>();
                var Date = new List<DateTime>();
    
                Int32 Testtime_hr, Testtime_min, TotalTesttime_hr, TotalTesttime_min;
                Int32 Waittime_hr, Waittime_min, TotalWaittime_hr, TotalWaittime_min;
                Int32 Systemtime_hr, Systemtime_min, TotalSystemtime_hr, TotalSystemtime_min; 
                worker.ReportProgress(10);
                System.Threading.Thread.Sleep(1000);
    
                Application.Current.Dispatcher.Invoke(DispatcherPriority.Normal, (Action)(() =>
                {
                    string[] formats = { "dd/MM/yyyy" };
                    var Start = DateTime.ParseExact(DateTimePicker_Start.Text.ToString(), formats, new CultureInfo("en-US"), DateTimeStyles.None);
                    var End = DateTime.ParseExact(DateTimePicker_End.Text.ToString(), formats, new CultureInfo("en-US"), DateTimeStyles.None);
                
                    foreach (var arr in listBox_TestSystems.SelectedItems)
                    {
                        SelectedTestSystems.Add(arr.ToString());
                    }
                
                    TimeSpan ts = End - Start;
                    int DifferenceInDays = ts.Days;
                
                    for (int i = 0; i < SelectedTestSystems.Count(); i++)
                    {
                        Testtime.Clear();
                        Waittime.Clear();
                        Systemtime.Clear();
                        Utilisation1.Clear();
                        Utilisation2.Clear();
    
                        for (int d = 0; d <= DifferenceInDays; d++)
                        {
                            DateTime START1 = Start.AddDays(d).AddHours(START_AddHours1);
                            DateTime END1 = START1.AddHours(END_AddHours1);
                            DateTime START2 = Start.AddDays(d).AddHours(START_AddHours2);
                            DateTime END2 = START2.AddHours(END_AddHours2);
    
                            IEnumerable<DataRow> query =
                            from row in table.AsEnumerable()
                            where (row.Field<string>("SYSTEMTYPE") == SelectedTestSystems[i]) &&
                            ((row.Field<DateTime?>("TS_START") > START1) && (row.Field<DateTime?>("TS_END") < END1)
                            || (row.Field<DateTime?>("TS_START") > START2) && (row.Field<DateTime?>("TS_END") < END2))
                            select row;
    
                            int count = query.Count();
                            var TimeTest = query.Select(row => row.Field<Decimal?>("TIME_TEST")).Sum();
                            var TimeWait = query.Select(row => row.Field<Decimal?>("TIME_WAIT")).Sum();
                            var TimeSystem = query.Select(row => row.Field<Decimal?>("TIME_SYSTEM")).Sum();
    
                            decimal? hr_per_day = Convert.ToDecimal(label_Base.Content.ToString());
    
                            decimal? Utilisation_1 = (TimeTest + TimeSystem) * 100 / (hr_per_day * 3600);
                            decimal? Utilisation_2 = (TimeTest + TimeSystem + TimeWait) * 100 / (hr_per_day * 3600);
    
                            Testtime.Add(Convert.ToInt32(TimeTest));
                            Waittime.Add(Convert.ToInt32(TimeWait));
                            Systemtime.Add(Convert.ToInt32(TimeSystem));
    
                            Utilisation1.Add(Convert.ToDouble(Utilisation_1));
                            Utilisation2.Add(Convert.ToDouble(Utilisation_2));
    
                            Date.Add(START1);
    
                            Testtime_hr = Testtime[d] / 3600;
                            Testtime_min = Testtime[d] / 60 - Testtime_hr * 60;
                            Waittime_hr = Waittime[d] / 3600;
                            Waittime_min = Waittime[d] / 60 - Waittime_hr * 60;
                            Systemtime_hr = Systemtime[d] / 3600;
                            Systemtime_min = Systemtime[d] / 60 - Systemtime_hr * 60;
    
                            // to remove the time from DateTime
                            int foundS1 = Date[d].ToString().IndexOf(" ");
                            string SelectedDate = Date[d].ToString().Remove(foundS1,12);
    
                            if (radio_Yes.IsChecked == true)
                            {
                                sw.WriteLine("{0,8}{1,10}{2,13}{3,12}{4,14}{5,19}{6,27}", SelectedDate, SelectedTestSystems[i],
                                                string.Concat(Testtime_hr, "h ", Testtime_min, "min"),
                                                string.Concat(Waittime_hr, "h ", Waittime_min, "min"),
                                                string.Concat(Systemtime_hr, "h ", Systemtime_min, "min"),
                                                string.Format("{0:N2}", Utilisation1[d]), string.Format("{0:N2}", Utilisation2[d]));
                            }
                        }
                        
                        TotalTesttime_hr = Testtime.Sum() / 3600;
                        TotalTesttime_min = Testtime.Sum() / 60 - TotalTesttime_hr * 60;
                        TotalWaittime_hr = Waittime.Sum() / 3600;
                        TotalWaittime_min = Waittime.Sum() / 60 - TotalWaittime_hr * 60;
                        TotalSystemtime_hr = Systemtime.Sum() / 3600;
                        TotalSystemtime_min = Systemtime.Sum() / 60 - TotalSystemtime_hr * 60;
                     
                        sw.WriteLine("{0,8}{1,9}{2,13}{3,12}{4,14}{5,19}{6,27}", "Total      ", SelectedTestSystems[i],
                                        string.Concat(TotalTesttime_hr, "h ", TotalTesttime_min, "min"),
                                        string.Concat(TotalWaittime_hr, "h ", TotalWaittime_min, "min"),
                                        string.Concat(TotalSystemtime_hr, "h ", TotalSystemtime_min, "min"),
                                        string.Format("{0:N2}", Utilisation1.Average()), string.Format("{0:N2}", Utilisation2.Average()));
                        sw.WriteLine();
                    }
    
                    sw.Close();
                    Update_ListBoxUtilisation();
                }));
            }
       
            private void bw_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
            {
                if ((e.Cancelled == true))
                {
                    textBox_Status.Text = "Canceled!";
                }
    
                else if (!(e.Error == null))
                {
                    textBox_Status.Text = ("Error: " + e.Error.Message);
                }
    
                else
                {
                    textBox_Status.Text = "Completed.";
                }
            }
    
            private void bw_ProgressChanged(object sender, ProgressChangedEventArgs e)
            {
                textBox_Status.Text = (e.ProgressPercentage.ToString() + "%");
                progressBar1.Value = e.ProgressPercentage;
            }     
           
        }
    }
    

    Friday, January 10, 2014 2:06 AM

Answers

All replies