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: enter image description here

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.

• Joe Enos: Does your SQL text consist of only a single SELECT statement? If there are more than one, then you can move through them by calling reader.NextResult().
• marc_s: Using a decent ORM like Entity Framework, Dapper.NET or NHibnerate would make this just soo much easier.... no messy SqlDataReader, no messy "glue code" .....
• Joe Enos: Can you post the SQL statement (the contents of sql_Syntax while you're debugging).
• Joe Enos: You posted what you saw in profiler. Can you post the contents of the sql_Syntax variable on the .NET side? My guess is that there are multiple statements or commands in there - this would result in multiple statements in profiler, so even if your desired query worked properly, something else in there is funking up your results.
• Eamon Nerbonne: What Joe Enos said - show us the real query...

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?

• marc_s: And while you're at it - please also put SqlCommand and SqlDataReader into using()...... blocks!
• Joe Enos: I'm assuming the connection string was applied and the connection was opened by the wrapper class. Otherwise they would have gotten an exception rather than just no rows.
• Joe Enos: The query is being set in SqlCommand comm_Command = new SqlCommand(sql_Syntax, connectionWrapper.conn); - the sql_Syntax string is the query, and the connectionWrapper.conn is the SqlConnection.
• AAlferez: the sql_Syntax is nowhere, and the SqlConnection is opened before that!

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.