Trying to read data from sql database in asp.net web service

Question

I've been trying to get data from a local database in asp.net web service .asmx page but I only get one element back, and not even the first element. When i try another index, i get index out of bounds of array exception, but i know it's not because my database is larger than whatever index i try.

Here is my current .asmx code

    <%@ WebService Language="C#" Class="PostWebService" %>

using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.SqlClient;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]

public class PostWebService  : System.Web.Services.WebService {
    int result;
    [WebMethod]
    public int GetRates() {
        //try
        //{
            SqlConnection connection = new SqlConnection(@"Data Source = (local); Initial Catalog =  RatesDB; Integrated Security=True");
            SqlCommand cmd = new SqlCommand(" select HeartRate from Rates order by HeartRate DESC", connection);
            connection.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            int i = 0;
            while (dr.Read())
            {
                result = Convert.ToInt32(dr[i++]);
                Console.WriteLine(result);
            }
            dr.Close();
            connection.Close();
        //}
        //finally
        //{ 

        //}
        return result;
    }
}

I've been playing with the code to try to get a different result but i keep getting error:

System.IndexOutOfRangeException: Index was outside the bounds of the array.
   at System.Data.SqlClient.SqlDataReader.CheckDataIsReady(Int32 columnIndex, Boolean allowPartiallyReadColumn, Boolean permitAsync, String methodName)
   at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn)
   at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
   at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
   at System.Data.SqlClient.SqlDataReader.get_Item(Int32 i)
   at PostWebService.GetRates() in C:\Users\Rommel\Desktop\Website\Website\Website\PostWebService.asmx:line 28

If i use 0 as my index for dr[0] then i can read a value but it's not even the first one, so i do not understand why it is reading my database incorrectly. Any tips in the right direction or even corrections in my code would be appreciated.

`dr[i]` gets the value of column number `i + 1` on the current row (so `dr[0]` is the first column). As `i++` keeps on increasing `i` forever you will try to read a non-existent column if there are more rows than columns & that will cause an exception. — Alex K., Jul 26 '18 at 16:10
You only get one result because you only return one result - you overwrite the `result` variable in the loop so ultimately you return the value from the last row you saw. If you want to return *n* rows you need to return a collection of some type. — Alex K., Jul 26 '18 at 16:11
thank you for quick reply. why is it getting the value of the column and not the row? and even then why is it not returning the first element if i use dr[0]? how should i go about fixing this? — trainee, Jul 26 '18 at 16:19
Since you are returning an int, you want only one result. Which one is that? First row, last row, or what? Decide on that and use ExecuteScalar instead. — Cetin Basoz, Jul 26 '18 at 16:22
`dr[i]` reads *columns* (across) not *rows* (down), inside your loop `dr[0]` is the first column, i.e. HeartRate, of the current row. If you change `dr[i++]` to `dr[0]` you should get a line printed for each row in you query result. — Alex K., Jul 26 '18 at 16:22
Here it shows how to do it https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader and a nice post with "Using" to dispose https://stackoverflow.com/questions/4018114/read-data-from-sqldatareader — P. Waksman, Jul 26 '18 at 16:23
@CetinBasoz i want to return every int in my column of HeartRate. @ Alex i tried that but i only get one element back, which is either the last one or the fifth one depending on if i have the "order by HeartRate DESC". @ waksman i took a look at those but i still get index out of bounds — trainee, Jul 26 '18 at 16:38
@CetinBasoz yes my ultimate goal is to later connect this web service to a website i am making which contains graphs that reads int type. but my current problem is even being able to get all the data in int form. is this possible? — trainee, Jul 26 '18 at 16:48

score 0 · Accepted Answer · answered Jul 26 '18 at 16:56

Get a single result (decided by ordering):

public int GetRate()
{
    int i = -1; // an impossible value
    try
    {
        using (SqlConnection connection = new SqlConnection(@"Data Source=(local);Initial Catalog=RatesDB;Integrated Security=True"))
        using (SqlCommand cmd = new SqlCommand(@"select top(1) HeartRate 
                   from Rates 
                   order by HeartRate DESC", connection))
        {
            connection.Open();
            i = Convert.ToInt32(cmd.ExecuteScalar());
            connection.Close();
        }
    }
    catch
    { }
    return result;
}

Get multiple values from a column:

public List<int> GetRates()
{
    var result = new List<int>();
    try
    {
        using (SqlConnection connection = new SqlConnection(@"Data Source=(local);Initial Catalog=RatesDB;Integrated Security=True"))
        using (SqlCommand cmd = new SqlCommand(@"select HeartRate 
                   from Rates 
                   order by HeartRate DESC", connection))
        {
            connection.Open();
            var rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                result.Add((int)rdr[0]);
            }
            connection.Close();
        }
    }
    catch
    { }
    return result;
}

yes thank you, just what i needed. appreciate everyone for their help! — trainee, Jul 26 '18 at 17:05

Trying to read data from sql database in asp.net web service

1 Answers1