Not getting all results from ExecuteReader and Read()

Question

I am running a C# application with System.Data.SqlClient. My code is not retrieving all of the rows it should. In its simplest forms it is:

class SalesDatabase()
{

protected SqlConnection m_database; // opened on class creation

...

     public bool GetPurchaseOrder(String SalesOrderID, out PurchaseOrder po)
     {
     ...

     String QueryString = "SELECT SalesOrderID from sysdba.SalesOrder WHERE " +
                          "(SalesOrderID='" + SalesOrderID + "')";
      // OR
     String QueryString = "SELECT SalesOrderID from sysdba.SalesOrder WHERE " +
              "(SalesOrderID LIKE '" + SalesOrderID.Substring(0,8) + "%')'";


     SqlCommand SelectCommand = new SqlCommand(QueryString, m_database);
     SqlDataReader results = SelectCommand.ExecuteReader();

     bool bRetVal = results.Read();
     // OR
     while (results.Read()) push results[0] into a List<String>
     ...
     }
...
}

In order to debug the equality query, which returned false on Read(), I tried using the LIKE query and pushing the results into a List. What I found was that if I copy/pasted the LIKE query from SelectCommand._commandText into MS SQL 2005 it would return about 8600 rows in MS SQL 2005 but only about 2300 in my C# program.

Similarly, the equality query always finds the item in MS SQL but only finds it in my C# code if it is part of these 2300 or so records. Apparently it doesn't want to return all of the results, but only the first several thousand rows, and it behaves like this whether I'm asking for 10000 rows (LIKE clause) or 1 row (equality).

What should I do?

What is the exact value of `SalesOrderID` in the code and what is it's type in the `SalesOrder` table? — juharr, Oct 22 '14 at 16:02
Please look into parameterising your queries to avoid sql injection attacks. — FJT, Oct 22 '14 at 16:04
Are you 100% sure that your connection string is using the same database as when you use SSMS? — FJT, Oct 22 '14 at 16:06
Hello. Fiona is correct, during our recent activities the database was copied and the original server was not retired so I was working against the original database in my C# code which did not contain the new entries. The original code was parametrized but I decided to simplify it to text only. I reverted to the original code. — Michael Lewchuk, Oct 22 '14 at 16:14

score 0 · Answer 1 · answered Oct 22 '14 at 16:29

Try doing this to make yout SQL statement:

String QueryString = "SELECT SalesOrderID from sysdba.SalesOrder WHERE SalesOrderID = @SalesOrderID";
SelectCommand.Parameters.Add("@SalesOrderID", SalesOrderID);

And this to save rows into a list:

List<string> list = new List<string>();
    while(results.Read()){
    list.Add(results["SalesOrderID"]);
    }

Not getting all results from ExecuteReader and Read()

1 Answers1