469,275 Members | 1,871 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

nested SqlDataReader.Read() problem

3
I am using 2 SqlDataReader and while(dr.Read()) get only first record and loop stops. its not retriving all of the records but if I comment
db1.RunSQLStatement(sql1,out unit_dr);
while (unit_dr.Read())
these statements then outer loop gives all the records. could you please help me how can while(dr.Read()) loop executes untill end of file.


SqlDataReader dr = null;
db.RunSQLStatement(sql,out dr);

while(dr.Read())
{
.....
.....
sql1="select * from table where scheduleid="+dr.GetValue(0).ToString();
db1.RunSQLStatement(sql1,out unit_dr);
while (unit_dr.Read())
{
....
}
}
Jun 14 '07 #1
4 8395
TRScheel
638 Expert 512MB
I am using 2 SqlDataReader and while(dr.Read()) get only first record and loop stops. its not retriving all of the records but if I comment
db1.RunSQLStatement(sql1,out unit_dr);
while (unit_dr.Read())
these statements then outer loop gives all the records. could you please help me how can while(dr.Read()) loop executes untill end of file.


SqlDataReader dr = null;
db.RunSQLStatement(sql,out dr);

while(dr.Read())
{
.....
.....
sql1="select * from table where scheduleid="+dr.GetValue(0).ToString();
db1.RunSQLStatement(sql1,out unit_dr);
while (unit_dr.Read())
{
....
}
}
First off, I'd seperate the calls. If one of them fails, you could have catastrophic failure with unknown results from either calls.

Second off, I would use stored procedures. I dont know where you are implementing this code, but a few good coding practices include avoiding embedded strings and avoiding SQL Injections. Having a string like you do above can result in a SQL Injection depending on where it is used and how exposed it is. Using stored procedures is safer (not perfect mind you, but a lot safer). Good use of permissions should close most of those gaps.

Here is the function from a library I wrote at work that I use for SQL Data Reader requests:

Expand|Select|Wrap|Line Numbers
  1.             public static SqlDataReader SqlDataReaderRequest(string storedProcedureName, string connectionString,
  2.                 SqlParameter[] parameters)
  3.             {
  4.                 SqlConnection connection = null;
  5.                 SqlCommand command = null;
  6.                 IAsyncResult ar = null;
  7.  
  8.                 try
  9.                 {
  10.                     using (connection = new SqlConnection(connectionString))
  11.                     {
  12.                         using (command = new SqlCommand(storedProcedureName, connection))
  13.                         {
  14.                             command.CommandType = CommandType.StoredProcedure;
  15.                             if (parameters != null)
  16.                                 command.Parameters.AddRange(parameters);
  17.                             command.Connection.Open();
  18.  
  19.                             ar = command.BeginExecuteReader();
  20.  
  21.                             return command.EndExecuteReader(ar);
  22.                         }
  23.                     }
  24.                 }
  25.                 catch
  26.                 {
  27.                     if (command != null)
  28.                         command.Dispose();
  29.  
  30.                     if (connection != null)
  31.                     {
  32.                         connection.Close();
  33.                         connection.Dispose();
  34.                     }
  35.  
  36.                     return null;
  37.                 }
  38.             }
  39.  

EDIT: If you want more of the SQL portion of the library, I can post that for you
Jun 14 '07 #2
nateraaaa
663 Expert 512MB
I am using 2 SqlDataReader and while(dr.Read()) get only first record and loop stops. its not retriving all of the records but if I comment
db1.RunSQLStatement(sql1,out unit_dr);
while (unit_dr.Read())
these statements then outer loop gives all the records. could you please help me how can while(dr.Read()) loop executes untill end of file.


SqlDataReader dr = null;
db.RunSQLStatement(sql,out dr);

while(dr.Read())
{
.....
.....
sql1="select * from table where scheduleid="+dr.GetValue(0).ToString();
db1.RunSQLStatement(sql1,out unit_dr);
while (unit_dr.Read())
{
....
}
}
You will need to close your first reader before running a while loop on your second reader. The first reader is losing access to the database after the 1st iteration and then the 2nd reader continues reading until it has read all rows.

Expand|Select|Wrap|Line Numbers
  1. SqlDataReader reader1;
  2. SqlDataReader reader2;
  3.  
  4. while(reader1.Read())
  5. {
  6. //do something
  7. }
  8. reader1.Close()
  9.  
  10. while(reader2.Read())
  11. {
  12. //do something
  13. }
  14. reader2.Close();
Nathan
Jun 14 '07 #3
nayla
3
Thanks for your help. I understand it in better way now.
Nayla.


First off, I'd seperate the calls. If one of them fails, you could have catastrophic failure with unknown results from either calls.

Second off, I would use stored procedures. I dont know where you are implementing this code, but a few good coding practices include avoiding embedded strings and avoiding SQL Injections. Having a string like you do above can result in a SQL Injection depending on where it is used and how exposed it is. Using stored procedures is safer (not perfect mind you, but a lot safer). Good use of permissions should close most of those gaps.

Here is the function from a library I wrote at work that I use for SQL Data Reader requests:

Expand|Select|Wrap|Line Numbers
  1.             public static SqlDataReader SqlDataReaderRequest(string storedProcedureName, string connectionString,
  2.                 SqlParameter[] parameters)
  3.             {
  4.                 SqlConnection connection = null;
  5.                 SqlCommand command = null;
  6.                 IAsyncResult ar = null;
  7.  
  8.                 try
  9.                 {
  10.                     using (connection = new SqlConnection(connectionString))
  11.                     {
  12.                         using (command = new SqlCommand(storedProcedureName, connection))
  13.                         {
  14.                             command.CommandType = CommandType.StoredProcedure;
  15.                             if (parameters != null)
  16.                                 command.Parameters.AddRange(parameters);
  17.                             command.Connection.Open();
  18.  
  19.                             ar = command.BeginExecuteReader();
  20.  
  21.                             return command.EndExecuteReader(ar);
  22.                         }
  23.                     }
  24.                 }
  25.                 catch
  26.                 {
  27.                     if (command != null)
  28.                         command.Dispose();
  29.  
  30.                     if (connection != null)
  31.                     {
  32.                         connection.Close();
  33.                         connection.Dispose();
  34.                     }
  35.  
  36.                     return null;
  37.                 }
  38.             }
  39.  

EDIT: If you want more of the SQL portion of the library, I can post that for you
Jun 14 '07 #4
nayla
3
Thanks Nathan. this what i have to its working now.

Nayla.

You will need to close your first reader before running a while loop on your second reader. The first reader is losing access to the database after the 1st iteration and then the 2nd reader continues reading until it has read all rows.

Expand|Select|Wrap|Line Numbers
  1. SqlDataReader reader1;
  2. SqlDataReader reader2;
  3.  
  4. while(reader1.Read())
  5. {
  6. //do something
  7. }
  8. reader1.Close()
  9.  
  10. while(reader2.Read())
  11. {
  12. //do something
  13. }
  14. reader2.Close();
Nathan
Jun 14 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Don Low | last post: by
reply views Thread by Pascal Délisle | last post: by
6 posts views Thread by Licheng Fang | last post: by
78 posts views Thread by Josiah Manson | last post: by
2 posts views Thread by rn5a | last post: by
9 posts views Thread by notahipee | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.