Retrieve multiple rows from SQL Server in a web service and xamarin android?

Question

I want to retrieve multiple rows from SQL Server in a web service. I get 2 rows as result when I search jo number 1 and my table name is TestOrderStatus.

While I get only one row in the search result.

Thanks for all

public class ReturnOrder
{
    public string Message;
    public int QtqSlit;
    public int QtyPcs;
    public string Design;
}

[WebMethod(MessageName = "OrderStatus", Description = "OrderStatus new Order")]
[System.Xml.Serialization.XmlInclude(typeof(ReturnOrder))]
public ReturnOrder OrderStatus(string JO)  /// get list of notes
{
    int QtqSlit = 0;
    int QtyPcs = 0;
    String Design = "";
    string Message = "";

    SqlDataReader reader;
    using (SqlConnection connection = new SqlConnection(DBConnection.ConnectionString))
    {
        SqlCommand cmd = new SqlCommand("SELECT QtqSlit,QtyPcs,Design  FROM TestOrderStatus where JO=@JO");
        cmd.CommandType = CommandType.Text;
        cmd.Connection = connection;
        cmd.Parameters.AddWithValue("@JO", JO);

        connection.Open();

        reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                QtqSlit = reader.GetInt32(0);
                QtyPcs = reader.GetInt32(1);
                Design = reader.GetString(2);
            }
        }

        if (QtqSlit == 0)
        {
            Message = " user name or password is incorrect";
        }

        reader.Close();
        connection.Close();
    }

    ReturnOrder rt = new ReturnOrder();
    rt.Message = Message;
    rt.QtqSlit = QtqSlit;
    rt.QtyPcs = QtyPcs;
    rt.Design = Design;

    return rt;
}

The rest of the code public class ReturnOrder { public string Message; public int QtqSlit; public int QtyPcs; public string Design; } — Ahmed Rashed
– Ahmed Rashed, Commented Apr 22, 2017 at 21:07
First you don't need the internal for loop. This will read the three fields of the current record for three times. One is enough. Second the while loop, after the first loop (first record), runs again a second time replacing the values loaded in the first loop (and if there are 3 record it runs again and so on). So you end with just one record, the last one — Steve
– Steve, Commented Apr 22, 2017 at 21:10

wikiCan · Accepted Answer · 2017-04-22 21:37:28Z

1

You need a List for multiple rows if you want more than one result. So your code may be like this.

public class ReturnOrder
{
    public string Message;
    public int QtqSlit;
    public int QtyPcs;
    public string Design;


}


[WebMethod(MessageName = "OrderStatus", Description = "OrderStatus new Order")]
[System.Xml.Serialization.XmlInclude(typeof(List<ReturnOrder>))]
public List<ReturnOrder> OrderStatus(string JO)  /// get list of notes
{
    List<ReturnOrder> result=new List<ReturnOrder>();
    int QtqSlit = 0;
    int QtyPcs = 0;
    String Design = "";
    string Message = "";

    //try
    //{
        SqlDataReader reader;
        using (SqlConnection connection = new SqlConnection(DBConnection.ConnectionString))
        {
            SqlCommand cmd = new SqlCommand("SELECT QtqSlit,QtyPcs,Design  FROM TestOrderStatus where JO=@JO");
            cmd.CommandType = CommandType.Text;
            cmd.Connection = connection;
            cmd.Parameters.AddWithValue("@JO", JO);
            connection.Open();

            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                    QtqSlit = reader.GetInt32(0);
                    QtyPcs = reader.GetInt32(1);
                    Design = reader.GetString(2);

    ReturnOrder rt = new ReturnOrder();
    rt.Message = Message;
    rt.QtqSlit = QtqSlit;
    rt.QtyPcs = QtyPcs;
    rt.Design = Design;

result.add(rt);


            }
            if (QtqSlit == 0)
            {
                Message = " user name or password is in correct";
            }
            reader.Close();

            connection.Close();
        }

    //}
    //catch (Exception ex)
    //{
    //    Message = " cannot access to the data";
    //}




    return result;
}

answered Apr 22, 2017 at 21:37

wikiCan

4495 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ahmed Rashed Over a year ago

Thank you to much now web service is ok , But when he summoned the values in Xamrian Android doesn't know.

wikiCan Over a year ago

Sorry for late answer, but did you change returned data type List<ReturnOrder> in the Xamarian ?

Ahmed Rashed · Accepted Answer · 2017-04-23 08:10:50Z

Thank you to much now web service is ok , But when he summoned the values in Xamrian Android doesn't know.

public class Mainlistview : Activity { protected override void OnCreate(Bundle bundle) { base.OnCreate(bundle);

        // Set our view from the "main" layout resource
        SetContentView(Resource.Layout.Mainlistview);


        ListView ListView = FindViewById<ListView>(Resource.Id.listView1);



        Selling.WebServiceDB ws = new Selling.WebServiceDB();
        ws.OrderStatusListCompleted += Ws_OrderStatusListCompleted;
        ws.OrderStatusListAsync(Convert.ToString(1));



    }


    private void Ws_OrderStatusListCompleted(object sender, Selling.OrderStatusListCompletedEventArgs e)
    {

        ListView ListView = FindViewById<ListView>(Resource.Id.listView1);
        string msg = "";

         if (e.Result.QtqSlit.ToString().Equals("0"))
        {
            msg = e.Result.Message;
        }
        else
        {

            // full class
            List<TableItem> tableItems = new List<TableItem>();
            tableItems.Add(new TableItem("" + e.Result.QtqSlit, "" + e.Result.QtyPcs, Resource.Drawable.Icon));
            ListView.Adapter = new HomeScreenAdapter(this, tableItems);


        }

    }

Collectives™ on Stack Overflow

Retrieve multiple rows from SQL Server in a web service and xamarin android?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related