SQL Query + Progressbar

[ignnniter]

If anyone can help me , I wanted to know how to use a progressbar while the sql query is executing. Just a "Beg"inner in VB.net here's my code:

dataopen()
        cmd.Connection = con
        cmd.CommandText = "Select User_Name, userpass from Borrower where user_name='" & EmsTextBox1.Text & "' AND userpass = '" & EmsTextBox2.Text & "'"
        rd = cmd.ExecuteReader
        If rd.HasRows Then
            MsgBox("Login Success")
            Choice.Show()
        Else
            MsgBox("Invalid Username or Password")
        End If
        cmd.Dispose()
        con.Close() 

Thanks!

[Reverend Jim]

A progress bar is only useful if you know the endpoint. For example, if you know how long the process will take, or if you know how many iterations are necessary then you can display a progress bar indicating where you are in the process.

[cgeier]

You only seem to be retrieving no rows or 1 row, so it seems unnecessary. You could use a status label, instead to keep the user informed what is going on.

In order to use a progress bar above, you will need to first get the number of rows:

Select Count(*) from Borrower where user_name='" & EmsTextBox1.Text & "' AND userpass = '" & EmsTextBox2.Text & "'"

This query most likely only returns 0 or 1 rows.

Then run the query (above) to retrieve your data.

This would be more useful if you are retrieving many rows such as:

Select * from Borrower

So, you would first get the row count using:

    Select Count(*) from Borrower

Then, get your data:

    Select * from Borrower

Use a counter in your loop to keep track of the current row number. You know the total number of rows from the first query.

You would probably have to use a BackgroundWorker to accomplish this.

How to: Use a Background Worker

101 Samples for Visual Basic 2005
(In "Base Class Libraries - Group 2 Samples", see "WorkingWithThreads")

[Reverend Jim]

SQL queries return sets. You issue the query then wait for the response. There is no loop into which you can embed a progress bar update. Doing a SELECT COUNT to get the actual number of rows, then executing the query to get the data is a bad idea. The server has to process the tables to get the record count, then again to get the records. That makes your application half as responsive and puts an unnecessary burden on the DB server which could impact performance for other users.

[cgeier]

Requesting the data twice will increase the load on the server. To say that it will make your application half as responsive is an overstatement--the server querying the database is only part of the process (the part which you can't get the progress of). Performing a database query is like any other thing in life, there are costs and benefits. One must ask, if the cost is worth the benefit. You must also take into account the cost to the database, the end-user, and the other users. Providing reassurance to an end-user always increases the cost of performing the operation.

You could probably use a ProgressBar with Style = Continuous (if using a BackgroundWorker). It doesn't show any actual progress, but is more of an animated graphic that provides reassurance to the end-user.

[Reverend Jim]

I was referring only to the responsiveness of the query. In my opinion, a progressBar that does not reflect reality is worse than useless. Even in the best case, the users will learn to mistrust the application.