SqlDataReader does not work - does not read data

Question

I have a SqlDataReader, but it never enters into Read().

When I debug it, it pass the loop while(readerOne.Read()). It never enter into this loop even though there is data.

public static List<Pers_Synthese> Get_ListeSynthese_all(string codeClient, DateTime DateDeb, DateTime DateFin)
{
   try
   {
      using (var connectionWrapper = new Connexion())
      {
         var connectedConnection = connectionWrapper.GetConnected();

         string sql_Syntax = Outils.LoadFileToString(Path.Combine(appDir, @"SQL\Get_ListeSynthese_All.sql"));

         SqlCommand comm_Command = new SqlCommand(sql_Syntax, connectionWrapper.conn);
         comm_Command.Parameters.AddWithValue("@codeClioent", codeClient);
         comm_Command.Parameters.AddWithValue("@DateDeb", DateDeb);
         comm_Command.Parameters.AddWithValue("@DateFin", DateFin);

         List<Pers_Synthese> oListSynthese = new List<Pers_Synthese>();

         SqlDataReader readerOne = comm_Command.ExecuteReader();

         while (readerOne.Read())
         {
            Pers_Synthese oSyntehse = new Pers_Synthese();
            oSyntehse.CodeTrf = readerOne["CODE_TARIF"].ToString();
            oSyntehse.NoLV = readerOne["NOID"].ToString();
            oSyntehse.PrxUnitaire = readerOne["PRIX_UNITAIRE"].ToString();
            oSyntehse.ZoneId = readerOne["LE_ZONE"].ToString();
            oSyntehse.LeZone = readerOne["LIB_ZONE"].ToString();
            oSyntehse.LeDept = readerOne["DEPT"].ToString();
            oSyntehse.LeUnite = readerOne["ENLEV_UNITE"].ToString();
            oSyntehse.LePoids = Convert.ToInt32(readerOne["POID"]);
            //oSyntehse.LePoidsCorr = Convert.ToInt32(readerOne["POID_CORR"]);
            oSyntehse.LeColis = readerOne["NBR_COLIS"].ToString();
            oSyntehse.LeCr = readerOne["NBR_CREMB"].ToString();
            oSyntehse.SumMontantCR = readerOne["ENLEV_CREMB"].ToString();
            oSyntehse.LeVd = readerOne["NBR_DECL"].ToString();
            oSyntehse.SumMontantVD = readerOne["ENLEV_DECL"].ToString();
            oSyntehse.LePrixHT = readerOne["PRIX_HT"].ToString();
            oSyntehse.LePrixTTC = readerOne["PRIX_TTC"].ToString();
            oSyntehse.TrDeb = readerOne["TR_DEB"].ToString();
            oSyntehse.TrFin = readerOne["TR_FIN"].ToString();

            oListSynthese.Add(oSyntehse);
         }
         readerOne.Close();
         readerOne.Dispose();
         return oListSynthese;
      }
  }
  catch (Exception excThrown)
  {
     throw new Exception(excThrown.Message);
  }
}

When I debug it with SQL Server profiler it shows the data....that meant the data is not empty, but it never enter into this loop.

while (readerOne.Read())
{

by the way my connection class:

 class Connexion : IDisposable 
    {
        public SqlConnection conn;
        public SqlConnection GetConnected()
        {
            try
            {
                string strConnectionString = Properties.Settings.Default.Soft8Exp_ClientConnStr;
                conn = new SqlConnection(strConnectionString);
            }
            catch (Exception excThrown)
            {
                conn = null;
                throw new Exception(excThrown.InnerException.Message, excThrown);
            }

            // Ouverture et restitution de la connexion en cours
            if (conn.State == ConnectionState.Closed) conn.Open();
            return conn;
        }

        public Boolean IsConnected
        {
            get { return (conn != null) && (conn.State != ConnectionState.Closed) && (conn.State != ConnectionState.Broken); }
        }

        public void CloseConnection()
        {
            // Libération de la connexion si elle existe
            if (IsConnected)
            {
                conn.Close();
                conn = null;

            }

        }

        public void Dispose()
        {
            CloseConnection();
        }
    }

and my SQL Statement:

exec sp_executesql N'SELECT CODE_TARIF,PRIX_UNITAIRE,TR_DEB,TR_FIN,LE_ZONE,T_TARIF_ZONE.LIBELLE as LIB_ZONE,
  SUBSTRING(CP_DEST,1,2) as DEPT,T_UNITE.LIBELLE as ENLEV_UNITE,
  count(NOID)as NOID,
   SUM(CASE WHEN POID_CORR IS NOT NULL THEN POID_CORR ELSE POID END) as POID,sum(NBR_COLIS)as NBR_COLIS,COUNT(NULLIF(ENLEV_CREMB,0))as NBR_CREMB, sum(ENLEV_CREMB)as ENLEV_CREMB,COUNT(NULLIF(ENLEV_DECL,0))as NBR_DECL,sum(ENLEV_DECL)as ENLEV_DECL,sum(PRIX_HT)as PRIX_HT,sum(PRIX_TTC)as PRIX_TTC, sum (POID_CORR)as POID_CORR
  FROM LETTRE_VOIT_FINAL
   LEFT JOIN T_TARIF_ZONE ON LETTRE_VOIT_FINAL.LE_ZONE = T_TARIF_ZONE.NO_ID
  LEFT JOIN T_UNITE ON LETTRE_VOIT_FINAL.ENLEV_UNITE = T_UNITE.NO_ID
  where code_client = @codeClioent
   and DATE_CLOTUR_REEL BETWEEN @DateDeb AND @DateFin
   and STATUT_LV = 2

 group by CODE_TARIF,PRIX_UNITAIRE,TR_DEB,TR_FIN,LE_ZONE,T_TARIF_ZONE.LIBELLE,SUBSTRING(CP_DEST,1,2),T_UNITE.LIBELLE
 order by LE_ZONE,PRIX_UNITAIRE

',N'@codeClioent nvarchar(8),@DateDeb datetime,@DateFin datetime',@codeClioent=N'17501613',@DateDeb='2013-06-05 00:00:00',@DateFin='2013-06-05 23:59:00'

it return the data on SQL profiler:

my real query :

SELECT CODE_TARIF,PRIX_UNITAIRE,TR_DEB,TR_FIN,LE_ZONE,T_TARIF_ZONE.LIBELLE as LIB_ZONE,
  SUBSTRING(CP_DEST,1,2) as DEPT,T_UNITE.LIBELLE as ENLEV_UNITE,
  count(NOID)as NOID,
   SUM(CASE WHEN POID_CORR IS NOT NULL THEN POID_CORR ELSE POID END) as POID,sum(NBR_COLIS)as NBR_COLIS,COUNT(NULLIF(ENLEV_CREMB,0))as NBR_CREMB, sum(ENLEV_CREMB)as ENLEV_CREMB,COUNT(NULLIF(ENLEV_DECL,0))as NBR_DECL,sum(ENLEV_DECL)as ENLEV_DECL,sum(PRIX_HT)as PRIX_HT,sum(PRIX_TTC)as PRIX_TTC, sum (POID_CORR)as POID_CORR
  FROM LETTRE_VOIT_FINAL
   LEFT JOIN T_TARIF_ZONE ON LETTRE_VOIT_FINAL.LE_ZONE = T_TARIF_ZONE.NO_ID
  LEFT JOIN T_UNITE ON LETTRE_VOIT_FINAL.ENLEV_UNITE = T_UNITE.NO_ID
  where code_client = @codeClioent
   and DATE_CLOTUR_REEL BETWEEN @DateDeb AND @DateFin
   and STATUT_LV = 2

 group by 

    CODE_TARIF,PRIX_UNITAIRE,TR_DEB,TR_FIN,LE_ZONE,T_TARIF_ZONE.LIBELLE,SUBSTRING(CP_DEST,1,2),T_UNITE.LIBELLE
     order by LE_ZONE,PRIX_UNITAIRE

it is strange....when the data is between :

DATE_CLOTUR_REEL BETWEEN '2013-06-05 00:00:00' and '2013-06-05 23:59:00'

but

DATE_CLOTUR_REEL BETWEEN '2013-06-01 00:00:00' and '2013-06-05 23:59:00'

it works.

Answer 1

This is the way it should be. You are not doing the connection.Open() Also set up the connection string.

   private static void ReadOrderData(string connectionString)
    {
        string queryString =
            "SELECT OrderID, CustomerID FROM dbo.Orders;";

        using (SqlConnection connection =
                   new SqlConnection(connectionString))
        {
            SqlCommand command =
                new SqlCommand(queryString, connection);
            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            // Call Read before accessing data. 
            while (reader.Read())
            {
                ReadSingleRow((IDataRecord)reader);
            }

            // Call Close when done reading.
            reader.Close();
        }
 }

The perfect example of how to do it belongs to MSDN - Microsoft Website

NOTICE:

        SqlCommand command =
            new SqlCommand(queryString, connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

1. Create the SqlCommand
2. then open the connection

You are doing it the other way, you open it and then create the command.

I also don't see where you set the query string, I just see that you add the parameters; are you missing it?

Answer 2

This is perhaps not the answer you're looking for, but your code sample exhibits a number of bad coding practices that are easy to fall into due to ADO.NET's poor API design. Rather than manually do all this sql-to-.net conversion, you should use a library that does this for you.

It's easier to avoid bugs when you're not using a bug-prone API.

I recommend PetaPoco - it's easier to use than your current code, and has virtually no overhead (and given your example, is probably faster). There are many other alternatives, however.

Issues with your code sample:

• Improperly disposed objects: you aren't disposing SqlCommand and SqlDataReader properly. You possibly aren't disposing connections either (but that depends on Connexion internals).
• Using .ToString rather than type-safe casts. You should never extract data from an SqlDataReader like that because it undermines the whole point of the type system, and it's slow to boot. (PetaPoco or something similar will help a lot here)
• You're discarding stack traces on error due to the (pointless) try-catch. That just makes your code less readable and harder to debug. Don't catch unless you have have.
• Keeping your query away from the code - your code is tightly coupled to the query, and this separation just makes it hard to keep them in sync. Also, loading from the filesystem each and everytime you query is slow and opens up unnecessary filesystem-related failure modes such as locking, max path lengths, and permissions. This is probably the source of your bug - your query probably doesn't do what you think it does.

With PetaPoco or something similar, your entire function would look something like this:

public static List<Pers_Synthese> Get_ListeSynthese_all(
                string codeClient, DateTime DateDeb, DateTime DateFin) {
    var db = new PetaPoco.Database("Soft8Exp_ClientConnStr");

    //you should probably not be storing a query in a file.
    //To be clear: your query should not be wrapped in exec sp_executesql,
    //ADO.NET will do that for you.
    string sql_Syntax = Outils.LoadFileToString(
        Path.Combine(appDir, @"SQL\Get_ListeSynthese_All.sql"));

    //You'll need to rename Pers_Synthese's properties to match the db,
    // or vice versa, or you can annotate the properties with the column names.
    return db.Fetch<Pers_Synthese>(sql_Syntax, new {
        codeClioent = codeClient, //I suspect this is a typo
        DateDeb,
        DateFin
    });
}

And in that much shorter, readable, faster form, you'll hopefully find whatever bug you have much faster.

Alternatives:

• PetaPoco
• Dapper (fewer features, but stackoverflow uses it!)
• OrmLite (of ServiceStack fame)
• Massive (older, uses dynamic which is a feature that can cause bad habits - I don't recommend this unless you really know what you're doing)

You could use heavier, more invasive ORM's like the Entity framework and NHibernate, but these require quite a bit more learning, and they're much slower, and they impose a particular workflow on you which I don't think makes them the best choice in your case.

Answer 3

when i debug it with sql profiler it show the data....that meant the data is not empty, but it never enter into this loop.

It's the other way round: if it never enters into this loop, then it means "the data is empty", i.e. the query returns no rows.

The bug is in your code, not SqlReader: you possibly have the wrong values in your parameters, or maybe the query you read from a file isn't what you think it is. Get the debugger out and inspect the query text and parameters.