We help IT Professionals succeed at work.

SqlDataReader Looping Problem?

kvnsdr
kvnsdr asked
on
615 Views
Last Modified: 2012-06-21
The following code loops only through the first part. It needs to make a decision based on whether or not data is present at  if (dr.HasRows) . I'm certain there in not any data yet the ELSE will never run. I do know the code doing work does work if I bypass the first part..........

while(true)
{
          try
          {
                    string Sql = " SELECT DISTINCT User FROM tbl_Employees
                    cn = new SqlConnection(ConfigurationManager.AppSettings["ApCn"]);
                    SqlDataAdapter da = new SqlDataAdapter(Sql, cn);
                    cn.Open();
                    dr = da.SelectCommand.ExecuteReader();
                                   
                    while (dr.Read())
                    {                      
                        if (dr.HasRows)
                        {
                             // do work here
                        }
                        else
                       {
                                string SQL = " SELECT DISTINCT User FROM tbl_Employees
                                cn = new SqlConnection(ConfigurationManager.AppSettings["ApCn"]);
                                SqlDataAdapter da = new SqlDataAdapter(SQL, cn);
                                cn.Open();
                                dr = da.SelectCommand.ExecuteReader();
                                   
                                while (dr.Read())
                                {                      
                                     if (dr.HasRows)
                                     {
                                           // do work here
                                     }
                          }
          }
          catch
          {
          }
          finally
          {
                dr.Close();
                cn.Close();
          }

                           
Comment
Watch Question

There are a couple of problems here.  while(true) will make it loop forever, and you're redefining dr within a loop that reads it.
The comments from Proactivation are correct.

Also, the main problem is that if there is not data it will not get to the if statement inside your while statement.

No data means the dr.read() will return false if there are no records and not enter the loop.

Hope this helps.
Did you accidentally paste the same piece of code in twice?  Everything between string Sql... and the end of the if (dr.HasRows){} block is duplicated identically.

Author

Commented:
Correct on all accounts. The 'While' causes the class.cs to run like a thread, forever looping. Which was not a problem until I wanted to add the second block of code. The idea of looping is what I need. However, the loop needs a condition that seems to elude me..........

Q. Whats the solution?
Without knowing what the code is meant to achieve, it's hard to say.  All I can tell is it loops permanently with nothing to ever break that loop or anything to release resources to the system to handle its own events, and constantly hammers SQL for the same list of employers.  I assume it's supposed to monitor the table until an employee is inserted, but it'll lock te CPU at 100% until one is.  If that's the case, there are better ways to fire some code on an insert, such as a SQL trigger.
I would be glad to provide a solution if you tell me what condition you are trying to test for?

If I understand what you are trying to do then the fix is this.  Get rid of the if/else.  Leave the code for the if(true) in the loop body of while(dr.read()) and place the "else" logic after the while loop.

So, if there are records returned the while loop will process them one record at a time.  When it is done or if there are no records it will exit.  Then you can execute your code to get more data.

Are we on track here?

Author

Commented:
As I read through the code it makes sense to me, but there is a problem on the condition of the if (dr.HasRows) ..

To me, if(dr.HasRows) equals NO (False) then go on to the ELSE and work in that code block.

The condition is: Check for any data to be processed in the SQL datatable, if none is there, then go to the ELSE code......
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Each datareader reads data from a seperate SQL table. The idea is to continually poll the first table. If data is found then process it. If no data is found then goto the second datareader and do the same. The first datareader always has high priority, so the second datareader will sit idle most of the time. There will be times where the first datareader will sit idle too. Then the second will do some work.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.