Unable to loop all database value in webmethod and display in asmx

I'm trying to loop and display out all the values i have in my database through the webmethod from my web-service. I'm then trying to display it out approperaitely in the asmx format.

public class eCommerce
{
    public string ItemName { get; set; }
    public string cost { get; set; }
}

I tried to use the get set in order to get the data accordingly.

Below i also tried using the .hasRow with a while loop. However, it is only displaying and returning me the 1st row of data in my database.

[WebMethod]
public eCommerce ItemAvailable(string itemcategory)
{
    eCommerce Item = new eCommerce();

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    con.Open();
    SqlCommand cmd = new SqlCommand("SELECT ItemName, Cost FROM eCommerce Where Category ='"+itemcategory+"'", con);
    SqlDataReader dr = cmd.ExecuteReader();
    if (dr.HasRows)
    {
        while (dr.Read())
        {
            Item.ItemName = Convert.ToString(dr["ItemName"]);
            Item.cost = Convert.ToString(dr["Cost"]);
        }
    }
    con.Close();

    return Item;
}

Have i left out anything which is able to loop and return me all the values i have in the database.

Answers


The while loop is overwriting Item in each loop. It looks like you want to return multiple eCommerce instances so why don't you return a List.

[WebMethod]
public List<eCommerce> ItemAvailable(string itemcategory)
{

    List<eCommerce> allItems = new List<eCommerce>();

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    con.Open();
    SqlCommand cmd = new SqlCommand("SELECT ItemName, Cost FROM eCommerce Where Category ='"+itemcategory+"'", con);
    SqlDataReader dr = cmd.ExecuteReader();
    if (dr.HasRows)
    {
        while (dr.Read())
        {
            item = new eCommerce();
            item.ItemName = Convert.ToString(dr["ItemName"]);
            item.cost = Convert.ToString(dr["Cost"]);

            allItems.add(item);
        }
    }
    con.Close();

    return allItems;
}

Need Your Help

Add leiningen.new.templates namespace as a dependency in project

clojure leiningen

Is it feasible to add only this one Leiningen namespace as a dependency in a project (standard project and not lein template)?

Access VBA Search Loop

vba ms-access access-vba

I am currently making an search loop in VBA for my database. In this database I Have 2 Tables, one with the customers records called Main and another one with search words called Sparr. The idea is...