How to show a Progress Bar in ADO.NET

Posted on 7/5/2006 @ 11:41 AM in #Vanilla .NET by | Feedback | 5175 views

Now this could be hella useful in showing progressbars. Note that you can easily hook this up with an ASP.NET AJAX Progress bar. Usually you would do this when reading huge blobs out of the db. But what I did was, I instead used a varchar(max) column simply because it was a lot easier to setup via T-SQL than a varbinary. Here is the relevant script --

-- Setup the kickass example table
Create Table Tester
(
TestValue varchar(max
)
)

-- Insert a dummy row
Insert into Tester
(TestValue)
values
('Sahil Malik is so hot. He likes to give out samples like this for free. This particular sample he mentioned it in his book as a concept, but could not include the actual code in code download because adding this later would have completely screwed up listing numbers etc. So instead he has mentioned the concept in his book, and he is gonna put the code in his blog instead. Man he is just so hot what can I say. Plus of course I dont have to be as neat and clean on my blog as I have to be in the book, and I can use words like kickass and junky crap and bullshit .. you get the idea.'
)

-- Run the below updates to bloat the size, y'know so we can see the friggin' thing work on a dual core p4.
Update Tester set TestValue = (Select TestValue + TestValue + TestValue + TestValue from nvcmTable
)
Update Tester set TestValue = (Select TestValue + TestValue + TestValue + TestValue from nvcmTable
)
Update Tester set TestValue = (Select TestValue + TestValue + TestValue + TestValue from nvcmTable)

Great, now that the table is setup, ju gotta setup the windows application with a progress bar and a button that says "Load". Here's how that looks like -

Okay great, so the magic happens in the button click event of the Load Data button. The idea is, to first execute a query to find out the length of the data available - this is used to set the maximum value of the progress bar. This is done using the following code

// First Lets fix the progress bar maximum
cmd.CommandText = "Select len(TestValue) from Tester";
progressBar1.Maximum =
Convert
.ToInt32(cmd.ExecuteScalar());

Nice nice .. Next, comes the progressbar magic. Truly all I'm doing here is, using CommandBehavior.Sequential to read little by little, the little in sequential mode over here is hardcoded to 1 byte, because that gives me the most precision. It also makes my reading process slower BTW (because you are doing a bunch of stuff between reading). So this will have a negative affect on connection pooling performance (Lesson learnt: show progress bars only if you really really need to because they will keep the underlying connection open for a much longer time).

So anyway, Here is the full final code -

private void button1_Click(object sender, EventArgs e)
{
   using (SqlConnection myConn = new SqlConnection
(connectionString))
   {
      myConn.Open();
      SqlCommand
cmd = myConn.CreateCommand();
      // First Lets fix the progress bar maximum
      cmd.CommandText = "Select len(TestValue) from Tester";
      progressBar1.Maximum =
Convert
.ToInt32(cmd.ExecuteScalar());
      // Kickass !! Now lets go ahead and setup the bigass query we will use.
      cmd.CommandText = "Select TestValue from Tester";
      SqlDataReader myDataReader = cmd.ExecuteReader(CommandBehavior
.SequentialAccess);
      myDataReader.Read();
      // This bufferSize over here is my precision control.
      // Lower the bufferSize, slower the speed, but it's damn accurate
      // Cuz it's readin' byte by byte,
      //
Which obviously means that your conn. pooling performance now sucks - so don't overuse this ok?
      // If you do overuse this, I will have to request Bill Ryan give you a public butt waxing.
      int
bufferSize = 1; 
      char[] buf = new char
[bufferSize]; 
      int
startIndex = 0 ;
      // Lets Begin
      long
retval = myDataReader.GetChars(0, startIndex, buf, 0, bufferSize);
      while
(retval == bufferSize)
      {
         startIndex += bufferSize;
         retval = myDataReader.GetChars(0, startIndex, buf, 0, bufferSize);
         progressBar1.Value = startIndex;
      }
   myConn.Close();
}

Okay, so basically now when you run the application and hit "Load Data", you should see a progress bar that looks like this ---

One thing I wanna mention is that due to the internal logic how this works, for very large data read operations (naturally), you probably do want to keep a large buffer size. So calculate the bufferSize (precision) based on a formula that is a function of the max value of the progress bar. Secondly, if you were loading an image (you can actually now show it like a browser loads it), you would use the GetBytes method instead of GetChars.

For you ADHD folks who don't have the patience to actually understand and read all that I typed with such pains and love above, and want to get instant gratification by just copy pasting code, here is a code download location. (The TSQL is embedded in the code itself).

Sound off but keep it civil:

Older comments..


On 6/26/2007 8:10:26 AM kerem said ..
is there a way for seeing progress while uploading binary data to the server?


On 7/16/2007 8:37:37 AM Mike DiRenzo said ..
Good article. Funny wit. Thanks. Good luck.


-M


On 8/28/2007 5:21:16 AM Rams said ..
Great man, keep writing. It was your wit that made me (an ADHD guy) go through the entire stuff.