Web 服务仅使用 sql queryC# 返回第一行

Question

>我有以下代码

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();                              
}

问题是只返回第一行而不是表中的所有行。有什么想法可能有什么问题？

同样，当我返回时，它说"此XML文件似乎没有任何与之关联的样式信息。文档树如下所示。它应该有一个这样的标题：

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

Answer 1

如果你在

读取器中有n可用的获取行，你可能会得到最后一行，因为创建对象的属性在while (dr.Read())的每次迭代中都会过度写入，最后将最新值返回给调用方法。你应该重新定义你的方法以返回List<typVenda>，从而用每次迭代中构造的对象填充列表。最后在迭代结束时返回列表。

还有几条建议供您改进代码：

1. 在处理SqlConnection和SqlCommand时利用使用;因为您不需要为紧密连接和处置命令等而烦恼，因此使用将处理这些事情
2. 不需要检查读取器是否有行（if (dr.HasRows)）使用while (dr.Read())如果没有行，则不会执行封闭的语句。

现在考虑以下代码：

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;
}

Answer 2

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;
}