WebService only returning first row using a sql queryC#

Question

I have the following code

public class dthvendas: System.Web.Services.WebService {

public class typVenda 
{
    //public string campanha;
    public string id;
    public string contact_moment;
    public string nome;
    // few more properties
}

[WebMethod][SoapDocumentMethod]

public typVenda getListaVendas(string dt_min, string dt_max)
{
    //venda vendas = new List<venda>();     
    typVenda objVenda = new typVenda();     

    SqlConnection con = new SqlConnection(@"Data Source=server;Initial Catalog=database;User ID=user;password=password");            
    //SqlCommand cmd = new SqlCommand("SELECT * FROM dbo where contact_moment >='" + dt_min + "' AND contact_moment <DATEADD(dd, 1, '" + dt_max + "')", con); 
    SqlCommand cmd = new SqlCommand("SELECT * FROM dbo.vcnosadesoes_getlistavendas", con); 

    con.Open();     
    SqlDataReader dr = cmd.ExecuteReader();
    if (dr.HasRows)
    {
        while (dr.Read())
        {

            //var objVenda = new typVenda();
            //objVenda.campanha = dr["id"].ToString();

            objVenda.id = dr["id"].ToString();
            objVenda.contact_moment = dr["contact_moment"].ToString();
            objVenda.nome = dr["nome"].ToString();
            objVenda.pacote = dr["pacote"].ToString();
            objVenda.telefone = dr["telefone"].ToString();
            objVenda.codigo_wc = dr["codigo_wc"].ToString();

            //vendas.Add(objVenda);
        }
        dr.Close();
    }
    con.Close();                        
    return objVenda;

    //return vendas.ToArray();                              
}

The problem is that is only returning the first row instead of all rows from the table. What could be the problem any ideas?

Also when i return it says "This XML file does not appear to have any style information associated with it. The document tree is shown below." It should have a header like this:

<?xml version="1.0" encoding="UTF‐8" ?>

Well yes, your method is declared to return `typVenda` which is a single result. Perhaps you want to return `List` instead? (At that point, you should create a new instance for each iteration of the loop.) Also, you should be using `using` statements for your SqlConnection etc, rename everything to follow .NET naming conventions, and use parameterized SQL. — Jon Skeet, Apr 26 '16 at 12:39
Your question contains too much irrelevent code. I didn't bother reading what wasn't immediately visible. — Dan Bracuk, Apr 26 '16 at 12:40

sujith karivelil · Answer 1 · 2016-04-26T12:58:29.147

If you have n fetched rows available in the reader, probably you will get the last row, since the created object's properties ware over writted in each iteration of the while (dr.Read()) and finally return the latest value to the calling method. You should re-define your method to return List<typVenda>, and hence populate the list with objects constructed in each iteration. and finally return the list at the end of iteration.

Few more suggestions For you to improve the Code:

Make use of using while dealing with SqlConnection and SqlCommand; since you need not to bother about the close connection and disposing commands etc. using will take care of these things
Need not to check the reader has rows or not (if (dr.HasRows)) use while (dr.Read()) will not execute the enclosed statements if there are no rows.

Now consider the following code:

public List<typVenda> getListaVendas(string dt_min, string dt_max)
{
    List<typVenda> objVendaList = new List<typVenda>();

    using (SqlConnection con = new SqlConnection("connection String here"))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM dbo.vcnosadesoes_getlistavendas", con))
        {
            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {

                var objVenda = new typVenda();

                // Assign the values to the properties here

                objVendaList.Add(objVenda);
            }
            dr.Close();
        }
    }
    return objVendaList;
}

It works great thanks : ) . Is it possible for the output to be a XML string with that header i mentioned? — Miguel Pecegueiro, Apr 26 '16 at 13:28

score 0 · Answer 2 · answered Apr 26 '16 at 12:43

public List<typVenda> getListaVendas(string dt_min, string dt_max)
{
    venda vendas = new List<typVenda>();     
    typVenda objVenda = new typVenda();     

    SqlConnection con = new SqlConnection(@"Data Source=server;Initial Catalog=database;User ID=user;password=password");            
    //SqlCommand cmd = new SqlCommand("SELECT * FROM dbo where contact_moment >='" + dt_min + "' AND contact_moment <DATEADD(dd, 1, '" + dt_max + "')", con); 
    SqlCommand cmd = new SqlCommand("SELECT * FROM dbo.vcnosadesoes_getlistavendas", con); 

    con.Open();     
    SqlDataReader dr = cmd.ExecuteReader();
    if (dr.HasRows)
    {
        while (dr.Read())
        {

            var objVenda = new typVenda();
            //objVenda.campanha = dr["id"].ToString();

            objVenda.id = dr["id"].ToString();
            objVenda.contact_moment = dr["contact_moment"].ToString();
            objVenda.nome = dr["nome"].ToString();
            objVenda.pacote = dr["pacote"].ToString();
            objVenda.telefone = dr["telefone"].ToString();
            objVenda.codigo_wc = dr["codigo_wc"].ToString();

            vendas.Add(objVenda);
        }
        dr.Close();
    }
    con.Close();                        

    return vendas;
}

WebService only returning first row using a sql queryC#

2 Answers2