August 13, 2013 at 1:57 pm
hi all,
I have this rather generic function that executes a stored proc with a bunch of parameters:
protected ArrayList ExecSProcReturnAList(string storedProcName, SqlParameter[] parameters)
{
ArrayList result = new ArrayList();
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
try
{
connection.Open();
using (SqlCommand command = new SqlCommand(storedProcName, connection))
{
command.CommandTimeout = 200;
command.CommandType = System.Data.CommandType.StoredProcedure;
//add parameters
foreach (SqlParameter sqlpar in parameters)
{
command.Parameters.Add(sqlpar);
}
SqlDataReader sqldr;
sqldr = command.ExecuteReader();
while (sqldr.NextResult() == true)
{
ArrayList intermediaryResult = new ArrayList();
while (sqldr.Read())
{
ArrayList sqldrRow = new ArrayList();
for (int i = 0; i < sqldr.FieldCount; i++)
{
//Console.Write(sqldr.ToString());
sqldrRow.Add(sqldr);
}
intermediaryResult.Add(sqldrRow);
}
result.Add(intermediaryResult);
}
return result;
}
}
finally
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Dispose();
}
}
}
}
Every time I run this from top to bottom, the result array list is empty. When I put a breakpoint just at the while line, I can see the sql data reader, sqldr does have the expected data in it, however, it fails at the condition ... somehow sqldr.NextResult() == true is false and it jumps out of the loop.
Can anybody tell me why?
Thanks,
kowalsky
August 13, 2013 at 3:13 pm
I'm wondering if NextResult takes you to the next Result Set from the Reader. You probably only have one Result Set.
maybe you need to look at something like this or close to it:
do
{
if (sqldr.HasRows)
{
while (sqldr.Read())
{
Hope it helps
August 13, 2013 at 5:06 pm
Thank you very much,
I did not realize I have TWO loops - one that loops through the results, one that gets the rows of each result.
And I have only one result ...
Thanks again,
kowalsky
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply