Using a progressbar when loading SQL-data

Using a progressbar when loading SQL-data

• AlexReg

  You can do something like this....

  private System.Windows.Forms.ProgressBar prgInsertStatus;

  private System.Windows.Forms.Label lblLoadDesc;

  int nLoopCtr = 1;

  // Read the table first or all the lines coming from the database......

  // This is how you read from file, this works pretty similar to Database

  private int CountFileLines()

  {

  int nLineCount = 0;

  string strLine = "";

  FileStream file = new FileStream("some.txt", FileMode.Open, FileAccess.Read);

  StreamReader sr = new StreamReader(file);

  while((strLine = sr.ReadLine()) != null)

  {

  nLineCount++;

  }

  sr.Close();

  file.Close();

  return nLineCount;

  }

  // Then in your code

  //setup the progress bar

  prgInsertStatus.Maximum = CountFileLines();

  prgInsertStatus.Minimum = 0;

  prgInsertStatus.Step = 1;

  // Your code here

  // do it in a loop

  lblLoadDesc.Text = "Record " + nLoopCtr + " of " + (prgInsertStatus.Maximum).ToString();

  prgInsertStatus.PerformStep();

  nLoopCtr++;

  // loop ends

  Thanks,

  Harsimrat

  
  

• Sweeps78

  TaylorMichaelL is right you will have to use an SQL data reader to read in the query results segment by segment. Although I am not entirely sure how using this method you get the maximum value for the progress bar.
  To update the progress bar you will need to use thread safe calls through a delegate procedure. It's easier then it sounds and you can read up about it here: http://msdn2.microsoft.com/en-us/library/ms229730.aspx (With example code)
  
  Also as I mentioned about you will need an SQL data reader to get those results in line by line and that can be found here: http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx
  
  Not perfect but the only way I know of doing what you want. Good luck.
  
  
  

• BillyBee

  AFAIK there are no events or other methods called while the data is being transferred. It really depends on how you are loading the data though. If you are using a DataReader then the data is not actually transferred until you call Read(). Each time you call it the next batch of data is sent.

  Therefore if you wanted to provide a semblance of progress notification you would load the data using a DataReader and then each time you called Read() increment some progress indicator indicating the status. Unfortunately I believe the total number of rows available is not set until the reader is closed. Therefore in order to get the # of rows being returned you'd first have to send a separate query to get the count. Then you could query for the data. It isn't perfect but it works. Alternatively if your DB supports it then you could return multiple record sets (MARS). The first set would be the count and the subsequent set would be the data. This would give you a more accurate count.

  If you are using a DataSet then you could hook into the row changing events on the data table you are populating. This could be iffy though as I'm not sure if you can hook into the table early enough to get notified when it is populated. Finally also note that you still won't know how much data is being returned.

  Personally I don't bother tracking the database stuff. Instead I use the BackgroundWorker component to asynchronously load the data. Then I use a progress indicator (if desired) to load the data into business objects or the UI. Not perfect but it works.

  Michael Taylor - 7/31/06

  
  

• Olley

  I've tried both solutions out and due the multi-tier environment none seems to work. :)
  At last I used the backgroundworker and that is quite easy indeed. But because I load my data in The DataAccessLayer(DAL) i can never do

  Backgroundworker.instance.ReportProgress(i);
  

  Is there a way to give that value to the PresentationLayer.
  
  Grtz
  
  Annihil8

  
  
• Using a progressbar when loading SQL-data