Solved

Display real elapsed time taking to execute stored procedure call in C#

Posted on 2016-10-10
13
37 Views
Last Modified: 2016-11-02
Hello, I want to execute bunch of stored procedures in a loop in a windows C# application. Each SP executes for over a minute. User will be interested in how long each process takes and I wanted to display real elapsed time while executing SP. I tried below code and it doesn't show real elapsed time instead final time took to execute. Please help.

private void Process()
{
          Dictionary<string, int> spData = new Dictionary<string, int>;
          foreach(KeyValuePair<string, string>k in formData)
         {
               Stopwatch stopWatch = new Stopwatch();
               stopWatch.Start();

               ExeccuteSP(k.Key);  //Key has SP Name and gets executed in ExecuteSP function

               stopWatch.Stop();
               long duration = stopWatch.ElapsedMilliseconds;

               lblTime = duration;
         }

}
0
Comment
Question by:vidhyashan
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
perhaps you can try StartNew instead?

I used this in my project and it works fine:

Stopwatch watch = Stopwatch.StartNew();

//run your stuff here

 watch.Stop();

Open in new window

0
 
LVL 44

Accepted Solution

by:
AndyAinscow earned 350 total points (awarded by participants)
Comment Utility
The line
lblTime = duration;
overwrites the previous value.  Did you want something like:
lblTime = lblTime + ", " + duration;
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
I guess you'll need a label for each SP. Something like:
lblTime1 = duration;
lblTime2= duration;
lblTimeN = duration;
0
 
LVL 1

Author Comment

by:vidhyashan
Comment Utility
None works. Please find the sample program I wrote below. When Ok button is pushed the label don't display any value until the for loop completes (please find the 2 screen shots attached). I want the label to display the time while executing the for loop too. Timer_Tick event don't trigger until for loop completes. This is the same scenario when executing a SP from my original post:


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ElapsedTime
{
    public partial class Form1 : Form
    {
        private Stopwatch stopWatch;
        public Form1()
        {
            InitializeComponent();
        }

        private void mytimer_Tick(object sender, EventArgs e)
        {
            mylabel.Text = string.Format("{0}:{1}:{2}", stopWatch.Elapsed.Hours.ToString("00"), stopWatch.Elapsed.Minutes.ToString("00"), stopWatch.Elapsed.Seconds.ToString("00"));
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            stopWatch.Stop();
            mytimer.Enabled = false;
        }

        private void btnOk_Click(object sender, EventArgs e)
        {
            stopWatch = new Stopwatch();
            mytimer.Enabled = true;
            stopWatch.Start();

            for (int i = 1; i < 10000; i++)
            {
                Console.WriteLine(i);
            }

        }

    }
}
Screen.PNG
Screen1.PNG
0
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 350 total points (awarded by participants)
Comment Utility
Application.DoEvents will allow the UI to update, put it where you want in your loop.

ps.  Keep my earlier comment in mind if you want to display all the times taken.
0
 
LVL 1

Author Comment

by:vidhyashan
Comment Utility
Andy, it doesn't work. I tried it and see same results
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 49

Assisted Solution

by:Ryan Chong
Ryan Chong earned 150 total points (awarded by participants)
Comment Utility
@vidhyashan,

Not too sure why you need a Timer control and then you still need a StopWatch there. And not too sure why you doing a looping in your button's clicked event there...

But what Andy mentioned should be something similar to below, which should work:

private void btnOk_Click(object sender, EventArgs e)
        {
            stopWatch = new Stopwatch();
            mytimer.Enabled = true;
            stopWatch.Start();

            for (int i = 1; i < 10000; i++)
            {
                Console.WriteLine(i);
                Application.DoEvents();
            }

        }
0
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 350 total points (awarded by participants)
Comment Utility
You need to have something like

SP1
displayElapsedTime
Application.DoEvents
SP2
displayElapsedTime
Application.DoEvents
SP3
displayElapsedTime
Application.DoEvents
....


It will work.
0
 
LVL 1

Author Comment

by:vidhyashan
Comment Utility
Ryan and Andy, thanks for your response. Lets focus on original item below. When Sp is called cursor wont come back until finished. I already have Application.DoEvents() like below. If this is the case how can it elapsed time gets displayed ? Please advice.


private void Process()
{
          Application.DoEvents() ;
          Dictionary<string, int> spData = new Dictionary<string, int>;
          foreach(KeyValuePair<string, string>k in formData)
         {
               Stopwatch stopWatch = new Stopwatch();
               stopWatch.Start();

               Application.DoEvents() ;
               ExeccuteSP(k.Key);  //Key has SP Name and gets executed in ExecuteSP function
               Application.DoEvents() ;
               stopWatch.Stop();
               long duration = stopWatch.ElapsedMilliseconds;

               lblTime.text = duration;
         }

}
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
A partial repeat of myself:
You need to have something like

SP1
displayElapsedTime
Application.DoEvents


so modifying your example to

private void Process()
{
//          Application.DoEvents() ;
          Dictionary<string, int> spData = new Dictionary<string, int>;
          foreach(KeyValuePair<string, string>k in formData)
         {
               Stopwatch stopWatch = new Stopwatch();
               stopWatch.Start();

//               Application.DoEvents() ;
               ExeccuteSP(k.Key);  //Key has SP Name and gets executed in ExecuteSP function
//               Application.DoEvents() ;
               stopWatch.Stop();
               long duration = stopWatch.ElapsedMilliseconds;

               lblTime.text = duration;
              Application.DoEvents() ;
         }

}

should be what you desire.

Application.DoEvents:
https://msdn.microsoft.com/en-us/library/system.windows.forms.application.doevents(v=vs.110).aspx
Basically it lets the GUI handle waiting events by pausing the current code and then continues with the code after anything waiting is processed.
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
ps.  Don't forget what I pointed out in my first comment (plus a potential solution) that
lblTime.text = duration;
will overwrite the previous value the next time through the loop.
0
 
LVL 1

Author Comment

by:vidhyashan
Comment Utility
Andy, I understand that lblTime.Text = duration will overwrite. Here its to check how long its taking to execute SP. But so far no solutions worked. I will have to find alternate solution. I expected some experts to alter the way I have done so far meaning different solution to achieve result.
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
I don't know why it is reported as not working because it does work and do what was asked for
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now