SqlDataReader returning incorrect results

Question

I have an issue with this code in my MSSQL Data Access manager. Sometimes the returned records are not the expected ones. The DataReader columns come from the good table but the fields seems to become from another request.
This behavior appears when I got lot of simultaneous requests on the same table and the result is moreless like 'select A retrun B, select B return D, select C retrun Z,...'

 public bool Read(string ID)
    {

        bool _return = false;

        System.Data.SqlClient.SqlConnection Connection = new System.Data.SqlClient.SqlConnection(ConnectionString);

        try
        {

            string Query = "Select * From Sounds Where ID = '" + ID + "'";

            System.Data.SqlClient.SqlDataReader DataReader;


            using (System.Data.SqlClient.SqlCommand Command = new SqlCommand())
            {
                Command.Connection = Connection;
                Command.CommandText = Query;

                Connection.Open();

                DataReader = Command.ExecuteReader();
            }
            if (DataReader.Read())
            {
                FillClass(DataReader);
                _return = true;
            }
            else
            {
                _return = false;
            }

            if (! DataReader.IsClosed)  DataReader.Close();

        }
        catch (Exception e)
        {
            _return = false;
        }
        finally
        {
            if ((Connection != null) && (Connection.State == ConnectionState.Open)) {
                Connection.Close();
            }
        }


        return _return;
    }

I don't understand SQL 2012 return a bad record or the .Net CLR mismatch the SQL pipes...

Thank for help. -Alex

First things to fix: a) use parameterized SQL; b) stop swallowing exceptions without reporting them; c) stop catching bare Exception; d) use using statements to dispose of the connection, command and reader. — Jon Skeet
– Jon Skeet, Commented Jun 24, 2013 at 9:16
calling if (DataReader.Read()) advances the reader to 1 record. Then you call FillClass(DataReader);. If you call DataReader.Read() again inside FillClass, you will miss the first record. Check that you don't have that mistake. — Khanh TO
– Khanh TO, Commented Jun 24, 2013 at 9:18
I would be very interested in seeing what the calling code looks like here; I wonder if the problem is at the caller (i.e. sharing state) rather than in the data access — Marc Gravell
– Marc Gravell, Commented Jun 24, 2013 at 9:30
That FillClass method is just a binding method to load datareader content to derivated class poperties (i.e. this.foo = DataReader("foo")) — Alex
– Alex, Commented Jun 24, 2013 at 13:39

Marc Gravell · Accepted Answer · 2013-06-24 09:26:32Z

It is staggeringly unlikely that you're seeing a new never-seen-before issue on command / result mismatches; more likely, your code is simply getting something wrong; the first thing to do is to simplify (and fix a lot of the problems):

public bool Read(string ID)
{
    using(var connection = new SqlConnection(ConnectionString))
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "select * from Sounds where ID=@ID";
        command.Parameters.AddWithValue("ID",ID);
        Connection.Open();
        using(var reader = command.ExecuteReader())
        {
            if(reader.Read()
            {
                FillClass(reader);
                return true;
            }
            return false;
        }
    }
}

The next interesting question is "what does FillClass look like?".

The next interesting question is "what does the calling code look like? - is there any chance you are simply incorrectly sharing state between requests, for example by using static in a web application?".

The final interesting question is: could this just use something like dapper (which simplifies ADO.NET access, parameterizes, and automatically maps properties/fields by column name):

Foo GetFoo(string id)
{
    using(var connection = new SqlConnection(ConnectionString))
    {
        return connection.Query<Foo>(
            "select * from Sounds where ID=@id", new { id }).SingleOrDefault();
    }
}

the less code you write, the less chance there is to inject unnecessary mistakes...

Alex · Accepted Answer · 2013-06-25 09:02:03Z

there is no static in this part of the application. This read method is a part of the base class who load object from the database.

I implement your piece of code in my application and put on my server. The problem still remain...

Here is a part of the code who call the read to the database:

public class program
{
    public static Toolbox toolbox = new Toolbox();

    public void main()
    {
        // ....

        var Duration = toolbox.GetFileDuration(File);

        // ...
    }

    //...
}


public class Toolbox
{
    //....

     public long GetFileDuration (string FileName){

         Repository.dbSound Sound = new Repository.dbSound(ConnectionString);
         if (Sound.Read(FileName))
         {
             return Sound.Duration;
         }
         else
         {
             return -1;
         }

    }

    //....

}

I the other hand, I add logging in the method to get the ID send and the ID returned in the datareader and I got this in the logfile:

07920-04.mp3 --> Datareader.Read() FAILED
07747-12.mp3 --> 07920-04.mp3

the both file exist in the table !!!

Thank for help.

Collectives™ on Stack Overflow

SqlDataReader returning incorrect results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related