CODEWITHSUNDEEP
sql-serverado.netsqldatareader
user800576
user800576

Reputation:

SqlDataReader.Read Not Working As Advertised?

So I'm running a query in ADO.NET that I know returns a single row. The relevant portion of my code looks like:

SqlCommand sqc = new SqlCommand();
sqc.Connection = new SqlConnection("connection string");
sqc.CommandText = "SELECT A, B FROM C WHERE D=@d";
sqc.Parameters.AddWithValue("@d", "d");
sqc.Connection.Open();
SqlDataReader rdr = sqc.ExecuteReader();
bool boolio = rdr.Read();
String a = (String)rdr["A"];

When I run this, I get an exception on the final line, complaining that I'm trying to read where there's no data.

When I step through this in VS I inspect rdr before the rdr.Read() line executes and I can see the data I want is sitting in the reader. Then I call rdr.Read() and the result is FALSE, indicating there is no more data for me to read. When I inspect rdr again, the internal ResultsView is empty.

I realize I can get my data simply by discarding the call to Read() BUT: this behaviour is contrary to the MSDN documentation which explicitly says "The default position of the SqlDataReader is before the first record. Therefore, you must call Read to begin accessing any data.". It is also contrary to every example of SqlDataReader usage I've found on the web such as this SO question which seems to exist only to taunt me.

Note that I've tested my code against .NET 3.5 and .NET 4.5 with identical results.

Am I missing something? Is there a bug in ADO.NET? Any input is appreciated!

UPDATE 1: The actual exception I get on the last line is:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: Invalid attempt to read when no data is present.

UPDATE 2:

Because Nathan Skerl was unable to reproduce this behavior, and posted verified working code which subsequently failed for me, I suspect there may be an issue with the platforms we're running on. For the record I am using Windows 7 Professional (64-bit) Service Pack 1 and have compiled my project against .NET Framework 3.5 and 4.5 with the same results. If there is any relevant information I have failed to include please let me know and I will add it.

Upvotes: 0

Views: 2575

Answers (2)

scorpiophd
scorpiophd

Reputation: 105

I have had the exact same problem trying to read-in 1 row, returned from a SP, called using ADO SQLCommand from Microsoft Dynamics 2013R2.

Specifically, I use this C/AL code to call my SP:

SQLCommand := SQLCommand.SqlCommand();
SQLCommand.CommandText := 'Custom_Item_Card_report_Get_Item_Qties'; //name of SP
SQLCommand.Connection := SQLConnection;
SQLCommand.CommandType := SQLCommand.CommandType.StoredProcedure;
SQLCommand.CommandTimeout := 0;

IF SQLCommand.Parameters.Count = 0 THEN BEGIN
SQLCommand.Parameters.AddWithValue('@CompanyName', COMPANYNAME);
SQLCommand.Parameters.AddWithValue('@ItemNo', InputItemCode);
SQLCommand.Parameters.AddWithValue('@FromDate', InputDateFrom);
SQLCommand.Parameters.AddWithValue('@Location', InputLocation);
END;

SQLReader := SQLCommand.ExecuteReader;
//IF SQLReader.Read() THEN BEGIN
rs1QtyCumulative := SQLReader.Item('QtyCumulative');
MESSAGE(FORMAT(rs1QtyCumulative));
//END;

Now, in my result-set I have just 2 rows (, by 3 columns decimal). Row 0 and row 1, let's call them.

And as stated in this issue, without the IF enclosure, it will give me the message with data from row 1, column 1 coming from the result-set of the SP.

With the IF and the .Read() it will give an error stating that a command to read data was issued where none exists. It is refering to the line of code before the MESSAGE.

SQLReader.Item('QtyCumulative');

This, as stated in this issue, constitutes a contradiction to the documentation that clearly states that the .ExecuteReader() will leave the SQLReader collection pointing to the row before the start of the data after it executes.

This most certainly does not happen and on top of that, if we do call the .Read() method for the SQLReader object, immediately after the call to .ExecuteReader() then it will move to the second row of the result-set.

Clearly they have some debugging to do with SqlDataReader objects.

Upvotes: 0

nathan_jr
nathan_jr

Reputation: 9302

UPDATE: After some troubleshooting we realized the reader was empty due to inspecting the reader in the debugger by expanding the Results View (which calls to Read() and consumes the rows).

Using your code I ran this unit test in Linqpad and the results were as expected, the initial reader.read() advances to row "ONE", and the second call to row "TWO". Can you reproduce your error with a test such as this:

SqlCommand sqc = new SqlCommand();
sqc.Connection = (SqlConnection)this.Connection;
sqc.CommandText = "SELECT 'ONE' [A] union all select 'TWO' order by [A] asc;";
sqc.Parameters.AddWithValue("@d", "d");
sqc.Connection.Open();
SqlDataReader rdr = sqc.ExecuteReader();

bool boolio = rdr.Read();
String a = (String)rdr["A"];
a.Dump();

rdr.Read();
String b = (String)rdr["A"];
b.Dump();

Can you also show us what this pattern returns:

using (SqlConnection connection = (SqlConnection)this.Connection)
using (SqlCommand sqc = new SqlCommand("select 'ONE' as [A] union all select 'TWO';", connection))
{
   connection.Open();

   using (SqlDataReader rdr = sqc.ExecuteReader())
   {
       while (rdr.Read())
       {
          Console.WriteLine(String.Format("{0}", (String)rdr["A"]));
       }
   }
}

Upvotes: 2

Related Questions