Eric Filardi Eric Filardi - 10 days ago 5
C# Question

Missing results when querying SQL Server from ASP.NET WebService

Guys please I've tried almost everything I know and all possibilities that I can implement...

What happens is that when I make this select by proc or commandtext, my datareader returns only 3 records but I have 7 on the database with those parameters.
If I mark those 3 as read and make a new requisition only 2 are sent back to me. Same again if there is only one, the last one is never brought back to me.

Here is my code

public List<Documentos> GetDocumentosDisponiveisParaMotorista(string motoristaCpf, string veiculoPlaca,
long transportadora)
{
var retorno = new List<Documentos>();

var command = Banco.GetStoredProcCommand("dbo.SelectDocumentos");
AddInParameter(command, "@MotoristaCpf", motoristaCpf);
AddInParameter(command, "@VeiculoPlaca", veiculoPlaca.ToUpper());
AddInParameter(command, "@TransportadoraId", transportadora);

//var command = Banco.GetSqlStringCommand("SELECT " +
// "TransportadoraId" +
// ", FilialSigla" +
// ", TipoDocumentoId" +
// ", Documento" +
// ", DocumentoSerie" +
// ", MotoristaCpf" +
// ", VeiculoPlaca" +
// ", DocumentoChave" +
// ", DocumentoTransporte" +
// ", TipoDocTransporte" +
// ", NumeroNotaFiscal" +
// ", DestinatarioCpfCnpj" +
// ", LocalEntregaId" +
// ", StatusId" +
// ", Comprovante" +
// ", Transmitido" +
// ", TransmitidoData" +
// ", DataCadastro" +
// ", DataAlteracao " +
// "FROM Documentos WITH(NOLOCK) " +
// "WHERE MotoristaCpf = @MotoristaCpf " +
// "AND VeiculoPlaca = @VeiculoPlaca " +
// "AND TransportadoraId = @TransportadoraId " +
// "AND Transmitido is null ");

//command.Parameters.Add(new SqlParameter("@MotoristaCpf", SqlDbType.NVarChar, 11, "MotoristaCpf"));
//command.Parameters.Add(new SqlParameter("@VeiculoPlaca", SqlDbType.NVarChar, 7, "VeiculoPlaca"));
//command.Parameters.Add(new SqlParameter("@TransportadoraId", SqlDbType.BigInt, 999999999, "TransportadoraId"));

//command.Parameters["@MotoristaCpf"].Value = motoristaCpf;
//command.Parameters["@VeiculoPlaca"].Value = veiculoPlaca.ToUpper();
//command.Parameters["@TransportadoraId"].Value = transportadora;
//command.Connection = Banco.CreateConnection();
//command.Connection.Open();
//command.CommandTimeout = 3600;

using (var dataReader = Banco.ExecuteReader(command))
{
while (dataReader.Read())
{
retorno.Add(dataReader.Read() ? Preencher(dataReader) : null);
}

dataReader.Close();

}
return retorno;
}


I'm using SQL Server 2005. This is a WebService code in asp.net C#. My connection is made using connectionstring. I don't use NHibernate and I'm using
Microsoft.Practices.EnterpriseLibrary.Data
.

Answer

As Crowcoder mentions in the comments your main problem is calling dataReader.Read() twice. Take a look at this related question to see how to use the SqlDataReader.Read() method.

Furthermore, there is no need for you to call dataReader.Close(); as it will automatically be disposed of at the end of your using block.